Технологии
баз данных |
ЛЕКЦИИ
Лекция на тему
ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ
Разработчик: доц. Оскерко В.С.
План лекции
1. Требования, предъявляемые к базе данных
2. Этапы жизненного цикла базы данных
4. Преобразование ER-модели в реляционную
5. Нормализация таблиц
6. Этапы проектирования базы данных и их процедуры
&
5. Нормализация таблиц
Реляционная база данных считается эффективной, если она обладает приведенными ниже характеристиками.
1. Минимизация избыточности данных. В базе данных присутствует избы-
точность, если одни и те же данные находятся в нескольких местах. Вследствие этого память компьютера используется неэкономно и времени на корректировку данных тратится больше. Так в табл. 1.1 содержится много избыточной информации.
Таблица 1.1
Сведения о студентах, изучающих иностранные языки
Ф.И.О. |
Шифр группы |
Название курса |
Преподаватель |
Жибуль И.П. |
А1 |
Английский |
Мороз В.С. |
Булатый В.А. |
А1 |
Английский |
Мороз В.С. |
Кузьмич Н.М. |
А1 |
Английский |
Мороз В.С. |
Шкляр Е.К. |
Н1 |
Немецкий |
Перов И.Т. |
Теслюк Г.О. |
А2 |
Английский |
Null |
Шнек В.И. |
А2 |
Английский |
Null |
Примечание. Если таблица является объектом реляционной базы данных, то ее столбцы называются полями, а строки – записями.
Если, например, изменится название курса "Английский" на "Английский для делового общения", то его надо заменить во всех записях о тех студентах, которые изучают данный курс.
2. Минимальное использование отсутствующих значений (Null-значений). В нашем примере неясно, означают ли Null-значения атрибута "Преподаватель", что для группы А2 не определен преподаватель или его Ф.И.О. не введено. Из-за неопределенности интерпретации Null-значений их использование желательно свести к минимуму.
3. Предотвращение потери информации. Если, например, студент Шкляр Е.К. решит не изучать немецкий язык, то придется удалить запись со сведениями о нем и тогда вообще будет потеряна информация о данном курсе.
Минимизировать избыточность данных позволяет процесс, называемый нормализацией таблиц. Нормализацию можно было использовать для получения эффективных структур данных, созданных в результате преобразования ER-диаграмм в таблицы в предыдущем параграфе. Но чтобы пояснить этот процесс, будем исходить из описания предметной области БАНК, данного в параграфе 1.3, и предположения, что на его основе была разработана база данных, состоящая из следующих двух таблиц:
ФИЛИАЛ
НФ |
АДР_Ф |
НМ |
НС |
ОСТ |
ТИП |
511 |
Ванеева, 6 |
7 |
1111 2222 3333 |
200 350 1000 |
Д Т Т |
513 |
Солтыса, 3 |
9 |
5555 6666 |
800 14 |
Т Д |
Примечание. Д – депозитный счет, Т – текущий счет.
КЛИЕНТ
НК |
ФИО_К |
СОЦ_П |
АДР_К |
НС |
23 |
Сокол С.С. |
Служащий |
Садовая, 1 |
1111 3333 |
34 |
Брас Б.Б. |
Рабочий |
Гая, 9 |
5555 |
45 |
Лань Л.Л. |
Служащий |
Лесная, 4 |
2222 6666 1111 |
Примечание. Ключ – комбинация НК, НС.
Методику нормализации таблиц разработал американский ученый
А.Ф. Кодд в 1970 г. Ее суть сводится к приведению таблиц к той или иной нормальной форме. Были выделены три нормальные формы – 1НФ, 2НФ, 3НФ. Позже стали выделять нормальную форму Бойса–Кодда (НФБК), а затем 4НФ и 5НФ. Каждая последующая нормальная форма вводит определенные ограничения на хранимые в базе данные.
Реляционная база данных считается эффективной, если все ее таблицы находятся как минимум в 3НФ. Приведение к 3НФ осуществляется, если есть основание для этого.
Таблица находится в 1НФ, если все ее поля содержат только простые неделимые значения.
Таблицы ФИЛИАЛ и КЛИЕНТ не удовлетворяют требованиям 1НФ. Для приведения их к 1НФ в них надо вставить новые записи следующим образом:
ФИЛИАЛ
НФ |
АДР_Ф |
НМ |
НС |
ОСТ |
ТИП |
511 |
Ванеева, 6 |
7 |
1111 |
200 |
Д |
511 |
Ванеева, 6 |
7 |
2222 |
350 |
Т |
511 |
Ванеева, 6 |
7 |
3333 |
1000 |
Т |
513 |
Солтыса, 3 |
9 |
5555 |
800 |
Т |
513 |
Солтыса, 3 |
9 |
6666 |
14 |
Д |
КЛИЕНТ
НК |
ФИО_К |
СОЦ_П |
АДР_К |
НС |
23 |
Сокол С.С. |
Служащий |
Садовая, 1 |
1111 |
23 |
Сокол С.С. |
Служащий |
Садовая, 1 |
3333 |
34 |
Брас Б.Б. |
Рабочий |
Гая, 9 |
5555 |
45 |
Лань Л.Л. |
Служащий |
Лесная, 4 |
2222 |
45 |
Лань Л.Л. |
Служащий |
Лесная, 4 |
6666 |
45 |
Лань Л.Л. |
Служащий |
Лесная, 4 |
1111 |
Но полученные таблицы неэффективны, так как содержат много избыточной информации. Необходимо их привести к 2НФ.
Таблица находится в 2НФ, если она удовлетворяет требованиям 1НФ и неключевые поля функционально полно зависят от первичного ключа.
Функциональная зависимость – это понятие, отображающее определенную семантическую связь между полями таблицы. Пусть (Х1, Х2,…,Хк) – множество полей, образующих первичный ключ.
Неключевое поле А функционально полно зависит от первичного ключа, если:
· оно функционально зависит от первичного ключа, т.е. каждой комбинации значений полей первичного ключа соответствует одно и только одно значение поля А, что записывается
(Х1, Х2,…,Хк)®А
· не существует функциональной зависимости А ни от какого подмножества полей первичного ключа (в противном случае А находится в частичной функциональной зависимости от первичного ключа).
В таблице КЛИЕНТ неключевые поля ФИО_К, СОЦ_П, АДР_К функционально зависят от ключа (НК, НС), что запишем
НК, НС ® ФИО_К, СОЦ_П, АДР_К
Кроме того, они функционально зависят от подмножества ключа – НК, что запишем
НК ® ФИО_К, АДР_К, СОЦ_П
Следовательно, неключевые поля ФИО_К, СОЦ_П, АДР_К находятся в частичной функциональной зависимости от первичного ключа (НК, НС) и нарушаются требования 2НФ. Эти поля надо из таблицы КЛИЕНТ удалить. Полученную в результате этого таблицу назовем КЛИЕНТ–СЧЕТ (таблица 1), которая имеет вид
КЛИЕНТ–СЧЕТ
НК |
НС |
23 |
1111 |
23 |
3333 |
34 |
5555 |
45 |
2222 |
45 |
6666 |
45 |
1111 |
Эта таблица удовлетворяет требованиям 2НФ.
Удаленные неключевые поля помещаются в новую таблицу совместно с подмножеством НК, от которого они зависят. И это подмножество будет первичным ключом новой таблицы КЛИЕНТ (таблицы 2) вида
КЛИЕНТ
НК |
ФИО_К |
СОЦ_П |
АДР_К |
23 |
Сокол С.С. |
Служащий |
Садовая, 1 |
34 |
Брас Б.Б. |
Рабочий |
Гая, 9 |
45 |
Лань Л.Л. |
Служащий |
Лесная, 4 |
Новая таблица КЛИЕНТ также удовлетворяет требованиям 2НФ. Ее неключевые поля функционально полно зависят от первичного ключа.
Полученные таблицы 1, 2 не содержат избыточной информации, и нет основания приводить их к 3НФ.
Таблица ФИЛИАЛ удовлетворяет требованиям 2НФ, так как ее неключевые поля НФ, АДР_Ф, НМ, ОСТ, ТИП функционально полно зависят от первичного ключа
НС® НФ, АДР_Ф, НМ, ОСТ, ТИП
Но в таблице ФИЛИАЛ повторяется информация о филиале для всех счетов, обрабатываемых им. Поэтому ее надо привести к 3НФ.
Таблица находится в 3НФ, если она удовлетворяет требованиям 2НФ и не содержит транзитивных зависимостей.
Транзитивной зависимостью называется функциональная зависимость между неключевыми полями. В таблице ФИЛИАЛ она наблюдается
НФ® АДР_Ф, НМ
Следовательно, нарушаются требования 3НФ. Из таблицы ФИЛИАЛ надо удалить поля, участвующие в этой транзитивной зависимости, – АДР_Ф, НМ. Получится таблица, характеризующая счет (таблица 3), вида
СЧЕТ
НС |
ОСТ |
ТИП |
НФ |
1111 |
200 |
Д |
511 |
2222 |
350 |
Т |
511 |
3333 |
1000 |
Т |
511 |
5555 |
800 |
Т |
513 |
6666 |
14 |
Д |
513 |
Затем создается новая таблица, в которую помещаются удаленные поля и поле, от которого они зависят (таблица 4). Она имеет вид
ФИЛИАЛ
НФ |
АДР_Ф |
НМ |
511 |
Ванеева, 6 |
7 |
513 |
Солтыса, 3 |
9 |
Полученные таблицы 3, 4 приведены к 3НФ. В них каждая запись есть отдельное независимое утверждение. Повторяются только значения внешнего ключа НФ в таблице СЧЕТ, что неизбежно, так как одним филиалом могут обрабатываться несколько счетов.
Как видим, нормализация приводит к фрагментации исходных таблиц. В нашем примере таблица КЛИЕНТ разбивается на таблицы 1, 2, а таблица ФИЛИАЛ – на таблицы 3, 4. Осуществив связь этих таблиц посредством связи первичных и внешних ключей, получим реляционную модель данных предметной области БАНК, в которой минимизирована избыточность данных. Эта модель представлена на рис. 1.8.
Рис. 1.8. Реляционная модель предметной области БАНК после нормализации
© Минск
БГЭУ, |