1 БАЗЫ ДАННЫХ Функциональные зависимости и их использование в базах данных ЗАНЯТИЕ 4 ПУГАЧЁВ Ю.В. Учитель информатики Харьковская общеобразовательная школа. - презентация

1 1 БАЗЫ ДАННЫХ Функциональные зависимости и их использование в базах данных ЗАНЯТИЕ 4 ПУГАЧЁВ Ю.В. Учитель информатики Харьковская общеобразовательная школа І-ІІІ ступеней 60 Харьковского городского совета Харьковской области

2 2 Цель: Содержание: Изучить методы проектирования реляционной базы данных путем нормализации отношений, рассмотреть возможные аномалии баз данных и требования к нормальным формам, позволяющим устранить эти аномалии. цель нормализации; аномалии баз данных; идентификация функциональных зависимостей; нормальные формы отношений; эффективность нормализации и примеры нормализации баз данных.

3 3 Процесс проектирования БД – процесс нормализации исходных схем отношений, причем каждая следующая нормальная форма обладает лучшими свойствами, чем предыдущая. Нормализация Отношение 1 Отношение n Отношение 1 Отношение 2 Отношение m Нормализация – это разбиение таблицы на две или более, обладающих лучшими свойствами при включении, изменении и удалении данных. Цель нормализации – получение такого проекта базы данных, в котором каждый факт появляется лишь в одном месте (исключена избыточность информации). Проблемы проектирования баз данных Проблема логического проектирования БД – каким образом отобразить объекты предметной области в абстрактные объекты модели данных, чтобы это отображение не противоречило семантике предметной области Проблема физического проектирования БД – как обеспечить эффективность выполнения запросов к базе данных, как расположить данные во внешней памяти

4 4 Аномалия – противоречие между моделью предметной области и физической моделью данных, т.е. неадекватность модели данных предметной области. Виды аномалий : Аномалии вставки (INSERT) – неадекватности, проявляющиеся при добавлении в БД новой записи; Аномалии обновления (UPDATE) - неадекватности, проявляющиеся при изменении данных в БД; Аномалии удаления (DELETE) – неадекватности, проявляющиеся при удалении записей в БД. Причины аномалий: хранение в одном отношении разнородной информации; избыточность данных. Н_СОТР*ФАМН_ОТДТЕЛН_ПРО*ПРОЕКТ Н_ЗАДАН 1Иванов Космос1 1Иванов Климат1 2Петров Космос2 3Сидоров Космос3 3Сидоров Климат2 Аномалии баз данных Отделы-Сотрудники- Проекты-Задания

5 5 Функциональная зависимость Атрибут В отношения функционально зависит от атрибута А того же отношения (атрибуты могут быть составными) в том и только в том случае, когда в любой заданный момент времени для каждого из различных значений атрибута А обязательно существует только одно из различных значений атрибута В. Функциональные зависимости ФИОДолжностьРазрядОклад Иванов И.И.Слесарь3150 Петров П.П.Слесарь2200 Сидоров С.С.Токарь2250 Андреев А.А.Слесарь4100 Александров А.А.ЭлектрикНет разряда200 Романов Р.Р.Токарь3150 Николаетв Н.Н.Токарь2250 Сергеев С.С.Слесарь1300 Павлов П.П.Токарь1300 Евгеньев Е.Е.Слесарь4100 Полная функциональная зависимость Атрибут В находится в полной функциональной зависимости от составного атрибута А, если он функционально зависит от А и не зависит функционально от любого подмножества атрибута А. зачетки ФИОДисциплинаДатаОценка Иванов И.И.Мат.анализ Отл Иванов И.И.Ин. яз Хор Петров П.П.Мат.анализ Хор Петров П.П.Ин. яз Отл Сидорова С.С.Мат.анализ Отл Сидорова С.С.Ин. яз Уд. Экзаменационная ведомость Бухгалтерская ведомость

6 6 Неключевой атрибут - любой атрибут отношения, не входящий в состав первичного ключа. Взаимно независимые атрибуты Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других. Транзитивная функциональная зависимость Атрибут В отношения транзитивно функционально зависит от атрибута А того же отношения (атрибуты могут быть составными) в том и только в том случае, если существует такой атрибут С, что имеются функциональные зависимости между атрибутами А и С, а также между В и С. Дисциплина Преподавател ь Учебник ИнформатикаШипилов П.А. Форсайт Р. Паскаль для всех ИнформатикаШипилов П.А.Уэйт М. и др. Язык Си ИнформатикаГолованев Г.Л. Форсайт Р. Паскаль для всех ИнформатикаГолованев Г.Л.Уэйт М. и др. Язык Си... Многозначная зависимость Атрибут А многозначно определяет атрибут В того же отношения, если для каждого значения атрибута А существует хорошо определенное множество соответствующих значений В. Многозначная и транзитивная зависимости

7 7 Задание: определите функциональные зависимости Первичный ключ: Н_СОТР,Н_ПРО Зависимости от первичного ключа: 1) {Н_СОТР, Н_ПРО} -> ФАМ 2) {Н_СОТР, Н_ПРО} -> Н_ОТД 3) {Н_СОТР, Н_ПРО} -> ТЕЛ 4) {Н_СОТР, Н_ПРО} -> ПРОЕКТ 5) {Н_СОТР, Н_ПРО} -> Н_ЗАДАН Зависимость номера телефона от номера отдела: 10) Н_ОТД -> ТЕЛ Н_СОТРФАМН_ОТДТЕЛН_ПРОПРОЕКТН_ЗАДАН 1Иванов Космос1 1Иванов Климат1 2Петров Космос2 3Сидоров Космос3 3Сидоров Климат2 Зависимости от табельного номера сотрудника: 6) Н_СОТР -> ФАМ 7) Н_СОТР -> Н_ОТД 8) Н_СОТР -> ТЕЛ Зависимость наименования проекта от номера проекта: 9) Н_ПРО -> ПРОЕКТ

8 8 первая нормальная форма (1NF); вторая нормальная форма (2NF); третья нормальная форма (3NF); нормальная форма Бойса-Кодда (BCNF); четвертая нормальная форма (4 NF); пятая нормальная форма, или нормальная форма проекции-соединения ); Основные свойства нормальных форм: каждая следующая нормальная форма в некотором смысле лучше предыдущей; при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются. Н_СОТРФАМН_ОТДТЕЛН_ПРОПРОЕКТН_ЗАДАН 1Иванов Космос1 1Иванов Климат1 2Петров Космос2 3Сидоров Космос3 3Сидоров Климат2 Отделы-Сотрудники-Проекты-Задания Основные нормальные формы

9 9 Первая нормальная форма (1НФ) - это обычное отношение, т.е. отношение, удовлетворяющее следующим свойствам: В отношении нет одинаковых кортежей. Кортежи не упорядочены. Атрибуты не упорядочены и различаются по наименованию. Все значения атрибутов атомарны. Н_СОТРФАМН_ОТДТЕЛН_ПРОПРОЕКТН_ЗАДАН 1Иванов Космос1 1Иванов Климат1 2Петров Космос2 3Сидоров Космос3 3Сидоров Климат2 Отделы-Сотрудники- Проекты-Задания Н_ОТДТЕЛН_СОТРФАМН_ПРОПРОЕКТН_ЗАДАН Иванов 1Космос1 2Климат1 2Петров1Космос Сидоров 1Космос3 2Климат2 Первая нормальная форма - это когда на пересечении любого столбца и любой записи находится атомарное значение. 1НФ (Первая Нормальная Форма)

10 10 2НФ (Вторая Нормальная Форма) Н_СОТР*ФАМН_ОТДТЕЛН_ПРО*ПРОЕКТН_ЗАДАН 1Иванов Космос1 1Иванов Климат1 2Петров Космос2 3Сидоров Космос3 3Сидоров Климат2 Отделы-Сотрудники- Проекты-Задания Отношение находится во второй нормальной форме (2NF) в том и только в том случае, когда оно находится в 1NF, и каждый неключевой атрибут полностью функционально зависит от первичного ключа отношения. Функциональные зависимости : Зависимость атрибутов от ключа отношения: {Н_СОТР, Н_ПРО} -> ФАМ {Н_СОТР, Н_ПРО} -> Н_ОТД {Н_СОТР, Н_ПРО} -> ТЕЛ {Н_СОТР, Н_ПРО} -> ПРОЕКТ {Н_СОТР, Н_ПРО} -> Н_ЗАДАН Зависимость атрибутов, характеризующих сотрудника, от табельного номера сотрудника: Н_СОТР -> ФАМ Н_СОТР -> Н_ОТД Н_СОТР -> ТЕЛ Зависимость наименования проекта от номера проекта: Н_ПРО - > ПРОЕКТ Зависимость номера телефона от номера отдела: Н_ОТД -> ТЕЛ

11 11 Н_СОТР*ФАМН_ОТДТЕЛН_ПРО*ПРОЕКТН_ЗАДАН 1Иванов Космос1 1Иванов Климат1 2Петров Космос2 3Сидоров Космос3 3Сидоров Климат2 Отделы-Сотрудники-Проекты-Задания Заменить T(K1,K2, F1, F2), первичный ключ (К1,К2), ФЗ K1->F1 на T1(K1,F1), первичный ключ К1, и T2(K1,K2,F2), первичный ключ (К1,К2). Вторая нормальная форма : Н_СОТРФАМН_ОТДТЕЛ 1Иванов Петров Сидоров Н_ПРОПРОЕКТ 1Космос 2Климат Н_СОТРН_ПРОН_ЗАДАН Отделы-СотрудникиПроекты Задания Процедура приведения ко 2НФ

12 12 3НФ (Третья Нормальная Форма) Заменить T(K,F1,F2), первичный ключ К, ФЗ F1->F2 на T1(F1,F2), первичный ключ F1, и T2(K,F1), первичный ключ К. Н_СОТРФАМН_ОТДТЕЛ 1Иванов Петров Сидоров Н_ПРОПРОЕКТ 1Космос 2Климат Н_СОТРН_ПРОН_ЗАДАН Отделы-СотрудникиПроекты Задания Отношение находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 2NF и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Н_ОТДТЕЛ Н_СОТРФАМН_ОТД 1Иванов1 2Петров1 3Сидоров2 Отделы Сотрудники Процедура приведения к 3НФ:

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

14 14 Эффективность нормализации Критерий Отношения слабо нормализованы (1НФ, 2НФ) Отношения нормализованы (3НФ) Отношения сильно нормализованы (4НФ, 5НФ) Адекватность базы данных предметной области ХУЖЕ (-)ЛУЧШЕ (+) Легкость разработки и сопровождения базы данных СЛОЖНЕЕ (-)ЛЕГЧЕ (+) СЛОЖНЕЕ (-) Скорость выполнения вставки, обновления, удаления МЕДЛЕННЕЕ (-)БЫСТРЕЕ (+) МЕДЛЕННЕЕ (-) Скорость выполнения выборки данных БЫСТРЕЕ (+)МЕДЛЕННЕЕ (-)