Практическая работа 8. Нормализация отношений в базе данных. Практическая работа 8 Тема. Нормализация отношений в базе данных
 Скачать 0.65 Mb.
|
|
Практическая работа № 8 Тема. Нормализация отношений в базе данных. Цель. Изучить нормальные формы отношений, приводить отношения в соответствия с ними. Ход работы Ознакомиться с теоретической частью. Выполнить практическое задание. Ответить на контрольные вопросы. Оформить отчет. Теоретическая часть 1. Общие сведения Сущность – это класс однотипных объектов, информация о которых должна быть учтена в модели. Каждая сущность должна иметь наименование, выраженное существительным в единственном числе. Примерами сущностей могут быть такие классы объектов, как «Поставщик», «Сотрудник», «Накладная». Экземпляр сущности – это конкретный представитель данной сущности. Например, представителем сущности «Сотрудник» может быть «Сотрудник Иванов». Экземпляры сущностей должны быть различимы, т.е. сущности должны иметь некоторые свойства, уникальные для каждого экземпляра этой сущности. Атрибут сущности – это именованная характеристика, являющаяся некоторым свойством сущности. Наименование атрибута должно быть выражено существительным в единственном числе (возможно, с характеризующими прилагательными) примерами атрибутов сущности «Сотрудник» могут быть такие атрибуты, как «Табельный номер». «Фамилия, «Имя», «Отчество», «Должность», «Зарплата» и т.п. Ключ сущности – это неизбыточный набор атрибутов, значения которых в совокупности являются уникальными для каждого экземпляра сущности. Неизбыточность заключается в том, что удаление любого атрибута из ключа нарушает его уникальность. Сущность может иметь несколько различных ключей. Первичный ключ – минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности (строку таблицы). Минимальность означает, что исключение из набора любого из атрибутов не позволяет идентифицировать сущность по оставшимся. Внешний ключ – это атрибут или набор атрибутов, зависимой (оперативной) таблицы, с помощью которого (которых) производится ссылка на первичный ключ другой таблицы (справочника), т.е. производится связывание таблиц. При этом справочником может быть как независимая, так и зависимая таблица – цепочка связей между таблицами ничем не ограничивается и может иметь чрезвычайно сложную структуру. Связь – это некоторая ассоциация между двумя сущностями. Одна сущность может быть связана с другой сущностью или сама с собою. Связи позволяют по одной сущности находить другие сущности, связанные с нею. Например, связи между сущностями могут выражаться следующими фразами: «СОТРУДНИК может иметь несколько ДЕТЕЙ», «каждый СОТРУДНИК обязан числиться ровно в одном ОТДЕЛЕ». Каждая связь имеет два конца и одно или два наименования. Наименование обычно выражается в неопределенной глагольной форме: «иметь», «принадлежать» и т.п. Каждое из наименований относится к своему концу связи. Иногда наименования не пишутся в виду их очевидности. Каждая связь может иметь один из следующих типов: связь типа «один к одному» означает, что один экземпляр первой сущности связан с одним экземпляром второй сущности. Такая связь чаще всего свидетельствует о том, что на самом деле мы имеем всего одну сущность, неправильно разделенную на две» связь типа «один ко многим» означает, что один экземпляр первой сущности связан с несколькими экземплярами второй сущности. Это наиболее часто используемый тип связи. Сущность со стороны «один» называется родительской, со стороны «много» - дочерней. связь типа «многие ко многим» означает, что каждый экземпляр первой сущности связан с несколькими экземплярами второй сущности и каждый экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности. Этот тип связи является временным типом, допустимой на раннем этапе разработки модели. В дальнейшем этот тип связи должен быть заменен двумя связями типа «один ко многим» путем создания промежуточной сущности. Ключ связи – набор ключей сущности, соединяемых данной связью. 2. Нормализация БД Нормализация – разбиение исходного отношения на два или несколько, обладающих лучшими свойствами при включении, изменении и удалении данных. Целью нормализации является получение БД, где каждый факт появляется только один раз, т.е. исключена избыточность, причем к этому стремятся не столько ради экономии памяти, сколько для исключения возможной противоречивости хранимой информации. Нормализация выражается в приведении отношений в соответствии с несколькими требованиями, называемыми нормальными формами. Сегодня известны шесть нормальных форм: Первая нормальная форма (1 НФ); Вторая нормальная форма (2 НФ); Третья нормальная форма (3 НФ); Нормальная форма Бойса-Кодда (НФБК); Четвертая нормальная форма (4 НФ); Пятая нормальная форма (5 НФ) В дальнейшем будем рассматривать только четыре первые четыре нормальные формы. 3. Нормальные формы отношений Будем рассматривать нормальные формы на примере конкретной БД. Пусть требуется спроектировать БД для сети продуктовых магазинов, в которой будут храниться данные о поступлениях товаров в магазины и их продажах. Исходные данные: Товар производиться производителем. При этом: у каждого товара обязательно есть его производитель, и каждый производитель производит хотя бы один товар. У товара есть тип. Каждый товар обязательно относится к какому-либо типу, причем только к одному. В магазине могут быть в наличии товары не всех типов. Товаров одного типа может быть несколько. Экземпляр товара находится в магазине или продан. Экземпляр товара обязательно находится в каком-либо магазине, и в каждом магазине обязательно есть товары. Каждый экземпляр товара может быть только в одном магазине, при этом в каждом магазине может быть множество экземпляров товаров. Составим теперь универсальное отношение – отношение, включающее все атрибуты и содержащее все данные БД. Таблица будет иметь 5 полей: «Производитель»: со всеми данными о производителе, «Магазин» со всеми данными о магазине, «Тип товара», «Товар», «Экземпляр товара». С учетом полученных сведений можно заключить, что будет иметь место дублирование данных (рис. 1,б), равно как и отсутствие значений некоторых полей в записях.  Рисунок 1 – Дублирование данных в универсальном отношении а – универсальное отношение; б – дублирование данных Теперь рассмотрим процесс нормализации отношений и нормальные формы. 3.1. Первая нормальная форма Все атрибуты отношения простые, то есть их значения неделимы. В нашем случае все атрибуты не являются простыми. Так, например, данные о производителе можно разделить на название, телефон, адрес и т.д. Иными словами, 1НФ содержит правило об исключении повторяющихся групп, т.е. атрибут некоторого экземпляра сущности должен содержать одно значение, а не список значений. Например, атрибут «Цена» сущности «Товар» должен содержать только одно значение цены для каждого экземпляра этой сущности. После приведения отношения к первой нормально форме можно получить, например, такое отношение (рис.2). 3.2. Вторая нормальная форма Каждое неключевое поле связано полной функциональной зависимостью с первичным ключом. Полной функциональной зависимостью называется ситуация, при которой значение данного атрибута определяется значение некоторого составного атрибута и не определяется значением любой его части.  Ключ может быть составным, то есть состоять из нескольких полей. Поэтому 2НФ фактически добавляет требование о том, чтобы значение любого неключевого поля зависело от значения всего ключа и не зависело от значения его части. В примере таким ключом могла бы быть совокупность полей : «Название производителя», «Название магазина», «Тип товара», но, например, значения поля «Адрес производителя» зависит от части ключа «Название производителя». Решением является дополнительного атрибута «Код», являющего первичным ключом. После этого отношение будет выглядеть, как показано на рисунке 3.3.3. Третья нормальная форма  Рисунок 1 - Вторая НФ Любой неключевой атрибут транзитивно зависит от ключа. Транзитивная зависимость – ситуация, при которой в некоторой тройке атрибутов первый зависит от второго, второй от третьего, а третий от первого. В рассмотренном отношении транзитивные зависимости присутствуют. Исключить транзитивные зависимости можно, разбив данное отношение на 5 отношений: «Производитель», «Магазин», «Тип товара», «Товар» и «Экземпляр товара». При этом в каждое из них следует добавить поле, являющееся первичным ключом, например, «Код». Кроме того, чтобы данные не потеряли данные между собой, нужно добавить в ключи вторичные ключи. Этими ключами будут: в таблице «Товар» - «Производитель», «Тип»; в таблице «Экземпляр товара» - «Товар». В результате получим схему данных, изображенную на рисунке 4.  Рисунок 4 – Третья НФ Иными словами, 3 НФ содержит правило о том, что каждый атрибут, не являющийся ключевым, должен зависеть только от ключа сущности и не зависеть от других атрибутов. Требование о независимости атрибутов друг от друга означает, что изменение одного атрибута, не входящего в ключ сущности, никак не отразится на другом. 3.4. Третья усиленная нормальная форма (нормальная форма Бойса-Кодда) Каждый детерминант отношения является возможным ключом. Иначе говоря, каждое поле, по значению которого можно однозначно идентифицировать запись, по требованиям соответствует первичному ключу, хотя может им и не являться. Требования НФБК наши отношения не нарушают. Примечание: каждая последующая нормальная форма является недостижимой, пока не будут достигнуты все предыдущие. Моделирование структуры базы данных при помощи алгоритма нормализации, имеет серьезные недостатки: Первоначальное размещение всех атрибутов в одном отношении является очень неестественной операцией. Интуитивно разработчик сразу проектирует несколько отношений в соответствии с обнаруженными сущностями. Даже если создать одно или несколько отношений, включив в них все предполагаемые атрибуты, то совершенно не ясен смысл полученного отношения. Невозможно сразу определить список полный список атрибутов. Пользователи имеют привычку называть разными именами одни и те же вещи, или наоборот, называть одними именами разные вещи. Для проведения процедуры нормализации необходимо выделить зависимость атрибутов, что тоже очень нелегко, так как необходимо явно выписать все зависимости, даже те, которые являются очевидными. Практическая часть Записать исходные данные с указанием связей между атрибутами.. Составить универсальное отношение. Выполнить нормализацию до НФБК. Контрольные вопросы Понятие первичного и вторичного ключей. Что такое нормализация? Каковы ее цели? Сколько известно нормальных форм? По какому принципу они строятся? Опишите 1НФ, 2НФ, 3НФ, НФБК. Варианты заданий ( Сведения о сотрудниках: фамилия и инициалы сотрудника; должность; оклад; норма выработки по должности; фактическая выработка; расчетная величина премии. Продажа сотовых телефонов: название телефона; номер модели; заводская цена; наценка магазина; количество проданных телефонов; выручка от продаж по каждой модели телефона (расчет); сведения о фирме-изготовителе (страна, континент, город) . Продажи: наименование товара, поступившего в магазин; вид товара; количество; стоимость единицы товара; дата изготовления; срок хранения (количество дней); сведения о фирме-производителе (название, фамилия директора, телефон); название страны, на территории которой произведён товар. Книги: название учебника; фамилия и инициалы автора; год издания; сведения об издательстве (название, адрес, телефон); название учебной дисциплины, при изучении которой используется учебник; стоимость экземпляра; количество имеющихся экземпляров. Театр: название театра; направление театральной деятельности (драматический, оперный и т.д.); название типа посадочного места (партер, амфитеатр, балкон и т.п.); количество мест соответствующего типа; средняя цена одного места соответствующего типа; продано билетов соответствующего типа; Оснащение компьютерных лабораторий: наименование факультета (фамилия И.О. директор, № администрации); номер аудитории, в которой расположен компьютерная лаборатория; длина аудитории; ширина аудитории; площадь аудитории (расчет); количество компьютеров, находящихся в аудитории; стоимость одного компьютера (в рублях). Принтеры: марка принтера; тип принтера (матричный, струйный, лазерный); тип подачи бумаги (рулон, пачка, лист…); скорость печати (страниц в минуту); возможность цветной печати; формат листа бумаги; стоимость; дата изготовления; сведения о фирме-изготовителе (название, страна). Гостиница: фамилия и инициалы, проживающего в гостинице: номер проживания; классность номера; стоимость проживания одних суток; дата бронирования (гость еще не заселился); дата начала проживания; дата освобождения номера; стоимость проживания в гостинице (расчет). Выборы в муниципальном образовании: номер муниципального округа (телефон для связи, фамилия и инициалы секретаря); численность населения, имеющего право голоса; количество граждан, которые пришли на выборы; фамилия и инициалы кандидата; партийная принадлежность; число голосов, отданных за кандидата. Автомобильный салон: марка легкового автомобиля; вес; мощность двигателя; количество ведущих осей; год выпуска; название компоновочной схемы (седан, хетчбек, универсал); стоимость; сведения о заводе-изготовителе (название, факс, страна). Теплоходы: название судна; водоизмещение; количество мест для пассажиров; количество членов экипажа; год выпуска; данные о заводе-изготовителе (название, факс); порт приписки судна. |