Statements

Определите, какие индексы требуется добавить к таблицам базы данных «Университет» на основе запросов из ДЗ-5, 6 и 7.
Пусть частым запросом является определение среднего балла студентов группы по дисциплине. Как будет выглядеть запрос и какие индексы могут помочь при его исполнении?
Придумайте три запроса, требующих новых индексов и запишите их. Если в результате, некоторые из старых индексов станут бесполезными, удалите их.

При выполнении задания считайте, что ФЗ соответствуют полученным в ДЗ-3 и 4.

1

1.S

-- Первичный ключ, не нужен порядок.
-- ДЗ-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);

1.G

-- Первичный ключ, не нужен порядок.
-- ДЗ-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);

1.C

-- Первичный ключ, не нужен порядок.
-- ДЗ-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);

1.L

-- Первичный ключ, не нужен порядок.
-- ДЗ-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);

1.P

-- Используется для соединений. 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);

1.M

-- Используется для соединений.
-- ДЗ-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);

2

2.Q

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 = 'Базы данных';

2.I

-- Ускорение соединения с 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);

3

3.1.Q

-- Список студентов курса в алфавитном порядке
SELECT StudentName
FROM Students
     NATURAL JOIN Plan
     NATURAL JOIN Courses
WHERE CourseName = 'Управление проектами'
ORDER BY StudentName;

Использование идентификаторов место имён и названий

3.1.I

-- Индекс на дереве позволяет избавиться от сортировки результатов при
-- запросов, требующих упорядочивание по именам студентов. Индекс уже
-- хранит нужный порядок
CREATE INDEX StudentsStudentNameOrd ON Students USING BTREE (StudentName);

3.2.Q

-- Получить информацию о группах определенного (4-го) курса
SELECT GroupId, GroupName
FROM Groups
WHERE GroupName LIKE 'M34%';

3.2.I

-- Нужен поиск по префиксу. При чем GroupName - ключ
CREATE UNIQUE INDEX GroupsGroupNameOrd ON Groups USING BTREE (GroupName);

3.3.Q

-- Поиск группы студента по префиксу имени
SELECT
    StudentName,
    GroupName
FROM
    Students
    JOIN GROUPS ON Students.GroupId = Groups.GroupId
WHERE
    Students.StudentName LIKE 'Иванов%';

3.3.I

-- Нужен поиска по префиксу строки. Так же оптимизировать соединение с
-- Groups, добавив GroupId
CREATE INDEX StudentsStudentName ON Students USING BTREE (StudentName, GroupId);

Comments

Запросы из предыдущих ДЗ
- 1.S. Students
  - Индекс мало/бесполезен: ДЗ-5.6.1 для create index StudentsGroupId on Students using HASH (GroupId);
- 1.G. Groups
- 1.C. Courses
  - Индекс мало/бесполезен: ДЗ-5.4.2 для create UNIQUE index CoursesCourseId on Courses using HASH (CourseId);
  - Индекс мало/бесполезен: ДЗ-5.5.1 для create UNIQUE index CoursesCourseId on Courses using HASH (CourseId);
  - Индекс мало/бесполезен: ДЗ-5.5.2 для create UNIQUE index CoursesCourseId on Courses using HASH (CourseId);
- 1.L. Lecturers
  - Индекс мало/бесполезен: ДЗ-5.3.6 для create UNIQUE index LecturersLecturerId on Lecturers using HASH (LecturerId);
  - Индекс мало/бесполезен: ДЗ-5.3.4 для create UNIQUE index LecturersLecturerId on Lecturers using HASH (LecturerId);
  - Индекс мало/бесполезен: ДЗ-5.6.1 для create UNIQUE index LecturersLecturerId on Lecturers using HASH (LecturerId);
- 1.P. Plan
  - Индекс мало/бесполезен: ДЗ-5.3.4 для create UNIQUE index PlanCourseIdGroupId on Plan using BTREE (CourseId, GroupId);
  - Индекс мало/бесполезен: ДЗ-5.6.4 для create UNIQUE index PlanCourseIdGroupId on Plan using BTREE (CourseId, GroupId);
  - Индекс мало/бесполезен: ДЗ-5.3.4 для create UNIQUE index PlanGroupIdCourseId on Plan using BTREE (GroupId, CourseId);
- 1.M. Marks
  - Индекс мало/бесполезен: ДЗ-5.3.6 для create UNIQUE index MarksStudentIdCourseId on Marks using BTREE (StudentId, CourseId);
  - Индекс мало/бесполезен: ДЗ-5.3.6 для create UNIQUE index MarksMark on Marks using BTREE (Mark, CourseId, StudentId);
  - Индекс мало/бесполезен: ДЗ-5.5.3 для create UNIQUE index MarksMark on Marks using BTREE (Mark, CourseId, StudentId);
  - Индекс мало/бесполезен: ДЗ-6.4.1 для create UNIQUE index MarksMark on Marks using BTREE (Mark, CourseId, StudentId);
Средний балл
- 2.Q. Запрос
- 2.I. Индексы
Новые запросы
- 3.1.Q. Запрос
  - Бесполезно без ограничения множества студентов
- 3.1.I. Индексы
- 3.2.Q. Запрос
- 3.2.I. Индексы
  - Ожидалось, что такой индекс будет обявлен в первом разделе
- 3.3.Q. Запрос
  - Не хватает пробела в like ‘prefix %’
- 3.3.I. Индексы