Рекомендации по разработке структур
Какими должны быть сущности
- каждой сущности отдельную таблицу
- поля в таблицах сущностей могут быть ключевыми и не ключевыми.
- обычно достаточно простого ключа, но может быть и составной.
- информацию о сущностях следует представлять так, чтобы не ключевые поля в таблицах были взаимно независимыми и полностью зависели от ключа.
Организация связей сущностей
- Записи таблицы связей предназначены для отображения связей между сущностями, информация о которых находится в соответствующих таблицах.
- Одна таблица связей описывает связь между двумя сущностями.
- Более сложные связи необходимо сводить к бинарным.
- Транзитивных связей не должно быть.
Обеспечение целостности
Целостность - свойство базы данных, означающее, что она содержит полную, непротиворечивую, адекватно отражающую предметную область информацию.
Логическая целостность БД - это отсутствие нарушений структуры БД или ее объектов, при удалении или изменении связей между объектами.
Поддержание целостности - это проверка целостности и восстановление в случае нарушения.
Целостное состояние задается с помощью ограничений целостности в виде условий, которым должны удовлетворять хранимые данные.
Основные ограничения:
- Ограничения значений атрибутов отношений
- Структурные ограничения на кортежи отношений
- Целостность ссылок
Примером ограничений значений атрибутов отношений является требование недопустимости пустых или повторяющихся значений в атрибутах, а также контроль принадлежности атрибутов заданному диапазону.
Структурные ограничения определяют требования целостности сущностей и целостности ссылок. Целостность сущностей состоит в том, что любой кортеж отношения должен быть отличим от любого другого кортежа этого отношения.
Целостность ссылок связана с понятием внешнего ключа. Отношение, в котором определен внешний ключ, ссылается на отношение, в котором этот же атрибут является первичным ключом (дочернее отношение). Требование целостности состоит в том, что для каждого значения внешнего ключа родительской таблицы должна найтись строка в дочерней таблице с таким же значением первичного ключа.
Долж и Стаж – первичные ключи для R3, R2 и внешние ключи для таб R1.
Метод сущность - связь
Один из подходов к проектированию БД является метод "сущность–связь" или ER-метод. Суть метода в построении ER- диаграмм, отображающих в графической форме основные объекты ПО (предметной области) и связи между ними, а также определение характеристик этих связей. Затем по четким правилам переходят от ER- диаграмм к таблицам, которые заполняются атрибутами, проверяются условия нормализации, определяются ключевые атрибуты и связи между ними. Результатом является схема данных Базы данных.
Основные понятия метода
Сущность – объект, информация о котором хранится в БД. Экземпляр сущности – конкретный объект.
Экземпляры сущностей отличаются друг от друга и однозначно идентифицируются. Названия сущностей, как правило, существительные. Преподаватель, дисциплина, кафедра, студент- это сущности.
Атрибут сущности – свойство сущности. Фамилия, Имя, Стаж – атрибуты сущности преподаватель.
Ключ сущности – атрибут или набор атрибутов, используемых для идентификации сущности.
Связь между сущностями – соединение между двумя или более сущностями. Предполагается зависимость между атрибутами этих сущностей. Название связи обычно представляется глаголом. Примеры связи: Преподаватель ведет дисциплину. Преподаватель преподает в группе. Преподаватель работает на кафедре.
Степень связи – является характеристикой связи между сущностями, которая может быть типа: 1:1, 1:М, М:1, М:М.
Диаграммы ER- экземпляров, Диаграммы ER-типов используются для наглядности и удобства проектирования.
Примеры ER- диаграмм:
Преподаватель Ведет Дисциплину
| Преподаватель | Ведет | Дисциплина |
  П1
| Физика | |
  П2
| Химия | |
| П3 | Информатика | |
| П4 | Математика | |
| П5 | СУБД |
Рис. 1 Диаграмма ER- экземпляров сущностей
Какую конкретно дисциплину ведет каждый преподаватель.
Рис. 2 Диаграмм ER- типов
На основании диаграмм ER- типа формируются отношения проектируемой БД.
Здесь учитывается степень связи сущностей и класс их принадлежности,что определяется на основе диаграмм ER- экземпляров соответствующих сущностей.
Класс принадлежности экземпляров сущности может быть обязательным и необязательным.
Обязательный класс – все экземпляры сущности обязательно участвуют в рассматриваемой сущности, иначе – необязательный класс принадлежности сущности.
Изменяя класс принадлежности для каждого типа связи можно получить несколько вариантов диаграмм ER- типа. Для характеристик связи на диаграммах используется символ "точка". Если точка внутри прямоугольника, то соответствующая сущность имеет обязательный класс принадлежности. Если вне прямоугольника - необязательный класс принадлежности. Цифры и буквы рядом с точкой указывают на степень связи.
Пример1. связь 1:1 и необязательный класс принадлежности.
На Рис. 1 Диаграмма ER- экземпляров сущностей изображена такая диаграмма: каждый преподаватель ведет не более одной дисциплины, а каждая дисциплина ведется не более чем одним преподавателем. Некоторые преподаватели не ведут дисциплины, а также есть дисциплины, которые не ведутся не одним преподавателем.
Пример 2. связь 1:1 и обязательный класс принадлежности
| Преподаватель | Ведет | Дисциплина |
| П1
| Физика | |
| П2
| Химия | |
 П3
| Информатика | |
| П4 | Математика | |
| П5 | СУБД |
1
Рис. 3 диаграмм связи 1:1 и обязательный класс принадлежности
Возможен промежуточный случай, когда класс принадлежности одной сущности необязательный.
Пример3. Связь типа 1:М
Каждый преподаватель ведет несколько дисциплин, но каждая дисциплина ведется одним преподавателем.
Пример4. Связь типа М:1
Каждый преподаватель ведет одну дисциплину, но каждую дисциплину могут вести несколько преподавателей.
Эти примеры могут иметь несколько вариантов, отличающихся классом принадлежности экземпляров сущности.
Обозначим обязательный класс принадлежности "О", а необязательный "Н". Тогда имеем 4 варианта связи для каждого типа связи: О-О, О-Н, Н-О, Н-Н.
Пример5. Связь типа 1:М, Н-О.
Каждый преподаватель может вести несколько дисциплин или не одной, но каждая дисциплина ведется одним преподавателем.
| Преподаватель | Ведет | Дисциплина |
 П1
| Физика | |
  П2
| Химия | |
| П3
| Информатика | |
 П4
| Математика | |
| П5 | СУБД | |
| С++ | ||
| История |
Рис. 4 Связь типа 1:М, Н-О.
Аналогично составляются диаграммы для других вариантов.
Пример 6. Связь типа N:М
Каждый преподаватель может вести несколько дисциплин, а каждая дисциплина ведется несколькими преподавателями. Возможны 4 варианта в зависимости от Класса принадлежности экземпляров сущности.
Пример7. Связь типа N:М и О-Н
Каждый преподаватель ведет не менее одной дисциплины, а дисциплина может вестись более чем одним преподавателем, но в тоже время есть дисциплины, которые никем не ведутся.
| Преподаватель | Ведет | Дисциплина |
  П1
| Физика | |
 П2
| Химия | |
  П3
| Информатика | |
 П4
| Математика | |
 П5
| СУБД | |
| С++ | ||
| История |
 |
Рис. 5 Связь типа N:М и О-Н
Этапы проектирования
- Выделение сущностей и связей между ними.
- Построение диаграмм ER- типа с учетом сущностей и их связей.
- Формирование набора предварительных отношений с указанием предполагаемого первичного ключа для каждого отношения.
- Добавление неключевых атрибутов в отношения.
- Приведение к третьей нормальной форме или нормальной форме Бойса-Кодда.
Один из главных этапов – этап формирования отношений.
Правила формирования отношений для 1:1
Правило 1.
Если степень бинарной связи 1:1, класс принадлежности обеих сущностей обязательный, то формируется одно отношение. Первичным ключом отношения может быть ключ любой из двух сущностей.
КП КД
Получаем отношение Преподаватель (КП, Фамилия, Телефон, КД, Дисциплина, Часы)
Преподаватель
| КП | Фамилия | Телефон | КД | Дисциплина | Часы |
| П1 | Андреев | Д1 | Физика | ||
| П2 | Петров | Д2 | Логика | ||
| П3 | Рушин | Д3 | Информатика | ||
| П4 | Репин | Д4 | СУБД |
Правило 2.
Если степень связи 1:1 и класс принадлежности одной сущности является обязательным, а другой – необязательным, то строится два отношения. Под каждую сущность выделяется одно отношение с первичным ключом, являющимся ключом соответствующих сущностей. Ключ сущности, с необязательным классом принадлежности добавляется в качестве атрибута в отношение с обязательным классом принадлежности.
Преподаватель Дисциплина
| КП | Фамилия | Телефон | КД |
| П1 | Андреев | Д1 | |
| П2 | Петров | Д2 | |
| П3 | Рушин | Д3 | |
| П4 | Репин | Д4 |
| КД | Дисциплина | Часы |
| Д1 | Физика | |
| Д2 | Логика | |
| Д3 | Информатика | |
| Д4 | СУБД | |
| Д5 | ИТ |
Правила формирования отношений для степени связи 1:1, а класс принадлежности обеих сущностей является необязательным, то необходимо использовать три отношения. Два отношения соответствуют связываемым сущностям, ключи которых являются первичными в этих отношениях. Третье отношение является связным между первыми двумя, поэтому его ключ объединяет ключевые атрибуты связываемых отношений.
КП КД
Преподаватель Дисциплина Ведет
| КП | Фамилия | Телефон |
| П1 | Андреев | |
| П2 | Петров | |
| П3 | Рушин | |
| П4 | Репин | |
| П5 | Иванов |
| КД | Дисциплина | Часы |
| Д1 | Физика | |
| Д2 | Логика | |
| Д3 | Информатика | |
| Д4 | СУБД | |
| Д5 | ИТ |
| КД | КП |
| Д1 | П1 |
| Д2 | П2 |
| Д3 | П3 |
| Д4 | П4 |
Правила формирования отношений для 1:М
Если две сущности связаны как 1:М, то сущность С1 Называется односвязанной (1-связанной), а сущность С2 –многосвязанной (М-связанной). При формировании отношений определяющим является класс принадлежности М-связанной сущности, класс отношения 1-связанной сущности на результат не влияет.
Рассмотрим диаграмму 1:М Н-О
К1 К2
| КП | Фамилия | Телефон | КД | Дисциплина | Часы |
| П1 | Андреев | Д1 | Физика | ||
| П1 | Андреев | Д2 | Логика | ||
| П2 | Петров | Д3 | Информатика | ||
| П2 | Петров | Д4 | СУБД | ||
| П3 | Рушин | Д5 | С++ | ||
| П4 | Репин | Д6 | Матанализ | ||
| П4 | Репин | Д7 | Экономика | ||
| П5 | Егоров | - | - | - |
Две проблемы:
- есть записи с пустыми полями
- избыточное дублирование данных
Если бы класс принадлежности одно-связанной сущности был обязательным, то от первой проблемы избавились, но от второй нет.
Правило 4.
Если степень связи между сущностями 1:М (илиМ:1) и класс принадлежности М-связанной сущности обязательный, то достаточно формирования двух отношений. При этом первичными ключами этих отношений являются ключи их сущностей. Кроме того, ключ одно-связанной сущности добавляется как атрибут (внешний ключ) в отношение, соответствующее М-связанной сущности.
| КП | КД | Дисциплина | Часы |
| П1 | Д1 | Физика | |
| П1 | Д2 | Логика | |
| П2 | Д3 | Информатика | |
| П2 | Д4 | СУБД | |
| П3 | Д5 | С++ | |
| П4 | Д6 | Матанализ | |
| П4 | Д7 | Экономика |
Преподаватель (родительская) Дисциплина (дочерняя)
| КП | Фамилия | Телефон |
| П1 | Андреев | |
| П2 | Петров | |
| П3 | Рушин | |
| П4 | Репин | |
| П5 | Егоров |
Рассмотрим связь 1:М Н-Н
| КП | Фамилия | Телефон | КД | Дисциплина | Часы |
| П1 | Андреев | Д1 | Физика | ||
| П1 | Андреев | Д2 | Логика | ||
| П2 | Петров | Д3 | Информатика | ||
| - | - | - | Д4 | СУБД | |
| П3 | Рушин | Д5 | С++ | ||
| П4 | Репин | Д6 | Матанализ | ||
| П4 | Репин | Д7 | Экономика | ||
| П5 | Егоров | - | - | - |
Две проблемы:
- Имеются пустые поля в записях
- Избыточные данные о преподавателях ведущих более одной дисциплины.
Для устранения этих проблем необходимо перейти к следующему правилу.
Правило 5.
Если степень связи 1:М (М:1) и класс принадлежности М-связанной сущности является необязательной, то необходимо формирование трех отношений. Два отношения соответствуют связываемым сущностям, ключи которых являются первичными ключами этих отношений. Третье отношение является связным между первыми двумя (его ключ объединяет ключевые атрибуты связываемых отношений).