Преимущества нормализации
В вышеприведенном примере рассматривалась нормализация исходной таблицы, представленной на рисунке 3. В этой таблице определяются типы коммуникаций в виде массива значений, что приводит к практической невозможности отыскать содержимое, например, поля «Факс». Кроме того, при изменении контактного телефона или появлении нового типа коммуникации, например, почтового адреса, редактирование данных оказывается достаточно сложным. Первая нормальная форма значительно улучшает ситуацию и повышает гибкость, организуя более совершенную схему хранения данных о номерах телефонов, факсов, адресах электронной почты и т.д.
В изображенной на рисунке 4, приведенной к первой, но не ко второй нормальной форме, обработка данных об адресатах затруднена, поскольку информация многократно повторяется, Если в БД имеется 10 телефонных номеров и фамилия повторяется 10 раз, это приводит к напрасному расходованию дискового пространства, а также к потере времени при изменении 10 вхождений в случае перемены фамилии адресата. Очевидно, подобная ситуация нуждается в дальнейшей оптимизации.
Изображенные на рисунке 5 таблицы приведены к первой и второй нормальным формам, поскольку каждый столбец зависит от полного ключа, но не выполняет требования третьей нормальной формы. Переместив данные и достигнув третьей нормальной формы, получаем максимально возможную гибкость модели данных, поскольку теперь данные о типах коммуникаций содержаться в отдельной таблице (рисунок 6).
Нормализация отношений позволяет сократить дублирование данных, но появление новых отношений порождает проблему поддержки семантической целостности данных.
Правила обеспечения целостности данных.При нормализации таблиц необходимо принимать во внимании правила обеспечения целостности данных. Большинство задаваемых правил определяются установленными отношениями. Кроме того, с помощью отношений можно организовать каскадное изменение и удаление данных. Установка каскадного изменения обеспечивает следующее: при изменении значения первичного ключа в таблице «один», изменение распространяется на все таблицы «многие». Например, имеется таблица подстановки, содержащая названия штатов. Значение «New York» представлено значением «NY» в поле первичного ключа. Представим ситуацию, когда город Нью-Йорк преобразуется в самостоятельный штат, при этом формируется его собственное сокращение название штата. При изменении значения «NY» на «NYI» (либо на нечто более описательное), значение «NY» во всех дочерних таблицах меняется на «NYI». Если каскадное обновление не установлено, потребуется в таблицу подстановки внести значение «NYI», изменить все записи в дочерней таблице и затем удалить из таблицы подстановки запись «NY». Установка каскадного удаления обеспечивает следующее: при удалении записи из таблицы «один» удаляются также все записи из таблицы «многие». Это может оказаться как преимуществом, так и недостатком. При удалении информации о заказчике в режиме каскадного удаления удаляются все его счета в таблице «многие». Если такой режим не установлен, удаление записей в таблице «один» не разрешается до тех пор, пока в таблице «многие» не будут удалены все связанные записи, чтобы в ней не осталось записей с неопределенным значением внешнего ключа.
Пример проектирования реляционной базы данных
В качестве примера возьмем базу данных компании, которая занимается издательской деятельностью.
Инфологическое проектирование
Анализ предметной области
База данных создаётся для информационного обслуживания редакторов, менеджеров и других сотрудников компании. БД должна содержать данные о сотрудниках компании, книгах, авторах, финансовом состоянии компании и предоставлять возможность получать разнообразные отчёты.
В соответствии с предметной областью система строится с учётом следующих особенностей:
1) каждая книга издаётся в рамках контракта;
2) книга может быть написана несколькими авторами;
3) контракт подписывается одним менеджером и всеми авторами книги;
4) каждый автор может написать несколько книг (по разным контрактам);
5) порядок, в котором авторы указаны на обложке, влияет на размер гонорара;
6) если сотрудник является редактором, то он может работать одновременно над несколькими книгами;
7) у каждой книги может быть несколько редакторов, один из них – ответственный редактор;
8) каждый заказ оформляется на одного заказчика;
9) в заказе на покупку может быть перечислено несколько книг.
Выделим базовые сущности этой предметной области:
1) Сотрудники компании. Атрибуты сотрудников – ФИО, табельный номер, пол, дата рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для редакторов необходимо хранить сведения о редактируемых книгах; для менеджеров – сведения о подписанных контрактах.
2) Авторы. Атрибуты авторов – ФИО, ИНН (индивидуальный номер налогоплательщика), паспортные данные, домашний адрес, телефоны. Для авторов необходимо хранить сведения о написанных книгах.
3) Книги. Атрибуты книги – авторы, название, тираж, дата выхода, цена одного экземпляра, общие затраты на издание, авторский гонорар.
Контракты будем рассматривать как связь между авторами, книгами и менеджерами. Атрибуты контракта – номер, дата подписания и участники.
Для отражения финансового положения компании в системе нужно учитывать заказы на книги. Для заказа необходимо хранить номер заказа, заказчика, адрес заказчика, дату поступления заказа, дату его выполнения, список заказанных книг с указанием количества экземпляров.
ER–диаграмма издательской компании приведена на рисунке 6 (базовые сущности на рисунках выделены полужирным шрифтом).
Рисунок 7 ER–диаграмма издательской компании