Н.В. Усманова - Проектирование баз данных (Методические указания по выполнению курсовой работы), страница 6

Суррогатный первичный ключ также может вводиться в тех случаях, когдапотенциальный ключ имеет большой размер (например, длинная символьная строка) или являетсясоставным (не менее трёх атрибутов).В отношении ПРОЕКТЫ три потенциальных ключа: Номер проекта, Названиепроекта и Сокращённое название. Меньше места занимает первый из них, но онмалоинформативен.

Зато сокращённое название, используемое в качестве внешнегоключа в других таблицах, позволит специалисту идентифицировать проект безнеобходимости соединения с отношением ПРОЕКТЫ.Таблица 3. Схема отношения ПРОЕКТЫ (Projects)Содержание поляИмя поляНомер проектаP_IDТип,длинаN(6)Название проектаСокращённое названиеОтделЗаказчикДанные заказчикаРуководительДата начала проектаДата окончанияпроектаРеальная датаокончанияСтоимость проектаПолученная суммаP_TITLEP_ABBRP_DEPARTP_COMPANYP_LINKSP_CHIEFP_BEGINP_ENDV(100)С(10)С(10)V(40)V(200)N(4)DDP_FINISHP_COSTP_SUMПримечанияобязательноеуникальноеполеобязательное полепервичный ключвнешний ключ (к Departs)обязательное полеобязательное полевнешний ключ (к Employees)обязательное полеобязательное поле, большедаты начала проектаDN(10)N(10)27обязательное полеобязательное поле, значениепо умолчанию – 0Потенциальным ключом отношения ЭТАПЫ является комбинация внешнегоключа и номера этапа, а потенциальным ключом вспомогательного отношенияУЧАСТИЕ является комбинация первых трёх полей этого отношения.

Можно вообщене вводить первичный ключ для данных отношений, т.к. на них никто не ссылается.Но уникальность этих комбинации является в данном случае ограничениемцелостности данных, поэтому мы возьмём эти комбинации в качестве первичныхключей соответствующих отношений.Таблица 4. Схема отношения ЭТАПЫ ПРОЕКТА (Stages)Содержание поляИмя поляПроектS_PROТип,длинаC(10)Номер этапаНазвание этапаДата начала этапаДата окончания этапаS_NUMS_TITLES_BEGINS_ENDN(2)V(200)DDРеальная датаокончанияСтоимость этапаПолученная сумма поэтапуФорма отчётностиS_FINISHDS_COSTS_SUMN(10)N(10)S_FORMV(100)ПримечаниявнешнийключProjects)составной(к первичныйключобязательное полеобязательное полеобязательное поле, > датыначалабольше даты начала этапаобязательное полеобязательное поле, значение поумолчанию – 0обязательное полеТаблица 5.

Схема отношения УЧАСТИЕ (Job)Содержание поляИмя поля Тип, длинаПримечания*ПроектJ_PROC(10)внешний ключ (к Projects)СотрудникJ_EMPN(4)внешний ключ (к Employees)РольJ_ROLEV(20)обязательное полеДоплатаJ_BONUSN(2)* – в отношении УЧАСТИЕ первичный ключ состоит из первых 3-х полей этогоотношения.2.3. Нормализация полученных отношений (до 4НФ)Механизм нормализации подразумевает определённую последовательностьпреобразования отношений к третьей нормальной форме. Мы не будем чёткопридерживаться этой последовательности, т.к. она избыточна, и многозначныеатрибуты сразу вынесем в отдельные отношения на первом же этапе. 1НФ.

Дляприведения таблиц к 1НФ требуется составить прямоугольные таблицы (однозначение атрибута – одна ячейка таблицы) и разбить сложные атрибуты на простые.Примечание. В реальных БД сложные атрибуты разбиваются на простые, если: а) этого требуетвнешнее представление данных; б) в запросах поиск может осуществляться по отдельной частиатрибута.Разделим атрибут Фамилия, имя, отчество на два атрибута Фамилия и Имя,отчество, Паспортные данные на Номер паспорта (уникальный), Дата выдачи и Кемвыдан, а Данные об образовании – на Вид образования, Специальность, Номер дипломаи Год окончания учебного заведения.28Многозначные атрибуты Комнаты и Телефоны из отношения ОТДЕЛЫ вынесемв отдельное отношение КОМНАТЫ, а домашние и мобильные телефоны и адресасотрудников – в отношение АДРЕСА-ТЕЛЕФОНЫ. Так как в комнате может не бытьтелефона, первичный ключ отношения КОМНАТЫ не определен (ПК не можетсодержать null–значения), но на этих атрибутах можно определить составнойуникальный ключ.

В отношении АДРЕСА-ТЕЛЕФОНЫ также нет потенциальныхключей: оставим это отношение без первичного ключа, т.к. на это отношение никто нессылается. Данные об образовании сотрудников также вынесем в отдельноеотношение.Что касается рабочих телефонов сотрудников, то один из этих номеров – основной– определяется рабочим местом сотрудника (рассматриваются только стационарныетелефоны).

Будем хранить этот номер в атрибуте Рабочий телефон. Наличие другихномеров зависит от того, есть ли в том же помещении (комнате) другие сотрудники,имеющие стационарные телефоны. Добавим в отношение СОТРУДНИКИ атрибутНомер комнаты, чтобы дополнительные номера телефонов сотрудника можно быловычислить из других кортежей с таким же номером комнаты.Связь между отношениями СОТРУДНИКИ и КОМНАТЫ реализуем черезсоставной внешний ключ (Номер комнаты, Рабочий телефон).Мы также удалим вычислимый атрибут Полученная сумма из отношенияПРОЕКТЫ, т.к. он является суммой значений аналогичного атрибута из отношенияЭТАПЫ ПРОЕКТОВ. Но атрибут Стоимость проекта оставим, т.к.

она фигурирует вдокументации по проекту. А для обеспечения логической целостности данныхпредусмотрим в приложении проверку того, что сумма по всем этапам совпадает состоимостью проекта.2НФ. В нашем случае составные первичные ключи имеют отношения ЭТАПЫПРОЕКТА и УЧАСТИЕ. Неключевые атрибуты этих отношений функционально полнозависят от составных первичных ключей.3НФ. В отношении ПРОЕКТЫ атрибут Данные заказчика зависит от атрибутаЗаказчик, а не от первичного ключа, поэтому его следует вынести в отдельноеотношение ЗАКАЗЧИКИ.

Но при этом первичным ключом нового отношения станетатрибут Заказчик, т.е. длинная символьная строка. Целесообразнее перенести в новоеотношение атрибуты Заказчик и Данные заказчика и ввести для него суррогатный ПК.Так как с каждым заказчиком может быть связано несколько проектов, связь междуотношениями ПРОЕКТЫ и ЗАКАЗЧИКИ будет 1:n и суррогатный ПК станет внешнимключом для отношения ПРОЕКТЫ.В отношении СОТРУДНИКИ атрибут Оклад зависит от атрибута Должность.Поступим с этой транзитивной зависимостью так же, как в предыдущем случае:создадим отношение ДОЛЖНОСТИ, перенесём в него атрибуты Должность и Оклад,а первичным ключом сделаем название должности. В отношениях СОТРУДНИКИ иОБРАЗОВАНИЕ атрибуты (Дата выдачи и Кем выдан) и (Номер диплома и Годокончания учебного заведения) зависят не от первичного ключа, а от атрибутовсоответственно Номер паспорта и Специальность.

Но если мы выделим их вотдельное отношение, то получим связи типа 1:1. Следовательно, здесь декомпозициянецелесообразна.4НФ. Отношение АДРЕСА-ТЕЛЕФОНЫ нарушают 4НФ, т.к. не всякий телефонпривязан к конкретному адресу (т.е. мы имеем две многозначных зависимости в одномотношении). Но выделять Телефоны в отдельное отношение не стоит, т.к. эти сведенияносят справочный характер и не требуется их автоматическая обработка.Отношения, полученные после нормализации, приведены в табл. 5-14.29Таблица 5.

Схема отношения ОТДЕЛЫ (Departs)Содержание поляАббревиатура отделаНазвание отделаИмя поляD_IDD_NAMEТип, длинаС(10)V(100)Примечанияпервичный ключобязательное полеТаблица 6. Схема отношения КОМНАТЫ (Rooms)Содержание поляОтделНомер комнатыТелефоныИмя поляR_DEPARTR_ROOMR_PHONEТип, длинаV(12)N(4)V(20)Примечаниявнешний ключ (к Departs)составной уникальныйключТаблица 7. Схема отношения ДОЛЖНОСТИ (Posts)Содержание поляНазвание должностиОкладИмя поляP_POSTP_SALТип, длинаV(30)N(8,2)Примечанияпервичный ключобязательное поле,12130 руб.>Таблица 8. Схема отношения СОТРУДНИКИ (Employees)Содержание поляИдентификаторсотрудникаФамилияИмя, отчествоДата рожденияПолСерия и номер паспортаИмя поляE_IDТип, длинаN(4)E_NAMEE_LNAMEE_BORNE_SEXE_PASPV(25)V(30)DC(1)C(10)Когда выдан паспортКем выдан паспортИНННомер пенсионногострахового свидетельстваОтделE_DATEE_PASPE_INNE_PENSDV(50)С(12)С(14)E_DEPARTV(12)ДолжностьНомер комнатыРабочий телефоныЛогинE_POSTE_ROOME_PHONEE_LOGINV(30)N(4)V(20)V(30)Примечаниясуррогатныйпервичный ключобязательное полеобязательное полеобязательное полеобязательное полеобязательное уникальноеполеобязательное полеобязательное полеобязательное полеобязательное уникальноеполевнешнийключ(кDeparts)внешний ключ (к Posts)составной внешний ключ(к Rooms)Таблица 9.

Схема отношения ОБРАЗОВАНИЕ (Edu)Содержание поляИдентификаторсотрудникаВид образованияСпециальностьНомер дипломаГод окончания учебногозаведенияИмя поляU_IDТип, длинаN(4)U_TYPEU_SPECU_DIPLOMU_YEARV(20)V(40)V(15)N(4)30ПримечаниявнешнийключEmployees)обязательное полеобязательное поле(кТаблица 10. Схема отношения АДРЕСА-ТЕЛЕФОНЫ (AdrTel)Содержание поляИдентификаторсотрудникаАдресТелефонИмя поляA_IDТип, длинаN(4)A_ADDRA_PHONEV(50)V(30)ПримечаниявнешнийключEmployees)обязательное поле(кТаблицы ОБРАЗОВАНИЕ и АДРЕСА-ТЕЛЕФОНЫ не имеют потенциальныхключей, но мы не будем вводить суррогатные первичные ключи, т.к. на эти таблицыникто не ссылается.Таблица 11.