Материалы по дисциплине «Базы данных».Модели систем управления данными: сетевая, иерархическая, реляционная модель. Нормальные формы (1НФ, 2НФ, 3НФ, НФБК).

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

Модели данных

Объектные модели данных

Примеры:

Модель типа "сущность-связь", или ER-модель (Entity-Relationship model)

Объектно-ориентированная модель

Модели данных на основе записей

3 основных типа:

Реляционная модель данных

Сетевая модель данных

Иерархическая модель данных

Физические модели данных

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

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

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

Сущность Branch (отделения компании):

BranchId

Street

City

1

22 Deer Rd

London

2

16 Argyll St

Aberdeen

Сущность Staff (сотрудники компании):

StaffId

FirstName

LastName

BranchId

1

John

White

1

2

Ann

Ford

2

3

David

Brand

1

Пользователь реляционной системы видит данные в виде таблиц и никак иначе. Важной отличительной особенностью реляционных систем является отсутствие указателей между записями (между отношениями Staff и Branch нет явно заданной связи; ее существование можно заметить, только зная, что атрибут BranchId в отношении Staff эквивалентен атрибуту BranchId в отношении Branch).

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

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

Пример отношения StaffBranch, содержащего избыточные данные:

StaffId

Name

Position

Salary

BranchId

BranchAddress

1

John White

Manager

30000

1

22 Deer Rd, London

2

David Ford

Assistant

12000

2

163 Main St, Glasgow

3

Susan Brand

Supervisor

18000

1

22 DeerRd, London

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

· Аномалии вставки. 1. При вставке сведений о новых сотрудниках в отношение Staf fBranch необходимо указать и сведения об отделении компании, в котором эти сотрудники работают. 2. Непонятно, как вставить сведения о новом отделении компании, которое еще не имеет собственных сотрудников.

· Аномалии удаления. При удалении из отношения StaffBranch строки с информацией о последнем сотруднике некоторого отделения компании сведения об этом отделении будут полностью удалены из базы данных.

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

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

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

Первая нормальная форма (1НФ) – отношение, в котором на пересечении каждой строки и каждого столбца содержится одно и только одно значение.

Пример ненормализованной формы:

ClientId

ClientName

PropertyId

PropertyAddress

1

John Kay

1

2

6 Lawrence St Glasgow

5 Novar Dr, Glasgow

2

Aline Stewart

3

4

6 Lawrence St, Glasgow

2 Manor Rd, Glasgow

В таблице хранится, какой клиент какой объект недвижимости арендует. Предполагается, что один и тот же клиент не может дважды арендовать один и тот же объект недвижимости. Видно, что клиенту John Kay соответствует 2 объекта (1 и 2), в результате на пересечении некоторых строк и столбцов находится 2 значения.

После приведения к 1НФ таблица ClientRental примет следующий вид:

ClientId

ClientName

PropertyId

PropertyAddress

1

John Kay

1

6 Lawrence St, London

1

John Kay

2

5 Novar Dr, Glasgow

2

Aline Stewart

1

6 Lawrence St, London

2

Aline Stewart

2

5 Novar Dr, Glasgow

Вторая нормальная форма (2НФ). Необходимо ввести следующие определения.

Функциональная зависимость. Описывает связь между атрибутами отношения. Если в отношении R, содержащем атрибуты А и В, атрибут B функционально зависит от атрибута А, то каждое значение атрибута А связано только с одним значением атрибута B. (Атрибуты A и B могут состоять из одного или нескольких атрибутов). В примере выше атрибут ClientAddress функционально зависит от атрибута ClientId. Обозначение: A→B (ClientId→ClientAddress).

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

Полная функциональная зависимость. Если А и B – атрибуты отношения, то атрибут B находится в полной функциональной зависимости от атрибута А, если атрибут B является функционально зависимым от А, но не зависит ни от одного собственного подмножества атрибута A. Собственное подмножество не включает пустое подмножество и само множество. (Простыми словами: A не содержит «лишних» атрибутов).

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

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

Рассмотрим получившуюся выше таблицу в 1НФ. Предполагается, что каждый клиент может арендовать объект недвижимости только однажды, поэтому первичным ключом является пара атрибутов (ClientId, PropertyId) (ClientId не является первичным ключом: как видно по таблице, клиент может встречаться несколько раз). Рассмотрим функциональную зависимость ClientId ClientName. Имеет место нарушение 2НФ: атрибут ClientName зависит от части первичного ключа – ClientId, а не от весго первичного ключа (ClientId, PropertyId). Аналогично, PropertyAddress зависит от части первичного ключа – атрибута PropertyId. Необходимо разбить отношение на 3:

Client:

ClientId

ClientName

1

John Kay

2

Aline Stewart

Property:

PropertyId

PropertyAddress

1

6 Lawrence St, London

2

5 Novar Dr, Glasgow

ClientRental:

ClientId

PropertyId

1

1

1

2

2

1

2

2

Третья нормальная форма (3НФ). Введем следующее определение.

Транзитивная зависимость. Если для атрибутов А, B и C некоторого отношения существуют зависимости вида A B и B C, это означает, что атрибут C транзитивно зависит от атрибута А через атрибут B (при условии, что атрибут А функционально не зависит ни от атрибута B, ни от атрибута C). Транзитивная зависимость является одним из типов функциональной зависимости.

Третья нормальная форма (ЗНФ). Отношение, которое находится в первой и во второй нормальных формах и не имеет атрибутов, не входящих в первичный ключ, которые находились бы в транзитивной функциональной зависимости от этого первичного ключа. [Частное определение, в котором не учитываются потенциальные ключи, кроме первичного.]

Третья нормальная форма (ЗНФ) – отношение, находящееся в первой и второй нормальной форме, в котором ни один атрибут, отличный от атрибута первичного ключа, не является транзитивно зависимым ни от одного потенциального ключа. [Общее определение 3НФ.]

По-другому можно сформулировать так: отношение находится в 3НФ в том и только том случае, если все неключевые атрибуты отношения взаимно независимы и полностью зависят от первичного ключа.

Рассмотрим в качестве примера приведенное выше отношение StaffBranch:

StaffId

Name

Position

Salary

BranchId

BranchAddress

1

John White

Manager

30000

1

22 Deer Rd, London

2

David Ford

Assistant

12000

2

163 Main St, Glasgow

3

Susan Brand

Supervisor

18000

1

22 Deer Rd, London

Атрибут BranchAddress зависит от BranchId, который в свою очередь функционально зависит от первичного ключа. Это пример нарушения 3НФ. Необходимо разбить отношение на два:

Staff:

StaffId

Name

Position

Salary

BranchId

1

John White

Manager

30000

1

2

David Ford

Assistant

12000

2

3

Susan Brand

Supervisor

18000

1

Branch:

BranchId

BranchAddress

1

22 Deer Rd, London

2

163 Main St, Glasgow

1

22 Deer Rd, London

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

Нормальная форма Бойса-Кодда (НФБК). Отношение находится в НФБК тогда и только тогда, когда каждый его детерминант является потенциальным ключом.

Применим данное определение к отношению StaffBranch. Детерминант BranchId (BranchId является детерминантом, так как от него зависит BranchAddress) не является потенциальным ключом, следовательно, НФБК нарушена. Необходимо провести декомпозицию отношений, что и было сделано.

В подавляющем большинстве случаев, если отношение находится в 3НФ,оно находится и в НФБК. Часто нормализацию проводят до 3НФ.

Реляционная модель. Отношения. Терминология, ключи, реляционная алгебра. Реляционная целостность.

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

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

Отношение – плоская таблица, состоящая из столбцов и строк.

Атрибут – именованный столбец отношения.

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

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

Кортеж – строка отношения. Элементами отношения являются кортежи, или строки, таблицы.

Степень отношения – количество атрибутов, которые оно содержит.

Кардинальность – количество кортежей, которые содержатся в отношении.

Отношение обладает следующими характеристиками:

· Отношение имеет имя, которое отличается от имен всех других отношений в реляционной схеме.

· Каждая ячейка отношения содержит только одно элементарное (неделимое) значение.

· Каждый атрибут имеет уникальное имя.

· Значения атрибута берутся из одного и того же домена.

· Каждый кортеж является уникальным, т.е. дубликатов кортежей быть не может (в современных СУБД данное требование, как правило, не учитывается).

· Порядок следования атрибутов не имеет значения.

· Порядок следования кортежей в отношении не имеет значения.

Суперключ (superkey) – атрибут или множество атрибутов, которое единственным образом идентифицирует кортеж данного отношения.

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

Первичный ключ – потенциальный ключ, который выбран для уникальной идентификации кортежей внутри отношения.

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

Реляционная целостность. Модель данных содержит набор ограничений целостности, которые гарантируют корректность данных.

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

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

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

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

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

а) Выборка.

Операция выборки (унарная операция) применяется к одному отношению R и определяет результирующее отношение, которое содержит только те кортежи (строки) из отношения R, которые удовлетворяют заданному условию (предикату).

Пример: составьте список всех сотрудников с зарплатой, превышающей 10000 фунтов стерлингов.

На языке SQL был бы написан запрос select * from Staffwhere salary > 1000

Более сложные предикаты могут быть созданы с помощью логических операций AND, OR, NOT.

б) Проекция.

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

Пример: создайте ведомость зарплаты всех сотрудников компании с указанием только атрибутов staffNo, fName, lName, salary.

SQL: select staffNo, fName, lName, salary from Staff

г) Объединение.

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

Пример: cоздайте список всех городов, в которых имеется отделение компании или объект недвижимости.

SQL:

(select city

from Branch)

UNION

(select city

from PropertyForRent)

д) Пересечение.

Операция пересечения определяет отношение, которое содержит кортежи, присутствующие как в отношении R, так и в отношении S. Отношения R и S должны быть совместимыми по объединению.

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

SQL:

(select city

from Branch)

INTERSECT

(select city

from PropertyForRent)

е) Разность множеств.

Разность двух отношений R и S состоит из кортежей, которые имеются в отношении R, но отсутствуют в отношении S. Отношения R и S должны быть совместимыми по объединению.

Пример: создайте список всех городов, в которых есть отделение компании, но нет объектов недвижимости, сдаваемых в аренду.

SQL:

(select city

from Branch)

EXCEPT

(select city

from PropertyForRent)

в) Декартово произведение.

Операция декартова произведения определяет новое отношение, которое является результатом конкатенации (т.е. сцепления) каждого кортежа из отношения R с каждым кортежем из отношения S.

Пример: создайте список всевозможных пар арендаторов и сделанных комментариев (независимо от того, кем сделаны данные комментарии).

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c, Viewning v

Результат:

Client.clientNo

fName

lName

Viewing.clientNo

propertyNo

comment

1

John

Kay

1

1

Too small

1

John

Kay

2

2

Too remote

2

Aline

Stewart

1

1

Too small

2

Aline

Stewart

2

2

Too remote

Обратите внимание, что в результирующую таблицу попали все возможные комбинации записей независимо; для клиента отображаются даже комментарии, сделанные другими клиентами (те записи, где различаются значения в полях Client.clientNo и Viewing.clientNo). Для того, чтобы оставить только комментарии самих клиентов, необходимо добавить выборку:

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c, Viewning v

where c.ClientNo = v.ClientNo

Комбинация декартова произведения и выборки может быть сведена к одной операции соединения (см. ниже).

ж) Тета-соединение

Операция тета-соединения определяет отношение, которое содержит кортежи из декартова произведения отношений R и S, удовлетворяющие предикату F. Предикат имеет вида , где вместо может быть указана одна из операций сравнения (<, <=, >, >=, = или ~=).

Обозначение тета-соединения можно переписать на основе базовых операций выборки и декартова произведения:

Пример: создайте список арендаторов и сделанных ими комментариев.

Помимо указанного выше способа с помощью тета-соединения можно записать так:

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c join Viewning v

on c.ClientNo = v.ClientNo

з) Соединение по эквивалентности.

Если предикат F тета-соединения содержит только операцию сравнения по равенству (=), то соединение называется соединением по эквивалентности (equi-join). Приведенный выше пример как раз является примером соединения по эквивалентности.

и) Естественное соединение.

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

Пример: создайте список арендаторов и сделанных ими комментариев.

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c natural join Viewning v

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

к) Внешнее соединение.

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

Пример: создайте полный список арендаторов и сделанных ими комментариев.

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c left join Viewning v

on c.ClientNo = v.ClientNo

Client.clientNo

fName

lName

Viewing.clientNo

propertyNo

comment

1

John

Kay

1

1

Too small

2

Aline

Stewart

2

2

Too remote

3

Mike

Andrews

NULL

NULL

NULL

В результате был выдан также клиент номер 3, не сделавший ни просмотра; в поля, которые должны были быть заполнены из таблицы Viewing, был проставлен NULL.

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

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c right join Viewning v

on c.ClientNo = v.ClientNo

Еще один вариант – полное внешнее соединение (), когда в результат включаются все записи как отношения R, так и отношения S, даже если для них нет соответствующих записей в другом отношении.

SQL:

select c.clientNo, fName, lName, v.clientNo, propertyNo, comment

from Client c full join Viewning v

on c.clientNo = v.clientNo

л) Полусоединение.

Операция полусоединения определяет отношение, содержащее те кортежи отношения R, которые входят в соединение отношений R и S.

Пример: создайте отчет, содержащий полную информацию обо всех сотрудниках, работающих в отделении компании, расположенном в городе Glasgow.

SQL:

select c.clientNo, fName, lName

from Client c join Branch b

on c.branchNo = b.branchNo

where b.branchNo=’Glasgow’

м) Операция деления.

Схематично результат деления представлен ниже:

Предположим, что отношение R определено на множестве атрибутов А, а отношение S — на множестве атрибутов В, причем (т.е. В является подмножеством А). Пусть , т.е. С является множеством атрибутов отношения R, которые не являются атрибутами отношения S. Тогда определение операции деления будет выглядеть следующим образом.

Результатом операции деления является набор кортежей отношения R, определенных на множества атрибутов C, которые соответствуют комбинации всех кортежей отношения S.

Рассмотрим на примере, показанном на рисунке справа. Отношение V содержит 2 атрибута (A и B), отношение W содержит 1 атрибут B. Множество атрибутов отношения W является подмножеством атрибутов отношения V. Соответственно, результатом деления V на W будет отношение, содержащее единственный атрибут A из отношения V, которого нет в W.

Далее, в V у атрибута A есть 3 значения: a, b и c. В W у атрибута B есть 2 значения: 1 и 2. Столбец A из V и столбец B из W дают 6 комбинаций: a,1; a,2; b,1; b,2; c,1; c,2. Все комбинации, которые возможны для значения a, есть в отношении V, поэтому a оказалось в результате деления V на W; аналогично в V есть все комбинации для b, поэтому b оказалось в результате. Для c в V не хватает одной комбинации, поэтому c в результате нет.

Операция деления может быть определена через другие операции:

( – операция присваивания)

Пример: создайте список всех арендаторов, которые осмотрели все объекты недвижимости с тремя комнатами.

SQL (возможны варианты):

select distinct clientNo from Viewing v1

where not exists

(select propertyNo from PropertyForRent p

where not exists

(select * from Viewng v2

where v2.clientNo = v1.clientNo = and v2.propertyNo = p.propertyNo)

).

(

1000

salary

Staff

>

s

).

(

,...,

a

1

R

П

n

a

).

(

salary

lName,

fName,

staffNo,

Staff

П

.

S

R

È

).

Re

(Pr

)

(

city

city

nt

opertyFor

П

Branch

П

È

.

S

R

Ç

).

Re

(Pr

)

(

city

city

nt

opertyFor

П

Branch

П

Ç

.

S

R

-

).

Re

(Pr

)

(

city

city

nt

opertyFor

П

Branch

П

-

.

S

R

´

).

(

)

(

,

propertyNo

clientNo,

lName

fName,

clientNo,

Viewing

П

Client

П

comment

´

)).

(

)

(

(

,

propertyNo

clientNo,

lName

fName,

clientNo,

.

.

Viewing

П

Client

П

comment

ClientNo

Viewing

clientNo

Client

´

=

s

.

S

R

F

><

i

i

b

S

a

R

.

.

Q

).

(

S

R

S

R

F

F

´

=

s

><

)).

(

)

(

,

propertyNo

clientNo,

.

.

lName

fName,

clientNo,

Viewing

П

Client

П

comment

ClientNo

Viewing

clientNo

Client

=

><

.

S

R

><

)).

(

)

(

,

propertyNo

clientNo,

lName

fName,

clientNo,

Viewing

П

Client

П

comment

><

.

S

R

<

É

)).

(

)

(

,

propertyNo

clientNo,

lName

fName,

clientNo,

Viewing

П

Client

П

comment

<

É

S

R

Ì

>

S

R

ÉÌ

.

S

R

F

>

.

'

'

.

.

.

Branch

Staff

Glasgow

city

branch

and

branchNo

Branch

branchNo

Staff

=

=

>

A

B

Ì

B

A

C

-

=

.

S

R

¸

2

1

)

)

T

C((S

2

C

1

1

)

(

T

T

T

П

T

R

П

T

R

-

¬

¬

¬

-

´

))).

Re

(Pr

(

(

))

(

(

3

propertyNo

cientNo,

nt

opertyFor

П

Viewing

П

rooms

propertyNo

=

¸

s

R).

(

предикат

s