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

`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.