Пример проектирования структуры БД со сложной структурой
Лабораторная работа №3
Проектирование структуры базы данных сосложной структурой.
Цель:Сформировать умения и навыки по проектированию структуры базы данных со сложной структурой с использованием нормализации.
Порядок выполнения работы:
1. Изучить сведения из теории.
2. Согласно индивидуальной темы нормализовать БД.
3. Оформить отчет о проделанной работе.
Сведения из теории:
Нормализация данных в реляционной модели
Один и тот же набор данных в реляционной модели можно представить различными способами. Процесс нормализации данных позволяет решить вопрос о наиболее эффективной структуре данных, обладающей минимальной избыточностью. Это позволяет решить следующие задачи:
a) исключить ненужное повторение данных,
b) обеспечить быстрый доступ к данным,
c) обеспечить целостность данных.
Обычно говорят только об уменьшении избыточности данных и нормализацией называют процесс уменьшения избыточности информации в реляционной БД.
Теория нормализации оперирует с пятью нормальными формами и одной промежуточной, уточняющей третью. На практике же обычно руководствуются только первыми тремя нормальными формами. Процесс проектирования БД с использованием нормальных форм является итерационным и заключается в последовательном применении правил нормальных форм от низшей к высшей. Каждая следующая нормальная форма уменьшает избыточность данных и сохраняет свойства всех предыдущих форм. Перевод отношения в следующую нормальную форму осуществляется методом «декомпозиции без потерь», т.е. разбиением исходной таблицы на несколько связанных. Такая декомпозиция должна обеспечить равенство результатов выборок из исходного отношения и выборок, основанных на совокупности полученных отношений, т.е. говоря математическим языком, должно соблюдаться следующее правило:
|
|
|
m
∪Ri= R, где R={A1, A2 ,…,An} - исходное отношение, а
i=1
D={R1,R2,…,Rm} - декомпозиция, т.е. множество нормализованных отношений. При декомпозиции следует не только опасаться потери информации, но также избегать и возможности появления так называемых подложных записей, которые могут появиться вследствие проведения объединений (например, такие записи могут появляться при объединении таблиц посредством не ключевых полей). И наконец, при установлении зависимостей между таблицами следует придерживаться связей, существующих между реальными объектами.
Рассмотрим ненормализованную таблицу «Продажи», которая будет содержать сведения о покупателях, сведения о проданных товарах и оформленных заказах:
|
|
|
Таблицу «Продажи» можно рассматривать как однотабличную БД. Основная проблема состоит в том, что в ней содержится значительное количество повторяющейся информации. Например, сведения о каждом покупателе повторяются для каждого сделанного им заказа. Наличие повторяющейся информации ведет к возможной потере согласованности данных, т.е. к появлению аномалий обновления, неоправданному увеличению размера базы данных, и, как следствие, к снижению скорости поиска и выполнения запросов. Аномалии обновления можно условно разбить на три вида:
a) аномалии добавления. Возникают в случаях, когда информацию в таблицу нельзя поместить до тех пор, пока она не полная. Например, мыbхотим добавить информацию о новом товаре, пришедшем на склад и у которого пока еще нет покупателей. Нам придется оставить незаполненными все поля, касающиеся клиентов и оформления заказа. Но если некоторые поля были объявлены обязательными, то либо в них придется ввести что-нибудь, либо вообще отказаться от записи. К тому же, после первой же покупки этого товара необходимость в этой записи вообще исчезнет;
b) аномалии удаления. Состоят в том, что при удалении некоторой ин-формации может пропасть и другая информация, не связанная напрямую с удаляемой. Например, при удалении последней записи о продаже некоторого товара сведения об этом товаре будут утеряны, хотя это никак не будет означать, что данного товара больше нет на складе;
|
|
|
c) аномалии модификации. Проявляются в том, что изменение значения некоторого поля в одной записи может привести к просмотру всей таблицы и соответствующему изменению других записей таблицы. Например, такая аномалия возникнет при обновлении не всех записей о покупках некоторого клиента при смене адреса этого клиента. Причем простая автозамена здесь не сможет помочь во всех случаях, тат как адрес может быть записан разным образом.
Первая нормальная форма
Первая нормальная форма требует соответствия исходной таблицы требованиям, предъявляемым к отношениям, т.е.:
1. Таблица не должна иметь повторяющихся записей;
2. Все атрибуты должны быть простыми (скалярными);
Первое требование легко решается введением ключевого поля, однозначно определяющего остальные поля таблицы. Разумеется, можно использовать одно из полей, имеющееся в таблице. К примеру, номер паспорта. Но паспорт легко сменить, сменив фамилию. В таком случае рекомендуется ввести так называемый табельный номер, основным назначением которого будет являться идентификация некоторой записи. Если этот номер не имеет практического смысла, то его можно скрыть от пользователей. Ввод табельного номера рекомендуется в следующих случаях:
|
|
|
• в предметной области наблюдается синомия, т.е. ключевой атрибут не является уникальным (например, фамилия);
• если естественный атрибут может меняться со временем;
• если данный атрибут участвует во многих связях. Так как связь осуществляется посредством ключевых полей, то во всех подчиненных таблицах придется повторить значение ключевого поля. При применении не числовых и достаточно громоздких полей скорость выполнения операций доступа и поиска будет существенно падать. Лучшим решением является применение целочисленных значений.
Второе требование постулирует, чтобы в каждой ячейке было представлено одно значение, отнюдь не массив или перечисление. Составные поля должны быть разложены на простые, а многозначные – вынесены в отдельные таблицы. В нашем случае поле «Адрес» должно быть разложено на поля «Страна», «Город» и «Адрес», чтобы сохранить возможность поиска и сортировки по данным полям. Поле «Телефон» является многозначным и, следовательно, должно быть выделено в отдельную таблицу.
После ввода составного ключа «Код_клиента», «Код_товара» и «Дата_заказа» таблица «Продажи» будет находиться в первой нормальной форме. Получим
см. рисунок:
Вторая нормальная форма
Таблица находится во второй нормальной форме, если:
1. она удовлетворяет условиям первой нормальной формы и
2. любое поле, не входящее в ключ, должно однозначно идентифицироваться значением первичного ключа. Если первичный ключ является составным, то остальные поля должны зависеть от полного выражения ключа, а не от его части (неключевые атрибуты должны характеризоваться полной функциональной зависимостью от первичного ключа).
Легко заметить, что атрибуты «Фамилия», «Имя», «Отчество», «Пас-порт», «Страна», «Город», «Адрес», «Предприятие», «Руководитель», «Кредит» и «Примечание» зависят только от части составного ключа – поля «Код_клиента», а атрибуты «Наим_товара», «Категория» и «Цена» зависят только от поля «Код_товара». Следовательно, они должны быть вынесены в две отдельные таблицы.
Иначе говоря, требование 2 также постулирует устранение повторяющейся информации для группы полей. Такие повторяющиеся группы полей обычно соответствуют некоторым реальным объектам, информацию о которых мы сохраняем. Следовательно, таблица должна содержать данные, относящиеся только к одному объекту. Поскольку здесь явно выделяются объекты «покупатели», «товары» и «заказы», нам необходимы три таблицы. Записи одной таблицы будут содержать сведения об покупателях, второй – информацию о товарах, третей – информацию о каждом из заказов. Вначале разобьем таблицу «Продажи» на две отдельные таблицы («Клиенты» и «Заказы») и определим поле «Код_клиента» в качестве совпадающего поля для связывания таблиц.
Аналогично выделим из таблицы «Заказы» таблицу «Товары», которая будет содержать информацию о товарах каждого типа. Для связывания таблиц «Заказы» и «Товары» будем использовать поле "Код_товара". Между таблицами «Клиенты» – «Заказы» и «Товары» – «Заказы» будут отношения один-ко-многим. В таблице «Заказы» в качестве ключевого поля можно выбрать поля «Код_клиента», «Код_товара» и «Дата_заказа», а также ввести табельный номер «Код_заказа». Получим:
Третья нормальная форма
Таблица находится в третьей нормальной форме, если:
1. она удовлетворяет условиям второй нормальной формы и,
2. ни одно из полей таблицы, не входящих в ключ, не должно идентифицироваться с помощью другого поля, не входящего в ключ (иначе, отношение не должно иметь неключевых атрибутов, которые бы находились в транзитивной функциональной зависимости от первичного ключа).
Используя пример нормализации данных в лабораторной работе №2 продолжим нормализацию:
Сведение таблицы к третьей нормальной форме предполагает разделение таблицы с целью помещения в отдельную таблицу (или несколько таблиц) столбцов, которые не зависят напрямую от выражения первичного ключа. В результате такого разбиения каждое из полей, не входящих в первичный ключ, должно оказаться независимым от какого-либо другого не ключевого поля. Обратимся к таблице «Товары». Поле «Размещение» этой таблицы содержит описание места размещения товара на складе, которое однозначно определяется значением поля «Категория». Поскольку поле «Размещение», не входящее в индекс, однозначно определяется другим неключевым полем «Категория», таблица «Товары» не является таблицей в третьей нормальной форме. Для приведения этой таблицы к третьей нормальной форме создадим новую таблицу Категории. Получим:
Третья нормальная форма также постулирует отсутствие полей, которые могут быть вычислены на основе других. Если полная цена может быть вычислена на основе полей «Цена» и «Количество», то его лучше исключить из таблицы и вычислять динамически. Но в нашем случае это поле содержит отпускную цену, которая может включать скидки и налоги и может не зависеть напрямую от цены товара.
На этом процесс нормализации обычно заканчивается. Если же необходима более детальная проработка данных, то дополнительно рассматривают требования нормальных форм более высокого порядка
Правила нормализации
Резюмируя вышесказанное, сформулируем несколько правил, которых следует придерживаться при нормализации таблиц:
1. Разрабатывайте схему данных таким образом, чтобы можно было бы легко объяснить ее, т.е. не комбинируйте атрибуты независимых объектов и не создавайте сложные связи;
2. Разрабатывайте схему данных таким образом, чтобы исключить возможность появления аномалий обновления;
3. Разрабатывайте схему данных таким образом, чтобы в связях участвовали только первичные (можно допустить потенциальные) и внешние ключи. Это позволит избежать появления подложных записей;
Требование, чтобы все таблицы были нормализованы, не является обязательным. В нашем случае вполне можно было бы обойтись без таблицы «Телефоны». В заключение сформулируем определение реляционной базы данных как совокупность нормализованных отношений.
Связи в реляционной БД
Отношение между объектами определяет связи между таблицами. Поддерживаются связи четырех типов: «один к одному», «один ко многим», «много к одному» и «многие ко многим». Рассмотрим подробнее типы связей в применении к реляционной модели данных:
• «один к одному»
Отношение «один к одному» означает, кто каждой записи из первой таблицы соответствует одна и только одна запись из другой таблицы. Рассмотрим таблицы, содержащие персональные и служебные сведения о работниках некоторой фирмы.
Между таблицами «Персональные сведения» и «Служебные сведения» существует отношение «один к одному», поскольку для одного человека, работающего в определенной фирме, может существовать только одна запись о его служебном положении. Табельные номера «Код_пс» и «Код_сс» были добавлены для однозначной идентификации записей. Эти же поля и приняты в качестве первичных ключей. Связь между этими таблицами поддерживается при помощи совпадающих значений полей «Код_пс» и «Код_сс». Легко убедится, что между двумя ключевыми полями
может существовать только связь «один к одному», поскольку любые дублирования одного и того же табельного номера исключены с обеих сторон.
• «один ко многим» и «много к одному»
|
|
|
|
Отношение «один ко многим» означает, кто каждой записи из первой таблицы может соответствовать одна либо много записей из другой таблицы. Рассмотрим таблицы, содержащие сведения о клиентах некоторой фирмы и сделанных ими заказах.
Предполагается, что один и тот же покупатель может сделать несколько заказов. Таким образом, между этими таблицами существует связь «один ко многим». Для установления связи необходимо в таблицу «Заказы» ввести поле «Код_клиента», которое будет являться внешним ключом для таблицы «Заказы». Связь между таблицами будет осуществляться на основании значений полей «Клиенты.Код_клиента» и «Заказы.Код_клиента». Причем подчеркнем, что связь устанавливается на основе значений совпадающих полей, а не их наименований. Таким образом, если связь устанавливается между ключевым полем одной таблицы и неключевым полем второй таблицы, то это будет связь типа «один ко многим»
• «много ко многим»
Отношение «много ко многим» возникает между двумя таблицами в тех случаях, когда каждой записи из первой таблицы может соответствовать одна либо много записей из второй таблицы, и наоборот, одной записи из второй таблицы может соответствовать одна либо много записей из первой таблицы.
Типичным примером является связь между клиентами некой фирмы и покупаемыми ими товарами. Каждый товар может быть куплен несколькими клиентами и наоборот, один клиент может купить несколько товаров. Такая связь может быть реализована с помощью дополнительной таблицы, содержащей ключевые поля обеих таблиц, участвующих в связи (являющи-мися для нее внешними ключами).
Пример проектирования структуры БД со сложной структурой.
В качестве примера рассмотрим проектирование структуры БД «Учет заказов покупателей», которая содержит следующую информацию:
- сведения о покупателе;
- дату заказа и количество заказанного товара;
- дату выполнения заказа и количество проданного товара;
- характеристика проданного товара;
Структура таблицы ФИРМА.
| Имя поля | Тип | Ширина | Дес | |
| 1. | Фам_покупателя | Character | 20 | |
| 2. | Телефон | Character | 13 | |
| 3. | Индекс | Numeric | 10 | |
| 4. | Город | Character | 20 | |
| 5. | Адрес | Character | 60 | |
| 6. | Предприятие | Character | 20 | |
| 7. | Руководитель | Character | 30 | |
| 8. | Кредит | Numeric | 12 | 2 |
| 9. | Дата_заказа | Date | 8 | |
| 10. | Кол_заказано | Numeric | 10 | |
| 11. | Дата_продажи | Date | 8 | |
| 12. | Кол_продано | Numeric | 10 | |
| 13. | Цена | Numeric | 12 | 2 |
| 14. | Наимен_категории | Character | 15 | |
| 15 | Наимен_ товара | Character | 35 |
Примеры хранимых данных
| Имя поля | Запись1 | Запись2 | Запись3 |
| Фам_покупателя | Иванов С.С. | Лужкова Л.А. | Иванов С.С. |
| Телефон | 20-30-21 | 22-34-56 | 20-30-21 |
| Индекс | 445050 | 445050 | 445050 |
| Город | г.Тольятти | г.Тольятти | г.Тольятти |
| Адрес | ул Дорожная 22 | ул.Заречная 98 | ул Дорожная 22 |
| Предприятие | ООО «Луг» | ООО «Стриж» | ООО «Луг» |
| Руководитель | Сидоров Р.И. | Морозов С.П. | Сидоров Р.И. |
| Кредит | 0 | 1500 | 0 |
| Дата_заказа | 12.03.2005 | 12.03.2005 | 15.03.2005 |
| Кол_заказано | 20 | 30 | 50 |
| Дата_продажи | 13.03.2005 | 12.03.2005 | 15.03.2005 |
| Кол_продано | 20 | 30 | 50 |
| Цена | 10 р. | 156 р. | 10 р. |
| Наимен_категории | Хлебо-булочная | Хлебо-булочная | Хлебо-булочная |
| Наимен_ товара | хлеб «Бородинский» | Торт «Марс» | хлеб Бородинский» |
В этой таблице содержится значительное количество повторяющейся информации. Например, сведения о каждом покупателе повторяются для каждого сделанного им заказа. Такая структура таблицы приведет к трате значительного времени на ввод повторяющихся данных. Наличие повторяющейся информации приведет к неограниченному увеличению объема базы данных, возрастет вероятность ошибок при вводе.
Приведенная таблица не является нормализованной.
Для удовлетворения условий первой нормальной формы таблица должна иметь по крайней мере один уникальный ключ. Поэтому для выполнения условия необходимо создать индекс (определить ключ), который включал бы совокупность всех полей таблицы, однозначно определял запись.
Должна устранять повторяющиеся группы полей. Поскольку каждый покупатель может заказать несколько товаров, поэтому необходимы две таблицы.
Разобьем таблицу ФИРМА на
ПОКУПАТЕЛЬ и ЗАКАЗ
(сведения о покупателе) (информация о каждом заказе товара)
| Код покупателя Фамилия Индекс Телефон Город Адрес Предприятие Руководитель Кредит |
| Код покупателя Код товара Дата_заказа Кол_заказано Дата продажи Цена Наимен_категории Наимен_товара Кол_продано |
Рис. Приведение к первой нормальной форме.
Между таблицами будет связь один ко многим по коду покупателя.
Понятие 2-ой нормальной формы применимо только к таблицам имеющим составной ключ.
В данном примере таблица ЗАКАЗ имеет составной индекс: код покупателя + код товара + Дата_заказа. Поля Наимен_товара, Цена, Наимен_категории однозначно определяются только одним из индексным полем Код товара.
Для приведения таблицы ко второй нормальной форме разделим таблицу ЗАКАЗ на две:
ПОКУПАТЕЛЬ ЗАКАЗ ТОВАР
| Код товара Наимен_категории Наимен_товара Цена |
| Номер заказа Код товара Код покупателя Дата_заказа Кол_заказано Дата продажи Кол_продано |
| Код покупателя Фамилия Индекс Телефон Город Адрес Предприятие Руководитель Кредит |
Рис. Приведение ко второй нормальной форме.
Для связывания таблиц используется Код товара.
2-я НФ, имеет вид:
ПОКУПАТЕЛЬ ЗАКАЗ ТОВАР
| Код товара Наимен_категории Наимен_товара Цена |
| Номер заказа Код товара Код покупателя Дата_заказа Кол_заказано Дата продажи Кол_продано |
| Код покупателя Фамилия Индекс Телефон Город Адрес Предприятие Руководитель Кредит |
Рис. Вторая нормальная форма БД «Учет заказов покупателей».
Продолжим применение процесса нормализации БД.
В таблице ПОКУПАТЕЛЬ поле Руководитель содержит имена руководителей, которые однозначно определяются значением поля Предприятие. Посколько неиндексное поле Руководитель однозначно определяется другим неиндексным полем Предприятие, то таблица Покупатель не является таблицей в третьей нормальной форме. Тоже и полем Наимен_категории и Наимен_товара в таблице ТОВАР.
Для приведения этих таблиц к третьей нормальной форме создадим новые таблицы КАТЕГОРИЯ и ПРЕДПРИЯТИЕ.
| Кодпредприятия Индекс Город Адрес Предприятие Руководитель |
| Код покупателя Фамилия Телефон Код предприятия Кредит |
| Код товара Код категории Наимен_товара Цена |
| Код категории Наимен_категории |
Рис. Приведение к третьей нормальной форме.
После определения структуры таблиц, строится Информационно-логическая модель.
Информационно-логическая модель является моделью данных, отображающей предметную область в виде совокупности информационных объектов и структурных связей между ними.
Логическая структура реляционной базы данных.
КАТЕГОРИЯ ТОВАР
| Код покупателя Фамилия Телефон Код предприятия Кредит |
| Кодпредприятия Индекс Город Адрес Предприятие Руководитель |
| Код товара Код категории Наимен_товара Цена |
| Код категории Наимен_категории |
| Номер заказа Код товара Код покупателя Дата_заказа Кол_заказано Дата продажи Кол_продано |
ПРЕДПРИЯТИЕ ПОКУПАТЕЛЬ
Дата добавления: 2018-02-28; просмотров: 1534; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!