Практическое занятие 2 Тема: Нормализация реляционной БД, освоение принципов проектирования БД Цель занятия: 1. Изучить приемы сбора концептуальных требований. 2. Изучить приемы выделения сущностей. 3. Изучить приемы нормализации отношений (создания таблиц). Теоретические сведения При получении задания на разработку базы данных программист исследует производственный процесс фирмы. Он определяет: - места возникновения информации (пополнение базы данных новыми сведениями); - лиц, имеющих право вносить новые и редактировать существующие данные; - изучает ранее используемые программные продукты, из которых и берёт концептуальные требования; - во время бесед с конечными пользователями определяет новые концептуальные требования. Так как концептуальные требования собираются в отделах и службах фирмы, то уже имеется примерное разделение концептуальных требований на сущности. При детальном анализе уточняются ранее используемые сущности и добавляются новые сущности, определяется наполнение каждой сущности атрибутами (конкретными концептуальными требованиями). Предположим, что надо создать базу данных «Магазин «Молоко». Для создания указанной базы данных были собраны следующие концептуальные требования: Наименование товара Кол-во товара Дата накладной Дата изготовления Наименование поставщика Адрес поставщика № накладной Цена за единицу Стоимость товара Ед. измерения Концептуальные требования разделим на две сущности. Сущность «Поставщик» будет содержать атрибуты: Наименование поставщика и Адрес поставщика. Сущность «Товар» будет содержать атрибуты: Наименование товара, Кол-во товара, Цена за единицу, Дата накладной, Дата изготовления, № накладной, Единица измерения и Стоимость товара. Применим к этим двум сущностям условия первой нормальной формы: - должны отсутствовать повторяющиеся записи; - должны отсутствовать повторяющиеся атрибуты; - каждый атрибут (поле) должен быть не делимым. Для атрибута Адрес сущности «Поставщик» третий пункт не выполняется. Поэтому атрибут Адрес разделим на четыре атрибута: Почтовый индекс, Город, Улица и Остаток (т.е. оставшаяся часть адреса). К сущностям «Поставщик» и «Товар» применим условия второй нормальной формы: - выполняются условия первой нормальной формы; - первичный ключ однозначно определяет запись; - все поля записи зависят от первичного ключа; - первичный ключ имеет минимальную форму (отсутствует избыточность). Для сущности «Товар» назначим сложный первичный ключ, состоящий из двух атрибутов: № накладной и Дата накладной. Так как одна накладная может содержать несколько товаров, то нет однозначности определения записи. Поэтому из сущности «Товар» выделим новую сущность «Накладная». В сущность «Накладная» поместим атрибуты: № накладной, Дата накладной, Кол-во товара, Дата изготовления и Стоимость товара. Сложный первичный ключ, состоящий из полей № накладной и Дата накладной будет относиться к сущности «Накладная». Для определения прихода или расхода товара в сущность «Накладная» добавим атрибут Признак, где единицей будем определять приход товара, а нулём – расход товара. Для определения Наименования товара (для связи с сущностью «Товар») добавим в сущность «Накладная» атрибут Ключ товара, который будет внешним ключом. Сложный первичный ключ, состоящий из атрибутов № накладной и Дата накладной, будет избыточным, так как в указанный день можно выписать одну накладную с указанным номером. Если допустить, что нумерация накладных будет уникальной в течение года, то атрибут № накладной будет однозначно определять записи, в сущности, «Накладная», т.е. первичный ключ будет состоять из одного атрибута № накладной. Сущность «Товар» состоит из атрибутов: Наименование товара, Цена за единицу и Единица измерения. Для связи сущности «Товар» с сущностью «Накладная», добавим в сущность «Товар» атрибут Ключ товара, который и назначим первичным ключом. От первичного ключа Ключ товара не зависит атрибут Единица измерения. Поэтому из сущности «Товар» выделим новую сущность «Единицы измерения», добавив в сущность «Товар» атрибут Ключ единиц, по значениям которого будет построен внешний ключ для связи с сущностью «Единицы измерения». Сущность «Единицы измерения» будет состоять из атрибутов Единица измерения и Ключ единиц. Атрибут Ключ единиц назначим первичным ключом для сущности «Единицы измерения». Сущность «Поставщик» состоит из атрибутов: Наименование поставщика, Почтовый индекс, Город, Улица и Остаток. В сущность «Поставщик» добавим атрибут Ключ поставщика, который и назначим первичным ключом. Для связи сущности «Поставщик» с сущностью «Накладная» добавим в сущность «Накладная» атрибут Ключ поставщика, по которому построим внешний ключ. От первичного ключа Ключ поставщика не зависят атрибуты Город и Улица (так как в одном городе может находиться много поставщиков и поставщики могут находиться на одной улице либо в разных городах , либо в одном городе). Поэтому из сущности «Поставщик» выделим две сущности «Город» и «Улица», добавив в сущность «Поставщик» атрибуты Ключ города и Ключ улицы, по которым будут построены внешние ключи для связи с соответствующими сущностями. Сущность «Город» будет состоять из двух атрибутов: Ключ города (первичный ключ) и Наименование города. Сущность «Улица» будет состоять из двух атрибутов: Ключ улицы (первичный ключ) и Наименование улицы. Рис. 1. Структура базы данных «Магазин «Молоко»». Применим ко всем сущностям условия третьей нормальной формы: - должны выполняться условия второй нормальной формы; - внутри каждой сущности должны отсутствовать транзитивные связи. Атрибут «Стоимость товара» сущности «Накладная» не соответствует второму пункту третьей нормальной формы, так как стоимость товара определяется как произведение количества товара на цену за единицу. Здесь можно поступить двумя способами либо атрибут «Стоимость товара» выделить в отдельную таблицу, добавив соответствующие ключи, либо атрибут «Стоимость товара» оставить в сущности «Накладная», но поместив его в виртуальное (вычисляемое) поле. Поступим вторым способом. Просмотрев все сущности, установили, что транзитивные связи отсутствуют. На основании полученных сущностей построим таблицы. База данных нормализована и состоит из шести таблиц. Структура базы данных приведена на рис. 1. Темы индивидуальных заданий Вариант 1 1. Московская область : административное деление. Концептуальные требования: Районные центры. Площадь района. Население района Ф.И.О. главы администрации Тел. главы Адрес администрации Отделы Фото Режимы работы: Сортировка по полям – Районные центры, Площадь района, Население района. Поиск по полям - ФИО главы админ., Районные центры, Отделы. Фильтрация по полям на диапазон значений- ФИО главы админ., Районные центры, Площадь района, Население района. Вариант 2 2.Мосты и акведуки г. Москвы. Концептуальные требования: Наименование Дата постройки Ф.И.О. архитектора Ф.И.О. инженера Адрес Владелец Стоимость Дата послед. ремонта Осн. параметры. Фото Режимы работы: Сортировка по полям – Наименование, Дата постройки, Адрес, Стоимость. Поиск по полям - ФИО архитектора, ФИО инженера, Владелец, Наименование. Фильтрация по полям на диапазон значений- Дата постройки, Дата послед. ремонта, ФИО архитектора, ФИО инженера, Стоимость. Вариант 3 3. Каталог почтовых марок. Концептуальные требования: Номер Год издания Тематика Ф.И.О. художника Сюжет Номинал Стоимость Тираж Способ печати Размеры Фото Режимы работы: Сортировка по полям – Номер, Год издания, Тематика, Способ печати. Поиск по полям - ФИО художника, Номер, Номинал. Фильтрация по полям на диапазон значений- ФИО художника, Год издания, Способ печати. Вариант 4 4. Каталог монет России. Концептуальные требования: Номер Номинал Материал Размер Вес Год выпуска Ф.И.О. гравера Монетный двор Тираж Редкость Фото Режимы работы: Сортировка по полям – Номер, Год выпуска, Материал, Монетный двор. Поиск по полям - ФИО гравёра, Номер, Номинал, Редкость. Фильтрация по полям на диапазон значений- ФИО гравёра, Год выпуска, Материал, Номинал Вариант 5 5. Композиторы России. Концептуальные требования: Ф.И.О. композит. Дата рождения Оперы Балеты Симфонии Прочие произв. Дата смерти Возраст Фото Дата начала конц. деят. Режимы работы: Сортировка по полям – ФИО композитора, Дата рождения Поиск по полям - ФИО композитора, Оперы, Балеты, Дата рождения. Фильтрация по полям на диапазон значений- ФИО композитора, Год рождения, Возраст. Ход работы Название базы данных: ______________________________________________________ Концептуальные требования. № п.п. Название (русское) Название (английское) Тип Размер Сущность Примечание 1 2 3 4 5 Первая нормальная форма. Условия: 1. ___________________________________________________________________________ 2. ___________________________________________________________________________ 3. ___________________________________________________________________________ 4. ___________________________________________________________________________ Вывод (по применению первой нормальной формы): _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ Вторая нормальная форма. Условия: 1. ___________________________________________________________________________ 2. ___________________________________________________________________________ 3.____________________________________________________________________________ 4.____________________________________________________________________________ Вывод (по применению второй нормальной формы): _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ Третья нормальная форма. Условия: 1.____________________________________________________________________________ 2. ___________________________________________________________________________ Вывод (по применению третьей нормальной формы): _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ Структура таблиц. Таблица ______________________ _________________________ ( Название русское ) ( Название английское ) № п.п. Название (русское) Название (английское) Тип Размер Примечание 1 2 3 4 5 В случае необходимости при описании структур таблиц добавьте дополнительный лист. Структура базы данных «___________________________________________________» В случае необходимости при описании структуры базы данных добавьте дополнительный лист. Контрольные вопросы: 1. Дайте определения базы данных и СУБД? 2. Что такое сущность, атрибут сущности? 3. Что такое таблица, запись, поле базы данных? 4. Что такое первичный ключ таблицы? 5. Что такое нормализация отношений?