Базы данных. Проектирование баз данных. Нормальные формы. Лекция 4

1. Базы данных

Лекция 4
Проектирование баз данных.
Нормальные формы.
v.4 05.10.2019

2. Этапы проектирования сверху вниз

• Analysis. Построение концептуальной /
логической модели.
• Design. Преобразование логической модели
в физическую – схему таблиц.
• Build. Генерация скрипта и его выполнение
– создание базы данных.
27.06.2023
Горбунов О.Е.
2

3. Причины проектирования

• Описание основных информационных
потребностей.
• Обсуждение предметной области на
ранних стадиях.
• Снижение вероятности ошибок и
недопонимания.
• Основа для документирования системы.
• Основа для построения физической
(реляционной) модели базы данных.
28.09.2019
Горбунов О.Е.
3

4. Концептуальная модель, ERM

Элементы:
Сущности (entities), экземпляры сущностей;
Атрибуты (attributes), уникальные идентификаторы (UID);
Связи (relationships);
Чтение связей в ERD.
Графическое отображение (ERD):
• Barker notation;
• Bachman notation;
• Information engineering.
21.10.2017
Горбунов О.Е.
4

5. Бизнес-правила

• Типы бизнес-правил:
– Структурные;
– Процедурные.
• Не все бизнес-правила могут быть представлены
на ERM. Но все необходимо оформить в виде
документов для дальнейшей реализации.
21.10.2017
Горбунов О.Е.
5

6. UID

Классификация по количеству атрибутов
• простые
• составные
Классификация по соответствию реальным
объектам
• естественные
• искусственные
11.11.2015
Зафиевский А.В.
6

7. Общие требования

• Уникальность названий отдельных сущностей
(отношений) в базе данных;
• Уникальность названий атрибутов у отдельных
сущностей (отношений);
• Значения атрибута у экземпляров сущностей
(кортежей) принадлежат одному домену;
• Целостность сущности: все экземпляры сущностей
(кортежи) различны.
06.10.2017
Зафиевский А.В.
7

8. Первая нормальная форма

Сущность (отношение) находятся в 1НФ, если:
• выполняются общие требования;
• каждый атрибут содержит атомарное значение.
11.11.2015
Зафиевский А.В.
8

9. Функциональные зависимости

• Атрибут C называется функционально зависимым от
атрибутов A и B (обозначается A,B → C), если значения
атрибутов A и B однозначно определяют атрибут C.
• Выявление функциональной зависимости атрибутов
является неформальным действием и определяется в
результате тщательного анализа предметной области,
возможных событий в ней и возможных значений
атрибутов.
07.10.2017
Зафиевский А.В.
9

10. Вторая нормальная форма

Сущность (отношение) находится во 2НФ, если:
• она находится в 1НФ;
• каждый неключевой атрибут функционально
полно зависит от UID (PK).
28.09.2019
Горбунов О.Е.
10

11. Третья нормальная форма

Сущность (отношение) находятся в 3НФ, если:
• она находится в 2НФ;
• каждый неключевой атрибут нетранзитивно
зависит от UID (PK).
28.09.2019
Горбунов О.Е.
11

12. Другие нормальные формы

• Кроме приведенных нормальных форм имеются
и другие нормальные формы (нормальная форма
Бойса-Кодда – 3НФ+, 4НФ, 5НФ и др.), однако их
использование носит в большей степени
теоретический характер.
11.11.2015
Зафиевский А.В.
12

13. Примеры CASE-систем

• CA ERwin Data Modeler;
• MySQL Workbench;
• Enterprise Architect;
• Oracle SQL Developer Data Modeler;
• Microsoft Visio.
27.06.2023
Зафиевский А.В.
13

14. Проектирование снизу вверх

• Создается одна сущность (таблица) со
всеми атрибутами (столбцами);
• Осуществляется процесс нормализации.
05.10.2019
Горбунов О.Е.
14

15. Информационная система факультета

Предположим, что в результате первичного рассмотрения принято
решение, что система будет использовать следующие атрибуты.
StudentNumber – № студ. билета
LastName – фамилия студента
FirstName – имя студента
PatrName – отчество студента
Group – группа, в которой учится
студент
BirthDate – дата рождения студента
City – город проживания студента
Address – адрес в городе проживания
SubjectCode – код дисциплины,
сданной студентом
SubjectName – наименование
дисциплины
SubjectHours – количество часов,
выделенных на дисциплину
TeacherNumber – табельный номер
преподавателя по дисциплине
TeacherName – фамилия и инициалы
преподавателя
Chair – кафедра, на которой работает
преподаватель
TeacherPosition – должность
преподавателя
Grade – оценка по дисциплине
Salary – размер стипендии
05.10.2019
Горбунов О.Е.
15

16. Информационная система факультета

• На основе этой информации формируется универсальная
таблица – таблица, содержащая столбцы, соответствующие
наименованиям атрибутов.
• Зная значения в паре столбцов (StudentNumber, SubjectCode),
можно однозначно определить значения всех остальных
атрибутов, то есть эту пару можно выбрать в качестве
первичного ключа. Учитывая остальные свойства, можно
заключить, что универсальная таблица информационной
системы факультета находится в первой нормальной форме.
• Сокращенно структура приведенной таблицы записывается в
виде
Faculty(StudentNumber, LastName, FirstName, PatrName, Group,
BirthDate, City, Address, Salary, SubjectCode, SubjectName,
SubjectHours, TeacherNumber, TeacherName,
Chair, TeacherPosition, Grade).
07.10.2017
Зафиевский А.В.
16

17. Недостатки универсальной таблицы

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

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

• Аномалии вставки:
– при вводе информации об экзамене мы должны повторить
информацию о студенте;
– при вводе информации о студенте мы должны ввести
информацию о каком-нибудь экзамене, поскольку код
дисциплины является частью первичного ключа, который не
может принимать значение null.
• Аномалии удаления:
– при удалении ошибочной информации о единственном экзамене
студента будет удалена и вся информация о студенте.
• Аномалии замены:
– при замене какого-либо значения в столбце, не относящемся к
сданным экзаменам, надо будет заменить все значения в этом
столбце, относящиеся к тому же студенту; если это будет сделано
не для всех таких строк, это приведет базу данных в
противоречивое состояние.
11.11.2015
Зафиевский А.В.
18

19. Нормализация 2НФ

• Атрибуты LastName, FirstName, PatrName, Group, BirthDate, City,
Address, Salary функционально зависят только от атрибута
StudentNumber, в то время как значение атрибута SubjectCode
не оказывает на них никакого влияния.
• Для преобразования таблицы в приведенном примере к 2НФ
она должна быть разделена на две связанных таблицы: в
первую из них (родительскую) входит атрибут StudentNumber и
все зависящие от него неключевые атрибуты, а во вторую
(дочернюю) – весь первичный ключ таблицы Faculty и
оставшиеся неключевые атрибуты.
Student(StudentNumber, LastName, FirstName, PatrName,
Group, BirthDate, City, Address, Salary),
Exam(StudentNumber, SubjectCode, SubjectName,
SubjectHours, TeacherNumber, TeacherName,
Chair, TeacherPosition, Grade)
07.10.2017
Зафиевский А.В.
19

20. Нормализация 2НФ

• Таблица Student находится в 2НФ по той причине, что первичный ключ
в ней состоит только из одного атрибута. В таблице Exam атрибуты
SubjectName и SubjectHours зависят только от атрибута SubjectCode, и
поэтому она также должна быть разделена на две:
Subject(SubjectCode, SubjectName, SubjectHours),
Exam2(StudentNumber, SubjectCode, TeacherNumber, TeacherName,
Chair, TeacherPosition, Grade).
• Таблица Exam2 является при этом дочерней по отношению к обеим
таблицам Student и Subject.
• Полученная база данных из 3 таблиц уже находится в 2НФ, поскольку
для определения значений неключевых атрибутов таблицы Exam2
необходимо знать оба ключевых атрибута, поскольку у разных
студентов одну и ту же дисциплину могут вести разные
преподаватели, следовательно, частичные функциональные
зависимости в третьей таблице отсутствуют.
07.10.2017
Зафиевский А.В.
20

21. Нормализация 3НФ

• Тем не менее, преобразование базы данных к 2НФ не
устраняет всех аномалий обновления. Это связано с тем, что
неключевые атрибуты TeacherName, Chair, TeacherPosition
функционально зависят только от неключевого же атрибута
TeacherNumber. Используется тот же прием выделения
новой таблицы.
• В итоге база данных информационной системы факультета,
приведенная к 3НФ, содержит четыре таблицы:
Subject(SubjectCode, SubjectName, SubjectHours);
Session(StudentNumber, SubjectCode, TeacherNumber, Grade);
Student(StudentNumber, LastName, FirstName, PatrName, Group,
BirthDate, City, Address, Salary);
Teacher(TeacherNumber, TeacherName, Chair, TeacherPosition).
05.10.2019
Горбунов О.Е.
21

22. Идентифицирующие связи

• Таблица Session является дочерней по отношению
к трем другим таблицам, но при этом:
– столбцы связи с таблицами Student и Subject
(внешние ключи) входят в состав первичного
ключа таблицы Session;
– столбец связи с таблицей Teacher является
неключевым.
• В этом случае принято говорить, что связи
таблицы Session с таблицами Student и Subject
являются идентифицирующими, а связь с
таблицей Teacher – неидентифицирующей.
07.10.2017
Зафиевский А.В.
22

23. Построение физических моделей (структуры базы данных)

• Построение таблиц.
– именование;
– атрибуты;
– первичные ключи;
• Построение связей.
• Построение ограничений.
28.09.2019
Горбунов О.Е.
23