Введение в базы данных

1. Введение в базы данных

Карпук Анатолий Алексеевич,
профессор кафедры ПОСТ
e-mail: А_К[email protected]

2. Лекция 6. ПРЕОБРАЗОВАНИЕ ИНФОРМАЦИОНО-ЛОГИЧЕСКОЙ МОДЕЛИ ПРЕДМЕТНОЙ ОБЛАСТИ В КОНЦЕПТУАЛЬНУЮ МОДЕЛЬ БАЗЫ ДАННЫХ

Лекция 6.
ПРЕОБРАЗОВАНИЕ ИНФОРМАЦИОНОЛОГИЧЕСКОЙ МОДЕЛИ ПРЕДМЕТНОЙ
ОБЛАСТИ В КОНЦЕПТУАЛЬНУЮ МОДЕЛЬ
БАЗЫ ДАННЫХ
Вопросы:
1. Преобразование ER-диаграммы в логическую структуру
базы данных
2. Описание таблиц базы данных
3. Описание ограничений целостности
4. Аномалии модификации данных
5. Нормализация схемы таблицы
6. Первая нормальная форма
7. Вторая нормальная форма
8. Третья нормальная форма
9. Четвертая нормальная форма

3. 1. Преобразование ER-диаграммы в логическую структуру базы данных

Правила преобразования:
1. Каждый тип сущности преобразуется в таблицу БД. В таблицу
вносятся все атрибуты, относящиеся к данному типу сущности.
2. Бинарная связь 1:n (между сущностями разных типов)
реализуется с помощью внешнего ключа между двумя
таблицами:
ГРУППЫ
1
GROUPS
(Группы)
учатся
IDG
N
СТУДЕНТЫ
STUDENTS
(Студенты)
3

4. 1. Преобразование ER-диаграммы в логическую структуру базы данных

3. Правила преобразования:
Бинарная связь типа n:m реализуется с помощью промежуточной
таблицы
4

5. 1. Преобразование ER-диаграммы в логическую структуру базы данных

4. Правила преобразования: Связь 1:1 реализуется в рамках
одной таблицы. Исключение из этого правила составляют
ситуации, когда связанные сущности существуют независимо
друг от друга
5

6. 1. Преобразование ER-диаграммы в логическую структуру базы данных

5. Правила преобразования: Унарная связь 1:n (между
сущностями одного типа) реализуется с помощью внешнего
ключа, определённого в той же таблице, что и первичный ключ
6

7. 1. Преобразование ER-диаграммы в логическую структуру базы данных

6. Правила преобразования: Унарная связь n:m реализуется с
помощью промежуточной таблицы
7

8.

1. Преобразование ER-диаграммы в логическую
структуру базы данных
1
ОТДЕЛЫ
N
выполнять
ПРОЕКТЫ
1
K
работать
руководить
включать
1
M
M
СОТРУДНИКИ
Схема
реляционной
базы данных
участвовать
N
1
N
ЭТАПЫ

9. 2. Описание структуры таблиц

Определение первичных ключей (РК):
При наличии потенциальных ключей РК выбирается из них. Обычно,
берется тот ключ, по которому чаще всего происходит обращение к
данным. Если такого нет, то выбирается ключ, занимающий меньше
памяти.
ИНН
ГНЗ
Номер телефона
Если потенциальных ключей нет, назначается суррогатный РК (он не
несет смысловой нагрузки). Некоторые СУБД позволяют определять
значения такого ключа как AUTOINCREMENT: его значения
генерируются автоматически, начиная с 1, и увеличиваются на 1 при
добавлении новой записи. (Для Postgres это тип SERIAL).
Составной РК назначается в том случае, если необходимо
реализовать ограничение целостности "уникальность комбинации
полей".
PRIMARY KEY (номер_класса, параллель)
9

10. 2. Описание структуры таблиц

Определение типов данных атрибутов. Общие рекомендации:
Для коротких символьных значений и символьных строк фиксированной
длины следует выбирать тип CHAR.
Для символьных строк переменной длины нужно выбирать тип VARCHAR с
указанием максимально возможной длины хранимого значения.
Для числовых атрибутов, имеющих ведущие нули, следует выбирать тип
CHAR, а не числовой тип, иначе ведущие нули будут потеряны.
Для хранения дат нужно выбирать тип DATE или его варианты (DATETIME,
например).
Для числовых атрибутов, не участвующих в сложных расчётах, нужно
использовать основной числовой тип реляционных СУБД – тип NUMBER,
указывая реально необходимое количество разрядов.
Для числовых атрибутов, которые участвуют в сложных расчётах, следует
использовать такие числовые типы, которые хранят данные в машинном
(двоичном) представлении.
Для хранения больших объектов (графических, звуковых и т.п.) следует
выбирать специальные типы данных, перечень которых зависит от СУБД.
Для семантически одинаковых полей разных таблиц нужно выбирать
одинаковые типы данных. Во многих СУБД для упрощения типизации данных
можно создать специальные типы данных (CREATE TYPE) и использовать их
10
в качестве типов полей таблиц.

11. 3. Описание ограничений целостности

Определение и реализация ограничений целостности:
Если какое-либо ограничение целостности может быть
включено в структуру БД (на языке DCL), то его надо
реализовать именно так!
Рассмотрим различные типы ограничений целостности в языке SQL:
Уникальность значения первичного ключа (PRIMARY KEY).
Уникальность ключевого поля или комбинации значений ключевых
полей (UNIQUE).
Обязательность/необязательность значения (NOT NULL/NULL).
Задание условия на значения атрибутов (CHECK).
Определение домена атрибута на основе значений другого
атрибута: множество значений некоторого атрибута отношения
является подмножеством значений другого атрибута этого или
другого отношения (внешний ключ, FOREIGN KEY).
11

12. 3. Описание ограничений целостности

Определение и реализация ограничений целостности (ОЦ)
Если какое-либо ОЦ нельзя реализовать средствами DCL, то возможны
следующие способы его реализации:
С помощью процедурных объектов БД (trigger).
Программно (т.е. через приложение).
Вручную.
Поле со списком возможных значений:
1) CHECK(<поле> IN (<список значений>)).
2) Справочная таблица.
12

13. 4. Аномалии модификации данных

При неправильно спроектированной схеме реляционной БД могут
возникнуть аномалии выполнения операций модификации данных.
Рассмотрим эти аномалии на примере следующей таблицы:
ПОСТАВКИ (Номер поставки, Название товара, Цена товара, Количество,
Дата поставки, Название поставщика, Адрес поставщика)
Аномалия обновления: изменился адрес
поставщика. Если от него было несколько
поставок, то придется менять несколько записей:
UPDATE Поставки SET адрес = ‘Зеленая д.3/4‘
where название_поставщика = ‘Аванта +‘;
13

14. 4. Аномалии модификации данных

Аномалия удаления: при удалении в архив
записей обо всех поставках определённого
поставщика все данные об этом поставщике
(название, адрес) будут утеряны.
Аномалия добавления: нельзя добавить
сведения о поставщике, пока от него нет ни
одной поставки.
Для решения проблемы аномалии модификации данных при
проектировании РБД Э. Кодд создал механизм нормализации
отношений.
14

15. 5. Нормализация схемы таблицы

Нормализация схемы таблицы выполняется путём декомпозиции схемы.
Декомпозицией схемы таблицы R называется замена её совокупностью
схем таблиц Аi таких, что
R Ai
i
и не требуется, чтобы таблицы Аi были непересекающимися. Условия:
1) Декомпозиция таблицы не должна приводить к потере зависимостей
между атрибутами сущностей.
2) Для декомпозиции должна существовать операция реляционной алгебры,
применение которой позволит восстановить исходную таблицу.
Покажем нормализацию на примере таблицы КЛИЕНТЫ :
Id
– идентификатор (первичный ключ),
Name – фамилия, имя, отчество клиента,
Birth
– дата рождения,
Sex
– пол,
Address – адрес(а),
Phone – телефон(ы),
Email
– адрес электронной почты,
Status – статус клиента,
Bonus
– скидка.
15

16. 5. Пример для демонстрации нормализации

В таблице приведен пример содержимого исходной таблицы КЛИЕНТЫ:
16

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

Введём понятие простого и сложного атрибута.
Простой атрибут – это атрибут, значения которого атомарны (т.е.
неделимы).
Название
Количество
Дата
Фамилия
Сложный атрибут – это составной и/или многозначный атрибут. Составной
атрибут имеет значение, которое является конкатенацией нескольких
значений одного или разных доменов. Многозначный атрибут может иметь
несколько значений для одного экземпляра сущности.
ФИО
Адреса
Контактные данные
Первая нормальная форма (1НФ).
Таблица приведена к 1НФ, если все его атрибуты простые.
Для того чтобы привести к 1НФ таблицу, содержащее сложные атрибуты,
нужно:
разбить составные атрибуты на простые,
построить декартово произведение всех многозначных атрибутов с
кортежами, к которым они относятся.
17

18. 6. Приведение к 1НФ. Таблица КЛИЕНТЫ

В таблицах, полученных после приведения к 1НФ, первичный ключ (РК)
состоит из первоначального РК и всех многозначных атрибутов.
18

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

Введём понятие функциональной зависимости. Пусть X и Y – атрибуты
(группы атрибутов) некоторой таблицы. Говорят, что Y функционально
зависит от X, если в любой момент времени каждому значению X=х
соответствует единственное значение Y=y (X Y). (При этом любому
значению Y=y может соответствовать несколько значений Х=(х1, х2,…)).
Номер паспорта ФИО
Должность Оклад
Атрибут X в функциональной зависимости X Y называется
детерминантом отношения.
Неключевой атрибут функционально полно зависит от составного ключа, если он
функционально зависит от ключа, но не находится в функциональной
зависимости ни от какой части составного ключа.
Вторая нормальная форма (2НФ).
Таблица находится во 2НФ, если она приведена к 1НФ и каждый
неключевой атрибут функционально полно зависит от составного ключа.
Для того чтобы привести таблицу к 2НФ, нужно:
1. Построить ее проекцию, исключив атрибуты, которые не находятся в
функционально полной зависимости от составного ключа.
2. Построить дополнительные проекции на часть составного ключа и
атрибуты, функционально зависящие от этой части ключа.
19

20. 7. Приведение к 2НФ

20

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

Рассмотрим понятие транзитивной зависимости.
Пусть X, Y, Z – атрибуты некоторой таблицы. При этом X Y и Y Z, но
обратное соответствие отсутствует, т.е. Y не зависит от Z или X не
зависит от Y. Тогда говорят, что Z транзитивно зависит от X (X Z).
Номер поставки Фирма-поставщик Адрес поставщика
Третья нормальная форма (3НФ).
Таблица находится в 3НФ, если она находится во 2НФ и в ней
отсутствуют транзитивные зависимости.
Исключение:
если для атрибутов X,Y,Z есть транзитивная зависимость X Z, и при
этом Y X или Z Y, то такая зависимость не требует декомпозиции
отношения.
Например, для отношения АВТОМОБИЛИ с первичным ключом
Государственный номерной знак и полями № кузова и № двигателя
очевидно, что номера кузова и двигателя зависят как друг от друга, так
и от первичного ключа. Но эта зависимость взаимно однозначная,
поэтому декомпозиция отношения не нужна.
21

22. 8. Приведение к 3НФ

Для того чтобы привести таблицу к 3НФ, нужно:
построить проекцию, исключив транзитивно зависящие от РК атрибуты;
построить дополнительно одну или несколько проекций на детерминанты
исходной таблицы и атрибуты, функционально зависящие от них.
Таблицы, находящиеся в 3НФ, свободны от аномалий модификации.
22

23. 9. Четвертая нормальная форма

Введём понятие многозначной зависимости.
Многозначная зависимость существует, если заданным значениям атрибута X
соответствует множество, состоящее из нуля (или более) значений атрибута Y. Если в
таблице есть многозначные зависимости, то схема таблицы должна находиться в
4НФ.
Различают тривиальные и нетривиальные многозначные зависимости. Тривиальной
называется многозначная зависимость X–»Y, для которой Y X или X U Y = R, где R –
рассматриваемое отношение.
Тривиальная многозначная зависимость не нарушает 4НФ. Если хотя бы одно из двух
этих условий не выполняется (т.е. Y не является подмножеством X или X U Y состоит
не из всех атрибутов R), то такая многозначная зависимость называется
нетривиальной.
Четвертая нормальная форма (4НФ).
Таблица находится в 4НФ, если она находится в 3НФ и в ней
отсутствуют нетривиальные многозначные зависимости.
23

24.

9. Приведение к 4НФ
В таблицах, полученных после приведения к 4НФ, первичный ключ (РК)
состоит из всех атрибутов таблицы.
24

25. 9. Схема после нормализации (до 4НФ)

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