Проект реляционной базы данных интернет-магазина

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

– свести к минимуму избыточность хранения информации;

– обеспечить целостность БД при выполнении операций модификации данных;

– минимизировать возможные изменения в наборе отношений при добавлении новых атрибутов.

Преобразование исходного набора отношений в конечный набор с лучшими характеристиками называется процессом нормализации, или методом нормальных форм. Нормализация заключается в последовательном переводе отношений из первой нормальной формы в нормальные формы более высокого порядка по определенным правилам. Обычно ограничиваются приведением отношений к третьей нормальной форме. В результате одна или несколько исходных таблиц преобразуются в значительно большее их количество. На практике такой теоретический подход применяется в комбинации с предварительным построением ER-диаграмм (диаграмм «сущность–связь»), соответствующих концептуальной модели базы данных. Как мы видели, с точки зрения реляционного подхода каждая сущность ER-диаграммы может быть представлена в виде одной таблицы, имеющей один или группу идентифицирующих отдельный экземпляр сущности атрибутов. Для преобразования сущностей в совокупность отношений требуется выполнить следующие действия:

– создать по одной таблице для каждой сущности. Для каждой сущности, которая выступает во взаимоотношениях с другими сущностями как «один-ко-многим» или «один-к-одному», указать один столбец в качестве первичного ключа;

– для каждой сущности, которая выступает как «многие-к-одному» во взаимоотношениях с хотя бы одной сущностью, указать в качестве внешних ключей первичные ключи каждой из родительских сущностей;

– задать первичный ключ для каждой сущности, выступающей во взаимоотношениях как «многие-к-одному».

Применим этот подход к построению логической модели базы данных интернет-магазина.

Концептуальная модель (рис. 32 и табл. 1) дает пять сущностей, из которых три – КНИГА, ПОКУПАТЕЛЬ и КУРЬЕР – выступают во взаимоотношениях только как «один-ко-многим». Первичные ключи для них уже определены: это ISBN-код книги, Код покупателя и Код курьера соответственно.

Сущность ЗАКАЗ выступает во взаимоотношении «многие-к-одному» с сущностями ПОКУПАТЕЛЬ и КУРЬЕР и во взаимоотношении «один-ко-многим» с сущностью КОРЗИНА ЗАКАЗА. Атрибуты Код покупателя и Код курьера сущности ЗАКАЗ являются первичными ключами сущностей ПОКУПАТЕЛЬ и КУРЬЕР и поэтому выступают внешними ключами отношения ЗАКАЗ. Первичным ключом отношения ЗАКАЗ является Код заказа. Сущность КОРЗИНА ЗАКАЗА выступает во взаимоотношении «многие-к-одному» с сущностями ЗАКАЗ и КНИГА. Атрибуты Код заказа и ISBN-код книги сущности КОРЗИНА ЗАКАЗА выступают ключами сущностей ЗАКАЗ и КНИГА и считаются ключами отношения КОРЗИНА ЗАКАЗА.

Первичным ключом отношения КОРЗИНА ЗАКАЗА становится совокупность атрибутов Код заказа, ISBN-код книги.

Логическая схема совокупности полученных отношений представлена на рис. 35. Здесь жирным шрифтом выделены первичные ключи, а курсивом – внешние ключи.

Теперь нужно проверить соответствие отношений третьей нормальной форме.

Рис. 35. Логическая схема отношений

Для нее должны быть выполнены следующие условия:

1. Отсутствуют многозначные атрибуты.

2. Все неключевые атрибуты отношения зависят от полного первичного ключа, а не от какой-то его части.

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

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

Неполная зависимость атрибутов. На этапе концептуального проектирования мы также устранили неполную зависимость атрибутов от первичного ключа для сущности ЗАКАЗ. Если в описание заказа включить заказанные книги, то первичным ключом будет совокупность атрибутов Код заказа, ISBN-кода книги. Тогда такие атрибуты, как Дата заказа, Покупатель, зависят только от кода заказа, а атрибут Количество экземпляров книги в заказе определяется и заказом и ISBN-кодом книги, т. е. является атрибутом новой сущности КОРЗИНА ЗАКАЗА.

Зависимость от неключевых атрибутов. Рассмотрим отношение ЗАКАЗ из нашего примера. В нем присутствуют неключевые атрибуты Тип доставки и Цена доставки, причем цена доставки определяется типом доставки. Здесь мы имеем избыточность данных, так как цена доставки хранится для каждого заказа, хотя достаточно было ввести ее один раз при описании доставки данного типа. Кроме того, имеется так называемая аномалия модификации: при изменении в записи заказа значения атрибута Тип доставки всякий раз нужно изменять значение цены доставки.

Для устранения этих аномалий нужно исключить функциональную зависимость между неключевыми атрибутами. В результате исходное отношение разбивается на два, представленные в табл. 4, – ЗАКАЗ и ЦЕНА ДОСТАВКИ. Здесь для отношения ЗАКАЗ приведены не все неключевые атрибуты. Дублирование данных по ценам доставки и аномалия корректировки вида доставки здесь устранены. Цены для каждого вида доставки вводятся и корректируются независимо от конкретного заказа.

Таким образом, мы получили реляционную логическую модель БД интернет-магазина из семи отношений, приведенных к третьей нормальной форме:

ПОКУПАТЕЛЬ (Код покупателя, Организация, Фамилия, Имя, Отчество, Адрес электронной почты, Почтовый адрес)

ПОКУПАТЕЛЬ-ТЕЛЕФОН (Код покупателя, Телефон)

КНИГА (ISBN-код книги, Раздел литературы, Название, Авторы, Издательство, Год издания, Цена)

ЗАКАЗ (Код заказа, Код покупателя, Форма оплаты, Дата заказа, Дата доставки, Дата исполнения, Тип доставки, Код курьера, Адрес доставки, Примечание)

ЦЕНА ДОСТАВКИ (Тип доставки, Цена доставки)

КОРЗИНА ЗАКАЗА (Код заказа, ISBN-код книги, Количество экземпляров в заказе)

КУРЬЕР (Код курьера, Фамилия, Имя, Отчество, Дата рождения, Дата приема на работу, Рабочая смена)

Здесь ключевые атрибуты выделены жирным шрифтом, а внешние ключи – курсивом.

Рис. 36. Логическая модель базы данных интернет-магазина

Связи между отношениями и соответствующие первичные и внешние ключи представлены на рис. 36.