Урок по структуризации и проектированию баз данных

Хорошо структурированная база данных:

• экономит место на диске, так как не содержит лишней информации
• поддерживает целостность и точность данных
• обеспечивает удобный доступ к данным

Проектирование содержательной и эффективной базы данных — вопрос выполнения надлежащей процедуры, в которую входят следующие фазы:

1. Анализ требований, или выявление цели базы данных
2. Организация данных в таблицы
3. Указание первичных ключей и анализ связей
4. Нормализация и стандартизация таблиц

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

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

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

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

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

Клиенты

• Имя
• Адрес
• Город, регион, почтовый индекс
• Адрес электронной почты

Товары

• Имя
• Цена
• Количество на складе
• Количество в заказе

Заказы

• Номер заказа
• Консультант
• Дата
• Товар(ы)
• КоличествоS
• Цена
• Общая стоимость

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

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

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

Схожие данные в пределах базы группируются в таблицы, каждая из которых состоит из строк (или кортежей) и столбцов.

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

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

Имя	Фамилия	Возраст	Почтовый индекс
Роджер	Уильямс	43	34760
Джеррика	Йоргенсен	32	97453
Саманта	Хопкинс	56	64829

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

• CHAR — заданная длина текста
• VARCHAR — текст разной длины
• TEXT — крупный текстовый блок
• INT — целое число (отрицательное или положительное)
• FLOAT, DOUBLE — может содержать число с плавающей запятой
• BLOB — бинарные данные

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

Сами по себе таблицы не входят в наглядный обзор базы данных (который также называется схемой «сущность-связь», или ER-схемой). Для их обозначения на схеме используются блоки. Название каждого блока указывает, какой тип данных описывает таблица, а ниже перечислен список атрибутов, например:

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

Атрибуты, выбранные в качестве первичных ключей, должны обладать уникальностью, постоянством и определенным значением (они не могут оставаться пустыми или содержать значение NULL). Поэтому номера заказов и имена пользователей идеально подходят на роль первичных ключей, тогда как телефонные номера и адреса — нет. В качестве первичного ключа можно использовать и сочетание нескольких полей (в этом случае ключ называется составным).

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

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

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

Связь «один к одному»

Если на каждый экземпляр сущности Б приходится только один экземпляр сущности А, считается, что между ними существует связь «один к одному» (которая часто обозначается «1:1»). На ER-диаграммах такая связь обозначается линией с небольшой чертой на каждом конце:

Связь 1:1, как правило, указывает на то, что, если у вас нет весомых причин держать их по отдельности, данные двух таблиц лучше всего объединить в одну.

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

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

Связь «один ко многим»

Отношения такого рода возникают, когда запись одной таблицы связана с несколькими сущностями другой. К примеру, один и тот же клиент мог разместить несколько заказов, а посетитель библиотеки — за один визит позаимствовать сразу несколько книг. Связи «один ко многим» (или сокращенно «1:М») выражаются на схеме в виде нотации «вороньи лапки», как показано на примере ниже:

Чтобы применить связь 1:М при планировании базы данных, просто добавьте первичный ключ из таблицы «один» в качестве атрибута к таблице «многие». Если первичный ключ находится в другой таблице, он носит название «внешний ключ». Таблица «один» считается родительской, тогда как таблица «многие» — дочерней.

Связь «многие ко многим»

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

На ER-диаграмме этот тип связи отображается следующим образом:

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

Для этого вам понадобится создать новую сущность между двумя таблицами. Если связь М:М установлена между продажами и товарами, новую сущность можно назвать «проданные_товары», так как в ней будет представлено содержимое каждой продажи. С «проданными_товарами» и у таблицы «продажи», и у таблицы «товары» будет установлена связь по типу 1:М. В разных моделях такие промежуточные сущности называются по-разному — «связующие таблицы», «ассоциативные сущности» или «узловые таблицы».

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

Обязательно или нет?

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

Две сущности могут быть взаимозависимыми (то есть одна не может существовать без другой).

Рекурсивные связи

Иногда таблица может ссылаться на себя же. Например, в таблице сотрудников может присутствовать атрибут «менеджер», который будет отсылать нас к другому сотруднику в той же таблице. Это и есть рекурсивная связь.

Лишние связи

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

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

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

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

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

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

Первая нормальная форма (или сокращенно 1НФ) диктует, что в каждой ячейке таблицы может содержаться лишь одно значение и ни в коем случае не список. Таким образом, таблица ниже не отвечает этому требованию:

Код товара	Цвет	Цена
1	коричневый, желтый	15 $
2	красный, зеленый	13 $
3	синий, оранжевый	11 $

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

Правильный подход — разбить таблицу на несколько таблиц или записей, пока в каждой ячейке не останется по одному значению, а в самих таблицах не будет лишних столбцов. На этом этапе мы получим атомарные данные, то есть данные, разбитые на мельчайшие осмысленные фрагменты. Например, в дополнение к таблице выше можно составить еще одну — «Информация о продажах», где конкретные товары будут сопоставлены с продажами. Таким образом, между «Продажами» и «Информацией о продажах» будет установлена связь типа 1:М.

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

Вторая нормальная форма (или сокращенно 2НФ) требует, чтобы каждый атрибут полностью зависел от всего первичного ключа. Это означает, что каждый атрибут должен зависеть от первичного ключа напрямую, а не опосредованно через другой атрибут.

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

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

Так, например, таблица с полями, перечисленными ниже, не отвечает условиям второй нормальной формы, так как атрибут «наименование товара» зависит от кода товара, но не зависит от номера заказа:

• Номер заказа (первичный ключ)

• Код товара (первичный ключ)

• Наименование товара

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

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

Это правило предотвращает хранение в таблице производных данных, например, содержимого столбца «Налог», размер которого всегда напрямую зависит от общей цены заказа:

Заказ	Цена	Налог
14325	40,99 $	2,05 $
14326	13,73 $	0,69 $
14327	24,15 $	1,21 $

Были предложены и другие формы нормализации, включая форму Бойса-Кодда, формы с четвертой по шестую и нормальную форму «домен-ключ», однако первые три формы имеют наиболее широкое распространение.

Хотя в этих формах изложены основные общие правила, степень нормализации все же зависит от контекста базы данных.

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

База данных также должна быть настроена для проверки данных на соответствие применимым правилам. Многие системы управления базами данных, например, Microsoft Access, требуют исполнения ряда правил автоматически.

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

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

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

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

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

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

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

Унифицированный язык моделирования (UML) — еще один способ наглядного представления сложных систем, созданных с помощью объектно-ориентированного языка. Некоторые понятия, упоминаемые в данном руководстве, также встречаются в UML, однако под другими названиями. Например, вместо термина «сущность» в UML применяется понятие «класс».

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

Многие решения, которые вам предстоит принять в процессе схематизации, будут продиктованы выбранной вами системой управления базами данных (СУБД). В число самых распространенных систем входят:

• Oracle DB

• MySQL

• Microsoft SQL Server

• PostgreSQL

• IBM DB2

По возможности постарайтесь выбрать оптимальную СУБД согласно доступному бюджету и вашим требованиям к операционной системе, набору функций и так далее.