Процесс проектирования базы данных обычно состоит из трех этапов:
1) исследование предметной области;
2) анализ данных (сущностей и их атрибутов);
3) определение отношений между сущностями и определение первичных и
вторичных (внешних) ключей.
В процессе проектирования определяется структура реляционной БД (состав таблиц,
их структура и логические связи). Структура таблицы определяется составом столбцов,
типом данных и размерами столбцов, ключами таблицы.
К базовым понятиями модели БД «сущность – связь» относятся: сущности, связи
между ними и их атрибуты (свойства).
Сущность – любой конкретный или абстрактный объект в рассматриваемой
предметной области. Сущности – это базовые типы информации, которые хранятся в БД
(в реляционной БД каждой сущности назначается таблица). К сущностям могут
относиться: студенты, клиенты, подразделения и т.д. Экземпляр сущности и тип сущности – это разные понятия. Понятие тип сущности относится к набору однородных личностей,
предметов или событий, выступающих как целое (например, студент, клиент и т.д.).
Экземпляр сущности относится, например, к конкретной личности в наборе. Типом
сущности может быть студент, а экземпляром – Петров, Сидоров и т. д.
Связь – взаимосвязь между сущностями в предметной области. Связи представляют
собой соединения между частями БД (в реляционной БД – это соединение между
записями таблиц).
Сущности – это данные, которые классифицируются по типу, а связи показывают, как
эти типы данных соотносятся один с другим. Если описать некоторую предметную
область в терминах сущности – связь, то получим модель сущность - связь для этой БД.
Связь устанавливается между двумя общими полями (столбцами) двух таблиц.
Существуют связи с отношением «один-к-одному», «один-ко-многим» и «многие-ко-
многим».
Отношения, которые могут существовать между записями двух таблиц:
1) один – к - одному, каждой записи из одной таблицы соответствует одна запись в
другой таблице;
2) один – ко - многим, каждой записи из одной таблицы соответствует несколько
записей другой таблице;
3) многие – к - одному, множеству записей из одной таблице соответствует одна
запись в другой таблице;
4) многие – ко - многим, множеству записей из одной таблицы соответствует
несколько записей в другой таблице.
Тип отношения в создаваемой связи зависит от способа определения связываемых
полей:
1) отношение «один-ко-многим» создается в том случае, когда только одно из полей
является полем первичного ключа или уникального индекса.
2) отношение «один-к-одному» создается в том случае, когда оба связываемых поля
являются ключевыми или имеют уникальные индексы.
3) отношение «многие-ко-многим» фактически является двумя отношениями «один-
ко-многим» с третьей таблицей, первичный ключ которой состоит из полей внешнего
ключа двух других таблиц
Ключ – это столбец (может быть несколько столбцов), добавляемый к таблице и
позволяющий установить связь с записями в другой таблице. Существуют ключи двух
типов: первичные и вторичные или внешние.
Первичный ключ – это одно или несколько полей (столбцов), комбинация значений
которых однозначно определяет каждую запись в таблице. Первичный ключ не допускает
значений Null и всегда должен иметь уникальный индекс. Первичный ключ используется
для связывания таблицы с внешними ключами в других таблицах.
Внешний (вторичный) ключ - это одно или несколько полей (столбцов) в таблице,
содержащих ссылку на поле или поля первичного ключа в другой таблице. Внешний ключ
определяет способ объединения таблиц.
Из двух логически связанных таблиц одну называют таблицей первичного ключа или
главной таблицей, а другую таблицей вторичного (внешнего) ключа или подчиненной
таблицей. СУБД позволяют сопоставить родственные записи из обеих таблиц и совместно
вывести их в форме, отчете или запросе.
Существует три типа первичных ключей: ключевые поля счетчика (счетчик), простой
ключ и составной ключ.
Поле счетчика (Тип данных «Счетчик»). Тип данных поля в базе данных, в котором
для каждой добавляемой в таблицу записи в поле автоматически заносится уникальное
числовое значение.
Если поле содержит уникальные значения, такие как коды или инвентарные номера,
то это поле можно определить как первичный ключ. В качестве ключа можно определить
любое поле, содержащее данные, если это поле не содержит повторяющиеся значения или
значения Null.
В случаях, когда невозможно гарантировать уникальность значений каждого поля,
существует возможность создать ключ, состоящий из нескольких полей. Чаще всего такая
ситуация возникает для таблицы, используемой для связывания двух таблиц многие - ко -
многим.
Необходимо еще раз отметить, что в поле первичного ключа должны быть только
уникальные значения в каждой строке таблицы, т.е. совпадение не допускается, а в поле
вторичного или внешнего ключа совпадение значений в строках таблицы допускается.
Если возникают затруднения с выбором подходящего типа первичного ключа, то в
качеcтве ключа целесообразно выбрать поле счетчика.
Связь вида 1:М в реляционных СУБД используется наиболее часто, поэтому ее мы
рассмотрим наиболее подробно.
Возьмем в качестве примера таблицу «Абонент». Очевидно, что для этой таблицы первичным ключом является поле счетчика ID, так как его значение однозначно определяет значение поля FIO и bdate.
Предоставим
СУБД MySQL информацию о том, что это ключевое поле. Это можно сделать с помощью
SQL-скрипта вида:
ALTER TABLE `phonelib` DROP PRIMARY KEY, ADD PRIMARY KEY(`ID`)
или с помощью визуального интерфейса в среде phpMyAdmin.
Теперь выполним анализ предметной области. Мы уже определили, что абонент
характеризуется фамилией, именем, отчеством и датой рождения. Каждый абонент может
иметь несколько телефонных номеров: домашний, мобильный, рабочий и т.п.
Очевидно, что поля «ID» и «IDAbonent» в обеих таблицах имеет одинаковый смысл -
оно обозначает номер, присвоенный абоненту. В случае если таблицы не имеют связи
друг с другом, ничего не мешает нам добавить в таблицу «Телефон» строку, например,
(142, «Домашний», «0506521318»), несмотря на то, что в таблице «Абонент» нет абонента
с номером 142. Это нарушение целостности данных, так как такая строка может быть
занесена, но она не соответствует действительности. Чтобы подобная ситуация стала
невозможной, необходимо установить связь между таблицами по полю «ID». В этом
случае сервер БД автоматически проследит, чтобы поле «ID» из вставляемой строки
существовало в таблице «Телефон».
Подобной проверки достаточно, чтобы условие целостности данных выполнялось при
добавлении данных в таблицы. Но его недостаточно при манипулировании данными.
Рассмотрим ситуацию, когда мы удаляем из таблицы «Абонент» некоторую запись,
например абонента с номером 1. В случае если таблицы не связанны, удаление абонента
повлечет за собой изменение только одной таблицы. В таблице «Телефон» останутся
сведения о номерах абонента с номером 1. Такая ситуация - также нарушение целостности
данных, так как о данном абоненте, после его удаления, базе данных ничего не известно.
В случае если таблицы связанны, удаление абонента должно повлечь за собой удаление
всех его номеров (говорят, что удаление каскадируется).
Такая же ситуация с модификацией данных в родительской таблице. Если абонент с
номером 1 изменил номер на 5, то в связанных таблицах изменение родительской таблицы
повлечет за собой автоматическое изменение дочерних таблиц.
Сформируем ограничения, обеспечивающие целостность базы данных «Телефонный
справочник». Следует отметить, что понятие ссылочной целостности появилось в MySQL
сравнительно недавно (начиная с версии 5.0) и большая часть «движков» ее не
поддерживает.
Интерфейс phpMyAdmin не имеет возможности визуально устанавливать внешние
ключи и определять правила каскадного удаления и обновления базы данных. Поэтому
для определения дочерней таблицы мы будем выполнять с помощью SQL-скрипта
