Пример выполнения контрольной работы

Задача. Три деревообрабатывающих предприятия поставляют MDF четырем мебельным фирмам. Таблица Предприятие имеет поля: Код предприятия, Название предприятия, Производительность в м² (за месяц). Таблица Фирма имеет поля Код фирмы, Название фирмы, Ежемесячная потребность фирмы в MDF. Таблица Продажи имеет поля Код предприятия, Код фирмы, Количество проданного MDF фирме за месяц. Создать базу данных «Фирма» и ответить на запросы:

 

1. Выдать список предприятий, которые продали MDF 1-й и 2-й фирмам, в каком количестве и на какую сумму.

2. Выдать список предприятий, у которых осталось не проданной MDF в конце месяца, выдать количество остатка в м².

 

Решение.

I Создаем структуру базы данных. Проектируем таблицы, соблюдая правила нормализации, рисунок 42.

Таблица Предприятие		Таблица Фирма		Таблица Продажи
Код предприятия		Код фирмы		Код предприятия
Название предприятия		Название фирмы		Код фирмы
Производительность		Потребность		Количество
Цена за 1кв.м

 

Рисунок 42 – Структура таблиц базы данных

 

В таблице Предприятие для однозначности идентификации предприятия используется поле КП(код предприятия), оно в этой таблице будет ключевым. В таблице Фирма ключевым полем будет код фирмы КФ. Тогда для уникальной идентификации фирмы и предприятия (какая фирма покупала продукцию и у какого предприятия), в таблице Продажи ключ будет составной (из двух полей) КП+КФ.

II Создаем таблицы в Microsoft Access. Запускаем программу Microsoft Access, в диалоговом окне отмечаем Новая БД, в следующем окне указываем папку и имя файла нашей базы данных – Фирма. Открывается окно базы данных, выбираем объект Таблицы. Выбираем режим создания таблицы, для этого 2 раза щелкаем по пункту Создание таблицы в режиме Конструктора.Сначала создаем таблицу Предприятие. Вводим имена полей, выбираем тип данных, размер поля, подпись поля. Для определения первичного ключа таблицы выделим поле КП, нажимаем на панели инструментов кнопку (ключевое поле), слева от имени поля должно появиться изображение ключа, см. рисунке 43. Имена полей таблиц следует сократить, а именно: Код предприятия на КП, Название предприятия на НП, Производительность на Произв. В таблице Фирма имена полей соответственно сократим так: Код фирмы на КФ, Название фирмы на НФ, Потребность.

Рисунок 43 - Таблица Предприятие в режиме конструктора

 

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

 

III Создаем схему данных. Это наглядное средство графического отображения логических связей таблиц базы данных.

1 Открываем окно Схема данных.Для этого нажимаем кнопку на панели инструментов , или в главном меню выбираем пункт Сервис-Схема данных.

2 В диалоговом окне Добавление таблицы выбираем вкладку Таблицы и кнопкой Добавить размещаем все 3 таблицы. Нажимаем на кнопку Закрыть.

3 Устанавливаем связь между таблицами Предприятие и Продажи по ключу НП.Для этогов окне Схема данных устанавливаем курсор мыши на ключевом поле НП таблицы Предприятие и перетаскиваем это поле на поле НП таблицы Продажи.

4 В открывшемся окне Изменение связейв строке Тип отношения установится один-ко-многим. Далее следует отметить флажки, обеспечивающие Целостность данных.

5 Устанавливаем связь между таблицами Фирма и Продажи по ключу КФ.Для этогов окне Схема данных устанавливаем курсор мыши на ключевом поле КФ таблицы Фирма и перетаскиваем это поле на поле КФ таблицы Продажи.

6 В открывшемся окне Изменение связейв строке Тип отношения установится один-ко-многим. Далее следует отметить флажки, обеспечивающие Целостность данных, рисунок 44.

 

 

Рисунок 44 - Сема данных базы данных Фирма

 

IV Заполняем таблицы данными. Открываем таблицу Предприятие в режиме таблицы (2 раза мышью по названию таблицы в окне БД) и вводим записи. После окончания ввода сохраняем и закрываем таблицу. Таким же образом заполняем таблицы Фирма и Продажи рисунки 45,46,47.

 

Рисунок 45 - Таблица Предприятие

 

 

Рисунок 46 - Таблица Фирма

 

 

Рисунок 47 - Таблица Продажи

 

V В Access имеется возможность при просмотре записей главной таблицы отображать записи подчиненной таблицы. Посмотрим это на примере таблиц Предприятие и Продажи. Открываем таблицу Предприятие в режиме Таблица и мы видим, что она имеет самый крайний левый столбец с плюсами. Щелкните по значку «+» и отобразятся записи подчиненной таблицы Продажи, рисунок 48, при этом знак «+» меняется на «-».

 

 

Рисунок 48 - Отображение подчиненных записей в главной таблице

 

VII Создаем 1-й запрос (Поставки 1-й и 2-й фирмам): выдать список предприятий, которые продали MDF 1-ой и 2-ой фирмам, в каком количестве и на какую сумму. Выполним следующие действия:

1 в окне базы данных в списке объектов выбираем объект Запрос;

2 дважды щелкнуть по значку Создание запроса в режиме конструктора;

3 в появившемся окне Конструктора запросов, в окне Добавление таблицы, выделить таблицы Предприятие и Продажи и щелкнуть по кнопке Добавить, закрыть окно Добавление таблицы;

4 далее надо перетащить мышью из таблиц в верхней области запроса в строку Поле нужные поля, а именно поле НП (название предприятия) из таблицы Предприятие, поля КФ (код фирмы) и Колич из таблицы Продажи;

5 в строке Условие отбора задаем критерии для отбора записей по полю КФ и записываем 1-е условие – 1Ф, а в строке Илизаписываем 2-е условие - 2Ф; После выполнении запроса Access сам записывает выражение “1Ф” or “2Ф”;

6 чтобы вычислить на какую сумму было продано MDF каждым предприятием надо создать вычисляемое поле Сумма: [Предприятие]![Цена за 1квм]*[Продажи]![Колич]. Это поле создаем с помощью построителя, рисунок 49;

 

Рисунок 49 - Вычисляемое поле Сумма в построителе

 

7 определяем сортировку по полю НП и по полю КФ,для этого в строке Сортировка отмечаем по возрастанию рисунок 50;

8 Закрыть запрос и дать ему имя «Поставки 1-й и 2-й фирмам». Для выполнения запроса можно нажать на кнопку на панели инструментов , если он открыт в режиме конструктора, если надо открыть запрос в окне базы данных, то щелкнуть правой кнопкой мыши по названию запроса, далее выбрать пункт Открыть, рисунок 51.

 

 

Рисунок 50 - 1-й запрос в режиме конструктора

 

 

Рисунок 51 - Результат выполнения 1-го запроса

 

VIII Создаем 2-й запрос (Остаток): выдать список предприятий, у которых осталось не проданной MDF в конце месяца, указать количество остатка в м² на каждом предприятии

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

1 Создаем запрос Продано продукции. В окне базы данных в списке объектов выбираем объект Запрос;

2 дважды щелкаем по значку Создание запроса в режиме конструктора;

3 в появившемся окне Конструктора запросов, в окне Добавление таблицы, выделить таблицы Предприятие и Продажи и щелкнуть по кнопке Добавить, закрыть окно Добавление таблицы;

4 далее надо перетащить мышью из таблиц в верхней области запроса в строку Поле нужные поля, а именно поле НП (название предприятия) и поле Производ (производительность) из таблицы Предприятие, поле Колич из таблицы Продажи;

5 нажать кнопку Групповые операции на панели инструментов, в поле Колич выбрать функцию Sum;

6 закрыть запрос и дать ему имя Продано продукции рисунок 52;

7 создаем запрос Остаток, для этого открываем Конструктор запросов, в окне Добавление таблицы, добавить таблицу Предприятие, открыть вкладку Запросы и добавить запрос Продано продукции, закрыть окно Добавление таблицы;

8 соединить эти таблицы по полю НП (название предприятия), буксируя левой кнопкой мыши поле НП из таблицы Предприятие на поле НП таблицы Продано продукции.

9 перетащить мышью из таблицы Предприятие поле НП в строку Поле, а второе поле Остаток будет вычисляемое: [Предприятие]![Производ]-[Продано продукции]![Sum-Колич] его надо создать в построителе.

10 задать группировку, щелкнув по кнопке . Закрыть запрос и дать ему имя Остаток рисунок 53.

 

 

Рисунок 52 - Запрос Продано продукции в режиме конструктора

 

 

Рисунок 53 - Запрос Остаток в режиме конструктора

 

IX Создаем форму.

Будем создавать многотабличную форму на основе таблиц Предприятие, Фирма и Продажи. Для этого выполняем следующие действия:

1 на панели объектов выбрать объект Формы, щелкнуть по кнопке Создать;

2 в появившемся окне Новая форма выбрать режим создания Мастер форм и таблицу Предприятие – источник записей для основной части создаваемой формы, щелкнуть по кнопке Ok; запустится Мастер;

3 выбрать поле НП из таблицы Предприятие и переместить его с помощью кнопки «>» в область Выбранные;

4 в этом же окне выбрать из списка Таблицы и Запросы таблицу Фирма и переместить из нее поля КФ, НФ, далее выбрать таблицу Продажи и переместить полеКолич;

5 в следующем окне выбрать вариант отображения подчиненной формы (переключатель Подчиненные формы), щелкнуть по кнопке Далее;

6 в следующем окне выбрать вид подчиненной формы, кнопка Далее;

7 ввести имена составной формы и подчиненной, кнопка Готово;

X Доработка формы в режиме конструктора

1 открыть форму в режиме конструктора;

2 оформляем заголовок формы. Для этого в разделе Заголовок формы выбрать на панели элементов элемент Надпись , мышью вычертить рамку и ввести текст заголовка (Объем продаж каждым предприятием), выбрать шрифт, тип выравнивания;

3 сокращенные названия полей переписать на полные, а именно: НП на Название предприятия, НФ на Название фирмы, КФ на Код фирмы, Колич на Количество MDF, поле Продажи удалим;

4 в подчиненной форме в области примечаний создадим вычисляемое поле Общее количество MDF, проданное предприятием. Для этого нажав на кнопку , мышью вычерчиваем поле, щелкнув правой кнопкой мыши по полю «Свободный», выбираем в появившемся меню команду Свойства.

5 В появившемся окне открыть вкладку Данныеи ввести в строку Данные выражение (можно воспользоваться построителем), =Sum([Продажи]![Колич]);

6 создаем кнопки перехода по записям, для этого выбираем элемент Кнопка на панели элементов, вычерчиваем в области Примечаний основой формы рамку, запускается Мастер кнопок, следуем указаниям Мастера (см. раздел 2.3.1.3).

7 сохраняем форму и открываем в режиме просмотра, рисунок 54.

 

Рисунок 54 - Форма

 

X Создаем отчет

1 Создаем макет отчета. Из макета видно, что надо группировать по полю НП(название предприятия) и по полю Цена за 1квм,рисунок 55;

 

Список предприятий и объем продаж
Название предприятия	Цена за 1 м2	Название фирмы	Количество
ДОЗ		Мебельная фабрика 2
		Мебельная фабрика 3
		Мебельная фабрика 4
Общее количество MDF
ДОК	--------	---------------	------------
И т.д.	--------	---------------	------------

 

Рисунок 55 Макет отчета

 

2 в основном окне базы данных выбрать объект Отчет и нажать на кнопку Создать. В появившемся окне выбрать Мастер отчетов;

3 в окне Создание отчета выбрать таблицу Предприятие. Щелчком мыши по кнопке со стрелкой (>) перенести поля НП и Цена за 1кв.м. Далее выбрать таблицу Фирма и перенести поле НФ (название фирмы), выбрать таблицу Продажи и перенести поле Колич. Нажать кнопку Далее;

4 во втором окне Мастер предлагает выбрать вид представления данных и выбирает по умолчанию одно из полей таблицы в качестве поля, по которому будет выполняться группировка данных. Но уровни группировки мы укажем в следующем окне, а здесь надо щелкнуть мышью по названию таблицы Продажи и все поля будут на одном уровне, рисунок 56;

 

Рисунок 56 - 2-ое окно Мастера отчетов

 

5 в третьем окне Мастера необходимо указать уровни группировки, щелкаем мышью по полю НП, затем по полю Цена за1квм, рисунок 57;

 

 

Рисунок 57 - Мастер отчетов на 3-ем шаге

6 на 4-ом шаге отмечаем сортировку по полю НФи нажав кнопку Итоги, отмечаем функцию Sum по полю Колич;

7 на 5-ом шаге выбираем вид макета и ориентацию страницы – книжная;

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

 

XI Редактируем отчет в режиме конструктора

1 Открываем отчет в режиме конструктора и с помощью кнопки Поле в области Заголовок отчета записываем заголовок «Список предприятий и объем продаж». Редактируем его, т.е. назначаем нужный шрифт, тонировку, можно подчеркнуть, сделать заливку и т.д;

2 в верхнем колонтитуле название поля НП меняем на Название предприятия, название поля НФна Название фирмы, форматируем надписи;

3 в области Примечание группы НП надпись на вычисляемом поле Sum, меняем на Общее количество MDF;

4 сохраняем отчет, даем имя и открываем в режиме просмотра, рисунок 58.

 

 

Рисунок 58 - Отчет в режиме просмотра.