Лекция
Привет, Вы узнаете о том , что такое проектирование базы данных, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое проектирование базы данных, функциональная зависимость, нормализация, нормальные формы 1нф, 2нф, 3нф, 4нф, 5нф, bcnf, теорема фейгина, теорема хеза , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.
Предыдущий раздел был посвящен вопросам представления в базе данных одного из важных видов информации о предметной области – ограничений целостности данных. В частности, были рассмотрены такие важные виды ограничений целостности, как ограничения потенциальных ключей и ограничения ссылочной целостности. Эти одни из важнейших видов ограничений целостности являются, однако, частными случаями представления в базе данных разного рода зависимостей между сущностями предметной области информационной системы. Действительно, ограничение потенциального ключа состоит в том, что значения одного атрибута (атрибутов) однозначно определяют значения других атрибутов в отношении. Ограничение внешних ключей указывает на зависимость значения атрибутов одного отношения от значений атрибутов другого отношения. Несомненно, что одной из важнейших составляющих задачи проектирования базы данных является как можно более адекватное представление в ней разнообразных зависимостей между сущностями предметной области и обеспечение механизма поддержания хранимых в системе данных в процессе ее функционирования в состоянии, соответствующем этим отношениям. Однако изложенный в предыдущих разделах материал не позволяет ответить на вопрос: как наилучшим образом представить данную предметную область в виде конкретной совокупности базовых (хранимых) реляционных отношений.
В данном разделе рассматриваются вопросы, связанные с тем, каким образом можно построить «хорошую» реляционную базу данных. Как уже говорилось выше, реляционная база данных представляет собой набор нормализованных таблиц-отношений. Исходя из уже изученного материала должно быть ясно, что одна и та же информация о предметной области, вообще говоря, может быть представлена с помощью различных наборов отношений, вплоть до того, что вся информация может быть представлена с помощью одной нормализованной супер-таблицы. Очевидно, что такие различные формы логической структуры базы данных не будут полностью эквивалентными с точки зрения удобства и эффективности использования содержащейся в них информации, возможности поддержания данных в целостном состоянии.
Задача проектирования базы данных заключается в выборе наиболее оптимальной структуры базовых отношений.
Материалы данного раздела как раз и посвящены выяснению критериев, позволяющих выбирать «лучшие» варианты логической структуры базы данных, а также определению методов, позволяющих осознанно с помощью формальных, эквивалентных с точки зрения информационного содержимого базы данных преобразований оптимизировать логическую структуру ее отношений.
При решении указанных вопросов одним из фундаментальных понятий теории реляционных баз данных является понятие функциональной зависимости.
Функциональная зависимость (Functional Dependency) является связью типа многие-к-одному между множествами значений атрибутов внутри отношения. Отношение при этом понимается в смысле переменной отношения, что подразумевает, что такого рода зависимость между значениями атрибутов имеет место при любых конкретных состояниях данного отношения.
Если говорить неформально, то функциональная зависимость между значениями атрибутов состоит в том, что значение одного атрибута (возможно, составного) однозначно определяет значение другого атрибута. Такого рода зависимость между атрибутами отношения нам уже знакома. Например, значения атрибутов, являющихся ключом отношения, всегда однозначно определяют значения всех остальных атрибутов отношения.
Более строгая формулировка определения функциональной зависимости имеет следующий вид.
Пусть R является переменной отношения, а X и Y – произвольными подмножествами множества атрибутов отношения R. Говорят, что значение Y функционально зависит от значения X тогда и только тогда, когда для любого допустимого значения отношения R каждое значение X связано в точности с одними значением Y.
Символически функциональная зависимость обозначается X→Y. Эта запись читается следующим образом: Xфункционально определяет Y или Y функционально зависит от X.
При этом атрибуты X называют детерминантом, а атрибуты Y – зависимой частью функциональной зависимости.
Важное уточнение в этом определении, говорящее о том, что функциональная зависимость имеет место длявсех возможных состояний отношения, показывает, что определение функциональных зависимостей для отношения относится к разновидности указания ограничений целостности отношения.
В конкретном отношении обычно присутствует не одна, а множество функциональных зависимостей. Например, в отношении, представленном на рис. 10.1, ключом которого является составной атрибут {КОД_СТУД, ДИСЦИПЛИНА}, имеют место несколько функциональных зависимостей.
|
{КОД_СТУД, ДИСЦИПЛИНА}→{ИМЯ_СТУД}
{КОД_СТУД, ДИСЦИПЛИНА}→{ОЦЕНКА}
{КОД_СТУД, ДИСЦИПЛИНА}→{ИМЯ_СТУД, ОЦЕНКА}
{КОД_СТУД, ДИСЦИПЛИНА, ИМЯ_СТУД}→{ОЦЕНКА}
{КОД_СТУД, ДИСЦИПЛИНА}→{КОД_СТУД}
{КОД_СТУД, ДИСЦИПЛИНА, ИМЯ_СТУД}→{ДИСЦИПЛИНА}
Однако не все имеющие место в отношении функциональные зависимости представляют собой практический интерес, в частности, так называемые тривиальные функциональные зависимости.
Функциональную зависимость называют тривиальной тогда и только тогда, когда правая (зависимая) часть символической записи данной зависимости является подмножеством ее левой части (детерминанта).
Таким образом, в приведенном выше примере зависимости
{КОД_СТУД, ДИСЦИПЛИНА}→{КОД_СТУД} и
{КОД_СТУД, ДИСЦИПЛИНА, ИМЯ_СТУД}→{ДИСЦИПЛИНА}
являются тривиальными. Понятно, что фиксация наличия между атрибутами такого рода зависимостей никакой дополнительной смысловой нагрузки не несет.
Говоря о функциональных зависимостях, можно также заметить, что некоторые зависимости могут бытьвыведенными из других функциональных зависимостей. Зависимость
{КОД_СТУД, ДИСЦИПЛИНА}→{ИМЯ_СТУД, ОЦЕНКА}
следует из зависимостей
{КОД_СТУД, ДИСЦИПЛИНА}→{ИМЯ_СТУД} и
{КОД_СТУД, ДИСЦИПЛИНА}→{ОЦЕНКА}.
Очевидно, что добавление к множеству функциональных зависимостей выведенных из них же также не добавляет никакой новой информации о характере зависимостей между атрибутами. Тем не менее, то, что одни зависимости могут быть выведены из других, говорит о том, что могут существовать множества зависимостей эквивалентных друг другу с точки зрения отражаемой ими информации о предметной области.
Множество всех функциональных зависимостей, которые задаются данным множеством функциональных зависимостей S, т.е. могут быть выведены из этих зависимостей, называетсязамыканием (closure) множества зависимостей S и обозначается S+.
Правила, позволяющие из данного множества зависимостей S вывести его замыкание S+, называемые правилами Армстронга, имеют следующий вид.
Пусть A, B, C и D – произвольные подмножества множества атрибутов заданного отношения R, тогда для них имеют место такие правила.
Эти три правила являются полным набором, позволяющим вывести все зависимости, составляющие замыкание S+ множества зависимостей S. Из этих правил для упрощения задачи практического вычисления замыкания S+ можно вывести несколько следствий.
На практике обычно более важным является не нахождение множества зависимостей S+, являющихся замыканием множества функциональных зависимостей S, а нахождение ответа на вопрос, может ли данная конкретная функциональная зависимость X→Y быть выведенной из имеющегося множества зависимостей S.
Дадим еще несколько полезных определений.
Пусть S1 и S2 являются двумя множествами функциональных зависимостей. Если любая функциональная зависимость, которая является зависимостью множества S1, также является зависимостью множества S2, т. е. если S1+ является подмножеством S2+, то S2 называется покрытием(cover) для S1.
Если S2 является покрытием для S1, а S1 является покрытием для S2, т. е. если S1+ = S2+, то S1 и S2эквивалентны.
Функциональная зависимость называется неприводимой слева (или функционально полной), если ни один атрибут не может быть опущен из ее детерминанта без изменения замыкания S+ (без преобразования множества S в некоторое множество, не являющееся ему эквивалентным).
Или, менее строго, но более просто:
функциональная зависимость называется неприводимой слева (или функционально полной), если ни один атрибут не может быть опущен из ее детерминанта без нарушения этой зависимости.
Нормальные формы
Как уже говорилось выше, реляционная модель поддерживает только нормализованные отношения, т.е. отношения, содержащие только скалярные (атомарные) значения атрибутов. Такую форму отношений в теории нормализации называют первой нормальной формой (сокращенно 1НФ). Это, однако, не единственная нормальная форма представления реляционных отношений. В настоящее время кроме первой нормальной формы известны еще пять других. Для чего же необходимо введение новых нормальных форм и преобразование отношений в нормальные формы более высокого порядка? Дело в том, что отношения, находящиеся в первой нормальной форме, могут обладать целым рядом нежелательных свойств. Рассмотрим в качестве примера отношение УСПЕВАЕМОСТЬ, представленное на рис.10.2.
|
Рис. 10.2. Пример отношения в первой нормальной форме
Нетрудно заметить, что в этом отношении присутствует определенная избыточность. Действительно, во многих кортежах дублируется информация о том, какому коду студента соответствует конкретная фамилия студента. Наличие избыточности информации рассматривается как недостаток отношения. И дело здесь не только и не столько в том, что дублирование информации приводит к увеличению объема памяти, требуемой для хранения информации в компьютерной системе. Гораздо более важным является то, что избыточность затрудняет поддержание содержащейся в отношении информации в целостном состоянии. Например, в случае изменения фамилии студента в отношении должны быть скорректированы значения соответствующего атрибута во всех кортежах, относящихся к этому студенту, в противном случае, данные в отношении окажутся в противоречивом состоянии.
Нормализация отношений реляционной базы данных представляет собой формализованные преобразования этих отношений с целью устранения в них возможных нежелательных свойств, которыми в общем случае может обладать логическая структура отношений, находящихся в первой нормальной форме, без потерь информации исходного отношения, в том числе и о зависимостях, имеющих место между его атрибутами.
Теория нормализации позволяет, например, ответить на вопрос – какая из двух логических структур отношений предпочтительнее: отношение
R1{КОД_СТУД, ИМЯ_СТУД, ФАКУЛЬТЕТ, ДИСЦИПЛИНА, ОЦЕНКА}
или структура из двух отношений
R2{КОД_СТУД, ИМЯ_СТУД, ФАКУЛЬТЕТ,} и R3{КОД_СТУД, ДИСЦИПЛИНА, ОЦЕНКА}.
Нормальными формами более высокого порядка являются определенные Коддом (Codd) первая, вторая и третья нормальные формы (1НФ, 2нф и 3нф ), введенная Бойсом (Boyce) и Коддом так называемая нормальная форма Бойса –Кодда (НФБК) и определенные Фейгином (Fagin) четвертая и пятая нормальные формы ( 4нф и 5нф ).
На рис.10.3 показано, что множества отношений, находящихся в различных нормальных формах, оказываются как бы вложенными друг в друга. Так, например, отношение, находящееся в 3НФ, одновременно будет находиться и в 2НФ и в 1НФ, но не обязательно будет находиться в НФБК. Отношение же, находящееся в 5НФ, одновременно будет находиться и во всех остальных нормальных формах.
Ниже будет показано, что преобразование отношений в нормальную форму более высокой степени является в определенном смысле «более желательным». Важно сразу же заметить, что процедура нормализации отношений должна быть обязательно обратимой. Это означает, что преобразование отношений в нормальные формы более высокой степени осуществляется без потерь информации, которую эти отношения представляют, так как всегда гарантируется возможность обратного преобразования, т. е. полного восстановления исходной информации.
Процедура нормализации отношений состоит в их разбиении, т.е. декомпозиции, на другие отношения, находящиеся в нормальных формах более высокого уровня. Причем, как уже говорилось выше, такая декомпозиция должна осуществляться без потерь информации, содержащейся в исходном отношении. Таким образом, при реализации процесса нормализации отношений интерес представляют не любые их преобразования, а только те, которые не приводят к потерям полезной информации.
Рассмотрим пример. Пусть исходное отношение имеет вид, представленный на рис.10.4.
|
На рис.10.5 показано, что это отношение может быть разбито на два отношения тремя различными способами.
Отношение 1а
|
Отношение 2а
|
||||||||
Отношение 1б
|
Отношение 2б
|
||||||||
Отношение 1в
|
Отношение 2в
|
Если внимательно посмотреть на приведенные три варианта декомпозиции отношений, то можно заметить, что в первом случае (вариант а) при разбиении отношений исходная информация не утрачивается. Действительно, исходное отношение Отношение 1 может быть восстановлено в прежнем виде путем операции естественного соединения отношений Отношение 1а и Отношение 2а по атрибуту КОД_СТУДЕНТА.
Во втором же и третьем случаях часть информации, содержащейся в отношении Отношение 1, оказывается утраченной, а именно: в них потеряна информация какой из студентов-однофамильцев Ивановых учится на физическом, а какой на историческом факультетах. Нетрудно увидеть, что естественное соединение отношений Отношение 1б и Отношение 2б по единственному их общему атрибуту ИМЯ_СТУДЕНТА, которое представлено на рис.10.6, не позволяет получить исходное состояние отношения Отношение 1.
|
Важный вопрос о том, происходит или не происходит потеря информации при декомпозиции отношений, самым тесным образом связан с понятием функциональной зависимости. Можно обратить внимание на то, что в предыдущем примере при проведении декомпозиции, представленной вариантом «б», была утеряна функциональная зависимость атрибута ФАКУЛЬТЕТ от атрибута КОД_СТУДЕНТА, а при декомпозиции варианта «в» утеряна зависимость атрибута ИМЯ_СТУДЕНТА от атрибута КОД_СТУДЕНТА.
Ответ на вопрос, в каком случае декомпозиция отношения является обратимой, дается теоремой Хеза (Heath).
теорема хеза . Пусть R{A, B, C} является отношением, а A, B и C являются атрибутами этого отношения. Если в отношении R имеет место функциональная зависимость A→B, то отношение R равно соединению его проекций R1{A, B} и R2{A, C}.
Другими словами, функциональная зависимость должна быть сохранена и детерминант этой зависимости (атрибут А) должен присутствовать в обоих результирующих отношениях. В приведенном на рис.10.4 примере отношения имеют место две функциональные зависимости КОД→ИМЯ_СТУДЕНТА иКОД_СТУДЕНТА→ФАКУЛЬТЕТ, и поэтому в соответствии с теоремой Хеза Отношение 1 может быть разбито на два являющихся его проекциями отношения, а именно Отношение 1а {КОД_СТУДЕНТА, ИМЯ_СТУДЕНТА} и Отношение& nbsp;1б {КОД_СТУДЕНТА, ФАКУЛЬТЕТ}, без потери информации. При любом другом варианте декомпозиции одна из этих функциональных зависимостей теряется.
Отношение находится в первой нормальной форме (1НФ) тогда и только тогда, когда домены всех его атрибутов содержат только скалярные значения.
Как уже говорилось выше, отношение, находящееся в первой нормальной форме, может обладать нежелательными свойствами.
Представим себе, что нам надо хранить в базе данных информацию о личном коде (идентификаторе) студента, его фамилии и о его оценках, полученных по конкретным дисциплинам. Для этих целей можно ввести следующие атрибуты: КОД_СТУДЕНТА, ИМЯ_СТУДЕНТА, ДИСЦИПЛИНА и ОЦЕНКА. Между этими атрибутами имеют место функциональные зависимости, представленные стрелками на рис.10.7.
Рис. 10.7. Диаграмма функциональных зависимостей
Эти зависимости показывают, что пара значений атрибутов {КОД_СТУДЕНТА, ДИСЦИПЛИНА} однозначно определяет значение атрибута ОЦЕНКА (конкретный студент может иметь по конкретной дисциплине только одну оценку), а значение атрибута КОД_СТУДЕНТА однозначно определяет значения атрибута ИМЯ_СТУДЕНТА (студент имеет одну фамилию).
Можно отметить также, что в отношении присутствует еще и приводимая функциональная зависимость атрибута от ключа отношения, а именно, зависимость {КОД_СТУДЕНТА, ДИСЦИПЛИНА}→{ИМЯ_СТУДЕНТА}, обозначенная на рис.10.7 пунктиром.
Какова должна быть структура отношений для хранения данных, соответствующих этому примеру? Рассмотрим простейший случай, когда все приведенные атрибуты включены в схему одного отношения, назовем его Экзамен, как это показано на рис.10.8.
|
Это отношение находится в первой нормальной форме, так как все значения его атрибутов являются скалярными. Единственным потенциальным ключом этого отношения является составной атрибут {КОД_СТУДЕНТА, ДИСЦИПЛИНА}, так как эта пара атрибутов однозначно определяет значения кортежей отношения, в то время как каждый из атрибутов КОД_СТУДЕНТА и ДИСЦИПЛИНА по отдельности таким свойством не обладает.
Хорошо видно, однако, что содержащиеся в этом отношении данные избыточны. Об этом говорит сайт https://intellect.icu . Так, в частности, в этом отношении для каждого студента многократно дублируется информация о его фамилии.
Избыточность в отношении приводит к возникновению нежелательных факторов, или к аномалиям, при осуществлении с этим отношением операций, связанных с изменением данных, а именно операций INSERT(вставка кортежа), DELETE (удаление кортежа) и UPDATE (обновление значений атрибута какого-либо кортежа). Рассмотрим, в чем состоят эти аномалии.
Операция INSERT. В отношение ЭКЗАМЕН нельзя вставить кортеж с данными о коде студента и его фамилии (например, запись о том, что студент с кодом С4 имеет фамилию Лукин), если мы не имеем данных о том, что этот студент сдавал хотя бы одну дисциплину. Действительно, в этом случае не может быть задано значение первичного ключа для кортежа, соответствующего этой записи, так как неизвестно значение атрибута Дисциплина.
Операция DELETE. При удалении информации о том, что конкретный студент сдавал какую-либо дисциплину, например, если мы хотим аннулировать ошибочно введенную запись о сдаче студентом с кодом С9 экзамена по иностранному языку, мы можем потерять информацию о фамилии этого студента из-за того, что удаляемый кортеж был единственным кортежем, содержащем информацию о нем.
Операция UPDATE. Значение атрибута имя_студента повторяется в этом отношении многократно для каждого конкретного значения атрибута КОД_СТУДЕНТА. Поэтому в случае, когда, например, для студентки с кодом С2 потребуется изменить фамилию (вышла замуж), эти изменения необходимо будет произвести для всехкортежей отношения, относящихся к этой студентке. Помимо громоздкости выполнения одинаковой операции корректировки значения атрибута для большого числа записей в этом случае возможна более неприятная ситуация. Если по тем или иным причинам (например, из-за сбоя системы), для каких-либо кортежей отношения такая коррекция не будет произведена, в отношении окажутся кортежи с противоречивыми данными (в одних кортежах коду С2 будет соответствовать одна фамилия, а в других для того же кода будет указана другая фамилия.
Указанные аномалии, однако, могут быть устранены, если заменить исходное отношение двумя его проекциями – отношениями СТУДЕНТЫ и УСПЕВАЕМОСТЬ, представленными на рис.10.9.
|
|
Диаграммы функциональных зависимостей для этих двух отношений представлены на рис.10.10.
Рис. 10.10. Диаграммы функциональных зависимостей отношений СТУДЕНТЫ и УСПЕВАЕМОСТЬ
Можно видеть, что рассмотренные выше проблемы, имеющие место в отношении ЭКЗАМЕН, связанные с обновлением данных, в данном случае оказываются преодоленными.
Операция INSERT. Информация о том, что студент с кодом С4 имеет фамилию Лукин, может быть сохранена путем вставки соответствующего кортежа в отношение СТУДЕНТЫ независимо от того, имеются или нет данные о сдаче этим студентом какой-либо учебной дисциплины.
Операция DELETE. Информация о том, что студент с кодом С9 сдавал экзамен по иностранному языку может быть удалена из отношения УСПЕВАЕМОСТЬ без потери информации о фамилии этого студента.
Операция UPDATE. Для изменения фамилии у студентки с кодом С2 необходимо скорректировать значение соответствующего атрибута в единственной записи отношения СТУДЕНТЫ.
Сравнивая диаграммы функциональных зависимостей на рис.10.7 и 10.10, можно увидеть, что произведенная декомпозиция отношения Экзамен на два отношения СТУДЕНТЫ и Успеваемость привела к исключению из отношений приводимой функциональной зависимости атрибута от ключа отношения, а именно зависимости {Код_студента, Дисциплина}→{ИМЯ_студента}, обозначенной на рис.10.7 пунктиром.
Указанная зависимость является избыточной, так как, на самом деле, она является следствием наличиянеприводимой функциональной зависимости {Код_студента}→{ИМЯ_студента}, что хорошо видно из диаграммы на рис.10.7.
Теперь можно дать определение второй нормальной формы отношения.
Отношение находится во второй нормальной форме (2НФ) тогда и только тогда, когда оно находиться впервой нормальной форме и каждый неключевой атрибут неприводимо (функционально полно) зависит от первичного ключа.
Напомним, что неключевым мы называем атрибут, который не входит в состав ключа отношения.
Оба отношения – СТУДЕНТЫ и Успеваемость, полученные в результате декомпозиции отношения Экзамен, находятся во второй нормальной форме, так как их неключевые атрибуты ИМЯ_студента, Факультет и Оценка зависят от первичных ключей этих отношений, соответственно, {Код_студента} и {Код_студента,Дисциплина} неприводимо.
Обратившись к приведенной ранее теореме Хеза, можно видеть, что для любого отношения, находящегося впервой нормальной форме, всегда возможно без потерь информации путем его декомпозиции преобразовать к двум отношениям, находящимся во второй нормальной форме.
Еще раз обращаем внимание на то, что декомпозиция эта осуществляется без потерь информации. Исходное отношение ЭКЗАМЕН (рис.10.8) всегда может быть восстановлено путем соединения двух выходных отношений студенты и УСПЕВАЕМОСТЬ (рис.10.9) по их общему атрибуту КОД_СТУДЕНТА.
Рассмотрим следующий пример. Представим себе, что требуется хранить в базе данных информацию о том, в каком общежитии живет студент с указанием адреса общежития. Для этих целей можно ввести следующие атрибуты: КОД_СТУДЕНТА, ОБЩЕЖИТИЕ и АДРЕС. Между указанными атрибутами имеют место функциональные зависимости, представленные на рис.10.11.
Рис. 10.11. Диаграмма функциональных зависимостей между атрибутами СТУДЕНТ_ОБЩЕЖИТИЕ_АДРЕС
Эти функциональные зависимости означают тот факт, что студент может жить только в одном общежитии и у общежития может быть только один адрес.
Для хранения рассматриваемой информации может быть использовано отношение СТУДЕНТ_ОБЩЕЖИТИЕ_АДРЕС, приведенное на рис.10.12.
|
продолжение следует...
Часть 1 Проектирование базы данных, функциональная зависимость, нормализация, нормальные формы 1НФ, 2НФ, 3НФ, 4НФ, 5НФ
Часть 2 10.6 Нормальная форма Бойса–Кодда (BCNF) - Проектирование базы данных, функциональная
Часть 3 10.9 Итоговая схема процедуры нормализации - Проектирование базы данных, функциональная
В заключение, эта статья об проектирование базы данных подчеркивает важность того что вы тут, расширяете ваше сознание, знания, навыки и умения. Надеюсь, что теперь ты понял что такое проектирование базы данных, функциональная зависимость, нормализация, нормальные формы 1нф, 2нф, 3нф, 4нф, 5нф, bcnf, теорема фейгина, теорема хеза и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Ответы на вопросы для самопроверки пишите в комментариях, мы проверим, или же задавайте свой вопрос по данной теме.
Комментарии
Оставить комментарий
Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Термины: Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL