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

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

Для создания базы данных «Заказы» используем СУБД  MS Access.

Через меню «Пуск» → «Программы» →  MS Access →  в меню «Файл» выбираем → создать → новая база данных. Откроется окно для сохранения базы данных в котором необходимо выбрать название базы данных -  в нашем случае «Заказы» и сохранить пустую базу данных.

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

2.Таблицы.

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

Открывается окно конструктора таблицы. В этом окне выбираем поля для нашей первой таблицы «Поставщики»:

- имя поля – код отдела                                           - тип данных – счетчик                                - название отдела                                                                                - текстовый

- заведующий отделом                                                                        - текстовый

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

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

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

     Для создания таблицы «Товары» выполняем  аналогичные действия. Выбираем поля:

- код отдела (счетчик)

- код товара (счетчик)

- цена товара (денежный).

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

     Таким же образом создаем третью таблицу «Поступления» с полями:

- Код отдела (числовой)

- код товара (числовой)

- количество (числовой)

- дата поступления (дата/время).

    При этом для поля  «дата поступления»  используем маску ввода. В нижней левой части  окна конструктора представлены свойства полей. Во вкладке «общие» можно задать маску ввода (например, для типа данных  «дата/время»  с клавиатуры 00.00.00 или используя, мастер).

    Поскольку таблица  «Поступления»  является подчиненной - она  не будет иметь ключевых полей.  Для полей «код отдела»  и  «код товара» задается  мастер подстановок из соответствующих главных таблиц «Отделы»  и  «Товары». Для задания мастера подстановок  выбираем в нижней левой части окна конструктора вкладку «подстановка» → тип элемента управления (из списка) – «поле со списком», источник строк  (из списка) – «таблица Отделы» для кода отделы и соответственно таблица Товары  для поля «код товара». Затем сохраняем таблицу «Поступления».

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

     Аналогичным образом создаем связь между таблицами Товары (главная) и Поступления (подчиненная). При этом связываем поля «код товара» с полем «код товара».  Обе связи – один (гл. таблицы)  ко многим (в подчиненных таблицах).

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

3. Формы

    Форма – это объект базы данных, предназначенный для ввода данных в таблицу.

Для заполнения таблиц мы создадим, и будем использовать соответствующие формы: Отделы, Товары и Поступления.

    Для создания форм на вкладке «Формы» окна базы данных нажимаем кнопку «Создать» и выбираем Мастер форм. Открывается окно мастера форм.

    В этом окне в строке Таблицы и запросы выбираем таблицу «Отделы», далее в строке доступные  поля – выбираем все поля соответствующие таблице Отделы (кнопкой «двойная стрелка» переносим их в правую часть окна) → нажимаем «Далее» → выбираем вид формы «табличный» → далее → стиль «стандартный» → задаем имя формы «Отделы» → подтверждаем кнопкой «готово». Форма «Отделы» готова для заполнения данными. С клавиатуры вводим необходимые данные в форму «Отделы». При этом колонка код отдела заполняется автоматически (т.к. тип поля счетчик). После заполнения этой формы информация автоматически попадает в таблицу «Отделы».

    Аналогичным образом создаются и заполняются Формы «Товары»  и  «Поступления». Информация из форм попадает в соответствующие таблицы.

4.Запросы

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

Запрос 1 должен включать поля: название отдела, товар, цена, количество, дата. Условие отбора – товары одного отдела. Предусмотреть ввод названия отдела с клавиатуры.

  Для этого во вкладке «Запросы»  выбирает кнопку «создать» → конструктор → открывается окно «Добавление таблиц» → кнопкой добавить выбирает таблицы поля, которых необходимы по условиям Запроса (нам необходимо «добавить все три таблицы), затем окно закрываем. В верхней части окна «запрос на выборку» появляются таблицы Отделы, Товары и Поступления, затем в нижней половине окна в строке «поле» выбираем необходимые нам поля, в строке таблицы – из каких таблиц берутся эти поля. Для ввода названия отдела с клавиатуры в столбце содержащем поле «название отдела» в строке условия отбора – ввести с клавиатуры текст в квадратных скобках [введите название отдела].

Запрос 2  создается аналогично запросу 1 и включает поля: название отдела, товар, цена, количество, дата. Условием отбора являются товары с ценой более 1000 и менее 15    рублей. Так же необходима сортировка по отделам. Для сортировки по названию отдела необходимо в строке «сортировка»  столбца  «название отдела» – выбрать сортировку. Для условия отбора по цене в столбце «цена»  в строке «условия отбора» с клавиатуры вводим  >1000 And < 15000.

Запрос 3 создаем итоговый запрос с полями: название отдела, товар и цена. Для этого создаем запрос с необходимыми полями аналогично двум предыдущим запросам и сохраняем его. 

    Для того чтобы выводилась информация по отделам о товарах с максимальной ценой на основе получившегося запроса строим новый. Во вкладке «Запросы»  при помощи кнопки «создать» создаем новый запрос  → в открывшемся окне выбираем способ создания → «простой запрос» → ОК. Открывается новое диалоговое окно, в котором выбираем в строке таблицы и запросы – Запрос 3 (сохраненный на 1 этапе), в окне «доступные  поля» выбираем все поля, содержащиеся в Запросе 3 → Далее → выбираем Итоговый   и подтверждаем нажатием на кнопку «Итоги» → Далее → открылось окно Итоги, в котором в строке цена выбираем «Max» (отмечаем птичкой) и подтверждаем нажатием ОК  → Далее → выбираем имя запроса «Запрос 3» и подтверждаем кнопкой «Готово». Итоговый Запрос 3 – готов.

Запрос 4   в данном запросе необходимо предусмотреть поля: название отдела, товар, цена, количество, дата. А так же необходимо создать новое вычисляемое поле «Стоимость товара» (произведение цены на количество). Для создания вычисляемого поля используем построитель выражений.

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

Запрос 4.

5. Отчеты

    На основе Запроса 4 необходимо создать   Итоговый отчет, который содержит сгруппированные по отделам сведения о товарах, а так же суммарную стоимость товаров в рублях по каждому отделу и по всей базе данных в целом.

   Во вкладке Отчеты выбираем команду Создать. В появившемся окне «Новый отчет»  выбираем способ создания  (мастер отчетов) и Запрос 4 (как основу для отчета), подтверждаем ОК.

    Открывается окно «Создание отчетов» в котором выбираем поля для нашего отчета (все) → Далее →  выбираем уровень группировки – Название отдела (т.к. по заданию необходимо сгруппировать по отделам) →  Далее → выбираем порядок сортировки -  название товара, цена, количество, стоимость и нажимаем на кнопку «ИТОГИ». Открывается новое окно, где в строке Стоимость выбираем (отмечаем птичкой) клеточку Sum (для того чтобы в отчетах суммировалась стоимость) и подтверждаем ОК→ Далее →  выбираем макет и стиль для нашего отчета (это можно делать произвольно). После этого Выбираем имя и сохраняем отчет нажатием кнопки «Готово». Наш отчет с необходимыми параметрами готов.

ОТЧЕТ  о  товарах

Название отдела              Название товара              Цена товара                Количеств              Стоимость

Бакалея              Гречка              3 800,00р.              30              114 000,00р.

              Макароны              2 900,00р.              30              87 000,00р.

              Мука пшеничная              2 300,00р.              20              46 000,00р.

              Рис длиннозернистый              5 000,00р.              20              100 000,00р.

Итоги для 'Название отдела' =  Бакалея (4 записей)

Sum              347000

Вино-водочный              Вино Мерло              14 200,00р.              20              284 000,00р.

              Водка Гомель              6 200,00р.              50              310 000,00р.

              Водка Минск              7 500,00р.              12              90 000,00р.

Итоги для 'Название отдела' =  Вино-водочный (3 записей)

Sum              684000

Детское питание              Каша овсяная              6 500,00р.              15              97 500,00р.

              Молоко детское              560,00р.              10              5 600,00р.

              Смесь Тонус-1              4 000,00р.              20              80 000,00р.

              Смесь Тонус-2              4 000,00р.              20              80 000,00р.

Итоги для 'Название отдела' =  Детское питание (4 записей)

Sum              263100

Колбасный              Колбаса в/к              26 000,00р.              30              780 000,00р.