ку. УП 07. Практикум по курсу технологии баз данных Учебное пособие
 Скачать 2.49 Mb.
|
|
|
85
Таблица «Teachers». При экспорте таблицы преподавателей мы должны на самом деле экспортировать результат его естественного соединения с табли- цей кафедр. Для этого придется создать специальное представление данных, так как запрос не может быть использован в качестве источника данных для экспорта.
CREATE VIEW Teacher_for_export
AS
SELECT idTeacher, FIOTeacher, TitleDepartment FROM Teachers
INNER JOIN Departments
ON Teachers.idDepartment=Departments.idDepartment;
Используем для загрузки данных о преподавателях файл формата Excel, указав при экспорте в качестве источника данных созданное представление.
При импорте этих данных следует обратить внимание на настройки относи- тельно строки с заголовками столбцов – требуется явно указать, что первая строка является строкой с именами столбцов, тогда данные будут считываться только со следующей строки. Не забудьте при импорте настроить соответствие для имен столбцов с названием кафедры
Рис.51. XLS-файл с данными о преподавателях.
Отметим, что экспортировать данные можно и в другие форматы: RTF,
PDF и т.д., но для импорта в другую базу эти форматы не будут пригодными.
Вполне естественно, что файл для импорта не обязательно должен быть сгенерирован каким-либо СУБД. Так как таблицу результатов сдачи зачетов- экзаменов в базе данных мы практически не заполняли, экспортировать в таб- лицу фактов нам пока нечего.
86
Напишем приложение, которое генерирует файл с оценками, и далее им- портируем его в нашу базу данных. Для простоты не будет рассматривать ситу- ацию, когда разные группы должны сдавать разный набор дисциплин. Будем предполагать, что у каждого студента имеются оценки по всем дисциплинам, которые есть в таблице измерений.
Программный код такого проекта по генерации данных будет следую- щим. Комментариев в коде должно быть достаточно для понимания алгоритма генерации: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Odbc; using System.IO; namespace generator
{ class Program
{
// в набор данных будут загружены измерения
// для правильной генерации кодов объектов static DataSet ds = new DataSet();
// вспомогательная функция перевода баллов в оценку static int GetMark(int b)
{ if (b < 55) return 2; if (b < 71) return 3; if (b < 86) return 4; return 5;
} static void Main(string[] args)
{
OdbcConnection con = new OdbcConnection("DSN=proba"); con.Open();
// загрузка таблицы студентов
OdbcDataAdapter adapt1 = new OdbcDataAdapter("select * from Students", con); adapt1.Fill(ds, "Students");
// загрузка таблицы дисциплин
OdbcDataAdapter adapt2 = new OdbcDataAdapter("select * from Subjects", con); adapt2.Fill(ds, "Subjects");
87
// загрузка таблицы преподавателей
OdbcDataAdapter adapt3 = new OdbcDataAdapter("select * from Teachers", con); adapt3.Fill(ds, "Teachers"); con.Close();
//создание текстового файла для записи
//сгенерированных данных
StreamWriter tf = new StreamWriter
(new FileStream("Results.csv", FileMode.Create));
// запись строки заголовка таблицы.
// Для вывода кавычек используется \” tf.WriteLine("\"idStudent\";\"idSubject\";
\"idTeacher\";\"DateExam\";
\Balls\";\"Mark\"");
// вызов функции генерации данных
GenerateData(tf);
// закрытие файла tf.Close();
}
// функция генерации оценок экзаменов по всем дисциплинам static void GenerateData(StreamWriter tf)
{
Random r = new Random();
// цикл перебора всех дисциплин foreach (DataRow dr_subj in ds.Tables["Subjects"].Rows)
{
// генерируем преподавателя,
// которому сдается данный предмет int i = r.Next(ds.Tables["Teachers"].Rows.Count); int nom_teach =
(int)ds.Tables["Teachers"].Rows[i]["idTeacher"];
//генерируем три даты для сдачи экзамена
DateTime [] dates=new DateTime[3];
//определяем зимнюю или весенюю сессии if (r.Next(100) % 2 == 0)
{
// зимняя сессия dates[0] = new DateTime(2014, 1, 5 + r.Next(20)); dates[1] = new DateTime(2014, 1, 5 + r.Next(20)); dates[2] = new DateTime(2014, 1, 5 + r.Next(20));
} else
{
// весенняя сессия dates[0] =
88 new DateTime(2014, 6, 3 + r.Next(24)); dates[1] = new DateTime(2014, 6, 3 + r.Next(24)); dates[2] = new DateTime(2014, 6, 3 + r.Next(24));
}
// цикл перебора всех студентов foreach (DataRow dr_stud in ds.Tables["Students"].Rows)
{
// генерируем оценку - будем оптимистами,
// все оценки положительные int balls = 55 + r.Next(45); int mark = GetMark(balls);
// генерируем дату сдачи экзамена int nom_date = r.Next(120) % 3;
// формируем строку вывода сгенерированной
// информации tf.WriteLine("{0};{1};{2};{3};{4};{5}", dr_stud["idStudent"], dr_subj["idSubject"], nom_teach, dates[nom_date], balls, mark);
}
}
}
}
}
Таким образом, получим следующий csv-файл, который импортируем в базу данных для организации хранилища:
Рис. 52.CSV-файл с оценками студентов.
89
3.3. ПОИСК ИНФОРМАЦИИ В ХРАНИЛИЩЕ
По своей сути таблица фактов представляет собой OLAP-куб (Online
Analytical Processing), который позволяет быстро находить интересующую ин- формацию в разных аспектах. Существуют стандартные операции с OLAP- кубами: срез, вращение, консолидация и детализация. Кратко опишем суть этих операций:
Операция среза обычно определяет все возможные данные с фиксаци- ей одного или нескольких измерений.
Операция вращения предполагает переупорядочивание измерений.
Операция консолидации предполагает выполнение агрегирования данных по отдельным измерениям.
Операция детализации предполагает получение нового куба, содер- жащего не все значения измерений.
А теперь продемонстрируем на примере ряда запросов применение этих операций для получения информации. Обычно все запросы к хранилищу дан- ных обращаются к таблице фактов или к естественному соединению таблицы фактов с необходимыми для запроса таблицами измерений:
Получить все оценки по дисциплине «Математический анализ». В этом случае мы используем операцию среза. Можно сказать, что это одновре- менно является срезом по измерению «Преподаватель» (так как согласно генерации данных дисциплина ведется только одним преподавателем):
SELECT FIOStudent, Mark FROM Results INNER JOIN Students
ON Results.idStudent=Students.idStudent
WHERE idSubject =
(SELECT idSubject FROM Subjects
WHERE TitleSubject='Математический анализ');
Результатом является другой гиперкуб, в данном случае с одним измере- нием FIOStudent и фактом - оценкой.
90
Рис. 53. Оценки студентов по дисциплине «Математический анализ».
Получить средние баллы всех студентов. Здесь демонстрируется опера- ция консолидации, которая выражается укрупнением группировок по из- мерениям.
SELECT FIOStudent, AVG(Balls) AS "Средний балл" FROM
Results INNER JOIN Students
ON Results.idStudent=Students.idStudent
GROUP BY FIOStudent;
Рис. 54. Средние баллы студентов.
Получить средние баллы сдачи экзаменов по группам студентов. В дан- ном случае производится консолидация по более крупной группе – номе- рам групп студентов. Таким образом, результатом является гиперкуб, в котором два измерения – дисциплина и номер учебной группы. В каком- то смысле здесь производится переупорядочивание измерений, поэтому можно говорить и о применении операции вращения.
SELECT TitleSubject, NumGroup, AVG(Balls) AS "Средний балл" FROM
Results INNER JOIN Subjects
ON Results.idSubject=Subjects.idSubject
INNER JOIN Students ON Results.idStudent=Students.idStudent
GROUP BY TitleSubject, NumGroup ORDER BY TitleSubject;
91
Рис. 55. Средние баллы по предметам и группам.
Получить средние баллы по преподавателям. Какому студенту не хоте- лось знать перед экзаменом, какой преподаватель более лояльный? Ситу- ация аналогичная запросу о средних баллах студентов. Как видно, нашел- ся один заметно более строгий преподаватель.
SELECT FIOTeacher, AVG(Balls) AS "Средний балл" FROM
Results INNER JOIN Teachers
ON Results.idTeacher=Teachers.idTeacher
GROUP BY FIOTeacher;
Рис. 56. Средние баллы по преподавателям.
Получить количество пятерок, которые поставили преподаватели.
SELECT FIOTeacher, COUNT(Mark) AS "Количество пятерок" FROM
Results INNER JOIN Teachers
ON Results.idTeacher=Teachers.idTeacher
WHERE Mark=5
GROUP BY FIOTeacher;
Рис. 57. Количество пятерок, которые поставили преподаватели.
92
Узнать, в какую сессию (зимнюю или летнюю) студенты сдают экзамены лучше. Для этого мы рассчитаем средние баллы сдачи экзаменов зимой и летом. Здесь мы опять сталкиваемся с операцией консолидации, но по не- стандартному измерению – дате сдачи экзамена.
SELECT DATEPART(month,DateExam) AS "Месяц",
AVG(Balls) AS "Средний балл"
FROM Results GROUP BY DATEPART(month,DateExam);
Рис. 58. Средние баллы в зимнюю и летнюю сессии.
Получить количество дисциплин, которые ведет каждый из преподавате- лей. Здесь в качестве факта выступает измерение таблицы фактов idSub- ject. В данном случае сначала применяем операцию детализации, выбирая для нового гиперкуба из таблицы фактов только некоторые данные, после чего применяется операция консолидации посредством группировки по измерению «Преподаватель».
SELECT FIOTeacher, COUNT(idSubject) AS "Количество дисциплин" FROM
(SELECT DISTINCT FIOTeacher, idSubject FROM Results
INNER JOIN Teachers
ON Results.idTeacher=Teachers.idTeacher) AS Q1
GROUP BY Q1.FIOTeacher;
Рис. 59. Количество дисциплин каждого из преподавателей.
Получить количество студентов, сдавших дисциплину «Математический анализ» на каждую дату сдачи этого предмета. Здесь используется опера-
93 ция среза, после чего применяется операция консолидации по дате сдачи экзамена.
SELECT DateExam, COUNT(*) AS "Количество студентов" FROM Results
WHERE idSubject=(SELECT idSubject FROM Subjects WHERE
TitleSubject='Математический анализ')
GROUP BY DateExam;
Рис. 60. Количество студентов, сдавших математический анализ на разные даты.
Получить количество дат, в которые проводились экзамены по каждому из предметов. Данный запрос будет связан с последовательностью опера- ции детализации и консолидации.
SELECT TitleSubject, COUNT(DateExam) AS "Количество дат" FROM
(SELECT DISTINCT TitleSubject, DateExam FROM Results
INNER JOIN Subjects
ON Results.idSubject=Subjects.idSubject) AS Q1
GROUP BY Q1.TitleSubject;
Рис. 61. Количество дат сдачи каждого из экзаменов.
Получить количество оценок каждого типа по дисциплине «Математиче- ский анализ». В данном примере применение среза и последующей дета- лизации приведет к построению гиперкуба, в котором можно сделать факт (оценку) измерением. Затем к этому новому гиперкубу применяется операция консолидации.
94
SELECT Mark, COUNT(*) AS "Количество" FROM Results
WHERE idSubject =
(SELECT idSubject FROM Subjects
WHERE TitleSubject='Математический анализ')
GROUP BY Mark;
Рис. 62. Количество оценок каждого типа по математическому анализу.
95
3.4. ПОСТРОЕНИЕ ОТЧЕТОВ С ПОМОЩЬЮ ЗАПРОСОВ К
ХРАНИЛИЩУ
Оболочка dbForge Studio содержит собственные средства для создания отчетов. Отчеты формируются в виде файлов с расширением rbd. Они содержат разметку элементов данных в отчете. Файл можно создать с помощью кон- структора, который вызывается с помощью вкладки «Анализ данных» старто- вой страницы и пункта «Дизайн нового отчета».
На первой странице дизайнера (мастера) предлагается выбрать базу дан- ных и источник данных (таблицу или запрос). На следующей странице можно будет выбрать таблицу или написать текст запроса для отображения в отчете.
Далее можно будет выбрать поля для отображения в отчете. На следующем ша- ге задаются принципы группировки записей (группировок может быть несколь- ко и они могут образовывать иерархию). Далее следуют несколько шагов с за- данием характеристик внешнего вида отчета.
Например, на основании запроса, представляющего собой соединение таблицы фактов и всех таблиц измерений, сгенерируем отчет:
Рис. 63. Вид отчета.
96
Основная группировка данных производится по номеру группы, далее по иерархии идут группировки по дисциплине (преподавателю), дате экзамена.
Детализацией записи являются ФИО студента, набранные баллы и полученная оценка.
Сгенерированный отчет можно редактировать с помощью специальных панелей и окон свойств.
Результат генерации отчета можно посмотреть, войдя в режим просмотра
(вкладка внизу области отчета).
Рис. 64. Сгенерированный отчет.
97
СПИСОК ЛИТЕРАТУРЫ
1. Бейли, Л. Изучаем SQL [Текст]: пер.с англ./ Линн Бейли – СПб: ИД
«Питер», 2012. – 592 с.
2. Кренке, Д. Теория и практика построения баз данных [Текст]: пер.с англ. / Дэвид Кренке. – СПб: Питер, 2003. – 800 с.
3. Ульман., Д. Основы реляционных баз данных [Текст]: пер.с англ. /
Джеффри Д.Ульман, Дженифер Уидом. – М: Вильямс, 2006. – 382 с.
4. Лобел, Л. Разработка приложений на основе Microsoft SQL Server 2008
[Текст]: пер с англ./ Л. Лобел, Э.Дж. Браст, С. Форте. – СПб.:БХВ-
Петербург, 2010. – 1024 с.
5. Кузнецов, М. MySQL 5 в подлиннике [Текст] / Максим Кузнецов,
Игорь Симдянов. – СПб: БХВ-Петербург, 2010. – 1007 с.
6. Уорсли, Д. PostgreSQL [Текст]: пер.с англ. /Дж.Уорсли, Дж.Дрейк. –
СПб: Питер, 2003. – 496 с.
7. Пинягина, О.В. Практикум по курсу "Базы данных" [Текст]/
О.В.Пинягина, И.А.Фукин. – Казань, Казанский федеральный универ- ситет, 2012. – 92 c.
8. Пирогов, В.Ю. Информационные системы и базы данных: организация и проектирование [Текст]/ В.Ю.Пирогов. – СПб: БХВ-Петербург, 2009.
– 528 с.
9. Советов, Б.Я. Базы данных: теория и практика: Учебник для бакалавров
[Текст]/ Б.Я. Советов, В.В. Цехановский, В.Д. Чертовской. – М.:
Юрайт, 2013. – 463 c.
98
Учебное пособие
Андрианова Анастасия Александровна
Мухтарова Татьяна Маратовна
Рубцова Рамиля Гакилевна
ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО КУРСУ
«ТЕХНОЛОГИИ БАЗ ДАННЫХ»
Дизайн обложки
Р.Г.Рубцова
Подписано в печать _10.03.2016.
Бумага офсетная. Печать цифровая.
Формат 60х84 1/16. Гарнитура «Times New Roman». Усл. печ. л. .
Тираж экз. Заказ
Отпечатано с готового оригинал-макета в типографии Издательства Казанского университета
420008, г. Казань, ул. Профессора Нужина, 1/37 тел. (843) 233-73-59, 233-73-28