Проект реляционной базы данных Интернет-магазина
Проблема логического проектирования реляционной базы данных заключается в выборе рационального количества отношений и их атрибутов. Вообще говоря, задача имеет множество решений, однако нужно построить такую совокупность отношений и так выбрать атрибуты, чтобы при этом выполнялись следующие основные требования:
· свести к минимуму избыточность хранения информации;
· обеспечить целостность базы данных при выполнении операций модификации данных;
· минимизировать возможные изменения в наборе отношений при добавлении новых атрибутов.
Преобразование исходного набора отношений в конечный набор с лучшими характеристиками называется процессом нормализации, или методом нормальных форм. Нормализация заключается в последовательном переводе отношений из первой нормальной формы в нормальные формы более высокого порядка по определенным правилам. Обычно ограничиваются приведением отношений к третьей нормальной форме. В результате одна или несколько исходных таблиц преобразуется в значительно большее их количество.
На практике такой теоретический подход применяется в комбинации с предварительным построением ER-диаграмм (диаграмм сущность-связь), соответствующих концептуальной модели базы данных. Как мы видели, с точки зрения реляционного подхода каждая сущность ER-диаграммы может быть представлена в виде одной таблицы, имеющей один или группу идентифицирующих отдельный экземпляр сущности атрибутов. Для преобразования сущностей в совокупность отношений требуется выполнить следующие действия:
· создать для каждой сущности по одной таблице. Для каждой сущности, которая выступает во взаимоотношениях с другими сущностями как «один-ко-многим» или «один-к-одному», указать один столбец в качестве первичного ключа;
· для каждой сущности, которая выступает как «многие-к-одному» во взаимоотношениях с хотя бы с одной сущностью, указать в качестве внешних ключей первичные ключи каждой из родительских сущностей.
· задать первичный ключ для каждой сущности, выступающей во взаимоотношениях как «многие-к-одному».
Применим этот подход к построению логической модели базы данных Интернет-магазина.
Концептуальная модель (рис. 7.20 и табл.7.1) дает пять сущностей, из которых три – КНИГА, ПОКУПАТЕЛЬ и КУРЬЕР – выступают во взаимоотношениях только как «один-ко-многим». Первичные ключи для них уже определены: это ISBN-код книги, Код покупателя и Код курьера соответственно.
Сущность ЗАКАЗ выступает во взаимоотношении «многие-к-одному» с сущностями ПОКУПАТЕЛЬ и КУРЬЕР и во взаимоотношении один-ко-многим с сущностью КОРЗИНА ЗАКАЗА. Атрибуты Код покупателя и Код курьера сущности ЗАКАЗ являются первичными ключами сущностей ПОКУПАТЕЛЬ и КУРЬЕР и поэтому являются внешними ключами отношения ЗАКАЗ.
Первичным ключом отношения ЗАКАЗ является Код заказа.
Сущность КОРЗИНА ЗАКАЗА выступает во взаимоотношении «многие-к-одному» с сущностями ЗАКАЗ и КНИГА. Атрибуты Код заказаи ISBN-код книги сущности КОРЗИНА ЗАКАЗА являются первичными ключами сущностей ЗАКАЗ и КНИГА и поэтому являются внешними ключами отношения КОРЗИНА ЗАКАЗА.
Первичным ключом отношения КОРЗИНА ЗАКАЗА является совокупность атрибутов Код заказа,ISBN-код книги.
Логическая схема совокупности полученных отношений представлена на рис.7.23. Здесь жирным шрифтом выделены первичные ключи, а курсивом – внешние ключи.
 |
| Рис. 7.23 Логическая схема отношений |
Теперь нужно проверить соответствие отношений третьей нормальной форме. Для нее должны быть выполнены следующие условия:
1. Отсутствуют многозначные атрибуты.
2. Все неключевые атрибуты отношения зависят от полного первичного ключа, а не от какой-то его части.
3. Все неключевые атрибуты отношения зависят только от первичного ключа и не зависят от других неключевых атрибутов.
Многозначность атрибутов снята на этапе концептуального проектирования. Так, мы предположили, что для каждого покупателя будет записан только один телефон, а не несколько. Если желательно вводить несколько телефонов, то нужно создавать новое отношение ТЕЛЕФОН и связывать его с отношением ПОКУПАТЕЛЬ. То же относится и к авторам книг. Все авторы книги будут вводиться одной строкой, и, следовательно, анализ продаж книг по отдельным авторам будет затруднителен. Если же такой анализ необходим, следует включить в базу данных дополнительное отношение АВТОРЫ. Для простоты картины мы этого делать не будем.
Неполная зависимость атрибутов. На этапе концептуального проектирования мы также устранили неполную зависимость атрибутов от первичного ключа для сущности ЗАКАЗ. Если в описание заказа включить заказанные книги, то первичным ключом будет совокупность атрибутов Код заказа, ISBN-кода книги. Тогда такие атрибуты как Дата заказа, Покупатель зависят только от кода заказа, а атрибут Количество экземпляров книги в заказе определяется и заказом и ISBN-кодом книги, то есть является атрибутом новой сущности КОРЗИНА ЗАКАЗА (см.рис. 7.18).
Зависимость от неключевых атрибутов. Рассмотрим отношение ЗАКАЗ из нашего примера. В нем присутствуют неключевые атрибуты Тип доставки и Цена доставки, причем цена доставки определяется типом доставки. Здесь мы имеем избыточность данных, так как цена доставки хранится для каждого заказа, хотя достаточно было ввести ее один раз при описании доставки данного типа. Кроме того, имеется так называемая аномалия модификации: при изменении в записи заказа значения атрибута Тип доставки всякий раз нужно изменять значение цены доставки.
Для устранения этих аномалий нужно устранить функциональную зависимость между неключевыми атрибутами. В результате исходное отношение разбивается на два, представленные в табл. 7.4, - ЗАКАЗ и ЦЕНА ДОСТАВКИ. Здесь для отношения ЗАКАЗ приведены не все неключевые атрибуты. Дублирование данных по ценам доставки и аномалия корректировки вида доставки здесь устранены. Цены для каждого вида доставки вводятся и корректируются независимо от конкретного заказа.
| Таблица 7.4 Результат приведения отношения ЗАКАЗ к третьей нормальной форме | ||||||||
| ЗАКАЗ | ЦЕНА ДОСТАВКИ | |||||||
| Код заказа | Код покупа-теля | Дата доставки | Тип доставки | Адрес доставки | Код курьера | Тип доставки | Цена доставки | |
| 08/06/2001 | Курьером по Москве | ул. Зеленая, 2-4 | Курьером по Москве | |||||
| 08/06/2001 | Курьером по Москве | ул. Новая, 11-5 | Курьером по области | |||||
| 09/06/2001 | Курьером по области | Зеленоград, ул. Новаторов, 3 | Курьером по С.-Пб. | |||||
| 09/06/2001 | Курьером по С.-Пб. | Московский пр-т 12-125 | ||||||
| 09/06/2001 | Курьером по Москве | Пл. Ильича,15-68 | ||||||
Таким образом, мы получили реляционную логическую модель базы данных Интернет-магазина из семи отношений, приведенных к третьей нормальной форме:
ПОКУПАТЕЛЬ (Код покупателя, Организация, Фамилия, Имя, Отчество, Адрес электронной почты, Почтовый адрес)
ПОКУПАТЕЛЬ-ТЕЛЕФОН (Код покупателя, Телефон)
КНИГА (ISBN-код книги, Раздел литературы, Название, Авторы, Издательство, Год издания, Цена)
ЗАКАЗ (Код заказа, Код покупателя, Форма оплаты, Дата заказа, Дата доставки, Дата исполнения, Тип доставки, Код курьера, Адрес доставки, Примечание)
ЦЕНА ДОСТАВКИ (Тип доставки, Цена доставки)
КОРЗИНА ЗАКАЗА (Код заказа, ISBN-код книги, Количество экземпляров в заказе)
КУРЬЕР (Код курьера, Фамилия, Имя, Отчество, Дата рождения, Дата приема на работу, Рабочая смена)
Здесь ключевые атрибуты выделены жирным шрифтом, а внешние ключи – курсивом.
Связи между отношениями и соответствующие первичные и внешние ключи представлены на рис. 7.24.
 |
| Рис. 7.24. Логическая модель базы данных Интернет-магазина |
Физическое проектирование
Главными вопросами физического проектирования являются оптимизация времени выполнения основных запросов к базе данных и обеспечение безопасности данных.
Как уже было отмечено, логическая модель базы данных определяется только выбранным типом модели данных и совокупностью сущностей и их атрибутов и не зависит от конкретной системы управления базами данных, в которой она будет реализовываться. Напротив, построение физической модели основывается на структурах, типах данных и функциях, существующих в выбранной СУБД.
Физическая модель содержит полную информацию, необходимую для реализации конкретной базы данных. В этом разделе мы коснемся общих вопросов функционирования реляционных СУБД и построения физической модели базы данных.
При реализации физической модели в СУБД отношение обычно называют таблицей, атрибут отношения – полем или столбцом таблицы, кортеж отношения – записью или строкой.
Словарь данных
Реляционные СУБД хранят информацию о структуре базы данных в специальной области, называемой словарем данных. Словарь данных представляет собой набор таблиц той же структуры, что и таблицы базы данных. Однако, в отличие от основных таблиц, хранящих только данные, словарь данных содержит только описания данных и связей между таблицами.
В словаре данных обычно отражены следующие сведения:
– имена таблиц, количество столбцов в каждой таблице, описание столбцов: тип данных, размер поля, является ли столбец ключевым;
– ограничения целостности, наложенные на связанные таблицы;
– сведения о доступе отдельных пользователей к таблицам на выполнение определенных операций с таблицами: выборка, модификация, удаление (безопасность данных);
– описание пользовательских представлений (запросов) и доменов, определяемых пользователями.
При любом пользовательском обращении к данным СУБД обращается к словарю данных, определяет наличие затребованных элементов (таблиц, записей, полей), проверяет права доступа и соответствие запрошенной операции ограничениям целостности.
Таким образом, управление базой данных в реляционных СУБД осуществляется через словарь данных.
Индексирование
В реляционных базах данных записи в таблицах хранятся в той последовательности, в которой они были введены, что отражает требование отсутствия упорядоченности кортежей в отношении. При такой организации для поиска нужной записи необходимо просмотреть большую часть таблицы. Так как в информационных системах таблицы базы данных могут содержать десятки и сотни тысяч строк, неупорядоченность записей может привести к недопустимо большому времени выполнения запросов.
Для повышения производительности реляционные СУБД используют специальные объекты, называемые индексами. Индекс содержит набор записей из двух элементов: {значение ключевого поля; указатель на соответствующую запись в таблице}. Индекс упорядочен по значению ключевого поля, что позволяет быстро находить нужные значения. Для оптимизации поиска в индексах используются специальные алгоритмы. Упорядоченный индекс можно просматривать во много раз быстрее, чем саму неупорядоченную таблицу. Фактически индексная структура является «оглавлением» таблицы. В таблице 7.5. приведен индекс к таблице КНИГА и записи самой таблицы.
| Таблица 7.5 Схема индексирования | ||||
| Индекс | Таблица КНИГА | |||
| ISBN-код | ISBN-код | Автор | Наименование | |
 5-272-00046-3 | 966-03-1257-1 | Глушаков С.В. | Базы данных. Учебный курс | |
| 5-279-02346-9 | 5-272-00046-3 | Мелихова Л. | Энциклопедия Интернет | |
| … | … | |||
| 966-03-1257-1 | 5-279-02346-9 | Попов В. М. | Бизнес-план инвестиционного проекта |
При процедуре поиска нужной записи таблицы по ключевому полю вначале просматривается ее индекс, находится нужное значение ключа, определяется по указателю адрес нужной записи и только затем считывается запись.
СУБД автоматически создает индексы для первичных ключей таблиц. В качестве индексного ключа берется столбец или совокупность столбцов первичного ключа. При вводе новой строки происходит проверка уникальности значения первичного ключа не по записям таблицы, а в соответствующем индексе, что также ускоряет работу системы.
Так как обработка целых чисел производится значительно быстрее, чем текстовых данных, целесообразно в качестве ключевых полей (атрибутов) применять числовые коды, например, код товара, номер заказа и т. п.
Если в информационной системе предполагается часто выполнять поиск по полям, не входящим в первичный ключ, можно задать дополнительные индексы для этих полей. Например, для таблицы КНИГА можно провести индексирование записей по полю Раздел литературы. В этом случае ускоряется выполнение всех запросов, связанных с поиском книг определенного раздела.
При любой модификации, добавлении или удалении записей СУБД автоматически обновляет как базовую таблицу, так и все индексы. Это замедляет операции, связанные с изменением таблиц. Чем больше индексов будет создано для таблицы, тем медленнее будут выполняться операции ее обновления. Стремясь к обеспечению быстрого доступа к данным, можно существенно замедлить работу системы при модификации данных. Как правило, индексы всегда создаются для внешних ключей и для полей, по которым часто проводится поиск.
В нашем примере для таблицы ЗАКАЗ СУБД автоматически создаст индекс для первичного ключа Код заказа. Также целесообразно создать индексы для внешних ключей Код покупателя, Тип доставки, Код курьера. Если известно, что будет часто проводиться поиск заказов по дате, то целесообразно построить дополнительный индекс по полю Дата заказа.
Разделение таблиц
Разделение означает разбиение таблицы на части в целях ускорения работы системы. Разделение таблиц может быть горизонтальным или вертикальным.
При горизонтальном разделении таблица делится на две или более таблиц с идентичной структурой. Записи исходной таблицы распределяются между новыми таблицами в соответствии с частотой обращения к ним.
Например, при длительной эксплуатации информационной системы Интернет-магазина в таблицах ЗАКАЗ и КОРЗИНА ЗАКАЗА будет накоплено огромное число записей, что значительно ухудшит производительность системы. Конечно, информация обо всех заказах важна с точки зрения анализа работы нашего магазина и ведения денежной и другой документации и должна храниться длительное время. Однако для текущей работы отдела заказов достаточно хранить информацию лишь о невыполненных или недавно выполненных заказах.
В этом случае целесообразно создать новые связанные между собой таблицы АРХИВ ЗАКАЗОВ и АРХИВ КОРЗИН ЗАКАЗОВ, в которые нужно переписать все записи о заказах, произведенных месяц назад или ранее, кроме записей о невыполненных заказах. Основная работа будет вестись с таблицами ЗАКАЗ и КОРЗИНА ЗАКАЗА, содержащими относительно небольшой объем записей. Операцию перезаписи в архивные таблицы надо будет проводить ежемесячно.
При проведении горизонтального разделения таблицы следует изменить запросы, относящиеся ко всем записям. Теперь они должны включать операцию объединения двух таблиц. Например, при установлении количества заказов, доставленных по Москве за последний год, поиск должен проводиться по временно создаваемой таблице, полученной в результате объединения таблиц ЗАКАЗ и АРХИВ ЗАКАЗОВ. Такие запросы будут выполняться значительно медленнее, чем поиск в каждой таблице в отдельности. Разделение таблиц оправдано, если запросы к записям архивных таблиц будут выполняться значительно реже, чем к записям основных таблиц.
При вертикальном разделении таблицы вместо исходной таблицы создаются две или более новых таблиц, каждая из которых содержит первичный ключ исходной таблицы и ряд выбранных столбцов. Такое разделение проводят, если обращение к некоторым полям записей таблицы происходит значительно реже, чем к остальным.
Например, таблицу, содержащую информацию о сотрудниках:
СОТРУДНИК (Табельный номер, Фамилия, Имя, Отчество, Должность, Отдел, Дата рождения, Дата приема на работу, Образование, Специальность, Семейное положение, Количество детей)
можно разделить на две:
СОТРУДНИК (Табельный номер, Фамилия, Имя, Отчество, Должность, Отдел);
СОТРУДНИК-АНКЕТА (Табельный номер, Дата рождения, Дата приема на работу, Образование, Специальность, Семейное положение, Количество детей).
Первая таблица содержит существенные сведения, к которым происходит частое обращение при функционировании некоторой системы управления, вторая – редко запрашиваемые анкетные данные, интересующие только менеджера по кадрам.
Таблицы при вертикальном разделении связаны связью «один-к-одному» по полю первичного ключа исходной таблицы. При запросе полных сведений происходит объединение таблиц по первичному ключу, что замедляет выборку.