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

На Рисунке представлена таблица (отношение степени 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НФ и каждый не ключевой атрибут не транзитивно зависит от первичного ключа.

Определены три основных класса сущностей:

1) Стержневые – независимая сущность. Названия помещены в прямоугольник.

2) Ассоциативные – связь вида многие ко многим между двумя или более сущностями. Ассоциации рассматриваются как полноправная сущность. Могут участвовать в других ассоциациях и обладать набором атрибутов.

a. Обозначения (обозначающая сущность) – связи вида многие к одной или одна к одной между двумя сущностями. Отличается от характеристики тем, что не зависит от обозначающей сущности.

3) Характеристические (характеристика) – связь вида многие к одной или одна к одной между двумя сущностями. Является частным случаем ассоциации. Единственная цель характеристики – описание или уточнение некоторой другой сущности. Существование характеристики полностью зависит о характеризуемой сущности.

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

Минимальность означает, что лексически из набора любого атрибута не позволяет идентифицировать сущность по оставшимся.

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

Если сущность С связывает сущности А и В, то она должна включать внешние ключи, соответствующие первичным ключам сущностей А и В.

Для каждого внешнего ключа необходимо решить три вопроса:

1) Может ли дополнительный внешний ключ принимать неопределенные значения (null-значения), короче говоря, может ли существовать некоторый экземпляр сущности, для которого известна целевая сущность, указанная внешним ключом.

2) Что должно происходить при попытке удаления целевой сущности, на которую ссылается внешний ключ.

Существуют три возможности решения данного вопроса:

· Каскадирование

· Ограничение

· Установление в определенное значение

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

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

Типы данных и домены.

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

Реляционная модель рассчитана на организацию данных в виде двумерных таблиц. Реляционная таблица представляет собой двумерный массив и обладает следующими свойствами:

1) Каждый элемент таблицы – один элемент данных

2) Все столбцы в таблице однородны – все элементы в столбце имеют одинаковый тип и длину данных

3) Каждый столбец имеет уникальное имя

4) Одинаковые строки в таблице отсутствуют

5) Порядок следования строки столбцов может быть произвольным

Типы данных

Любые данные, используемые в программировании, имеют свои типы данных. Реляционная модель требует, чтобы типы используемых данных были простыми.

Как правило, типы данных делятся на три группы:

1) Простые типы данных

2) Структурированные типы данных

3) ссылочные типы данных

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

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

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

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

Домен porno.ru

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

Домен – семантическое понятие. Его можно рассматривать как подмножество значений некоторого типа данных.

Свойства домена:

1) домен имеет уникальное имя в пределах базы данных

2) домен определен на некотором простом типе данных или на другом домене

3) домен может иметь некоторое логическое условие, позволяющее описать подмножество данных, допустимых для данного домена.

4) домен несет некоторую смысловую нагрузку

Например, некоторый домен D, имеющий смысл «возраст сотрудника» можно описать как некоторое подмножество множества натуральных чисел

D={nϵN: n ≥ 18 and n ≤ 60}

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

5. Отношения и их свойства, атрибуты и кортежи.
Понятие отношения является фундаментальным понятием реляционной модели данных. Атрибут отношения: <Имя_атрибута: Имя_домена>. Имена атрибутов должны быть уникальными в пределах отношения. Часто имена атрибутов совпадают с именами соответствующих доменов. Некоторое отношение R, определенное на множестве доменов D 1 ,D 2 ,…D n содержит две части: заголовок и тело. Заголовок отношения содержит фиксированное количество атрибутов отношения.

(,,…)

Тело отношения содержит множество картежей отношений. Каждый картеж отношений представляет собой множество пар вида

<Имя_атрибута: Значение_атрибута>

(,,… ).

При этом значение Val i принадлежит атрибуту A i D i . значение записывается:

R (,,…).

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

Реляционной базой данных называется набор отношений. Схемой реляционных баз данных называется набор заголовков отношений, входящий в базу данных.

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

Свойства отношений

В свойствах отношений в основном состоят различия между отношениями

1) В отношении не одинаковых картежей.

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

2) Картежи не упорядочены (сверху вниз) так как тело отношения – множество.

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

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

4) Все значения атрибутов атомарны.

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

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

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

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

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

Последнее обновление: 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.

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

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

Перевод

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

4. ТАБЛИЦЫ И ПЕРВИЧНЫЕ КЛЮЧИ

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

В таблице имеются 6 уроков. Все 6 – разные, но для каждого урока значения одинаковых полей хранятся в таблице, а именно: tutorial_id (идентификатор урока), title (заголовок)и category (категория). Tutorial_id – первичный ключ таблицы уроков. Первичный ключ – это значение, которое уникально для каждой записи в таблице.
В таблице клиентов ниже customer_id – первичный ключ. В данном случае первичный ключ – также уникальное значение (число) для каждой записи.

Первичные ключи в повседневной жизни

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

Несколько примеров

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

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

Что характеризует первичный ключ? Характеристики первичного ключа.

Первичный ключ служит для идентификации записей.

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

Первичный ключ уникален.

Первичный ключ всегда имеет уникальное значение. Представьте, что его значение не уникально. Тогда его бы нельзя было использовать для того, чтобы идентифицировать данные в таблице. Это значит, что какое-либо значение первичного ключа может встретиться в столбце, который выбран в качестве первичного ключа, только один раз. РСУБД устроены так, что не позволят вам вставить дубликаты в поле первичного ключа, получите ошибку.
Еще один пример. Представьте, что у вас есть таблица с полями first_name и last_name и есть две записи:

Т.е. есть два Васи. Вы хотите выбрать из таблицы какого-то конкретного Васю. Как это сделать? Записи ничем друг от друга не отличаются. Вот здесь и помогает первичный ключ. Добавляем столбец id (классический вариант синтетического первичного ключа) и…

Теперь каждый Вася уникален.

Типы первичных ключей.

Обычно первичный ключ – числовое значение. Но он также может быть и любым другим типом данных. Не является обычной практикой использование строки в качестве первичного ключа (строка – фрагмент текста), но теоретически и практически это возможно.
Составные первичные ключи.
Часто первичный ключ состоит из одного поля, но он может быть и комбинацией нескольких столбцов, например, двух (трех, четырех…). Но вы помните, что первичный ключ всегда уникален, а значит нужно, чтобы комбинация n-го количества полей, в данном случае 2-х, была уникальна. Подробнее об этом расскажу позднее.

Автонумерация.

Поле первичного ключа часто, но не всегда, обрабатывается самой базой данных. Вы можете, условно говоря, сказать базе данных, чтобы она сама автоматически присваивала уникальное числовое значение каждой записи при ее создании. База данных, обычно, начинает нумерацию с 1 и увеличивает это число для каждой записи на одну единицу. Такой первичный ключ называется автоинкрементным или автонумерованным. Использование автоинкрементных ключей – хороший способ для задания уникальных первичных ключей. Классическое название такого ключа – суррогатный первичный ключ [Как и упоминалось выше. – прим. перев.]. Такой ключ не содержит полезной информации, относящейся к сущности (объекту), информация о которой хранится в таблице, поэтому он и называется суррогатным.

5. СВЯЗЫВАНИЕ ТАБЛИЦ С ПОМОЩЬЮ ВНЕШНИХ КЛЮЧЕЙ

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

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

Один-ко-многим.

Клиенты и заказы имеют связь (состоят в отношениях) один-ко-многим потому, что один клиент может иметь много заказов, но каждый конкретный заказ (их множество ) оформлен только одним клиентом, т.е. может иметь только одного клиента. Не беспокойтесь, если на данный момент понимание этой связи смутно. Я еще расскажу о связях в следующих частях.

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

Какую информацию мы будем хранить? Решаем первый вопрос.

Для начала мы определимся какую информацию о заказах и о клиентах мы будем хранить. Чтобы это сделать мы должны задать себе вопрос: “Какие единичные блоки информации относятся к клиентам, а какие единичные блоки информации относятся к заказам?”

Проектируем таблицу клиентов.

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

customer_id (primary key) – идентификатор клиента
first_name - имя
last_name - отчество
address - адрес
zip_code – почтовый индекс
country - страна
birth_date – дата рождения
username – регистрационное имя пользователя (логин)
password – пароль

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

Создание таблицы в SQLyog. Обратите внимание, что выбран флажок первичного ключа (PK) для поля customer_id. Поле customer_id является первичным ключом. Также выбран флажок Auto Incr, что означает, что база данных будет автоматически подставлять уникальное числовое значение, которое, начиная с нуля, будет каждый раз увеличиваться на одну единицу.

Проектируем таблицу заказов.
Какие минимальные блоки информации, необходимые нам, относятся к заказу?

order_id (primary key) – идентификатор заказа
order_date – дата и время заказа
customer – клиент, который сделал заказ

Ниже – пример таблицы в SQLyog.

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

Создание связи по внешнему ключу.

Вы можете задаться вопросом: “Каким образом я могу убедиться или как я могу увидеть, что поле customer в таблице заказов ссылается на поле customer_id в таблице клиентов”. Ответ прост – вы не можете сделать этого потому, что я еще не показал вам как создать связь.
Ниже – окно SQLyog с окном, которое я использовал для создания связи между таблицами.

Создание связи по внешнему ключу между таблицами заказов и клиентов.

В окне выше вы можете видеть, как поле customer таблицы заказов слева связывается с первичным ключом (customer_id) таблицы клиентов справа.

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

Заказы связаны с клиентами через поле customer, которое ссылается на таблицу клиентов.

На изображении вы видите, что клиент mary поместила три заказа, клиент pablo поместил один, а клиент john – ни одного.
Вы можете спросить: “А что же именно заказали все эти люди?” Это хороший вопрос. Вы возможно ожидали увидеть заказанные товары в таблице заказов. Но это плохой пример проектирования. Как бы вы поместили множественные продукты в единственную запись? Товары – это отдельные сущности, которые должны храниться в отдельной таблице. И связь между таблицами заказов и товаров будет являться связью один-ко-многим. Я расскажу об этом далее.

6. СОЗДАНИЕ ДИАГРАММЫ СУЩНОСТЬ-СВЯЗЬ

Ранее вы узнали как записи из разных таблиц связываются друг с другом в реляционных базах данных. Перед созданием и связыванием таблиц важно, чтобы вы подумали о сущностях , которые существуют в вашей системе (для которой вы создаете базу данных) и решили каким образом эти сущности бы связывались друг с другом. В проектировании баз данных сущности и их отношения обычно предоставляются в диаграмме сущность-связь (англ. entity-relationship diagram, ERD) . Данная диаграмма является результатом процесса проектирования базы данных.

Сущности.

Вы можете задаться вопросом, что же такое сущность. Нуу… это “вещь” в системе. Там. Моя Мама всегда хотела, чтобы я стал учителем потому, что я очень хорошо объясняю различные вещи.

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

Давайте возьмем интернет-магазин для примера. Интернет-магазин продает товары . Товар мог бы стать очевидной сущностью в системе интернет-магазина. Товары заказываются клиентами . Вот мы с вами и увидели еще две очевидных сущности: заказы и клиенты .

Заказ оплачивается клиентом… это интересно. Мы собираемся создавать отдельную таблицу для платежей в базе данных нашего интернет-магазина? Возможно. Но разве платежи – это минимальный блок информации, который относится к заказам? Это тоже возможно.

Если вы не уверены, то просто подумайте о том, какую информацию о платежах вы хотите хранить. Возможно, вы захотите хранить метод платежа или дату платежа . Но это все еще минимальные блоки информации, которые могли бы относиться к заказу . Можно изменить формулировки. Метод платежа - метод платежа заказа. Дата платежа – дата платежа заказа. Таким образом, я не вижу необходимости выносить платежи в отдельную таблицу, хотя концептуально вы бы могли выделить платежи как сущность, т.к. вы могли бы рассматривать платежи как контейнер информации (метод платежа, дата платежа).

Давайте не будет слишком академичными.

Как вы видите, есть разница между сущностью и непосредственно таблицей в базе данных, т.е. это не одно и то же. Специалисты отрасли информационных технологий могут быть ОЧЕНЬ академичными и педантичными в этом вопросе. Я не такой специалист. Эта разница зависит от вашей точки зрения на ваши данные, вашу информацию. Если вы смотрите на моделирование данных с точки зрения программного обеспечения, то вы можете прийти к множеству сущностей, которые нельзя будет перенести напрямую в базу данных. В данном руководстве мы смотрим на данные строго с точки зрения баз данных и в нашем маленьком мире сущность – это таблица.

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

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

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

Связи.

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

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

Таблицы

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

Таблица – это некоторая регулярная структура, состоящая из конечного набора однотипных записей.

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

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

В любой таблице всегда есть как минимум 1 столбец. В стандарте ANSI/ISO не указывается максимально допустимое число столбцов в таблице, однако почти во всех коммерческих СУБД этот предел существует. В СУБД Firebird этот предел составляет 32 767 столбцов.

В РМДреляционной модели данных для обозначения строки отношения используется понятие кортеж. Представлением кортежа на физическом уровне является строка таблицы базы данных. Строки таблицы не имеют имен и определенного порядка. В таблице может содержаться любое количество строк. Вполне допустимо существование таблицы с нулевым количеством строк. Такая таблица называется пустой. Пустая таблица сохраняет структуру, определенную ее столбцами, просто в ней не содержатся данные. Как правило, не накладывается ограничений на количество строк в таблице, и во многих СУБД размер таблиц ограничен лишь свободным дисковым пространством компьютера. В других СУБД имеется максимальный предел, однако он весьма высок – около двух миллиардов строк, а иногда и больше.

Проиллюстрируем более наглядно структуру одной из таблиц учебной базы данных (см. приложение А). На рис. 1.1 приведена структура таблицы Abonent, содержащей сведения об абонентах компаний жилищно-коммунального хозяйства.

Рис. 1.1. Структурареляционной таблицы Abonent

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

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

На пересечении каждой строки с каждым столбцом таблицы содержится в точности одно значение данных. Например, в строке, представляющей абонента Конюхова В. С., в столбце Fio содержится значение "Конюхов В.С.". В столбце AccountCD той же строки содержится значение "015527", которое является номером лицевого счета абонента с ФИО Конюхов В. С.

Все значения, содержащиеся в одном и том же столбце, являются данными одного типа. Например, в столбце Fio содержатся только слова, а в столбце StreetCD содержатся целые числа, представляющие идентификаторы улиц. В реляционной модели данных общая совокупность значений, из которой берутся действительные значения для определенных атрибутов (столбцов), называется доменом . Доменом столбца Fio, например, является множество фамилий, имен и отчеств (ФИО) абонентов. Каждый столбец всегда определяется на одном домене.

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

В учебной базе данных определены следующие домены:

§ Boollean (Логический): SMALLINT . Поля, определяемые на этом домене, могут принимать только целочисленные значения, равные 0 или 1. Это достигается наложением в домене условия проверки (CHECK) на принимаемые этим доменом значения.

§ Money (Деньги): NUMERIC(15,2) . Домен предназначен для определения в таблицах полей, хранящих денежные суммы.

§ PKField (Поле ПК): INTEGER . Домен предназначен для определения первичных и внешних ключей таблиц. Ограничение обязательности данных (NOT NULL) на этот домен не наложено. Оно накладывается при объявлении первичного ключа таблицы. Это сделано для того, чтобы можно было определить внешний ключ на этом домене без условия NOT NULL.

§ TMonth (Месяц): SMALLINT . Домен предназначен для определения в таблицах полей, содержащих номера месяцев. Целочисленные значения в таком поле могут находиться в диапазоне 1...12.

§ TYear (Год): SMALLINT . Домен предназначен для определения полей, содержащих номер года. Целочисленные значения могут находиться в диапазоне 1990...2100.

Поскольку строки в реляционной таблице не упорядочены, то нельзя выбрать строку по ее номеру в таблице. В таблице нет «первой», «последней» или «тринадцатой» строки. Тогда каким же образом можно указать в таблице конкретную строку, например строку для абонента с ФИО Аксенов С.А.?

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

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

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

Вернемся к рассмотрению таблицы Abonent учебной базы данных (рис. 1.1). На первый взгляд, первичным ключом таблицы Abonent могут служить и столбец AccountCD, и столбец Fio. Однако в случае если будут зарегистрированы 2 абонента с одинаковыми ФИО, то столбец Fio больше не сможет исполнять роль первичного ключа. На практике в качестве первичных ключей таблиц обычно следует выбирать идентификаторы, такие как уникальный номер лицевого счета абонента (AccountCD в таблице Abonent), идентификатор улицы (StreetCD в таблице Street) и т. д.

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

Если первичный ключ представляет собой комбинацию столбцов, то такой первичный ключ называется составным .

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