Иерархическая модель данных. Предметная область «Склад продовольственных товаров»

Между сущностями существуют связи. Связь представляет взаимодействие между сущностями. Исходя из этих связей, построим семантическую модель предметной области «Склад продовольственных товаров», которая представлена на рисунке 2.

Рисунок 2 - Семантическая модель предметной области «Склад  

                     продовольственных товаров»

Между сущностями «Группа товаров» и «Товары» существует связь (1:M), так одна товарная группа может содержать несколько наименований товаров. Связь обязательная с двух сторон, так как каждый товар обязательно соответствует определенной товарной группе и каждая группа содержит определенный перечень товаров.

Между сущностями «Товары» и «Ведомости» также связь (1:М), так как каждый один товар может поставляться на склад в разные дни, в разном количестве, по разной цене, что будет фиксироваться в разных Ведомостях. Связь обязательная с двух сторон. Так как каждый товар обязательно имеет свою ведомость, и в каждой ведомости фиксирует определенный товар.

Между сущностями «Ведомости» и «Поставщики» существует связь (1:М), так как один поставщик может поставлять товары на склад в разные дни, в разном количестве, по разной цене, что будет фиксироваться в разных ведомостях. Связь между сущностями тоже обязательная с двух сторон, так как каждый поставщик поставляет товар по ведомостью (накладной), и в каждой ведомости указывается, кто является поставщиком.

3.2 Логическая модель данных

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

Так как в нашей модели присутствуют только связи типа (1:М), необходимо обратиться к следующему правилу:

Правило 4. Если связь типа (1:М) и степень участия сущности на стороне М является обязательной, то необходимо создать две таблицы – по одной для каждой сущности. Первичные ключи таблиц соответствуют первичным ключам сущностей. Для установления связи между ними необходимо добавить в подчиненную таблицу копию ключевого атрибута из главной таблицы, который станет внешним ключом подчиненной таблицы.

В нашем примере данному правилу соответствует связь ГРУППЫ ТОВАРОВ-ТОВАРЫ. Тогда согласно правилу 4 должны быть сгенерированы две таблицы следующей структуры, они представлены на рисунке 3.

Рисунок 3- создание связи ГРУППА ТОВАРОВ-ТОВАРЫ

Данному правилу также соответствует связь ТОВАРЫ-НАКЛАДНЫЕ. Преобразовываем таблицы согласно правилу, таблицы представлены на рисунке 4.

Рисунок 4 – создание связи ТОВАРЫ-ВЕДОМОСТИ

Связь ПОСТАВЩИКИ-ВЕДОМОСТИ также соответствует данному правилу. В итоге получаем следующие таблицы, изображенные на рисунке 5.

Рисунок 5-создание связи ПОСТАВЩИКИ-ВЕДОМОСТИ

Связь АВТО-ПОСТАВЩИКИ-ВЕДОМОСТИ также соответствует данному правилу. В итоге получаем следующие таблицы, изображенные на Рис 6

В результате получим реляционную модель для ER-модели предметной области «Склад промышленных товаров», которая изображена на рисунке 7.

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

Теперь необходимо проверить полученные отношения на соответствие трем нормальным формам. Реляционная база данных считается эффективной, если все ее таблицы находятся как минимум в 3НФ.

Определение 1НФ: Таблица находится в 1НФ, если все ее поля содержат только неделимые значения. Для созданной нами модели это выполняется, так как нет неделимых значений полей.

Определение 2НФ: Таблица находится в 2НФ, если она удовлетворяет требованиям 1НФ и неключевые поля функционально полно зависят от первичного ключа. Для данной модели это выполняется, так как неключивые поля функционально полно зависят от первичного ключа, то есть каждое поле каждой таблицы зависит только от ключевого поля.

Определение 3НФ: Отношение находится в третьей нормальной форме тогда и только тогда, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей, то есть зависимостей неключевых атрибутов от неключевых. Для созданной нами модели это выполняется, так как транзитивные зависимости между неключевыми атрибутами нет, то есть неключевые поля не имеют зависимости между собой и зависят только от ключевого поля.

Достижение третьей нормальной формы обычно является достаточным для окончания процесса нормализации отношений. После приведения созданной модели в соответствие рассмотренным правилам мы получили логическую модель данных, которая приведена в Приложении А (в виде схемы данных из СУБД Microsoft Access).

3.3 Определение физических характеристик атрибутов

Определяем параметры каждого атрибута построенной нами логической модели базы данных: тип, размер и обязательность заполнения.

Параметры атрибутов для всех таблиц базы данных представлены в таблице 2.

Таблица 2 - Атрибуты таблиц базы данных

4. Реализация системы

4.1 Создание, связывание и заполнение таблиц

Реализацию системы производим на основе модели, разработанной в разделе 3. Атрибуты и их характеристики берем из таблицы 2. Сначала создаем базу данных, называем ее «Склад продовольственных товаров», все таблицы создаем в режиме конструктора.

Сначала создаем таблицу «Товары»,  типы и параметры полей берем из раздела 3.3. Поле «Код товары» выбираем ключевым. Все действия отображены на рисунке 7.

Рисунок 8-Изображение таблицы «Товары»

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

                   Рисунок 9-Таблица «Накладные» в режиме конструктора

Следующую создаем таблицу «Группы товаров». Поля и их значения  берем из раздела 3.3. Ключевое поле выбираем «Код группы». Итоговая таблица изображена на рисунке 10.

                      Рисунок 10- Таблица «Группы товаров» в режиме конструктора

              Следующую создаем таблицу «Поставщики», поля них значения берем из раздела 3.3, ключевое поле «Код поставщика», таблица изображена на рисунке 11.

                     Рисунок 11 –Таблица «Поставщики» в режиме конструктора.

И в конце создаем таблицу «Авто», поля них значения берем из раздела 3.3, ключевое поле «Код автомобиля», таблица изображена на рисунке 12.

Рисунок 12 –Таблица «Авто» в режиме конструктора

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

              1 поле «Код группы» таблицы «Группы товаров», которое является первичным ключом, связываем с полем «Код группы» таблицы «Товары», которое является ее внешним ключом, в результате образуется связь «Один ко многим». В свойствах необходимо включить пункт «Обеспечивание целостности данных»;

2 поле «Код поставщика» таблицы «Поставщики», которое является первичным ключом, связываем с полем «Код поставщика» таблицы «Ведомость», которое является ее внешним ключом, в результате образуется связь «Один ко многим». В свойствах необходимо включить пункт «Обеспечение целостности данных»;

3 поле «Код товара» таблицы «Товары», которое является первичным ключом, связываем с полем «Код товара» таблицы «Ведомости», которое является ее внешним ключом, в результате образуется связь «Один ко многим». В свойствах необходимо включить пункт «Обеспечение целостности данных».

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

Разработанная схема приведена в Приложении А.

Теперь, когда таблицы и связи созданы, необходимо перейти к их заполнению.  Таблицы заполняем в режиме ввода данных. В таблице «Товары», в поле «Код группы» используем «Мастер подстановок» и выбираем значения из списка, составленного на основе таблицы «Группы товаров». Для лучшей читаемости таблицы, выбираем для отображения не номер кода группы, а название  товарной группы, но на самом деле у нас будет введен именно код группы.

В таблице «Ведомости» используем мастер подстановок для полей «Код товара» и «Код поставщика» и выбираем значение из списка, составленного из таблицы «Поставщики». Для лучшей читаемости, выбираем,  чтобы отражались не цифры, а название товара и поставщика соответственно, хотя на самом деле у нас будут введены в таблицы именно коды товара и поставщика. Заполненные таблицы «Товары» и «Ведомости» изображены на рисунке 13.

              Рисунок 13 – Заполненные таблицы «Товары» и «Ведомости»

Заполненные таблицы «Группы товаров» и «Поставщики» изображены на рисунке 14.

              Рисунок 14 - Заполненные таблицы «Группы товаров» и «Поставщики»

4.2 Реализация запросов к базе данных

После того, как мы заполнили таблицы, необходимо создать запросы к нашей базе данных.

Сначала нам необходимо создать запросы на выборку.

Первым создаем запрос «Время и дата», в котором будут показаны товары, поступившие на склад после 10.11.2010 и стоимостью общей суммой 50 000 руб. Для этого добавляем в окно запроса таблицы «Товары» и «Ведомости». В бланк запроса добавляем поля «Название товара», «Дата», «Количество», «Оптовая цена за единицу». В строке «Условия отбора» для столбца «Дата» налагаем ограничение по дате, а для столбца «Оптовая цена за единицу» ограничение по стоимости.

Переключаемся в режим SQL и просматриваем текст запроса:

SELECT Товары.[Название товара], Ведомость.[Дата составления], Ведомость.Количество, Ведомость.[Оптовая стоимость]

FROM Товары INNER JOIN Ведомость ON Товары.[Код товара] = Ведомость.[Код товара]

WHERE (((Ведомость.[Дата составления])>#9/10/2010#) AND ((Ведомость.[Оптовая стоимость])>50000))