Проектирование БД для автоматизации задач составления учебного расписания вуза

1.1. Описание предметной области 3
1.2. Инфологическое моделирование предметной области 5
1.3. Реализация БД в MySQL Workbench 9
1.4. SQL-запросы SELECT 24

`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('3', '1', '1', '2', '18');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('4', '1', '2', '2', '10');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('5', '2', '1', '1', '12');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('6', '2', '2', '1', '10');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('7', '2', '4', '1', '2');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('8', '3', '1', '3', '18');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('9', '3', '3', '3', '10');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('10', '3', '2', '3', '10');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('11', '4', '1', '1', '18');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('12', '4', '2', '1', '10');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('13', '5', '1', '1', '24');INSERT INTO `sch`.`trud` (`idTrud`, `idDiscipline`, `Occupation`, `Semester`, `VolumeHours`) VALUES ('14', '5', '3', '1', '15');Результат выполнения заполнения таблиц данными:INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('1', 'Term', '1', '1', '8');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('2', 'Term', '1', '1', '9');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('3', 'Term', '1', '1', '10');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('4', 'Term', '1', '1', '11');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('5', 'Term', '1', '2', '8');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('6', 'Term', '1', '2', '9');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('7', 'Term', '1', '2', '10');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('8', 'Term', '1', '2', '11');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('9', 'Term', '1', '3', '8');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('10', 'Term', '1', '3', '9');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('11', 'Term', '1', '3', '10');INSERT INTO `sch`.`schedule` (`ID_Schedule`, ` TypeРасписание`, ` NumberWeek`, `DayWeek`, `TimeStart`) VALUES ('12', 'Term', '1', '3', '11');Результат выполнения заполнения таблиц данными:INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1000', '40', 'Lecture');INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1001', '40', 'Lecture');INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1002', '40', 'Lecture');INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1003', '15', 'Lab');INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1004', '15', 'Lab');INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1005', '15', 'Lab');INSERT INTO `sch`.`classroom` (`NClassroom`, `Sits`, `TypeClassroom`) VALUES ('1006', '100', 'Flow');Результат выполнения заполнения таблиц данными:INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1000', '1');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1001', '1');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1002', '1');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1003', '2');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1004', '2');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1005', '2');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1003', '3');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1004', '3');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1005', '3');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1006', '1');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1006', '2');INSERT INTO `sch`.`facilities` (`NClassroom`, `Occupation`) VALUES ('1000', '2');Результат выполнения заполнения таблиц данными:INSERT INTO `sch`.`scheduleclass` (`NClassroom`, `idSchedule`, `idTeacher`, `IDGroup`, `idTrud`) VALUES ('1000', '1', '100', '1091', '1');INSERT INTO `sch`.`scheduleclass` (`NClassroom`, `idSchedule`, `idTeacher`, `IDGroup`, `idTrud`) VALUES ('1000', '2', '100', '1091', '1');INSERT INTO `sch`.`scheduleclass` (`NClassroom`, `idSchedule`, `idTeacher`, `IDGroup`, `idTrud`) VALUES ('1001', '3', '101', '1092', '2');INSERT INTO `sch`.`scheduleclass` (`NClassroom`, `idSchedule`, `idTeacher`, `IDGroup`, `idTrud`) VALUES ('1001', '4', '101', '1092', '2');Результат выполнения заполнения таблиц данными:SQL-запросы SELECTа) Запрос на извлечение данных из нескольких связанных таблиц с использованием соединения по равенству полей таблиц. ( “=” в условии WHERE);Отчет о номерах дисциплин, и преподавателей, которые эти дисциплины читают: select t.FirstName +' '+ t.MidleName +' ' + t.Surname, d.idDisciplinefromsch.Teacher as t, sch.TchDiscipline as dwheret.idTeacher=d.idTeacherOrder by d.idDisciplineб) Запрос а) реализовать вторым способом с использованием INNERJOIN;select t.FirstName +' '+ t.MidleName +' ' + t.Surname, d.idDisciplinefromsch.Teacher tINNER JOIN sch.TchDiscipline dont.idTeacher=d.idTeacherOrder by d.idDisciplineDescв) Запрос с использованием процедурных возможностей SQL (команда CASE).Получим таблицу расписаний с корректными именами дней недели и времени.SELECT Day, 'Time' =CASE TimeStartWHEN 8 THEN '8:00'WHEN 9 THEN '9:00'WHEN 10 THEN '10:00'WHEN 11 THEN '11:00'ELSE 'Go home'ENDFrom(SELECT TimeStart, 'Day' =CASE DayWeekWHEN 1 THEN 'Monday'WHEN 2 THEN 'Tuesday'WHEN 3 THEN 'Wednesday'WHEN 4 THEN 'Thursday'ELSE 'Something Else'ENDFROM sch.Schedule) wг) Запрос с использованием группировок,группировочныхфункцийи условий на группы (HAVING)Получим отчет об общем количестве часов по каждой дисциплине:selectNameDiscipline, ITOGOfromsch.Discipline as dJoin(selectt.idDiscipline,sum (t.VolumeHours) as ITOGOfromsch.Trud tgroup by t.idDiscipline) as aond.idDiscipline=a.idDisciplineOrderby ITOGO DescОтфильтруем дисциплины, у которых общее количество часов меньше 30:selectNameDiscipline, a.ITOGOfromsch.Discipline as dJoin(selectt.idDiscipline, sum (t.VolumeHours) as ITOGOfromsch.Trud tgroup by t.idDisciplineHaving sum (t.VolumeHours) >30 ) as aond.idDiscipline=a.idDisciplineOrder by ITOGO Descд) Запрос с использованием внешнего соединения (OUTERJOIN);Получим отчет о свободных и занятых аудиториях:Select t.NClassroom as Class, r.Surname+ ' '+r.FirstName+' '+r.MidleName as Teacher,t.IDGroup as Gruppa, t.Sits, t.TypeClassroomfromsch.Teacher as rright outer join(selectc.NClassroom , s.idTeacher, s.IDGroup, c.Sits, c.TypeClassroomfromSch.ScheduleClass sright outer join sch.Classroom conc.NClassroom=s.NClassroom) tonr.idTeacher=t.idTeacherOrder by t.NClassroomе) Запрос с использованием вложенного подзапроса (вложенный SELECT);select Surname + ' '+ FirstName+' '+MidleName as FIO, a.NameDisciplinefromsch.TeacherJoin(selecttd.idTeacher, d.NameDisciplinefromsch.TchDiscipline tdjoinsch.Discipline as dontd.idDiscipline= d.idDiscipline) aonTeacher.idTeacher=a.idTeacherж) Создать представление (VIEW) по любому из запросов а)-д).Create View sch.occupationClassasSelect t.NClassroom as Class, r.Surname+ ' '+r.FirstName+' '+r.MidleName as Teacher,t.IDGroup as Gruppa, t.Sits, t.TypeClassroomfromsch.Teacher as rright outer join(selectc.NClassroom , s.idTeacher, s.IDGroup, c.Sits, c.TypeClassroomfromSch.ScheduleClass sright outer join sch.Classroom conc.NClassroom=s.NClassroom) tonr.idTeacher=t.idTeacherРеализовать выбранный запрос с использованием созданного представления.Select * fromsch.occupationClass