Технологии обработки экономической информации в различных средах

Задание 3 Технологии обработки данных в среде СУБД MS Access и использования языка запросов SQL, как средства расширения возможностей СУБД.

Условие

Вариант 4

Для анализа реализации продуктов отделами магазина по декадам месяца создать БД, содержащую следующие данные:

1) «Код отдела магазина»;

2) «Название отдела магазина»;

3) «Наименование продукта»;

4) «Реализация за 1 декаду, млн.р.»;

5) «Реализация за 2 декаду, млн.р.»;

6) «Реализация за 3 декаду, млн.р.»;

7) «Реализация за месяц, млн.р.»*.

В таблицу Справочник включить данные 1 и 2, а в таблицу Сведения - 1 и 3-7. Предусмотреть не менее трех отделов магазина, каждым из которых реализуется не менее четырех наименований продуктов.

Решение

1. Разработаем таблицы, на основании которых будем создавать базу данных:

Таблица Справочник

Таблица Сведения

2. С помощью конструктора СУБД MS Access создадим две таблицы: таблицу с именем Справочник и таблицу с именем Сведения как указано на рисунках ниже. Определим типы данных каждого поля.

В таблице Справочник:

поле [Код отдела магазина] определим целым типом,

поле [Наименование отдела магазина] - символьным типом с размером 80 символов.

поле [Код отдела магазина] определим ключевым.

Рис. 1 - Таблица Справочник в режиме конструктора СУБД ACCESS

В таблице Сведения:

поле [Код отдела магазина] определим целым типом;

поле [Наименование продукта] - символьным типом со 80 символами;

поля [Реализация за 1 декаду, млн р], [Реализация за 2 декаду, млн р], [Реализация за 3 декаду, млн р], [Реализация за месяц, млн р] - числовым вещественным типом.

Рис. 2 - Таблица Сведения в режиме конструктора СУБД ACCESS

· Команда CREATE TABLE, определяющая структуру таблицы Справочник, на языке SQL ANSI имеет вид:

CREATE TABLE Справочник

([Код отдела магазина] INT CONSTRAINT Ключ PRIMARY KEY,

[Название отдела магазина] CHAR(80));

· Команда CREATE TABLE, определяющая структуру таблицы Сведения, на языке SQL ANSI имеет вид:

CREATE TABLE Сведения

([Код отдела магазина] INT,

[Наименование продукта] CHAR(80),

[Реализация за 1 декаду, млн р] REAL,

[Реализация за 2 декаду, млн р] REAL,

[Реализация за 3 декаду, млн р] REAL,

[Реализация за месяц, млн р] REAL);

3. В режиме таблицы СУБД ACCESS заполним таблицы конкретными значениями данных, исходя из их смысла. Поле, помеченное знаком* ([Реализация за месяц, млн р]), оставим незаполненным. В результате таблицы приобретут вид, как показано выше.

· Команда заполнения базы данными INSERT INTO (для двух записей таблицы Справочник), записанная на языке SQL ANSI, имеет вид:

INSERT INTO Справочник

VALUES (1, "Галантерея");

INSERT INTO Справочник

VALUES (2, "Бытовая техника");

· Команда заполнения базы данными INSERT INTO (для двух записей таблицы Сведения), записанная на языке SQL ANSI, имеет вид:

INSERT INTO Сведения

( [Код отдела магазина], [Наименование продукта],

[Реализация за 1 декаду, млн р], [Реализация за 2 декаду, млн р],

[Реализация за 3 декаду, млн р] )

VALUES (1, "Набор для шитья", 11, 0, 9);

INSERT INTO Сведения

( [Код отдела магазина], [Наименование продукта],

[Реализация за 1 декаду, млн р], [Реализация за 2 декаду, млн р],

[Реализация за 3 декаду, млн р] )

VALUES (1, "Подкладочная ткань", 12, 0, 7);

4. Для того, чтобы с таблицами можно было работать как с единым целым, свяжем их, пользуясь инструментом Схема данных. Исходя из смысла базы данных, связь должна быть установлена по полю [Код отдела магазина] таблицы Справочник и полю [Код отдела магазина] таблицы Сведения (рис. 3). Это связь вида один ко многим, так как одной записи таблицы Справочник может соответствовать несколько записей таблицы Сведения.

Рис. 3 - Схема данных

5. Составим запросы к базе данных и реализуем их в СУБД Access:

Запрос 1. Рассчитать значение поля [Реализация за месяц, млн р].

Значение этого поля рассчитывается по формуле:

[Реализация за месяц, млн р] = [Реализация за 1 декаду, млн р]+[Реализация за 2 декаду, млн р]+[Реализация за 3 декаду, млн р]

Это запрос на обновление. Для его реализации необходимо активизировать вкладку Запросы ==> Создать ==> Конструктор==> Меню Запрос ==> Обновление ==> SQL. В окне SQL (рис.4) ввести текст запроса:

UPDATE Сведения SET [Реализация за месяц, млн р] =

[Реализация за 1 декаду, млн р]+[Реализация за 2 декаду, млн р]+

[Реализация за 3 декаду, млн р];

Рис. 4 - Окно запроса на обновление

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

В результате поле [Реализация за месяц, млн р] таблицы Сведения будет рассчитано в соответствии с введенной формулой (рис. 5).

Рис.5 - Таблица Сведения после выполнения запроса на обновление

Запрос 2.

Выбрать сведения о реализации продукции, объем которой в первой декаде не превышает 5 млн. руб., но является не нулевым. Данные упорядочить по возрастанию реализации.

Текст запроса на SQL:

SELECT [Название отдела магазина], [Наименование продукта],

[Реализация за 1 декаду, млн р]

FROM Справочник, Сведения

WHERE Справочник.[Код отдела магазина]=Сведения.[Код отдела магазина]

AND (([Реализация за 1 декаду, млн р]<>0 And

[Реализация за 1 декаду, млн р]<=5))

ORDER BY [Реализация за 1 декаду, млн р];

В результате выполнения запроса получим таблицу:

Создадим этот запрос с помощью конструктора MS Access:

Запрос 3.

Выбрать сведения по реализации товаров, объем которой в первой декаде находится в пределах от 2 до 6 млн. руб. Данные упорядочить по убыванию объема реализации.

Текст запроса на SQL:

SELECT [Название отдела магазина], [Наименование продукта],

[Реализация за 1 декаду, млн р]

FROM Справочник, Сведения

WHERE Справочник.[Код отдела магазина]=Сведения.[Код отдела магазина]

AND ([Реализация за 1 декаду, млн р] Between 2 And 6)

ORDER BY [Реализация за 1 декаду, млн р] DESC;

В результате выполнения запроса получим таблицу:

Создадим этот запрос с помощью конструктора MS Access:

Запрос 4.

Вывести сведения о реализации ткани в третьей декаде. Упорядочить по величине реализации.

Текст запроса на SQL:

SELECT [Название отдела магазина], [Наименование продукта],

[Реализация за 3 декаду, млн р]

FROM Справочник, Сведения

WHERE Справочник.[Код отдела магазина] =

Сведения.[Код отдела магазина]

AND ((([Наименование продукта] Like ("*кань*")) AND

([Реализация за 3 декаду, млн р]<>0)))

ORDER BY [Реализация за 3 декаду, млн р];

В результате выполнения запроса получим таблицу:

Создадим этот запрос с помощью конструктора MS Access:

Запрос 5.

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

Текст запроса на SQL:

SELECT Справочник.[Код отдела магазина], [Название отдела магазина], Count( [Наименование продукта]) AS [Количество видов продукции],

Sum( [Реализация за 1 декаду, млн р]) AS [Общая реализация (1 декада)],

Avg( [Реализация за 1 декаду, млн р]) AS

[В среднем по продукции (1 декада)]

FROM Справочник, Сведения

WHERE Справочник.[Код отдела магазина] =

Сведения.[Код отдела магазина]

GROUP BY Справочник.[Код отдела магазина],

[Название отдела магазина]

ORDER BY [Название отдела магазина];

В результате выполнения запроса получим таблицу:

Создадим этот запрос с помощью конструктора MS Access:

Запрос 6

По каждому отделу определить количество видов продукции, общую реализацию в первой декаде и среднюю реализацию в расчете на 1 вид продукции. При этом учитывать только продукцию с ненулевой реализацией в первой декаде. Вывести данные об отделах, реализующих более 4 видов продукции, упорядочив эти данные по возрастанию средней реализации.

Текст запроса на SQL:

SELECT Справочник.[Код отдела магазина], [Название отдела магазина],

Count( [Наименование продукта]) AS [Количество видов продукции],

Sum( [Реализация за 1 декаду, млн р]) AS [Общая реализация (1 декада)],

Avg( [Реализация за 1 декаду, млн р]) AS

[В среднем по продукции (1 декада)]

FROM Справочник, Сведения

WHERE Справочник.[Код отдела магазина] =

Сведения.[Код отдела магазина]

AND ([Реализация за 1 декаду, млн р]<>0)

GROUP BY Справочник.[Код отдела магазина],

[Название отдела магазина]

HAVING (Count([Наименование продукта])>4)

ORDER BY Avg([Реализация за 1 декаду, млн р]);

В результате выполнения запроса получим таблицу:

Создадим этот запрос с помощью конструктора MS Access:

Задание 4. Спроектировать объект БД - отчет (форму) в СУБД Access

Сформируем отчет по реализуемой продукции, в котором поведем итоги по каждому отделу магазина. Воспользуемся возможностями Мастера отчетов: вкладка Отчеты == > Создать == > Мастер отчетов, который за несколько шагов поможет создать отчет с группировкой по выбранным полям.

Фрагмент сформированного отчета представлен ниже.

Список литературы

1.Вардомацкая Е.Ю., Окишева Т.Н. Информатика. В двух частях. Часть I. Учебное пособие. Витебск, 2007-220 с.

2.Вардомацкая Е.Ю., Окишева Т.Н. Информатика. В двух частях. Часть II. Excel. Учебное пособие. Витебск, 2007-237 с.

3.Вардомацкая Е.Ю., «Информационные технологии в экономике и управлении». Методические указания для студентов ФПК, Витебск,2008 г.

4.Дьяконов, В. Maple 6 : учебный курс - Санкт-Петербург : Питер, 2001. - 608 с. : ил.

5.Информатика для юристов и экономистов: Учебник для вузов / Под ред. С.В. Симоновича. - СПб: Питер, 2006.

6.Коцюбинский, А. Excel в примерах для менеджера и экономиста / А.О.Коцюбинский, С.В. Грошев. - М.: ГроссМедиа, 2004. - 304 с.

7. Шарстнев В.Л. Компьютерные информационные технологии: Курс лекций - Витебск: УО «ВГТУ», 2006 г.

8.Шарстнев В.Л., Вардомацкая Е.Ю. Компьютерные информационные технологии: лабораторный практикум : пособие- Витебск: УО «ВГТУ», 2008. - 170 с.

9.Шарстнев В.Л., Вардомацкая Е.Ю. Методические указания к лабораторным работам по курсу ТОХОД для студентов экономических специальностей заочного отделения. - Витебск: УО «ВГТУ», 2002.

Размещено на Allbest