Реляционная модель. Лекция 2

1. БД и их безопасность

ЛЕКЦИЯ 2. РЕЛЯЦИОННАЯ МОДЕЛЬ

2. План

Реляционная модель данных
Отношения
Общее понятие отношений
Создание и использование отношений
Инфологическое проектирование

3. Реляционная модель

Реляционная
модель (relational model) —
математическая теория, описывающая структуры
данных, логику контроля целостности данных и
правила управления данными.
Упрощённо: модель для описания реляционных
баз данных.

4. Реляционная модель/Достоинства

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

5. Реляционная модель/Достоинства

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

6. Общие сведения об отношениях

Отношение — множество кортежей (записей,
строк таблицы), обладающих одинаковым
набором атрибутов (свойств, полей, столбцов
таблицы).
!!!
Важно разделять понятия «отношение»
(relation) и «связь» (relationship), несмотря на то,
что очень часто в русскоязычной (переводной)
литературе их путают. Строго говоря, термин
(например) «отношение многие ко многим»
неверен, должно быть «связь многие ко многим».

7. Общие сведения об отношениях

Тип данных (data type) — набор объектов
данных определённой структуры и набор
допустимых операций, в любой из которых такие
объекты могут выступать операндами.
Упрощённо, на примере: числа, строки, даты и
т.д.
Все значения, которыми оперирует база данных,
являются типизированными (т.е. в любой
момент времени известен их тип).

8. Общие сведения об отношениях

На
типы
данных
могут
налагаться
дополнительные ограничения, что приводит к
появлению понятия «домен данных».
Домен данных (attribute domain) — набор
всех возможных значений атрибута отношения.
Упрощённо, на примере: номер телефона,
фамилия, название улицы и т.д.
«Код товара» - «ABC1234yz»
Строка – «понедельник»
Триггеры или проверки

9. Общие сведения об отношениях

10. Общие сведения об отношениях

Атрибут
(attribute) — именованное свойство
сущности (отношения).
Упрощённо: столбец (колонка) таблицы.
Кортеж
(tuple)
—
часть
отношения,
представляющая
собой
уникальную
взаимосвязанную комбинацию значений, каждое из
которых соответствует своему атрибуту.
Упрощённо: строка (запись) таблицы.
На бытовом уровне под атрибутом можно понимать
«столбец таблицы», а под кортежем — «строку
таблицы».

11. Общие сведения об отношениях

Ключевой атрибут (key attribute, prime attribute) —
атрибут отношения, входящий в состав как минимум одного
потенциального ключа этого отношения.
Упрощённо: столбец (колонка) таблицы, являющаяся частью
потенциального ключа этой таблицы.
Неключевой атрибут (nonkey attribute) — атрибут
отношения, не входящий в состав ни одного из потенциальных
ключей этого отношения.
Упрощённо: столбец (колонка) таблицы, не являющаяся
частью ни одного из потенциальных ключей этой таблицы.
Первичный атрибут (primary key attribute) — атрибут
отношения, входящий в состав первичного ключа этого
отношения.
Упрощённо: столбец (колонка) таблицы, являющаяся частью
первичного ключа этой таблицы.

12. Отношения

13. Поясним все термины графически

1) В процессе создания
модели данных
описываются сущности
и их атрибуты:
2) Модель
детализируется до
схемы отношения:

14. Поясним все термины графически

3) На основе
имеющейся схемы
отношения
генерируется SQL-код
для создания
переменной отношения
(т.е. реальной таблицы
в реальной базе данных
под управлением
СУБД):

15.

4) В СУБД появляется
реальная таблица:
5) Теперь на основе
таблицы можно
построить представление,
т.е. производную
переменную отношения:

16.

6) Пример значения
отношения выглядит так:
7) Пример значения
представления
(производной
переменной отношения)
выглядит так:

17. ПРИМЕР ПРИЛОЖЕНИЯ БАЗЫ ДАННЫХ

Требования, собранные для КОМПАНИИ
Сотрудники, отделы и проекты
Компания организована в отделы
Отдел контролирует несколько проектов
Сотрудник: требуется имя каждого сотрудника,
номер социального страхования, адрес, зарплата,
пол и дата рождения
Отслеживается деятельность каждого сотрудника

18. Понятия «сущность-связь»

Сущность (Entity) или объект – то,
о чем будет накапливаться
информация в информационной
системе (нечто такое, за чем
пользователь хотел бы наблюдать).
Атрибут – поименованное свойство
(характеристика) сущности.
Соответствующие взаимоотношения
сущностей выражаются связями
(Relationships).

19. класс сущностей

Совокупность
сущностей,
характеризующихся в информационной
системе одним и тем же перечнем свойств,
называется
классом
сущностей
(набором объектов).
Так,
например,
совокупность
всех
сущностей СТУДЕНТ составляет класс
сущностей СТУДЕНТ, совокупность всех
сущностей ФАКУЛЬТЕТ составляет класс
сущностей ФАКУЛЬТЕТ. Класс сущностей
описывается перечнем свойств сущностей,
составляющих этот класс.
Важнейшим свойством сущности
является однозначная
идентификация ее экземпляров по
одному или группе атрибутов
(уникальному идентификатору).

20. Категории атрибутов

Простые (атомарные) и составные атрибуты
Однозначные и многозначные атрибуты
Хранимые и производные атрибуты
Ключевые или уникальные атрибуты
Значения атрибутов ограничены тем, чтобы быть разными
для отдельных сущностей в наборе сущностей

21. Категории атрибутов

Простые атрибуты
Простые атрибуты, также известные как
атомарные атрибуты, неделимы. Они содержат
единичные атомарные значения, которые
невозможно разделить дальше.
Пример: В сущности “Employee” атрибуты
Employee ID и Salary просты. Каждый из них
содержит единственное неделимое значение.
Например, идентификатор сотрудника может
быть "E12345", а зарплата может составлять "50
000".
Простыми атрибутами легко управлять, потому
что они содержат только одну часть информации.
Они обычно используются для базовых типов
данных, таких как целые числа, строки и даты.
Составные атрибуты
Составные атрибуты включают в себя множества
простых атрибутов. Их можно разделить на
более мелкие части, каждая из которых
представляет более подробный аспект общего
атрибута.
Пример: Атрибут адреса для сотрудника можно
рассматривать как составной. Его можно
разбить на более мелкие части, такие как улица,
город и почтовый индекс.

22. Категории атрибутов

Однозначные атрибуты
Однозначные атрибуты содержат только
одно значение для каждого экземпляра
сущности.
Пример: Атрибут Date Of Birth (DOB) для
сотрудника
является
однозначным,
поскольку у каждого сотрудника есть
только одна дата рождения. Например,
датой рождения сотрудника может быть
"1990-05-15".
Многозначные атрибуты
Многозначные атрибуты могут содержать
несколько значений для одного
экземпляра объекта.
Пример: Если у сотрудника может быть
несколько телефонных номеров, атрибут
Phone Numbers будет многозначным.
Например, у сотрудника могут быть
телефонные номера, такие как "555-1234"
и "555-5678".

23. Категории атрибутов

Хранимые атрибуты
Хранимые атрибуты - это те, которые
хранятся в базе данных. Они содержат
данные, которые явно вводятся и
сохраняются.
Пример: Такие атрибуты, как имя, дата
рождения и адрес сотрудника, являются
сохраняемыми атрибутами. Эти атрибуты
содержат
данные,
вводимые
непосредственно в базу данных и
поддерживаемые с течением времени.
Производные атрибуты
Производные
атрибуты не хранятся
непосредственно в базе данных, а
вычисляются или являются производными
от других сохраненных атрибутов.
Пример: Возраст сотрудника может быть
производным атрибутом, вычисляемым на
основе даты рождения. Если дата
рождения "1990-05-15", возраст может
быть рассчитан на основе текущей даты.

24. НАЧАЛЬНАЯ ER-ДИАГРАММА ДЛЯ КОМПАНИИ

Четыре типа сущностей
Большинство атрибутов
простые, однозначные и
хранимыеWorks_on и
Locations являются
многозначными
Имя сотрудника является
составным
У сотрудника один ключ,
у отдела и проекта два
ключа, у зависимого нет
ни одного

25. Слабые сущности

Слабые сущности — это те
сущности, которые не могут
существовать без другой сущности.
Представьте, что у Вас в
существует таблица «Родитель» и
«Ребенок», соответственно такиеже сущности в диаграмме. Может
ли одно существовать без другого?
Я думаю — нет. Как в
биологическом, так и в целом
логическом.
Слабая сущность: яблока без
яблони быть не может.
В этом примере сущность
«Ребенок» — слабая сущность.

26. слабая сущность

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

27.

Иногда
для
более
удобной классификации
используются
так
называемые
подтипы
сущностей.
Их
обозначают
с
помощью треугольника,
направленного
от
подтипа к надтипу. Этот
треугольник
может
сопровождаться
надписью «есть» или «is
a» (т.е., «является»).

28. Общие сведения о связях

Связь (relationship) — ассоциация, объединяющая
несколько сущностей.
Упрощённо: указание того факта, что отношения
находятся в некоей взаимосвязи друг с другом.
Мощность связи (relationship cardinality) —
свойство
связи,
определяющее
допустимые
мощности взаимосвязанных подмножеств кортежей
отношений, объединённых данной связью.
Упрощённо: указание количества взаимосвязанных
строк в таблицах, объединённых связью (например:
«один ко многим», «многие ко многим», «один к
трём» и т.д.)

29. Связь один ко многим

Связь один ко многим (one to many correspondence) —
ассоциация, объединяющая два отношения таким образом, что
одному кортежу родительского отношения (или даже нулю
таких кортежей) может соответствовать произвольное
количество кортежей дочернего отношения.
Связь многие к одному (many to one correspondence) —
ассоциация, объединяющая два отношения таким образом,
произвольное количество кортежей дочернего отношения
может соответствовать одному кортежу родительского
отношения (или даже нулю таких кортежей).
Упрощённо:
одной
записи
в
таблице
A
может
соответствовать много записей в таблице B; при этом
может быть ситуация, когда неким записям в таблице B нет
соответствия в таблице A.

30. Связь один ко многим

31. Связь один ко многим

32. Связь многие ко многим

Связь многие ко многим (many to many
correspondence) — ассоциация, объединяющая
два отношения таким образом, что одному
кортежу любого из объединённых отношений
может соответствовать произвольное количество
кортежей второго отношения.
Упрощённо: одной записи в таблице A может
соответствовать много записей в таблице B, и
в то же время одной записи в таблице B может
соответствовать много записей в таблице A.

33. Связь многие ко многим

Рассмотрим следующий классический пример: студенты и предметы.
Каждый студент изучает много предметов, и каждый предмет изучается
многими студентами.
Отразить такую ситуацию в базе данных с помощью связи «один ко
многим» невозможно, т.к. здесь нет явной родительской и подчинённой
сущностей (и попытка выполнить взаимную миграцию первичных ключей
приведёт к очень серьёзным техническим нарушениям и полной потере
адекватности предметной области)

34. Связь многие ко многим

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

35.

В случае связи «многие ко
многим»
т.н.
«свойство
связи» отражается в таблице
связи.
Например,
по
итогам
изучения предмета студент
должен получить оценку. Но
оценка
объективно
не
является
ни
свойством
студента (у него много
оценок, а не одна), ни
свойством
предмета
(предмету вообще нелогично
ставить оценки) — она
является именно свойством
взаимосвязи
конкретного
студента
и
конкретного
предмета

36. Связь один к одному

Связь
один к одному (one to one
correspondence) — ассоциация, объединяющая
два отношения таким образом, что одному
кортежу родительского отношения может
соответствовать не более одного кортежа
дочернего отношения.
Упрощённо: одной записи в таблице A может
соответствовать не более одной записи в
таблице B.

37. Связь один к одному

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

38. Элементы ER диаграммы

39.

40.

41. Пример

Контора «Рога и копыта» занимается коммерческой деятельностью по
реализации продукции, произведенной из рогов и копыт, и предоставлению
магических услуг.
Сотрудник организации имеет ФИО, табельный номер, должность.
Сотрудники распределены по нескольким отделам. Каждый отдел имеет
номер, название и руководителя. Сотрудник не может руководить более чем
одним отделом.
Организация работает с предприятиями-клиентами. Каждое предприятие
имеет название и адрес. С предприятием может быть заключено несколько
договоров. Договор характеризуется уникальным номером, датой и типом.
Каждый договор курирует некоторый сотрудник.
По мере реализации клиенту товаров и услуг по договору с некоторой
периодичностью выставляются счета. Счет характеризуется уникальным
номером, датой выставления, сроком оплаты и суммой, а также списком
реализованных товаров и услуг с указанием их количества. По неоплаченным
счетам начисляются пени.
Счет может быть оплачен в несколько приемов, каждый платеж
характеризуется номером, датой и суммой. Номер платежа уникален в
пределах его счета. Цены на товары и услуги могут изменяться со временем.

42.

43. Отношения

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

44. Как создать простую ER‑диаграмму

Как создать простую ER-диаграмму
1. Определить сущности
Чтобы собрать все сущности будущего проекта, системные аналитики общаются с заказчиком и будущими пользователями
ПО: сотрудниками или клиентами компании. Например, если нужно разработать ПО для ветеринарной клиники, системный
аналитик проведёт интервью с руководителем клиники, сотрудниками, врачами и клиентами, которые будут записываться на
приём.
На этом этапе обычно создают концептуальную модель и согласовывают её с заказчиком.
2. Определить атрибуты
Системный аналитик детализирует информацию, собранную во время интервью, и описывает характеристики сущностей.
Если данных не хватает, нужно повторно опросить заинтересованных лиц.
3. Определить связи между сущностями
На этом этапе выясняют, какие сущности связаны между собой. Например, пациенты и медицинская карта, филиал клиники
и врачи, которые ведут приём.
4. Определить типы и характеристики связей
Например, пациенты и медицинская карта — это связь «один-к-одному», врач и день приёма — «один-ко-многим».
Затем ищут идентифицирующие связи между сущностями и определяют, какая из сущностей родительская. Допустим, у
клиники есть филиалы — A, B и C. В каждом филиале есть кабинеты под номерами от 1 до 5. Это значит, что нельзя
использовать номер кабинета без уточнения, в каком филиале он находится. Филиал — родительская сущность, а связь между
филиалом и кабинетом — идентифицирующая.
5. Проверить ER-модель
После завершения работы над ER-моделью системный аналитик проверяет, нет ли в ней лишних сущностей, дубликатов
данных и косвенных связей между данными в одной таблице. Такую проверку называют нормализацией данных.

45. Создание и использование отношений

Всегда используйте для именования объектов базы
данных только английский язык. Если вы его не
знаете, пишите «транслитом», но всё равно
используйте только английский алфавит.
Выработать стандарт именования объектов базы
данных. Чтобы избежать бесполезных споров о том,
какой стандарт лучше, подчеркнём лишь главную
мысль: обязательно стоит следовать какому-то (пусть
и созданному лично вами) одному стандарту. И
только ему.
* на даталогическом
уровне стоит сделать следующее

46. Создание и использование отношений

47. Создание и использование отношений

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

48. Создание и использование отношений

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

49. Создание и использование отношений

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

50. Создание и использование отношений

Указать самые очевидные ключи , связи ,
индексы.
Здесь продолжается та же логика, что была
указана в предыдущем пункте: отмечаем лишь
самое важное — то, без чего модель данных явно
начнёт терять адекватность предметной области.
Так, например, у атрибута f_uid включено свойство
PK, т.е. он является первичным ключом
отношения

51. Создание и использование отношений

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

52. отношения на физическом уровне

Конкретизировать типы данных.
Здесь уже недостаточно в общих чертах понимать,
что «вот тут — строка, а вот тут — число». Строки,
числа, датавременные и денежные величины (и
многие другие) выражаются вполне конкретными
(и при том несколькими разными!) типами
данных, поддерживаемыми конкретной СУБД.

53. отношения на физическом уровне

54. отношения на физическом уровне

Указать все ограничения.
И здесь речь идёт не только об уникальности
значений или NOT NULL, здесь нужно создать всё:
первичные и внешние ключи (и доработать все
необходимые
связи),
уникальные
индексы,
проверки, триггеры.

55. отношения на физическом уровне

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

56. Инфологическая модель

Исследование предметной области как раз и
представляет
основную
сложность,
разделяющуюся
на
следующие
локальные
проблемы:
извлечение информации;
глубина исследования;
границы исследования

57. извлечение информации

Семинары и
мозговой штурм
Анкетирование
Наблюдение
Работа с
фокусными
группами
Интервью
Анализ
документов
Информация
ПО
моделирование
процессов и
взаимодействий

58.

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

59.

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

60. . Инструменты и техники проектирования на инфологическом уровне

61.

ER-диаграмма

62.

63.

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

64.

65. Задание

Конспект лекции
СРО
Срок 15.09.2025
https://drive.google.com/drive/folders/1PEQn1t4hk
WhkRkHWMhE0couJ9OKgK0Sg?usp=drive_link