Лабораторная работа № 3
Логическая модель данных. Правила целостности.
Задание
- На основе концептуальной модели предметной области построить логическую модель данных. Использовать реляционную модель. Модель построить в среде Microsoft Access. Использовать режим Конструктора таблиц. Построить схему (диаграмму) базы данных.
- Спроектировать и реализовать необходимые правила(ограничения) целостности.
- Подготовить и сдать преподавателю отчёт по лабораторной работе в печатном виде. Отчёт должен содержать:
· титульный лист;
· логическую модель в виде схемы (диаграммы) базы данных;
· логическую модель в виде текста с подробным описанием правил(ограничений) целостности;
· анализ модели на соответствие принципам нормализации.
- Продемонстрировать преподавателю базу данных в среде MS Access. В базу необходимо ввести небольшое количество тестовых данных. В каждой таблице должно быть не менее 5 запией. В ассоциациях, не менее 10 записей.
Учебный пример разработки базы данных
Построение логической модели данных
Логическая модель строится на основе концептуальной модели.
Из всех возможных видов логических моделей используем реляционную логическую модель. Это самый распространенный вид логических моделей.
Необходимо выполнить следующие шаги процедуры проектирования логической модели.
1. Представить каждую сущность как таблицу базы данных и специфицировать первичный ключ этой таблицы. При необходимости специфицировать внешний ключ и ограничения на внешний ключ этой таблицы.
2. Представить каждую ассоциацию как таблицу. Использовать в этой таблице внешние ключи для идентификации участников ассоциации и специфицировать ограничения, связанные с каждым из этих внешних ключей.
5. Представить каждый атрибут сущности как поле в таблице, представляющей сущность.
6. Для того чтобы исключить в проекте непреднамеренные нарушения каких-либо принципов нормализации, выполнить процедуру проверки отношений на соответствие 3-й нормальной форме (3НФ) или нормальной форме Бойса-Кодда (НФБК), описание процедуры см. в курсе лекций.
7. Если в процессе проверки было выявлено несоответствие и произведено разделение каких-либо таблиц, то следует модифицировать концептуальную модель базы данных и повторить перечисленные шаги.
8. Кроме уже введенных, ввести необходимые правила целостности, в частности правила доменной целостности. Дать краткое описание полученных таблиц и их полей.
Для нашей учебной базы данных «Библиотека» логическая модель в виде схемы (диаграммы) базы данных будет выглядеть так:
Логическая модель в виде текста с подробным описанием правил(ограничений) целостности описывается так:
СОЗДАТЬ ТАБЛИЦУ Создатели
ПЕРВИЧНЫЙ КЛЮЧ (Код_создателя)
ПОЛЯ (Код_создателя Целое, Фамилия Текст 30, Имя Текст 30, Отчество Текст 30,);
СОЗДАТЬ ТАБЛИЦУ Издательства
ПЕРВИЧНЫЙ КЛЮЧ (Код_издательства)
ПОЛЯ (Код_издательства Целое, Название
Текст 40, Город Текст 25);
СОЗДАТЬ ТАБЛИЦУ Заглавия
ПЕРВИЧНЫЙ КЛЮЧ (Код_заглавия)
ПОЛЯ (Код_заглавия Целое, Заглавие Текст 100);
СОЗДАТЬ ТАБЛИЦУ Вид_издания
ПЕРВИЧНЫЙ КЛЮЧ (Вид_издания)
ПОЛЯ (Вид_издания Целое, Название_вида Текст 16);
СОЗДАТЬ ТАБЛИЦУ Характеры
ПЕРВИЧНЫЙ КЛЮЧ (Код_характера)
ПОЛЯ (Код_характера Целое, Характер_переиздания Текст 16);
СОЗДАТЬ ТАБЛИЦУ Языки
ПЕРВИЧНЫЙ КЛЮЧ (Код_языка)
ПОЛЯ (Код_языка Целое, Язык Текст 16, Сокращение Текст 6);
СОЗДАТЬ ТАБЛИЦУ Места
ПЕРВИЧНЫЙ КЛЮЧ (Код_места)
ПОЛЯ (Код_места Целое, Номер_комнаты Целое,
Номер_стелажа Целое, Номер_полки Целое)
ОГРАНИЧЕНИЯ (Номер_стелажа > 0; Номер_полки > 0);
СОЗДАТЬ ТАБЛИЦУ Читатели
ПЕРВИЧНЫЙ КЛЮЧ (Ном_билета)
ПОЛЯ (Ном_билета Целое, Фамилия Текст 30, Имя Текст 20,
Отчество Текст 20, Адрес Текст 60, Телефон Текст 9);
СОЗДАТЬ ТАБЛИЦУ Издания
ПЕРВИЧНЫЙ КЛЮЧ (Код_издания)
ВНЕШНИЙ КЛЮЧ (Код_заглавия ИЗ Заглавия
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Заглавия ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Заглавия.Код_заглавия ОГРАНИЧИВАЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Вид_издания ИЗ Вид_издания
NULL-значения ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Вид_издания ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Вид_издания.Вид_издания КАСКАДИРУЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Код_издательства ИЗ Издательства
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Издательства ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Издательства.Код_издательства КАСКАДИРУЕТСЯ)
ПОЛЯ (Код_издания Целое, Код_заглавия Целое,
Вид_издания Текст 16, Номер_тома Целое,
Авторский_знак Текст 3, Библиотечн_шифр Текст 12,
Повторность Целое, Код_издательст- ва Целое,
Год_издания Целое);
СОЗДАТЬ ТАБЛИЦУ Переплеты
ПЕРВИЧНЫЙ КЛЮЧ (Номер_переплета)
ВНЕШНИЙ КЛЮЧ (Код_издания ИЗ Издания
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Издания ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Издания.Код_издания КАСКАДИРУЕТСЯ)
ПОЛЯ (Номер_переплета Целое, Код_издания Целое, Цена Деньги,
Дата_приобретения Дата);
СОЗДАТЬ ТАБЛИЦУ Аннотации
ПЕРВИЧНЫЙ КЛЮЧ (Код_издания)
ВНЕШНИЙ КЛЮЧ (Код_издания ИЗ Издания
NULL-значения ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Издания ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Издания.Код_издания КАСКАДИРУЕТСЯ)
ПОЛЯ (Код_издания Целое, Аннотация Текст 800);
СОЗДАТЬ ТАБЛИЦУ Авторы (Связывает Создатели и Издания)
ПЕРВИЧНЫЙ КЛЮЧ (Код_создателя, Код_издания)
ВНЕШНИЙ КЛЮЧ (Код_создателя ИЗ Создатели
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Создатели ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Создатели.Код_создателя КАСКАДИРУЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Код_издания ИЗ Издания
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Издания ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Издания.Код_издания КАСКАДИРУЕТСЯ)
ПОЛЯ (Код_создателя Целое, Код_издания Целое);
Аналогичное содержание имеют описания таблиц Составители, Редакторы, Художники и Переиздания. Остальные же таблицы проектируемой базы данных описываются так:
СОЗДАТЬ ТАБЛИЦУ Переводчики *(Связывает Создатели, Издания и Языки)
ПЕРВИЧНЫЙ КЛЮЧ (Код_создателя, Код_издания)
ВНЕШНИЙ КЛЮЧ (Код_создателя ИЗ Создатели
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Создатели ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Создатели.Код_создателя КАСКАДИРУЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Код_издания ИЗ Издания
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Издания ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Издания.Код_издания КАСКАДИРУЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Код_языка ИЗ Языки
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Языки ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Языки.Код_языка КАСКАДИРУЕТСЯ)
ПОЛЯ (Код_создателя Целое, Код_издания Целое, Код_языка Целое);
СОЗДАТЬ ТАБЛИЦУ Размещение *(Связывает Места и Переплеты)
ПЕРВИЧНЫЙ КЛЮЧ (Код_места, Номер_переплета)
ВНЕШНИЙ КЛЮЧ (Код_места ИЗ Места
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Места ОГРАНИЧИВАЕТСЯ
ОБНОВЛЕНИЕ Места.Код_места КАСКАДИРУЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Номер_переплета ИЗ Переплеты
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Переплеты КАСКАДИРУЕТСЯ
ОБНОВЛЕНИЕ Переплеты.Ном_переплета КАСКАДИРУЕТСЯ)
ПОЛЯ (Код_места Целое, Номер_переплета Целое,
Дата_размещения Дата, Дата_изъятия Дата)
ОГРАНИЧЕНИЯ (Дата_размещения <= Дата_изъятия);
СОЗДАТЬ ТАБЛИЦУ Выдача *(Связывает Читатели и Переплеты)
ПЕРВИЧНЫЙ КЛЮЧ (Ном_билета, Ном_переплета, Дата_выдачи)
ВНЕШНИЙ КЛЮЧ (Ном_билета ИЗ Читатели
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Читатели КАСКАДИРУЕТСЯ
ОБНОВЛЕНИЕ Читатели.Ном_билета КАСКАДИРУЕТСЯ)
ВНЕШНИЙ КЛЮЧ (Ном_переплета ИЗ Переплеты
NULL-значения НЕ ДОПУСТИМЫ
УДАЛЕНИЕ ИЗ Переплеты КАСКАДИРУЕТСЯ
ОБНОВЛЕНИЕ Переплеты.Ном_переплета КАСКАДИРУЕТСЯ)
ПОЛЯ (Ном_билета Целое, Ном_переплета Целое, Дата_выдачи Дата,
Срок Целое, Дата_возврата Дата)
ОГРАНИЧЕНИЯ (Дата_выдачи <= Дата_возврата; Срок > 0);
Теперь следует проверить, не нарушены ли в данном проекте какие-либо принципы нормализации, т.е. что любое неключевое поле каждой таблицы:
- функционально зависит от полного первичного ключа, а не от его части (если ключ составной);
- не имеет функциональной зависимости от другого неключевого поля.
Сущности Авторы, Составители, Редакторы, Художники и Переиздания, не имеющие неключевых полей, безусловно нормализованы. Нормализованы и сущности Создатели, Характеры, Заглавия, Вид_издания и Аннотации, состоящие из несоставного ключа и единственного неключевого поля.
Анализ сущностей Переводчики, Размещение и Выдача, состоящих из составного ключа и неключевых полей, показал, что в них нет функциональных связей между неключевыми полями. Последние же не зависят функционально от какой-либо части составного ключа.
Наконец, анализ сущностей Издания, Переплеты, Места, Читатели и Языки, показал, что единственной "подозрительной" сущностью является стержень Языки, имеющий два функционально связанных неключевых поля: Язык и Сокращение.
Поле Язык стало неключевым из-за ввода цифрового первичного ключа Код_языка, заменяющего текстовый возможный ключ Язык. Это позволило уменьшить объем хранимых данных в таблице Переводчики, затраты труда на ввод множества текстовых значений и возможной противоречивости, которая часто возникает из-за ввода в разные поля ошибочных дубликатов (например, "Английский", "Англиский", "Анлийский", "Англйский" и т.п.). Если мы вспомним рекомендации о замене на время нормализации цифровых заменителей первичных ключей (Код_языка) на исходный ключ (Язык) или воспользуемся формулировкой НФБК, то окажется, что таблица Языки – нормализована.