При выполнении задания считайте, что ФЗ соответствуют полученным в ДЗ-3 и 4.
-- Первичный ключ, не нужен порядок.
-- ДЗ-5.1.1. Информацию о студентах с заданным идентификатором
-- (StudentId, StudentName, GroupId по :StudentId)
-- ДЗ-5.2.1. Полную информацию о студентах с заданным идентификатором
-- (StudentId, StudentName, GroupName по :StudentId)
-- ДЗ-7.2.1. Напишите запросы, обновляющие данные студентов изменение
-- имени студента (StudentId, StudentName)
CREATE UNIQUE INDEX StudentsStudentId ON Students USING HASH (StudentId);
-- Используем целую строку, высокая селективность, не нужен порядок,
-- ДЗ-5.1.2. Информацию о студентах с заданным ФИО (StudentId,
-- StudentName, GroupId по :StudentName)
-- ДЗ-5.2.2. Полную информацию о студентах с заданным ФИО (StudentId,
-- StudentName, GroupName по :StudentName)
-- ДЗ-6.1.1. Информацию о студентах с заданным ФИО (StudentId,
-- StudentName, GroupId по :StudentName)
CREATE INDEX StudentsStudentName ON Students USING HASH (StudentName);
-- Внешний ключ, средняя селективность, не нужен порядок,
-- ДЗ-5.3.3. Информацию о студентах с заданной оценкой по дисциплине с
-- заданным идентификатором (StudentId, StudentName, GroupId по :Mark,
-- :CourseId)
-- ДЗ-5.3.4. Информацию о студентах с заданной оценкой по дисциплине
-- которую у них вёл лектор, заданный ФИО (StudentId, StudentName,
-- GroupId по :Mark, :LecturerName)
-- ДЗ-5.6.1. Идентификаторы студентов по преподавателю имеющих хотя бы
-- одну оценку у преподавателя (StudentId по :LecturerName).
CREATE INDEX StudentsGroupId ON Students USING HASH (GroupId);
-- Первичный ключ, не нужен порядок.
-- ДЗ-5.2.1. Полную информацию о студентах с заданным идентификатором
-- (StudentId, StudentName, GroupName по :StudentId)
-- ДЗ-5.2.2. Полную информацию о студентах с заданным ФИО (StudentId,
-- StudentName, GroupName по :StudentName)
-- ДЗ-6.2.1. Полную информацию о студентах для всех студентов
-- (StudentId, StudentName, GroupName).
CREATE UNIQUE INDEX GroupsGroupId ON Groups USING HASH (GroupId);
-- Используем целую строку, есть ФЗ GroupName -> GroupId (GroupName -
-- ключ),не нужен порядок.
-- ДЗ-6.1.2. Информацию о студентах учащихся в заданной группе
-- (StudentId, StudentName, GroupId по :GroupName)
-- ДЗ-7.1.2. Напишите запросы, удаляющие студентов учащихся в группе,
-- заданной названием (GroupName)
-- ДЗ-7.2.4. Напишите запросы, обновляющие данные студентов перевод
-- всех студентов из группы в группу по названиям (GroupName,
-- FromGroupName)
CREATE UNIQUE INDEX GroupsGroupName ON Groups USING HASH (GroupName);
-- Первичный ключ, не нужен порядок.
-- ДЗ-5.4.2. Информацию о студентах не имеющих оценки по дисциплине
-- среди студентов, у которых есть эта дисциплина (StudentId,
-- StudentName, GroupId по :CourseName)
-- ДЗ-5.5.1. Для каждого студента ФИО и названия дисциплин которые у
-- него есть по плану (StudentName, CourseName)
-- ДЗ-5.5.2. Для каждого студента ФИО и названия дисциплин есть, но у
-- него нет оценки (StudentName, CourseName)
CREATE UNIQUE INDEX CoursesCourseId ON Courses USING HASH (CourseId);
-- Покрывающей индекс, CourseId - ключ
-- ДЗ-5.4.2. Информацию о студентах не имеющих оценки по дисциплине
-- среди студентов, у которых есть эта дисциплина (StudentId,
-- StudentName, GroupId по :CourseName)
-- ДЗ-5.4.1. Информацию о студентах не имеющих оценки по дисциплине
-- среди всех студентов (StudentId, StudentName, GroupId по
-- :CourseName)
-- ДЗ-6.2.5. Полную информацию о студентах студентов, не имеющих
-- оценки по дисциплине, у которых есть эта дисциплина (StudentId,
-- StudentName, GroupName по :CourseName)
CREATE UNIQUE INDEX CoursesCourseName ON Courses USING BTREE (CourseName, CourseId);
-- Первичный ключ, не нужен порядок.
-- ДЗ-5.3.6. Информацию о студентах с заданной оценкой по дисциплине
-- которую вёл лектор, заданный ФИО (StudentId, StudentName, GroupId
-- по :Mark, :LecturerName)
-- ДЗ-5.3.4. Информацию о студентах с заданной оценкой по дисциплине
-- которую у них вёл лектор, заданный ФИО (StudentId, StudentName,
-- GroupId по :Mark, :LecturerName)
-- ДЗ-5.6.1. Идентификаторы студентов по преподавателю имеющих хотя бы
-- одну оценку у преподавателя (StudentId по :LecturerName).
CREATE UNIQUE INDEX LecturersLecturerId ON Lecturers USING HASH (LecturerId);
-- Покрывающей индекс, LecturerId - ключ
-- ДЗ-5.3.6. Информацию о студентах с заданной оценкой по дисциплине
-- которую вёл лектор, заданный ФИО (StudentId, StudentName, GroupId
-- по :Mark, :LecturerName)
-- ДЗ-5.6.1. Идентификаторы студентов по преподавателю имеющих хотя бы
-- одну оценку у преподавателя (StudentId по :LecturerName)
-- ДЗ-6.5.1. Идентификаторы студентов по преподавателю имеющих хотя бы
-- одну оценку у преподавателя (StudentId по :LecturerName)
CREATE UNIQUE INDEX LecturersLecturerName ON Lecturers USING BTREE (LecturerName, LecturerId);
-- Используется для соединений. CourseId, GroupId - ключ
-- ДЗ-5.3.4. Информацию о студентах с заданной оценкой по дисциплине
-- которую у него вёл лектор, заданный ФИО (StudentId, StudentName,
-- GroupId по :Mark, :LecturerName).
-- ДЗ-5.6.4. Идентификаторы студентов по преподавателю имеющих оценки
-- по всем дисциплинам преподавателя, которые он вёл у этого студента
-- (StudentId по :LecturerName).
-- ДЗ-6.2.4. Полную информацию о студентах студентов, не имеющих
-- оценки по дисциплине, у которых есть эта дисциплина (StudentId,
-- StudentName, GroupName по :CourseId)
CREATE UNIQUE INDEX PlanCourseIdGroupId ON Plan USING BTREE (CourseId, GroupId);
-- Используется для соединений. Ищем по LecturerId
-- ДЗ-5.6.1. Идентификаторы студентов по преподавателю имеющих хотя бы
-- одну оценку у преподавателя (StudentId по :LecturerName)
-- ДЗ-5.3.4. Информацию о студентах с заданной оценкой по дисциплине
-- которую у него вёл лектор, заданный ФИО (StudentId, StudentName,
-- GroupId по :Mark, :LecturerName).
-- ДЗ-5.3.5. Информацию о студентах с заданной оценкой по дисциплине
-- которую вёл лектор, заданный идентификатором (StudentId,
-- StudentName, GroupId по :Mark, :LecturerId)
CREATE INDEX PlanLecturerIdCourseId ON Plan USING BTREE (LecturerId, CourseId);
-- Используется для соединений. GroupId, CourseId - ключ
-- ДЗ-5.3.4. Информацию о студентах с заданной оценкой по дисциплине
-- которую у него вёл лектор, заданный ФИО (StudentId, StudentName,
-- GroupId по :Mark, :LecturerName).
-- ДЗ-5.6.4. Идентификаторы студентов по преподавателю имеющих оценки
-- по всем дисциплинам преподавателя, которые он вёл у этого студента
-- (StudentId по :LecturerName).
-- ДЗ-6.2.4. Полную информацию о студентах студентов, не имеющих
-- оценки по дисциплине, у которых есть эта дисциплина (StudentId,
-- StudentName, GroupName по :CourseId)
CREATE UNIQUE INDEX PlanGroupIdCourseId ON Plan USING BTREE (GroupId, CourseId);
-- Используется для соединений.
-- ДЗ-5.3.6. Информацию о студентах с заданной оценкой по дисциплине
-- которую вёл лектор, заданный ФИО (StudentId, StudentName, GroupId
-- по :Mark, :LecturerName)
-- ДЗ-6.5.3. Идентификаторы студентов по преподавателю имеющих оценки
-- по всем дисциплинам преподавателя (StudentId по :LecturerName).
-- ДЗ-7.1.6. Напишите запросы, удаляющие студентов студентов, c
-- долгами (здесь и далее — по отсутствию оценки)
CREATE UNIQUE INDEX MarksStudentIdCourseId ON Marks USING BTREE (StudentId, CourseId);
-- Используется для соединений.
-- ДЗ-6.1.3. Информацию о студентах c заданной оценкой по дисциплине,
-- заданной идентификатором (StudentId, StudentName, GroupId по :Mark,
-- :CourseId)
-- ДЗ-6.4.1. Студенты и дисциплины, такие что дисциплина есть в его
-- плане, и у студента долг по этой дисциплине долгом считается
-- отсутствие оценки (StudentName, CourseName)
-- ДЗ-6.5.3. Идентификаторы студентов по преподавателю имеющих оценки
-- по всем дисциплинам преподавателя (StudentId по :LecturerName).
CREATE UNIQUE INDEX MarksCourseIdStudentId ON Marks USING BTREE (CourseId, StudentId);
-- Нужен порядок на Mark, покрывающий индекс.
-- ДЗ-5.3.6. Информацию о студентах с заданной оценкой по дисциплине
-- которую вёл лектор, заданный ФИО (StudentId, StudentName, GroupId
-- по :Mark, :LecturerName)
-- ДЗ-5.5.3. Для каждого студента ФИО и названия дисциплин есть, но у
-- него не 4 или 5 (StudentName, CourseName)
-- ДЗ-6.4.1. Студенты и дисциплины, такие что дисциплина есть в его
-- плане, и у студента долг по этой дисциплине долгом считается
-- отсутствие оценки (StudentName, CourseName)
CREATE UNIQUE INDEX MarksMark ON Marks USING BTREE (Mark, CourseId, StudentId);
SELECT
AVG(CAST(Mark AS float)) AS AvgMark
FROM
GROUPS
JOIN Students ON Groups.GroupId = Students.GroupId
JOIN Marks ON Students.StudentId = Marks.StudentId
JOIN Courses ON Marks.CourseId = Courses.CourseId
WHERE
Marks.Mark IS NOT NULL
AND Groups.GroupName = 'M3435'
AND Courses.CourseName = 'Базы данных';
-- Ускорение соединения с Groups.
CREATE INDEX StudentsGroupId ON Students USING HASH (GroupId);
-- Ускорение соединения с Students и Courses. Покрывающий индекс
CREATE UNIQUE INDEX MarksCovering ON Marks USING BTREE (CourseId, StudentId, Mark);
-- Ускорение поиска по GroupName.
CREATE UNIQUE INDEX GroupsGroupName ON GROUPS USING HASH (GroupName);
-- Ускорение поиска по CourseName и его префиксам.
CREATE UNIQUE INDEX CoursesCourseName ON Courses USING BTREE (CourseName, CourseId);
-- Список студентов курса в алфавитном порядке
SELECT StudentName
FROM Students
NATURAL JOIN Plan
NATURAL JOIN Courses
WHERE CourseName = 'Управление проектами'
ORDER BY StudentName;
Использование идентификаторов место имён и названий
-- Индекс на дереве позволяет избавиться от сортировки результатов при
-- запросов, требующих упорядочивание по именам студентов. Индекс уже
-- хранит нужный порядок
CREATE INDEX StudentsStudentNameOrd ON Students USING BTREE (StudentName);
-- Получить информацию о группах определенного (4-го) курса
SELECT GroupId, GroupName
FROM Groups
WHERE GroupName LIKE 'M34%';
-- Нужен поиск по префиксу. При чем GroupName - ключ
CREATE UNIQUE INDEX GroupsGroupNameOrd ON Groups USING BTREE (GroupName);
-- Поиск группы студента по префиксу имени
SELECT
StudentName,
GroupName
FROM
Students
JOIN GROUPS ON Students.GroupId = Groups.GroupId
WHERE
Students.StudentName LIKE 'Иванов%';
-- Нужен поиска по префиксу строки. Так же оптимизировать соединение с
-- Groups, добавив GroupId
CREATE INDEX StudentsStudentName ON Students USING BTREE (StudentName, GroupId);