Нормализация данных в базе данных доклад
Обновлено: 19.05.2024
Ссылочной целостностью называют особый механизм, осуществляемый средствами СУБД или программистом, ответственный за поддержание непротиворечивых данных в связанных релятивными отношениями таблицах . Ссылочная целостность подразумевает, что в таблицах , имеющих релятивные связи, нет ссылок на несуществующие записи. Взгляните на рис. 1.3. Если мы удалим из списка студента Иванова И.И., и при этом не изменим таблицу со сданными экзаменами, ссылочная целостность будет нарушена, в таблице с экзаменами появится "мусор" - данные, на которые не ссылается ни одна запись из таблицы студентов. Ссылочная целостность будет нарушена.
Таким образом, если мы удаляем из списка студента Иванова И.И., следует позаботиться о том, чтобы из таблицы со сданными экзаменами также были удалены все записи, на которые ранее ссылалась удаленная запись главной таблицы. Существует несколько видов изменений данных, которые могут привести к нарушению ссылочной целостности:
- Удаляется запись в родительской таблице , но не удаляются соответствующие связанные записи в дочерней таблице .
- Изменяется запись в родительской таблице , но не изменяются соответствующие ключи в дочерней таблице .
- Изменяется ключ в дочерней таблице , но не изменяется значение связанного поля родительской таблицы.
Многие СУБД блокируют действия пользователя, которые могут привести к нарушению связей. Нарушение хотя бы одной такой связи делает информацию в БД недостоверной. Если мы, например, удалили Иванова И.И., то теперь номер 1 принадлежит Петрову П.П.. Имеющиеся связи указывают, что он сдал экзамены по математике и физике, но не сдавал экзаменов по русскому языку и литературе. Достоверность данных нарушена. Конечно, в таких случаях в качестве ключа обычно используют счетчик - поле автоинкрементного типа. Если удалить запись со значением 1, то другие записи не изменят своего значения, значение 1 просто невозможно будет присвоить какой-то другой записи, оно будет отсутствовать в таблице. Путаницы в связях не случится, однако все равно подчиненная таблица будет иметь "потерянные" записи, не связанные ни с какой записью главной таблицы. Механизм ссылочной целостности должен запрещать удаление записи в главной таблице до того, как будут удалены все связанные с ней записи в дочерней таблице .
Нормализация базы данных
Каждый программист обычно по-своему проектирует базу данных для программы, над которой работает. У одних это получается лучше, у других - хуже. Качество спроектированной БД в немалой степени зависит от опыта и интуиции программиста, однако существуют некоторые правила, помогающие улучшить проектируемую БД . Такие правила носят рекомендательный характер, и называются нормализацией базы данных .
Процесс нормализации данных заключается в устранении избыточности данных в таблицах.
Существует несколько нормальных форм, но для практических целей интерес представляют только первые три нормальные формы.
Первая нормальная форма ( 1НФ ) требует, чтобы каждое поле таблицы БД было неделимым (атомарным) и не содержало повторяющихся групп.
Неделимость означает, что в таблице не должно быть полей , которые можно разбить на более мелкие поля. Например, если в одном поле мы объединим фамилию студента и группу, в которой он учится, требование неделимости соблюдаться не будет. Первая нормальная форма требует, чтобы мы разбили эти данные по двум полям.
Под понятием повторяющиеся группы подразумевают поля, содержащие одинаковые по смыслу значения. Взгляните на рисунок:
Верно, такую таблицу можно сделать, однако она нарушает правило первой нормальной формы. Поля "Студент 1", "Студент 2" и "Студент 3" содержат одинаковые по смыслу объекты, их требуется поместить в одно поле "Студент", как в рисунке 1.4. Ведь в группе не бывает по три студента, правда? Представляете, как будет выглядеть таблица , содержащая данные на тридцать студентов? Это тридцать одинаковых полей ! В приведенном выше рисунке поля описывают студентов в формате "Фамилия И.О.". Однако если оператор будет вводить эти описания в формате "Фамилия Имя Отчество", то нарушается также правило неделимости. В этом случае каждое такое поле следует разбить на три отдельных поля, так как поиск может вестись не только по фамилии, но и по имени или по отчеству.
Вторая нормальная форма ( 2НФ ) требует, чтобы таблица удовлетворяла всем требованиям первой нормальной формы, и чтобы любое не ключевое поле однозначно идентифицировалось полным набором ключевых полей . Рассмотрим пример: некоторые студенты посещают спортивные платные секции, и ВУЗ взял на себя оплату этих секций. Взгляните на рисунок:
В чем здесь нарушение? Ключом этой таблицы служат поля "№ студента" - "Секция". Однако данная таблица также содержит отношение "Секция" - " Плата ". Если мы удалим запись студента № 110, то потеряем данные о стоимости секции по скейтборду. А после этого мы не сможем ввести информацию об этой секции, пока в нее не запишется хотя бы один студент. Говорят, что такое отношение подвержено как аномалии удаления , так и аномалии вставки.
В соответствие с требованиями второй нормальной формы, каждое не ключевое поле должно однозначно зависеть от ключа. Поле " Плата " в приведенном примере содержит сведения от стоимости данной секции, и ни коим образом не зависит от ключа - номера студента. Таким образом, чтобы удовлетворить требованию второй нормальной формы, данную таблицу следует разбить на две таблицы, каждая из которых зависит от своего ключа:
Мы получили две таблицы, в каждой из которых не ключевые данные однозначно зависят от своего ключа.
Третья нормальная форма ( 3НФ ) требует, чтобы в таблице не имелось транзитивных зависимостей между не ключевыми полями, то есть, чтобы значение любого поля, не входящего в первичный ключ , не зависело от другого поля, также не входящего в первичный ключ . Допустим, в нашей студенческой базе данных есть таблица с расходами на спортивные секции:
Как нетрудно заметить, ключевым полем здесь является поле "Секция". Поля " Плата " и "Кол-во студентов" зависят от ключевого поля и не зависят друг от друга. Однако поле "Общая стоимость " зависит от полей " Плата " и "Кол-во студентов", которые не являются ключевыми, следовательно, нарушается правило третьей нормальной формы.
Поле "Общая стоимость " в данном примере можно спокойно убрать из таблицы, ведь если потребуется вывести такие данные, нетрудно будет перемножить значения полей " Плата " и "Кол-во студентов", и создать для вывода вычисляемое поле .
Таким образом, нормализация данных подразумевает, что вы вначале проектируете свою базу данных: планируете, какие таблицы у вас будут, какие в них будут поля, какого типа и размера. Затем вы приводите каждую таблицу к первой нормальной форме. После этого приводите полученные таблицы ко второй, затем к третьей нормальной форме, после чего можете утверждать, что ваша база данных нормализована.
Однако такой подход имеет и недостатки: если вам требуется разработать программный комплекс для крупного предприятия, база данных будет довольно большой. При нормализации данных, вы можете получить сотни взаимосвязанных между собой таблиц. С увеличением числа нормализованных таблиц уменьшается восприятие программистом базы данных в целом, то есть вы можете потерять общее представление вашей базы данных , запутаетесь в связях. Кроме того, поиск в чересчур нормализованных данных может быть замедлен. Отсюда вывод : при работе с данными большого объема ищите компромисс между требованиями нормализации и собственным общим восприятием базы данных .
В данной теме я затрону 6 нормальных форм и методы приведения таблиц в эти формы.
Процесс проектирования БД с использование метода НФ является итерационным и заключается в последовательном переводе отношения из 1НФ в НФ более высокого порядка по определенным правилам. Каждая следующая НФ ограничивается определенным типом функциональных зависимостей и устранением соответствующих аномалий при выполнении операций над отношениями БД, а также сохранении свойств предшествующих НФ.
Используемые термины
Атрибут — свойство некоторой сущности. Часто называется полем таблицы.
Домен атрибута — множество допустимых значений, которые может принимать атрибут.
Кортеж — конечное множество взаимосвязанных допустимых значений атрибутов, которые вместе описывают некоторую сущность (строка таблицы).
Отношение — конечное множество кортежей (таблица).
Схема отношения — конечное множество атрибутов, определяющих некоторую сущность. Иными словами, это структура таблицы, состоящей из конкретного набора полей.
Проекция — отношение, полученное из заданного путём удаления и (или) перестановки некоторых атрибутов.
Нормальная форма — требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц).
Метод нормальных форм (НФ) состоит в сборе информации о объектах решения задачи в рамках одного отношения и последующей декомпозиции этого отношения на несколько взаимосвязанных отношений на основе процедур нормализации отношений.
Цель нормализации: исключить избыточное дублирование данных, которое является причиной аномалий, возникших при добавлении, редактировании и удалении кортежей(строк таблицы).
Аномалией называется такая ситуация в таблице БД, которая приводит к противоречию в БД либо существенно усложняет обработку БД. Причиной является излишнее дублирование данных в таблице, которое вызывается наличием функциональных зависимостей от не ключевых атрибутов.
Аномалии-модификации проявляются в том, что изменение одних данных может повлечь просмотр всей таблицы и соответствующее изменение некоторых записей таблицы.
Аномалии-удаления — при удалении какого либо кортежа из таблицы может пропасть информация, которая не связана на прямую с удаляемой записью.
Аномалии-добавления возникают, когда информацию в таблицу нельзя поместить, пока она не полная, либо вставка записи требует дополнительного просмотра таблицы.
Первая нормальная форма
Отношение находится в 1НФ, если все его атрибуты являются простыми, все используемые домены должны содержать только скалярные значения. Не должно быть повторений строк в таблице.
Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным. Преобразуем таблицу к 1НФ:
Вторая нормальная форма
Отношение находится во 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 |
Третья нормальная форма
Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа. Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых может относиться к нескольким записям таблицы в отдельные таблицы.
| Модель | Магазин | Телефон |
| BMW | Риал-авто | 87-33-98 |
| Audi | Риал-авто | 87-33-98 |
| Nissan | Некст-Авто | 94-54-12 |
| Магазин | Телефон |
| Риал-авто | 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 | Премиум-А |
Отношение находится в 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НФ. Однако не любую переменную отношения можно привести к ДКНФ.
Шестая нормальная форма
Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 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 |
Должности работников
| Таб.№ | Время | Должность |
| 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 |
Литература
Начну я с нормализации баз данных. В этом материале мы поговорим в целом о процессе нормализации, узнаем, зачем проводить нормализацию базы данных, что такое нормальная форма базы данных, а также какие нормальные формы существуют. В следующих материалах я подробно и с примерами расскажу про каждую нормальную форму.
Реляционная база данных
В целом под базой данных можно понимать любой набор информации, которую можно найти в этой базе данных и воспользоваться ей, однако если говорить в контексте SQL, то речь будет идти, конечно, о реляционных базах данных, а что же это такое?
Реляционная база данных – это упорядоченная информация, связанная между собой определёнными отношениями.
Логически такая база данных представлена в виде таблиц, в которых и лежит вся эта информация.
Примечание! Если Вас интересует язык SQL, рекомендую пройти мой онлайн-курс по основам SQL, который ориентирован на изучение SQL как стандарта, таким образом, Вы сможете работать в любой системе управления базами данных. Курс включает много практики: онлайн-тестирование, задания и многое другое.
Нормализация баз данных
Нормализация – это процесс удаления избыточных данных.
Также нормализацию можно рассматривать и с позиции проектирования базы данных, в таком случае мы можем сформулировать определение нормализации следующим образом.
Нормализация – это метод проектирования базы данных, который позволяет привести базу данных к минимальной избыточности.
Избыточность устраняется, как правило, за счёт декомпозиции отношений (таблиц), т.е. разбиения одной таблицы на несколько.
Зачем нормализовать базу данных?
У Вас может возникнуть вопрос – а зачем вообще нормализовать базу данных и бороться с этой избыточностью?
Дело в том, что избыточность данных создает предпосылки для появления различных аномалий, снижает производительность, и делает управление данными не гибким и не очень удобным. Отсюда можно сделать вывод, что нормализация нужна для:
- Устранения аномалий
- Повышения производительности
- Повышения удобства управления данными
Теперь давайте поговорим о самой избыточности данных, что же это такое.
Избыточность данных – это когда одни и те же данные хранятся в базе в нескольких местах, именно это и приводит к аномалиям.
Так как в этом случае необходимо добавлять, изменять или удалять одни и те же данные в нескольких местах. Например, если не выполнить операцию в каком-нибудь одном месте, то возникает ситуация, когда одни данные не соответствуют вроде как точно таким же данным в другом месте.
Давайте рассмотрим пример. Допустим, у нас есть следующая таблица, она хранит информацию о предметах мебели, в частности наименование предмета и материал, из которого изготовлен этот предмет.
| Идентификатор предмета | Наименование предмета | Материал |
| 1 | Стул | Металл |
| 2 | Стол | Массив дерева |
| 3 | Кровать | ЛДСП |
| 4 | Шкаф | Массив дерева |
| 5 | Комод | ЛДСП |
| Идентификатор предмета | Наименование предмета | Материал |
| 1 | Стул | Металл |
| 2 | Стол | Натуральное дерево |
| 3 | Кровать | ЛДСП |
| 4 | Шкаф | Массив дерева |
| 5 | Комод | ЛДСП |
| Идентификатор предмета | Наименование предмета | Материал |
| 1 | Стул | Металл |
| 2 | Стол | Натуральное дерево |
| 3 | Кровать | ЛДСП |
| 4 | Шкаф | Массив дерева |
| 5 | Комод | ЛДСП |
| 6 | Тумба | Дерево |
Однако по своей сути это один и тот же материал, мы просто решили или подкорректировать его название, или ошиблись при добавлении новой записи. Это и есть аномалия, когда одни данные в одном месте не соответствуют вроде как точно таким же данным в другом месте. Это всего лишь один вид аномалии, однако в процессе добавления, изменения и удаления данных может возникать много других противоречивых ситуаций, т.е. аномалий.
При этом, обязательно стоит отметить, что в нашей таблице всего 5 записей, а теперь представьте, что их миллион!
Именно поэтому мы должны устранять избыточность данных в базе, т.е. проводить так называемую нормализацию базы данных.
В данном конкретном случае мы должны название материала, из которого изготовлены предметы мебели, вынести в отдельную таблицу, а в таблице с предметами сделать всего лишь ссылку на нужный материал, тем самым, соотнеся эту ссылку с исходной записью, мы будем понимать, из какого материала сделан тот или иной предмет.
| Идентификатор предмета | Наименование предмета | Идентификатор материала |
| 1 | Стул | 2 |
| 2 | Стол | 1 |
| 3 | Кровать | 3 |
| 4 | Шкаф | 1 |
| 5 | Комод | 3 |
Материалы, из которых изготовлены предметы мебели.
| Идентификатор материала | Материал |
| 1 | Массив дерева |
| 2 | Металл |
| 3 | ЛДСП |
Таким образом, представляя материалы в виде отдельной сущности и создавая для нее отдельную таблицу, мы устраняем описанную выше аномалию.
Другими словами, каждая сущность должна храниться отдельно, а в случае необходимости использования этой сущности в другой таблице на нее делается всего лишь ссылка, т.е. выстраивается связь.
Нормальные формы базы данных
В целом процесс нормализации базы данных выглядит следующим образом: мы, следуя определённым правилам и соблюдая определенные требования, проектируем таблицы в базе данных.
При этом все эти правила и требования можно сгруппировать в несколько наборов, и если спроектировать базу данных с соблюдением всех правил и требований, которые включаются в тот или иной набор, то база данных будет находиться в определённом состоянии, т.е. форме, и такая форма называется нормальная форма базы данных.
Иными словами, следуя определённым правилам и соблюдая определенные требования мы приводим базу данных к определенной нормальной форме.
Нормальная форма базы данных – это набор правил и критериев, которым должна отвечать база данных.
Каждая следующая нормальная форма содержит более строгие правила и критерии, тем самым приводя базу данных к определённой нормальной форме мы устраняем определённый набор аномалий.
Отсюда можно сделать вывод, что чем выше нормальная форма, тем меньше аномалий в базе будет.
Процесс нормализации – это последовательный процесс приведения базы данных к эталонному виду, т.е. переход от одной нормальной формы к следующей.
Иными словами, процесс перехода от одной нормальной формы к следующей – это усовершенствование базы данных. Так как если база данных находится в какой-то определённой нормальной форме – это означает, что в базе данных отсутствует определенный вид аномалий.
Существует 5 основных нормальных форм базы данных:
- Первая нормальная форма (1NF)
- Вторая нормальная форма (2NF)
- Третья нормальная форма (3NF)
- Четвертая нормальная форма (4NF)
- Пятая нормальная форма (5NF)
Однако выделяют еще дополнительные нормальные формы:
- Ненормализованная форма или нулевая нормальная форма (UNF)
- Нормальная форма Бойса-Кодда (BCNF)
- Доменно-ключевая нормальная форма (DKNF)
- Шестая нормальная форма (6NF)
Если объединить оба этих списка и упорядочить нормальные формы от менее нормализованной до самой нормализованной, т.е. начиная с формы, при которой база данных по своей сути не является нормализованной, и заканчивая самой строгой нормальной формой, то мы получим следующий перечень:
- Ненормализованная форма или нулевая нормальная форма (UNF)
- Первая нормальная форма (1NF)
- Вторая нормальная форма (2NF)
- Третья нормальная форма (3NF)
- Нормальная форма Бойса-Кодда (BCNF)
- Четвертая нормальная форма (4NF)
- Пятая нормальная форма (5NF)
- Доменно-ключевая нормальная форма (DKNF)
- Шестая нормальная форма (6NF)
База данных считается нормализованной, если она находится как минимум в третьей нормальной форме (3NF).
В реальном мире нормализация до третьей нормальной формы (3NF) является обычной, стандартной практикой, так как 3NF устраняет достаточное количество аномалий, при этом производительность базы данных, а также удобство ее использования не снижается, что нельзя сказать о всех последующих формах.
Ситуации, при которых требуется нормализовать базу данных до четвертой нормальной формы (4NF), в реальном мире встречаются достаточно редко.
Если говорить о всех последующих нормальных формах (5NF, DKNF, 6NF), то в реальной жизни трудно даже представить ситуации, при которых потребуется нормализовать базу данных до этих форм.
Иными словами, 5NF, DKNF, 6NF – это в большей степени теоретические нормальные формы, немного отстраненные от реального мира.
Стоит отметить, что приведение базы данных к какой-то конкретной нормальной форме, обязательно требует, чтобы эта база данных уже находилась в предыдущей нормальной форме. Другими словами, если Вы хотите нормализовать базу данных до третьей нормальной формы, то база уже должна находиться во второй нормальной форме, т.е. нельзя нормализовать базу данных до третьей формы, если она еще не нормализована до второй.
Описание нормальных форм базы данных
В следующих статьях представлено подробное описание каждой нормальной формы и приведены примеры.
Нормализация базы данных - это процесс структурирования реляционной базы данных в соответствии с серией так называемых нормальных форм, чтобы уменьшить избыточность данных и улучшить целостность данных. Впервые он был предложен Эдгаром Ф. Коддом как часть его реляционной модели.
Нормализация предполагает организацию столбцов (атрибутов) и таблиц (отношений) базы данных, чтобы гарантировать, что их зависимости должным образом обеспечиваются ограничениями целостности базы данных. Это достигается путем применения некоторых формальных правил в процессе синтеза (создание нового дизайна базы данных) или декомпозиции (улучшение существующего дизайна базы данных).
Основная цель первой нормальной формы, определенной Коддом в 1970 году, состояла в том, чтобы позволить запрашивать данные и манипулировать ими с помощью "универсального языка данных", основанного на логике первого порядка. (SQL является примером такого подъязыка данных, хотя Кодд считал его языком с серьезными недостатками.)
Цели нормализации за пределами 1NF (первая нормальная форма) были сформулированы Коддом следующим образом:
- Освободить коллекцию отношений от нежелательных зависимостей вставки, обновления и удаления.
- Уменьшить необходимость реструктуризации сбора отношений, так как вводятся новые типы данных, и таким образом увеличить срок службы прикладных программ.
- Сделать реляционную модель более информативной для пользователей.
- Сделать набор отношений нейтральным по отношению к статистике запросов, где эти статистические данные могут изменяться с течением времени.
Когда делается попытка изменить (обновить, вставить или удалить) отношение, в отношениях, которые не были достаточно нормализованы, могут возникнуть следующие нежелательные побочные эффекты:
Аномалия обновления. Одна и та же информация может быть выражена в нескольких строках; поэтому обновления отношения могут привести к логическим несоответствиям. Например, каждая запись в отношении "Employees' Skills" может содержать идентификатор сотрудника, адрес сотрудника и навык; таким образом, изменение адреса для конкретного сотрудника может потребоваться применить к нескольким записям (по одной для каждого навыка). Если обновление выполнено только частично - адрес сотрудника обновляется в некоторых записях, но не в других - тогда отношение остается в несогласованном состоянии. В частности, отношение дает противоречивые ответы на вопрос, каков адрес этого конкретного сотрудника. Это явление известно как аномалия обновления.
Аномалия обновления. Employee 519 показан как имеющий разные адреса в разных записях.
Аномалия вставки. Существуют обстоятельства, при которых определенные факты не могут быть записаны вообще. Например, каждая запись в отношении "Faculty and Their Courses" может содержать идентификатор факультета, название факультета, дату приема на работу и код курса. Поэтому мы можем записать данные любого преподавателя, который преподает хотя бы один курс, но мы не можем записать вновь нанятого преподавателя, которому еще не было назначено преподавать какие-либо курсы, кроме как путем установки кода курса на ноль. Это явление известно как аномалия вставки.
Аномалия вставки. Пока новый преподаватель, Dr. Newsome, не назначен для преподавания хотя бы одного курса, его или ее данные не могут быть записаны.
Аномалия удаления. При определенных обстоятельствах удаление данных, представляющих определенные факты, требует удаления данных, представляющих совершенно разные факты. Отношение "Faculty and Their Courses", описанное в предыдущем примере, страдает этим типом аномалии, поскольку если преподаватель временно прекращает назначаться на какие-либо курсы, мы должны эффективно удалить последнюю из записей, на которых этот преподаватель появляется также удаляя преподавателя, если мы не установили код курса на ноль. Это явление известно как аномалия удаления.
Аномалия удаления. Вся информация о Dr. Giddens теряется, если он или она временно перестает назначаться на какие-либо курсы.
Минимизировать редизайн при расширении структуры базы данных
Полностью нормализованная база данных позволяет расширять ее структуру для размещения новых типов данных без значительного изменения существующей структуры. В результате приложения, взаимодействующие с базой данных, подвергаются минимальному воздействию.
Нормализованные отношения и отношения между одним нормализованным отношением отражают концепции реального мира и их взаимосвязи.
Пример
Запрос и манипулирование данными в ненастроенной структуре данных, такой как следующее представление транзакций по кредитным картам клиентов, не относящихся к 1NF, включает в себя большую сложность, чем это действительно необходимо:
Каждому клиенту соответствует "повторяющаяся группа" транзакций. Следовательно, автоматическая оценка любого запроса, относящегося к транзакциям клиентов, обычно включает два этапа:
- Распаковка групп транзакций одного или нескольких клиентов, позволяющая исследовать отдельные транзакции в группе
- Вывод результата запроса на основе результатов первого этапа
Например, чтобы узнать денежную сумму всех транзакций, которые произошли в октябре 2003 года для всех клиентов, система должна знать, что она должна сначала распаковать группу транзакций каждого клиента, а затем суммировать суммы всех полученных таким образом транзакций, где дата сделки приходится на октябрь 2003 года.
Одним из важных выводов Кодда было то, что структурная сложность может быть уменьшена. Снижение структурной сложности дает пользователям, приложениям и СУБД больше возможностей и гибкости для формулирования и оценки запросов. Более нормализованный эквивалент приведенной выше структуры может выглядеть так:
В измененной структуре ключом является в первом отношении, во втором отношении.
Теперь каждая строка представляет отдельную транзакцию по кредитной карте, и СУБД может получить интересующий ответ, просто найдя все строки с датой падения в октябре и суммируя их суммы. Структура данных помещает все значения в равную основу, выставляя каждое из них непосредственно СУБД, поэтому каждое из них может потенциально участвовать непосредственно в запросах; тогда как в предыдущей ситуации некоторые значения были встроены в структуры более низкого уровня, которые должны были обрабатываться специально. Соответственно, нормализованный дизайн поддается обработке запросов общего назначения, тогда как ненормализованный дизайн - нет. Нормализованная версия также позволяет пользователю изменять имя клиента в одном месте и защищает от ошибок, которые возникают, если имя клиента написано с ошибкой в некоторых записях.
Развития вычислительной техники осуществлялось по двум основным направлениям:
· применение вычислительной техники для выполнения численных расчетов;
· использование средств вычислительной техники в информационных системах.
Информационная система – это совокупность программно-аппаратных средств, способов и людей, которые обеспечивают сбор, хранение, обработку и выдачу информации для решения поставленных задач. На ранних стадиях использования информационных систем применялась файловая модель обработки. В дальнейшем в информационных системах стали применяться базы данных. Базы данных являются современной формой организации, хранения и доступа к информации. Примерами крупных информационных систем являются банковские системы, системы заказов железнодорожных билетов и т.д.
База данных – это интегрированная совокупность структурированных и взаимосвязанных данных, организованная по определенным правилам, которые предусматривают общие принципы описания, хранения и обработки данных. Обычно база данных создается для предметной области.
Предметная область – это часть реального мира, подлежащая изучению с целью создания базы данных для автоматизации процесса управления.
Наборы принципов, которые определяют организацию логической структуры хранения данных в базе, называются моделями данных.
Существуют 4 основные модели данных – списки (плоские таблицы), реляционные базы данных, иерархические и сетевые структуры.
В течение многих лет преимущественно использовались плоские таблицы (плоские БД) типа списков в Excel. В настоящее время наибольшее распространение при разработке БД получили реляционные модели данных. Реляционная модель данных является совокупностью простейших двумерных таблиц – отношений (англ. relation), т.е. простейшая двумерная таблица определяется как отношение (множество однотипных записей объединенных одной темой).
От термина relation (отношение) происходит название реляционная модель данных. В реляционных БД используется несколько двумерных таблиц, в которых строки называются записями, а столбцы полями, между записями которых устанавливаются связи. Этот способ организации данных позволяет данные (записи) в одной таблице связывать с данными (записями) в других таблицах через уникальные идентификаторы (ключи) или ключевые поля.
Основные понятия реляционных БД: нормализация, связи и ключи
1. Принципы нормализации:
· В каждой таблице БД не должно быть повторяющихся полей;
· В каждой таблице должен быть уникальный идентификатор (первичный ключ);
· Каждому значению первичного ключа должна соответствовать достаточная информация о типе сущности или об объекте таблицы (например, информация об успеваемости, о группе или студентах);
· Изменение значений в полях таблицы не должно влиять на информацию в других полях (кроме изменений в полях ключа).
2. Виды логической связи.
Отношения, которые могут существовать между записями двух таблиц:
· один – к - одному, каждой записи из одной таблицы соответствует одна запись в другой таблице;
· один – ко - многим, каждой записи из одной таблицы соответствует несколько записей другой таблице;
· многие – к - одному, множеству записей из одной таблице соответствует одна запись в другой таблице;
· многие – ко - многим, множеству записей из одной таблицы соответствует несколько записей в другой таблице.
Тип отношения в создаваемой связи зависит от способа определения связываемых полей:
3. Ключи. Ключ – это столбец (может быть несколько столбцов), добавляемый к таблице и позволяющий установить связь с записями в другой таблице. Существуют ключи двух типов: первичные и вторичные или внешние.
Первичный ключ – это одно или несколько полей (столбцов), комбинация значений которых однозначно определяет каждую запись в таблице. Первичный ключ не допускает значений Null и всегда должен иметь уникальный индекс. Первичный ключ используется для связывания таблицы с внешними ключами в других таблицах.
Внешний (вторичный) ключ - это одно или несколько полей (столбцов) в таблице, содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ определяет способ объединения таблиц.
Из двух логически связанных таблиц одну называют таблицей первичного ключа или главной таблицей, а другую таблицей вторичного (внешнего) ключа или подчиненной таблицей. СУБД позволяют сопоставить родственные записи из обеих таблиц и совместно вывести их в форме, отчете или запросе.
Существует три типа первичных ключей: ключевые поля счетчика (счетчик), простой ключ и составной ключ.
Если поле содержит уникальные значения, такие как коды или инвентарные номера, то это поле можно определить как первичный ключ. В качестве ключа можно определить любое поле, содержащее данные, если это поле не содержит повторяющиеся значения или значения Null.
В случаях, когда невозможно гарантировать уникальность значений каждого поля, существует возможность создать ключ, состоящий из нескольких полей. Чаще всего такая ситуация возникает для таблицы, используемой для связывания двух таблиц многие - ко - многим.
Необходимо еще раз отметить, что в поле первичного ключа должны быть только уникальные значения в каждой строке таблицы, т.е. совпадение не допускается, а в поле вторичного или внешнего ключа совпадение значений в строках таблицы допускается.
Если возникают затруднения с выбором подходящего типа первичного ключа, то в качеcтве ключа целесообразно выбрать поле счетчика.
Программы, которые предназначены для структурирования информации, размещения ее в таблицах и манипулирования данными называются системами управления базами данных (СУБД). Другими словами СУБД предназначены как для создания и ведения базы данных, так и для доступа к данным. В настоящее время насчитывается более 50 типов СУБД для персональных компьютеров. К наиболее распространенным типам СУБД относятся: MS SQL Server, Oracle, Informix, Sybase, DB2, MS Access и т. д.
Создание БД. Этапы проектирования
Создание БД начинается с проектирования.
Этапы проектирования БД:
· Исследование предметной области;
· Анализ данных (сущностей и их атрибутов);
· Определение отношений между сущностями и определение первичных и вторичных (внешних) ключей.
В процессе проектирования определяется структура реляционной БД (состав таблиц, их структура и логические связи). Структура таблицы определяется составом столбцов, типом данных и размерами столбцов, ключами таблицы.
Сущность – любой конкретный или абстрактный объект в рассматриваемой предметной области. Сущности – это базовые типы информации, которые хранятся в БД (в реляционной БД каждой сущности назначается таблица). К сущностям могут относиться: студенты, клиенты, подразделения и т.д. Экземпляр сущности и тип сущности - это разные понятия. Понятие тип сущности относится к набору однородных личностей, предметов или событий, выступающих как целое (например, студент, клиент и т.д.). Экземпляр сущности относится, например, к конкретной личности в наборе. Типом сущности может быть студент, а экземпляром – Петров, Сидоров и т. д.
Атрибут – это свойство сущности в предметной области. Его наименование должно быть уникальным для конкретного типа сущности. Например, для сущности студент могут быть использованы следующие атрибуты: фамилия, имя, отчество, дата и место рождения, паспортные данные и т.д. В реляционной БД атрибуты хранятся в полях таблиц.
Связь – взаимосвязь между сущностями в предметной области. Связи представляют собой соединения между частями БД (в реляционной БД – это соединение между записями таблиц).
Сущности – это данные, которые классифицируются по типу, а связи показывают, как эти типы данных соотносятся один с другим. Если описать некоторую предметную область в терминах сущности – связь, то получим модель сущность - связь для этой БД.
Задача о предметной области
Рассмотрим предметную область: Деканат (Успеваемость студентов)
Основные предметно-значимые атрибуты сущностей:
-студенты – фамилия, имя, отчество, пол, дата и место рождения, группа студентов;
-группы студентов – название, курс, семестр;
-дисциплины – название, количество часов
- успеваемость – оценка, вид контроля.
Основные требования к функциям БД:
-выбрать успеваемость студента по дисциплинам с указанием общего количества часов и вида контроля;
-выбрать успеваемость студентов по группам и дисциплинам;
-выбрать дисциплины, изучаемые группой студентов на определенном курсе или определенном семестре.
Из анализа данных предметной области следует, что каждой сущности необходимо назначить простейшую двумерную таблицу (отношения). Далее необходимо установить логические связи между таблицами. Между таблицами Студенты и Успеваемость необходимо установить такую связь, чтобы каждой записи из таблицы Студенты соответствовало несколько записей в таблице Успеваемость, т.е. один – ко – многим, так как у каждого студента может быть несколько оценок.
Логическая связь между сущностями Группы – Студенты определена как один – ко – многим исходя из того, что в группе имеется много студентов, а каждый студент входит в состав одной группе. Логическая связь между сущностями Дисциплины – Успеваемость определена как один – ко – многим, потому что по каждой дисциплине может быть поставлено несколько оценок различным студентам.
Для создания БД необходимо применить одну из известных СУБД, например СУБД Access.
Читайте также: