Построение логической модели БД

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

а) Каждая сущность инфологической модели отображается отдельной таблицей.

б) Каждая связь типа многие-ко-многим отображается отдельной таблицей.

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

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

Таблица 1. Группа – Sgroup

G_Id	S_index
Pk

 

Таблица 2. Студент – Student

S_Id	Name	G_Id
Pk		Fk

 

Таблица 3. Преподаватель – Teacher

T_Id	T_Name
Pk

 

Таблица 4. Экзамен – Exam

E_Id	E_date	S_id	Semester	G_Id
Pk		Fk		Fk

 

Таблица 5. Дисциплина – Subject

S_id	S_name
Pk

 

Таблица 6. Студент-Экзамен – Vedomost

E_Id	S_Id	Mark
Pk, Fk	Pk, Fk

 

Таблица 7. Преподаватель-Экзамен – TeX

T_Id	E_Id
Pk,Fk	Pk,Fk

 

Разработка оперативных запросов.

1. Показать результаты экзамена по Математике группе РК6-41 в 2016 году по форме

Оценка

Количество оценок на экзамене

 

Select mark, count(*)

From Exam join Sgroup using(g_id) join Subject using(s_id)

Where s_index=’PK6-41’ and s_name=’Математика’ and year(e_date)=2016

Group by mark;

1. Показать, сколько студентов в каждой группе, сдававшей экзамен по Математике в 5

семестре 2016 года, получили отличные оценки

Select s_index, count(*)

From exam join vedomost using(e_id) join Sgroup using(g_id) join Subject using(s_id)

Where s s_name=’Математика’ and year(e_date)=2016 and semester=5

Group by s_index;

1. Показать, сколько экзаменов принял преподаватель Петров в 2016 году.

Select count(*)

From Exam join TeX using(e_id) where year(e_date)=2016 and t_id =(select t_id from Teacher where t_name=’Petrov’);

1. Показать фамилии преподавателей, которые никогда не принимали экзамены.

Select t_name

From Teacher left join TeX using(t_id)

Where e_id Is NULL;

1. Показать индексы групп, которые не сдали ни одного экзамена в 2016 году

Select s_index

From Sgroup left join (Select * From Exam Where year(e_date)=2016) ex2016 using(g_id)

Where e_id Is NULL;

1. Показать дату первого экзамена весной 2016 года.

Select min(e_date)

From Exam

Where year(e_date)=2016 and semester in (2,4,6,8,10);

 

 

5.4. Разработка стандартного статистического отчета.

       Предполагаем, что отчеты создаются для анализа изменения успеваемости студентов по одним и тем же дисциплинам в разные годы. На этом этапе проектирования создадим всего один отчет.

Отчет 1

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

 

Кафедра

Дисциплина

Специальность

Год

Семестр

Средний балл в текущем году

 

       В этом отчете:

· Кафедра – это кафедра, которая читает дисциплину, в общем случае, различным потокам, в общем случае, на разных семестрах. Например, кафедра «Математика» читает дисциплину «Линейная алгебра» потокам РК6, ИУ5 и т.д.

· Дисциплина – это одна из дисциплин, читаемая кафедрой.

· Поток – это аббревиатура кафедры, на которой обучаются студенты, например РК6, ИУ5 и т. д.

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

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

 

Дополнения логической модели для хранения данных к Отчету 1

       Таблица «Кафедра» содержит данные об аббревиатуре, названии и телефоне кафедры. Сделаем предположение, что уникальный ключ кафедры является одновременно ее аббревиатурой. Например, кафедра имеет аббревиатуру РК6 и название «Системы автоматизированного проектирования».

 

       Таблица 8. Кафедра – Department

Dep_id	D_name	Telefon
Pk

 

       Теперь надо добавить данные о том, какими кафедрами читаются дисциплины и на каких потоках в каких семестрах. Данные о том, какой кафедрой читается дисциплина, добавим в таблицу «Дисциплина».

       Таблица 9. Дисциплина – Subject

S_id	S_name	Dep_id
Pk		Fk

 

       Данные о потоках и семестрах, на которых читается дисциплина, внесем в новую таблицу «Учебный план».

 

       Таблица 10. Учебный план – T_plan

T_Id	Sub_id	Dep_id	Semester
Pk	Fk	Fk

 

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

 

       Таблица 11. Группа – Sgroup

G_Id	S_index	Dep_id
Pk		Fk

 

       Осталось дополнить логическую модель базы данных таблицей, в которой будут сохраняться вычисленные агрегаты.

 

Таблица 12. Avg_mark

Am_Id	T_id	A_year	A_mark
Pk	Fk

 

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

 

Разработка плана  хранимой процедуры

а) В качестве входных параметров в процедуру должны передаваться год, семестр и дисциплина, для которых должны быть построены отчеты. Средние баллы по дисциплине (агрегаты) будут вычисляться для всех специальностей, сдававших дисциплину.

б) В процедуре создается курсор, в который из таблицы T_plan помещаются идентификаторы записей и шифры специальностей, сдававших дисциплину в заданный период.

в) Открывается курсор.

д) Далее в цикле извлекается очередная запись из курсора. Вычисляется средний балл, полученный студентами одной из специальностей по заданной дисциплине. Полученный результат заносится в таблицу Avg_mark.

е) Цикл завершается после обработки всех записей в курсоре.