Третья нормальная форма (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 d_name
Administrative department
Projecting department
Realization department
Testing department

d_id – первичный ключ таблицы

e_id e_name job d_id
Drovko Administrator DB
Ivanov System admin
Konov Program’s
Kornienko Program’s
Koval Program’s
Palkin Program’s
Petrov Program’s
Sidorov Administrator DB
Turov System admin
Vertko Program’s

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.

Наши рекомендации