Проектирование базы данных

78 Исходники баз данных

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

Другим последствием плохого проекта базы данных будет чрезмерная избыточность данных (data redundancy), которая сама по себе имеет два недостатка: наличие аномалий в данных и повышенные требования дискового пространства.

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

Процесс удаления избыточных данных в таблицах основан на теории функциональных зависимостей. Функциональная зависимость означает, что по значению одного столбца можно всегда однозначно определить соответствующее значение другого столбца. (То же самое действительно и для групп столбцов.) Функциональная зависимость между столбцами A и B обозначается как A->B, что означает, что значение в столбце B можно всегда определить по соответствующему значению в столбце A. (Данная формула читается как "B функционально зависит от A".)

В примере ниже демонстрируется функциональная зависимость между двумя атрибутами таблицы Employee базы данных SampleDb:

Id -> LastName

Имея однозначное значение табельного номера сотрудника, можно определить его фамилию (и все прочие соответствующие столбцы). Такой тип функциональной зависимости, когда столбец зависит от первичного ключа таблицы, называется тривиальной функциональной зависимостью.

Другой тип функциональной зависимости называется многозначной зависимостью. В отличие от только что описанной функциональной зависимости, многозначная зависимость задается для многозначных атрибутов. Это означает, что, используя известное значение одного атрибута (столбца), можно однозначно определить набор значений другого многозначного атрибута. Многозначная зависимость обозначается как ->->.

В примере ниже показана многозначная зависимость, действующая между двумя атрибутами объекта book:

ISBN ->-> Authors

Значение ISBN книги всегда определяет всех ее авторов. Поэтому атрибут Authors многозначно зависит от атрибута ISBN.

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

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

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

Первая нормальная форма

Первая нормальная форма (First Normal Form, 1NF) означает, что таблица не содержит многозначных или составных атрибутов. (Составной атрибут содержит другие атрибуты, поэтому его можно разделить на меньшие части.) По определению, все реляционные таблицы находятся в первой нормальной форме, т.к. значение любой ячейки должно быть атомарным, т.е. однозначным.

В таблице ниже демонстрируется первая нормальная форма (1NF) на примере таблицы Works_on базы данных SampleDb. Строки этой таблицы можно сгруппировать, используя табельный номер сотрудника:

Часть таблицы Works_on (находится в 1NF)
EmpId ProjectNumber ...
12054 p1 ...
12054 p3 ...
... ... ...
Эта таблица уже не в первой нормальной форме
EmpId ProjectNumber ...
12054 (p1, p3) ...
... ... ...

Вторая таблица уже не будет в первой нормальной форме, поскольку столбец ProjectNumber содержит набор значений (p1, p3).

Вторая нормальная форма

Таблица находится во второй нормальной форме (2NF), если она в первой нормальной форме (1NF) и не содержит ключевых столбцов, зависимых от части столбцов первичного ключа этой таблицы. Это означает, что если (A, B) является комбинацией двух столбцов таблицы, составляющих первичный ключ, тогда таблица не содержит столбцов, зависящих только от A или только от B.

Например, в таблице ниже показана таблица Works_on1, которая идентична таблице Works_on, за исключением наличия дополнительного столбца DepartmentNumber:

Таблица Works_on1
EmpId ProjectNumber Job EnterDate DepartmentNumber
12054 p1 Аналитик 2012.10.6 d3
12054 p3 Менеджер 2014.7.9 d3
8690 p2 Консультант 2013.6.15 d3
18600 p2 NULL 2013.8.26 d1
... ... ... ... ...

Первичным ключом этой таблицы является комбинация столбцов EmpId и ProjectNumber. Столбец DepartmentNumber этой таблицы зависит от части первичного ключа - столбца EmpId - и не зависит от второй его части, т. е. столбца ProjectNumber. Поэтому эта таблица не находится во второй нормальной форме. (Исходная же таблица, Works_on, находится во второй нормальной форме.)

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

Третья нормальная форма

Таблица находится в третьей нормальной форме (3NF), если она имеется во второй нормальной форме (2NF) и отсутствуют функциональные зависимости между неключевыми столбцами. Например, таблица Employee1 (показана ниже), которая точно такая же, как и таблица Employee, за исключением дополнительного столбца DepartmentName, не находится в третьей нормальной форме, т.к. для каждого известного значения столбца DepartmentNumber можно точно определить соответствующее значение столбца DepartmentName. (Исходная таблица Employee находится в третьей нормальной форме, как и все остальные таблицы базы данных SampleDb.)

Таблица Employee1
Id FirstName LastName DepartmentNumber DepartmentName
1 Василий Фролов d2 Бух. учет
2 Елена Лебеденко d1 Исследования
... ... ... ... ...

Модель "сущность - отношение"

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

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

Модель "сущность - отношение" (entity-relationship (ER)) используется при проектировании реляционных баз данных с целью удаления любой избыточности данных. Основным объектом модели "сущность - отношение" является сущность, т.е. реальный объект. Каждая сущность обладает несколькими атрибутами, которые являются свойствами сущности и, таким образом, описывают ее. Атрибут может быть одного из следующих типов:

Сущность Person в примере ниже обладает несколькими атомарными атрибутами, одним составным атрибутом Address и многозначным атрибутом College_degree:

PERSON (Personal_number, F_name, L_name, 
    Address(City,Zip,Street),
    {College_degree}
)

Каждая сущность имеет один или несколько ключевых атрибутов, которые являются атрибутами (или комбинацией двух или больше атрибутов) с однозначными значениями для каждой конкретной сущности. В примере ключевым атрибутом сущности PERSON является атрибут Personal_number.

Помимо сущности и атрибута, еще одним базовым понятием модели "сущность-отношение" является отношение. Отношение существует, когда одна сущность ссылается на другую (или несколько других). Количество вовлеченных сущностей определяет степень отношения. Например, между сущностями Employee и Project существует отношение второй степени - Works_on.

Каждое существующее отношение между двумя сущностями должно быть одним из следующих трех типов: 1:1, 1:N или M:N. Это свойство отношения называется мощностью отношения (cardinality ratio) или кардинальным числом. Например, между сущностями Department и Employee существует отношение типа 1:N, т. к. каждый сотрудник принадлежит в точности одному отделу, который, в свою очередь, содержит одного или нескольких сотрудников. А между сущностями Project и Employee существует отношение типа M:N, поскольку в каждом проекте участвует один или больше сотрудников и каждый сотрудник одновременно участвует в одном или больше проектов.

Отношение также может иметь свои атрибуты. На рисунке ниже показан пример диаграммы модели "модель - отношение" (ER-диаграммы), т.е. графическое описание этой модели:

Пример ER-диаграммы

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

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

Пройди тесты
Лучший чат для C# программистов