Модуль 1. Математические основы баз данных и знаний 1

Лекция 8 Проектирование реляционных баз данных на основе нормализации 1. Основные принципы проектирования БД 2. Третья нормальная форма 2

1. Основные принципы проектирования БД две основные проблемы проектирования базы данных: Непротиворечивое и эффективное отображение объектов предметной области в абстрактные объекты модели данных (проблема логического проектирования баз данных) Обеспечение эффективного выполнения запросов к базе данных (проблема физического проектирования баз данных) 3

4 классический подход Весь процесс проектирования базы данных осуществляется методом последовательных приближений к удовлетворительному набору схем отношений. Исходная точка - представление предметной области в виде одного или нескольких отношений. На каждом шаге проектирования производится некоторый набор схем отношений, обладающих «улучшенными» свойствами - нормализация схем отношений. 1. Основные принципы проектирования БД

Каждой нормальной форме соответствует определенный набор ограничений, и отношение находится в некоторой нормальной форме, если удовлетворяет свойственному ей набору ограничений Первая нормальная форма – значения всех атрибутов отношения атомарны – базовое требование классической реляционной модели данных 5 1. Основные принципы проектирования БД

6 последовательность нормальных форм: первая нормальная форма (1NF) вторая нормальная форма (2NF) третья нормальная форма (3NF) нормальная форма Бойса-Кодда (BCNF) четвертая нормальная форма (4NF) пятая нормальная форма или нормальная форма проекции-соединения (5NF или PJ/NF). 1. Основные принципы проектирования БД

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

Исходное отношение 8 1. Основные принципы проектирования БД Ключ отношения - составной атрибут {СЛУ_НОМ, ПРО_НОМ}

Аномалии обновления Добавление кортежей. Мы не можем дополнить отношение данными о служащем, который в данное время еще не участвует ни в одном проекте (ПРО_НОМ является частью первичного ключа и не может содержать неопределенных значений). Между тем часто бывает, что сначала служащего принимают на работу, устанавливают его разряд и размер зарплаты, а лишь потом назначают для него проект Основные принципы проектирования БД

Аномалии обновления Удаление кортежей. Мы не можем сохранить в отношении данные о служащем, завершившем участие в своем последнем проекте (по той причине, что значение атрибута ПРО_НОМ для этого служащего становится неопределенным). Между тем характерна ситуация, когда между проектами возникают перерывы, не приводящие к увольнению служащих Основные принципы проектирования БД

Аномалии обновления Модификация кортежей. Чтобы изменить разряд служащего, мы будем вынуждены модифицировать все кортежи с соответствующим значением атрибута СЛУ_НОМ. В противном случае будет нарушена естественная FD СЛУ_НОМ СЛУ_УРОВ (у одного служащего имеется только один разряд) Основные принципы проектирования БД

Решение - декомпозиция переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ на две переменных отношений – СЛУЖ {СЛУ_НОМ, СЛУ_УРОВ, СЛУ_ЗАРП} и СЛУЖ_ПРО_ЗАДАН {СЛУ_НОМ, ПРО_НОМ, СЛУ_ЗАДАН} Основные принципы проектирования БД

13 1. Основные принципы проектирования БД

14 Диаграммы FD 1. Основные принципы проектирования БД

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

аномалии обновления вызываются наличием транзитивной FD СЛУ_НОМ СЛУ_ЗАРП (через FD СЛУ_НОМ СЛУ_УРОВ и СЛУ_УРОВ СЛУ_ЗАРП) Основные принципы проектирования БД Аномалии обновлений 2NF

Добавление кортежей. Невозможно сохранить данные о новом разряде (и соответствующем ему размере зарплаты), пока не появится служащий с новым разрядом. (Первичный ключ не может содержать неопределенные значения.) 17 1.Основные принципы проектирования БД Аномалии обновлений 2NF

Удаление кортежей. При увольнении последнего служащего с данным разрядом мы утратим информацию о наличии такого разряда и соответствующем размере зарплаты Основные принципы проектирования БД Аномалии обновлений 2NF

Модификация кортежей. При изменении размера зарплаты, соответствующей некоторому разряду, мы будем вынуждены изменить значение атрибута СЛУ_ЗАРП в кортежах всех служащих, которым назначен этот разряд (иначе не будет выполняться FD 19 1.Основные принципы проектирования БД Аномалии обновлений 2NF

20 Решение - декомпозиция переменной отношения СЛУЖ на две переменных отношений – СЛУЖ1 {СЛУ_НОМ, СЛУ_УРОВ} и УРОВ {СЛУ_УРОВ, СЛУ_ЗАРП}. 1. Основные принципы проектирования БД

21 1. Основные принципы проектирования БД

22 2. Третья нормальная форма Переменная отношения находится в третьей нормальной форме (3NF) в том и только в том случае, когда она находится во второй нормальной форме, и каждый неключевой атрибут нетранзитивно функционально зависит от первичного ключа

23 Теорема Риссанена Проекции r1 и r2 отношения r являются независимыми тогда и только тогда, когда: каждая FD в отношении r логически следует из FD в r1 и r2; общие атрибуты r1 и r2 образуют возможный ключ хотя бы для одного из этих отношений 2. Третья нормальная форма

24 2. Третья нормальная форма Переменная отношения находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, когда любая выполняемая для этой переменной отношения нетривиальная и минимальная FD имеет в качестве детерминанта некоторый возможный ключ данного отношения.

25 2. Третья нормальная форма Алгоритм нормализации (приведение к 3НФ) Шаг 1 (Приведение к 1НФ). На первом шаге задается одно или несколько отношений, отображающих понятия предметной области. По модели предметной области (не по внешнему виду полученных отношений!) выписываются обнаруженные функциональные зависимости. Все отношения автоматически находятся в 1НФ.

26 2. Третья нормальная форма Алгоритм нормализации (приведение к 3НФ) Шаг 2 (Приведение к 2НФ). Если в некоторых отношениях обнаружена зависимость атрибутов от части сложного ключа, то проводим декомпозицию этих отношений на несколько отношений следующим образом: те атрибуты, которые зависят от части сложного ключа выносятся в отдельное отношение вместе с этой частью ключа. В исходном отношении остаются все ключевые атрибуты:

27 2. Третья нормальная форма Алгоритм нормализации (приведение к 3НФ) Шаг 3 (Приведение к 3НФ). Если в некоторых отношениях обнаружена зависимость некоторых неключевых атрибутов от других неключевых атрибутов, то проводим декомпозицию этих отношений следующим образом: те неключевые атрибуты, которые зависят других неключевых атрибутов выносятся в отдельное отношение. В новом отношении ключом становится детерминант функциональной зависимости.

28 2. Третья нормальная форма Замечание. При создании логической модели данных, как правило, не следуют прямо приведенному алгоритму нормализации. Опытные разработчики обычно сразу строят отношения в 3НФ. Кроме того, основным средством разработки логических моделей данных являются различные варианты ER-диаграмм. Особенность этих диаграмм в том, что они сразу позволяют создавать отношения в 3НФ. Алгоритм важен по двум причинам: Во-первых, этот алгоритм показывает, какие проблемы возникают при разработке слабо нормализованных отношений. Во-вторых, как правило, модель предметной области никогда не бывает правильно разработана с первого шага из-за ошибок понимания предметной области.

29 2. Третья нормальная форма OLTP - системы Сильно нормализованные модели OLTP (On-Line Transaction Processing) - системы складского учета, системы заказов билетов, банковские системы. Основная функция систем – выполнение большого количества коротких транзакций. Проблема - транзакций очень много; выполняются они одновременно; при возникновении ошибки, транзакция должна целиком откатиться и вернуть систему к состоянию, которое было до начала транзакции Практически все запросы к базе данных в OLTP-приложениях состоят из команд вставки, обновления, удаления. Таким образом, критическим для OLTP-приложений является скорость и надежность выполнения коротких операций обновления данных. Чем выше уровень нормализации данных в OLTP-приложении, тем оно быстрее и надежнее.

30 2. Третья нормальная форма OLАP - системы Слабо нормализованные модели OLAP-приложения (On-Line Analitical Processing) - оперативная аналитическая обработка данных). Такие системы характеризуются следующими признаками: Добавление в систему новых данных происходит редко и крупными блоками. Данные, добавленные в систему, обычно никогда не удаляются. Перед загрузкой данные проходят различные процедуры "очистки«. Запросы к системе являются нерегламентированными. Скорость выполнения запросов важна, но не критична. В системах OLAP, использующих реляционную модель данных (ROLAP), данные целесообразно хранить в виде слабо нормализованных отношений, содержащих заранее вычисленные основные итоговые данные.

31 Выводы При разработке БД выделяют уровни моделирования: Сама предметная область Модель предметной области Логическая модель данных Физическая модель данных Собственно база данных и приложения Ключевые решения по качеству БД закладываются на этапе разработки логической модели данных. "Хорошие" модели данных должны удовлетворять критериям: Адекватность базы данных предметной области Легкость разработки и сопровождения базы данных Скорость выполнения операций обновления данных Скорость выполнения операций выборки данных