Проектирование реляционных баз данных

1. Проектирование реляционных баз данных

2.

Этапы проектирования БД
Процесс создания базы данных можно представить в виде трех этапов:
1. Инфологическое (концептуальное) описание баз данных
Исходными данными для осуществления инфологического проектирования
является словесная и документальная характеристика предметной области. На
этом этапе решается вопрос о том, какие данные должны храниться в базе и
какого типа информационные выборки и отчеты могут потребоваться
пользователю БД.
2. Логическое проектирование баз данных
На этом этапе осуществляется выбор подходящей системы управления базами
данных (СУБД) и представление инфологической модели предметной области в
форме структуры базы данных конкретной СУБД. Для реляционных баз данных
на этом этапе производится описание структуры каждой таблицы и их
взаимосвязей.
3. Физическое проектирование. Предполагает определение способов и мест
размещения базы данных, оценку ее объема и других параметров.

3.

Реляционные базы данных.
Нормализация БД
Большинство современных систем управления
базами данных (СУБД) разработаны на основе
реляционной алгебры.
Первая работа по реляционной модели данных «A
Relational Model of Data for Large Shared Data Banks»
была опубликована в 1970 г.
Её автор - Эдгар Франк Кодд. В своей статье Э. Кодд
вывел несколько правил, или форм, по упорядочиванию
данных и их отношений.
Нормализация БД - это проектирование базы
данных так, чтобы она была компактной и не несла
логическую
избыточность.
Существует
несколько
разновидностей
нормализации,
так
называемые
нормальные формы. Все они идут в порядке
усложнения от простого.
Каждой нормальной форме соответствует некоторый
определенный набор ограничений, и отношение
находится в некоторой нормальной форме, если
удовлетворяет свойственному ей набору ограничений.
Всего существует 6 нормальных форм. На практике
редко нормализуют выше 3-ей нормальной формы.

4.

Ключи
Ключи являются составляющей частью нормализованных таблиц. Бывают
двух видов — внешние и первичные.
Первичный ключ — это атрибут, значения которого уникально
идентифицируют каждую запись таблицы. Первичный ключ отвечает
следующим условиям:
• Он должен иметь значение, не NULL.
• Быть неизменным — значение ключа не должно меняться.
• Иметь уникальное значение для каждой строки.
Внешние ключи — это ссылки на первичные ключи других таблиц.
Первичный
ключ
таблицы
«Маршруты»
Первичный
ключ
таблицы
«Маршрутостановка»
Внешний
ключ

5.

Степень связи
Степень связи показывает, как соотносятся данные в одной таблице с данными в
другой. Степень связи бывает 3-х видов:
• один-к-одному
• один-ко-многим
• многие-к-многим.
1) Связь «один-к-одному». Такая связь означает,
что каждому значению реквизита А
соответствует одно и только одно значение
связанного с ним реквизита В, и наоборот.
2) Связь «один-ко-многим». Эта связь означает,
что каждому значению реквизита А
соответствует ноль, одно или несколько
значений связанного с ним реквизита В, а
каждому значению реквизита В соответствует
одно и только одно значение реквизита А.
3) Связь «многие-ко-многим». Такая связь
означает, что каждому значению реквизита А
соответствует несколько значений связанного с
ним реквизита В, и наоборот. Например,
турагентство может работать с несколькими
туроператорами, а туроператор обычно имеет
разветвленную сеть турагентств.

6.

Первая нормальная форма (1NF)
Объект базы данных находится в первой нормальной форме тогда, когда каждый
ее атрибут атомарен.
Атрибут атомарен тогда, когда его значение теряет смысл при перестановке любой
из его частей или при любом разбиении его на части. То есть, одно поле - одно
значение.
Пример таблицы, которая не атомарна, а значит и не отвечает правилам первой
нормальной формы:
Наименование
Холодильник «Лютый»
Микроволновая печь «Вулкан»
Фен «Торнадо»
Телевизор ч\б «Рассвет»
Стоимость
Склад
5600
Основной А
1500
Основной Б
Производственный
1 100
Основной А

7.

Правила 1NF
1. Если у нескольких товаров одинаковая цена, то для каждого товара должна быть
отдельная запись (строка в таблице).
2. Если товар может храниться на нескольких складах, то для каждого склада
должна быть отдельная запись.
3. Стоимость товара – это число. Записывать его с пробелом в качестве
разделения числового разряда не правильно.
Пример таблицы, которая атомарна, а значит и отвечает правилам первой
нормальной формы:
Наименование
Стоимость
Склад
Холодильник «Лютый»
5600
Основной А
Микроволновая печь «Вулкан»
5600
Основной А
Фен «Торнадо»
1500
Основной Б
Фен «Торнадо»
1500
Производственный
Телевизор ч\б «Рассвет»
1100
Основной А

8.

Вторая нормальная форма (2NF)
Объект базы данных находится во второй нормальной форме тогда, когда
он находится в первой нормальной форме и при этом любой его атрибут,
не входящий в состав потенциального ключа, функционально полно
зависит от каждого потенциального ключа.
Это правило говорит об отделении функционально полных зависимостей
на отдельные структуры.

9.

Пример таблиц, находящихся во второй нормальной форме:
Товары
ID
Товар-Склад
Наименование
Стоимость
ID
ID
товара
ID
склада
Кол-во
1
Холодильник «Лютый»
5600
2
Микроволновая печь «Вулкан»
5600
1
1
1
3
3
Фен «Торнадо»
1500
2
2
1
4
4
Телевизор ч\б «Рассвет»
1100
3
3
2
5
4
3
3
10
5
4
1
1
Склады
ID
Склад
Адрес
Город
Индекс
1
Основной А
Ул. Тухачевского, 24
Кемерово
650023
2
Основной Б
Ул. Ангарская, 7
Томск
520543
3
Производственный
Ул. Терешковой, 133
Кемерово
650026

10.

Правила 2NF
1. Таким образом, в таблице «Товары» для каждого товара мы пишем не
названия склада, а указываем его первичный ключ, именуемый как ID. При
этом, названия складов выносим в отдельную таблицу.
2. Исходя из пункта №1 можно сделать вывод, что наличие первичного ключа, у
всех таблиц обязательно.
Такое функциональное разделение удобно. Условно предположим, что на
предприятии поменялось название склада. Тогда необходимо будет сделать
всего лишь одно исправление в таблице "Склады", а не исправлять несколько
тысяч записей.

11.

Третья нормальная форма (3NF)
Объект базы данных находится в третьей нормальной форме тогда, когда он
находится во второй нормальной форме и отсутствуют транзитивные зависимости не
ключевых объектов от ключевых.
Транзитивная зависимость - это очевидная зависимость между полями. Если поле А
равно х, то поле Б обязательно будет равно y. А если поле Б равно z, то тогда поле С будет
равно m. Такой зависимости между объектами быть не должно.
ID
Склад
Адрес
Город
Индекс
1
Основной А
Ул. Тухачевского, 24
Кемерово
650023
2
Основной Б
Ул. Ангарская, 7
Томск
520543
3
Производственный
Ул. Терешковой, 133
Кемерово
650026
На рис. показано наличие транзитивной зависимости - это поля "Город" и "Индекс". Если
индекс равен 650023, то мы уже знаем, что это город Кемерово (других городов с таким
индексом нет). От такой транзитивной зависимости необходимо избавляться путем
разбиения таблицы.

12.

Третья нормальная форма (3NF)
В конечном итоге, таблица «Склады» разбилась на 3 таблицы и полностью отвечает
правилам третьей нормальной формы.
Примечание: В таблице "Индексы" в качестве первичного ключа используется не ID
(суррогатный ключ, генерируемый СУБД), а номер индекса, так как он уникален, не может
повторяться и не изменяется с течением времени.
Склады
ID
Склад
Индекс
Адрес
1
Основной А
650023
Ул. Тухачевского, 24
2
Основной Б
520543
Ул. Ангарская, 7
3
Производственный
650026
Ул. Терешковой, 133
Индекс
Индекс
ID города
Город
ID города
Город
650023
1
1
Кемерово
520543
2
2
Томск
650026
1

13. Задание 1

Провести инфологическое и логическое проектирование в предметной области:
1. Библиотечная система
2. Станция ТО и Р
3. АТП
4. Университет
5. Реестр транспортных средств

14. Задание 2

Вариант 1
В результате инфологического проектирования были определены следующие
характеристики объектов, информация о которых должна находиться в базе
данных:
фамилия, имя, наименование дисциплины, преподаватель, оценка, год
поступления в ВУЗ, группа, домашний адрес, школа, хобби, награды, дата
рождения студента, E-mail студента, дата вручения награды, дата получения
оценки, шифр дисциплины.
Построить логическую модель базы данных.

15. Задание 2

Вариант 2
В результате инфологического проектирования были определены следующие
характеристики объектов, информация о которых должна находиться в базе
данных:
имена, адреса, даты рождения, домашние и служебные телефоны всех
служащих банка. В банке работает 5 отделов. Каждый отдел имеет свой
профиль. Данные по каждому отделу включают: его состав и начальник
отдела, прежние начальники, руководившие отделом за прошедшие 10 лет,
должности подчиненных, образование, год принятия на должность.
Построить логическую модель базы данных.

16. Задание 2

Вариант 3
В результате инфологического проектирования были определены следующие
характеристики объектов, информация о которых должна находиться в базе
данных:
фамилия исполнителя, дата рождения исполнителя, год создания группы,
адрес исполнителя, E-mail исполнителя, сот. телефон исполнителя, сайт
группы, название группы, руководитель группы, название альбома, дата
выпуска альбома, название композиций, номер композиции в альбоме по
порядку, жанр композиции, фотография исполнителя, фотография обложки
альбома, цена альбома, год вступления в группу.
Построить логическую модель базы данных.