Зависимости между таблицами в базе данных

4.1 Введение

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

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

• Один к одному
• Один ко многим
• Многие ко многим

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Лишние связи

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

4.6 Ссылочная целостность данных

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

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

• Аномалия удаления (deletion anomaly) возникает при удалении строки из главной таблицы. В таком случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы. Отсутствие строки в главной таблице приводит к нарушению целостности данных.
• Аномалия вставки (insertion anomaly) возникает при попытке вставить строку в зависимую таблицу. Однако, в таком случае внешний ключ из зависимой таблицы не будет иметь никакого соответствия с первичным ключом ни одной из строк главной таблицы. Это может привести к некорректной интерпретации данных и последующей неверной работе системы.
• Аномалии обновления (update anomaly) проявляются при изменении данных в одной строке таблицы. В таком случае данные из другой строки могут прийти в противоречие с новыми данными из первой строки и привести к нарушению целостности данных. Таким образом, такая аномалия может привести к неверным результатам в системе.

Аномалия удаления

Для решения аномалии удаления для внешнего ключа следует установить одно из двух ограничений.

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

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

Аномалия вставки

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

Аномалии обновления

Для решения проблемы аномалий обновления применяется нормализация, которая была рассмотрена ранее.