Транзитивная зависимость в базе данных - это косвенная связь между значениями в той же таблице, которая вызывает функциональную зависимость. Для достижения стандарта нормализации третьей нормальной формы (3NF) вы должны устранить любую транзитивную зависимость.
По своей природе транзитивная зависимость требует трех или более атрибутов (или столбцов базы данных), которые имеют функциональную зависимость между ними, что означает, что столбец A в таблице опирается на столбец B через промежуточную колонку C.
Давайте посмотрим, как это может работать.
Пример транзитивной зависимости
АВТОРЫ
| aUTHOR_ID | автор | Книга | Author_Nationality |
|---|---|---|---|
| Auth_001 | Карточка Орсона Скотта | Игра Эндера | Соединенные Штаты |
| Auth_001 | Карточка Орсона Скотта | Игра Эндера | Соединенные Штаты |
| Auth_002 | Маргарет Этвуд | Сказка Служанки | Канада |
В приведенном выше примере АВТОРЫ:
- Книга → автор : Здесь Книга атрибут определяет автор приписывать. Если вы знаете название книги, вы можете узнать имя автора. Тем не мение, автор не определяет Книга , потому что автор может писать несколько книг. Например, только потому, что мы знаем имя автора Orson Scott Card, мы все еще не знаем названия книги.
- автор → Author_Nationality : Аналогичным образом, автор атрибут определяет Author_Nationality , но не наоборот; просто потому, что мы знаем, что национальность не означает, что мы можем определить автора.
Но эта таблица вводит транзитивную зависимость:
- Книга → Author_Nationality: Если мы знаем название книги, мы можем определить национальность через колонку «Автор».
Избегайте транзитивных зависимостей
Чтобы обеспечить третью нормальную форму, удалим транзитивную зависимость.
Мы можем начать с удаления столбца «Книга» из таблицы «Авторы» и создания отдельной таблицы «Книги»:
КНИГИ
| Book_ID | Книга | aUTHOR_ID |
|---|---|---|
| Book_001 | Игра Эндера | Auth_001 |
| Book_001 | Дети разума | Auth_001 |
| Book_002 | Сказка Служанки | Auth_002 |
АВТОРЫ
| aUTHOR_ID | автор | Author_Nationality |
|---|---|---|
| Auth_001 | Карточка Орсона Скотта | Соединенные Штаты |
| Auth_002 | Маргарет Этвуд | Канада |
Это исправить? Рассмотрим теперь наши зависимости:
Столы BOOKS:
- Book_ID → Книга: Книга зависит от Book_ID .
- В этой таблице нет других зависимостей, поэтому мы в порядке. Обратите внимание, что внешний ключ aUTHOR_ID связывает эту таблицу с таблицей AUTHORS через ее первичный ключ aUTHOR_ID , Мы создали связь, чтобы избежать транзитивной зависимости, ключевой дизайн реляционных баз данных.
Таблица АВТОРОВ:
- aUTHOR_ID → Автор: автор зависит от aUTHOR_ID .
- автор → Author_Nationality: Гражданство может быть определено автором.
- aUTHOR_ID → Author_Nationality: Гражданство можно определить из aUTHOR_ID сквозь автор приписывать. У нас все еще есть транзитивная зависимость.
Для нормализации этих данных необходимо добавить третью таблицу:
СТРАНЫ
| cOUNTRY_ID | Страна |
|---|---|
| Coun_001 | Соединенные Штаты |
| Coun_002 | Канада |
АВТОРЫ
| aUTHOR_ID | автор | cOUNTRY_ID |
|---|---|---|
| Auth_001 | Карточка Орсона Скотта | Coun_001 |
| Auth_002 | Маргарет Этвуд | Coun_002 |
Теперь у нас есть три таблицы, используя внешние ключи для связывания между таблицами:
- Внешний ключ таблицы BOOK aUTHOR_ID связывает книгу с автором в таблице АВТОРС.
- Внешний ключ таблицы AUTHORS cOUNTRY_ID связывает автора с страной в таблице СТРАН.
- Стол COUNTRIES не имеет внешнего ключа, поскольку ему не нужно ссылаться на другую таблицу в этом дизайне.
Почему транзитивные зависимости плохой дизайн базы данных
Какова ценность предотвращения переходных зависимостей, чтобы помочь обеспечить 3NF? Давайте снова рассмотрим нашу первую таблицу и рассмотрим проблемы, которые она создает:
АВТОРЫ
| aUTHOR_ID | автор | Книга | Author_Nationality |
|---|---|---|---|
| Auth_001 | Карточка Орсона Скотта | Игра Эндера | Соединенные Штаты |
| Auth_001 | Карточка Орсона Скотта | Дети разума | Соединенные Штаты |
| Auth_002 | Маргарет Этвуд | Сказка Служанки | Канада |
Такой дизайн может способствовать аномалиям данных и несоответствиям, например:
- Если вы удалите две книги «Дети разума» и «Игра Эндера», вы полностью удалите автора «Карту Орсона Скотта» и его гражданство из базы данных.
- Вы не можете добавить нового автора в базу данных, если вы не добавите книгу; что, если автор еще не опубликован или вы не знаете названия книги, которую она написала?
- Если «Орсон Скотт Кард» изменил свое гражданство, вам придется изменить его во всех рекордах, в которых он появляется. Наличие нескольких записей с одним и тем же автором может привести к неточным данным: что, если человек, вводящий данные, не понимает, что для него есть несколько записей и изменяет данные только в одной записи?
- Вы не можете удалить книгу, такую как «Сказка служанки», не удаляя автора полностью.
Это лишь некоторые причины, по которым нормализация и избежание транзитивных зависимостей, защита данных и обеспечение согласованности.
