Тема  5. Реляционная алгебра

Содержание

  1. Реляционная алгебра
    1. Предназначение
    2. Замкнутость
  2. Унарные операции
    1. Проекция
    2. Фильтрация
    3. Переименование
  3. Операции над множествами
    1. Объединение
    2. Пересечение
    3. Разность
  4. Соединения
    1. Полное соединение
    2. Естественное соединение
    3. Внешние соединения
    4. Полусоединения
    5. Условные соединения
  5. Деление
  6. Операции над данными
    1. Расширение
    2. Агрегирование
  7. Свойства реляционной алгебры
    1. Базис операций
    2. Ограничения реляционной алгебры
  8. Реляционная алгебра и SQL
    1. Простые операции
    2. Соединения

Слайды

HTML

Экзаменационные вопросы

  1. Реляционная алгебра: предназначение и свойства
  2. Реляционная алгебра: унарные и множественные операции
  3. Реляционная алгебра: соединения
  4. Реляционная алгебра: деление и операции над данными

Практические навыки

  1. Построение запросов в терминах реляционной алгебры
  2. Преобразование запросов
  3. Перевод запросов из терминов реляционной алгебры в термины SQL и обратно
  4. Построение запросов на SQL (без подзапросов)

Библиография

  1. Дейт К. Введение в системы баз данных (глава 7)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (главы 4 и 5)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 28 – Introduction to SQL-data operations)
  4. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 29 – Simple Search Conditions)
  5. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 30 – Searching with Joins)
  6. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 32 – Searching with Set Operators)
  7. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 33 – Searching with Groups)
  8. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 34 – Sorting Search Results)
  9. Select Syntax in MySQL Reference Manual

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

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

  • Faculties(FacultyId, FacultyName, DeanId)
  • Groups(GroupId, GroupName, GroupFacultyId)
  • Students(StudentId, StudentName, GroupId)
  • Courses(CourseId, CourseName)
  • Lecturers(LecturerId, LecturerName, LecturerFacultyId)
  • Plan(GroupId, CourseId, LecturerId)
  • Marks(StudentId, CourseId, Mark)

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

  1. Информацию о студентах

    1. С заданным идентификатором (StudentId, StudentName, GroupId по :StudentId).

    2. С заданным ФИО (StudentId, StudentName, GroupId по :StudentName).

  2. Полную информацию о студентах

    1. С заданным идентификатором (StudentId, StudentName, GroupName по :StudentId).

    2. С заданным ФИО (StudentId, StudentName, GroupName по :StudentName).

    3. Из заданной группы (StudentId, StudentName, GroupName по :GroupName).

    4. C заданного факультета (StudentId, StudentName, GroupName по :FacultyName).

    5. C факультета, заданного деканом (StudentId, StudentName, GroupName по :LecturerName).

  3. Информацию о студентах с заданной оценкой по дисциплине

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

  4. Информацию о студентах не имеющих оценки по дисциплине

    1. Среди всех студентов (StudentId, StudentName, GroupId по :CourseName).

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

    3. Среди студентов факультета (StudentId, StudentName, GroupId по :CourseName, :FacultyName).

    4. Среди студентов, у которых есть эта дисциплина (StudentId, StudentName, GroupId по :CourseName).

  5. Для каждого студента ФИО и названия дисциплин

    1. Которые у него есть по плану (StudentName, CourseName).

    2. Есть, но у него нет оценки (StudentName, CourseName).

    3. Есть, но у него не 4 или 5 (StudentName, CourseName).

    4. Вёл преподаватель (StudentName, CourseName по :LecturerName).

    5. Вёл преподаватель с :FacultyName (StudentName, CourseName по :FacultyName).

    6. Вёл преподаватель другого факультета (StudentName, CourseName).

    7. Вёл декан (StudentName, CourseName).

  6. Идентификаторы студентов по преподавателю

    1. Имеющих хотя бы одну оценку у преподавателя (StudentId по :LecturerName).

    2. Не имеющих ни одной оценки у преподавателя (StudentId по :LecturerName).

    3. Имеющих оценки по всем дисциплинам преподавателя (StudentId по :LecturerName).

    4. Имеющих оценки по всем дисциплинам преподавателя, которые он вёл у этого студента (StudentId по :LecturerName).

  7. Группы и дисциплины, такие что все студенты группы имеют оценку по этой дисциплине

    1. Идентификаторы (GroupId, CourseId).

    2. Названия (GroupName, CourseName).

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

  1. Суммарный балл

    1. Одного студента (SumMark по :StudentId).

    2. Каждого студента (StudentName, SumMark).

    3. Каждой группы (GroupName, SumMark).

  2. Средний балл

    1. Одного студента (AvgMark по :StudentId).

    2. Каждого студента (StudentName, AvgMark).

    3. Каждой группы (GroupName, AvgMark).

    4. Средний балл средних баллов студентов каждой группы (GroupName, AvgAvgMark).

  3. Для каждого студента: число дисциплин, которые у него были, число сданных дисциплин и число несданных дисциплин (StudentId, Total, Passed, Failed).

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

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

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