Тема  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. Реляционная алгебра

Реляционная алгебра

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

  • образовательные программы — EdProgs(EdProgId, EdProgName)
  • группы — Groups(GroupId, GroupName, EdProgId)
  • студенты — Students(StudentId, StudentName, GroupId)
  • дисциплины — Courses(CourseId, CourseName)
  • лекторы — Lecturers(LecturerId, LecturerName)
  • учебные планы — Plans(EdProgId, CourseId, Semester)
  • дисциплины, которые велись — Assignments(GroupId, CourseId, Semester, LecturerId)
  • оценки — Marks(StudentId, CourseId, Semester, Mark)

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

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

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

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

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

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

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

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

    4. Заданной образовательной программы (StudentId, StudentName, GroupName, EdProgName по :EdProgName).

    5. По дисциплине и семестру (StudentId, StudentName, GroupName, EdProgName по :CourseName, :Semestеr).

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

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

    2. С заданным названием (StudentId, StudentName, GroupId по :Mark, :CourseName, :Sеmestеr).

    3. Которую у него вёл лектор заданный идентификатором (StudentId, StudentName, GroupId по :Mark, :LеcturеrId, :Semеster).

    4. Которую у них вёл лектор, заданный ФИО в семестре (StudentId, StudentName, GroupId по :Mark, :LеcturerName, :Semester).

    5. Которую у них вёл лектор, заданный ФИО (StudentId, StudentName, GroupId по :LеcturerNamе).

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

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

    2. Образовательной программы (StudentId, StudentName, GroupId по :CourseName, :EdPrоgName).

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

  5. Для каждого студента ФИО, дисциплины и семестры

    1. Которые у него велись (StudentName, CourseName, Semester).

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

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

    4. Вёл лектор (StudentName, CourseName по :LecturerNamе).

    5. Образовательной программы (StudentName, CourseName, Semester по :EdProgName).

  6. Пары студентов

    1. Одногруппники (StudentName1, StudentName2, GroupName).

    2. Сопрограммники (StudentName1, StudentName2, EdProgName).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    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 и outer join делаются через него.
    • Все вложенные запросы надо именовать, даже если вы не будете использовать это имя: 
          select ... from ... (select ... ) SubQueryName ...
    • Используйте данные из минимально возможного набора таблиц.