6. Создание структуры реляционной бд торговой фирмы

Кейс-ситуация: Вам необходимо спроектировать БД для условной предметной области, связанной с типовым торговым бизнесом, в котором сотрудники обслуживают клиентов и выполняют определённые заказы.

Цель учебного кейса: приобрести навыки в разработке инфологической модели для сложной предметной области; научиться формировать структуру реляционной БД, устанавливать связи между таблицами и заполнять таблицы данными.

Задания и алгоритм реализации кейс-ситуации

1. Создайте базу данных «Фирма» (кейс-компонент 3).

2. Разработайте информационно-логическую модель реляционной базы данных для условной предметной области «Фирма». Перед разработкой информационно-логической модели реляционной базы данных рассмотрим, из каких информационных объектов должна состоять эта БД. Если все сведения поместить в одном объекте, то будет неудобно для работы. Начнутся повторы данных. Всякий раз, когда сотрудник Иванов будет работать с какой-либо фирмой, придется прописывать данные о сотруднике и клиенте заново, в результате чего можно допустить множество ошибок. Чтобы уменьшить число ошибок, можно исходный объект разбить на несколько объектов и установить связи между ними. Можно выделить три объекта, которые не будут обладать избыточностью.

Таким образом, по описанию предметной области выделяем 3 объекта: Сотрудники, Клиенты и Заказы. Представим состав реквизитов этих объектов в виде «название объекта (перечень реквизитов)»:

Сотрудники (код сотрудника, фамилия, имя, отчество, должность, дата рождения, телефон, адрес, эл_почта, комиссия).

Клиенты (код клиента, название компании, адрес, телефон, факс, эл_ почта, заметки)

Заказы (код заказа, код клиента, код сотрудника, дата размещения, дата исполнения, сумма, отметка о выполнении).

Рассмотрим связь между объектами. Объект Сотрудники связан с объектом Заказы связью один-ко-многим, поскольку один сотрудник может обслуживать несколько заказов. Объект Клиенты связан с объектом Заказы связью один-ко-многим, поскольку один клиент может оформить в данной фирме несколько заказов. В реляционной базе данных в качестве объектов рассматриваются отношения, кото­рые можно представить в виде таблиц. Таблицы между собой связываются посредством общих полей, т.е. одинаковых по форматам и, как правило, по названию, имеющихся в обеих таблицах. Рассмотрим, какие общие поля надо ввести в таблицы для обеспечения связности данных. Для связи таблиц Сотрудники и Заказы используем поле код сотрудника, которое в одной таблице будет первичным ключом, а в другой – внешним (или вторичным). А для связи таблиц Клиенты и Заказы используем поле код клиента, которое в одной таблице будет первичным ключом, а в другой – внешним (или вторичным).

3. Создайте в режиме Конструктора (кейс-компонент 4) 3 таблицы (таблицы 13-15). Задайте свойства полей.

4. Отдельные таблицы, содержащие информацию по определенной теме, необходимо связать в единую структуру БД. Для связывания таблиц следует задать ключевые поля (определение ключевого поля см. п. 1.3).

5. Таблица Заказы содержит поля код сотрудника и код клиента. При их заполнении могут возникнуть некоторые трудности, так как не всегда удается запомнить все предприятия, с которыми работает фирма, и всех сотрудников с номером кода. Для удобства можно создать раскрывающиеся списки с помощью Мастера подстановок.

Таблица 13 – Структура таблицы Сотрудники

Имя поля	Тип данных
код сотрудника	Числовой
фамилия	Текстовый
имя	Текстовый
отчество	Текстовый
должность	Текстовый
телефон	Текстовый
адрес	Текстовый
дата рождения	Дата/Время
эл_почта	Гиперссылка
комиссия	Числовой

Таблица 14 – Структура таблицыКлиенты

Имя поля	Тип данных
код клиента	Числовой
название компании	Текстовый
адрес	Текстовый
телефон	Текстовый
факс	Числовой
эл_почта	Гиперссылка
заметки	Поле МЕМО

Таблица 15 – Структура таблицы Заказы

Имя поля Тип данных	Тип данных
код заказа Счетчик	Числовой
код клиента Числовой	Числовой
код сотрудника Числовой	Числовой
дата размещения Дата/Время	Дата/Время
дата исполнения Дата/Время	Дата/Время
сумма Денежный	Денежный
отметка о выполнении Логический	Логический

1. Откройте таблицу Заказы в режиме Конструктора.

2. Для поля код сотрудника выберите тип данных Мастер подстановок.

3. В списке таблиц выберите таблицу Сотрудники, Click на кнопке Далее.

4. В списке Доступные поля выберите поле код сотрудника и Click на кнопке со стрелкой, чтобы ввести поле в список Выбранные поля. Таким же образом добавьте поля фамилия и имя и щелкните на кнопке Далее.

5. Выберите порядок сортировки списка по полю Фамилия.

6. Сбросьте флажок Скрыть ключевой столбец и нажмите кнопку Далее.

7. На последнем шаге Мастера подстановок замените при необходимости надпись для поля подстановок и Click на кнопке Готово.

6. Аналогичным образом создайте раскрывающийся список для поля код клиента.

7. После создания ключевых полей необходимо создать связи. Создайте схему данных (кейс-компонент 5), не забыв закрыть при этом все открытые таблицы. В результате должна получиться схема данных, представленная на рисунке 28.

Рисунок 28 – Схема данных БД «Фирма»

8. В таблицу Сотрудники внесите данные о семи работниках. В поле адрес введите только город, в котором живет сотрудник (таблица 16).

9. В таблицу Клиенты внесите данные о семи предприятиях, с которыми работает данная фирма. В поле адрес введите только город, в котором находится фирма (таблица 17).

10. В таблице Заказы оформите десять заявок, поступивших на фирму (таблица 18).

Таблица 16 – Данные о сотрудниках

код струдника	фамилия	имя	отчество	должность	телефон	адрес	дата рождения	эл_ почта	комиссия
1001	Строкова	Анна	Петровна	менеджер	89237895543	Кемерово	05/05/1979	cak@ mail.ru	0,1
1002	Кирин	Иван	Алексеевич	менеджер	89046595534	Белово	12/01/1980	kia@ mail.ru	0,1
1003	Удалов	Семен	Павлович	менеджер	89053195678	Юрга	16/04/1965	usp@ mail.ru	0,2
1004	Кроль	Ирина	Ивановна	бухгалтер	89053595521	Кемерово	09/07/1983	kii@ mail.ru	0,1
1005	Сенченко	Ольга	Дмитриевна	начальник	89233199041	Кемерово	20/04/1974	sod@ mail.ru	0,2
1006	Креков	Павел	Михайлович	менеджер	89047892554	Прокопьевск	02/02/1980	[e-mail]	0,1
1007	Чиркова	Юлия	Семеновна	менеджер	89057893456	Мыски	13/05/1981	cus@ mail.ru	0,1

Таблица 17 – Данные о клиентах

код клиента	название компании	адрес	телефон	факс	эл_почта	заметки
2001	ОАО ПРОК	Кемерово	89237895523	567834	[e-mail]
2002	ООО Юрлин	Юрга	89046595534	234501	[e-mail]
2003	ОАО Варт	Белово	89053195665	345678	[e-mail]
2004	ООО БНК	Кемерово	89053595578	213457	[e-mail]
2006	ОАО Трек	Киселевск	89233199878	890123	[e-mail]
2008	ООО ВИТА	Белово	89047897059	557890	[e-mail]
2007	ОАО ДИТ	Кемерово	89057894451	234567	[e-mail]

Таблица 18 – Данные о заказах

код заказа	код клиента	код сотрудника	дата размещения	дата исполнения	сумма	отметка о выпол-нении
3001	2008	1007	05/11/2009	10/11/2009	5000	да
3002	2001	1001	05/11/2009	18/11/2009	8900	да
3003	2007	1004	16/12/2009	20/12/2009	13700	да
3004	2003	1002	16/12/2009	25/12/2009	24500	да
3005	2008	1007	22/02/2010	01/03/2010	6700	да
3006	2002	1003	22/02/2010	07/03/2010	12000	да
3007	2004	1002	25/02/2010	02/03/2010	3600	нет
3008	2006	1001	25/02/2010	10/03/2010	4600	да
3009	2004	1002	06/03/2010	26/03/2010	7400	нет
3010	2006	1001	06/03/2010	20/03/2010	3450	нет