2.2.2 Er-диаграмма физического уровня. Ограничения доменов. Ограничения ссылочной целостности. Переопределение триггеров. Индексирование отношений.
ER-диаграмма физического уровня представлена на рисунке 33 и в приложении 3.
Рисунок 33 - ER-диаграмма физического уровня
Физическая модель данных зависит от конкретной СУБД. В ней содержится информация обо всех объектах БД. Одной и той же логической модели может соответствовать несколько разных физических. В физической модели важно описать всю информацию о конкретных физических объектах – таблицах, колонках, индексах, процедурах.
Проверим, удовлетворяют ли все имеющиеся отношения соответствующим наборам ограничений.
Первая нормальная форма требует, чтобы значения всех атрибутов отношения были атомарными. При рассмотрении информационной модели было отмечено, что значения атрибутов всех отношений логически разделить на элементы нельзя и, следовательно, они удовлетворяют условию первой нормальной формы. Пример, рассмотрим таблицу «Сотрудники». Ключевой аттрубут в ней – «Табельный номер» не может быть разделен на элементы. Не ключевые аттрубуты – «Фамилия», «Имя», «Отчество», «Должность», «Образование», «Пол», «Телефон», «Номер отдела» также являются атомарными.
Вторая нормальная форма требует, чтобы отношение находилось в первой нормальной форме, и каждый не ключевой атрибут функционально полно зависел от первичного ключа. И это требование также выполняется в рассматриваемой модели. Пример, рассмотрим таблицу «Сотрудники». Ключевой аттрубут в ней – «Табельный номер». Не ключевые аттрубуты – «Фамилия», «Имя», «Отчество», «Должность», «Образование», «Пол», «Телефон», «Номер отдела» зависят функционально полно только от первичного ключа.
Для нормализации схем отношений к третьей нормальной форме необходимо чтобы каждый детерминант (любой атрибут, от которого функционально полно зависит некоторый другой атрибут) является возможным ключом. В рассматриваемой модели это условие соблюдается. Пример, рассмотрим таблицу «Сотрудники». Как было отмечено выше, все неключевые атрибуты функционально полно зависят от первичного ключа, т.е. первичный ключ является детерминантом.
На физическом уровне ER-диаграммы указываем уточненные типы данных для атрибутов, в частности уточняем длины текстовых полей. Также имеется возможность внести дополнительные ограничения ссылочной целостности, создать триггеры, индексы.
Ссылочная целостность базы данных требует обеспечить, чтобы значение внешнего ключа экземпляра дочерней сущности соответствовало значениям первичного ключа в родительской сущности. Ссылочная целостность должна контролироваться при всех операциях, изменяющих данные в базе данных.
Реализация ссылочной целостности:
При изменении информации о каком-либо отделе из таблицы «Отделы» в таблицах «Сотрудники», удалять записи из таблицы «Отделы» запрешено;
При изменении информации о каком-либо сотруднике из таблицы «Сотрудники» в таблице «Зарплата», «Должники», «Заявки» информация будет автоматически изменяться (каскадное обновление);
При изменении информации о каком-либо квартиросъемщике из таблицы «Квартиросъемщике» в таблице «Данные со счетчиков», «Квартиры», «Заявки» информация будет автоматически изменяться (каскадное обновление);
При изменении информации о каком-либо доме из таблицы «Дома» в таблице «Договор», «Квартиры» информация будет автоматически изменяться (каскадное обновление);
В таблице «Должники» разрешается обновление и удаление записей.
При изменении информации о какой-либо услуге из таблицы «Услуги» в таблице «Договор» информация будет автоматически изменяться (каскадное обновление);
В таблице «Зарплата» разрешается обновление записей. Удаление записей не допускается, т.к. данные этой таблицы используются для бухгалтерских учетов.
В таблице «Договор» при изменении информации о договоре происходит каскадное обновление данных. Разрешается удаление информации только в случае, когда на данную информацию нет ссылок в других связанных таблицах (это означает, что договор не должен содержать услугу).
В таблице «Данные со счетчиков» разрешается обновление и удаление записей.
В таблице «Квартиры» разрешается обновление записей. Разрешается удаление информации только в случае, когда на данную информацию нет ссылок в других связанных таблицах (это означает, что дома не должен содержать ссылку на квартиры).
В таблице «Заявки» разрешается обновление записей. Удаление записей не допускается, т.к. данные этой таблицы используются для создания отчетов.
Индекс представляет собой дополнение к таблице, помогающее ускорить поиск необходимых данных за счет физического или логического их упорядочивания. Индекс является набором ссылок, упорядоченным по определенной колонке таблицы, которая называется индексируемой колонкой. Физически индекс представляет собой всего лишь упорядоченный набор значений из индексированной колонки с указателями на месте физического размещения исходных строк в структуре базы данных. В индексе хранится не информация обо всей строке данных, а лишь ссылка на нее. Когда пользователь выполняет запрос, обращающийся к индексированной колонке, сервер автоматически использует индекс для поиска требуемых значений.
Принципиальным отличием кластерного индекса (Clustered Index) от индексов других типов является то, что при его определении в таблице физическое расположение данных перестраивается в соответствии со структурой индекса. Информация об индексе и сами данные физически располагаются вместе. Некластерные индексы являются наиболее типичными представителями семейства индексов. В отличие от кластерных, они не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.
Разрешено удаление в таблицах «Сотрудники», «Зарплата», «Заказы», «Отделы», «Должники», «Квартиросъемщики», «Заявки», «Дома на обслуживании», «Квартиры», «Данные со счетчиков» , «Договор» , «Услуги»
В разработанной информационной системе есть домен Min который ограничивает ввод отрицательных чисел.
Для приложения были разработаны следующие триггеры:
proverka _date - Триггер, который будет запрещать добавления записи в таблицу «Договор», если дата начала договора старше текущей даты.
Delete_doljnik – Триггер, который будет разрешать удаление данных из таблицы «Должники» при наличии прав администратора.
Delete_zarplata - Триггер, который будет запрещать удаление записей в таблице «Зарплата», если дата выдачи меньше трех месяцев.
Proverka_date_zarplata - Триггер, который будет срабатывать при изменении данных таблицы «Зарплата» и проверять дату выдачи зарплаты.
Insert_zayavka - Триггер, который будет проверять дату подачи заявки при добавлении записей в таблицу «Заявки».
Update_zayavki - Триггер, который будет записывать информацию о заявках в талицу Updated_zzayavki при обновлении записей в таблице «Заявки».
Zayavki_del - Триггер, который будет запрещать удаление записей из таблицы «Заявки», при наличии статуса «В процессе».
Zapret_deleted_otdel - Триггер, который будет запрещать удаление записи из таблицы «Отделы» при наличии в удаляемом отделе сотрудников.
Deleted_sotrdnik - Триггер, который будет записывать информацию о удаляемых записях в таблице «Сотрудники».
Proverka_otdela - Триггер, который будет проврять существование отдела упри добавлении записи в таблицу «Сотруники».
Proverka_sovershennolet - Триггер, который будет проверять возраст нового сотрудника.
Для приложения были разработаны следующие индексы, для ускорения сравнения введенного значения с существующими:
CREATE UNIQUE NONCLUSTERED INDEX index1 ON [Дома на обслуживании] ([Код дома])
В таблице Дома на обслуживании был создан уникальный, некластеризованный индекс по полю Код дома, так как это поле используется в качестве критерия поиска в хранимой процедуре Вывод данных о договорах определенного дома
CREATE NONCLUSTERED INDEX index2 ON [Услуги] ([Стоимость услуги])
В таблице Услуги был создан неуникальный, некластеризованный индекс по полю Стоимость услуги , так как это поле используется в качестве критерия поиска в представлении Информация о услугах со стоимостью выше средней, а также в хранимых процедурах Самая дешевая услуга и Услуги, цена которой выше заданной.
CREATE NONCLUSTERED INDEX index3 ON [Сотрудники] ([Фамилия])
В таблице Сотрудники был создан неуникальный, некластеризованный индекс по полю Фамилия , так как это поле используется в качестве критерия поиска в представлении Выдать информацию о сотруднике по фамилии
CREATE UNIQUE CLUSTERED INDEX index4 ON [Отделы] ([Название отдела])
В таблице Отделы был создан уникальный, кластеризованный индекс по полю Название отдела , так как это поле используется в качестве критерия поиска в хранимой процедуре Поиск сотрудников по отделу.
CREATE NONCLUSTERED INDEX index5 ON [Сотрудники] ([Образование])
В таблице Сотрудники был создан неуникальный, некластеризованный индекс по полю Образование , так как это поле используется в качестве критерия поиска в представлении Вывести информацию о сотрудниках с высшим образованием
CREATE NONCLUSTERED INDEX index6 ON [Квартиросъемщики] ([Фамилия])
В таблице Квартиросъемщики был создан неуникальный, некластеризованный индекс по полю Фамилия , так как это поле используется в качестве критерия поиска в хранимых процедурах Выдача информации о квартиросъемщике по первой букве, поиск квартиросъемщиков по фамилии
CREATE NONCLUSTERED INDEX index7 ON [Заявки] ([Дата подачи заявки])
В таблице Заявки был создан неуникальный, некластеризованный индекс по полю Дата подачи заявки , так как это поле используется в качестве критерия поиска в хранимых процедурах Показать заявки за последние n дней, Показать заявки за выбранный период, а также в триггере insert_zayavka
CREATE UNIQUE NONCLUSTERED INDEX index8 ON [Отделы] ([Номер отдела])
В таблице Отделы был создан уникальный, некластеризованный индекс по полю Номер отдела, так как это поле используется в качестве критерия поиска в хранимой процедуре Выдача кол-во рабочих по должностям, а также в курсоре search_sotrd_po_otdelu, а также в триггере Запрет удаления отдела и при вставке в триггере proverka_otdela.
CREATE NONCLUSTERED INDEX index9 ON [Зарплата] ([Зарплата])
В таблице Зарплата был создан неуникальный, некластеризованный индекс по полю Зарплата , так как это поле используется в качестве критерия поиска в хранимых процедурах Разбитие по группам по величине зарплаты, Вся выданная зарплата, а также в представлениях Максимальная зарплата каждого сотрудника, Информация о сотруднике с самой маленькой зарплатой
CREATE NONCLUSTERED INDEX index10 ON [Дома на обслуживании] ([Жилая площадь])
В таблице Дома на обслуживании был создан неуникальный, некластеризованный индекс по полю Жилая площадь , так как это поле используется в качестве критерия поиска в хранимой процедуре Выдать адреса домов по жилой площади, а также в курсоре srednnyya_ploshad_odnoi_kvartir