Приложение 6 пример проектирования базы данных
Условие задания: Решается задача о выполнении сроков проведения строительно-ремонтных работ на объекте.
База данных должна содержать следующие сведения: код строительно-ремонтной организации; наименование организации; код объекта; наименование объекта; адрес объекта; код работы; наименование работы; норматив на выполнение работы; фактические сроки начала и окончания работы на объекте.
Учесть, что строительная организация может вести работы на нескольких объектах, и на одном строительном объекте работы ведет несколько строительных организаций.
Построение информационно-логической модели.
Построение информационно-логической модели будем выполнять методом нормализации данных. Так как должна решаться задача о выполнении сроков проведения строительно-ремонтных работ на объекте, то первоначально выделим один объект – Работа. Все остальные сведения определим атрибутами этого объекта. Схема отношения Работа будет имеет вид:
Работа (код работы; код строительно-ремонтной организации; наименование организации; код объекта; наименование объекта; адрес объекта; наименование работы; норматив на выполнение работы; фактические сроки начала и окончания работы на объекте).
Проанализируем соответствует ли это отношение правилам 1NF, 2NF и 3NF нормальным формам.
1NF: Все атрибуты отношения Работа атомарны (неделимы с точки зрения их обработки), следовательно, оно находится в 1NF.
Правило 2NF: Отношение находится во второй нормальной форме (2NF) в том и только в том случае, когда находится в 1NF, и каждый не ключевой атрибут функционально полно зависит от первичного ключа.
Определяем ключ отношения Работа. – Каждая работа должна иметь уникальный код работы, следовательно, атрибут код работы выбирает первичным ключом.
Проверяем, находятся ли все не ключевые атрибуты в функциональной зависимости от первичного ключа.
Коду работы соответствует только одно наименование работы, поэтому наименование работы находится в функциональной зависимости от ключа.
В функциональной зависимости от код работы находится и атрибут норматив на выполнение работы.
Атрибут код строительно-ремонтной организации не находится в функциональной зависимости от ключа, так как одному значению код работы может соответствовать несколько значений атрибута код строительно-ремонтной организации (одна и та же работа может выполняться разными строительными организациями).
В функциональной зависимости от код работы не находятся также: наименование организации; код объекта; наименование объекта; адрес объекта; фактические сроки начала и окончания работы на объекте. Все эти атрибуты не являются характеристиками Работы.
Тогда схема отношения Работа примет вид:
Работа (Код работы; наименование работы; норматив на выполнение работы)
Отношение Работа находиться во 2NF.
Все прочие атрибуты определим в схему отношения Объект.
Определяем первичным ключом в отношении Объект - код объекта.
Объект (код объекта; код строительно-ремонтной организации; наименование организации; наименование объекта; адрес объекта; фактические сроки начала и окончания работы на объекте).
В функциональной зависимости от ключа код объекта находятся наименование объекта и адрес объекта.
Убираем остальные атрибуты и получаем схему отношения:
Объект (код объекта; наименование объекта; адрес объекта). Оно находится во 2NF.
Остались атрибуты: код строительно-ремонтной организации; наименование организации; фактические сроки начала и окончания работы на объекте.
Рассуждая аналогичным образом, выделяем объект:
Организация (Код строительно-ремонтной организации; наименование организации)
Отношение Огранизация находится во 2NF.
Рассмотрим, от каких атрибутов зависят атрибуты фактические сроки начала и окончания работы на объекте.
Код работы <-> фактическая дата начала работы на объекте строительной организацией
Код объекта <-> фактическая дата начала работы на объекте строительной организацией
Код работы <-> дата окончания работы на объекте строительной организацией
Код объекта <-> дата окончания на объекте строительной организацией
Рассмотрим числовой пример с данными о выполнении строительными организациями работ на объектах. (таблица 2).
Пусть коды работ имеют следующие значения: 11, 12, 13; код объекта имеет значения -1,2,3 и код организации - 90, 91.
По условию задания одна и та же работа может выполняться на нескольких объектах, на одном и том же объекте может выполняться несколько работ, и работы на одном объекте могут выполнять разные организации. В таблице 2 приведены данные соответствующие требованиям условия.
Таблица 2
|
Код работы |
Код объекта |
Код организации |
дата начала работы |
дата окончания работы |
|
11 |
1 |
90 |
28.10.14 |
28.11.14 |
|
13 |
1 |
90 |
28.10.14 |
2.11.14 |
|
13 |
2 |
90 |
4.11.14 |
4.12.14 |
|
12 |
2 |
91 |
4.11.14 |
20.11.14 |
|
11 |
1 |
91 |
28.10.14 |
20.11.14 |
|
|
|
|
|
|
Работа 13 выполняется на нескольких объектах. На объекте 1 выполняется несколько работ. На объекте 1 работу с кодом 11 выполняют разные организации.
В качестве первичного ключа следует выбрать атрибуты, суммарное значение которых должно быть уникальным в каждой строке. Это: Код работ, Код Объекта, Код организации.
Схема последнего отношения: (Код работ, Код Объекта, Код организации, фактические сроки начала и окончания работы на объекте). Оно находиться во 2NF.
Присвоим этому отношению имя: Выполняемая работа.
Таким образом, база данных должна хранить сведения о четырех объектах. На рис. 8 представлена ER-модель, в которой определены связи между объектами.
Рис. 8. ER-модель
Модель состоит из четырех объектов, для которых определены три связи один-ко-многим:
Работа – Выполняемая работа по Коду работы;
Объект - Выполняемая работа по Коду объекта;
Организация - Выполняемая работа по Коду организации.
Для обозначения атрибутов, являющихся внешними ключами, используется текст FK.
Построение даталогической модели базы данных.
Необходимо описать логическую структуру каждого объекта в соответствии с построенной ER-моделью. Для каждого атрибута информационного объекта необходимо определить следующие параметры: идентификатор, тип данных, размер для текстовых полей. Определить первичный и внешние ключи.
Логические структуры сформированных информационных объектов, предполагаемые к реализации в базе данных, приведены в табл.3-.6.
Таблица 3
Логическая структура информационного объекта Работа
|
Поле |
Признак ключа |
Формат поля | |||||
|
Имя |
Наименование |
Тип |
Размер |
Точность | |||
|
КодРаб |
Код выполняемой работы |
PK |
Числовой |
целое |
- | ||
|
НаимРаботы |
Наименование выполняемой работы |
|
Текстовый |
50 |
- | ||
|
Норматив |
Норматив на выполнение работы |
|
Числовой |
целое |
- | ||
Таблица 4
Логическая структура информационного объекта Объект
|
Поле |
Признак ключа |
Формат поля | |||||
|
Имя |
Наименование |
Тип |
Размер |
Точность | |||
|
КодОб |
Код объекта |
PK |
Числовой |
целое |
- | ||
|
НаимОб |
Наименование объекта |
|
Текстовый |
60 |
- | ||
|
АдресОб |
Адрес объекта |
|
Текстовый |
80 |
- | ||
Таблица 5
Логическая структура информационного объекта Организация
|
Поле |
Признак ключа |
Формат поля | |||||
|
Имя |
Наименование |
Тип |
Размер |
Точность | |||
|
КодОр |
Код ремонтно-строительной организации |
PK |
Числовой |
целое |
- | ||
|
НаимОр |
Наименование организации |
|
Текстовый |
60 |
- | ||
Таблица 6
Логическая структура информационного объекта Выполняемая работа
|
Поле |
Признак ключа |
Формат поля | |||||
|
Имя |
Наименование |
Тип |
Размер |
Точность | |||
|
КодРаб |
Код выполняемой работы |
РК |
Числовой |
целое |
- | ||
|
КодОб |
Код объекта |
Числовой |
целое |
- | |||
|
КодОр |
Код ремонтно-строительной организации |
Числовой |
целое |
| |||
|
ДатаНР |
Фактическая Дата начала выполнения работы |
|
Дата |
|
| ||
|
ДатаОР |
Фактическая дата окончания работы |
|
Дата |
|
| ||
После определения логической структуры информационных объектов требуется определить должна ли поддерживаться ссылочная целостность для связей типа «один ко многим» и какие правила изменения и удаления связанных данных в этом случае установить.
Исходя из логики взаимодействия информационных объектов, для каждой связи следует задать опции:
обеспечение ссылочной целостности данных;
правило удаления связанных данных;
правила обновления связанных данных.
В базе данных рассматриваемого примера должны поддерживаться следующие требования:
Для связи Работа-Выполняемая работа:
- необходимо установить обеспечение ссылочной целостности. Это обеспечит следующий контроль вводимых данных: в таблицу Выполняемая работа можно будет вводить данные только о той работе, код которой уже содержится в значениях столбца код работы таблицы Работа;
- запретить удаление из таблицы Работа той работы, для которой есть строки в таблице Выполняемая работа (запретить удаление связанных данных).
- при изменении значения столбца код работы в таблице Работа разрешить выполнять изменение этого же значения во всех строках таблицы Выполняемая работа (т.е. разрешить каскадное изменение связанных данных).
Для связи Объект-Выполняемая работа:
- необходимо установить обеспечение ссылочной целостности, это запретит вводить в таблицу Выполняемая работа сведения о том объекте, код которого отсутствует в столбце код объекта таблицы Объект;
- запретить удаление из таблицы Объект сведений о объекте, код которого уже содержатся в строках таблицы Выполняемая работа;
- запретить изменение кода объекта в таблице Объект, если старое значение кода объекта уже содержатся в строках таблицы Выполняемая работа.
Для связи Организация-Выполняемая работа:
- необходимо установить обеспечение ссылочной целостности, это запретит вводить в таблицу Выполняемая работа сведения о тое организации, код которой отсутствует в строках таблицы Организация;
- запретить удаление из таблицы Организация сведений об организации, код которой уже содержатся в строках таблицы Выполняемая работа;
- запретить изменение кода организации в таблице Организация, если старое значение кода организации уже содержатся в строках таблицы Выполняемая работа.
Разработка запросов на создание таблиц.
Исходными данными для разработки являются ER-модель и даталогическая модель.
В ER-модели объект Выполняемая работа является зависимым от всех остальных объектов, поэтому необходимо в первую очередь разрабатывать запросы на создание таблиц для независимых объектов. Ниже приведены запросы на создание независимых таблиц.
Запрос на создание таблицы Работа:
create table Работа
(КодРаб integer Primary key,
НаимРаб text(50),
Норматив integer) ;
Запрос на создание таблицы Объект:
create table Объект
(КодОб integer Primary key,
НаимОб text(60),
АдресОб text(80)) ;
Запрос на создание таблицы Организация:
create table Организация
(КодОр integer Primary key,
НаимОр text(60) ) ;
В запросе на создание таблицы Выполняемая работа теперь после описания полей можно описать ограничения по первичному ключу (потому что он составной) и внешним ключам, так как уже описаны те таблицы, на которые ведут ссылки внешнего ключа.
Запрос на создание таблицы Выполняемая работа:
create table ВыполняемаяРабота
(КодРаб integer,
КодОб integer ,
КодОр integer ,
ДатаНР datetime,
ДатаОР Datetime,
Primary key (КодРаб, КодОб, КодОр),
foreign key (КодРаб)
REFERENCES Работа(КодРаб),
foreign key (КодОб)
REFERENCES Объект(КодОб),
foreign key (КодОр)
REFERENCES Организация(КодОр) ) ;
Создание таблиц в среде СУБД Microsoft Access 2010.
Создать пустую базу данных средствами СУБД Microsoft Access 2010. Для этого:
4.1. После запуска Microsoft Access на вкладке Файл в окне Новая база данных указать имя файла Контрольная работа_ФамилияСтудента (по умолчанию файл будет создан в папке Мои документы). Нажать кнопку Создать.
4.2. Выбрать вкладке Создание команду Конструктор запросов.
4.3. Закрыть открывшееся окно Добавление таблицы.
4.4. Находясь в окне Запрос1, выбрать на ленте Работа с запросами\Конструктор из группы Результаты - Режим SQL(см. рис.9).
Рис.9. Состояние окна для перехода в редактор SQL
Набрать в окне редактора текст запроса на создание таблицы Работа.
Сохранить текст запроса с именем Создание таблицы Работа.
Выполнить запрос (команда Выполнить на ленте).
Повторить действия пунктов 4.5-4.7 для таблиц Объект, Организация, Выполняемая работа.
Открыть вкладку Работа с базами данных и выбрать Схема данных. При правильном проектировании автоматически должна быть получена схема, представленная на рис. 10.
Рис.10. Схема созданной БД.
Создать контрольный пример и заполнить таблицы базы данных.
Контрольный пример должен содержать данные для каждой таблицы с учетом тех ограничений, которые были определены в даталогической модели при описании полей таблиц и для каждой связи. Контрольный пример рассматриваемого варианта приведен на рис. 11.
Первоначально следует ввести данные в независимые таблицы. В нашем примере это таблицы Работа, Объект и Организация. И только после заполнения данными этих таблиц необходимо заполнить данными таблицу Выполняемая работа.
Ввод данных в таблицу можно выполнять в режиме таблицы. Для активизации этого режима в области перехода дважды кликнете мышью по имени таблицы (или предварительно выбрав имя таблицы выполните из контекстного меню команду Открыть).
Рис. 11. Контрольный пример.
Успешное выполнение правильно составленного контрольного примера говорит о том, что база данных была спроектирована правильно (в соответствии с заданием) и может эксплуатироваться.