АЛМАТИНСКИЙ ИНСТИТУТ ЭНЕРГЕТИКИ И СВЯЗИ 

Кафедра инженерной кибернетики 

 

 

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

 

 

Конспект лекций

для студентов всех форм обучения специальности
050718 - Электроэнергетика

 

 

Алматы 2010

СОСТАВИТЕЛЬ: В.М. Тарасов. Конспект лекций по дисциплине  «Проектирование баз данных» для студентов всех форм обучения специальности 050718 – Электроэнергетика. – Алматы: АИЭС, 2010. – 26 с.

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

 

1 Лекция 1. Основные понятия реляционной модели базы данных

 

Некоторые формулировки.

База данных (БД) – совокупность сведений о конкретных объектах реального мира в какой-либо области знаний (металлургия, образование, предприятие, энергетика).

СУБД – система управления базами данных, состоящая из комплекса программ.

Объект или сущность – нечто существующее и различимое. (предприятие, студент, преподаватель).  Объекты или сущности это не только материальные предметы, но и абстрактные понятия: события, решения, нормы.

Атрибут или данное – показатель, который характеризует некоторый объект и принимает числовое, текстовое или иное значение.

Кодирование. Списки всевозможных значений атрибутов часто кодируют. Кодирование применяют для единообразия, удобства, исключения ошибок ввода. Названия Тульский з-д, тульский завод, Тульский завод обозначают для человека одно и то же, для ПК это разные данные.

В БД можно вставлять ссылки на фото или на сайты.

Если в ячейке ничего не записано, ее считают пустой (значение Null), не следует Null путать с пробелом (он имеет код 32) или нулем (он имеет код 48).

БД могут иметь сетевую, иерархическую или табличную (реляционную) структуру.

Сетевая структура показана на рисунке 1. В узлах сети находятся данные. Такую базу данных трудно автоматизировать.

Иерархическая структура показана на рисунке 2.

Реляционная модель базы данных

Реляционная модель является удобной и наглядной фор­мой представления данных в виде таблицы.

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

В математических дисциплинах понятию «таблица» соответствует понятие «отношение» (relation). Отсюда и произошло название модели — реляционная модель. Применительно к базам данных понятия «реляционная БД» и «таблич­ная БД» являются синонимами.

Одним из основных преимуществ реляционной модели является ее однородность. Все данные рассматриваются как хранимые в таб­лицах, в которых каждая запись имеет один и тот же формат. Каж­дая запись в таблице представляет некоторый объект реального мира, описанный с помощью атрибутов или отношение между объектами.

Столбцы в таблице БД в теоретической литературе называют атрибутами, а в технической литературе чаще называют полями. Строки в теоретической литературе называют кортежи, а в технической литературе чаще называют записями.

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

 

Наименование  фирмы

Коды продукции (шины, фары, зеркала)

Коды технологий (штамповка, литье, покраска, хромирование)

Цена

 

 

Как мы видим,  в БД широко применяют кодирование информации. Ее применение повышает надежность и достоверность БД.

Т а б л и ц а 1

В таблицу 1 для целей автоматизации введено дополнительное поле –  Счетчик. Теперь каждая запись таблицы однозначно определяется значением поля Счетчик, называемым первичным ключом.

Первичный ключ (Primary Key, PK) – идентификатор, значение которого однозначно определяет запись в данной таблице. Остальные поля этой таблицы нельзя использовать в качестве ключевых полей из-за наличия повторяющихся значений. Как правило, в качестве первичного ключа используется обычно счетчик записей.

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

В таблице 2 сочетания значений ключевых полей однозначно определяют вид товара и его цену.

Т а б л и ц а 2 – Составной первичный ключ

Ключевое поле 1	Ключевое поле 2	Товар	Цена
0	0	Фара	5000
0	1	Шина	6000
1	0	Стекло	4000
1	1	Руль	4500

Номера (ключи) записей никогда не меняются.

На практике есть возможности выводить на экран (печать) вместо большинства кодов их значения. Например, вместо кода 02 выводить слово «Штамповка».

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

Внешний ключ (Foreign Key, FK)  — это поле одной таблицы, совпадающее с первичным ключевым полем другой  таблицы. Таблицу, в которой определен внешний ключ, будем называть подчиненной, а таблицу с первичным ключом — главной. В обоих полях должны быть записаны однотипные данные, например, номера зачеток студентов.  Внешние и первичные клю­чи создаются для  связи между таблицами базы данных.

Если таблица связана с несколькими другими таблицами, она имеет несколько внешних ключей.

Недопустимо, чтобы в ячейке таблицы содержались сложная неатомарная информация. Не следует в одну ячейку писать класс и литер класса, например, «10Б», но в отдельных случаях это можно допустить.

Ограничения целостности

Еще одним назначением первичного ключа является обеспечение ссылочной целостности данных в нескольких таблицах. Это может быть реализовано только при наличии соответствующих внешних ключей (Foreign Key) в подчиненных таблицах. Ссылочная целостность данных двух таблиц обеспечивается следующим обра­зом:

- в подчиненной таблице нельзя вставить такое данное во внешний ключ, которого нет в первичном ключе главной таблицы;

- из главной таблицы нельзя удалить строку, если значение ее первичного ключа используется в подчиненной таблице.

Столбцы внешнего ключа (в отличие от полей первичного ключа) могут содержать значения типа Null, то есть быть пустыми. За­дать внешний ключ можно как при создании, так и при изменении таблиц. Имена полей первичного и внешнего ключей в обеих таблицах могут совпадать, но это не является обя­зательным (см. рисунок 3).

Рисунок 3 - Связь внешних и первичных ключей

На рисунке 3 поля Код студента и Код плана являются по отдельности внешними ключами таблицы Сводная ведомость и служат для ее связи с таблицами Студенты и Учебный план. Вместе эти поля являются составным первичным ключом для таблицы Сводная ведомость и однозначно функционально определяют поля  Оценка и Дата сдачи.

Установка ограничений для данных

Ограничения для данных устанавливаются для исключения ошибок при вводе данных. Их можно определить, задав диапазон возможных зна­чений для поля. Например, если в поле хранится данное, выраженное  в процентах от общего количества, то следует указать его диапазон от 0 до 100. Оценку в ведомости за экзамен ограничить значениями от 0 до 9.

Кроме того, для поля может быть задано значение по умолчанию. Например, часто ноль определяется как значение по умолчанию для числовых полей, а «н/о» (не определено), как значение по умолчанию для символьных (текстовых) полей.  

Проектирование БД начинается с изучения предметной области.

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

На основании предметной области можно создать универсальное отношение. Дальнейшая задача проектирования БД — это сокращение из­быточности хранимых данных, например, устранение одинаковых полей в разных таблицах. Каждый факт в БД должен быть записан только один раз, это обеспечивает:

- уменьшение затрат на многократные опера­ции вставки одних и тех же данных в разные таблицы;

- устранение возможности воз­никновения противоречий из-за хранения в разных местах сведений об одном и том же объекте в разной форме (Тульский завод, тульский завод, Тульский зав-д).

Универсальное отношение (таблица)

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

Пусть требуется создать базу данных Деканат, которая учитывала бы сдачу всеми студентами плановых предметов. Кроме того, БД должна учитывать работу преподавателей в часах. Анализ предметной области показал, что, исходя из современной практики проставления рубежных оценок и экзаменационных оценок, необходимо дисциплину представлять в виде нескольких независимых названий. Вместо ТОЭ следует писать ТОЭ лекции, ТОЭ практика, ТОЭ лабораторные.

Следовательно, в БД должны быть основные поля: ФИО студента,  Дисциплина, Форма отчетности, Оценка, Дата сдачи, Кол-во часов, ФИО преподавателя, Поток студентов. Этот вариант таблицы Деканат еще не является отношением, так как часть ее полей являются зависимыми от других полей. Независимыми являются лишь значения полей ФИО студента, Дисциплина. Определим их как составной первичный ключ. Остальные поля таблицы — зависимые от каких-то других полей

Т а б л и ц а  3 – Универсальное отношение Деканат (фрагмент)

ФИО студента	Дисциплина	Форма отчетности	Оценка	Дата сдачи	Кол часов	ФИО преподавателя	Поток студентов
Иванов И.	ТОЭ лек	Экзамен	9	09.03.2009	20	Тайцев Л.К.	БЭ-09-1,2,3,4
Иванов И.	ТОЭ лаб	Рубежная	7	04.03.2009	26	Теткова Л.Б.	БЭ-09-1
Иванов И.	Физика лек	Экзамен	3	01.03.2009	30	Факов Л.К.	БЭ-09-1,2,3,4
Пак И.	Физика лаб	Рубежная	9	05.03.2009	16	Феменов Л.Б.	БЭ-09-2

 

2 Лекция 2. Этапы проектирования данных. Нормализация

Избыточность и противоречивость

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

- избыточность  данных.   Значения  полей в  таблице   много­кратно повторяются, например, данные о дисциплине, ФИО студента;

- потенциальная противоречивость. Если требуется  изменить, на­пример,   количество  часов для  дисциплины  ТОЭ лаб., то для исправления необходимо найти все строки, содержащие сведения об этой дисциплине, и во всех этих строках произвести изменения. Кроме того, при заполнении та­кой таблицы случайно могут быть использованы различные формы записи одного и того же значения, например, «ТОЭ лаб.» и «ТОЭ лаб».

Решение указанных проблем состоит в нормализации таблиц.

Процесс нормализации — это разбиение исходной таблицы на несколько новых таблиц, с целью ликвидации дублирования данных и устранения потенциальной их про­тиворечивости. Цель нормализации - полу­чить такой проект базы данных, в котором каждый факт заполняется лишь в одном месте. Уровни нормализации определяются по так называемым нормальным формам.

Если в каждой ячейке таблицы находится атомарное, неделимое  значение, то говорят, что табли­ца  находится в первой нормаль­ной форме, сокращенно пишут 1НФ.

Например, в поле Класс значение записи «10 б» не атомарно, поэтому номер класса и символ класса следует писать в разных полях.

В дополнение к 1НФ определяют дальнейшие уровни нормализации:  вторую нормальную форму (2НФ), третью нормальную форму (ЗНФ).

Функциональная и многозначная зависимости

Рассмотрим два вида зависимостей между полями: функциональные (однозначные) и многозначные.

Функциональная зависимость. В таблице 1 поля Фирма, Код продукции функционально однозначно зависят от ключа Счетчик.

В таблице 2 поле Товар функционально однозначно зависит от состав­ного ключа (Ключевое поле 1, Ключевое поле 2).

Функциональная зависимость является связью типа «один к одному» между значениями  полей в рас­сматриваемой таблице.

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

В качестве примера рассмотрим фрагмент таблицы 4. В этой таблице существует многозначная зависимость Дисциплина — Пре­подаватель. Дисциплину Математика лекции ведут несколько преподавателей (Усов И. И., Петин К. К.), и, соот­ветственно, они могут участвовать в приеме экзаменов. При этом поля Форма отчетности и Преподаватель не связаны функциональной зависимостью, что приводит к появлению избы­точности. Чтобы добавить фамилию еще одного преподавателя, ведущего лекции и практику, при­дется ввести в таблицу две новые записи для рубежного контроля и экзамена.

Т а б л и ц а  4 - Фрагмент отношения Контроль

Дисциплина	Преподаватель	Форма отчетности
Математика лекции	Усов И. И.	Экзамен
Математика лекции	Петин К. К.	Экзамен
Математика практика	Усов И. И.	Рубежный контроль
Математика практика	Петин К. К.	Рубежный контроль

 

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

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

Ко второй нормальной форме приведена таблица 2.

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

Кодд и Бойс предложили определение для ЗНФ, которое учитывает, что в таблице может быть два первичных ключа.

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

Т а б л и ц а 5

Счетчик	Фирма	Код продукции	Код технологии	Цена
1	Фирма Арго	3895	02	578
2	Фирма Арго	3895	03	612
3	Фирма Арго	3002	02	650
4	Фирма Арго	3002	05	680
5	Фирма Борей	3895	02	600
6	Фирма Борей	3895	03	605
7	Фирма Борей	3095	03	1200

В таблице 5 существуют зависимости между полем Цена и ключевым полем Счетчик с одной стороны, и совокупно­стью полей Фирма, Код продукции и Код технологии — с дру­гой стороны. То есть тройка  этих полей (Фирма, Код продукции и Код технологии) может выступать в качестве первичного ключа, который на самом деле представлен в таблице полем Счетчик. Таким образом, в таблице 5 существуют два возможных первичных ключа. Следовательно, таблица 5 находится в НФБК или в ЗНФ.

Рассмотрим еще таблицу 6, в которой есть однофамильцы. На первый взгляд таблица находится в НФБК, в таблице два возможных первичных ключа, поскольку сочетание полей ФИО студента и Группа однозначно определяют поля Староста и Эдвайзер.

Т а б л и ц а 6.

Код студента	ФИО студента	Группа	Староста	Эдвайзер
1	Ким К.	БЭ-09-1	Тулеев П.	Зайцев К.И.
2	Серов А.	БЭ-09-1	Тулеев П.	Зайцев К.И.
2	Ким К.	БЭ-09-2	Кукин Т.	Сапаров М.И..
Первичный ключ	Возможный первичный ключ

Однако, если есть студенты однофамильцы-тезки, учащиеся в разных группах, то оказывается, что поля Староста и Эдвайзер зависят от части возможного первичного ключа Группа. И таблица не находится в НФБК. С другой стороны, если этим пренебречь из-за малой вероятности, то оказывается, что ключ один Код студента и поля Староста и Эдвайзер  зависят от неключевого поля Группа. Следовательно, таблица нуждается в дальнейшей нормализации в любом случае.

3 Лекция 3. Процедура нормализации

Рассмотрим процедуру приведения таблиц к 3НФ (НФБК). Она основывается на том, что единственными функциональными зависимостями в любой таблице должны быть зависимости, когда первичный ключ однозначно определяет любое поле. Принцип «один факт в одном месте» говорит о том, что не должно существовать в рамках таблицы никаких других функциональных зависимостей. Цель нормализации и состоит в удалении этих «других» функциональных зависимостей.

Рассмотрим два возможных варианта.

Вариант 1. Таблица имеет составной первичный из полей D_K, E_К и включает поля А, В и С. Пусть поле А функционально зависит только от части составного ключа только от поля E_К, см. рисунок 4. В этом случае рекомендуется сформировать дополнительную таблицу, содержащую поле E_К (в качестве первичного ключа),  и  поле А. Неключевое поле А из первоначальной таблицы удаляется. В ней останутся поля D_K, E_К,  В, С.

Рисунок 4

Вариант 2. Таблица имеет первичный  ключ С_К, а также поля А и В. Поле А функционально зависит от ключа С_К и от неключевого поля В. При нормализации формируется дополнительная таблица, содержащая поле В_K (как первичный ключ) и поле  А. Поле А удаляется из первоначальной таблицы и в ней останутся поля С_К и В.

 С_К, А, В è С_К, В + В_К , А

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

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

Определим зависимости в отношении Деканат.

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

Т а б л и ц а 7 – Отношение Деканат

ФИО Студента	Дисцип­лина	Форма отчетности	Оценка	Дата	Кол. часов	ФИО преподавателя	Поток
К1	К2
Составной ключ, PK

Каждое из полей Форма отчетности, Количество ча­сов, ФИО преподавателя, Поток — функционально однозначно зависит только от части составного первичного ключа поля Дисциплина, но не от поля ФИО студента.

Проведем декомпозицию таблицы Деканат согласно разобранному выше варианту 1. Выделим поля Дисциплина, Форма отчетности, Кол. часов, ФИО преподавателя и Поток в отдельное отношение Учебный план см. таблицу 8. В ней поле Дисциплина, указано как первичный ключ.

Т а б л и ц а 8 - Отношение Учебный план

Дисциплина	Форма отчетности	Кол часов	ФИО преподавателя	Поток
РК

Из исходного отношения Деканат при этом удаляются поля Форма отчетности, Количество часов, Преподаватель и Поток. Оставшуюся часть отношения,  назовем  отношением Результаты сессии (см. таблицу 9).

Т а б л и ц а 9 - Отношение Результаты сессии

ФИО студента	Дисциплина	Оценка	Дата
К1	К2
Составной первичный ключ, PK

 

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

Т а б л и ц а 10 – Отношение Учебный план

Код дисциплины	Дисциплина	Форма отчетности	Кол часов	Поток студентов	Код преподавателя
1	ТОЭ лек	Экзамен	20	БЭ-09-1,2,3,4	Тайцев Л.К.
2	ТОЭ лаб.	Рубежная	26	БЭ-09-2	Теткова Л.Б.
3	ТОЭ лаб.	Рубежная	26	БЭ-09-1	Теткова Л.Б.

Теперь оказывается поля Форма отчетности и Кол. часов зависят не только от ключевого поля, но и от неключевого поля Дисциплина. Выполняем декомпозицию таблицы по варианту 2 и получаем две новые таблицы.

Т а б л и ц а 11 – Отношение Учебный план

Код дисциплины (РК)	Дисциплина	Поток студентов	Преподаватель
1	ТОЭ лек.	БЭ-09-1,2,3,4	Тайцев Л.К.
2	ТОЭ лаб.	БЭ-09-2	Теткова Л.Б.
3	ТОЭ лаб.	БЭ-09-1	Теткова Л.Б.

Т а б л и ц а 12 – Отношение Дисциплины

Дисциплина (РК)	Форма отчетности	Кол часов
ТОЭ лек.	Экзамен	32
ТОЭ лаб.	Рубеж	26
Физика лек.	Экзамен	32
Физика лаб.	Рубеж	26

 

В отношении Учебный план осталось избыточным поле Поток, в него одинаковые данные придется заносить для разных дисциплин несколько раз. Кроме того, поле поток теперь зависит от неключевого поля Дисциплина. Чтобы устранить этот недостаток, выделим поле в отдельную таблицу Потоки и добавим поле, необходимое для учета часов работы преподавателя на экзамене при проверке РГР и курсовых работ Кол. Студентов.

Т а б л и ц а 13 – Потоки (фрагмент)

Поток (РК)	Кол студентов
БЭ-09-1	21
БЭ-09-1,2,3,4	85

Такой декомпозицией мы преобразовали исходную таблицу к совокупности нормализованных таблиц. Все полученные таблицы приведены к НФБК. Схема данных полученной БД приведена на рисунке 5.

Рисунок 5 – Проект БД

4 Лекция 4. Улучшение проекта БД

 

По­лученный проект БД может быть улучшен путем создания дополнитель­ных таблиц Студенты,  Преподаватели. В них создадим ключевые поля Код студента и Код преподавателя из-за возможного наличия тезок.  В них можно ввести сервисные поля Группа, Пол, Телефон, Адрес, Кафедра, Должность и другие, зависящие только от первичного ключа.  В основные таблицы для их связи с новыми таблицами добавим вторичные ключевые поля Код преподавателя  и Код студента.

Т а б л и ц а 14 – Отношение Студенты

Т а б л и ц а 15 – Отношение Преподаватели

В таблице 16 для наглядности вместо кодов студентов и кодов преподавателей приведено их текстовое значение, что легко выполняется в СУБД Access при использовании метода подстановки и свойства Подпись.

Т а б л и ц а 16 – Отношение Результаты сессии

Код студента (РК)	Дисциплина (РК)	Оценка	Дата сдачи
Иванов И.	ТОЭ лек	9	09.03.2009
Иванов И.	ТОЭ прак	6	07.03.2009
Иванов И.	ТОЭ лаб	7	04.03.2009

Схема данных полученного проекта БД приведена на рисунке 6.

Рисунок 6

Обратим внимание на то, что в БД недостаточно сведений о кафедрах, на которых работают преподаватели. Необходимо иметь следующие сервисные сведения: ФИО заведующего, телефон и аудиторию кафедры. Просто добавить эти поля в таблицу Преподаватели невозможно,  так как они будут зависеть от неключевого поля ФИО преподавателя. Поэтому создадим новую таблицу Кафедры с указанными полями и первичным ключевым полем Кафедра, а в таблицу Преподаватели включим вторичный ключ Кафедра для связи таблиц.

Т а б л и ц а 17 – Отношение Кафедры

Кафедра (РК)	ФИО заведующего	Телефон каф.	Аудитория

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

Т а б л и ц а 18 – Отношение Группы

Группа (РК)	ФИО старосты	ФИО эдвайзера

В каждой таблице созданной базы данных есть первичный ключ (PK),  в таблице  Результаты сессии он составной и состоит из полей Код студента и Код дисциплины. Эти поля с другой стороны являются внешними ключами и служат для связи с таблицами Студенты и Учебный план.

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

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

Т а б л и ц а 19 – Отношение Результаты сессии

Имя поля	Код студента	Код дисциплины	Оценка	Дата сдачи
Надпись	Студент	Дисциплина
	Иванов И.	ТОЭ лек.	9	09.03.2009
	Иванов И.	ТОЭ лаб.	7	04.03.2009
	Иванов И.	Физика лек.	3	01.03.2009
	Петров П.	ТОЭ лек.	6	06.08.2009
	Петров П.	Физика лек.	7	06.08.2009

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

Схема связи этих таблиц с добавлением сервисных полей приведена  на рисунке 7.

Рисунок 7

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

Лекция 4.  Проектирование БД с помощью инфологической модели

Сущности и связи

Рассмотрим  еще один метод проектирования БД на основе модели «Сущность – Связь». Дадим определения:

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

Набор сущностей (entity set) - множество сущностей одного типа (обладающих одинаковыми свойствами). Примеры: все люди, студенты, предприятия, праздники и т.д.

Любая предметная область может быть представлена как множество сущностей, между которыми существует множество связей.

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

Рассмотрим, например, множество студентов. Каждого из них можно описать с помощью атрибутов (свойств): номер зачетки, ФИО, дата рождения, группа. Поэтому сущность Студент может иметь атрибуты Номер зачетки, ФИО студента, Дата рождения, Группа и другие.

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

Связь (relationship) - это ассоциация, установленная между несколькими сущностями. Примеры связей:

- поскольку каждый Студент учится  в какой-либо группе, между сущностями Студент и Группа существует связь "учится  в";

- так как один из студентов является старостой, то между сущностями Студент и Группа имеется вторая связь "руководит".

Графически на схемах сущности показываются в прямоугольниках, а связи в ромбах (см. рисунок 8).

Рисунок 8

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

Набор связей (relationship set) - это отношение между сущностями. Могут существовать следующие степени связей:

а)  Один к одному (обозначается 1:1). Это означает, что в такой связи сущности с одной ролью всегда соответствует не более одной сущности с другой ролью. Такова связь «Руководит» между сущностями «Студент» и «Группа». Действительно, в каждой группе может быть только один староста, который может руководить только одной группой;

б) Один ко многим (1:М). В данном случае сущности с одной ролью может соответствовать любое число сущностей с другой ролью. Такова связь Учится для сущностей Группа и Студент. В каждой группе может учиться некоторое число студентов, но студент может учиться только в одной группе. Связь такого типа представлена на рисунке 8;

в)  Много к одному (М:1). Эта связь аналогична связи 1:М. Так как один студент может изучать несколько предметов, то связь «Изучает» между сущностями Дисциплина - Студент будет иметь степень М:1;

г)  Многие ко многим (М:М). В этом случае каждая из сущностей может быть представлена любым количеством экземпляров другой сущности.

Этапы проектирования

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

Из рассмотренного  вытекают основные этапы, описанные ниже, на которые разбивается процесс проектирования базы данных информационной системы:

1. Концептуальное проектирование - сбор, анализ и редактирование требований к данным. Для этого осуществляются следующие мероприятия:

- обследование предметной области, изучение ее информационной структуры;

- выявление всех фрагментов, каждый из которых характеризуется пользовательским представлением, информационными объектами и связями между ними;

- моделирование и интеграция всех представлений.

2. Нормализация сущностей. Нормализация сущностей выполняется аналогично нормализации таблиц и рассмотренной ранее.

Создание проекта базы данных

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

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

- учет работы преподавателей в часах;

- получение сервисных данных о преподавателях и студентах.

Приведем этапы построения инфологической модели (ER-диаграммы) и реляционной схемы БД для решения этой задачи.

Естественно, что сущность Студент должна обязательно присутствовать, в ней должны быть аргументы, описывающие конкретного студента: Код студента (РК), ФИО студента, Пол, Телефон студента, и сервисные поля Группа, Староста и ФИО эдвайзера. Первичный ключ Код студента введен из-за возможного наличия студентов-тезок.

Анализ предметной области показал, что, исходя из современной практики проставления рубежных оценок и экзаменационных оценок, необходимо дисциплину представлять в виде нескольких независимых названий. Вместо ТОЭ следует писать ТОЭ лекции, ТОЭ практика, ТОЭ лабораторные.

Перечень сдаваемых дисциплин в сессии определяется сущностью Учебный план. В ней должен быть ключевой аргумент, описывающий конкретные дисциплины (ТОЭ лекции, ТОЭ лабораторные и ТОЭ практика и т.д.), форму отчетности (рубежный контроль или экзамен), количество часов, запланированных для обучения, ФИО преподавателя и сервисные аргументы преподавателя: телефон преподавателя, кафедра, телефон кафедры и другие. Для контроля объема работы  преподавателя в часах добавим аргументы Поток и Количество студентов. Количество студентов в потоке или в группе определяет количество часов, затраченных преподавателем на проверку РГР, курсовых работ, время на экзамены. Однако аргумент Дисциплина не может быть ключевым. Действительно, одну и ту же дисциплину в разных потоках, например, «ТОЭ лаб.» в потоке БЭ-09-1, БЭ-09-2 и БЭ-09-3 может вести один или несколько преподавателей. Следовательно, этот аргумент может повторяться, поэтому введем ключевой аргумент Код дисциплины.

Для контроля того, как учатся студенты, должна быть сущность, учитывающая, какую оценку получил каждый студент на экзамене или рубежном контроле по каждой  дисциплине и дату. ФИО преподавателя, принявшего экзамен, здесь указывать не требуется, он полностью определен ключевым свойством Дисциплина из сущности Учебный план. Назовем эту сущность Ведомость. В ней должны быть аргументы Код студента, Код дисциплины, выполняющие роль составного ключевого аргумента, и неключевые поля Оценка и Дата сдачи.

Между сущностями требуется найти связи. На схемах сущности показываются в прямоугольниках, а связи - в ромбиках. Все сущности и связи между ними показаны на рисунке 9.

Рисунок 9 – Первоначальная ER- диаграмма

Все сущности превратим в одноименные таблицы, а аргументы сущностей представим  одноименными полями (см. рисунок 10).

Чтобы БД не имела потенциальной противоречивости и избыточности (каждый факт должен отражаться один раз на своем месте), все таблицы должны находиться в первой, второй и третьей нормальных формах (1НФ, 2НФ и 3НФ).

Все созданные таблицы находятся в 1НФ, поскольку все данные в полях атомарны и их нельзя упростить.

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

Рисунок 10 – Представление сущностей  в виде отношений

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

Рассмотрим таблицу Студенты. В ней поля Староста и Эдвайзер функционально зависят от неключевого поля Группа, следовательно, таблица нуждается в нормализации. Устраняем эту зависимость согласно варианту 2 (см  рисунок 11).

Рисунок 11 – Результат декомпозиции таблицы Студенты

В таблице Учебный план (см. рисунок 12) аналогичная ситуация: поле Телефон кафедры зависит от неключевого поля Кафедра, поле Кол. студентов зависит от неключевого поля Поток, поля Телефон преподавателя и Должность зависят от неключевого поля ФИО преподавателя, поля Форма отчетности и Кол. часов зависят от поля Дисциплины. Поступая аналогично предыдущему, разбиваем таблицу Учебный план на пять проекций: Учебный план, Дисциплины, Потоки, Кафедры, Преподаватели, причем в таблицы Кафедры, Преподаватели, имеется возможность добавить сервисные поля, зависящие только от первичных ключей таблиц, что было и сделано. В таблицу Кафедры добавлены поля ФИО заведующего и Аудитория. В Таблицу Преподаватели добавлено поле Адрес и из-за возможного наличия тезок введено ключевое поле Код преподавателя. В таблице Учебный план останутся поля, показанные на рисунке 5.

Рисунок 12- Результат декомпозиции таблицы Учебный план

Рассмотрим таблицу Ведомость (см. рисунок 3). Поля  Оценка и Дата  зависят от составного ключа ФИО студента и Дисциплина.  Действительно, оценка ставится конкретному студенту по конкретной дисциплине в день, определенный учебным планом. ФИО преподавателя всегда можно вставить в ведомость при формировании запроса по результатам экзаменов. Следовательно, таблица Ведомость находится в 3НФ. Теперь все таблицы приведены к 3НФ. Схема полученной БД приведена на рисунке 13.

Созданная БД не является избыточной и потенциально противоречивой, поскольку каждый факт указан только один раз на своем месте. При указании типа связи между таблицами в MS Access мы имеем право установить все свойства связи: обеспечение целостности данных, каскадное обновление связанных полей и каскадное удаление связанных записей. Эти установки значительно ускоряют ввод данных и автоматически гарантируют целостность БД.

 

Рисунок 13 – Полная схема БД

 

 

Материал для самостоятельного изучения

Определение ти­пов данных

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

Например, если в поле предполагается хранить доли в процентах от общего количества, то следует задать числовой тип и указать диапазон возможных значений — от 0 до 100.

Далее, в каждой таблице должны быть выделены поля, кото­рые обязательно должны быть заполнены при создании каждой записи таблицы. Задание такого ограничения целостности не позво­лит, например, ввести в таблицу Студенты запись, в которой не указан номер группы. Если подобные ограничения целостности не будут заданы, в таблице могут появиться записи, которые не будут учтены при обработке данных. Это приведет к ошибкам при формировании запросов и отчетов, например, ведомости.

Следующий шаг — задание для полей значений по умолчанию. Значение по умолчанию впоследствии будет автома­тически вводиться в указанное поле для каждой записи табли­цы. Например, в поле Дата сдачи таблицы Сводная ведомость при заполнении очередной записи по умолчанию может автоматически заноситься текущая дата.

Пример задания типов данных и ограничений приведен в таблице 20.

Т а б л и ц а 20 - Сводная ведомость

Имя поля	Ключ	Тип данных	Ограничения
Код студента	РК	Счетчик	Значение сочетаний ключевых полей уникально, совпадения не допускаются. Значение не должно быть пустым
Код дисциплины		Числовой
Оценка		Числовой	Значение не должно быть пустым и должно быть в интервале от 0 до 9
Дата сдачи		Дата/время	Значение не должно быть пустым, по умолчанию текущая дата

Остальные таблицы рекомендуется разработать самостоятельно.

В программе MS Access указанные ограничения вносятся в свойства полей: Значения по умолчанию, Условия на значения, Обязательное поле, Пустые строки, Индексированное поле (ключ), Подстановка.

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

Ниже перечислены свойства полей в Access.

Имя служит для управления БД (переменная в программировании).

Подпись применяется при необходимости сделать наименование поля более наглядными. Подпись выводится на экран или бумагу вместо имени поля.

Значение по умолчанию применяется для автоматического вывода часто встречающихся данных, например, надписи или даты.

Условие на значение определяет диапазон допустимых значений поля, например, диапазон оценки от 0 до 9 баллов.

Обязательное поле – поле, в котором можно установить требование обязательного его заполнения.

Пустые строки – можно разрешить наличие пустой ячейки в поле таблицы или установить обязательное заполнения всех ячеек поля. Например, можно разрешить не заполнять поле Телефон и установить обязательное заполнение поля Группа.

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

Подстановка служит для заполнения поля данными из фиксированного списка или из одноименного поля главной таблицы.

Ниже приведены пример таблицы с указанием свойств полей.

Т а б л и ц а 21 – Таблица  Студенты со свойствами полей.

Имя поля	Код студента	ФИО студента	Группа	Пол	Стипендия
Подпись
Тип данных	Счетчик	Текстовый	Текстовый	Текстовый	Денежный
Значение по умолчанию			БЭ-09-
Условие на значение					>=0 And <=15000
Обязательное поле		Да	Да	Да	Нет
Пустые строки		Нет	Нет	Нет
Индексирован­ное поле (ключевое)	Да (совпадения не допускаются)	Нет	Нет	Нет	Нет
Подстановка				Муж, Жен из списка

Распределенные базы данных

Исторически первой (до появления ПК) была создана модель распределенного представления данных, которая реализовывалась на большой универсальной ЭВМ с подключенными к ней неинтеллектуальными терминалами (дисплеями). Управление данными и взаимодействие с пользователем при этом объединялись в одной программе, на дисплей передавалась только "картинка", сформированная на центральном компьютере.

Затем, с появлением персональных компьютеров (ПК) и локальных сетей, были реализованы новые модели доступа к удаленной базе данных. При этом один из компьютеров является файловым сервером, на компьютерах - клиентах выполняются приложения, в которых совмещены СУБД и прикладная программа. Такая архитектура имеет очевидные недостатки:  высокий сетевой трафик, поскольку всю базу данных приходится перемещать на каждый клиентский компьютер.

Архитектура клиент-сервер.

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

Итак, почти все способы организации взаимодействия пользователя с удаленной базой данных основаны на модели "клиент- сервер", т.е. предполагается, что каждое приложение обработки данных разбито, как минимум, на две части:

- клиента, который отвечает за организацию интерфейса для пользователя;

- сервера, который собственно хранит данные, обрабатывает запросы и посылает их результаты клиенту для отображения.

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

Такой подход по сравнению с файловым сервером ведет к уменьшению загрузки сети. Однако сетевой трафик остается достаточно высоким.

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

Рисунок 14 – Архитектура клиент - сервер

 

Язык SQL   

Пользователь, чтобы получить данные из БД, создает запрос с помощью языка SQL. Результаты запроса отражаются на компьютере-клиенте пользователя.

Язык SQL позволяет только манипулировать данными, поскольку в нем отсутствуют средства создания экранного интерфейса. Для создания этого интерфейса служат универсальные языки (C, C++, Pascal) или проблемно-ориентированные языки (xBase, Informix 4Gl, Progress). В исходный текст программы включаются операторы языка SQL, которые во время исполнения программы передаются серверу БД. Сервер собственно и производит манипулирование данными. Отношения, полученные в результате выполнения сервером SQL - запросов, возвращаются прикладной программе (клиенту). Дальнейшая работа клиента (отображение, корректировка записей) ведется прикладной программой клиента. Количество клиентов может доходить до 20 на один сервер. Схема взаимодействия клиентского приложения с сервером базы данных показана на рисунке 15.

Рисунок 15 – Архитектура Сервер - Клиент

Транзакции

Любая база данных пригодна к использованию только тогда, когда ее состояние соответствует состоянию предметной области. Такие состояния называют целостными. Очевидно, что при изменении аргументов БД должна переходить от одного целостного состояния к другому. Однако, в процессе обновления данных возможны ситуации, когда состояние целостности нарушается. Пример: в банковской системе производится перевод денежных средств с одного счета на другой. На языке SQL эта операция описывается последовательностью двух команд UPDATE. Но, после выполнения первой команды и до завершения второй команды, база данных не находится в целостном состоянии. Искомая сумма списана с первого счета, но еще не зачислена на второй. Если в этот момент в системе произойдет сбой, то целостное состояние БД будет безвозвратно утеряно, и возникнет проблема с деньгами.

Целостность БД может нарушаться даже во время обработки одной команды SQL. Пусть выполняется операция увеличения зарплаты всех сотрудников фирмы на 20%:

             UPDATE employers SET salary=salary*1.2

При этом СУБД последовательно обрабатывает все записи, подлежащие обновлению, т.е. существует время, когда часть записей содержит новые значения, а часть - старые. Если в этот момент в системе произойдет сбой, то целостное состояние БД будет безвозвратно утеряно.

Во избежание таких ситуаций в СУБД вводится понятие транзакции - атомарного действия над БД, переводящего ее из одного целостного состояния в другое целостное состояние. Другими словами, транзакция - это последовательность операций, которые должны быть или все выполнены, или все не выполнены (все или ничего).

Методом контроля выполнения транзакций является ведение журнала, в котором фиксируются все изменения, совершаемые транзакцией в БД. Если во время выполнения транзакции происходит сбой, транзакция откатывается - из журнала восстанавливается состояние БД, которое было до начала транзакции.

Литература

1. О. Голицына, Н. Максимов, И. Попов. Базы данных. -М.: “Форум”, 2005.

2. С. Робинсон. Microsoft Access 2000: учебный курс. - СПБ: Питер, 2001.

3. Н. Вирт. Алгоритмы и структуры данных. -М.: Мир, 1989.

4. Дейт К.Дж. Введение в системы баз данных. / Перевод с англ. 7-е издание. М.: Вильямс, 2001.

5. Ю. Шафрин.  Курс компьютерной технологии. Учебное пособие. -М.: АБФ, 1998.

6. MS Access. Меню справка - Примеры баз данных – Учебная база данных БОРЕЙ.

Содержание

1 Лекция 1. Основные понятия реляционной модели базы данных	1
2 Лекция 2. Этапы проектирования данных. Нормализация	7
3 Лекция 3. Процедура нормализации	10
4 Лекция 4. Улучшение проекта БД	13
Материал для самостоятельного изучения	21