Лабораторная работа №1

 

Проектирования БД на основе декомпозиции универсального отношения.

 

Цель работы: в процессе выполнения лабораторной работы научиться проектировать БД на основе декомпозиции универсального отношения.

 

Основные теретические сведения.

Реляционная база данных (БД) –по существу это БД, в которой данные представлены в виде таблиц. Реляционная таблица состоит из пересекающихся строк (другие названия – запись, сущность) и столбцов (другие названия - поле, атрибут). Термин «relation» (отношение) – это формальное название определенного вида таблицы.

Требования, предъявляемые к реляционным таблицам:

1.      данные в ячейках таблицы должны быть структурно неделимы;

2.      данные в одном столбце должны быть одного типа;

3.      каждый столбец должны быть уникальным;

4.      столбцы размещаются в произвольном порядке;

5.      строки размещаются также в произвольном порядке;

6.      столбцы имеют уникальные наименования.

Главными «строительными блоками» реляционной БД являются сущности (entity). Термин сущность обычно используется для обозначения любого различимого объекта, который может быть представлен в БД (персона, местоположение или предмет, сведения о которых подлежат сбору и хранению). Сущности группируются по их общим свойствам. Набор сущностей (entity set) это именованная совокупность сущностей, объединенных общими свойствами. Таким образом, таблица содержит группу связанных сущностей, по этой причине термины таблица и набор сущностей чаще всего означают одно и тоже.

Каждая сущность имеет некоторые свойства, называемые атрибутами. Например, на рис. 3 набор сущностей с именем «Студенты» содержит множество сущностей «студент» с атрибутами Код_студента, Фамилия, Имя и др.   

Ключи – центральная составляющая реляционных таблиц. Ключи определяют функциональные зависимости, то есть другие атрибуты зависят от ключа и могут, следовательно, быть найдены по его значению. Каждая строка таблицы должна иметь первичный ключ, который представляет собой атрибут или комбинацию атрибутов, которые уникально идентифицируют все остальные атрибуты в любой строке. Поскольку первичный ключ должен быть уникален, в нем для обеспечения целостности не допускаются пустые значения.

Несмотря на то, что все таблицы независимы, их можно связать при помощи совместно используемых атрибутов. Связи в БД можно классифицировать по типу «один-ко-одному» (1:1), «один-ко-многим» (1:М) и «многие-ко-многим» (М:М). Связь «один-ко-одному» означает, что данная сущность может быть связана только с одной другой  сущностью и т. д. Связь 1:М практически идеальна для реляционной модели и является в ней основным кирпичиком.

Процесс нормализации – это разбиение (или декомпозиция) таблицы на две или более с целью ликвидации дублирования данных и потенциальной их противоречивости.

 

Пример процесса нормализации таблиц БД.

Рассмотрим следующую задачу: пусть необходимо обеспечить сбор и обработку данных по результатам сдачи экзаменов и зачетов студентами факультета. При этом организация данных должна поддерживать:

- формирование сводной ведомости курса;

- формирование ведомостей по отдельным дисциплинам для групп студентов;

- формирование листов зачетных книжек студентов;

- расчет среднего балла по дисциплинам, группам и т. п.;

- формирование информационных листов о результатах сдачи сессии для рассылки родителям;

- содержать основную информацию о кадровом составе.

 

Рассмотрение задачи проектирования начнем с анализа сводной таблицы, содержащей все сведения, необходимые для решения первой из поставленных задач, пример которой приведен на рис.1. Предложенная таблица отражает результаты сдачи сессии (шкала оценок: зачет – 0 и 1; экзамен – 2, 3, 4, 5). Этот вариант таблицы не является отношением, т. к. большинство ее столбцов не атомарны (атомарными являются лишь значения столбцов «ФИО студента» и «Семестр»). Остальные столбцы таблицы – множественные.

Рис. 1. Исходные данные для создания БД «Сессии»

 

Для преобразования данных в отношение необходимо реконструировать таблицу, результат такого преобразования представлен на рис. 2.

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

Однако при использовании универсального отношения возникают, по крайней мере, две проблемы:

1.      Избыточность данных. Значения столбцов таблицы многократно повторяются. пто такое преобразование приводит к возникновению большого объема и избыточных данных. преобразования представлен на рис. акульПовторяются также и некоторые наборы значений столбцов, например, данные о дисциплине.

2.      Потенциальная противоречивость. При изменении в учебном плане количества часов по отдельной дисциплине, необходимо  для исправления найти все строки, содержащие о ней сведения. Более того, при заполнении таблицы могут быть использованы различные формы записи одного и того же значения (например, Мат. ан. и мат. анализ).

 

Рис. 2. Универсальное отношение «Сессия»

 

Решение этих проблем состоит в разделении данных и связей, т. е. в определении отдельных сущностей и выделении в отдельные таблицы сведений о студентах, преподавателях, дисциплинах и результатах сдачи сессии (рис. 3).

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

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

 

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

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

Теория нормализации основывается на наличии той или иной зависимости между столбцами таблицы.

Студенты                               Дисциплины                                   Преподаватели

Код сту- дента	ФИО студента		Код дисцип- лины	Дисциплина		Код препо- давателя	ФИО преподавателя
1	Андреев А. А.		1	Английский язык		1	Борисов Б. Б.
2	Денисов Д. Д.		2	Математический анализ		2	Васильев В. В.
3	Алексеев А. А.		3	Программирование		3	Гаврилов Г. Г.
4	Русланов Р. Р.		4	Линейная алгебра		4	Дмитриев Д. Д.
			5	История отечества		5	Егоров Е. Е.
			6	Информатика		6	Петров П. П.
			7	Теория вероятностей		7	Сидоров С.  С.
			8	Экономическая теория		8	Савельев С. С.
						9	Яковлев Я. Я.
						10	Иванов И. И.
						11	Павлов П. П.

 

Учебный план                                                                   Результаты сессии

Код учебного плана	Код Дисцип- лины	Семестр	Кол-во часов	Форма отчетности	Код Препода-вателя		Код студента	Код учебного плана	Оценка
1	1	1	60	зачет	1		1	1	1
2	2	1	28	зачет	2		1	2	1
3	2	1	32	экзамен	3		1	3	3
4	3	1	36	зачет	4		1	4	1
5	3	1	32	экзамен	5		1	5	3
6	4	1	24	экзамен	6		1	6	3
7	5	1	24	экзамен	7		1	7	3
8	1	3	60	зачет	1		2	1	1
9	2	3	20	зачет	3		2	2	1
10	2	3	28	экзамен	8		2	3	3
11	6	3	32	зачет	9		2	4	1
12	6	3	36	экзамен	9		2	5	3
13	7	3	32	экзамен	10		2	6	3
14	8	3	24	экзамен	11		2	7	3
15							3	1	1
16

Рис. 3. Разделение универсального отношения «Сессия»

 

Функциональная зависимость, по сути, является связью типа «многие к одному» между множествами атрибутов (столбцов) рассматриваемого отношения. Например, в таблице «Учебный план» (рис. 3) столбцы Код _дисциплины, Семестр и Форма _отчетности функционально зависят от ключа Код _учебного _плана, а в таблице «Результаты сессии» столбец Оценка функционально зависит от составного ключа (Код _студента, Код _учебного _плана).

 

Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все ее атрибуты (столбцы), не входящие в первичный ключ, связаны полной функциональной зависимостью с первичным ключом.

Не удовлетворяет этим требованиям таблица на рис. 2. таблица имеет составной первичный ключ (ФИО _студента, Семестр, Дисциплина, Форма _отчетности) и содержит множество неключевых атрибутов (Оценка, Количество _часов, ФИО _преподавателя), зависящих лишь от той или иной части первичного ключа. Так, атрибуты Количество часов и ФИО преподавателя зависят только от атрибутов Семестр, Дисциплина, Форма _отчетности. Следовательно, эти атрибуты не связаны с первичным ключом полной функциональной зависимостью.

К 2НФ приведены все таблицы на рис. 3.

 

Таблица находится в 3НФ, если она удовлетворяет определению 2НФ и ни один из ее неключевых атрибутов не связан функциональной зависимостью с любым другим неключевым атрибутом.

Таблица «Учебный план» (рис. 3), очевидно, не находилась бы в 3НФ, если включала бы в себя столбец Кафедра (место работы преподавателя). В этом случае необходимо было бы произвести декомпозицию таблицы для получения дополнительной таблицы с атрибутами Код _преподавателя, ФИО _преподавателя, Кафедра (в нашем примере – это таблица «Преподаватели»).

В примере на рис. 3 в таблице «Учебный план» первичный ключ представлен атрибутом Код_ учебного_ плана. В качестве первичного ключа в этой же таблице может выступать также и тройка атрибутов: Семестр, Код_ дисциплины и Форма_ отчетности. Кодд и Бойс обосновали и предложили более строгое определение для 3НФ, которое учитывает, что в таблице может быть несколько первичных ключей.

Таблица находится в нормальной форме Бойса - Кодда (НФБК) тогда и только тогда, когда любая функциональная зависимость между ее атрибутами сводится к полной функциональной зависимости от ее возможного первичного ключа.

 

Если не дополнять таблицу «Учебный план» атрибутом Код_ учебного_ плана, то составной первичный ключ в этой таблице будет следующим: Код_ дисциплины, Семестр, Форма_ отчетности, а в таблице «Результаты сессии» в качестве первичного ключа будут использованы атрибуты: Код_ дисциплины, Семестр, Форма_ отчетности и Код_ студента.  Повторение в обеих таблицах  атрибутов, используемых в качестве первичного ключа, приводит к избыточности информации при дублировании сразу трех столбцов, поэтому кажется целесообразным введение дополнительного атрибута – Код_ учебного_ плана и использование именно его в качестве первичного ключа.

 

На рис.3 каждая из определенных нами сущностей, необходимых для решения поставленной задачи («Студенты», «Дисциплины», «Преподаватели», «Учебный план», «Результаты сессии») представлена  в виде отдельной таблицы  или, другими словами, каждая из разработанных таблиц содержит набор сущностей. Дополнив наборы сущностей «Студенты», «Дисциплины» и «Преподаватели»  дополнительными атрибутами, описывающими те их свойства, которые необходимы для решения поставленных задач и установив связи между совместно используемыми атрибутами, получаем схему данных, представленную на рис. 4.

 

 

 

Рис. 4. Структура БД «Сессия»

 

Как видно из рисунка все таблицы БД находятся в 3НФ:

1.      каждый столбец таблицы неделим, и в рамках одной таблицы нет столбцов с одинаковыми по смыслу значениями (1НФ);

2.      первичные ключи однозначно определяют запись и неизбыточны, все поля каждой из таблиц зависят от ее первичного ключа (2НФ);

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

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

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

 

Примечание

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

 

Задание к лабораторной работе:

1.      Выбрать вариант, определяющий интересную Вам предметную область. При этом помнить, что с данным вариантом Вы будете работать в течение всего семестра.

2.      Сформулировать задачи, для решения которых разрабатывается проект БД. Размерность и сложность задачи должна быть сокращена до такого уровня, чтобы конечная схема данных содержала 4-6 таблиц.

3.      Разработать схему проекта БД, удовлетворяющую условиям нормализации на трех уровнях.

4.      Определить типы данных, хранящихся в столбцах таблиц.

5.      В отчете отразить выполнение по каждому из пунктов задания.

 

Контрольные вопросы:

1.      Дайте определение реляционной базы данных.

2.      Из чего состоит реляционная таблица?

3.      Что обозначает термин «relation»?

4.      Перечислите требования, предъявляемые к реляционным таблицам.

5.      Что такое сущность?

6.      Что такое набор сущностей?

7.      Раскройте понятие атрибута.

8.      Что такое ключ? Понятие первичного ключа.

9.      Виды связей в БД.

10. Процесс нормализации БД.

11.  Приведение БД к 1 НФ.

12. Приведение БД ко 2 НФ.

13. Приведение БД к 3 НФ.

14. Что такое универсальное отношение?

15. Что такое домен?