Переход к физической модели. Определение таблиц, полей и типов данных
Физическая модель базы данных представляет собой размещение данных на носителях, а также метод и средства организации эффективного доступа к ним.
Так как СУБД функционирует в составе и под управлением операционной системы, и база данных в основном размещается на устройствах общего доступа, используемых самой операционной системе и другими прикладными программами, то организация хранения данных и доступа к ним в значительной степени зависит от принципов и методов управления данными операционной системы.
Каждой сущности будет соответствовать таблица базы данных, каждому атрибуту - поле.
Для связи между таблицами добавим в каждую из них первичный ключ, пусть это будет поле счетчик с названием Код. Кроме того счетчик полезен для сортировки, выборки, поиска данных.
Имена таблиц и полей в них должны быть без пробелов для удобства обращения к ним. Тип данных выбираем по содержимому.
То есть получаем следующие таблицы:
Таблица 3
Группы товаров
| № п/п | Наименование поля | Тип данных | Примечание |
| КодГруппы | Счетчик | Первичный ключ | |
| Тип_товара | Числовой | Внешний ключ, связано с первичным ключом таблицы Типы_товаров | |
| Группа_товаров | Текстовый |
Таблица 4
Курс валют
| № п/п | Наименование поля | Тип данных | Примечание |
| КодКурс | Счетчик | Первичный ключ | |
| Дата | Дата/время | ||
| Курс | Числовой |
Таблица 5
Магазины
| № п/п | Наименование поля | Тип данных | Примечание |
| КодМаг | Счетчик | Первичный ключ | |
| Название | Текстовый | ||
| Адрес | Текстовый | ||
| ФИО_дир | Текстовый |
Таблица 6
Размеры
| № п/п | Наименование поля | Тип данных | Примечание |
| КодРазмера | Счетчик | Первичный ключ | |
| Тип_товара | Числовой | Внешний ключ, связано с первичным ключом таблицы Типы_товаров | |
| Размер | Текстовый |
Таблица 7
Цвета
| № п/п | Наименование поля | Тип данных | Примечание |
| КодЦвета | Счетчик | Первичный ключ | |
| Цвет | Текстовый |
Таблица 8
Типы товаров
| № п/п | Наименование поля | Тип данных | Примечание |
| КодТипТовара | Счетчик | Первичный ключ | |
| Тип_товара | Текстовый |
Таблица 9
Производители
| № п/п | Наименование поля | Тип данных | Примечание |
| КодПроизв | Счетчик | Первичный ключ | |
| Производитель | Текстовый |
Таблица 10
Товары
| № п/п | Наименование поля | Тип данных | Примечание |
| КодТовара | Счетчик | Первичный ключ | |
| Тип товара | Числовой | Внешний ключ, связано с первичным ключом таблицы Типы_товаров | |
| Группа товара | Числовой | Внешний ключ, связано с первичным ключом таблицы Группы_товаров | |
| Производитель | Числовой | Внешний ключ, связано с первичным ключом таблицы Производители | |
| Описание | Текстовый |
Таблица 11
Товары, Цвета, Размеры
| № п/п | Наименование поля | Тип данных | Примечание |
| КодТовЦвРазм | Счетчик | Первичный ключ | |
| Товар | Числовой | Внешний ключ, связано с первичным ключом таблицы Товары | |
| Цвет | Числовой | Внешний ключ, связано с первичным ключом таблицы Цвета | |
| Размер | Числовой | Внешний ключ, связано с первичным ключом таблицы Размеры |
Таблица 12
Поставки
| № п/п | Наименование поля | Тип данных | Примечание |
| КодПоставки | Счетчик | Первичный ключ | |
| Дата_поставки | Дата/время | ||
| Магазин | Числовой | Внешний ключ, связано с первичным ключом таблицы Магазины | |
| Курс | Числовой | Внешний ключ, связано с первичным ключом таблицы Курс_валют21 | |
| ТовЦветРазм | Числовой | Внешний ключ, связано с первичным ключом таблицы ТоварыЦветаРазмеры | |
| Кол-о | Числовой | ||
| Закупочная_цена | Денежный | Формат = 0,00¥ | |
| Корректировка_стоимости | Денежный |
Таблица 13
Продажи
| № п/п | Наименование поля | Тип данных | Примечание |
| КодПродажи | Счетчик | Первичный ключ | |
| Дата_продажи | Дата/время | ||
| Магазин | Числовой | Внешний ключ, связано с первичным ключом таблицы Магазины | |
| ТовЦветРазм | Числовой | Внешний ключ, связано с первичным ключом таблицы ТоварыЦветаРазмеры | |
| Кол-о | Числовой | ||
| Скидка | Денежный |
1.4 Определение связей между таблицами
Так как уже определены первичные и внешние ключи таблиц, связи между таблицами будут осуществляться по ним.
Все связи с обеспечением целостности данных для исключения случайного удаления данных со стороны «один» (см. рис. 14).
Рис.14. Схема данных
2. Создание запросов
Запрос позволяет выбрать необходимые данные из одной или нескольких взаимосвязанных таблиц, произвести вычисления и получить результат в виде виртуальной таблицы.
Полученная таблица может использоваться в качестве источника данных в следующих запросах, формах, отчетах, страницах доступа к данным.
Через запрос можно производить обновление данных в таблицах, добавление и удаление записей.
С помощью запроса можно выполнить следующие виды обработки данных:
· Выбрать записи, удовлетворяющие условиям отбора;
· Произвести вычисления в каждой из полученных записей;
· Произвести обновление полей в выбранном подмножестве записей;
· Создать новую таблицу базы данных, используя данные из существующих таблиц;
· Удалить выбранное подмножество записей из таблицы базы данных.
Так же в Access может быть создано несколько видов запроса:
· Запрос на выборку - выбирает данные из взаимосвязанных таблиц и других запросов. Результатом является таблица, которая существует до закрытия запроса.
· Запрос на создание таблицы - также выбирает данные из взаимосвязанных таблиц и других запросов, но, в отличие от запроса на выборку, результат сохраняет в новой постоянной таблице;
· Запросы на обновление, добавление, удаление - являются запросами действия, в результате выполнения которых изменяются данные в таблицах.
На данном этапе работы были разработаны следующие запросы:
1) Поставки Запрос: в этом запросе рассчитывается стоимость товара с учетом текущего курса валют и корректировки стоимости.
В него входят таблицы Поставки и Курс валют. Вычисляемые поля:
ЗакупСтоим: [Кол-о]*[Закупочная цена]
РекомендЦена: [Закупочная_цена]*Курс_валют!Курс+[Корректировка стоимости]
РекомендСтоим: [РекомендЦена]*[Кол-о]
2) Продажи Запрос: в этом запросе находится значение рекомендуемой цены для каждого продаваемого товара по последней поставке, а также рассчитывается фактическая цена и стоимость с учетом скидки или надбавки.
РекомендЦена: DLookUp("[РекомендЦена]";"[Поставки Запрос]";"[ТовЦветРазм] = " & [ТовЦвРазм] & " And [Магазин] = " & [Магазин] & " And [Дата поставки] <= #" & Format([Дата продажи];"dd\/mm\/yyyy") & "#")
ФактЦена: [РекомендЦена]-[Скидка]
ФактСтоим: [ФактЦена]*[Кол-о]
Для расчета рекомендуемой цены используется функция DLookUp(), позволяющая обратиться к полю РекомендЦена запроса Поставки Запрос с условием отбора, товар и магазин должны быть равны текущим значениям, Дата поставки должна быть меньше либо равной дате продажи.
3) Всего поступило - в этом запросе рассчитывается сколько всего поступило за все время каждого товара в каждый из магазинов (см. рис. 15).
Рис.15. Форма запрос на выборку «Всего поступило»
В запрос входят таблицы Поставки, ТоварыЦветаРазмеры и Магазины, объединенные между собой. Причем заданы особые параметры объединения, которые выводят на экран все записи таблицы Поставки и только совпадающие записи таблицы ТоварыЦветаРазмеры; все записи таблицы Магазины и только совпадающие записи таблицы Поставки. Данные в запросе сгруппированы по магазину и товару, в поле Количество рассчитывается сумма для выделенных групп с помощью групповой функции Sum.
4) Всего продано: аналогичный запрос, подсчитывающий сколько всего продано каждого товара в каждом магазине (см. рис. 16):
Рис.16. Форма запрос на выборку «Всего продано»
5) Текущие_остатки: подсчитывает текущий остаток каждого товара в каждом магазине (см. рис. 17).
Рис.17. Форма запрос на выборку «Текущее остатки»
6) Включает в себя запросы Всего поступило и Всего продано, объединенные по полям Магазин и Товар с параметрами объединения: все записи запроса Всего поступило и только совпадающие записи запроса Всего продано, так как поступление товара «первичнее» продаж.
Содержит расчетное поле Остаток:
Остаток: [Всего поступило.Sum-Кол-о]-Nz([Всего продано.Sum-Кол-о];0)
При этом суммарное количество проданного товара берется в функцию Nz(), заменяющую пустые ячейки на нули.