Лекция 4 Тема Проектирование реляционных баз данных на

Лекция 4 Тема: Проектирование реляционных баз данных на основе принципов нормализации Рассматриваемые вопросы: 1. Выбор СУБД 2. Даталогическое проектирование 3. Нормализация схемы данных 4. Преобразование ER-модели в реляционную

Этапы проектирования БД Системный анализ предметной области Словесное описание информационных объектов предметной области и их анализ. Инфологическое моделирование Частично формализованное описание объектов предметной области в терминах некоторой семантической модели. Выбор СУБД Даталогическое проектирование Физическое проектирование Описание БД в терминах принятой модели данных, например реляционной. Выбор размещения БД на внешних носителях для обеспечения наиболее эффективной работы приложения. 2

1. Выбор СУБД 3

Критерии выбора программных продуктов: • Модель представления данных • Объем данных • Особенности архитектуры и функциональные возможности • Качество средств разработки • Качество средств защиты и контроля корректности БД • Категории пользователей • Удобство и простота использования • Фирма-разработчик • Стоимость 4

2. Даталогическое проектирование 5

В результате выполнения этапа даталогического проектирования должны быть получены следующие результирующие документы: • Описание концептуальной схемы БД в терминах выбранной СУБД. • Описание связей данных (структурная целостность). • Описание семантических ограничений (семантическая целостности). 6

7

Даталогическое моделирование – отображение инфологической модели на модель данных, используемую в конкретной СУБД, например на реляционную модель. Для реляционных СУБД даталогическая модель – набор таблиц, с указанием типов полей, ключевых полей, связей между таблицами. Инфологическое моделирование - построение ER-диаграмм Даталогическое моделирование - построение таблиц по определённым формализованным правилам, а также нормализацию этих таблиц. 8

Нормальная форма —совокупность требований, которым должно удовлетворять отношение. Процесс преобразования базы данных к виду, отвечающему нормальным формам, называется нормализацией. Нормализация предназначена для приведения структуры базы данных к виду, обеспечивающему минимальную избыточность. Устранение избыточности производится таким образом, чтобы в каждом отношении хранились только первичные факты (то есть не выводимые из других хранимых фактов). Приведение структуры базы данных к нормальному виду выполняется за счет декомпозиции или объединения отношений. 9

Процесс нормализации базы данных – итерационный процесс. Каждая следующая нормальная форма сохраняет свойства предыдущей. Каждая следующая нормальная форма дополняет свойства предыдущей. 10

Типы нормальных форм • Первая нормальная форма (1 NF) • Вторая нормальная форма (2 NF) • Третья нормальная форма (3 NF) • Нормальная форма Бойса — Кодда (BCNF) • Четвёртая нормальная форма (4 NF) • Пятая нормальная форма (5 NF) • Доменно-ключевая нормальная форма (DKNF) • Шестая нормальная форма (6 NF) 11

Функциональные зависимости Теория нормализации основана на анализе функциональных зависимостей между атрибутами отношений. Функциональные зависимости определяют устойчивые взаимоотношения между объектами и их свойствами в рассматриваемой предметной области 12

Определение функциональной зависимости Пусть дано отношение r. A и B — некоторые подмножества атрибутов отношения r. Множество B функционально зависит от A тогда и только тогда, когда каждое значение множества A связано в точности с одним значением множества B. Другими словами, если два кортежа совпадают по атрибутам A, то они совпадают и по атрибутам B. В этом случае A — детерминант, B — зависимая часть. 13

3. Нормализация схемы данных 14

Первая нормальная форма Определение Таблица находится в первой нормальной форме (1 НФ или 1 NF), если каждый её атрибут атомарен, то есть может содержать семантически неделимое значение. Таким образом, не существует 1 NF таблицы, в полях которых могут храниться списки значений. Для приведения таблицы к 1 NF обычно требуется разбить неатомарные поля на несколько полей, возможно принадлежащих нескольким таблицам или несколько записей. 15

Пример приведения таблицы к первой нормальной форме Исходная, ненормализованная таблица: Таблица, приведённая к 1 NF: Сотрудник Номер телефона Иванов И. И. 283 -56 -82 390 -57 -34 Иванов И. И. 283 -56 -82 Иванов И. И. 390 -57 -34 708 -62 -34 Петров П. Ю. 16

Атомарность атрибутов Значение « 4286» является • атомарным, если его смысл — «пин-код кредитной карты» (при разбиении на части или переупорядочивании смысл теряется) • неатомарным, если его смысл — «четные цифры» (при разбиении на части или переупорядочивании смысл не теряется) 17

Разбиение атрибутов Хорошим способом принятия решения о необходимости разбиения атрибута на части является вопрос: «будут ли части атрибута использоваться по отдельности? » Если да, то атрибут следует разделить (но так, чтобы сохранились осмысленные части атрибута). 18

Вторая нормальная форма Определение Таблица находится во второй нормальной форме (2 НФ или 2 NF), если она находится в первой нормальной форме, и при этом любой её атрибут, не входящий в состав ключа, функционально полно зависит от ключа. Функционально полная зависимость означает, что атрибут функционально зависит от всего составного ключа, но при этом не находится в функциональной зависимости от какой-либо из входящих в ключ атрибутов (частей). Другими словами: в 2 NF нет неключевых атрибутов, зависящих от части составного ключа (плюс выполняются условия 1 NF). 19

Пример приведения таблицы ко второй нормальной форме Пусть поля «Сотрудник» и «Должность» вместе образуют первичный ключ в такой таблице: Сотрудник Должность Зарплата Наличие компьютера Гришин Кладовщик 20000 Васильев Программист 40000 Есть Васильев Кладовщик Нет 25000 Нет Зарплату сотруднику каждый начальник устанавливает сам, но её границы зависят от должности. Наличие компьютера у сотрудника зависит только от должности, то есть зависимость от первичного ключа неполная. 20

В результате приведения к 2 NF получаются две таблицы: Сотрудник Должность Зарплата Должность Наличие компьютера Гришин Кладовщик 20000 Кладовщик Нет Васильев Программист 40000 Васильев Кладовщик Программист Есть 25000 Первичный ключ, как и в исходной таблице, составной, но единственный не входящий в него атрибут «Зарплата» зависит теперь от всего ключа, то есть полно. 21

Третья нормальная форма (сокращённо 3 НФ или 3 NF) является достаточной при решении большинства практических задач, и процесс проектирования реляционной БД, как правило, заканчивается приведением к 3 НФ 22

Определение Таблица находится в 3 НФ, когда она находится во второй нормальной форме и каждый непервичный атрибут находится в нетранзитивной (то есть прямой) зависимости от ключа. Непервичный (неключевой) атрибут — это атрибут, который не принадлежит ни одному из возможных (альтернативных) ключей. Транзитивная зависимость — это функциональная зависимость, при которой X → Z (X определяет Z) не напрямую, а посредством отношения X → Y и Y → Z 23

Пример приведения таблицы к третьей нормальной форме Фамилия Отдел Гришин Фамилия Отдел Телефон Гришин 1 211 -22 -33 Васильев 1 211 -22 -33 Петров 2 244 -55 -66 В результате приведения к 3 НФ получаются две таблицы: 1 Васильев 1 Петров 2 Отдел Телефон 1 211 -22 -33 2 244 -55 -66 24

Алгоритм приведения модели данных к 3 -й нормальной форме • Шаг 1. Проанализировать схему на присутствие сущностей, которые скрыто моделируют несколько разных взаимосвязанных классов объектов реального мира (именно это соответствует ненормализованным отношениям). • Если такое выявлено, то разделить каждую из этих сущностей на несколько новых сущностей и установить между ними соответствующие связи. • Полученная схема будет находиться в первой нормальной форме. 25

• Шаг 2. Проанализировать все сущности, имеющие составные первичные ключи, на наличие неполных функциональных зависимостей непервичных атрибутов от атрибутов возможного ключа. • Если такие зависимости обнаружены, то разделить данные сущности на две, определить для каждой сущности первичные ключи и установить между ними соответствующие связи. • Полученная схема будет находиться во второй нормальной форме 26

• Шаг 3. Проанализировать неключевые атрибуты всех сущностей на наличие транзитивных функциональных зависимостей. • При обнаружении таковых разделить каждую сущность на несколько таким образом, чтобы ликвидировать транзитивные зависимости. • Схема находится в третьей нормальной форме. 27

4. Преобразование ER-модели в реляционную 28

Правила преобразования ER-модели в реляционную 1. Каждой сущности ставится в соответствие отношение реляционной модели данных. 2. Каждый атрибут сущности становится атрибутом соответствующего отношения. 29

Преобразование ключей 3. Первичный ключ сущности становится PRIMARY KEY соответствующего отношения. Атрибуты, входящие в первичный ключ отношения, автоматически получают свойство обязательности (NOT NULL). 4. В каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов основной сущности, являющейся первичным ключом основной сущности. В отношении, соответствующем подчиненной сущности, этот набор атрибутов становится внешним ключом (FOREING KEY). 30

Модальность связи 5. Для моделирования необязательного типа связи на физическом уровне у атрибутов, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений (признак NULL). При обязательном типе связи атрибуты получают свойство отсутствия неопределенных значений (признак NOT NULL). 31

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

33

Дополнительные сущности 1. Сущности "Накладная" и "Товар" связаны друг с другом отношением типа многие-ко-многим. Такая связь должна быть разделена на две связи типа один-комногим. Для этого требуется дополнительная сущность. Такой сущностью может быть сущность "Список товаров в накладной". Атрибуты "Количество товара в накладной" и "Цена товара в накладной" являются атрибутами сущности "Список товаров в накладной". 34

Связь сущностей "Накладная" и "Товар" характеризуется следующими фразами: "каждая накладная обязана иметь не менее одной записи из списка товаров в накладной", "каждая запись из списка товаров в накладной обязана включаться ровно в одну накладную", "каждый товар может включаться в несколько записей из списка товаров в накладной", "каждая запись из списка товаров в накладной обязана быть связана ровно с одним товаром". 35

2. Сущности "Склад" и "Товар" связаны друг с другом отношением типа многие-ко-многим. Такая связь должна быть разделена на две связи типа один-ко-многим. Для этого требуется дополнительная сущность. Такой сущностью может быть сущность "Товар на складе". Атрибутом этой сущности будет "Количество товара на складе". Таким образом, товар будет числиться на любом складе и количество его на каждом складе будет свое. 36

Номер покупателя Номер склада Номер товара Номер накладной Номер склада Номер товара 37