Вторая нормальная форма (2НФ).

Отношение находится во 2НФ, если оно приведено к 1НФ и каждый неключевой атрибут функционально полно зависит от составного ключа.

Для того чтобы привести отношение ко 2НФ, нужно:

• построить его проекцию, исключив атрибуты, которые не находятся в функционально полной зависимости от составного ключа;

• построить дополнительно одну или несколько проекций на часть составного ключа и атрибуты, функционально зависящие от этой части ключа.

Рассмотрим понятие транзитивной зависимости. Пусть X, Y, Z – атрибуты некоторого отношения. При этом X Y и Y Z, но обратное соответствие отсутствует, т.е. Z не зависит от Y или Y не зависит от X. Тогда говорят, что Z транзитивно зависит от X
(XZ).

Третья нормальная форма (3НФ).

Отношение находится в 3НФ, если оно находится во 2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.

Для того чтобы привести отношение к 3НФ, нужно:

• построить его проекцию, исключив транзитивно зависящие от ключа атрибуты;

• построить дополнительно одну или несколько проекций на детерминанты исходного отношения и атрибуты, функционально зависящие от них.

Введём понятие многозначной зависимости. Многозначная зависимость существует, если заданным значениям атрибута X соответствует множество, состоящее из нуля (или более) значений атрибута Y (X–>>Y).

Различают тривиальные и нетривиальные многозначные зависимости. Тривиальной называется такая многозначная зависимость X–>>Y, для которой Y X или X U Y = R, где R – рассматриваемое отношение. Тривиальная многозначная зависимость не нарушает 4НФ. Если хотя бы одно из двух этих условий не выполняется, то такая зависимость называется нетривиальной.

Четвертая нормальная форма (4НФ).

Отношение находится в 4НФ, если оно находится в 3НФ и в нём отсутствуют нетривиальные многозначные зависимости.

Для того чтобы привести отношение к 4НФ, нужно построить две или более проекции исходного отношения, каждая из которых содержит ключ и одну из многозначных зависимостей.

Нормализация отношений позволяет сократить дублирование данных, но появление новых отношений порождает проблему поддержки семантической целостности данных.

ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БАЗ ДАННЫХ

Методические указания к курсовому проектированию по курсу "Базы данных". Часть II

СОДЕРЖАНИЕ

1. Инфологическое проектирование

1.1. Анализ предметной области

1.2. Анализ информационных задач и круга пользователей системы

2. Выбор СУБД и других программных средств

2.1. Определение требований к операционной обстановке

3. Даталогическое проектирование реляционной БД

3.1. Преобразование ER–диаграммы в схему базы данных

3.2. Составление реляционных отношений

3.3. Нормализация полученных отношений (до 3НФ)

3.3. Определение дополнительных ограничений целостности

---

3.4. Описание групп пользователей и прав доступа

4. Физическое проектирование БД

5. Реализация пользовательских запросов

В качестве примера возьмем базу данных компании, которая занимается издательской деятельностью.

1. Инфологическое проектирование

1.1. Анализ предметной области

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

В соответствии с предметной областью система строится с учётом следующих особенностей:

• каждая книга издаётся в рамках контракта;

• книга может быть написана несколькими авторами;

• контракт подписывается одним менеджером и всеми авторами книги;

• каждый автор может написать несколько книг (по разным контрактам);

• порядок, в котором авторы указаны на обложке, влияет на размер гонорара;

• если сотрудник является редактором, то он может работать одновременно над несколькими книгами;

• у каждой книги может быть несколько редакторов, один из них – ответственный редактор;

• каждый заказ оформляется на одного заказчика;

• в заказе на покупку может быть перечислено несколько книг.

Выделим базовые сущности этой предметной области:

• Сотрудники компании. Атрибуты сотрудников – ФИО, табельный номер, пол, дата рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для редакторов необходимо хранить сведения о редактируемых книгах; для менеджеров – сведения о подписанных контрактах.

• Авторы. Атрибуты авторов – ФИО, ИНН (индивидуальный номер налогоплательщика), паспортные данные, домашний адрес, телефоны. Для авторов необходимо хранить сведения о написанных книгах.

• Книги. Атрибуты книги – авторы, название, тираж, дата выхода, цена одного экземпляра, общие затраты на издание, авторский гонорар.

Контракты будем рассматривать как связь между авторами, книгами и менеджерами. Атрибуты контракта – номер, дата подписания и участники.

Для отражения финансового положения компании в системе нужно учитывать заказы на книги. Для заказа необходимо хранить номер заказа, заказчика, адрес заказчика, дату поступления заказа, дату его выполнения, список заказанных книг с указанием количества экземпляров.

ER–диаграмма издательской компании приведена на рис. 3 (базовые сущности на рисунках выделены полужирным шрифтом).

Рис.3. ER–диаграмма издательской компании

1.2. Анализ информационных задач и круга пользователей системы

Система создаётся для обслуживания следующих групп пользователей:

• администрация (дирекция);

• менеджеры;

• редакторы;

• сотрудники компании, обслуживающие заказы.

Определим границы информационной поддержки пользователей:

---

1) Функциональные возможности:

• ведение БД (запись, чтение, модификация, удаление в архив);

• обеспечение логической непротиворечивости БД;

• обеспечение защиты данных от несанкционированного или случайного доступа (определение прав доступа);

• реализация наиболее часто встречающихся запросов в готовом виде;

• предоставление возможности сформировать произвольный запрос на языке манипулирования данными.

2) Готовые запросы:

• получение списка всех текущих проектов (книг, находящихся в печати и в продаже);

• получение списка редакторов, работающих над книгами;

• получение полной информации о книге (проекте);

• получение сведений о конкретном авторе (с перечнем всех книг);

• получение информации о продажах (по одному или по всем проектам);

• определение общей прибыли от продаж по текущим проектам;

• определение размера гонорара автора по конкретному проекту.

2. Выбор СУБД и других программных средств

Анализ информационных задач показывает, что для реализации требуемых функций подходят почти все СУБД для ПЭВМ (FoxPro, Clipper, MS Access и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными.

Объём внешней и оперативной памяти, требующийся для функционирования СУБД, обычно указывается в сопроводительной документации.

Для того чтобы в учебном примере не привязываться к конкретной СУБД, выполним описание логической схемы БД на SQL-92.

2.1. Определение требований к операционной обстановке

Для выполнения этого этапа необходимо знать (хотя бы ориентировочно) объём работы издательства (т.е. количество книг, авторов и заказчиков), а также иметь представление о характере и интенсивности запросов.

Объём внешней памяти, необходимый для функционирования системы, складывается из двух составляющих: память, занимаемая модулями СУБД (ядро, утилиты, вспомогательные программы), и память, отводимая под данные (М_Д). Наиболее существенным обычно является М_Д. Объём памяти М_Д, требуемый для хранения данных, можно приблизительно оценить по формуле

,

где l_i – длина записи в i-й таблице (в байтах), N_i – примерное (максимально возможное) количество записей в i-й таблице, N_a – количество записей в архиве i-й таблицы. Коэффициент 2 перед суммой нужен для того, чтобы выделить память для хранения индексов, промежуточных данных, для выполнения объёмных операций (например, сортировки) и т.п.

Посчитаем приблизительно, какой объём внешней памяти потребуется для хранения данных. Примем ориентировочно, что:

• одновременно осуществляется около пятидесяти проектов, работа над проектом продолжается в среднем два месяца (по 0,3К);

• в компании работает 100 сотрудников (по 0,2К на каждого сотрудника);

• издательство сотрудничает с тридцатью авторами (по 0,2К);

• в день обслуживается порядка двадцати заявок (по 0,1К);

• устаревшие данные переводятся в архив.

---

Тогда объём памяти для хранения данных за первый год примерно составит:

M_c = 2(100*0,2+6(50*0,3)+30*0,2+250(20*0,1)) = 1232 К  1,2 М,

где 250 – количество рабочих дней в году, а 12 мес./2 мес. = 6. Объём памяти будет увеличиваться ежегодно на столько же при сохранении объёма работы.

Объём памяти, занимаемый программными модулями пользователя, обычно невелик по сравнению с объёмом самих данных, поэтому может не учитываться. Требуемый объём оперативной памяти определяется на основании анализа интенсивности запросов и объёма результирующих данных.

3 Даталогическое проектирование реляционной БД

3.1. Преобразование ER–диаграммы в схему базы данных

База данных создаётся на основании схемы базы данных. Для преобразования ER–диаграммы в схему БД приведём уточнённую ER–диаграмму, содержащая атрибуты сущностей (рис. 4).

Рис.4. Уточнённая ER–диаграмма издательской компании

Примечание: многозначные атрибуты на рисунке выделены подчеркиванием.

Преобразование ER–диаграммы в схему БД выполняется путем сопоставления каждой сущности и каждой связи, имеющей атрибуты, отношения (таблицы БД). Будем использовать обозначения, представленные на рис. 5.

Рис.5. Обозначения, используемые на схеме базы данных

Полученная схема реляционной базы данных (РБД) приведена на рис. 6.

Рис.6. Схема РБД, полученная из ER–диаграммы издательской компании

На схеме (рис. 6) есть связь типа 1:1 – обязательная связь между КНИГАМИ и КОНТРАКТАМИ. Такие отношения следует объединять в одно. Дополнительный эффект от объединения этих отношений – слияние связей авторы–контракты и авторы–книги: ведь в нашем случае контракт заключается именно для написания книги.

Примечание: исключение для связи типа 1:1 составляют ситуации, когда для увеличения производительности системы в отдельную таблицу выделяются редко используемые данные большого объёма.

Связь типа 1:n (один-ко-многим) между отношениями реализуется через внешний ключ. Ключ вводится для того отношения, к которому осуществляется множественная связь (КНИГИ).

Связь редактировать между отношениями КНИГИ и СОТРУДНИКИ принадлежит к типу n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение, которое является соединением первичных ключей соответствующих отношений.

Бинарная связь между отношениями не может быть обязательной для обоих отношений. После объединения сущностей КНИГИ и КОНТРАКТЫ остаётся три связи, обязательные для всех участников: между авторами и книгами и между заказами и строками заказов. Такой тип связи означает, что, например, прежде чем добавить новый заказ в отношение ЗАКАЗЫ, нужно добавить новую строку в отношение СТРОКИ ЗАКАЗА, и наоборот. Поэтому для такой связи необходимо снять с одной стороны условие обязательности. Так как все эти связи будут реализованы с помощью внешнего ключа, снимем условие обязательности связей для отношений, содержащих первичные ключи.

---

Уточнённая схема РБД издательской компании приведена на рис. 7.

Рис.7. Уточнённая схема РБД издательской компании

Схема на рис. 7 содержит цикл "сотрудники–книги–сотрудники". Цикл допустим только в том случае, если связи, входящие в него, независимы друг от друга. Примем для нашей ПО, что ответственный редактор книги может являться также просто редактором этой же книги или не входить в число редакторов. При этом цикл не приводит к нарушению логической целостности данных.

Примечание. Существует несколько подходов для разрешения ситуаций, в которых связи, входящие в цикл, зависят друг от друга. Рассмотрим пример цикла "отделы–проекты–сотрудники–отделы" (рис. 8,а). Будем считать, что в выполнении проекта могут участвовать только сотрудники, работающие в том же отделе, к которому относится проект. При циклической схеме СУБД не сможет гарантировать логическую целостность данных без использования дополнительных средств.

Один из способов – разорвать цикл, исключив одну из связей (рис. 8,б) или введя промежуточное отношение (рис. 8,в). В нашем случае можно было бы разорвать связь "сотрудники–проекты", если бы каждый сотрудник участвовал во всех проектах своего отдела. Промежуточное отношение можно было бы использовать, если бы существовала общая связь между сущностями, входящими в цикл. Например, если бы каждый сотрудник заключал договор с отделом на выполнение работ в рамках проекта. Тогда сущность ДОГОВОРЫ отражала бы связь между отделами, сотрудниками и проектами.

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

В тех ситуациях, когда все эти способы не пригодны, логическая целостность контролируется программно или вручную.

Рис.8. Некоторые способы разрешения циклов в схеме базы данных

3.2. Составление реляционных отношений

Каждое реляционное отношение соответствует одной сущности (объекту ПО) и в него вносятся все атрибуты сущности. Для каждого отношения необходимо определить первичный ключ и внешние ключи (если они есть). В том случае, если базовое отношение не имеет потенциальных ключей, вводится суррогатный первичный ключ, который не несёт смысловой нагрузки и служит только для идентификации записей (например, счетчик).

Примечание: суррогатный первичный ключ также может вводиться в тех случаях, когда потенциальный ключ имеет большой размер (например, длинная символьная строка) или является составным (не менее трёх атрибутов).

---

Смотрите также файлы

• УМК_Практические работы.pdf

• Требования к СУБД.docx

• Теория информации КР 2017.pdf

• Темы.docx

• Темы ВКР по ПИ.doc