Третья нормальная форма (3НФ)
Для того, чтобы таблица находилась в третьей нормальной форме необходимо, чтобы она находилась во второй нормальной форме и в ней должны быть исключены все транзитивные зависимости. Таблица Emplсодержит следующие функциональные зависимости:
e_id → e_name, job, d_id, d_ name
d_id → d_ name
Первичным ключом является e_idи все атрибуты полностью функционально зависимы от него. Но, заметим также, что:
e_id → d_id
e_id → d_name
и
d_id → d_name
Это значит, что функциональная зависимость e_id → d_name является транзитивной ( она содержит промежуточный шаг – зависимость d_id → d_name. Чтобы избавиться от этой транзитивной зависимости а, заодно, и уменьшить избыточность информации в таблице, разбиваем эту таблицу на две:
| Dep: | Empl: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
d_id – первичный ключ таблицы |
e_id – первичный ключ таблицы |
Обе таблицы находятся в 3НФ.
2. Учитывая опыт формирования предыдущих таблиц, для хранения информации о Клиентах и Проектах организации создаём следующие таблицы:
Таблица с информацией о Клиентах организации:
Client:
| cl_id | f_name | addr | tel |
| Strogof | 10945 Brigge Rd. Oacland | ||
| Tvorogenko | 578 Bljnde St. Rockville | ||
| Dragov | 5768 Seventh Av. Gary |
Столбец cl_id (код клиента) является первичным ключом таблицы. Таблица находится в 3НФ.
Формируем таблицу
Projects:
| pr_id | pr_theme | e_id | cl_id | data_start | data_finish | price |
| 12.02.2014 | 12.02.2015 | 12 345,67 | ||||
| 18.03.2014 | 27.08.2015 | 17 345,78 | ||||
| 24.05.2014 | 13.10.2015 | 20 567,55 | ||||
| 20.06.2014 | 23.03.2015 | 10 340.99 |
В эту таблицу мы поместили следующую информацию:
× pr_id – уникальный код проекта (номер заключенного договора на разработку проекта). Будет выполнять роль первичного ключа таблицы;
× pr_theme – название темы проекта;
× e_id – код сотрудника, являющегося руководителем проекта. Будет исполнять роль внешнего ключа, указывающего на таблицу Empl для получения информации о руководителе проекта;
× cl_id – код клиента, который является заказчиком данного проекта. Будет исполнять роль внешнего ключа, указывающего на таблицу Clientдля получения информации о том, какой клиент является заказчиком проекта.
× data_start – дата заключения договора на разработку проекта;
× data_finish – срок выполнения проекта;
× price – стоимость проекта.
Таблица находится в 3НФ.
В таблицу Projectsмы не включили информацию о сотрудниках, занятые в реализации проекта с указанием доли сотрудника в общем гонораре за проект. Решаем эту проблему:
3. Таблица Проект - Сотрудник
Создадим таблицу Proj_Emp, в которой столбцы pr_idи e_idбудут формировать составной первичный ключ, а по отдельности каждый из них будет внешним ключом, указывающим на соответствующие таблицы Projects и Empl. Не ключевой столбец royalty_share будет показывать долю сотрудника в общем гонораре за проект.
Proj_Emp:
| pr_id | e_id | royalty_share |
| 0,4 | ||
| 0,2 | ||
| 0,2 | ||
| 0,2 | ||
| 0,4 | ||
| 0,3 | ||
| 0,3 | ||
| 0,4 | ||
| 0,3 | ||
| 0,3 | ||
| 0,4 | ||
| 0,2 | ||
| 0,2 | ||
| 0,2 |
Таблица находится в 3НФ.
Процесс проектирования базы данных закончен.
Дадим базе данных имя Organizationи опишем структуру сформированных таблиц.
Описание структуры таблицыEmpl:
| Имя столбца | Описание | Тип данных | null (Да/Нет) | Ключи |
| e_id | Уникальный код сотрудника | int(3) | первичный | |
| e_name | Фамилия сотрудника | varchar(15) | ||
| job | Должность | varchar(15) | ||
| d_id | Номер отдела | int(2) | внешний - Dep (d_id) |
Описание структуры таблицы Dep:
| Имя столбца | Описание | Тип данных | null (Да/Нет) | Ключи |
| d_id | Уникальный номер отдела | int(2) | первичный | |
| d_name | Название отдела | varchar(30) |
Описание структуры таблицы Skills:
| Имя столбца | Описание | Тип данных | null (Да/Нет) | Ключи |
| e_id | Код сотрудника | int(3) | Первичный, внешний - Empl (e_id) | |
| skill | Название вида квалификации | varchar(10) | Первичный |
Описание структуры таблицы Client:
| Имя столбца | Описание | Тип данных | null (Да/Нет) | Ключи |
| cl_id | Уникальный код клиента | int(2) | первичный | |
| f_name | Фамилия клиента | varchar(15) | ||
| addr | Адрес клиента | varchar(50) | Да | |
| tel | Телефон клиента | char(10) | Да |
Описание структуры таблицы Projects:
| Имя столбца | Описание | Тип данных | null (Да/Нет) | Ключи |
| pr_id | Уникальный код проекта | int(3) | первичный | |
| pr_theme | Название проекта | varchar(50) | ||
| e_id | Код сотрудника-руководителя проекта | int(3) | внешний - Empl (e_id) | |
| cl_id | Код клиента-заказчика проекта | int(2) | внешний - Clienr (cl_id) | |
| data_start | Дата заключения договора на разработку проекта | date | ||
| data_finish | Cрок выполнения проекта | date | Да | |
| price | Стоимость проекта | Float(7,2) | Да |
Описание структуры таблицыProj_Emp:
| Имя столбца | Описание | Тип данных | null (Да/Нет) | Ключи |
| pr_id | Код проекта | int(3) | первичный, внешний - Projects (pr_id) | |
| e_id | Код сотрудника | int(3) | первичный, внешний - Empl (e_id) | |
| royalty_share | Доля сотрудника в гонораре за проект | Float(2,1) | Да |
III. ER – диаграмма базы данных
| Dep |
| d_id B |
| d_name |
| data_start |
| skill B |
| price |
| Empl |
| e_id B |
| e_name |
| job |
| d_id |
| e_id B |
| Proj_Emp |
| pr_id B |
| e_id B |
| addr |
| royalty_share |
| Projects |
| pr_id B |
| pr_theme |
| e_id |
| cl_id |
| Client |
| cl_id B |
| f_name |
| tel |
| Skills |
| data_finish |
Варианты заданий
1. Предметная область: Колледж.
Необходимая информация:
A. Отделение колледжа: Название отделения; заведующий отделением; предметные комиссии, входящие в состав отделения.
Учитывать:
× Заведующий отделением является сотрудником колледжа. Отделение имеет только одного заведующего. Один сотрудник может быть заведующим только одного отделения.
× Отделение может содержать несколько предметных комиссий. Одна предметная комиссия входит в состав только одного отделения.
B. Предметная комиссия: Название предметной комиссии, председатель предметной комиссии, сотрудники предметной комиссии.
Учитывать:
× Председатель предметной комиссии является сотрудником колледжа. Предметная комиссия имеет только одного председателя. Один сотрудник может быть председателем только одной предметной комиссии.
× В предметной комиссии работает несколько сотрудников. Один сотрудник может работать в нескольких предметных комиссий.
C. Сотрудник колледжа: ФИО сотрудника; дата рождения; пол; стаж работы; в какой(в каких) предметной комиссии работает; ставка.
2. Предметная область: Склад строительных материалов.
Необходимая информация:
A. Товары на складе: Наименование товара; производитель; количество товара на складе; цена за единицу товара.
Учитывать:
× Производитель может поставлять на склад различные товары. Один товар может быть поставлен различными производителями.
× Цена одного и того же товара от различных производителей может отличаться.
B. Производитель: Наименование производителя; вид изготавливаемой продукции; страна.
3. Предметная область: Магазин продовольственных товаров.
Необходимая информация:
A. Товары в ассортименте: Наименование товара; производитель; цена за единицу товара; срок хранения (в днях).
Учитывать:
× Производитель может поставлять в магазин различные товары. Один товар может быть поставлен различными производителями.
× Цена одного и того же товара от различных производителей может отличаться.
B. Товары в магазине: Наименование товара; производитель; количество товара в магазине; дата поставки.
C. Производитель: Наименование производителя; вид изготавливаемой продукции; адрес.
4. Предметная область: Сборочный цех.
Необходимая информация:
A. Изделия, изготавливаемые в цеху: Наименование изделия; детали, необходимые для сборки; рабочий, собравший изделие; контроль качества («прошел»/ «не прошел»).
Учитывать:
× При сборке изделия используются различные детали. Одна и та же деталь может использоваться при сборке различных изделий
× В перечне деталей необходимо указать название детали и количество, используемое для сборки одного изделия.
B. Детали: Наименование детали; наименование цеха, изготовившего деталь.
5. Предметная область: Дисциплины колледжа.
Необходимая информация:
A. Дисциплины: Наименование дисциплины; количество часов; вид контроля (зачет/экзамен); преподаватель, читающий дисциплину.
Учитывать:
× Одну и ту же дисциплину могут читать различные преподаватели. Один преподаватель может читать различные дисциплины.
B. Преподаватели: ФИО преподавателя; дисциплина; группа, в которой читается дисциплина.
Учитывать:
× Одну и ту же дисциплину преподаватель может читать в разных группах. В одной группе читаются разные дисциплины.
6. Предметная область: Библиотека.
Необходимая информация:
A. Книги: Автор (авторы); название книги; год издания; издательство; количество экземпляров.
Учитывать:
× Одна книга может иметь несколько авторов. Один автор может написать несколько книг.
B. Читатели: ФИО читателя; адрес; телефон.
C. Необходимо хранить информацию о выданных книгах: какая книга выдана; кому книга выдана; дата выдачи; дата возврата; признак возврата.
Учитывать:
× Одна и та же книга может быть выдана разным читателям. Один читатель может взять несколько книг.
7. Предметная область: Сессия.
Необходимая информация:
A. Студенты: ФИО студента; группа.
B. Экзамены и зачеты: Дисциплина; признак – экзамен/зачет; дата; преподаватель; группа; студент; оценка.
Учитывать:
× Одну и ту же дисциплину сдает много студентов из разных групп.
× Один студент сдает разные дисциплины.
× Один преподаватель принимает зачеты и экзамены в разных группах и по разным дисциплинам.
8. Предметная область: Прокат спортивного оборудования.
Необходимая информация:
A. Оборудование: Наименование; страна – производитель; количество; цена проката в сутки.
B. Клиенты: ФИО клиента; паспортные данные; адрес; телефон.
C. Необходимо хранить информацию о выданном оборудовании: какое оборудование выдано; кому выдано; дата выдачи; дата возврата; признак возврата; сумма оплаты за прокат; признак оплаты.
Учитывать:
× Одно и то же оборудование может быть выдано разным клиентам. Один клиент может взять на прокат разное оборудование.
9. Предметная область: Поставка – продажа непродовольственных товаров.
Необходимая информация:
A. Товары в ассортименте: Наименование товара; поставщик; количество.
Учитывать:
× Один поставщик может поставлять в магазин различные товары. Один товар может быть поставлен различными поставщиками.
B. Поставщик: Название; адрес; телефон.
C. Поставка товара: Наименование товара; поставщик; дата поставки; количество; цена поставки за единицу товара.
D. Продажа товара: Наименование товара; дата продажи; проданное количество.
Учитывать:
× Цена поставки и цена продажи одного и того же товара может зависеть от даты( учет инфляции).
10. Предметная область: Банковские кредиты.
Необходимая информация:
A. Виды кредитов: Наименование кредита; процентная ставка по кредиту; минимальная сумма кредита; максимальная сумма кредита; максимальный срок погашения кредита (в месяцах).
B. Клиенты банка: ФИО клиента; паспортные данные; адрес; телефон.
C. Информация по выданным кредитам: какой кредит выдан; кому выдан; сумма кредита; дата выдачи кредита; дата погашения кредита; сумма погашения с учетом процентной ставки; признак погашения.
Учитывать:
× Один и тот же вид кредита может быть выдан различным клиентам. Один клиент может взять в банке различные кредиты.
11. Предметная область: Туристическая фирма.
Необходимая информация:
A. Клиенты: ФИО клиента; паспортные данные; адрес; телефон.
B. Сотрудники фирмы: ФИО сотрудника; должность; паспортные данные; адрес; телефон.
C. Туристические маршруты: Наименование маршрута; страна; описание маршрута (список посещаемых городов); количество дней; вид транспорта; стоимость путевки.
Учитывать:
× На каждый маршрут имеется по несколько путевок на разные даты выезда.
D. Информация по проданным путевкам: клиент; маршрут; дата выезда; сотрудник, продавший путевку.
Учитывать:
× Один и тот же вид маршрута может быть продан различным клиентам. Один клиент может купить путевки на различные маршруты
× Один сотрудник фирмы может обслужить нескольких клиентов. Один клиент может купить путевки у различных сотрудников.
12. Предметная область: Поликлиника.
Необходимая информация:
A. Врачи: ФИО; должность; специализация; квалификация; номер кабинета; график работы (по дням недели с указанием времени начала и конца приема).
Учитывать:
× В поликлинике может работать несколько врачей каждой специализации.
B. Пациенты: ФИО; паспортные данные; адрес; телефон.
C. Информация о посещение пациентами врачей поликлиники: пациент, врач, дата посещения, время, поставленный диагноз.
Учитывать:
× Один пациент может посетить разных врачей. Один врач принимает многих пациентов.
13. Предметная область: Абитуриенты колледжа.
Необходимая информация:
A. Анкета абитуриента: ФИО; дата рождения; оконченное учебное заведение (название, номер, населенный пункт, номер диплома); дата окончания учебного заведения; наличие красного диплома или золотой/ серебряной медали; адрес; телефон; наименование выбранной специальности.
Учитывать:
× Один абитуриент может подавать документы на несколько специальностей.
B. Специальности: Название специальности; количество бюджетных мест; количество контрактных мест; список дисциплин, которые сдаются при поступлении.
C. Вступительные экзамены: Абитуриент; дисциплина; экзаменационная оценка.
Учитывать:
× Одну и ту же дисциплину сдает много абитуриентов.
× Один абитуриент сдает разные дисциплины.
14. Предметная область: Ателье по пошиву одежды.
Необходимая информация:
A. Мастера: ФИО; должность; квалификация.
B. Клиенты: ФИО; адрес; телефон.
C. Заказы: Номер заказа; клиент; наименование изделия; мастер, выполняющий заказ; дата заказа; срок выполнения заказа; стоимость.
Учитывать:
× Один мастер может обслуживать нескольких клиентов. Один клиент может обслуживаться у разных мастеров.
15. Предметная область: Поставки товаров в сеть магазинов.
Необходимая информация:
A. Магазины: Название магазина; ФИО владельца; адрес.
Учитывать:
× Один человек может быть владельцем нескольких магазинов.
B. Поставщики: Наименование поставщика; телефон.
C. Товары: Наименование товара; страна-производитель;
D. Условия поставки товаров: Товар; поставщик; минимальный срок поставки; минимальная партия поставки; цена поставки за единицу товара.
E. Поставка: Товар; поставщик; магазин; объем поставки; дата поставки.
Учитывать:
× Один поставщик может поставлять товары в разные магазины.
× Один магазин может получать товары от разных поставщиков (в том числе один и тот же товар).
16. Предметная область: Транспортные перевозки.
Необходимая информация:
A. Автомобили: Марка автомобиля; модель; государственный номер; грузоподъемность; расход бензина; ФИО водителя.
Учитывать:
× Фирма может иметь несколько автомобилей одинаковой марки и модели.
B. Заявки: Код заявки; дата и время заявки; Название груза; количество груза; пункт доставки; срок доставки (в часах).
C. Выполнение заявок (доставка груза): Заявка; автомобиль; дата и время отправления; дата и время прибытия; пройденное расстояние.
Учитывать:
× Один автомобиль может доставлять груз по разным заявкам и в разные дни.
× Груз по одной заявке может быть доставлен несколькими автомобилями.
17. Предметная область: Автосалон.
Необходимая информация:
A. Автомобили: Марка; модель; цвет; год выпуска; страна-производитель; цена за базовую комплектацию.
B. Оборудование доукомплектации: Наименование; страна-производитель; цена.
C. Клиенты: ФИО клиента; паспортные данные; адрес; телефон.
D. Сотрудники фирмы: ФИО сотрудника; должность; паспортные данные; адрес; телефон.
E. Продажа автомобилей: какой автомобиль продан; какому клиенту; кто из сотрудников выполнил продажу; была ли проведена доукомплектация (и, если да, то чем доукомплектован автомобиль и цена доукомплектации); дата продажи; цена продажи.
18. Предметная область: SPA - салон.
Необходимая информация:
A. Процедуры (услуги салона): Наименование; длительность выполнения; стоимость.
B. Сотрудники: ФИО сотрудника; телефон; график работы (по дням недели с указанием времени начала и конца работы); список процедур, выполняемых сотрудником.
C. Обслуживание клиентов: ФИО клиента; дата посещения салона; какие процедуры были выполнены и кем из сотрудников..
19.