Проектирование базы данных

На основе описанных зависимостей выполним анализ данных и опишем ход нормализации.

Приведение к 1НФ

Отношение находится в первой нормальной форме, если...

В данном случае исходное универсальное отношение включает следующие атрибуты:

1. Код аптеки.

2. Название аптеки.

3. Адрес.

4. Телефон.

5. Код товара.

6. Код наименования.

7. Наименование лекарственного препарата.

8. Код группы.

9. Название группы.

10. Форма.

11. Доза.

12. Количество в упаковке.

13. Код страны-производителя.

14. Название страны-производителя.

15. Дата продажи.

16. Цена за упаковку.

17. Количество проданных упаковок.

В соответствии с описанными выше функциональными зависимостями формируем первичный ключ отношения, который включает следующие атрибуты:

- Код аптеки,

- Код товара,

- Дата продажи,

Их комбинация является уникальной для каждой строки (записи о продажах товара за день), позволяет однозначно идентифицировать запись.

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

1. Аномалии ввода (пояснить на примере указанных данных). …

2. Аномалии редактирования (пояснить на примере указанных данных). …

3. Аномалии удаления (пояснить на примере указанных данных). …

Для их устранения необходимо выполнить декомпозицию отношения в соответствии с алгоритмом нормализации.

Приведение к 2НФ

Отношение находится во второй нормальной форме, если...

В соответствии с описанными выше зависимостями можно сделать вывод, что в описанном отношении имеются следующие частичные зависимости:

1. Код аптеки определяет:

- Название аптеки,

- Адрес,

- Телефон.

Указанные атрибуты не зависят от кода товара и даты его продажи.

2. Код товара определяет:

- Код наименования,

- Наименование лекарственного препарата,

- Код группы,

- Название группы,

- Форма,

- Доза,

- Количество в упаковке,

- Код страны-производителя,

- Название страны-производителя.

Указанные атрибуты не зависят от кода аптеки и даты продажи товара.

Для выполнения требования полной функциональной зависимости неключевых атрибутов от всего первичного ключа необходимо устранить выявленные частичные зависимости путём декомпозиции таблицы (отношения).

На первом шаге декомпозиции устраняем первую частичную зависимость: сведения об аптеках выносим в отдельную таблицу (отношение) а в исходном отношении сохраняем атрибут «Код аптеки» в качестве внешнего ключа, позволяющего сохранить связь с выделенной таблицей «Аптеки», где «Код аптеки» становится первичным ключом.

На втором шаге аналогично выделяем отношение (таблицу) «Товары».

Таким образом, получаем три отношения (таблицы):

«Аптеки», которая включает атрибуты:

(Код аптеки, Название аптеки, Адрес, Телефон).

«Товары», которая включает атрибуты:

(Код товара, Код наименования, Наименование лекарственного препарата, Код группы, Название группы, Форма, Доза, Количество в упаковке, Код страны-производителя, Название страны‑производителя).

«Продажи товаров», которая включает атрибуты:

(Код аптеки, Код товара, Дата продажи, Цена за упаковку,

Количество проданных упаковок).

В приведённых отношениях отсутствуют частичные функциональные зависимости… Таким образом, все отношения находятся в 2НФ.

Однако при работе с данными (сведениями о товарах) могут возникать аномалии…:

1. Аномалии ввода (пояснить на примере указанных данных). …

2. Аномалии редактирования (пояснить на примере указанных данных). …

3. Аномалии удаления (пояснить на примере указанных данных). …

Следовательно, требуется продолжить декомпозицию отношения «Товары».

Приведение к 3НФ

Отношение находится в третьей нормальной форме, если...

В описанных выше отношениях «Аптеки» и «Продажи товаров» отсутствуют зависимости между неключевыми атрибутами, следовательно, они находятся в 3НФ.

В таблице «Товары» по коду наименования товара можно однозначно определить наименование лекарственного препарата, код группы, название группы.

Таким образом, имеется зависимость между неключевыми атрибутами отношения. Эти зависимости должны быть устранены путём декомпозиции – выделением таблицы «Наименования товаров», включающей атрибуты:

(Код наименования, Наименование лекарственного препарата, Код группы, Название группы),

где «Код наименования» является первичным ключом.

В исходном отношении «Товары» остаются следующие атрибуты:

(Код товара, Код наименования, Форма, Доза, Количество в упаковке, Код страны-производителя, Название страны‑производителя),

где «Код товара» является первичным ключом, а «Код наименования» – внешним ключом, позволяющим определить наименование товара по выделенной таблице «Наименования товаров».

В отношении присутствует ещё одна зависимость между некючевыми атрибутами: по коду страны-производителя можно однозначно определить название страны-производителя.

Для её устранения выполняем декомпозицию – выделяем ещё одно отношение – «Страны» с атрибутами:

(Код страны-производителя, Название страны‑производителя).

Исходное отношение «Товары» получает схему:

(Код товара, Код наименования, Форма, Доза, Количество в упаковке, Код страны-производителя),

где «Код страны-производителя» становится внешним ключом и позволяет определить наименование страны по выделенной таблице.

В этом отношении отсутствуют зависимости между неключевыми атрибутами, таким образом, оно находится в 3НФ.

В таблице «Наименования товаров» по коду группы можно однозначно определить название группы, следовательно, в отношении имеются зависимости между неключевыми атрибутами, которые должны быть устранены путём дальнейшей декомпозиции, в результате которой получаем два отношения:

«Группы товаров», включающее атрибуты:

(Код группы, Название группы).

где «Код группы» является первичным ключом.

 «Наименования товаров», включающее атрибуты:

(Код наименования, Наименование лекарственного препарата, Код группы),

где «Код наименования» является первичным ключом, а «Код группы» является внешним ключом, позволяющим установить связь товара с указанным наименованием с группой товаров (отнести его к соответствующей группе).

В полученных отношениях отсутствуют зависимости между неключевыми атрибутами, следовательно, все отношения находятся в 3НФ.

В результате нормализации получена схема БД, где каждое отношение находится в 3НФ:

«Аптеки»:

(Код аптеки, Название аптеки, Адрес, Телефон).

«Группы товаров»:

(Код группы, Название группы).

«Наименования товаров»:

(Код наименования, Наименование лекарственного препарата, Код группы).

«Страны»: 

(Код страны-производителя, Название страны‑производителя).

 «Товары»:

(Код товара, Код наименования, Форма, Доза, Количество в упаковке, Код страны-производителя).

«Продажи товаров»:

(Код аптеки, Код товара, Дата продажи, Цена за упаковку,

Количество проданных упаковок).

Пунктиром подчёркнуты внешние ключи. В отношении «Продажи товаров» их комбинация с атрибутом «Дата продажи» формирует первичный ключ. Эта таблица является подчинённой, она не имеет дочерних таблиц.