Sql и базы данных
 Скачать 1.44 Mb.
|
|
27. Что такое нормализация и денормализация? Расскажите про 3 нормальные формы? Нормализация - это процесс преобразования отношений базы данных к виду, отвечающему нормальным формам (пошаговый, обратимый процесс замены исходной схемы другой схемой, в которой наборы данных имеют более простую и логичную структуру). Нормализация – это и есть здравый смысл в проектировании БД. Цель нормализации: исключить избыточное дублирование данных, которое является причиной аномалий, возникших при добавлении, редактировании и удалении кортежей (строк таблицы). Назначение процесса нормализации заключается в следующем: - исключение некоторых типов избыточности (чтобы в каждом отношении хранились только первичные факты (то есть факты, не выводимые из других хранимых фактов); - устранение некоторых аномалий обновления; - разработка проекта базы данных, который является достаточно «качественным» представлением реального мира, интуитивно понятен и может служить хорошей основой для последующего расширения; - упрощение процедуры применения необходимых ограничений целостности. нормальные формы: ● Первая нормальная форма (1NF) - Отношение находится в 1NF, если значения всех его атрибутов атомарны (неделимы). ● Вторая нормальная форма (2NF) - Отношение находится в 2NF, если оно находится в 1NF, и при этом все неключевые атрибуты зависят только от ключа целиком, а не от какой-то его части. ● Третья нормальная форма (3NF) - Отношение находится в 3NF, если оно находится в 2NF и все неключевые атрибуты не зависят друг от друга. ● Четвёртая нормальная форма (4NF) - Отношение находится в 4NF , если оно находится в 3NF и если в нем не содержатся независимые группы атрибутов, между которыми существует отношение «многие-ко-многим». ● Пятая нормальная форма (5NF) - Отношение находится в 5NF, когда каждая нетривиальная зависимость соединения в ней определяется потенциальным ключом (ключами) этого отношения. ● Шестая нормальная форма (6NF) - Отношение находится в 6NF, когда она удовлетворяет всем нетривиальным зависимостям соединения, т.е. когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6NF, также находится и в 5NF. Введена как обобщение пятой нормальной формы для хронологической базы данных. ● Нормальная форма Бойса-Кодда, усиленная 3 нормальная форма (BCNF) - Отношение находится в BCNF, когда каждая её нетривиальная и неприводимая слева функциональная зависимость имеет в качестве своего детерминанта некоторый потенциальный ключ. ● Доменно-ключевая нормальная форма (DKNF) - Отношение находится в DKNF, когда каждое наложенное на нее ограничение является логическим следствием ограничений доменов и ограничений ключей, наложенных на данное отношение. Первая нормальная форма Отношение находится в 1НФ, если все его атрибуты являются простыми, все используемые домены должны содержать только скалярные значения (может быть выражено одним, как правило, действительным числом). Не должно быть повторений строк в таблице. Например, есть таблица «Автомобили»: Фирма Модели BMW M5, X5M, M1 Nissan GT-R Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным. Преобразуем таблицу к 1НФ (в одной ячейке – одно значение): Фирма Модели BMW M5 BMW X5M BMW M1 Nissan GT-R Вторая нормальная форма (прослеживается 2 зависимости из которых можно выделить 2-ю таблицу) Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут неприводимо зависит от Первичного Ключа(ПК). Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость. Например, дана таблица: Модель Фирма Цена Скидка M5 BMW 5500000 5% X5M BMW 6000000 5% M1 BMW 2500000 5% GT-R Nissan 5000000 10% Таблица находится в первой нормальной форме, но не во второй. Цена машины зависит от модели и фирмы. Скидка зависят от фирмы, то есть зависимость от первичного ключа неполная. Исправляется это путем декомпозиции на два отношения, в которых не ключевые атрибуты зависят от ПК. Модель Фирма Цена M5 BMW 5500000 X5M BMW 6000000 M1 BMW 2500000 GT-R Nissan 5000000 Фирма Скидка BMW 5% Nissan 10% Третья нормальная форма Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа. Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых может относиться к нескольким записям таблицы в отдельные таблицы. Рассмотрим таблицу: Модель Магазин Телефон BMW Риал-авто 87-33-98 Audi Риал-авто 87-33-98 Nissan Некст-Авто 94-54-12 Таблица находится во 2НФ, но не в 3НФ. В отношении атрибут «Модель» является первичным ключом. Личных телефонов у автомобилей нет, и телефон зависит исключительно от магазина. Таким образом, в отношении существуют следующие функциональные зависимости: Модель → Магазин, Магазин → Телефон, Модель → Телефон. Зависимость Модель → Телефон является транзитивной, следовательно, отношение не находится в 3НФ. В результате разделения исходного отношения получаются два отношения, находящиеся в 3НФ: Магазин Телефон Риал-авто 87-33-98 Некст-Авто 94-54-12 Модель Магазин BMW Риал-авто Audi Риал-авто Nissan Некст-Авто Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы) Определение 3НФ не совсем подходит для следующих отношений: 1) отношение имеет два или более потенциальных ключа; 2) два и более потенциальных ключа являются составными; 3) они пересекаются, т.е. имеют хотя бы один общий атрибут. Для отношений, имеющих один потенциальный ключ (первичный), НФБК является 3НФ. Отношение находится в НФБК, когда каждая нетривиальная и неприводимая слева функциональная зависимость обладает потенциальным ключом в качестве детерминанта. Предположим, рассматривается отношение, представляющее данные о бронировании стоянки на день: Номер стоянки Время начала Время окончания Тариф 1 09:30 10:30 Бережливый 1 11:00 12:00 Бережливый 1 14:00 15:30 Стандарт 2 10:00 12:00 Премиум-В 2 12:00 14:00 Премиум-В 2 15:00 18:00 Премиум-А Тариф имеет уникальное название и зависит от выбранной стоянки и наличии льгот, в частности: • «Бережливый»: стоянка 1 для льготников • «Стандарт»: стоянка 1 для не льготников • «Премиум-А»: стоянка 2 для льготников • «Премиум-B»: стоянка 2 для не льготников. Таким образом, возможны следующие составные первичные ключи: {Номер стоянки, Время начала}, {Номер стоянки, Время окончания}, {Тариф, Время начала}, {Тариф, Время окончания}. Отношение находится в 3НФ. Требования второй нормальной формы выполняются, так как все атрибуты входят в какой-то из потенциальных ключей, а неключевых атрибутов в отношении нет. Также нет и транзитивных зависимостей, что соответствует требованиям третьей нормальной формы. Тем не менее, существует функциональная зависимость Тариф → Номер стоянки, в которой левая часть (детерминант) не является потенциальным ключом отношения, то есть отношение не находится в нормальной форме Бойса — Кодда. Недостатком данной структуры является то, что, например, по ошибке можно приписать тариф «Бережливый» к бронированию второй стоянки, хотя он может относиться только к первой стоянки. Можно улучшить структуру с помощью декомпозиции отношения на два и добавления атрибута Имеет льготы, получив отношения, удовлетворяющие НФБК (подчёркнуты атрибуты, входящие в первичный ключ.): Тарифы Тариф Номер стоянки Имеет льготы Бережливый 1 Да Стандарт 1 Нет Премиум-А 2 Да Премиум-В 2 Нет Бронирование Тариф Время начала Время окончания Бережливый 09:30 10:30 Бережливый 11:00 12:00 Стандарт 14:00 15:30 Премиум-В 10:00 12:00 Премиум-В 12:00 14:00 Премиум-А 15:00 18:00 Четвертая нормальная форма Отношение находится в 4НФ, если оно находится в НФБК и все нетривиальные многозначные зависимости фактически являются функциональными зависимостями от ее потенциальных ключей. В отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С. Предположим, что рестораны производят разные виды пиццы, а службы доставки ресторанов работают только в определенных районах города. Составной первичный ключ соответствующей переменной отношения включает три атрибута: {Ресторан, Вид пиццы, Район доставки}. Такая переменная отношения не соответствует 4НФ, так как существует следующая многозначная зависимость: {Ресторан} → {Вид пиццы} {Ресторан} → {Район доставки} То есть, например, при добавлении нового вида пиццы придется внести по одному новому кортежу для каждого района доставки. Возможна логическая аномалия, при которой определенному виду пиццы будут соответствовать лишь некоторые районы доставки из обслуживаемых рестораном районов. Для предотвращения аномалии нужно декомпозировать отношение, разместив независимые факты в разных отношениях. В данном примере следует выполнить декомпозицию на {Ресторан, Вид пиццы} и {Ресторан, Район доставки}. Однако, если к исходной переменной отношения добавить атрибут, функционально зависящий от потенциального ключа, например цену с учётом стоимости доставки ({Ресторан, Вид пиццы, Район доставки} → Цена), то полученное отношение будет находиться в 4НФ и его уже нельзя подвергнуть декомпозиции без потерь. Пятая нормальная форма Отношения находятся в 5НФ, если оно находится в 4НФ и отсутствуют сложные зависимые соединения между атрибутами. Если «Атрибут_1» зависит от «Атрибута_2», а «Атрибут_2» в свою очередь зависит от «Атрибута_3», а «Атрибут_3» зависит от «Атрибута_1», то все три атрибута обязательно входят в один кортеж. Это очень жесткое требование, которое можно выполнить лишь при дополнительных условиях. На практике трудно найти пример реализации этого требования в чистом виде. Например, некоторая таблица содержит три атрибута «Поставщик», «Товар» и «Покупатель». Покупатель_1 приобретает несколько Товаров у Поставщика_1. Покупатель_1 приобрел новый Товар у Поставщика_2. Тогда в силу изложенного выше требования Поставщик_1 обязан поставлять Покупателю_1 тот же самый новый Товар, а Поставщик_2 должен поставлять Покупателю_1, кроме нового Товара, всю номенклатуру Товаров Поставщика_1. Этого на практике не бывает. Покупатель свободен в своем выборе товаров. Поэтому для устранения отмеченного затруднения все три атрибута разносят по разным отношениям (таблицам). После выделения трех новых отношений (Поставщик, Товар и Покупатель) необходимо помнить, что при извлечении информации (например, о покупателях и товарах) необходимо в запросе соединить все три отношения. Любая комбинация соединения двух отношений из трех неминуемо приведет к извлечению неверной (некорректной) информации. Некоторые СУБД снабжены специальными механизмами, устраняющими извлечение недостоверной информации. Тем не менее, следует придерживаться общей рекомендации: структуру базы данных строить таким образом, чтобы избежать применения 4НФ и 5НФ. Пятая нормальная форма ориентирована на работу с зависимыми соединениями. Указанные зависимые соединения между тремя атрибутами встречаются очень редко. Зависимые соединения между четырьмя, пятью и более атрибутами указать практически невозможно. Доменно-ключевая нормальная форма Переменная отношения находится в ДКНФ тогда и только тогда, когда каждое наложенное на неё ограничение является логическим следствием ограничений доменов и ограничений ключей, наложенных на данную переменную отношения. Ограничение домена – ограничение, предписывающее использовать для определённого атрибута значения только из некоторого заданного домена. Ограничение по своей сути является заданием перечня (или логического эквивалента перечня) допустимых значений типа и объявлением о том, что указанный атрибут имеет данный тип. Ограничение ключа – ограничение, утверждающее, что некоторый атрибут или комбинация атрибутов является потенциальным ключом. Любая переменная отношения, находящаяся в ДКНФ, обязательно находится в 5НФ. Однако не любую переменную отношения можно привести к ДКНФ. Шестая нормальная форма Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ. Идея «декомпозиции до конца» выдвигалась до начала исследований в области хронологических данных, но не нашла поддержки. Однако для хронологических баз данных максимально возможная декомпозиция позволяет бороться с избыточностью и упрощает поддержание целостности базы данных. Для хронологических баз данных определены U_операторы, которые распаковывают отношения по указанным атрибутам, выполняют соответствующую операцию и упаковывают полученный результат. В данном примере соединение проекций отношения должно производится при помощи оператора U_JOIN. Работники Таб.№ Время Должность Домашний адрес 6575 01-01-2000:10-02-2003 слесарь ул.Ленина,10 6575 11-02-2003:15-06-2006 слесарь ул.Советская,22 6575 16-06-2006:05-03-2009 бригадир ул.Советская,22 Переменная отношения «Работники» не находится в 6НФ и может быть подвергнута декомпозиции на переменные отношения «Должности работников» и «Домашние адреса работников». Должности работников Таб.№ Время Должность 6575 01-01-2000:10-02-2003 слесарь 6575 16-06-2006:05-03-2009 бригадир Домашние адреса работников Таб.№ Время Домашний адрес 6575 01-01-2000:10-02-2003 ул.Ленина,10 6575 11-02-2003:15-06-2006 ул.Советская,22 28. Что такое TIMESTAMP? DATETIME предназначен для хранения целого числа: YYYYMMDDHHMMSS. И это время не зависит от временной зоны настроенной на сервере. Размер: 8 байт TIMESTAMP хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича. При получении из базы отображается с учётом часового пояса. Размер: 4 байта |