Для реляционной БД проектирование логической структуры заключается в том, чтобы разбить всю информацию по таблицам, определив состав полей для каждой из этих таблиц.
Для перехода от ИЛМ к реляционной можно воспользоваться следующими рекомендациями:
1) Для каждого простого объекта и его единичных свойств строится таблица, атрибутами которой являются идентификатор объекта и реквизиты, соответствующие каждому из единичных свойств:
2) Если у объекта имеются множественные свойства, то каждому из них ставится в соответствие отдельная таблица:
3) Если между объектом и его свойством имеется условная связь, то при отображении в реляционную модель возможны следующие варианты:
· если многие из объектов обладают рассматриваемым свойством, то его можно хранить в БД так же, как и обычное свойство;
· если только незначительное число объектов обладает указанным свойством, то при использовании предыдущего решения для многих записей в таблице значение соответствующего поля будет пустым. Для устранения этого недостатка выделяют отдельную таблицу, которая будет включать в себя идентификатор объекта и атрибут, соответствующий рассматриваемому свойству.
4) Если у объекта имеется составное свойство, то составляющие составного свойства либо помещаются в отдельные поля реляционной таблицы, либо в одно поле:
5) Если связь между объектами 1:1 и классы принадлежности обоих объектов являются обязательными, то для отображения данных объектов и связи между ними:
· можно использовать одну таблицу, первичным ключом которой может быть идентификатор любого из двух объектов;
· можно для каждого из этих объектов использовать отдельные таблицы, а связь между ними отразить, включив в одну из таблиц идентификатор связанного объекта из другой таблицы.
6) Если связь между объектами 1:1 и класс принадлежности одного объекта является обязательным, а другого – необязательным, то для каждого из этих объектов используют отдельные таблицы, а идентификатор объекта, для которого класс принадлежности является необязательным, добавляется в таблицу, соответствующую тому объекту, для которого класс принадлежности обязательный:
7) Если связь между объектами 1:1 и класс принадлежности каждого объекта является необязательным, то следует использовать три таблицы: по одной для каждого объекта и одну для отображения связи между ними:
8) Если между объектами предметной области имеется связь 1:М и класс принадлежности n – связного объекта является обязательным, то используют две таблицы: по одной для каждого объекта и в таблицу, соответствующую n – связному объекту, добавляется идентификатор 1 – связного объекта:
9) Если между объектами предметной области имеется связь 1:М и класс принадлежности n - связного объекта является необязательным, то создают три таблицы: по одной для каждого объекта и одну для отображения связи между ними:
10) Если между объектами предметной области имеется связь М:М, то для хранения такой информации потребуется три таблицы: по одной для каждого объекта и одна для отображения связи между ними (классы принадлежности могут быть: оба – обязательными, оба - необязательными, один – обязательный, другой – необязательный):
11) Агрегированному объекту, имеющему место в предметной области, в ДЛМ ставится в соответствие одна таблица, атрибутами которой являются идентификаторы всех объектов, «задействованных» в данном агрегированном объекте, а также реквизиты, соответствующие свойствам этого объекта:
Такое объединение информации в одну таблицу возможно только в том случае, если между объектами имеется связь 1:1. Если же между объектами имеется связь М:1 (или М:М), то выделяют по одной таблице для каждого объекта и одну таблицу для связи:
R1 (ИО1, …)
R2 (ИО2, …)
R3 (ИО3, …)
R4 (ИО1, ИО2, ИО3, С1).
12) При отображении обобщенных объектов могут быть приняты разные решения:
· всему обобщенному объекту может быть поставлена в соответствие одна таблица:
· каждой из категорий ставится в соответствие отдельная таблица, то есть:
R1 (ИО1, С1, С2, С4, С5)
R2 (ИО1, С1, С2, С6, С7).
Кроме этих двух «крайних» решений, возможны и комбинированные варианты. Выбор конкретного решения будет зависеть от многих факторов, в том числе от того насколько часто информация о разных категориях объекта обрабатывается совместно, как велико различие в «видовых» свойствах и др.
13) При отображении составного объекта также возможны разные решения:
· если речь идет о составе изделий, то между «изделием» и «деталью» имеется связь типа М:М (одна деталь может входить в разные изделия и, наоборот, в изделие входят разные детали) – в этом случае для отображения связи «целое – часть» можно использовать три таблицы. В первой двух будет храниться информация о самих объектах, в третьей – информация о связи между ними и характере этой связи (для состава изделия это могут быть следующие поля: «что входит», «куда входит», «количество»);
· если речь идет о составе какой-то организации, то между объектами скорее всего будет связь типа 1:М – в этом случае для отображения связи «целое – часть» можно использовать рекомендации пунктов 8 и 9.
Реляционная модель БД, получаемая в результате использования предложенных рекомендаций, будет нормализованной и автоматически находиться в 4-й нормальной форме (вопросы нормализации рассматриваются в пятом разделе пособия).
Рис. 3.7. ER – модель предметной области
Рассмотрим упрощенный пример предметной области, ER – модель которой изображена на рис. 3.7.
Соответствующая ей даталогическая модель базы данных может иметь следующий вид:
Факультет (Код_фак, Кр_наим_фак, Полн_наим_фак);
Кафедра (Код_каф, Кр_наим_каф, Полн_наим_каф, Код_фак);
Сотрудник (Таб_номер, ФИО, Дата_рождения, Пол, Код_каф, Должность, Уч_степень);
Студент (Таб_номер, ФИО, Дата_рождения, Пол, Код_фак, Дата_поступления, Ступень_обучения);
Ин_яз (Код_языка, наименование_языка);
Знание_ин_яз (Таб_номер, Код_языка, Степень_владения);
Дети (Таб_номер, Имя, Дата_рождения).
Тесты для самоконтроля
1. Инфологической моделью называют:
а) описание логической структуры БД с точки зрения конкретного пользователя;
б) часть реального мира, представляющую интерес для данного проектирования;
в) описание предметной области, выполненное без ориентации на используемые в дальнейшем программные и технические средства.
2. Логические связи между элементами данных безотносительно к их содержанию и среде хранения отображаются:
а) в физической модели;
б) в даталогической модели;
в) в инфологической модели.
3. Схемой хранения называют:
а) описание логической структуры БД;
б) описание физической структуры БД;
в) описание логической структуры БД с точки зрения конкретного пользователя.
4. В список компонент ИЛМ входит:
а) информационная компонента;
б) описание объектов и связей между ними;
в) описание информационных потребностей пользователей.
5. В список компонент БнД входит:
а) СУБД;
б) технические средства;
в) ограничения целостности.
6. Класс принадлежности указывается:
а) для характеристики связи между разными объектами;
б) при необходимости фиксации каких-либо характеристик, относящихся к классу объектов в целом;
в) если объект участвует во многих связях.
7. Родо-видовые связи между объектами предметной области отражаются:
а) в составном объекте;
б) в агрегированном объекте;
в) в обобщенном объекте.
8. Обозначение для класса объектов в явном виде вводится:
а) если естественный идентификатор объекта не обладает свойством уникальности;
б) для характеристики связи между разными объектами;
в) при необходимости фиксации каких-либо характеристик, относящихся к классу объектов в целом.
Задания для самостоятельного выполнения
Дана инфологическая конструкция (варианты а - д):
а) б)
в) г)
д)
Построить соответствующую ей реляционную схему.
ЯЗЫКИ ЗАПРОСОВ
Реляционная алгебра
В основе реляционной модели лежит математическое понятие теоретико-множественного отношения. Теоретико-множественное отношение представляет собой подмножество декартова произведения доменов. Доменом называется набор значений элементов данных одного типа, отвечающий поставленным условиям (например, домен ФИО определяется на базовом типе строк символов, но в число его значений могут входить только те строки, которые могут изображать имя).
Декартовымпроизведениемk доменов (D1, D2,…, Dk), (обозначается D1×D2×…×Dk), называется множество всех кортежей вида (V1, V2, …, Vk) длины k, таких, что V1∈D 1, V2∈D2, …, Vk∈Dk.
Например, пусть D1 = {1, 2, 3}; D2 = {a, b, c, d}
Тогда
1,a | 1,b | 1,c | 1,d | в матричном виде | ||
D1×D2 = | 2,a | 2,b | 2,c | 2,d | ||
3,a | 3,b | 3,c | 3,d |
или:
D1×D2 = {(1,a), (1,b), (1,c), (1,d), (2,a), (2,b), (2,c), (2,d), (3,a), (3,b), (3,c), (3,d)}
Отношением называют некоторое подмножество декартова произведения доменов (предполагаются только конечные отношения).
Примеры отношений:
1) {(1,a), (1,c), (1,b)} - подмножество декартова произведения доменов D1×D2;
2) Ø.
В реляционных СУБД для выполнения операций над отношениями используют две группы языков, имеющие в качестве своей математической основы теоретические языки запросов, предложенные Э. Коддом:
· реляционную алгебру;
· реляционное исчисление.
Реляционнойалгеброй называют систему операций манипулирования отношениями, каждый оператор которой в качестве операнда имеет одно или более отношений и образует отношение по заранее обусловленному правилу.
Вариант реляционной алгебры, предложенный Коддом, включает в себя следующие операции: объединение, разность, пересечение, декартово (прямое) произведение, выборка (селекция), проекция, деление и соединение. Упрощенное графическое представление перечисленных операций показано на рис. 4.1.
1) Объединением двух отношений (r ∪ s) является отношение, содержащее все кортежи, которые принадлежат либо r, либо s, либо им обоим.
Данная операция применяется к отношениям с одной и той же схемой.
Схемойотношенияr называется конечное множество имен атрибутов {A1, A2, …, An}.
Примеры:
a) Пусть k и m – отношения со схемой ABC:
k | (A | B | C) | m | (A | B | C) | |
a1 | b1 | c1 | a1 | b2 | c1 | |||
a1 | b2 | c1 | a2 | b2 | c1 | |||
a2 | b1 | c2 | a2 | b2 | c2 |
Тогда объединение данных отношений:
k ∪ m = | (A | B | C) |
a1 | b1 | c1 | |
a1 | b2 | c1 | |
a2 | b1 | c2 | |
a2 | b2 | c1 | |
a2 | b2 | c2 |
б) Пусть отношение R1 – множество поставщиков из Москвы, а R2 – множество поставщиков, которые поставляют деталь D1:
R1 R2
№_П | ФИО_П | Город_П | №_П | ФИО_П | Город_П | |
Р1 | Иванов И. И. | Москва | Р1 | Иванов И. И. | Москва | |
Р4 | Петров П. П. | Москва | Р2 | Сидоров С. С. | Киев |
Тогда объединение данных отношений даст поставщиков из Москвы, либо поставщиков, которые поставляют деталь D1, либо тех и других:
R1 ∪ R2
№_П | ФИО_П | Город_П |
Р1 | Иванов И. И. | Москва |
Р4 | Петров П. П. | Москва |
Р2 | Сидоров С.С. | Киев |
2) Разностью двух отношений (обозначается r - s) является отношение, содержащее все кортежи, которые принадлежат r, но не принадлежат s.
Операция применяется к отношениям с одной и той же схемой.
Примеры:
а)
k – m = | (A | B | C) |
a1 | b1 | c1 | |
a2 | b1 | c2 |
б) Разность отношений R1 и R2 даст поставщиков из Москвы, не поставляющих деталь D1:
R1 – R2
№_П | ФИО_П | Город_П |
Р4 | Петров П. П. | Москва |
3) Пересечением двух отношений (обозначается r ∩ s) является отношение, содержащее все кортежи, которые принадлежат одновременно r и s.
Операция применяется к отношениям с одной и той же схемой.
Примеры.
а)
k ∩ m = | (A | B | C) |
a1 | b2 | c1 |
б) Пересечение отношений R1 и R2 даст всех поставщиков из Москвы, поставляющих деталь D1:
R1 ∩ R2
№_П | ФИО_П | Город_П |
Р1 | Иванов И. И. | Москва |
Рис. 4.1. Основные операции реляционной алгебры
4) Декартовымпроизведением отношения r степени k1 и отношения s степени k2 (r × s) является отношение степени (k1 + k2), содержащее такие кортежи, первые k1 элементов которых принадлежат r, а последние k2 элементов принадлежат s.
Примеры:
а) Даны отношения k и m
k | (A | B) | m | (C | D) | |
a1 | b1 | c1 | d1 | |||
a2 | b1 | c2 | d1 | |||
c2 | d2 |
Тогда
k × s = | (A | B | C | D) |
a1 | b1 | c1 | d1 | |
a1 | b1 | c2 | d1 | |
a1 | b1 | c2 | d2 | |
a2 | b1 | c1 | d1 | |
a2 | b1 | c2 | d1 | |
a2 | b1 | c2 | d2 |
б) Пусть отношение R1 – множество поставщиков, а S1 – множество деталей:
R1 S1
№_П | ФИО_П | Город_П | №_Д | Название | Вес | Город_Д | |
Р1 | Иванов И. И. | Москва | Д1 | гайка | Москва | ||
Р2 | Сидоров С. С. | Киев | Д3 | винт | Ростов | ||
Д6 | шпилька | Москва |
Тогда декартово произведение данных отношений:
R1 × S1
№_П | ФИО_П | Город_П | №_Д | Название | Вес | Город_Д |
Р1 | Иванов И.И. | Москва | Д1 | гайка | Москва | |
Р1 | Иванов И.И. | Москва | Д3 | винт | Ростов | |
Р1 | Иванов И.И. | Москва | Д6 | шпилька | Москва | |
Р2 | Сидоров С.С. | Киев | Д1 | гайка | Москва | |
Р2 | Сидоров С.С. | Киев | Д3 | винт | Ростов | |
Р2 | Сидоров С.С. | Киев | Д6 | шпилька | Москва |
5) Выборка (применяется к одному отношению). Результатом ее применения к отношению r является другое отношение, представляющее собой подмножество кортежей отношения r с определенным значением в выделенном атрибуте.
Пусть r отношение со схемой R, A – атрибут в R и a – элемент из домена А. Тогда – операция выборки в r кортежей, в которых значение A равно a. В условии выборки можно использовать константы, логические операции и операции сравнения.
Примеры:
а)
№_Д | Название | Вес | Город_Д |
Д1 | гайка | Москва |
б) Дано отношение R1
R1
№_П | №_Д | Количество |
Р1 | Д1 | |
Р1 | Д3 | |
Р2 | Д1 | |
Р2 | Д4 | |
Р3 | Д1 |
Тогда
№_П | №_Д | Количество |
Р3 | Д1 |
6) Проекция(применяется к одному отношению) - операция выбора подмножества столбцов. Пусть r – отношение со схемой R, Х – подмножество из R. Проекция r на X есть отношение , полученное вычеркиванием столбцов, соответствующих атрибутам в R – X, и исключением из оставшихся столбцов повторяющихся строк.
Примеры.
а) Пусть k - отношение со схемой АВС:
k | (A | B | C) |
a1 | b1 | c1 | |
a1 | b2 | c1 | |
a2 | b1 | c2 |
Тогда запись означает, что из каждого кортежа, принадлежащего k, формируется кортеж длины 2 из третьего и первого его атрибутов в указанном порядке:
= | (C | A) | повторяющиеся кортежи исключаются | |
c1 | a1 | |||
c2 | a2 |
Записи эквивалентна запись .
б) Пусть S1 - отношение, содержащее информацию о деталях:
S1
№_Д | Название | Вес | Город_Д |
Д1 | гайка | Москва | |
Д3 | винт | Ростов | |
Д6 | шпилька | Москва |
Тогда
№_Д | Город_Д |
Д1 | Москва |
Д3 | Ростов |
Д6 | Москва |
7) Деление. Пусть r – отношение со схемой R, s – отношение со схемой S и S ⊆ R. Положим = R – S. Частное от деления r на s (r ÷ s) – это максимальное подмножество множества , такое, что декартово произведение и s содержится в r.
Примеры:
а) Даны отношения k и m
k | (A | B | C | D) | m | (C | D) | |
a1 | b1 | c1 | d2 | c2 | d1 | |||
a2 | b2 | c2 | d1 | c4 | d1 | |||
a3 | b2 | c3 | d3 | |||||
a2 | b2 | c4 | d1 | |||||
a1 | b3 | c5 | d4 | |||||
a4 | b1 | c6 | d5 |
Тогда
k ÷ m = | (A | B) |
a2 | b2 |
б) Имеется отношение
Право
Пилот | Тип_самолета |
Иванов | |
Иванов | |
Иванов | |
Петров | |
Петров | |
Сидоров | |
Сидоров | |
Сидоров | |
Сидоров | |
Макаров |
Требуется найти тех пилотов, которые имеют право управлять всеми типами самолета из некоторого множества q.
q
Тип_самолета |
Тогда
Право ÷ q
Пилот |
Иванов |
Сидоров |
8) Соединение.
Естественноесоединение. Пусть r – отношение со схемой R, s – отношение со схемой S и R ∪ S = T. Естественным соединением отношений r и s (r ⊲⊳ s) является отношение q со схемой T, содержащее кортежи, каждый являющийся комбинацией кортежа из r и кортежа из s с равными (R ∩ S) – значениями.
Примечание. Если R ∩ S = ∅, то r ⊲⊳ s даст декартово произведение r и s.
Примеры:
а) Даны отношения k и m
k | (A | B) | m | (B | C) | |
a1 | b1 | b1 | c2 | |||
а1 | b2 | b2 | c1 | |||
a2 | b1 |
Тогда
k ⊲⊳m = | (A | B | C) |
a1 | b1 | c2 | |
a2 | b1 | c2 | |
a1 | b2 | c1 |
б) Даны отношения R1 и S1
R1 S1
№_П | ФИО_П | Город_П | №_Д | Название | Вес | Город_Д | |
Р1 | Иванов И. И. | Москва | Д1 | гайка | Москва | ||
Р2 | Сидоров С. С. | Киев | Д3 | винт | Ростов | ||
Д6 | шпилька | Москва |
Естественное соединение R1 и S1 по атрибуту Город (в отношении R1 – это Город_П, а в отношении S1 – Город_Д):
R1 ⊲⊳ S1
№_П | ФИО_П | Город | №_Д | Название | Вес |
Р1 | Иванов И.И. | Москва | Д1 | гайка | |
Р1 | Иванов И.И. | Москва | Д6 | шпилька |
Тета – соединение. При естественном соединении отношения могут комбинироваться только по одноименным столбцам и должны комбинироваться по всем таким столбцам.
Примечание. В предыдущем примере вначале была применена операция переименования атрибутов Город_Д и Город_П на Город.
Отношения могут соединяться также по столбцам с различными именами атрибутов, но равными доменами.
Тета – соединение отношений r и s по столбцам i и j представляет собой множество кортежей в декартовом произведении r и s, таких, что i-й элемент r находится в связи Θ с j–элементом s.
Если Θ является оператором «=», то эта операция называется эквисоединением.
Примеры
а) Пусть R1 – отношение, содержащее информацию о рейсах из города «а» в город «в», S1 – отношение, содержащее информацию о рейсах из города «в» в город «с»:
R1
Номер | Время_вылета | Время_прибытия |
9.40 | 11.45 | |
12.50 | 14.47 | |
16.05 | 18.15 | |
20.30 | 22.25 | |
21.15 | 23.11 |
S1
Номер | Время_вылета | Время_прибытия |
8.30 | 9.52 | |
12.25 | 13.43 | |
16.20 | 17.40 | |
19.10 | 20.35 |
Требуется узнать, какие рейсы из «a» в «в» сочетаются с рейсами из «в» в «c».
Для этого необходимо соединить те кортежи, для которых Время_прибытия в отношении R1 меньше Время_вылета в отношении S1.
Тета – соединение отношений R1 и S1:
Номер | Время_вылета | Время_прибытия | Номер | Время_вылета | Время_прибытия |
9.40 | 11.45 | 12.25 | 13.43 | ||
9.40 | 11.45 | 16.20 | 17.40 | ||
9.40 | 11.45 | 19.10 | 20.35 | ||
12.50 | 14.47 | 16.20 | 17.40 | ||
12.50 | 14.47 | 19.10 | 20.35 | ||
16.05 | 18.15 | 19.10 | 20.35 |
б) Даны отношения Маршрут и Адрес
Маршрут
Номер | Пункт_отправления | Пункт_назначения |
Чикаго | Нью-Йорк | |
Нью-Йорк | Лос-Анджелес | |
Атланта | Бостон | |
Нью-Йорк | Бостон | |
Бостон | Нью-Йорк |
Адрес
Пилот | Аэропорт | отношение указывает для каждого пилота адрес базового аэропорта, к которому он приписан | |
Терхьюн | Нью-Йорк | ||
Темпл | Атланта | ||
Тейлор | Атланта | ||
Тарбелл | Бостон | ||
Тодд | Лос-Анджелес | ||
Трумен | Чикаго |
Требуется назначить пилотов на те рейсы, пункт отправления которых совпадает с аэропортом, к которому приписан пилот.
Эквисоединение отношений Маршрут и Адрес по столбцам Пункт_отправления и Аэропорт:
Номер | Пункт_отправления | Пункт_назначения | Пилот | Аэропорт |
Чикаго | Нью-Йорк | Трумен | Чикаго | |
Нью-Йорк | Лос-Анджелес | Терхьюн | Нью-Йорк | |
Атланта | Бостон | Темпл | Атланта | |
Атланта | Бостон | Тейлор | Атланта | |
Нью-Йорк | Бостон | Терхьюн | Нью-Йорк | |
Бостон | Нью-Йорк | Тарбелл | Бостон |
Основное отличие эквисоединения от естественного в том, что при последнем соединяемые столбцы не повторяются.
По справедливому замечанию Дейта, реляционная алгебра Кодда обладает несколькими недостатками. Во-первых, восемь перечисленных операций по охвату своих функций, с одной стороны, избыточны, так как минимально необходимый набор составляют пять операций: объединение, вычитание, произведение, проекция и выборка. Три другие операции (пересечение, соединение и деление) можно определить через пять минимально необходимых. Во-вторых, этих восьми операций недостаточно для построения реальной СУБД на принципах реляционной алгебры. Требуются расширения, включающие операции: переименования атрибутов, образования новых вычисляемых атрибутов, вычисления итоговых функций, построения сложных алгебраических выражений, присвоения, сравнения и т. д.