Создание базы данных для магазина книг, занимающимся продажей книг лицам и закупкой их у поставщиков, страница 2

Покупки.

Имя клиента	Название книги	Количество	Дата	Цена
Кл1	Из жизни самурая	2	12,06,04	350,50
Кл2	Лунное затмение	35	21,06,04	3867,5
Кл3	Солнечное затмение	5	27,06,04	628
Кл4	Глухое место	10	20,06,04	1456,50

Звонки.

Код города	Город
8432	Казань
095	Москва
0862	Орел
0912	Рязань
3472	Уфа

3.  Реляционная модель данных.

Отношения реляционной модели. Ключи отношений.

В реляционной модели данных каждому типу объекта соответствует отдельная таблица. Столбцы таблицы - свойства объектов, строки - экземпляры. Таблицы строятся по определенным правилам, так чтобы каждая таблица представляла собой математические отношения. Строки таблицы картежи отношения, а столбцы таблицы – атрибуты отношения. В соответствии со структурой данных в модели БД продажа книг будет определяться тремя отношениями: книги-авторы-клиенты. В каждом отношении реляционной модели должен присутствовать первичный ключ (может быть простым или составным). Отношение клиент первичный ключ – имя клиента. Отношение книги – название книги. Отношение авторы – имя, фамилия.

Нормализация отношений.

Отношения реляционной модели должны быть нормализованы, т.е. приведены к 1-й, 2-й или 3-й нормальным формам. Все рассматриваемые отношения находятся в 1-й нф, т.к. значения всех атрибутов не структурированы. Ко 2-й нф надо привести отношения авторы, т.к. в нем составной первичный ключ. Единственный неключевой атрибут заметки, полностью зависит от всего составного первичного ключа. Для приведения отношений к 3-й нф проанализируем связи между ключевыми атрибутами. Неключевые атрибуты функционально полно зависят друг от друга. Выделим их в таблице.

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

Связи между отношениями.

Т.к. все объекты предметной области связаны между собой, то и между всеми отношениями реляционной модели должны быть установлены связи, как отношения в логической структуре данных. Связь между отношениями можно установить только тогда, когда в них содержатся одинаковые атрибуты, такие атрибуты являются внешним ключом этих отношений. Отношение звонить и клиенты связаны по отношению к атрибуту город 1:м. Для установления связи м:м между двумя отношениями надо создать дополнительно связывающие отношение – это отношение должно содержать как минимум первичные ключи связываемых отношений. Для установления отношения связи  книги-авторы создаем отношение книги-авторы. Между отношениями книги клиенты создадим отношение покупки. Между отношениями поставщики книги создадим отношение поставки.

Книги-авторы - (название, фамилия, имя).

Покупки – позволяют узнать, какую книгу кто приобрел, в каком количестве и  когда (имя клиента, название книги, количество, дата, цена).

Поставки – позволяют узнать, какие книги и кем поставлены в магазин, в каком количестве, когда и по какой цене (имя поставщика, книга, цена, количество, дата).

Устранение избыточности данных.

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

Получаем следующие таблицы:

Книги-авторы – Кн#, Ав#.

Покупки - Кл#, Кн#, название книги, количество, дата, цена.

Поставки - П#, Кн#, цена, количество, дата.

Книги - Кн#, название, раздел, цена книги.

Клиенты – Кл#, имя клиента, город, код города, телефон.

Авторы – Ав#, фамилия, имя, заметки.

Поставщики - П#, имя поставщика, код город, телефон, контактное лицо.

Звонить – Код города.

Для уменьшения объема хранимых данных в связывающие отношения (таблицы) вводим дополнительные поля (Кн#, Ав#, Кл#, П#, Код города).

Текущее состояние БД.

4.  Запросы к БД и обработка процедуры БД.

Запросы к БД: формирование запросов; формулы реляционной алгебры; результаты запросов.

Запрос №1: рассчитывает объемы поставок за определенные промежутки времени.

Поставки * {Дата=12,05,04}[sum[количество]]

Запрос №2: рассчитывает объемы продаж за определенные промежутки времени.

Покупки * {Дата=20,06,04}[sum[количество]]

Запрос №3: рассчитать доходы фирмы за определенные промежутки времени.

Покупки * {Дата=12,06,04}[объем покупок=sum[количество]х[цена]] [№книги]* Поставки[объем поставок=sum[количество]х[цена]] {доход=[объем покупок]-[объем поставок]}

Запрос №4: определить наличие на складе каждого изделия.

Поставки * {название = железная маска}*[№книги]*[В наличии = *Поставки [количество]-*Покупки[количество]]

Запрос №5: определить издания, пользующиеся наибольшим спросом за определенный промежуток времени.

Покупки * {Дата=20,06,04}Sum[количество] *[№книги]* [название]

                                                                 (сортируем по убыванию)

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

Запрос №6: определить самых активных клиентов за определенный промежуток времени.

Покупки * {Дата=21,06,04}Sum[количество] *[№клиента]* [имя клиента]                                                                                           (сортируем по убыванию)

Результатом запроса: самые первые в отсортированном списке будут и являться самыми активными клиентами  за заданный промежуток времени.

Запрос №7: установить порядок скидок покупателям.

Покупки * {Sum([количество]х[цена])>1 000} [Скидка=[цена]x[количество]*0,05] [Цена со скидкой=[цена]х[количество]*0,95]

Количество	Скидка	Цена со скидкой
2	-	-
35	193,37	3674,13
5	-	-
10	72,82	1383,68

Процедуры обработки данных (алгоритмы процедур).

Запрос №1: рассчитывает объемы поставок за определенные промежутки времени.

Считаем суммарное количество поставок (по  значению поля «количество» из таблицы «поставки») за заданный промежуток времени.

Запрос №2: рассчитывает объемы продаж за определенные промежутки времени.

Подсчитываем сумму количества проданных книг (по значению поля «количество» из таблицы «покупки») за заданный промежуток времени.

Запрос №3: рассчитать доходы фирмы за определенные промежутки времени.

Сначала определяем объемы покупок, как произведение цены на количество книг, заказанных клиентом, затем объем поставок, как произведение цены на количество поставленных книг. Тогда доход фирмы определим, как разность объема покупок и поставок.

Запрос №4: определить наличие на складе каждого изделия.

Задаем название книги, которую нужно определить в наличии на складе, затем ищем разность между поставленном количеством и купленном количеством книг.

Запрос №5: определить издания, пользующиеся наибольшим спросом за определенный промежуток времени.

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

Запрос №6: определить самых активных клиентов за определенный промежуток времени.

Суммируется количество покупок каждого клиента, сортируется по убыванию и выводится самый активный клиент, который будет располагаться в начале списка.

Запрос №7: установить порядок скидок покупателям.

Суммируется объем покупки данного клиента, который рассчитывается, как цена продажи книги на количество купленных экземпляров, сравнивается с суммой установленной данной фирмой. Если сумма объема покупки превышает сумму, установленную данной фирмой, клиенту предоставляется скидка в размере 5%.