narod.rubd-info.narod.ru/materials_bd_2020.docx · web viewМатериалы по...
Post on 24-Oct-2020
4 Views
Preview:
TRANSCRIPT
Материалы по дисциплине «Базы данных».Модели систем управления данными: сетевая, иерархическая, реляционная модель. Нормальные формы (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
top related