06122010 (Собственноручно набранные лекции по БД)

SeregaProMai.Narod.ru ©

Лекция от 06.12.2010. (на основе фото с лекции-презентации)

Логическое проектирование реляционных БД.

Целью логического проектирования БД является определение совокупности взаимосвязанных таблиц для представления всех необходимых данных о предметной области.

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

Основным формальным методом решения рассматриваемой задачи является нормализация отношений, предложенная Э. Ф. Коддом.

Рассмотрим процесс нормализации на примере логического проектирования базы данных предназначенной для хранения данных о поставках деталей.

Проанализируем, какие проблемы могут возникнуть, если для хранения данных использовать 1 таблицу. Ключ этой таблицы состоит из следующих атрибутов НомерП, НомерД, Дата.

Основными операциями с базой данных являются ДОБАВЛЕНИЕ (INSERT), УДАЛЕНИЕ (DELETE) и ОБНОВЛЕНИЕ (UPDATE) записей.

Если невозможно корректно выполнить эти операции или при их выполнении возможно получение противоречивых данных, то говорят, что имеет место аномалия.

АНОМАЛИЯ ДОБАВЛЕНИЯ. Нельзя добавить информацию о новой детали пока для неё не было поставок, так как будут неопределенны значения атрибутов НомерП, Дата, которые входят в состав ключа.

АНОМАЛИЯ УДАЛЕНИЯ. При удалении данных о поставщике могут быть потеряны данные о деталях, которые поставлял этот поставщик.

АНОМАЛИЯ ОБНОВЛЕНИЯ. Если от поставщика 1 поступила информация, что из-за строительства новой дороги расстояние до него (Твери) изменилось, легко получить противоречивые данные, если забыть обновить значение атрибут Расстояние для поставщика 3, который также расположен в городе Тверь.

Причиной возникновения аномалий является избыточное дублирование данных, которое имеет место в исходной таблице. Так как одна деталь может поставляться несколькими поставщиками, то имеет место избыточное дублирование данных о детали. Так как один поставщик может поставлять несколько деталей, то имеет место избыточное дублирование данных о поставщике.

В принципе слово избыточное во фразе избыточное дублирование данных является необязательным, но оно подчеркивает, что избыточное дублирование данных плохо.

Процесс нормализации заключается в разбиении исходной таблицы на несколько более простых и называется декомпозицией.

Не каждая декомпозиция является корректной. Корректной называется декомпозиция без потерь. Корректная декомпозиция - выполнение реляционного соединения JOIN. Если JOIN не дал декомпозицию, то она некорректна (с потерями).

Пример некорректной декомпозиции.

Пример корректной декомпозиции.

Причиной ошибки является наличие во втором отношении атрибутов Кол_во, Цена, которые зависят от ключа таблицы (НомерП, НомерД, Дата), а не от атрибута НомерД.

Пример декомпозиции без потерь.

Для проверки корректности декомпозиции необходимо выполнить операцию соединения (JOIN), если получим исходную таблицу, то имеет место декомпозиция без потерь.

Нормальные формы.

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

Основным понятием на основании, которого формулируется требования, предъявляемые к нормальным формам, является понятие функциональной зависимости.

Атрибут В функционально зависит от атрибута А, если по значению атрибута А можно определить значение атрибута В.

А→В (В зависит от А). А и В могут быть составными.

Например: Город→Расстояние

Отметим, что А и В могут состоять из двух и более атрибутов.

Частичной зависимостью называется зависимость не ключевого атрибута от части составного ключа.

НомерД - Название, СтатусД, Материал, Вес

НомерП - Имя, СтатусП, Город

Альтернативным вариантом является полная функциональная зависимость не ключевого атрибута от всего составного ключа.

НомерД, НомерП, Дата - Кол_во, Цена

Атрибут С зависит от атрибута А транзитивно (существует транзитивная зависимость), если для атрибута А, В, С выполняются условия А→В и В→С, но обратная зависимость отсутствует.

А →→ С

НомерП → Город

Город → Расстояние

НомерП →→ Расстояние

1-я нормальная форма

Отношение находится в 1-ой нормальной форме, если значения всех его атрибутов атомарны (неделимы) или же по-другому говорят, что речь идёт о плоских таблицах.

Пример а:

Пример а не удовлетворяет 1-ой форме (так как значение атрибута НомерД представляет собой список значений).

Пример б:

Пример б удовлетворяет 1-ой нормальной форме.

2-я нормальная форма

Не ключевые атрибуты могут находиться в полной функциональной зависимости от ключа или в частичной.

НомерП, НомерД, Дата → Кол_во, Цена (полная функциональная зависимость)

НомерД → Название, СтатусД, Материал, Вес (частичная функциональная зависимость)

НомерП → Имя, СтатусП, Город, Расстояние (частичная функциональная зависимость)

Отношение находится во 2-ой нормальной форме, если в нем отсутствуют частичные функциональные зависимости.

Правило декомпозиции (правило приведения таблицы ко 2-ой нормальной форме).

Атрибуты, находящиеся в частичной зависимости выделяются в отдельные отношения вместе с той частью ключа, от которого они зависят (при этом атрибут, входящий в состав ключа остаётся в исходном отношении).

3-я нормальная форма

Для того, чтобы отношение находилось в 3-ей форме, оно должно удовлетворять требованиям, предъявляемым ко 2-ой нормальной форме и в нём должны отсутствовать транзитивные функции зависимости.

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

В таблице Поставщики избыточное дублирование данных, которое заключается в том, что если в одном городе располагаются несколько поставщиков, то для каждого поставщика будет дублироваться расстояние до этого города.

Причиной является следующая транзитивная зависимость:

НомерП → Город → Расстояние

После декомпозиции получим:

Пример нормализации отношений:

Код тура и паспорт туриста = ключ.

Нормальная форма Бойся-Кодда.

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

Примеры нормализации отношений.

Рассмотрим задачу проектирования БД для туристической фирмы, в которой содержаться данные о реализации туров. В таблице 1 представлены данные, которые должны храниться в БД.

В этой таблице можно выявить несколько видов избыточного дублирования данных.

С одним отелем может быть связано несколько туров, следовательно, для каждого тура будут дублироваться данные об отеле.

Так как один тур могут приобрести несколько туристов, то для каждого туриста будут дублироваться данные о туре.

Один турист может приобрести несколько туров, поэтому для каждого тура, который он приобрел, будут дублироваться данные об этом туристе.

Полная функциональная зависимость.

Код тура, Паспорт → Дата приобретения, Цена.

Обсудим решение включить в состав атрибутов находящихся в полной функциональной зависимости от ключа атрибут Цена. На первый взгляд может показаться, что этот атрибут зависит от атрибута Код тура. Но анализ предметной области показывает, что цена может зависеть от того, кто приобретает тур (персональная скидка), а так же от даты приобретения тура.

Частичные функциональные зависимости.

Код тура → № отеля, Название отеля, Страна, Категория, Телефон отеля, Начало, Дней,

Питание.

Паспорт → ФИО туриста, Адрес туриста, Телефон туриста

Может появиться желание включить в состав частичных зависимостей следующую зависимость.

№ отеля - Название отеля, Страна, Категория, Телефон отеля

Делать этого не следует, так как атрибут № отеля не входит в состав --- зависимость, несомненно существует но мы будем её рассматривать --- этапе нормализации.

Причиной является наличие транзитивной зависимости.

Тур → № отеля → Название отеля, Страна, Категория, Телефон отеля

Выделив эту зависимость в отдельное отношение, получим:

Анализ этих таблиц не выявляет избыточного дублирования данных.

5