Последнее обновление: 02.07.2017
Базы данных могут содержать таблицы, которые связаны между собой различными связями. Связь (relationship) представляет ассоциацию между сущностями разных типов.
При выделении связи выделяют главную или родительскую таблицу (primary key table / master table) и зависимую, дочернюю таблицу (foreign key table / child table). Дочерняя таблица зависит от родительской.
Для организации связи используются внешние ключи. Внешний ключ представляет один или несколько столбцов из одной таблицы, который одновременно является потенциальным ключом из другой таблицы. Внешний ключ необязательно должен соответствовать первичному ключу из главной таблицы. Хотя, как правило, внешний ключ из зависимой таблицы указывает на первичный ключ из главной таблицы.
Связи между таблицами бывают следующих типов:
Один к одному (One to one)
Один к многим (One to many)
Многие ко многим (Many to many)
Связь один к одному
Данный тип связей встречает не часто. В этом случае объекту одной сущности можно сопоставить только один объект другой сущности. Например, на некоторых сайтах пользователь может иметь только один блог. То есть возникает отношение один пользователь - один блог.
Нередко этот тип связей предполагает разбиение одной большой таблицы на несколько маленьких. Основная родительская таблица в этом случае продолжает содержать часто используемые данные, а дочерняя зависимая таблица обычно хранит данные, которые используются реже.
В этом отношении первичный ключ зависимой таблицы в то же время является внешним ключом, который ссылается на первичный ключ из главной таблицы.
Например, таблица Users представляет пользователей и имеет следующие столбцы:
UserId (идентификатор, первичный ключ)
Name (имя пользователя)
И таблица Blogs представляет блоги пользователей и имеет следующие столбцы:
BlogId (идентификатор, первичный и внешний ключ)
Name (название блога)
В этом случае столбец BlogId будет хранить значение из столбца UserId из таблицы пользователей. То есть столбец BlogId будет выступать одновременно первичным и внешним ключом.
Связь один ко многим
Это наиболее часто встречаемый тип связей. В этом типе связей несколько строк из дочерний таблицы зависят от одной строки в родительской таблице. Например, в одном блоге может быть несколько статей. В этом случае таблица блогов является родительской, а таблица статей - дочерней. То есть один блог - много статей. Или другой пример, в футбольной команде может играть несколько футболистов. И в то же время один футболист одновременно может играть только в одной команде. То есть одна команда - много футболистов.
К примеру, пусть будет таблица Articles, которая представляет статьи блога и которая имеет следующие столбцы:
ArticleId (идентификатор, первичный ключ)
BlogId (внешний ключ)
Title (название статьи)
Text (текст статьи)
В этом случае столбец BlogId из таблицы статей будет хранить значение из столбца BlogId из таблицы блогов.
Связь многие ко многим
При этом типе связей одна строка из таблицы А может быть связана с множеством строк из таблицы В. В свою очередь одна строка из таблицы В может быть связана с множеством строк из таблицы А. Типичный пример - студенты и курсы: один студент может посещать несколько курсов, и соответственно на один курс могут записаться несколько студентов.
Другой пример - статьи и теги: для одной статьи можно определить несколько тегов, а один тег может быть определен для нескольких статей.
Но в SQL Server на уровне базы данных мы не можем установить прямую связь многие ко многим между двумя таблицами. Это делается посредством вспомогательной промежуточной таблицы. Иногда данные из этой промежуточной таблицы представляют отдельную сущность.
Например, в случае со статьями и тегами пусть будет таблица Tags, которая имеет два столбца:
TagId (идентификатор, первичный ключ)
Text (текст тега)
Также пусть будет промежуточная таблица ArticleTags со следующими полями:
TagId (идентификатор, первичный и внешний ключ)
ArticleIdId (идентификатор, первичный и внешний ключ)
Технически мы получим две связи один-ко-многим. Столбец TagId из таблицы ArticleTags будет ссылаться на столбец TagId из таблицы Tags. А столбец ArticleId из таблицы ArticleTags будет ссылаться на столбец ArticleId из таблицы Articles. То есть столбцы TagId и ArticleId в таблице ArticleTags представляют составной первичный ключ и одновременно являются внешними ключами для связи с таблицами Articles и Tags.
Ссылочная целостность данных
При изменении первичных и внешних ключей следует соблюдать такой аспект как ссылочная целостность данных (referential integrity). Ее основная идея состоит в том, чтобы две таблице в базе данных, которые хранят одни и те же данные, поддерживали их согласованность. Целостность данных представляет правильно выстроенные отношения между таблицами с корректной установкой ссылок между ними. В каких случаях целостность данных может нарушаться:
Аномалия удаления (deletion anomaly). Возникает при удалении строки из главной таблицы. В этом случае внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы
Аномалия вставки (insertion anomaly). Возникает при вставке строки в зависимую таблицу. В этом случае внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы.
Аномалии обновления (update anomaly). При подобной аномалии несколько строк одной таблицы могут содержать данные, которые принадлежат одному и тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки.
Аномалия удаления
Для решения аномалии удаления для внешнего ключа следует устанавливать одно из двух ограничений:
Если строка из зависимой таблицы обязательно требует наличия строки из главной таблицы, то для внешнего ключа устанавливается каскадное удаление. То есть при удалении строки из главной таблицы происходит удаление связанной строки (строк) из зависимой таблицы.
Если строка из зависимой таблицы допускает отсутствие связи со строкой из главной таблицы (то есть такая связь необязательна), то для внешнего ключа при удалении связанной строки из главной таблицы задается установка значения NULL. При этом столбец внешнего ключа должен допускать значение NULL.
Аномалия вставки
Для решения аномалии вставки при добавлении в зависимую таблицу данных столбец, который представляет внешний ключ, должен допускать значение NULL. И таким образом, если добавляемый объект не имеет связи с главной таблицей, то в столбце внешнего ключа будет стоять значение NULL.
Аномалии обновления
Для решения проблемы аномалии обновления применяется нормализация, которая будет рассмотрена далее.
Последнее обновление: 09.07.2017
Внешние ключи применяются для установки связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Хотя, как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы, но это необязательно должно быть непременным условием. Внешний ключ также может указывать на какой-то другой столбец, который имеет уникальное значение.
Общий синтаксис установки внешнего ключа на уровне столбца:
REFERENCES главная_таблица (столбец_главной_таблицы)
Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY , но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE .
Общий синтаксис установки внешнего ключа на уровне таблицы:
FOREIGN KEY (стобец1, столбец2, ... столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE); CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date);
Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса "FK_":
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
В данном случае ограничение внешнего ключа CustomerId называется "FK_Orders_To_Customers".
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:
CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
NO ACTION : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.
Каскадное удаление
По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается какая-либо строка из зависимой таблицы, то мы не сможем удалить эту строку из главной таблицы. Вначале нам необходимо будет удалить все связанные строки из зависимой таблицы. И если при удалении строки из главной таблицы необходимо, чтобы были удалены все связанные строки из зависимой таблицы, то применяется каскадное удаление, то есть опция CASCADE :
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE)
Аналогично работает выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Но так как первичные ключи, как правило, изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.
Установка NULL
При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
Установка значения по умолчанию
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT)В этой статье мы попробуем рассмотреть все, что касается ключей в SQL: для чего нужны, создание, ограничения ключей. В общем: будет скучно 😉
План на сегодня такой:
В теории реляционных баз данных — ключи это некие сущности, созданы для установления определенных ограничений, которые поддерживают целостность и доступность данных в таблицах баз данных.
Если говорить простыми словами, то ключи в sql
созданы для того, чтобы указать дополнительную функциональность столбца. Будь то уникальность или то, что столбец ссылается на другую таблицу (внешний ключ).
Первичный ключ
Столбец, который в базе данных должен быть уникальным помечают первичным ключом. Первичный ключ или primary key означает, что в таблице значение колонки primary key не может повторяться. Таким образом данный ключ позволяет однозначно идентифицировать запись в таблице не боясь при этом, что значение столбца повториться. Сразу пример: допустим у Вас есть таблица пользователей. В данной таблице есть поля: ФИО, год рождения, телефон. Как идентифицировать пользователя? Таким параметрам как ФИО и телефон доверять нельзя. Ведь у нас может быть несколько пользователей не только с одинаковой фамилией, но и с именем. Телефон может меняться со временем и пользователь с номером телефона может оказаться не тем кто у нас в базе данных.
Вот для этого и придумали первичный ключ. Один раз присвоили уникальный идентификатор и все. В mySql на примере которой мы выполняем все примеры из поле AUTO_INCREMENT нельзя задать если не указать, что это первичный ключ.
Думаю, что не стоит упоминать, что поле помеченное как первичный ключ не может быть пустым при создании записи.
Внешний ключ (foreign key )
Есть еще внешний ключ (foreign key ). Его еще называют ссылочным. Он нужен для связывания таблиц между собой.
Если посмотреть на картинку выше, то внешним ключем будет поле поставщик в таблице обувь. Как правило, при создании таблицы задают колонку уникальных целочисленных значений. Как мы это делали когда создавали таблицу supplier.
Колонка supplier_id
будет уникальна для каждой записи. Ее значение и будет выступать на мести колонки поставщик
в таблице обувь.
Предлагаю сразу и рассмотреть на примере как создается внешний ключ.
Создание внешнего ключа
create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, foreign key (supplier) references supplier (supplier_id));
Как видно на примере выше, синтаксис по созданию внешнего ключа довольно прост. Нужно в таблицу добавить поле, а после объявить это поле как внешний ключ и указать, куда он будет ссылаться. В данном случае поле supplier
будет ссылаться на поле supplier_id
в таблице supplier.
Составной ключ (composite key)
Что касается составного ключа — это несколько первичных ключей в таблице. Таким образом, создав composite key , уникальность записи будет проверяться по полям, которые объединенные в этот ключ.
Бывают ситуации, когда при вставке в таблицу нужно проверять запись на уникальность сразу по нескольким полям. Вот для этого и придуман составной ключ. Для примера я создам простую таблицу с composite key , чтобы показать синтаксис:
Create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));
В примере выше два поля объединенные в составной ключ и в таблице не будет записей с этими одинаковыми полями.
Это все, что касается ключей в SQL. Это небольшое пособие — подготовка к статье где мы подробно рассмотрим как объединять таблицы, чтобы они составляли единую базу данных.
Ключи являются фундаментальными элементами реляционной базы данных, поскольку они устанавливают связь между парой таблиц и обеспечивают уникальную идентификацию каждой записи в таблице. Ключи имеют более важное значение, чем установление отношений; они также помогают с ссылочной целостностью, и они являются основным компонентом целостности на уровне таблиц. Таблицы хранят в них огромные куски данных, которые обычно распространяются на тысячи записей, все из которых несортированы и дезорганизованы. Получение определенных данных из этих многочисленных записей может быть затруднительным порой или иногда невозможным. Именно здесь появляются Ключи. Здесь мы рассмотрим два очень важных ключа схемы реляционной базы данных и разницу между ними: Первичный ключ и Внешний ключ.
Что такое первичный ключ?
Первичный ключ - это специальный ключ, который однозначно идентифицирует каждую запись в таблице. В реляционной базе данных очень важно иметь уникальный идентификатор в каждой строке таблицы, а первичный ключ - это просто то, что вам нужно, чтобы однозначно идентифицировать кортеж в таблице. Кортеж представляет собой набор атрибутов value в реляционной базе данных. Первичный ключ может ссылаться на столбец или набор столбцов в таблице реляционной базы данных, используемый для неявной идентификации всех записей в таблице. Первичный ключ должен быть уникальным для каждой записи, поскольку он действует как уникальный идентификатор и не должен содержать значений Null. Каждая база данных должна иметь один и только один первичный ключ.
Что такое внешний ключ?
Внешний ключ относится к полю или коллекции полей в записи базы данных, которая однозначно идентифицирует ключевое поле другой записи базы данных в другой таблице. Проще говоря, он устанавливает связь между записями в двух разных таблицах в базе данных. Это может быть столбец в таблице, который указывает на столбцы первичного ключа, что означает, что внешний ключ, определенный в таблице, относится к первичному ключу какой-либо другой таблицы. Ссылки имеют решающее значение в реляционных базах данных для установления связей между записями, которые необходимы для сортировки баз данных. Внешние ключи играют важную роль в нормализации реляционных баз данных, особенно когда таблицы нуждаются в доступе к другим таблицам.
Разница между основным ключом и внешним ключом
Основы первичного ключа и внешнего ключа
Первичный ключ - это специальный ключ в реляционной базе данных, который действует как уникальный идентификатор для каждой записи, что означает, что он однозначно идентифицирует каждую строку / запись в таблице, и ее значение должно быть уникальным для каждой строки таблицы. С другой стороны, внешний ключ представляет собой поле в одной таблице, которое связывает две таблицы вместе. Он относится к столбцу или группе столбцов, которые однозначно идентифицируют строку другой таблицы или той же таблицы.
Отношение первичного ключа и внешнего ключа
Первичный ключ уникально идентифицирует запись в таблице реляционной базы данных, тогда как внешний ключ относится к полю в таблице, которая является первичным ключом другой таблицы. Первичный ключ должен быть уникальным, и только один первичный ключ разрешен в таблице, которая должна быть определена, тогда как в таблице допускается более одного внешнего ключа.
Дублирующие значения первичного ключа и внешнего ключа
Первичный ключ представляет собой комбинацию ограничений UNIQUE и Not Null, поэтому в поле первичного ключа в таблице реляционных баз данных нельзя допускать дублирования значений. Никакие две строки не могут переносить повторяющиеся значения для атрибута первичного ключа. В отличие от первичного ключа, внешний ключ может содержать повторяющиеся значения, а таблица в реляционной базе данных может содержать более чем внешний ключ.
NULL первичного ключа и внешнего ключа
Одно из основных различий между ними состоит в том, что в отличие от первичных ключей внешние ключи также могут содержать значения NULL. Таблица в реляционной базе данных может иметь только один первичный ключ, который не допускает значения NULL.
Временная таблица первичного ключа и внешнего ключа
Ограничение первичного ключа может быть определено неявно на временных таблицах и их переменных, тогда как ограничение внешнего ключа не может быть применено к локальным или глобальным временным таблицам.
Удаление основного ключа и внешнего ключа
Значение первичного ключа не может быть удалено из родительской таблицы, которая упоминается как внешний ключ в дочерней таблице. Перед удалением родительской таблицы сначала необходимо удалить дочернюю таблицу. Напротив, значение внешнего ключа может быть удалено из дочерней таблицы, даже если значение относится к первичному ключу родительской таблицы.
Первичный ключ или внешний ключ: сравнительная таблица
Резюме основных ключей
Ключи играют решающую роль в существовании схемы базы данных для установления связей между таблицами и внутри таблицы. Ключи устанавливают отношения и применяют различные типы целостности, особенно целостность на уровне таблицы и уровня отношений. Во-первых, они считают, что таблица содержит уникальные записи, а поля, которые вы используете для установления отношений между таблицами, должны содержать соответствующие значения. Первичный ключ и внешний ключ являются двумя наиболее важными и распространенными типами ключей, используемых в реляционных базах данных. Первичный ключ - это специальный ключ, используемый для уникальной идентификации записей в таблице, тогда как внешний ключ используется для установления отношения между двумя таблицами. Оба они идентичны по структуре, но играют разные роли в схеме реляционной базы данных.
На Рисунке представлена таблица (отношение степени 5), содержащая некоторые сведения о работниках гипотетического предприятия. Строки таблицы соответствуют кортежам. Каждая строка фактически представляет собой описание одного объекта реального мира (в данном случае работника), характеристики которого содержатся в столбцах. Реляционные отношения соответствуют наборам сущностей, а кортежи - сущностям. Столбцы в таблице, представляющей реляционное отношение, называют атрибутами .
Каждый атрибут определен на домене, поэтому домен можно рассматривать как множество допустимых значений данного атрибута. Несколько атрибутов одного отношения и даже атрибуты разных отношений могут быть определены на одном и том же домене.
Атрибут, значение которого однозначно идентифицирует кортежи, называется ключевым (или просто ключом ). Ключом является атрибут "Табельный номер", поскольку его значение уникально для каждого работника предприятия. Если кортежи идентифицируются только сцеплением значений нескольких атрибутов, то говорят, что отношение имеет составной ключ.
Первичный ключ - в реляционной модели данных один из потенциальных ключей отношения, выбранный в качестве основного ключа (или ключа по умолчанию).
Отношение может содержать несколько ключей. Всегда один из ключей объявляется первичным , его значения не могут обновляться. Все остальные ключи отношения называются возможными ключами .
С точки зрения теории все потенциальные (возможные) ключи отношения эквивалентны, то есть обладают одинаковыми свойствами уникальности и минимальности. Однако в качестве первичного обычно выбирается тот из потенциальных ключей, который наиболее удобен для тех или иных практических целей, например для создания внешних ключей в других отношениях либо для создания кластерного индекса. Поэтому в качестве первичного ключа как правило выбирают тот, который имеет наименьший размер (физического хранения) и/или включает наименьшее количество атрибутов.
Если первичный ключ состоит из единственного атрибута, его называют простым ключом .
Если первичный ключ состоит из двух и более атрибутов, его называют составным ключом . Так, имя, фамилия, отчество, номер паспорта, серия паспорта не могут быть первичными ключами по отдельности, так как могут оказаться одинаковыми у двух и более людей. Но не бывает двух личных документов одного типа с одинаковыми серией и номером. Поэтому в отношении, содержащем данные о людях, первичным ключом может быть подмножество атрибутов, состоящее из типа личного документа, его серии и номера.
В отличие от иерархической и сетевой моделей данных в реляционной отсутствует понятие группового отношения. Для отражения ассоциаций между кортежами разных отношений используется дублирование их ключей.
Атрибуты, представляющие собой копии ключей других отношений, называются внешними ключами .
Например, связь между отношениями ОТДЕЛ и СОТРУДНИК создается путем копирования первичного ключа "Номер_отдела" из первого отношения во второе. Таким образом, для того, чтобы получить список работников данного подразделения, необходимо: 1) Из таблицы ОТДЕЛ установить значение атрибута "Номер_отдела" , соответствующее данному "Наименованию_отдела". 2) выбрать из таблицы СОТРУДНИК все записи, значение атрибута "Номер_отдела" которых равно полученному на предыдушем шаге. Для того, чтобы узнать в каком отделе работает сотрудник, нужно выполнить обратную операцию: 1) Определяем "Номер_отдела" из таблицы СОТРУДНИК. 2) По полученному значению находим запись в таблице ОТДЕЛ.
18. Нормализация в реляционных БД, понятие нормальной формы при проектировании баз данных.
Нормальная форма - свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, которая потенциально может привести к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение.
Процесс преобразования базы данных к виду, отвечающему нормальным формам, называется нормализацией . Нормализация предназначена для приведения структуры базы данных к виду, обеспечивающему минимальную избыточность, то есть нормализация не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение объёма БД. Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в БД информации.
Устранение избыточности производится, как правило, за счёт декомпозиции отношений таким образом, чтобы в каждом отношении хранились только первичные факты (то есть факты, не выводимые из других хранимых фактов).
Функциональные зависимости.
Реляционная база данных содержит как структурную, так и семантическую информацию. Структура базы данных определяется числом и видом включенных в нее отношений, и связями типа "один ко многим", существующими между кортежами этих отношений. Семантическая часть описывает множество функциональных зависимостей, существующих между атрибутами этих отношений. Дадим определение функциональной зависимости.
19. 1НФ: Основные определения и правила преобразования.
Для обсуждения первой нормальной формы необходимо дать два определения:
Простой атрибут - атрибут, значения которого атомарны (неделимы).
Сложный атрибут - получается соединением нескольких атомарных атрибутов, которые могут быть определены на одном или разных доменах (его также называют вектор или агрегат данных).
Определение первой нормальной формы:
отношение находится в 1NF если значения всех его атрибутов атомарны. . В противном случае это вообще не таблица и такие атрибуты необходимо декомпозировать.
Рассмотрим пример:
В базе данных отдела кадров предприятия необходимо хранить сведения о служащих, которые можно попытаться представить в отношении
СЛУЖАЩИЙ(НОМЕР_СЛУЖАЩЕГО, ИМЯ, ДАТА_РОЖДЕНИЯ, ИСТОРИЯ_РАБОТЫ, ДЕТИ).
Из внимательного рассмотрения этого отношения следует, что атрибуты "история_работы"
и "дети"
являются сложными, более того, атрибут "история_работы"
включает еще один сложный атрибут "история_зарплаты".
Данные агрегаты выглядят следующим образом:
ИСТОРИЯ_РАБОТЫ (ДАТА_ПРИЕМА, НАЗВАНИЕ, ИСТОРИЯ_ЗАРПЛАТЫ),
ИСТОРИЯ_ЗАРПЛАТЫ (ДАТА_НАЗНАЧЕНИЯ, ЗАРПЛАТА),
ДЕТИ (ИМЯ_РЕБЕНКА, ГОД_РОЖДЕНИЯ).
Их связь представлена на рис. 3.3.
Рис.3.3. Исходное отношение.
Для приведения исходного отношения СЛУЖАЩИЙ к первой нормальной форме необходимо декомпозировать его на четыре отношения, так как это показано на следующем рисунке: 
Рис.3.4. Нормализованное множество отношений.
Здесь первичный ключ каждого отношения выделен синей рамкой, названия внешних ключей набраны шрифтом синего цвета. Напомним, что именно внешние ключи служат для представления функциональных зависимостей, существующих в исходном отношении. Эти функциональные зависимости обозначены линиями со стрелками.
Алгоритм нормализации описан Е.Ф.Коддом следующим образом:
- Начиная с отношения, находящегося на верху дерева (рис. 3.3.), берется его первичный ключ, и каждое непосредственно подчиненное отношение расширяется путем вставки домена или комбинации доменов этого первичного ключа.
- Первичный Ключ каждого расширенного таким образом отношения состоит из Первичного Ключа, который был у этого отношения до расширения и добавленного Первичного Ключа родительского отношения.
- После этого из родительского отношения вычеркиваются все непростые домены, удаляется верхний узел дерева, и эта же процедура повторяется для каждого из оставшихся поддеревьев.
20. 2НФ: Основные определения и правила преобразования.
Очень часто первичный ключ отношения включает несколько атрибутов (в таком случае его называют составным ) - см., например, отношение ДЕТИ, показанное на рис. 3.4 вопрос 19. При этом вводится понятие полной функциональной зависимости .
Определение:
неключевой атрибут функционально полно зависит от составного ключа если он функционально зависит от всего ключа в целом, но не находится в функциональной зависимости от какого-либо из входящих в него атрибутов.
Пример:
Пусть имеется отношение ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР, ЦЕНА).
Поставщик может поставлять различные товары, а один и тот же товар может поставляться разными поставщиками. Тогда ключ отношения - "N_поставщика + товар"
. Пусть все поставщики поставляют товар по одной и той же цене. Тогда имеем следующие функциональные зависимости:
- N_поставщика, товар -> цена
- товар -> цена
Неполная функциональная зависимость атрибута "цена" от ключа приводит к следующей аномалии: при изменении цены товара необходим полный просмотр отношения для того, чтобы изменить все записи о его поставщиках. Данная аномалия является следствием того факта, что в одной структуре данных объединены два семантических факта. Следующее разложение дает отношения во 2НФ:
- ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР)
- ЦЕНА_ТОВАРА (ТОВАР, ЦЕНА)
Таким образом, можно дать
Определение второй нормальной формы: Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от ключа.
21. 3НФ: Основные определения и правила преобразования.
Перед обсуждением третьей нормальной формы необходимо ввести понятие: транзитивная функциональная зависимость .
Определение:
Пусть X, Y, Z - три атрибута некоторого отношения. При этом X --> Y и Y --> Z, но обратное соответствие отсутствует, т.е. Z -/-> Y и Y -/-> X. Тогда Z транзитивно зависит от X.
Пусть имеется отношение ХРАНЕНИЕ (ФИРМА
, СКЛАД, ОБЪЕМ), которое содержит информацию о фирмах, получающих товары со складов, и объемах этих складов. Ключевой атрибут - "фирма"
. Если каждая фирма может получать товар только с одного склада, то в данном отношении имеются следующие функциональные зависимости:
- фирма -> склад
- склад -> объем
При этом возникают аномалии:
- если в данный момент ни одна фирма не получает товар со склада, то в базу данных нельзя ввести данные о его объеме (т.к. не определен ключевой атрибут)
- если объем склада изменяется, необходим просмотр всего отношения и изменение картежей для всех фирм, связанных с данным складом.
Для устранения этих аномалий необходимо декомпозировать исходное отношение на два:
- ХРАНЕНИЕ (ФИРМА , СКЛАД)
- ОБЪЕМ_СКЛАДА (СКЛАД , ОБЪЕМ)
Определение третьей нормальной формы:
Отношение находится в 3НФ, если оно находится во 2НФ и каждый не ключевой атрибут не транзитивно зависит от первичного ключа.
