4.3 Порядок выполнения работы

1. Построить концептуальную модель данных в синтаксисе Чена (в MS Word или MS Visio) для предметной области, исходная информация о которой задана в таблице 4.2.

2. Создайте новый файл для разработки новой модели данных на логическом и физическом уровне. Укажите при этом:

   • тип модели - Logical/Physical;

   • целевую СУБД - Database: Access.

3. Проанализируйте предметную область, представленную таблицей 4.2 и следующими БП:

1. Одну дисциплину может преподавать много преподавателей. Преподаватель может читать много дисциплин.

2. На кафедре работает много преподавателей, каждый преподаватель закреплен за одной кафедрой.

3. Преподаватель может иметь более одного номера телефона, некоторые преподаватели могут иметь одинаковый номер.

4. Оклад преподавателя определяется его должностью.

5. Студенческая группа состоит более чем из одного студента, каждый студент закреплен за одной группой.

6. Дисциплины, имеющие одинаковое название, но разное количество часов, считаются разными дисциплинами.

7. Студенту разрешено пересдавать экзамен по дисциплине, но только другому преподавателю, читающему такую же дисциплину.

Таблица 4.2 – Информация о предметной области Учебный процесс

Кафедра, каб.	№ студ, Ф.И.О. студента	Группа	Название дисциплины	Часы	Преподаватель	Оклад	Тел. преп.	Должность	Оценка	Дата	Тема дипл. работы
Кафедра Информа-тики, каб. 288	123456 Иванов И.И. 123457 Боков С.С. ……………	ПМ-06-1	Базы данных	102	Петров А.И.	3500	8-050-456-67-87 8-098-786-63-55 7021-34-56	проф.	5 5 …..	2.05.07 2.05.07	утвержд. не утв.
	133686 Аникин С.А. 133687 Орлова О.И. ………..	ПМ-06-2		82	Рожков П.Р.	3100	8-050-876-17-09	доцент	4 3 …..	6.05.07 6.05.07	не опред. не опред.
	123667 Иванов И.П. 123668 ВласоваС.С. ………….	ИНФ-04-3	Имит. моделирование	82	Петров А.И.	3500	8-067-499-17-11 7021-34-56	проф.	2 5 ……	3.05.07 6.05.07	утвержд. утвержд.
Кафедра ПМ, каб.27	………	…..	……….		……….				…..

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

• Кафедра (сильная сущность);

• Студент (сильная сущность);

• Дисциплина (сильная сущность);

• Преподаватель (сильная сущность);

• Экзамен (слабая сущность).

Создайте данные сущности концептуальной модели (логической модели в Erwin) (рис.4.12):

• проверьте, что выбран логический уровень: Logical;

• нажмите на панели инструментов кнопку Entity;

• нажмите правой кнопкой мышки на любом месте на диаграмме;

• введите название сущности.

Рисунок 4.12 – Создание сущностей предметной области Кафедра и связей между ними

4. Установите связи между сущностями (табл.4.3, рис.4.12):

   • выберите на панели инструментов кнопку, отвечающую нужному типу связи;

   • нажмите левой клавишей мышки на сущности предке, а затем на сущности потомке;

   • кликните дважды левой кнопкой мышки на связи;

   • в закладке General введите имя связи (фразу, характеризующую связь) в поле Verb Phrase;

   • установите кардинальность связи;

Таблица 4.3 – Связи между сущностями и их свойства

Сущность -предок	Сущность - потомок	Название связи Verb Phrase Parent-to-Child	Тип связи (инетиф/неидентиф)	Кардинальность (мощность)	Пояснение
Кафедра	Преподаватель	Состоит из	неидентифицирующая обязательная (Nulls Allowed)	1 или много	экземпляр Кафедра связан с 1 или более экземплярами Преподаватель
Преподаватель	Экзамен	Принимает	идентифицирующая	0,1 или много	экземпляр Преподаватель связан с 0, 1 или более экземплярами Экзамен
Дисциплина	Экзамен	Сдается	идентифицирующая	0,1 или много	экземпляр Дисциплина связан с 0, 1 или более экземплярами Экзамен
Студент	Экзамен	Сдает	идентифицирующая	0,1 или много	экземпляр Студент связан с 0, 1 или более экземплярами Экзамен

5. Выведите название связей на диаграмму (из контекстного меню диаграммы выберите пункт Relationship Display/Verb Phrase) (рис.4.13)

Рисунок 4.13 – Отображение название связей

6. Проанализируйте модель на достаточность всех связей.

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

Для решения этой проблемы необходимо ввести следующую связь (табл.4.4, рис.4.14):

Таблица 4.4 – Свойства связи между сущностями Преподаватель Дисциплина

Сущность -предок	Сущность - потомок	Название связи Verb Phrase Parent-to-Child	Название связи Verb Phrase Child-to- Parent	Тип связи (инетиф/неидентиф)	Пояснение
Преподаватель	Дисциплина	Преподает	Преподается	Многие-ко-многим	экземпляр Преподаватель связан с 0, 1 или более экземплярами Дисциплина; экземпляр Дисциплина связан с 0, 1 или более экземплярами Преподаватель;

Рисунок 4.14 – Сущности и связи между ними

7. Введите атрибуты сущностей и установите первичные ключи (табл.4.5, рис.4.15, 4.16):

   • из контекстного меню сущности выбрать Attributes …;

   • нажмите кнопку New ;

   • в появившемся диалоговом окне определите домен (тип) атрибута;

   • в поле Attribute Name наберите имя атрибута концептуальной модели (логической в ERwin) (табл.4.5, рис.4.15);

   • в поле Column Name имя столбца таблицы при реализации модели для выбранной СУБД (физической в ERwin) (табл.4.5, рис.4.15);

   • выберите тип атрибута;

   • нажмите кнопку ОК;

   • если атрибут является идентифицирующим, то укажите для него метку Primary Key;

   • повторите шаги 4 – 8 для каждого атрибута.

ВНИМАНИЕ: Вводить необходимо только собственные атрибуты сущности. Внешние ключи добавляются автоматически при создании связей;

• по завершению ввода атрибутов закройте окно редактора атрибутов.

Повторите все шаги для каждой сущности.

а

б

Рисунок 4.15 – Ввод атрибута

Таблица 4.5 –Атрибуты логической и физической моделей Erwin (внешние ключи не вводятся, они формируются автоматически, если уже установлена связь или в процессе установления связей)

Рисунок 4.16 – Концептуальная модель данных предметной области Кафедра

На этом концептуальную модель можно считать законченной (этап по специализации/генерализации типов сущности пока не рассматривается).

Однако она не соответствует реляционной модели данных:

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

Действия:

- создайте новую сущность Группа (рис.4.17);

- установите неидентифицирующую связь между сущностями (родительская сущность: Группа);

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

ВНИМАНИЕ: внешний ключ на сущность Группа возникнет автоматически при установке связей между сущностями.

Рисунок 4.17 –

Аналогичные рассуждения относительно сущности Преподаватель и ее атрибута Должность. Откорректируем это позднее при нормализации.

б) присутствует связь многие-ко-многим;

в) атрибут Телефон сущности Преподаватель является множественным атрибутом (не атомарным) и, следовательно, данная сущность не отвечает требованиям первой нормальной формы;

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

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

8. Преобразование связей типа M:N

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

В Erwin 7 данное преобразование может выполнять автоматически при переходе к физической модели Erwin. Для этого при создании данной связи должен быть установлен режим Many-to-Many Relationships with Association Tables на странице General в Model/Model Properties.

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

Для преобразования связи «многие-ко-многим» сделайте связь активной и в контекстном меню выберите Create Association Entity, далее отвечайте на вопросы мастера (рис 4.18) и получите преобразованную модель. Измените название возникшей слабой сущности на Учебный план (рис.4.19).

Рисунок 4.18 – Мастер преобразования связи типа «многие-ко-многим»

Произведенное мастером преобразование сохраняется (рис. 4.20) и по контекстному меню данного преобразования в браузере можно переводить связь в исходное состояние (возвращать к типу «многие ко многим»). Для этого выберите Show Source Object.

Рисунок 4.19 – Результат преобразования связи типа «многие-ко-многим»

Рисунок 4.20 – Браузер для просмотра объектов ERwin

9. Проверьте избыточность связей

Схема, приведенная на рисунке (рис. 4.18) содержит избыточность данных:

- ассоциативная сущность Учебный план содержит информацию о том, какой преподаватель читает какую дисциплину;

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

В связи с этим необходимо внести следующие изменения (рис.4.21):

- удалить избыточные связи между сущностями Преподаватель и Экзамен, и между сущностями Дисциплина Экзамен;

- ввести новую идентифицируемую связь между сущностью УченыйПлан и сущностью Экзамен.

Рисунок 4.21 –

10. Проведите нормализацию данных

10.1 Проверьте сущности на соответствие требованиям первой нормальной формы

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

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

Для приведения сущности Преподаватель к первой нормальной форме следует:

• создать новую сущность Телефон;

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

• перенести в область первичных ключей сущности Телефон «множественный атрибут» Телефон;

Теперь диаграмма содержит сущности, находящиеся в первой нормальной форме, и имеет вид, показанный на рисунке 4.22.

Рисунок 4.22 –

10.2 Проверьте модель на соответствие второй нормальной форме.

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

Сущность Экзамены.

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

Сущность Телефон вообще не имеет неключевых атрибутов.

Вывод: все сущности находятся во второй нормальной форме.

10.3. Проверьте модель на соответствие третьей нормальной форме.

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

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

Для приведения сущности Преподаватель к третьей нормальной форме необходимо:

- создать новую сущность Должность и перенести в нее атрибуты Должность (в качестве первичного ключа) и Оклад;

- установить неидентифицирующую связь от новой сущности Должность к старой сущности Преподаватель (рис.4.23).

Теперь все сущности находятся в третьей нормальной форме.

Рисунок 4.23 –

11. Сформируйте физическую модель Erwin для СУБД Access.

Создайте пустую базу данных в СУБД Access. Закройте созданную базу данных.

Перед переключением в физическую модель проверьте, что режим Many-to-Many Relationships with Association Tables на странице General в Model/Model Properties выключен.

В ERwin переключитесь на физическую модель (рис.4.8). Замените названия таблиц названиями на английском языке (рис.4.24).

Укажите целевую СУБД Access Database/Choose Database (4.9). Физическая модель ERwin для СУБД Access предметной области «Учебный процесс» будет иметь вид, показанный на рисунке 4.24.

Рисунок 4.24

Установите параметры соединения с заранее созданной СУБД Access (рис.4.11) и нажмите Connect.

Сгенерируйте скрипт создания структуры базы данных и запустите его на выполнение (Tool/Forward Engineer/Schema Generation… и нажмите Generate) (рис.4.10).

Откройте базу данных, изучите созданные в ней объекты (рис.4.25), ознакомьтесь со схемой данных (4.26).

Рисунок 4.25

Рисунок 4.26

11. Задание для самостоятельной работы:

• постройте самостоятельно концептуальную модель в синтаксисе Чена, с помощью Erwin, приведите ее к третьей нормальной форме для предметной области, приведенной в таблице 4.6;

• сформируйте физическую модель ERwin и сгенерируйте структуру базы данных в СУБД Access.

Информация о предметной области «Агентство недвижимости» содержится в таблице 4.6 и БП.

БП (вариант А):

1. При повторной продаже объект недвижимости регистрируется заново, т.е. имеет новый номер.

2. Объект недвижимости принадлежит только одному владельцу-продавцу.

3. Одному владельцу могут принадлежать много объектов.

4. В сделке может быть только один покупатель.

5. Один покупатель может совершать много сделок.

БП (вариант Б):

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

2. В один момент времени Объект недвижимости принадлежит только одному владельцу-продавцу, т.е. Объект недвижимости при повторной продаже имеет уже другого владельца.

3. Одному владельцу могут принадлежать много объектов.

4. В сделке может быть только один покупатель.

5. Один покупатель может совершать много сделок.

Таблица 4.6– Фрагмент документа «Агентство недвижимости»

Ф.И.О. покупателя	Адрес покупателя	Адрес объекта недвижимости	Вид фонда	Общая прощадь (кв. м.)	Стоимость (долл.)	Дата продажи	Ф.И.О. продавца	Адрес продавца
Васильев Р.А.		пер. Светлый 54	жилой	67	37000	10.05.2003	Иванов А.В.	ул. Пушкинская 34.
Головко К.П.	ул. Пушкинская 63	пер. Светлый 50	нежилой	129	19000	10.05.2003
		пр. Свободы 10	нежилой	325	60000	15.05.2003	Петров П.С.	ул. Гоголя 3.
………	…..	……..	…….	……..	…….	……..	………	……………