Лабораторная работа №1 Работа в Oracle Database Express Edition 1 Лабораторная работа №6

Лабораторная работа № 6.

Формирование хранилища данных из разных источников

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

(ввести 4-5 категорий)

2. Поставщики (Postavshiki) (ID поставщика, Название, Категория товара, Юридический Адрес, Телефон):

(ввести по 10-12 записей)

ввести по 5 записей для каждой категории)

6. 
   Таблица скидок (Table_Skidka), поля: Количество товара, Скидка, Описание:
   

Таблица Заказы будет содержать данные о заказах – номер заказа, ID продавца. ID покупателя и дату совершения заказа

Таблица Подробности заказов содержит данные о товарах, входящих в заказ (от 1 до 3 наименований - это число можно задать в программе) и их количестве. Количество генерируется случайным образом в пределах от 1 до 50 (этот параметр также можно поменять).

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

1. 
   Создать структуру таблицы Заказы (TABLE_ZAKAZ): Код подразделения, ID заказа, ID заказчика, Дата заказа, ID продавца:
   

2. 
   Создать структуру таблицы Подробности заказа (TABLE_Detali_zakazov): ID заказа, ID товара, Код филиала, ID Поставщика, Количество, Цена за единицу, Общая цена, Скидка, Цена с учетом скидки:
   

3. 
   Для заполнения таблиц данными создать форму с кнопками, при нажатии которых будут генерироваться данные для заполнения таблиц TABLE_ZAKAZ и TABLE_Detali_zakazov.
   

• 
  Открыть вкладку Формы\Создать\Конструктор\ОК. Откроется Мастер Формы.
  
• 
  На панели инструментов выбрать кнопку , щелкнув по ней и перетащив кнопку в поле формы. Открывается Мастер Создание кнопок.
  
• 
  Нажать кнопку Отмена, которая закроет мастера.
  
• 
  Щелкнуть правой кнопкой мыши по кнопке, выбрать: Обработка событий\Программы\ОК. При этом открывается окно редактора Visual Basic.
  

Private Sub Кнопка0_Click()

Dim nz As Integer

Dim d As String

Dim dbs As Database, r1 As Recordset, r2 As Recordset

Set dbs = CurrentDb

Set r1 = dbs.OpenRecordset("table_zakaz")

Set r2 = dbs.OpenRecordset("TABLE_Detali_zakazov")

Dim t As Integer
nz = InputBox("Необх.колич-о заказов (не менее 200)", , "200")

nt = InputBox("Число товаров", , "10")

np = InputBox("Число поставщиков", , "6")

nf = InputBox("Код филиала", , "1")

npr = InputBox("Число продавцов в филиале", , "5")

nzz = InputBox("Число покупателей", , "6")

izz = Int(Rnd * nzz) + 1 ' номер заказчика

ip = Int(Rnd * npr) + 1 ' номер продавца

d = Int(Rnd * 30) + 1

d = d + "."

d2 = Str(Int(Rnd * 12) + 1)

d = d + d2 + "."

d2 = Str(Int(Rnd * 3) + 2006)

d = d + d2

r1.AddNew

r1!ID_filiala = nf

r1!ID_zakaza = i 'Изменяемое поле. Для второго филиала должно=i+1000

r1!ID_pokupatel = izz

r1!Data_zakaza = d

r1!ID_prodavca = ip

r1.Update

Next i

' Заполнение данными таблицы Table_Detali_Zakaz "Детали заказов"

izz = Int(Rnd * nzz) + 1 ' номер заказа

kz = Int(Rnd * 3) + 1 ' кол.позиций данного заказа (от 1 до 3)

ip = Int(Rnd * npr) + 1 ' номер продавца

For k = 1 To kz

r2.AddNew

r2!ID_filiala = nf

r2!ID_zakaza = i 'Изменяемое поле. Для второго филиала должно=i+1000

r2!ID_tovara = Int(Rnd * nt) + 1

r2!ID_postav = Int(Rnd * np) + 1

r2!Kolichestvo = Int(Rnd * 49) + 1

r2.Update

Next k

Next i

________________________________

• 
  Закрыть редактор VBA.
  
• 
  Сохранить форму под названием Forma_Zakazov.
  
• 
  Открыть форму с помощью Конструктора и переименовать кнопку на «Сформировать заказы». Закрыть форму.
  
• 
  Открыть форму и запустить процедуру «Сформировать заказы». В ходе работы программа запросит ввести необходимые данные: Количество заказов – это число определяет, сколько строчек будет внесено в таблицу фактов о заказах. Данные будут разбросаны по месяцам 2006-2008 г.г. Остальные значения должны соответствовать количеству товаров, поставщиков, продавцов, покупателей, внесенных в таблицы измерений.
  
• 
  Задайте первоначально значения Количества заказов равным 10 записям для проверки работы программы.
  

В результате в таблице Детали заказов (TABLE_ Detali_zakazov) будут заполнены следующие поля:

Все поля таблицы Заказы (TABLE_ZAKAZ) будут заполнены.

4. 
   Следующим действием необходимо в таблице Детали заказов заполнить столбец Цена за единицу (Cena_za_ed), перенеся данные из таблицы Товары (Tovary). Будем считать цены за единицу товара в таблице Товары оптовыми, а розничные цены установим на 20% выше оптовых. Вычислим значение розничных цен в таблице Детали заказов (TABLE_ Detali_zakazov).
   

• 
  Вкладка ЗапросыСоздатьКонструктор. Из списка выбрать таблицу Tovary.
  
• 
  Кнопка Закрыть.
  
• 
  На панели инструментов выбрать ЗапросОбновление.
  
• 
  На панели инструментов выбрать ВидРежим SQL.
  
• 
  Записать запрос:
  

zakazov.ID_tovara=Tovary.ID_tovara SET TABLE_Detali_zakazov.Cena_za_ed = Tovary!Cena_za_ed*1.2;

• 
  Выполнить запрос, нажать кнопку на панели инструментов: Запуск !
  
• 
  Сохранить запрос под названием Запрос на обновление-Цена за ед.
  

• 
  Запрос на вычисление Цены_Всего (Vsego_cena):
  

1. 
   вкладка Запросы/Создать/В режиме конструктора.
   
2. 
   добавить таблицу TABLE_Detali_zakazov.
   
3. 
   щелкнуть правой кнопкой на поле: Тип запроса/Обновление.
   

4. 
   В графу Поле вставить поле Vsego_cena, щелкнув два раза по имени поля в таблице.
   
5. 
   В строке Обновление щелкнуть правой кнопкой мыши, выбрать Построить.
   
6. 
   В открывшемся окне Построитель выражений выбрать Таблицы/ TABLE_Detali_zakazov/ Cena_za_ed, выбрать операцию умножения - (* ) , далее выбрать Kolichestvo и нажмите ОК.
   

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

7. 
   Сохраните запрос Цена всего.
   
8. 
   В режиме SQL запрос будет выглядеть следующим образом:
   

UPDATE TABLE_Detali_zakazov SET TABLE_Detali_zakazov.Vsego_cena = TABLE_Detali_zakazov!Cena_za_ed*TABLE_Detali_zakazov!Kolichestvo;

5. 
   Добавить кнопку для внесения данных в поле «Skidka» таблицы TABLE_Detali_zakazov из таблицы Table_Skidka.
   

Private Sub Кнопка1_Click()

Dim dbs As Database, r1 As Recordset, r2 As Recordset

Dim Skidki(2, 10) As Integer

Set dbs = CurrentDb

Set r1 = dbs.OpenRecordset("TABLE_Detali_Zakazov")

Set r2 = dbs.OpenRecordset("Table_Skidka")

n = r2.RecordCount 'Количество строк в таблице Скидки

'nzz = InputBox("n=" + Str(n))

r2.MoveFirst

k = 1

While k <= n

Skidki(2, k) = r2!Skidka_v_procent

If k < n Then r2.MoveNext

k = k + 1

Wend

Skidki(1, k) = 1000 'предельное значение для кол.товара

Do While Not r1.EOF ' Цикл по табл.Заказы

z = r1!Kolichestvo

k = 1

k = k + 1

Wend

s = (r1!Vsego_cena) * Skidki(2, k) \ 100

r1!Skidka = s

r1.Update

r1.MoveNext

Loop

End Sub

• 
  Закрыть редактор VBA, закрыть конструктор формы и запустить форму.
  

• 
  Запрос на обновление: вычисление величины цены товара со скидкой (с помощью SQL или в режиме Конструктора с помощью Построителя):
  

UPDATE Table_Detali_zakazov SET Cena_so_skidkoi = Vsego_cena-Skidka;

(в каждой строчке поля Cena_so_skidkoi будет вычислена разность полной цены и скидки).

6. 
   Выполнить запросы на удаление.
   
   • 
     Запрос на удаление данных из таблицы TABLE_ZAKAZ:
     

• 
  ЗапросКонструктор TABLE_Zakaz. Правой кнопкой мыши щелкнуть по полю и выбрать Вид запросаУдаление.
  
• 
  ВидРежим SQL.
  

DELETE *

• 
  Запрос на удаление данных из таблицы TABLE_Detali_zakazov:
  

FROM TABLE_Detali_Zakazov;

7. 
   Добавить в таблицу TABLE_ZAKAZ новое поле ИТОГО, в котором надо рассчитать общую стоимость всего заказа. Для этого надо выбрать данные из таблицы TABLE_Detali_zakazov (объединить данные, относящиеся к одному заказу, из таблицы деталей заказов, и найти сумму со скидкой для всех товаров, входящих в заказ).
   

INSERT INTO Temp ( Id_zakaza, Itogo )

SELECT id_zakaza, Sum(Cena_so_skidkoi)

FROM TABLE_Detali_zakazov

GROUP BY Id_zakaza;
Потом выполните еще один запрос на обновление для переноса значения из поля Итого (Itogo) таблицы Temp в аналогичное поле таблицы Table_Zakaz.
III. Формирование данных для филиала 2.
После выполнения п.7 будет сформировано две таблицы фактов, содержащие столько записей, сколько было указано заказов при запуске первого сценария (кнопка 0). Однако обе таблицы содержат данные, относящиеся только к одному филиалу.

Необходимо дополнить полученные таблицы данными о продажах во втором филиале. Для выполнения этого необходимо повторить все указанные операции, перечисленные в части II лабораторной работы, для второго филиала. Чтобы номера заказов, выполненные во втором филиале, не совпадали с номерами заказов, необходимо, чтобы поле Id_zakaza в обеих таблицах фактов начинались с числа, превышающего число записей в таблице Table_Zakaz. Для этого необходимо:

• 
  добавить кнопку для формирования заказов в филиале 2;
  
• 
  скопировать листинг Программы 1.
  
• 
  в программе параметр i изменить на i+1000.