Табл. 1. Студенты

- ID студента (номер студенческого билета)

- ФИО студента

-Домашний телефон

-Номер группы
Табл.2. Сводная экзаменационная ведомость

- ID студента (внешний ключ из табл.1)

-Номер группы

- Оценка 1 (оценка по 1-ому экзамену) – пустое поле

- Оценка 2 (оценка по 2-ому экзамену) – пустое поле
Табл.3 Результаты сдачи экзамена по БД гр. 4206

- ID студента

-Оценка

Задание:

1. Задать структуры трех таблиц в СУБД MS Access

2. Заполнить данными

3. Перенести таблицы в БД Oracle.

4. Написать SQL- запрос и перенести данные из табл.3 в табл.2.

Лабораторная работа 5. Выполнение расчетов с использованием программирования в среде Visual Basic for Applications (VBA).

1. 
   Создать таблицу Employers. Поля таблицы: Months, Zarplata, Nadbavka. Тип данных – числовой. Имя поля ввести латиницей.
   
2. 
   Заполните поле Months цифрами от 1 до 12.
   
3. 
   Создать пустую форму в режиме Конструктора (выбрать источник данных – пустая строка).
   
4. 
   В меню Вид – Панель элементов, выбрать элементы: Кнопка и Надпись и перенести их на форму.
   
5. 
   В открывшемся диалоговом окне нажать Cancel.
   
6. 
   Щелкнуть правой кнопкой мыши по кнопке. Выбрать Обработка событий – Программа. Открывается диалоговое окно VBA.
   

Примечание: После каждой строки нажать клавишу Enter.
Private Sub Кнопка0_Click()

Dim dbs As Database, r1 As Recordset ‘определить объектные переменные

Dim n, s, z As Long

Set dbs = CurrentDb ‘текущая БД=currentDb

Set r1 = dbs.OpenRecordset("Employers") ‘присвоить переменной ссылку на таблицу "Employers"

r1.MoveFirst ‘установить курсор на 1 записи

n = r1.RecordCount ‘подсчитать число записей в табл.

Do While Not r1.EOF ‘цикл по записям-пока не дойдет до конца записи

r1.Edit

r1!Zar = 6000 + 1000 * Int(Rnd() * 10) ‘занести данные случайным образом

r1.Update

r1.MoveNext

Loop ‘конец цикла

s = 0

r1!MoveFirst

n = r1.RecordCount

Do While Not r1.EOF

z = r1!Zar

s = s + z

r1.Edit ‘Редактировать таблицу

If z > 6900 Then r1!Nad = 500 Else r1!Nad = 400 'Установить надбавку в зависимости от зарплаты

r1.Update ‘занести исправления в таблицу

r1.MoveNext

Loop

s = s \ n

Надпись1.Caption = s ‘значение средней зарплаты вывести в надпись1

End Sub

7. 
   Просмотреть поля таблицы Employers.
   
8. 
   Какие данные отображаются в окне «Надпись1»?
   

Задание

1. 
   Создать 3 таблицы: Таблица 1 – Товары. Поля таблицы – Наименование товара, Базовая цена (тип поля – числовой).
   

Ввести три записи.

2. 
   Таблица 2 – Продажи. Поля таблицы – Месяц, Товар 1, Товар 2, Товар 3, Базовая цена товара, Цена товара.
   
3. 
   Занести данные в столбец Месяц от 1 до 12.
   
4. 
   Таблица 3 – Курс Валюты. Поля таблицы – Месяц, КурсЕвро.
   
5. 
   Занести данные в столбец Месяц от 1 до 12.
   
6. 
   Написать программу на VBA, которая:
   

1. 
   Заполнить случайными числами столбцы Товар 1, Товар 2, Товар 3 в таблице 2.
   
2. 
   Заполнить столбец КурсЕвро случайными числами от 44,5 до 47 с одним знаком после запятой (0,1).
   

Например, r1!Zar = 6000 + 1000 * Int(Rnd() * 10)

«6000» - минимальная зарплата

«1000» - шаг изменения зарплаты

«Int(Rnd() * 10)» - случайное число от 0 до 10.

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

k=1+(курс евро – 40)*0,1

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

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

Задание: Используя в качестве источников табличные данные о результатах продаж, сформировать реляционное хранилище данных по схеме «звезда». (Каждый студент должен разработать свой вариант базы данных). Необходимо сформировать 5-6 таблиц измерений и 2 таблицы фактов в MS Access для дальнейшего переноса в Oracle.
Пример. Рассмотрим базовый пример «Фирма по оптовой продаже комплектующих для компьютера» для выполнения сквозного проекта по курсу «Хранилища данных», варианты которого были даны в лабораторной работе № 4.

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

I. Составить таблицы измерений:

1. Категории товаров (Category), поля: ID категории, Наименование:

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

ID категории	Наименование_категории
1	Материнская плата
2	Видеокарта
3	HDD
4	Карта памяти

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

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

3. Товары (Tovary), поля: ID товара, Код категории товара Наименование Товара, Цена за ед., Гарантия:

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

4. Покупатели (Pokupateli), поля: ID Покупателя, Наименование организации, Контактное лицо, Город, Адрес:


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


5. Служащие филиала 1: (Prodavci_Filial_1): поля: ID продавца, ФИО, Телефон:


(ввести не менее 5 записей)



5.1. Служащие филиала 2: (Prodavci_Filial_2): поля: ID продавца, ФИО, Телефон.

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

Количество товара	Скидка, %	Описание
5	3	Скидка в размере 3% при покупке товара от 5 до 10 шт.
10	5	Скидка в размере 5% при покупке товара от 10 до 20 шт.
20	7	Скидка в размере 7% при покупке товара от 20 и более.

II. Составить таблицы фактов:
В нашем проекте будет создано две таблицы фактов.

Таблица Заказы будет содержать данные о заказах – номер заказа, 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.
  

Листинг программы для первой кнопки (заполняем таблицы TABLE_ZAKAZ и TABLE_ Detali_zakazov
' Программа 1.

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")

' Заполнение данными таблицы Table_Zakazov "Таблица заказов"
For i = 1 To nz

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 "Детали заказов"

For i = 1 To nz

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

End Sub

________________________________

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

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

и т.д., в зависимости от количества введенных данных.

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

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

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

Для этого необходимо выполнить следующий запрос.

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

UPDATE TABLE_Detali_zakazov INNER JOIN Tovary ON TABLE_Detali_

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 и нажмите ОК.
   

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

TABLE_Detali_zakazov!Cena_za_ed*TABLE_Detali_zakazov!Kolichestvo

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

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

Примечание: Для переключения в режим SQL надо открыть запрос в конструкторе, потом нажать кнопку меню Вид\SQL.
В результате этого запроса будет вычислено и заполнено данными поле Vsego_cena.

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

Для этого открыть форму в режиме Конструктора, добавить из панели элементов кнопку, она будет иметь имя Кнопка1, в окне редактора Visual Basic набрать следующую программу:
Листинг программы для кнопки Расчет скидки, для таблицы (TABLE_Detali_zakazov):
' Программа 2.

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(1, k) = r2!kolich_tovara

Skidki(2, k) = r2!Skidka_v_procent

If k < n Then r2.MoveNext

k = k + 1

Wend

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

r1.MoveFirst

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

z = r1!Kolichestvo

k = 1

While (z > Skidki(1, k)) And (k < n)

k = k + 1

Wend

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

r1.Edit

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 *

FROM TABLE_Zakaz;

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

DELETE *

FROM TABLE_Detali_Zakazov;

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

Подсказка: Задайте структуру временной таблицы Temp c полями: Id_zakaza, Itogo, куда с помощью запроса перемести суммарные данные из таблицы TABLE_Detali_zakazov c помощью запроса с группировкой данных по полю Id_zakaza:

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.
  

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

Оценка эффективности работы филиалов

Задание. Импортировать все таблицы, созданные в лабораторной работе №6, в среду Oracle.

!. Имя пользователя должно совпадать с фамилией студента.

Запустить Oracle Database 10g Express Edition  Oracle Database Home Page, вкладка SQL SQL Commands. Выполнить запросы к созданной БД с целью оценки эффективности работы филиалов.

Перечень SQL запросов, необходимых для выполнения работы:

1. 
   Подсчитать общую сумму заказов по годам для каждого из филиалов.
   
2. 
   Подсчитать прибыль предприятия по годам для каждого из филиалов. Для этого, учитывая разницу между оптовыми и розничными ценами подсчитать сначала разницу между заработанными и затраченными средствами, потом вычесть издержки, связанные с зарплатами продавцов. Считать зарплату продавцов одинаковой и равной 10000 руб. в мес. Количество продавцов определяется таблицами Продавцы филиалов.
   
3. 
   Выбрать лучший филиал по общей выручке за 2008 год.
   
4. 
   Определить лучшего продавца по каждому из филиалов за 2008 год.
   
5. 
   Подсчитать среднюю выручку на одного продавца в каждом из филиалов.
   
6. 
   Определить 3 самых ходовых товара по каждому из филиалов и по всему предприятию в целом.
   
7. 
   Определить самые плохо продаваемые товары за 2008 год.
   
8. 
   Сделать выводы и выработать рекомендации о развитии продаж в будущем году.
   
9. 
   Сохраните весь набор полученных SQL-запросов в один файл.
   
10. 
    Выполнить письменный отчет по всей работе, включив данные (скриншоты) базовых таблиц, тексты запросов, результаты выполнения.