Привожу полное решение задачи о построении информационно-логической модели. Но в Access её пихать напрямую нельзя (всё-таки это не реляционная БД). К сожалению представленное задание не совсем корректно. Поэтому здесь и далее считается что нам изначальна даны атрибуты заголовка отношения, а не какая-либо иная сущность.
Так как мы приняли, что изначально имеем отношение, то все его атрибуты будут по определению атомарны и отношение находиться в первой нормальной форме (1NF).
Для перехода ко 2NF необходимо, чтобы атрибуты минимально зависели от первичных ключей. Однако сейчас часть атрибутов относятся к клиенту, а часть к заказу.
Опять же в задании ничего не указано насчёт уникальности атрибутов. Поэтому в дидактических целях примем, что атрибуты домена «Код клиента» уникальны, а остальные – нет.
Тогда при декомпозиции получается 2 отношения: Клиент{Код клиента, организация, имя, фамилия, отчество, город, район, область, страна, почтовый индекс, домашний телефон, рабочий телефон, факс, мобильный телефон, место работы, отдел, должность} и Заказ{Код клиента, дата заказа, форма оплаты}.
В данном случае домен «Код клиента» включён в состав отношения «Заказ», чтобы проведённая декомпозиция была бы декомпозицией без потерь согласно Хиту.
Для получения 3NF необходимо, чтобы любой атрибут отношения находился бы в прямой зависимости от ключа. Однако в отношении «Клиент» часть информации напрямую зависит от кода клиента, а часть характеризует место работы и организацию в которой работает клиент, а так же адрес клиента и организации. Для упрощения декомпозиции (чтобы иметь только связи «один ко многим») будем считать, что каждый клиент может работать только на 1 месте работы, место работы может быть только в 1 организации и клиент и организация могут иметь только 1 адрес.
Поэтому проведём декомпозицию этого отношения на 4: Клиент{Код клиента, имя, фамилия, отчество, адрес, домашний телефон, рабочий телефон, факс, мобильный телефон, место работы}, Адрес{id адрес, город, район, область, страна, почтовый индекс}, Место работы{организация, id место работы, отдел, должность} и Организация{id организация, организация, адрес}. Заметим, что для производства декомпозиции без потерь были введены суррогатные ключи «id адрес», «id место работы» и «id организация». На них ссылаются домен отношений «Клиент» и «Организация», домен «место работы» отношения «Клиент» и домен «организация» отношения «Место работы» соответственно. Домен «организация» отношения «Организация» содержит названия организаций.
3NF является достаточной при решении большинства практических задач, и процесс проектирования реляционной базы данных, как правило, заканчивается приведением к ней. Однако мы пойдём дальше и покажем приведение к 4NF.
Для приведения к 4NF надо убрать все нетривиальные многозначные зависимости. Такой зависимостью является в данном случае кортеж {домашний телефон, рабочий телефон, факс, мобильный телефон}. Очевидно что это всё номера телефонов, но разных типов. Здесь уже не будем упрощать ситуацию и используем связь «многие ко многим».
Проведём декомпозицию отношения «Клиент»: Клиент{Код клиента, имя, фамилия, отчество, адрес, место работы}, Тип телефона{Код клиента, номер телефона, тип телефона (домашний, рабочий, факс, мобильный)}, Номер телефона{номер телефона}.
Для данной БД 4NF совпадает с 5NF.
Таким образом 4NF отношения будет выглядеть так:
Клиент{Код клиента, имя, фамилия, отчество, адрес, место работы},
Тип телефона{Код клиента, номер телефона, тип телефона (домашний, рабочий, факс, мобильный)},
Номер телефона{номер телефона},
Адрес{id адрес, город, район, область, страна, почтовый индекс},
Место работы{организация, id место работы, отдел, должность},
Организация{id организация, организация, адрес},
Заказ{Код клиента, дата заказа, форма оплаты}.
Кроме того, для сохранения ссылочной целостности БД надо прописать следующие триггеры:
* Удаление кортежа из отношения «Клиент» => удалить все кортежи с атрибутом «Код клиента» совпадающим с атрибутом «Код клиента» отношения «Клиент» из отношений «Тип телефона», «Заказ»
* Удаление кортежа из отношения «Тип телефона» => если в отношении «Тип телефона» больше нет кортежей с таким же атрибутом «номер телефона» как и удаляемый, удалить все кортежи с атрибутом «номер телефона» совпадающим с атрибутом «номер телефона» отношения «Тип телефона» из отношения «Номер телефона».
* Удаление кортежа из отношения «Адрес» => удалить все кортежи с атрибутом «адрес» совпадающим с атрибутом «id адрес» отношения «Адрес» из отношений «Клиент», «Организация».
* Удаление кортежа из отношения «Место работы» => удалить все кортежи с атрибутом «место работы» совпадающим с атрибутом «id место работы» отношения «Место работы» из отношения «Клиент».
* Удаление кортежа из отношения «Организация» => удалить все кортежи с атрибутом «организация» совпадающим с атрибутом «id организация» отношения «Организация» из отношения «Место работы».
* Обновление кортежа из отношения «Тип телефона» => если в отношении «Тип телефона» больше нет кортежей с таким же атрибутом «номер телефона» как и удаляемый, удалить все кортежи с атрибутом «номер телефона» совпадающим с атрибутом «номер телефона» отношения «Тип телефона» из отношения «Номер телефона».
