Базы данных :: Домашние задания

Домашнее задание 1. Установка и использование СУБД

Установите систему управления реляционными базами данных.
Узнайте, как в вашей СУБД исполнять SQL в интерактивном режиме.
Узнайте, как в вашей СУБД исполнять SQL в пакетном режиме.
Разберитесь, как в вашей СУБД осуществляется поддержка русского языка.
Создайте базу данных и наполните ее в соответствии с примерами из презентации.

Ожидаемая структура проекта

Текстовая часть
1. Описание предметной области с кратким описанием неочевидных сущностей и атрибутов.
2. Предварительное разбиение на отношения (может отсутствовать).
3. Для каждого отношения: определение функциональных зависимостей, нормализация до 5НФ, денормализация (при необходимости).
4. Модель сущность-связь.
5. Физическая модель (должна соответствовать ERM) с указанием типов для доменов.
Часть на SQL
- ddl.sql – описание таблиц и индексов.
- insert.sql – добавление тестовых данных.
- select.sql – запросы на получение данных и представления.
- update.sql – запросы на изменение данных, хранимые процедуры и триггеры.

В рамках проекта:

Выберите тему проекта.
- Тема должна быть уникальной.
- Тема должна быть достаточно сложной.
- Нельзя брать темы: обучение в университете; торговля (как товарами, так и билетами); соревнования по программированию.

Домашнее задание 2. Моделирование БД «Университет»

Спроектируйте базу данных «Университет», позволяющую хранить информацию о студентах, группах, преподавателях, дисциплинах, оценках и клубах.

Составьте модель сущность-связь.
Преобразуйте модель сущность-связь в физическую модель.
Запишите физическую модель на языке SQL. Запись должна включать объявления ограничений.
Создайте базу данных по спроектированной модели.
Запишите операторы SQL, заполняющие базу тестовыми данными. Достаточно 2–3 записей на таблицу, если они в полной мере демонстрируют особенности БД.

Примечания

Не требуется поддержка:
- нескольких университетов;
- дисциплин по выбору;
- дисциплин с необычным распределением по группам (таких как физическая культура и иностранный язык);
- переводов между группами;
- исторических данных;
- нескольких оценок по одной дисциплине.
Многосеместровые дисциплины считаются по семестрам, например:
- Математический анализ (семестр 1);
- Математический анализ (семестр 2).

Форма для сдачи ДЗ. Проверка проводится в полуавтоматическом режиме, поэтому строго соблюдайте указанные форматы.

В рамках проекта:

Сделайте предварительную схему для БД проекта на основе моделей:
- модель сущность-связь;
- физическая модель;
- определение схемы на SQL.

Домашнее задание 3. Функциональные зависимости в БД «Университет»

Дано отношение с атрибутами StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, Mark, ClubId, ClubName, ClubPresidentId.

Найдите функциональные зависимости в данном отношении.
Найдите все ключи данного отношения.
Найдите замыкания множеств атрибутов:
1. GroupId, CourseId;
2. StudentId, CourseId;
3. StudentId, LecturerId;
4. StudentId, ClubId;
5. StudentId, ClubPresidentId;
6. GroupName, LecturerId.
Найдите неприводимое множество функциональных зависимостей для данного отношения.

Примечания

Не требуется поддержка:
- нескольких университетов;
- дисциплин по выбору;
- дисциплин с необычным распределением по группам (таких как физическая культура и иностранный язык);
- переводов между группами;
- нескольких оценок по одной дисциплине.
Многосеместровые дисциплины считаются по семестрам, например: Математический анализ (семестр 1), Математический анализ (семестр 2).

В рамках проекта:

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

Домашнее задание 4. Нормализация БД «Университет»

Дано отношение.

Атрибуты: StudentId, StudentName, GroupId, GroupName, CourseId, CourseName, LecturerId, LecturerName, ClubId, ClubName, ClubStudentHeadId, Mark.
Функциональные зависимости:
- StudentId → StudentName, GroupId, GroupName;
- GroupId → GroupName;
- GroupName → GroupId;
- CourseId → CourseName;
- LecturerId → LecturerName;
- StudentId, CourseId → Mark;
- GroupId, CourseId → LecturerId;
- ClubId → ClubName;
- ClubName → ClubId, ClubStudentHeadId.
Множественные зависимости (не все):
- GroupName ↠ StudentId;
- GroupName ↠ CourseId;
- ClubName ↠ StudentId.

Инкрементально приведите данное отношение в пятую нормальную форму.
Постройте соответствующую модель сущность-связь.
Постройте соответствующую физическую модель.
Реализуйте SQL-скрипты, создающие схему базы данных.
Создайте базу данных по спроектированной модели.
Заполните базу тестовыми данными.

Форма для сдачи ДЗ

В рамках проекта:

Приведите схему базы в пятую нормальную форму.
Если итоговая схема не будет в НФ-5, то обоснуйте принятое решение.
Запишите определения таблиц на языке SQL.
Запишите на языке SQL наполнение таблиц тестовым данными.

Домашнее задание 5. Реляционная алгебра

Структура базы данных «Университет»:

Groups(GroupId, GroupName)
Students(StudentId, StudentName, GroupId)
Courses(CourseId, CourseName)
Lecturers(LecturerId, LecturerName)
Plan(GroupId, CourseId, LecturerId)
Marks(StudentId, CourseId, Mark)
Clubs(ClubId, ClubName, ClubStudentHeadId)
ClubMembers(ClubId, StudentId)

Составьте выражения реляционной алгебры и соответствующие SQL-запросы, позволяющие получать

Информацию о студентах
1. С заданным идентификатором (StudentId, StudentName, GroupId по :StudentId).
2. С заданным ФИО (StudentId, StudentName, GroupId по :StudentName).
Полную информацию о студентах
1. С заданным идентификатором (StudentId, StudentName, GroupName по :StudentId).
2. С заданным ФИО (StudentId, StudentName, GroupName по :StudentName).
3. Из заданной группы (StudentId, StudentName, GroupName по :GroupName).
4. Заданного клуба (StudentId, StudentName, GroupName по :ClubName).
5. Клуба по руководителю (StudentId, StudentName, GroupName по :StudentName).
Информацию о студентах с заданной оценкой по дисциплине
1. С заданным идентификатором (StudentId, StudentName, GroupId по :Mark, :CourseId).
2. С заданным названием (StudentId, StudentName, GroupId по :Mark, :CourseName).
3. Которую у него вёл лектор заданный идентификатором (StudentId, StudentName, GroupId по :Mark, :LecturerId).
4. Которую у них вёл лектор, заданный ФИО (StudentId, StudentName, GroupId по :Mark, :LecturerName).
5. Которую вёл лектор, заданный идентификатором (StudentId, StudentName, GroupId по :Mark, :LecturerId).
6. Которую вёл лектор, заданный ФИО (StudentId, StudentName, GroupId по :Mark, :LecturerName).
Информацию о студентах не имеющих оценки по дисциплине
1. Среди всех студентов (StudentId, StudentName, GroupId по :CourseName).
2. Среди всех студентов (StudentId, StudentName, GroupId по :CourseName).
3. Среди участников клуба (StudentId, StudentName, GroupId по :CourseName, :ClubName).
4. Среди студентов, у которых есть эта дисциплина (StudentId, StudentName, GroupId по :CourseName).
Для каждого студента ФИО и названия дисциплин
1. Которые у него есть по плану (StudentName, CourseName).
2. Есть, но у него нет оценки (StudentName, CourseName).
3. Есть, но у него не 4 или 5 (StudentName, CourseName).
4. Вёл преподаватель (StudentName, CourseName по :LecturerName).
5. Участников клуба (StudentName, CourseName по :ClubName).
6. Соклубники (StudentName1, StudentName2, ClubName).
7. Руководители клубов (StudentName, CourseName).
Идентификаторы студентов по преподавателю
1. Имеющих хотя бы одну оценку у преподавателя (StudentId по :LecturerName).
2. Не имеющих ни одной оценки у преподавателя (StudentId по :LecturerName).
3. Имеющих оценки по всем дисциплинам преподавателя (StudentId по :LecturerName).
4. Имеющих оценки по всем дисциплинам преподавателя, которые он вёл у этого студента (StudentId по :LecturerName).
Группы и дисциплины, такие что все студенты группы имеют оценку по этой дисциплине
1. Идентификаторы (GroupId, CourseId).
2. Названия (GroupName, CourseName).

Составьте SQL-запросы, позволяющие получать

Суммарный балл
1. Одного студента (SumMark по :StudentId).
2. Каждого студента (StudentName, SumMark).
3. Каждой группы (GroupName, SumMark).
Средний балл
1. Одного студента (AvgMark по :StudentId).
2. Каждого студента (StudentName, AvgMark).
3. Каждой группы (GroupName, AvgMark).
4. Средний балл средних баллов студентов каждой группы (GroupName, AvgAvgMark).
Для каждого студента: число дисциплин, которые у него были, число сданных дисциплин и число несданных дисциплин (StudentId, Total, Passed, Failed).

Тестовый полигон

Технические особенности проверки.

Сдача — в PCMS. Если у вас нет аккаунта в PCMS, либо доступа к ДЗ, обратитесь к Николаю Викторовичу Ведерникову.
Проверяться и оцениваться будет последняя посланная версия.
Проверка разделена на 4 фазы:
1. пустые таблицы (синтаксис и набор столбцов);
2. таблицы с не более чем одной записью;
3. таблицы с простыми данными;
4. таблицы со сложными данными.
В случае проблем с синтаксисом или набором столбцов вы будете получать Presentation Error.
Реляционная алгебра проверяется одним тестом на фазу, движком из тестового полигона.
SQL проверяется тремя тестами на фазу — с разными СУБД. Первая СУБД — SQLite, как на тестовом полигоне.
Известные спецэффекты:
- SQLite поддерживает только left join. right и outer join делаются через него.
- Все вложенные запросы надо именовать, даже если вы не будете использовать это имя:
```
    select ... from ... (select ... ) SubQueryName ...
```
- Используйте данные из минимально возможного набора таблиц.

Домашнее задание 6. Реляционное исчисление

Составьте запросы в терминах языков Datalog и SQL для базы данных «Университет», позволяющие получать

Информацию о студентах
1. С заданным ФИО (StudentId, StudentName, GroupId по :StudentName).
2. Учащихся в заданной группе (StudentId, StudentName, GroupId по :GroupName).
3. Участниках клуба (StudentId, StudentName, GroupId по :ClubName).
4. C заданной оценкой по дисциплине, заданной идентификатором (StudentId, StudentName, GroupId по :Mark, :CourseId).
5. C заданной оценкой по дисциплине, заданной названием (StudentId, StudentName, GroupId по :Mark, :CourseName).
Полную информацию о студентах
1. Для всех студентов (StudentId, StudentName, GroupName).
2. Участниках клуба (StudentId, StudentName, GroupName по :ClubName).
3. Участниках клуба по руководителю (StudentId, StudentName, GroupName по :StudentName).
4. Студентов, не имеющих оценки по дисциплине, заданной идентификатором (StudentId, StudentName, GroupName по :CourseId).
5. Студентов, не имеющих оценки по дисциплине, заданной названием (StudentId, StudentName, GroupName по :CourseName).
6. Студентов, не имеющих оценки по дисциплине, у которых есть эта дисциплина (StudentId, StudentName, GroupName по :CourseId).
7. Студентов, не имеющих оценки по дисциплине, у которых есть эта дисциплина (StudentId, StudentName, GroupName по :CourseName).
Студенты и дисциплины, такие что у студента была дисциплина (по плану или есть оценка)
1. Идентификаторы (StudentId, CourseId).
2. Имя и название (StudentName, CourseName).
3. Имя и название по клубу (StudentName, CourseName по :ClubName).
4. Соклубников, изучавших один предмет (StudentName1, StudentName2, CourseName).
Студенты и дисциплины, такие что дисциплина есть в его плане, и у студента долг по этой дисциплине
1. Долгом считается отсутствие оценки (StudentName, CourseName).
2. Долгом считается оценка не выше 2 (StudentName, CourseName).
3. Долгом считается отсутствие оценки или оценка не выше 2 (StudentName, CourseName).
Идентификаторы студентов по преподавателю
1. Имеющих хотя бы одну оценку у преподавателя (StudentId по :LecturerName).
2. Не имеющих ни одной оценки у преподавателя (StudentId по :LecturerName).
3. Имеющих оценки по всем дисциплинам преподавателя (StudentId по :LecturerName).
4. Имеющих оценки по всем дисциплинам преподавателя, которые он вёл у этого студента (StudentId по :LecturerName).
Группы и дисциплины, такие что все студенты группы имеют оценку по предмету
1. Идентификаторы (GroupId, CourseId).
2. Названия (GroupName, CourseName).

Примечания

В Datalog итоговым считается последнее объявленное отношение.
Текущая реализация Datalog не поддерживает рекурсивные определения.
В SQL-запросах нельзя использовать * join.
SQL проверяется четырьмя тестами на фазу — с разными СУБД. Первая СУБД — SQLite, как на тестовом полигоне.

В рамках проекта:

Определите запросы (в том числе, агрегирующие), необходимые для работы проекта.
Реализуйте запросы на языке SQL.