5. Создание процедурных ограничений целостности

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

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

Назначим код ошибки 75001 и текст сообщения 'The DayOfWeekNumber is incorrect'.

Текст на создание триггера находится в приложении.

6. Создание типичных запросов к базе данных

6.1 Примеры операторов, которые записывают данные в таблицы

Для каждой таблицы приведем примеры запросов.

Таблица WeekType:

INSERT INTO WEEKTYPE(NAME) VALUES('Первая');

INSERT INTO WEEKTYPE(NAME) VALUES('Вторая');

Таблица Time:

INSERT INTO TIME(HOUR, MINUTE) VALUES(8, 30);

INSERT INTO TIME(HOUR, MINUTE) VALUES(10, 25);

Таблица ChairType:

INSERT INTO CHAIRTYPE(NAME) VALUES('Общая');

Таблица University:

INSERT INTO UNIVERSITY(NAME, ACCREDITATIONLEVEL, CREATIONDATE, ADDRESS, PHONE)

VALUES('Национальный Технический Университет - "Харьковский политехнический институт"', 4,

'3/09/1885', 'Украина 61002, г. Харьков, ул. Фрунзе, 21',

'706-27-49');

Таблица Department:

INSERT INTO DEPARTMENT(UNIVERSITYID, NAME, CREATIONDATE)

VALUES(1, 'Биологический', '1/9/1900');

Таблица Chair:

INSERT INTO CHAIR(CHAIRTYPEID, DEPARTMENTID, NAME, CREATIONDATE)

VALUES(2, 1, 'Биохимии', '1/9/1901');

Таблица Lecturer:

INSERT INTO LECTURER(CHAIRID, LASTNAME, FIRSTNAME, MIDDLENAME, ACADEMICDEGREENAME, POST)

VALUES(1, 'Перский', 'Евгений', 'Ефроимович', 'Профессор', 'Заведующий кафедрой');

Таблица Discipline:

INSERT INTO DISCIPLINE(DISCIPLINENUMBER, NAME, HOURS) VALUES('54354551', 'Высшая математика', 120);

Таблица Lecture:

INSERT INTO LECTURE(GROUPNAME, LECTURERID, TIMEID, WEEKTYPEID, DISCIPLINENUMBER, DAYOFWEEKNUMBER, AUDITORIUM, BUILDINGNAME)

VALUES('КИТ-64', 27, 1, 1, '54354559', 1, '202', 'ГАК');

7.2 Примеры операторов на выборку данных из базы данных

1. Определить университет, который имеет наибольшее число профессоров.

SELECT T.Name FROM

(SELECT U1.Name, count(L1.LecturerID) as ProfessorsCount

FROM University U1, Department D1, Chair C1, Lecturer L1

WHERE

L1.AcademicDegreeName='Профессор'

AND

L1.ChairID=C1.ChairID

AND

C1.DepartmentID=D1.DepartmentID

AND

D1.UniversityID=U1.UniversityID

group by U1.Name

) T

order by T.ProfessorsCount DESC

FETCH FIRST 1 ROWS ONLY

Результат выборки:

Харьковский Государственный Технический университет Радиоэлектроники

1 record(s) selected.

2. Вывести информацию о факультетах каждого университета и указать количество кафедр для этого факультета.

SELECT

D.Name, D.CreationDate, count(C.ChairID) as ChairsCount, U.Name AS University

FROM

Department D, University U, Chair C

WHERE

U.UniversityID=D.UniversityID

AND

C.DepartmentID=D.DepartmentID

GROUP BY D.Name, D.CreationDate, U.Name

ORDER BY U.Name, D.Name

Результат выборки:

SELECT

Lecture. BuildingName

FROM

Lecture, Lecturer, Chair, Department, University

WHERE

University.Name='Национальный Технический Университет - "Харьковский политехнический институт"'

AND

Department. UniversityID=University. UniversityID

AND

Chair. DepartmentID=Department. DepartmentID

AND

Lecturer.ChairID=Chair.ChairID

AND

Lecture.LecturerID=Lecturer.LecturerID

GROUP BY Lecture.BuildingName

Результат выборки:

BUILDINGNAME

АК

ВЦ

ГАК

У1

У2

5 record(s) selected

6. Выполнить предыдущий запрос используя представление.

SELECT

ScheduleView.BuildingName

FROM

ScheduleView

GROUP BY

ScheduleView.BuildingName

Результат выборки:

BUILDINGNAME

АК

ВЦ

ГАК

У1

У2

5 record(s) selected

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

SELECT SV1.* FROM

ScheduleView SV1, ScheduleView SV2

WHERE

SV1.GroupName=SV2.GroupName

AND

SV2.DayOfWeekNumber=SV1.DayOfWeekNumber

AND

SV2.DayOfWeekNumber=SV1.DayOfWeekNumber

AND

SV1.Hour=SV2.Hour

AND

SV1.Minute=SV2.Minute

AND

SV1.DisciplineName <> SV2.DisciplineName

ORDER BY

SV1.GroupName, SV1.DayOfWeekNumber, SV1.Hour, SV1.Minute

Результат выборки:

КИТ-44a 1 Вторая 10 25 Компьютерная графика Гладких Татьяна 506 У1 КИТ-44a 1 Первая

КИТ-44a 1 Первая 10 25 Теория алгоритмов Солощук Михаил 305 АК КИТ-44a 1 Вторая

КИТ-44a 3 Вторая 8 30 Высшая физика Старусев Олег 22 У2 КИТ-44a 3 Первая

КИТ-44a 3 Первая 8 30 Высшая математика Деревянко Александр 304 ГАК КИТ-44a 3 Вторая

КИТ-44a 5 Вторая 10 25 Философия Старусев Олег 803 У1 КИТ-44a 5 Первая

КИТ-44a 5 Первая 10 25 Культурология Старусев Олег 501 У1 КИТ-44a 5 Вторая

КИТ-64 1 Вторая 10 25 Компьютерная графика Гладких Татьяна 506 У1 КИТ-64 1 Первая

КИТ-64 1 Первая 10 25 Теория алгоритмов Солощук Михаил 305 АК КИТ-64 1 Вторая

КИТ-64 3 Вторая 8 30 Высшая физика Старусев Олег 22 У2 КИТ-64 3 Первая

КИТ-64 3 Первая 8 30 Высшая математика Деревянко Александр 304 ГАК КИТ-64 3 Вторая

КИТ-64 5 Вторая 12 35 Философия Старусев Олег 803 У1 КИТ-64 5 Первая

КИТ-64 5 Первая 12 35 Культурология Старусев Олег 501 У1 КИТ-64 5 Вторая

12 record(s) selected.

8. Выбрать коды всех дисциплин, которые имеют корень 'комп'

SELECT D.DisciplineNumber

FROM Discipline D

WHERE D.Name LIKE '%комп%'

Результат выборки:

DISCIPLINENUMBER

54354565

54354569

9. Выбрать расписание не Первую неделю для группы КИТ-64

SELECT * FROM scheduleview

WHERE GroupName='КИТ-64' and WeekName='Первая'

ORDER BY DAYOFWEEKNUMBER, HOUR, MINUTE

Результат выборки:

GROUPNAME DAYOFWEEKNUMBER WEEKNAME HOUR MINUTE DISCIPLINENAME LECTURERLASTNAME LECTURERFIRSTNAME AUDITORIUM BUILDINGNAME

КИТ-64 1 Первая 8 30 Финансы Гуридина Ирина 202 ГАК

КИТ-64 1 Первая 10 25 Теория алгоритмов Солощук Михаил 305 АК

КИТ-64 1 Первая 12 35 Системное программирование Сомхиева Ольга 305 АК

КИТ-64 2 Первая 8 30 Дискретная математика Пустовойтов Павел 23 ВЦ

КИТ-64 2 Первая 10 25 Теория вероятности Леонов Сергей 305 У1

КИТ-64 2 Первая 12 35 Организация баз данных и знаний Фастовский Эдуард 305 АК

КИТ-64 3 Первая 8 30 Высшая математика Деревянко Александр 304 ГАК

КИТ-64 3 Первая 10 25 История Украины Бреславец Виталий 304 У1

КИТ-64 3 Первая 12 35 Основы безопасности жизнедеятельности Панченко Владимир 1206 АК

КИТ-64 4 Первая 8 30 Структуры организации данных Гуридина Ирина 202 ГАК

КИТ-64 4 Первая 10 25 Методы и средства компьютерных и информационных технологий Бреславец Виталий 318 У1

КИТ-64 4 Первая 12 35 Основы программирования и алгоритмические языки Пустовойтов Павел 305 АК

КИТ-64 5 Первая 8 30 Микропроцессорные системы Порошин Сергей 202 ГАК

КИТ-64 5 Первая 10 25 Теория алгоритмов Солощук Михаил 305 АК

КИТ-64 5 Первая 12 35 Культурология Старусев Олег 501 У1

15 record(s) selected.

10. Для каждого профессора определить количество читаемых им предметов в группе 'КИТ-64'.

select Lecturer.LastName, Lecturer.FirstName,

count(DisciplineNumber)

from Lecturer, Lecture

where Lecture.LecturerID=Lecturer.LecturerID

AND

Lecturer.ACADEMICDEGREENAME='Профессор'

group by

Lecturer.LastName, Lecturer.FirstName

Результат выборки:

LASTNAME FIRSTNAME 3

Гладких Татьяна 2 Панченко Владимир 4Порошин Сергей 4 Солощук Михаил 6Фастовский Эдуард 4 5 record(s) selected.