количество атрибутов входящих в составной внешний ключ отношения

Ключ отношения

Перви́чный ключ (англ. primary key ) — понятие теории реляционных баз данных, минимальное множество атрибутов, являющееся подмножеством заголовка данного отношения, составное значение которых уникально определяет кортеж отношения. На практике термин первичный ключ обозначает поле (столбец) или группу полей таблицы базы данных, значение которого (или комбинация значений которых) используется в качестве уникального идентификатора записи (строки) этой таблицы.

Содержание

Смысл

В теории реляционных баз данных таблица представляет собой изначально неупорядоченный набор записей. Единственный способ идентифицировать определённую запись в этой таблице — это указать набор значений одного или нескольких полей, который был бы уникальным для этой записи. Отсюда и происходит понятие первичного ключа — набора полей (атрибутов, столбцов) таблицы, совокупность значений которых определена для любой записи (строки) этой таблицы и различна для любых двух записей.

Использование

Первичный ключ в таблице является базовым уникальным идентификатором для записей. Значение первичного ключа используется везде, где нужно указать на конкретную запись. На использовании первичных ключей основана организация связей между таблицами реляционной БД. Чтобы организовать между двумя таблицами связь типа «один к одному» или «один ко многим(многие к одному)» в одну из связываемых таблиц добавляют поле (поля), содержащее(ие) значение первичного ключа записи в связанной таблице (такое поле называют внешним ключом). Для организации связи типа «многие ко многим» создают отдельную таблицу (так называемую «таблицу связи» или «таблицу ассоциации»), каждая запись которой содержит первичные ключи двух связанных записей в разных таблицах.

Классификация

Простые и составные ключи

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

Естественные и суррогатные ключи

Первичный ключ может состоять из информационных полей таблицы (то есть полей, содержащих полезную информацию об описываемых объектах). Такой первичный ключ называют естественным ключом. Теоретически, естественный ключ всегда можно сформировать, в этом случае мы получим т. н. интеллектуальный ключ. На практике, однако, использование естественных ключей наталкивается на определённые сложности:

Вследствие этих и других соображений в практике проектирования БД чаще используют т. н. синтетические (суррогатные) ключи — искусственно созданные технические ключевые поля, не несущие информации об объектах.

Источник

Sysadminium

База знаний системного администратора

Первичный и внешний ключ SQL

Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.

Теория

Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:

К первичному ключу предъявляют следующее требование:

Первичный ключ может быть:

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

Связь между таблицами

Первостепенная задача первичного ключа – это уникальная идентификация каждой строки. Но первичный ключ может решить ещё одну задачу. В базе данных есть возможность связывания нескольких таблиц. Для такой связи используют первичный и внешний ключ sql. В одной из таблиц создают внешний ключ, который ссылается на поля другой таблицы. Но внешний ключ не может ссылаться на любые поля другой таблицы, а может ссылаться только на определённые:

Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:

Таблица pupils

И есть таблица “Успеваемость” (evaluations):

Таблица evaluations

В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “ Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.

Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “ Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.

Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:

Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.

Практика

Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:

Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.

Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:

Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.

Следующим шагом создадим таблицу evaluations:

В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.

Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.

Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.

Заполнение таблиц и работа с ними

Заполним таблицу “pupils“:

Заполним таблицу “evaluations“:

А теперь попробуем поставить оценку не существующему ученику:

Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.

Теперь удалим какого-нибудь ученика из таблицы pupils:

И посмотрим на строки в таблице evaluations:

Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.

Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:

Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.

Составной первичный ключ

Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.

Давайте удалим наши таблички и создадим их заново, но теперь создадим их используя составной первичный ключ:

Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.

Теперь посмотрим на структуры этих таблиц:

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

Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:

И также по второй таблице:

Удаление таблиц

Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:

Поэтому удалим наши таблицы в следующем порядке:

Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:

Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.

Создание связи в уже существующих таблицах

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

Вначале удалим оставшуюся таблицу:

И сделаем таблицы без ключей:

Теперь создадим первичный ключ в таблице pupils:

И создадим внешний ключ в таблице evaluations:

Посмотрим что у нас получилось:

В этой статье я рассказал про первичный и внешний ключ sql. А также продемонстрировал, как можно создать связанные между собой таблицы и как создать связь между уже существующими таблицами. Вы узнали, какие ограничения накладывает первичный ключ и какие задачи он решает. И вдобавок, какие требования предъявляются к нему. Вместе с тем я показал вам как работать с составным первичным ключом.

Дополнительно про первичный и внешний ключ sql можете почитать тут.

Источник

BestProg

Реляционные базы данных. Понятие ключа. Виды ключей. Отношения. Главная и подчиненная таблицы

В данной теме, на примере двух таблиц, определяются основные понятия реляционных баз данных, а именно:

Содержание

Поиск на других ресурсах:

Входные данные

Пусть задана база данных работников предприятия, которая состоит из двух таблиц. Первая таблица содержит данные о работнике. Вторая таблица содержит сведения о заработной плате работника.

Таблицы имеют следующую структуру.

Таблица «Работник». Содержит данные о работнике

Таблица «Зарплата». Содержит сведения о заработной плате работников.

Вопрос/ответ

1. Что такое первичный ключ в таблице базы данных? Для чего используются первичные ключи?

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

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

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

Пример. Для таблицы «Работник» можно ввести дополнительное поле, которое будет первичным ключом. Однако, поле (атрибут) «Табельный номер» также обеспечивает уникальность. Так как, теоретически, не может быть двух одинаковых табельных номеров. На практике могут быть случаи, что один и тот же табельный номер будет введен по ошибке и совпадут значения всех полей таблицы. В результате возникнут два одинаковых записи в таблице. Во избежание такой ошибки, лучше создать в таблице дополнительное поле-счетчик, которое обеспечит уникальность.

Также для таблицы «Зарплата» можно ввести дополнительное поле, которое будет первичным ключом.

2. Что такое отношение (связь) между таблицами (relationship)? Пример

Таблицы в реляционной модели данных могут иметь связи между собой. Такие связи называются отношениями. Для таблиц «Работник» и «Зарплата» можно установить связь по полю «Табельный номер».

Пример. Проанализируем таблицы «Работник» и «Зарплата». В этих таблицах можно установить отношение между таблицами на основе поля «Табельный номер». То есть, связь между таблицами происходит на основе поля (атрибуту) «Табельный номер».

Это означает следующее. Если нужно найти начисленную заработную плату в таблице «Зарплата» для работника Иванов И.И., то нужно выполнить следующие действия:

Рис. 1. Иллюстрация связи между таблицами. Табельный номер 2145 таблицы «Работник» отображается в таблице «Зарплата»

Рис. 2. Связь (отношение) между полями таблиц

3. Что такое внешний ключ (foreign key)? Пример

Понятие «внешний ключ» есть важным при рассмотрении связанных таблиц.

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

Пример. Пусть между таблицами «Работник» и «Зарплата» существует взаимосвязь по полю «Табельный номер». В этом случае, поле «Табельный номер» таблицы «Работник» может быть первичным ключом, а поле «Табельный номер» таблицы «Зарплата» внешним ключом. Это означает, что значения поля «Табельный номер» таблицы «Зарплата» заменяются значениями поля «Табельный номер» таблицы «Работник».

4. Что такое рекурсивный внешний ключ?

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

5. Могут ли первичный и внешний ключи быть простыми или составными (сложными)?

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

6. Какое отличие между искусственным и естественным ключом? Пример

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

Искусственный ключ вводится дополнительно для обеспечения уникальных значений. Чаще всего искусственный ключ есть полем типа счетчик (counter). В таком поле, при добавлении новой записи (строки) в таблицу, значение счетчика увеличивается на 1 (или другую величину). Если запись удалить из таблицы, то максимальное значение счетчика строк уже не уменьшается, а остается как есть. Как правило, за этим все следит система управления базами данных.

Пример. В таблице «Работник» естественном ключом есть поле (атрибут) «Табельный номер». Поле «Табельный номер» есть само по себе уникальным, так как не может быть двух работников с одинаковым табельным номером.

В таблице «Зарплата» значение во всех четырех полях могут случайно повториться. Поэтому, здесь целесообразно добавить дополнительное поле-счетчик, которое будет искусственным ключом. В этом случае таблица «Зарплата» с дополнительным полем может иметь приблизительно следующий вид:

где поле «Номер» есть искусственным ключом, который обеспечивает уникальность.

7. Какие существуют способы выбора первичного ключа?

Существует 3 способа выбора первичного ключа:

8. Что означают термины «главная таблица» (master) и «подчиненная таблица» (detail)?

Если между таблицами есть связь, то одна из них может быть главной (master), а другая подчиненной (detail). Главная таблица отображает все записи, которые помещаются в ней. Подчиненная таблица отображает только те записи, которые соответствуют значению ключа главной таблицы, который на данный момент есть активным (текущим). Если изменяется текущая запись главной таблицы, то изменяется множество доступных записей подчиненной таблицы.

Пример. Если рассмотреть таблицы «Работник» и «Зарплата», то таблица «Работник» есть главной, а таблица «Зарплата» есть подчиненной.

9. Какие существуют типы отношений (связей) между таблицами?

Существует 4 основных типа отношений между таблицами:

Пример. Если рассмотреть отношение между таблицами «Работник» и «Зарплата», то это отношения есть типа «один ко многим». Таблица «Работник» есть главной. Таблица «Зарплата» есть подчиненной.

Источник

ГОСы 2012

Реляционная модель (РМ) основана на понятии «отношения» (Relationship), она наиболее распространена сегодня.

Недостатками реляционной модели:

Основными понятиями модели являются: тип данных, домен, атрибут, кортеж, первичный ключ и отношение.

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

Под доменом понимают множество допустимых значений простого типа. Все элементы домена относятся к 1 типу данных и отвечают логическому условию. Элемент домена — число, символьная строка, дата и т. д.

Схемой отношения называют именованное множество пар (Ai, Di), i=1,k, где Ai — имя атрибута, Di — имя домена, k — ранг отношения. Например: Студенты ((имя, имена людей), (возраст, числа от 17 до 59), (номер паспорта, целые числа)) ранг=3.

Кортеж отношения — это множество пар вида «имя атрибута, значение атрибута», причем каждый атрибут отношения один и только один раз входит в кортеж. # (Саша, 19, 222222) или (Катя, 20, 353453) или (Настя, 18, 424242)

Отношение — это множество кортежей, соответствующих одной схеме отношения. Элементами отношений являются кортежи.
# R=

Фундаментальные свойства отношений:

Для связи между разными отношениями используется понятие внешнего ключа. Внешним ключом называется атрибут (совокупность атрибутов), который является ключом Ak в другом отношении R1 и его значения принадлежат домену Dk отношения R2, т.е отношение, в котором определен внешний ключ ссылается на другое отношение в котором такой же атрибут является первичным ключом.

Компоненты реляционной модели данных (согласно Дейту модель состоит из 3 частей)

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

В модели должны выполняться 2 базовых условия целостности:

РБД наиболее распространены в настоящее время. В РБД должна поддерживаться целостность данных. Если схема РБД неудачна, то она не обеспечивает этой целостности. Нарушение целостности называют аномалиями. Аномалии могут быть вызваны избыточностью отношений. Для устранения избыточности применяют нормализацию схем отношений.

Форма представления схемы РБД на каком-либо шаге этого процесса называется нормальной формой. Каждой нормальной форме соответствует некоторый определенный набор ограничений, и отношение находится в некоторой нормальной форме, если удовлетворяет свойственному ей набору ограничений. В РБД выделяется последовательность нормальных форм:

Основные свойства нормальных форм:

Дадим некоторые определения:

Функциональная зависимость. В отношении R атрибут Y функционально зависит от атрибута X — если каждому значению X соответствует в точности одно значение Y. Обозначается y:x→y (x функционально определяет y)

Полная функциональная зависимость. Функциональная зависимость y:x→y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X

Транзитивная функциональная зависимость. Функциональная зависимость y:x→y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости x →z и z→y (обратная зависимость отсутствует).

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

Неключевой атрибут — любой атрибут отношения, не входящий в состав первичного ключа.

Взаимно независимые атрибуты. Два или более атрибута называются взаимно независимыми, если не один из них не зависит функционально от других атрибутов.

Детерминант. Детерминантом называется любой атрибут, от которого полностью функционально зависит некоторый другой атрибут.

Нормализация

Первая нормальная форма (1NF)

Таблица находится в первой нормальной форме, если каждый её атрибут атомарен. Под выражением «атрибут атомарен» понимается, что атрибут может содержать только одно значение. Таким образом, не существует 1NF таблицы, в полях которых могут храниться списки значений. Для приведения таблицы к 1NF обычно требуется разбить таблицу на несколько отдельных таблиц.

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

Вторая нормальная форма (2NF)

Таблица находится во второй нормальной форме, если она находится в первой нормальной форме, и при этом любой её атрибут, не входящий в состав первичного ключа, функционально полно зависит от первичного ключа. Функционально полная зависимость означает, что атрибут функционально зависит от всего первичного составного ключа, но при этом не находится в функциональной зависимости от какой-либо из входящих в него атрибутов (частей). Или другими словами: в 2NF нет неключевых атрибутов, зависящих от части составного ключа (+ выполняются условия 1NF).

Третья нормальная форма (3NF)

Таблица находится в третьей нормальной форме (3NF), если она находится во второй нормальной форме 2NF и при этом любой ее неключевой атрибут зависит только от первичного ключа (Primary key, PK) (иначе говоря, один факт хранится в одном месте).

Таким образом, отношение находится в 3NF тогда и только тогда, когда оно находится во 2NF и отсутствуют транзитивные зависимости неключевых атрибутов от ключевых. Транзитивной зависимостью неключевых атрибутов от ключевых называется следующая: A → B и B → C, где A — набор ключевых атрибутов (ключ), B и С — различные множества неключевых атрибутов.

При решении практических задач в большинстве случаев третья нормальная форма является достаточной. Процесс проектирования реляционной базы данных, как правило, заканчивается приведением к 3NF.

Нормальная форма Бойса — Кодда (BCNF)

Это модификация третьей нормальной формы (в некоторых источниках именно 3NF называется формой Бойса — Кодда).

Таблица находится в BCNF, если она находится в 3NF, и при этом отсутствуют функциональные зависимости атрибутов первичного ключа от неключевых атрибутов. Таблица может находиться в 3NF, но не в BCNF, только в одном случае: если она имеет, помимо первичного ключа, ещё по крайней мере один возможный ключ. Все зависимые от первичного ключа атрибуты должны быть потенциальными ключами отношения. Если это условие не выполняется, для них создаётся отдельное отношение. Чтобы сущность соответствовала BCNF, она должна находиться в третьей нормальной форме. Любая сущность с единственным возможным ключом, соответствующая требованиям третьей нормальной формы, автоматически находится в BCNF.

Четвёртая нормальная форма (4NF)

Таблица находится в 4NF, если она находится в BCNF и не содержит нетривиальных многозначных зависимостей. Многозначная зависимость не является функциональной, она существует в том случае, когда из факта, что в таблице содержится некоторая строка X, следует, что в таблице обязательно существует некоторая определённая строка Y. То есть, таблица находится в 4NF, если все ее многозначные зависимости являются функциональными.

Пятая нормальная форма (5NF)

Доменно-ключевая нормальная форма (DKNF)

Отношение в ДКНФ не имеет аномалий модификации. Другими словами, что бы ни менялось — ничего не потеряется, если соблюдены все ограничения относительно ключей и доменов. Формулировка слишком общая, но суть ее заключается в том, что если выполнять некоторые правила, то при любых действиях с таблицей ее целостность не пострадает и вся необходимая информация сохранится. Если рассматривать на примере, то правила действуют примерно так: нельзя просто удалить категорию из таблицы категорий, если с этой категорией связаны, например, продукты из таблицы продуктов. Прежде чем удалять категорию, необходимо выполнить предварительные действия в таблице продуктов (например, поле отвечающее за id категории этого товара нужно сделать NULL).

Шестая нормальная форма (6NF)

Таблица находится в 6NF, если она находится в 5NF и удовлетворяет требованию отсутствия нетривиальных зависимостей. Зачастую 6NF отождествляют с DKNF.

Источник