Лабораторная работа № 3
База данных «Поставка товаров»
База данных состоит из 7 взаимосвязанных таблиц Покупатель, Договор, Товар, Накладная, Склад, Поставка_план, Отгрузка.
Связи между таблицами изображены на логической структуре реляционной БД «Поставка товара»
Основные параметры структуры таблицы ТОВАР
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Формат | Подпись поля |
| Код_тов | Простой | Да | Текстовый | Код товара | ||
| Наим_тов | Нет | Текстовый | Наименование товара | |||
| Цена | Нет | Денежный | Денежный | Цена | ||
| ЕИ | Нет | Текстовый | Единица измерения | |||
| Ставка_НДС | Нет | Числовой | Одинарное с плавающей точкой | процентный | Ставка НДС | |
| фото | нет | Поле объекта OLE | Фото товара |
Примечание:
Для поля Ставка_НДС предусмотреть
· Условие на значение: >=0.05 and <=0.35
· Сообщение об ошибке: «Ставка НДС должна быть >=5% и <=35%»
Для поля Цена:
· Условие на значение: >=0 and <=35000
· Сообщение об ошибке: «Цена должна быть >=0 и <=35000»
Основные параметры структуры таблицы СКЛАД
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Подпись поля |
| Ном_скл | Простой | Да | Текстовый | Номер склада | |
| Код_ф | Нет | Текстовый | Код фирмы | ||
| Наим_ск | Нет | Текстовый | Наименование | ||
| Отв_лицо | Нет | Текстовый | Ответственное лицо | ||
| Адрес_ск | Нет | Текстовый | Адрес склада |
Основные параметры структуры таблицы ПОКУПАТЕЛЬ
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Подпись поля |
| Код_пок | Простой | Да | Текстовый | Код покупателя | |
| ИНН | Нет | Текстовый | Идентификационный номер | ||
| Наим_пок | Нет | Текстовый | Наименование | ||
| Адрес_пок | Нет | Текстовый | Адрес | ||
| Тел | Нет | Текстовый | Телефон | ||
| Ном_рсч | Нет | Текстовый | Номер расч. счета | ||
| Банк | Нет | Текстовый | |||
| Описание | Нет | Поле Мемо | |||
| Web-адрес | Нет | Гиперссылка |
Примечание:
1. Для поля Телефон следует задать маску ввода: \(999\)000\-0099. Если не надо вводить код города, нужно нажать 3 пробела.
2. Для поля ИНН выполнить следующие операции:
· Указать маску ввода:000000000000
· В свойстве Индексированное поле выбрать значение ДА (совпадения не допускаются).
Основные параметры структуры таблицы ДОГОВОР
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Формат | Число десятичных знаков | Подпись поля |
| Ном_дог | Простой | Да | Текстовый | Номер договора | |||
| Дата_дог | Нет | Дата/время | Краткий формат даты | Дата | |||
| Код_пок | Да | Текстовый | Код покупателя | ||||
| Сумма_дог | Нет | Денежный | Денежный | Авто | Сумма по договору |
Примечание:
Для поля Дата_дог предусмотреть маску: 00.00.0000
Основные параметры структуры таблицы Поставка_План
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Формат | Число десятичных знаков | Подпись поля |
| Ном_дог | Составной | Да | Текстовый | Номер договора | |||
| Код_тов | Да | Текстовый | Код товара | ||||
| Срок_пост | Да | Числовой | байт | фиксированный | Срок поставки (ном. месяца) | ||
| Мин_пост | Нет | Числовой | целое | Авто | Мин. партия поставки | ||
| Кол_пост | Нет | Числовой | Длинное целое | авто | Количество поставки | ||
| Сумма_пост | Нет | Денежный | Денежный | Авто | Сумма поставки |
Основные параметры структуры таблицы накладная
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Формат | Число десятичных знаков | Подпись поля |
| Ном_накл | Составной | Да | Текстовый | Номер накладной | |||
| Код_ск | Да | Текстовый | Код склада | ||||
| Дата_отг | Нет | Дата/время | Краткий формат даты | Дата отгрузки | |||
| Ном_дог | Нет | Текстовый | Номер договора | ||||
| Сумма_накл | Нет | Денежный | Денежный | Авто | Сумма по накладной |
Примечание:
Для поля Дата_отг предусмотреть ввод значения по умолчанию текущей даты: Date().
Основные параметры структуры таблицы отгрузка
| Имя поля | Признак первичного ключа | Обязательное поле | Тип данных | Размер | Формат | Число десятичных знаков | Подпись поля |
| Ном_накл | Составной | Да | Текстовый | Номер накладной | |||
| Код_ск | Да | Текстовый | Код склада | ||||
| Код_тов | Да | Текстовый | Код товара | ||||
| Кол_отгр | Нет | Числовой | Длинное целое | авто | Количество | ||
| Сумма_отгр | Нет | Денежный | Денежный | Авто | Сумма по товару |
Пример заполнения документов для загрузки БД «Поставка товаров»
Справочник товаров
| Товар | |||||
| Код товара | Наименование товара | Цена | Единица измерения | Ставка НДС | Фото товара |
| Т001 | Монитор 17LG | 6 587,00 грн. | штука | 5,00% | |
| Т002 | FDD 3,5 | 3 630,00 грн. | коробка | 20,00% | |
| Т003 | HDD Maxtor20GB | 2 590,00 грн. | штука | 10,00% | |
| Т004 | Корпус МiniTower | 1 916,00 грн. | штука | 20,00% | |
| Т005 | CD-ROM Panasonic | 1 153,00 грн. | штука | 30,00% | |
| Т006 | DIMM64MPC100 | 360,00 грн. | штука | 15,00% | |
| Т007 | Принтер HPLaserJet122C | 5 432,00 грн. | штука | 10,00% | |
| Т008 | СканерAcer | 2 338,00 грн. | штука | 15,00% | |
| Т009 | Модем Genius ext | 1 295,00 грн. | штука | 5,00% | |
| Т010 | Миникомпьютер | 35 000,00 грн. | штука | 15,00% |
Справочник складов
| Склад | ||||
| Номер склада | Код фирмы | Наименование | Ответственное лицо | Адрес склада |
| Главный | Иваненко Т.С. | Мичуринская, 15 | ||
| Оптовый | Теров А.А. | Свердлова, 29 | ||
| Торговый | Смирнова О.Н. | Речной, 38 |
Справочник покупателей
| Покупатель | ||||||||
| Код покупателя | ИНН | Наименование | Адрес | Телефон | Номер расч. счета | Банк | Описание | Web-адрес |
| П001 | Компьютер маркет | Витебская, 12 | (0552)32-67-68 | Надра | ||||
| П002 | Перспектива | Московская, 45 | (0552)55-46-89 | Приват | ||||
| П003 | Инфоцентр | Суворова, 7 | (1236)12-45-89 | Аваль | ||||
| П004 | Монитор | Ушакова, 81 | ()55-14-23 | Приват | ||||
| П005 | Компьютер лэнд | Ладычука,89 | ()12-36-54 | Аваль |
| Договор | |||
| Номер договора | Дата | Код покупателя | Сумма по договору |
| Д111 | 05.07.2006 | П001 | 60 000,00 грн. |
| Д222 | 12.07.2006 | П003 | 2 000,00 грн. |
| Д333 | 25.07.2006 | П001 | 15 000,00 грн. |
| Д444 | 02.08.2006 | П004 | 8 000,00 грн. |
| Д555 | 11.08.2006 | П005 | 10 000,00 грн. |
| Поставка_план | |||||
| Номер договора | Код товара | Срок поставки (ном. месяца) | Мин. партия поставки | Количество поставки | Сумма поставки |
| Д111 | Т001 | 7,00 | 10 000,00 грн. | ||
| Д111 | Т002 | 7,00 | 2 000,00 грн. | ||
| Д222 | Т004 | 8,00 | 5 600,00 грн. | ||
| Д333 | Т006 | 8,00 | 3 000,00 грн |
| Отгрузка | ||||
| Номер накладной | Код склада | Код товара | Количество | Сумма по товару |
| Т001 | ||||
| Т003 | ||||
| Т005 | ||||
| Т004 |
В таблице Отгрузка поле Сумма по товару заполнить с помощью запроса на обновление.
Запросы.
1. Определить, какое суммарное количество каждого из товаров должно быть поставлено покупателям по договорам. (Все данные указаны в таблице Постака_план, используем функцию Sum для сгруппированных данных).
2. Подсчитать количество товаров, заказанных в заданном месяце. (Предыдущий запрос + срок поставки, параметр «Введите месяц»).
3. Определите, сколько раз отгружался товар по каждому договору. Факт отгрузки фиксируется документом Накладная.
4. Подсчитаем сколько накладных было выписано по каждому из договоров и какова общая стоимость товаров, отгруженных по этим накладным. В расчете будем учитывать только накладные на сумму более 10000 грн.
5. Необходимо выбрать договор, по которому отгружен товар на максимальную сумму.
6. Откройте запрос, созданного в пункте № 4, в режиме конструктора. Перейдите в режим SQL и удалите из списка полей Код_тов. Вернитесь в режим конструктора и убедитесь в произошедших изменениях.
7. Необходимо получить информацию о товарах, запланированных к поставке покупателям по всем договорам. (Многотабличный запрос. Таблицы: Покупатель, Договор, Поставка_план, Товар. Таблица Договор нужна для связи таблиц Покупатель и Поставка_план). Внесите изменения в запрос, если нужно получить информацию о поставке конкретного товара.
8. На основе предыдущего запроса создайте новый запрос, который подсчитывает общее количество товаров, отгруженных в указанный период.
9. Необходимо произвести анализ выполнения договоров на поставку товаров на конец заданного месяца. При решении этой задачи должно быть подсчитано количество товара, запланированного к поставке, отгруженного покупателям в соответствии с договорами, и получена величина недопоставки товаров.
10. Проанализируйте план поставок товара заданному покупателю.
11. Проанализируйте план поставок товара в стоимостном выражении.
12. Подсчитайте, на какую сумму отгружен товар покупателям, которые не заключали договоров.
13. Подсчитайте, на какую сумму покупатели недополучили товар на текущую дату.
14. Создайте таблицу Отгрузка склада по аналогии с таблицей Отгрузка. Названия ключевых полей должны совпадать. Введите новые данные в таблицу Отгрузка склада. Создайте запрос на добавление записей из таблицы Отгрузка склада в таблицу Отгрузка.
15. Необходимо удалить из Справочника запись о покупателе с кодом П003.
16. Необходимо удалить договор некоторого покупателя, заключенного в конкретную дату. Данные о договорах хранятся в таблицах Договор и Поставка_план. (сначала удаляем записи из Поставка_план, используя данные из Договора, а затем удаляет из бланка запроса Поставка_план и удаляем записи из договора. Запросы: Удаление_запрос1 и Удаление_запрос2).
17. С помощью Мастера создания запросов создайте запрос для подсчета суммарного количества товаров, отгруженных в каждом из месяцев учетного периода:
· выбрать поле Дата_отгр из таблицы Накладная; поле Кол_отгр из таблицы Отгрузка и поле Наим_тов из таблицы Товар;
· итоговый отчет и функцию Sum для поля Кол_отгр;
· интервал группировки дат – по месяцам.
18. Найдите товары, на поставку которых не было заключено договоров. В качестве анализируемой таблицы выберите таблицу Товар, подчиненной – Поставка_план. Для связи выберите поле Код_тов. Запрос создайте с помощью мастера – команда Создать\Записи без подчиненных.
19. Создать перекрестный запрос для вычисления количества товаров, запланированных к поставке по каждому договору:
- в основу положить таблицу Поставка _ план.
- для заголовок строк – поле Код_тов;
- заголовки столбцов – Ном_дог;
- вычисляемое поле – Кол_пост, функция Sum;
- отметить пункт Вычислить итоговые значения для каждой строки.
20. В предыдущий запрос внести изменения, чтобы в результирующей таблице отображались не только номера договоров, но и наименования покупателей. Предусмотрите возможностьфильтрации данных по Наименованию товара. Перед созданием перекрестного запроса надо создать простой запрос на основе таблиц Поставка_план, Договор, Товар и Покупатель:
- выбрать поле Наим_пок (из Покупатель), поле Ном_дог (из Договор); поле Кол_пост (из Поставка_план); поле Наим_тов (из Товар);
- перекрестный запрос строится на основе построенного простого запроса;
- для заголовков строк выбирают поля: Наим_пок и Ном_дог;
- далее по предыдущей схеме;
- для фильтрации данных в режиме конструктора надо описать параметр [Наименование товара]. Обязательно описать параметр в меню Запрос\Параметр.
Формы
1. С помощью команды Автоформа: в столбец создайте форму для таблицы Товар. В режиме конструктора форм внесите следующие изменения:
- создайте заголовок «Справочник товаров»;
- поменяйте положение надписей отдельно от привязанных к ним полям;
- отформатируйте объекты формы.
2. Создайте однотабличную форму для таблицы Договор с помощью мастера создания форм, отредактируйте ее в режиме конструктора и произведите добавление нескольких записей.
3. Создайте многотабличную форму для двух взаимосвязанных таблиц с помощью мастера.
4. Построить форму для двух таблиц Склад и Накладная, в качестве источника данных использовать построенный в режиме конструктор формы соответствующий запрос.
5. Создайте однотабличную форму для таблицы Покупатель. В режиме конструктора встройте в эту форму ранее созданную форму Договор в качестве подчиненной.
6. В режиме конструктора формы Товар встроим подчиненную форму для таблицы Поставка_план.
7. На основе таблиц Поставка_план и Товар в форме создать вычисляемое поле для расчета суммы минимальной поставки каждого товара по договору.
8. Создать двухтабличную форму, построенную на основе таблиц Договор и Поставка_план. На основе связанных записей подчиненной формы подсчитать сумму поставок по каждому договору. Результат вычисления вынести в основную форму.
9. Создайте интерфейс для ввода в базу данных накладных на отгрузку товаров. Основные элементы интерфейса показаны в форме на рис.
При создании формы произведите действия, аналогичные рассмотренным для формы по договорам. В результате загрузки в БД данных по накладным должны создаваться только записи таблиц накладная и отгрузка. В форме необходимо предусмотреть только отображение справочных данных по договорам, покупателям и товарам, без возможности ввода или изменения данных.
Осуществите проектирование интерфейса для загрузки данных документа Накладная в соответствии с технологией загрузки базы данных:
· определите общую структуру составной формы в соответствии с подсхемой данных для формы;
· разместите реквизиты в проекте формы так, чтобы обеспечить удобный ввод данных из документа и отображение справочной информации.
Выполните конструирование экранной формы, через которую будут осуществляться ввод, добавление и изменение записей таблиц накладная и ОТГРУЗКА.
Загрузите данные из документа «Накладная» с помощью построенной формы.
| Накладная | ||||
| Номер накладной | Код склада | Дата отгрузки | номер договора | Сумма по накладной |
| 23.07.2006 | Д111 | 112 000,00 грн. | ||
| 03.07.2006 | Д222 | 10 000,00 грн. | ||
| 23.08.2006 | Д222 | 5 000,00 грн. | ||
| 13.07.2006 | Д333 | 44 520,00 грн. | ||
| 05.08.2006 | Д111 | 2 500,00 грн. | ||
| 05.06.2006 | Д555 | 10 000,00 грн. |
Сводные таблицы.
- Создать сводную таблицу Отгрузка для подсчета общего количества каждого из товаров (используйте поля Код товара, Количество отгруженного товара, Код склада).
- Создайте запрос Отгрузка товаров, в результате работы которого образуется виртуальная таблица с полными сведениями об отгрузках. Для этого воспользуйтесь таблицами Отгрузка, Товар, Накладная, Договор и Покупатель. Преобразуем запрос в сводную таблицу для подсчета промежуточных и общих итогов для выбранных в строках, столбцах и фильтрах значений. В область фильтров перетащите поле Дата отгрузки по месяцам. С помощью сводной таблицы проанализируйте количество каждого из товаров, отгруженных по каждому из договоров, и по всем договорам в целом в разрезе дат отгрузки. Произведите анализ отгрузок по заданному товару, по заданному договору, за заданный период (за квартал, за год и т.д.).
- Создайте сводную диаграмму Отгрузка товара на основе сводной таблицы.
Отчеты.
1. Создание однотабличного отчета.
§ В таблицу Покупатель в поле Адрес_пок добавьте названия городов.
§ Создайте однотабличный отчет с помощью мастера на основе таблицы Покупатель..в отчет должны войти поля Код_пок, Наим_пок, банк, ном_рсч, адрес_пок
§ Сгруппируем записи по полю адрес_пок Т.к. в поле адрес_пок хранится не только название города, но название улицы, номер дома. Обычная группировка по полю не даст нужного результата. В окне Интервалы группировки выберем интервал по 4 первым буквам. На основе сделанного выбора в заголовок группы мастер поместит выражение «Left ([адрес_пок];4), которое возвратит первые 4 буквы из значения адреса.
§ Добавьте в отчет вычисляемое поле для подсчета числа покупателей в каждом городе, используйте статистическую функцию Count.
2. в отчет предполагается выводить данные по каждому договору, включая сведения о покупателе, заключившем договор. Эти данные содержатся в таблицах договор и покупатель. В табличной части по каждому договору необходимо вывести построчно данные о заказанных товарах, которые содержатся в таблицах поставка_план и товар.
Для создания отчета мастером выберем в окне базы данных объект Отчеты (Reports), а затем щелкнем на строке Создание отчета с помощью мастера в поле, где формируется список создаваемых отчетов. Если создание отчета начать нажатием кнопки Создать (New), в открывающемся окне Новый отчет (New Report) необходимо выбрать строку Мастер отчетов (Report Wizard). Уже в этом окне можно выбрать одну из таблиц, необходимых для формирования источника записей отчета, например, договор.
После выбора в качестве инструмента создания отчета мастера открывается окно Создание отчетов (Report Wizard), в котором требуется выбрать таблицы и поля, включаемые в отчет.
В примере в соответствии с проектом отчета выберем таблицы:
Ø договор, из нее поля:
• номер договора — ном_дог;
• дата заключения договора — дата_дог;
Ø поставка_план, из нее поля:
• срок поставки — срок_пост;
• минимальная партия поставки — мин_пост;
• количество товара — кол_пост;
Ø товар, из нее поля:
• наименование товара — наим_тов;
• цена — цена;
• единица измерения — ей;
• ставка НДС - ставка_ндс;
Ø покупатель, из нее поля:
• идентификационный номер налогоплательщика — инн;
• наименование покупателя — наим_пок;
• адрес покупателя — адрес_пок;
• телефон — тел;
• банк, который обслуживает счет покупателя — банк;
• номер расчетного счета покупателя — ном_рсч.
Первая выбранная таблица определит имя и заголовок отчета по умолчанию, а также вид представления данных в отчете. При выборе взаимосвязанных таблиц мастер отчетов в соответствии со схемой базы данных автоматически установит связи между ними и построит запрос, объединяющий записи этих таблиц и включающий в таблицу запроса указанные поля. Объединение записей таблиц будет производиться способом, указанным параметрами объединения для каждой из связей в схеме БД.
Для данного примера выберем таблицу Договор в качестве основы главной части отчета, мастер на основе полей этой таблицы осуществит группировку. Причем к полям группировки будут отнесены и поля таблицы Покупатель, т.к. эта таблица является главной по отношению к таблице Договор. Если, выбирая вид представления данных остановиться на таблице Покупатель, мастер создаст 2 уровня группировки: 1- данные о покупателе, 2- его договоры.
Поля таблицы Поставка_план составят основу записей для построения табличной части отчета. Причем записи о товарах будут дополнены реквизитами из таблицы Товар.
Внимание:
Группировка в отчете, в отличие от группировки в запросе, не приводит к образованию для группы одной записи. В отчете только поля группировки выводятся один раз в заголовке группы, а все остальные поля составляют записи группы, размещаемые в области данных в табличной части отчета.
В диалоговом окне мастера выберем альбомную ориентацию страниц отчета и зададим настройку ширины полей для размещения на одной странице.
Создать главную кнопочную форму, позволяющую запускать формы, отчеты и запросы.
Форма должна состоять из нескольких страниц, на которых расположены кнопки к объектам, сгруппированным по функциям и работа с данной базой данных должна автоматически начинаться с кнопочной формы.