План курса

Часть 1. Проектирование

Тем: 4

Тема 1. Введение

Содержание

  1. Структура курса
  2. Развитие баз данных
    1. Требования к системам управления базами данных
    2. База данных на основе текстового файла
    3. База данных на основе файловой системы
    4. Иерархические базы данных
    5. Сетевые базы данных
    6. Реляционные базы данных
    7. Объектные базы данных
  3. Архитектура современных СУБД
  4. Обзор современных РСУБД
    1. Oracle (Сайт, Wikipedia)
    2. DB2 (Сайт, Wikipedia)
    3. Microsoft SQL Server (Сайт, Wikipedia)
    4. MySQL (Сайт, Wikipedia)
    5. PostgreSQL (Сайт, Wikipedia)
    6. Firebird (Сайт, Wikipedia)
    7. Derby (Сайт, Wikipedia)
    8. HSQLDB (Сайт, Wikipedia)
    9. Microsoft Access (Сайт, Wikipedia)
  5. Введение в SQL
    1. Ревизии стандарта SQL
    2. Пример создания базы данных

Слайды

HTML

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

  1. Развитие баз данных
  2. Архитектура современной СУБД

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

  1. Дейт К. Введение в системы баз данных
  2. Уидом Д., Ульман Д. Основы реляционных баз данных
  3. Гарсиа Молина Г., Уидом Д., Ульман Д. Системы баз данных полный курс
  4. Фаулер М. Архитектура корпоративных программных приложений
  5. Эмблер С. В., Садаладж П. Дж. Рефакторинг баз данных. Эволюционное проектирование
  6. Garcia-Molina H., Ullman J., Widom J. Database System Implementation
  7. Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts
  8. Gulutzan P., Pelzer T. SQL-99 complete, really
  9. Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts (Slides)
  10. Werstein P. Database Theory and Applications (Slides)

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

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

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

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

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

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

Тема 2. Моделирование баз данных

Содержание

  1. Физическая модель базы данных
    1. Таблицы
    2. Ключи
    3. Ссылки
    4. Графическая нотация
  2. Модель сущность-связь
    1. Сущности
    2. Связи
    3. Ассоциации
    4. Слабые сущности
    5. Альтернативные нотации
  3. Преобразование модели сущность-связь в физическую модель
  4. Запись физической модели на языке DDL
    1. DDL как часть SQL
    2. Типы данных
    3. Описание ключей
    4. Описание ссылок
    5. Создание, изменение и удаление таблиц

Слайды

HTML

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

  1. Физическая модель базы данных
  2. Модель сущность-связь
  3. Преобразование модели сущность-связь в физическую модель

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

  1. Создание модели сущность-связь по словесному описанию
  2. Создание модели физической модели по словесному описанию
  3. Построение физической модели по модели сущность-связь
  4. Запись физической модели на DDL

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

  1. Дейт К. Введение в системы баз данных (глава 14)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (глава 2)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 18 – SQL Table and View)
  4. Chen P. The Entity-Relationship Model: Toward a Unified View of Data
  5. Chen P. Entity-Relationship Modeling: Historical Events, Future Trends, and Lessons
  6. Hartmann S. Reasoning about participation constraints and Chen's constraints
  7. MySQL Data Definition Statements
  8. PostgreSQL Data Definition
  9. Oracle Managing tables
  10. MSSQL Server Transact-SQL statements

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

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

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

Примечания

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

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

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

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

Тема 3. Реляционная модель и функциональные зависимости

Содержание

  1. Реляционная модель данных
    1. Отношение
    2. Кортежи
    3. Представление null'ов
    4. Отличия таблиц и отношений
  2. Ключи
    1. Надключи
    2. Ключи
    3. Требования к ключам
    4. Естественные и суррогатные ключи
    5. Выбор ключей
  3. Функциональные зависимости
    1. Определение и примеры
    2. Замыкание множества функциональных зависимостей
    3. Эквивалентность множеств функциональных зависимостей
    4. Правила вывода функциональных зависимостей
    5. Замыкание множество атрибутов
    6. Неприводимые множества функциональных зависимостей

Слайды

HTML

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

  1. Реляционная модель данных. Ключи
  2. Функциональные зависимости: замыкание, эквивалентность и правила вывода
  3. Функциональные зависимости: замыкание атрибутов, неприводимые множества функциональных зависимостей, их построение

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

  1. Выделение надключей и ключей отношения
  2. Определение функциональных зависимостей в отношении
  3. Построение замыкания множества атрибутов
  4. Построение неприводимого множества функциональных зависимостей, эквивалентного данному

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

  1. Дейт К. Введение в системы баз данных (главы 9 и 11)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (глава 3)
  3. Armstrong W.W. Dependency Structures of Data Base Relationships
  4. Fagin R. Functional Dependencies in a Relational Database and Propositional Logic
  5. Beeri C., Fagin R., Howard J. A complete axiomatization for functional and multivalued dependencies in database relations
  6. Maier D. Maier D. - Minimum covers in the relational database model

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

Дано отношение с атрибутами StudentId, StudentName, GroupId, GroupName, GroupFacultyId, CourseId, CourseName, LecturerId, LecturerName, LecturerFacultyId, Mark, FacultyId, FacultyName, FacultyDeanId.

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

Примечания

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

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

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

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

Тема 4. Нормализация баз данных

Содержание

  1. Цели и средства нормализации
    1. Нормализация
    2. Проекции и соединения
    3. Декомпозиция отношений
  2. Первые нормальные формы
    1. Аномалии
    2. Первая нормальная форма
    3. Вторая нормальная форма
    4. Третья нормальная форма
    5. Нормальная форма Бойса-Кодда
  3. Многозначные зависимости и четвертая нормальная форма
    1. Многозначные зависимости
    2. Четвертая нормальная форма
  4. Зависимости соединения и пятая нормальная форма
    1. Зависимости соединения
    2. Пятая нормальная форма
  5. Процесс нормализации и другие нормальные формы
    1. Иерархия нормальных форм
    2. Связи нормальных форм
    3. Другие нормальные формы
    4. Денормализация

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

  1. Цели и средства нормализации
  2. Нормальные формы: первая и вторая
  3. Нормальные формы: третья и Бойса-Кодда
  4. Многозначные зависимости и четвертая нормальная форма
  5. Зависимости соединения и пятая нормальная форма

Слайды

HTML

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

  1. Нормализация баз данных
  2. Проектирование высококачественных баз данных

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

  1. Дейт К. Введение в системы баз данных (главы 12 и 13)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (глава 3)
  3. Fagin R. Multivalued Dependencies and a New Normal Form for Relational Databases
  4. Beeri C., Fagin R., Howard J. A complete axiomatization for functional and multivalued dependencies in database relations
  5. Date C.J., Fagin R. Simple conditions for guaranteeing higher normal forms in relational databases
  6. Fagin R. Normal forms and relational database operators

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

Дано отношение с атрибутами StudentId, StudentName, GroupId, GroupName, GroupFacultyId, GroupFacultyName, GroupFacultyDeanId; CourseId, CourseName, LecturerId, LecturerName, LecturerFacultyId, LecturerFacultyName, LecturerFacultyDeanId, Mark. и функциональными зависимостями:

  • StudentId → StudentName, GroupId, GroupName;
  • GroupId → GroupName, GroupFacultyId;
  • GroupName → GroupId;
  • CourseId → CourseName;
  • LecturerId → LecturerName, LecturerFacultyId;
  • StudentId, CourseId → Mark;
  • GroupId, CourseId → LecturerId;
  • GroupFacultyId → GroupFacultyName, GroupFacultyDeanId;
  • GroupFacultyName → GroupFacultyId;
  • LecturerFacultyId → LecturerFacultyName, LecturerFacultyDeanId;
  • LecturerFacultyName → LecturerFacultyId.
  1. Инкрементально приведите данное отношение в пятую нормальную форму.
  2. Постройте соответствующую модель сущность-связь.
  3. Постройте соответствующую физическую модель.
  4. Реализуйте SQL-скрипты, создающие схему базы данных.
  5. Создайте базу данных по спроектированной модели.
  6. Заполните базу тестовыми данными.

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

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

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

Часть 2. Запросы

Тем: 3

Тема 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 ...
    • Используйте данные из минимально возможного набора таблиц.

Тема 6. Реляционное исчисление

Содержание

  1. Реляционное исчисление
    1. Типы реляционного исчисления
    2. Структура запроса
  2. Исчисление кортежей
    1. Реляционные переменные
    2. Условия
      1. Простые
      2. С кванторами
  3. Связь реляционной алгебры и реляционного исчисления
    1. Выражение алгебры через исчисление
    2. Выражение исчисления через алгебру
    3. Реляционная полнота
  4. Исчисление доменов
    1. Доменные переменные
    2. Условие принадлежности
  5. Язык Datalog
    1. Определение отношений
    2. Реляционная полнота
    3. Рекурсия
  6. Реляционное исчисление и SQL
    1. Структура запроса
    2. Подзапросы
      1. Существования
      2. Вхождения
      3. Условные
      4. Скалярные
    3. Рекурсия

Слайды

HTML

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

  1. Исчисление кортежей и его реляционная полнота
  2. Исчисление доменов и его реляционная полнота
  3. Datalog и рекурсия

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

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

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

  1. Дейт К. Введение в системы баз данных (глава 8)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (главы 4 и 5)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 31 – Searching with Subqueries)
  4. Codd E.F. A relational model of data for large shared data banks
  5. Codd E.F. Relational completeness of data base sublanguages

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

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

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

Примечания

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

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

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

Тема 7. Изменение данных

Содержание

  1. Операторы
    1. Вставка
    2. Обновление
    3. Объединение
    4. Удаление
  2. Целостность данных
    1. Корректность и целостность
    2. Типы ограничений целостности
      1. Типов и атрибутов
      2. Отношений
      3. Баз данных
    3. Компенсирующие действия
  3. Триггеры
  4. Представления
    1. Объявление и применения
    2. Обновление представлений
    3. Материализованные представления
  5. Управление доступом
    1. Схемы управления доступом
    2. Пользователи и группы
    3. Data Control Language
    4. Представления и права

Слайды

HTML

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

  1. Целостность данных. Триггеры
  2. Представления и их обновление
  3. Управление доступом к данным

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

  1. Построение изменяющих запросов на SQL
  2. Задание ограничений целостности
  3. Работа с представлениями
  4. Управление доступом к данным

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

  1. Дейт К. Введение в системы баз данных (главы 9, 10, 17)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (глава 6)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 35 – Changing SQL-data)
  4. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 18 – Table and View)
  5. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 20 – SQL Constraint and Assertion)
  6. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 24 – SQL Trigger)
  7. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 15 – SQL Authorization)

Домашнее задание 7. Изменение данных

Реализуйте указанные запросы, представления, проверки и триггеры на языке SQL.

  1. Напишите запросы, удаляющие студентов
    1. Учащихся в группе, заданной идентификатором (GroupId).
    2. Учащихся в группе, заданной названием (GroupName).
    3. Учащихся на факультете (FacultyName).
    4. Без оценок.
    5. Имеющих 3 и более оценки.
    6. Имеющих 3 и менее оценки.
  2. Напишите запросы, удаляющие должников (здесь и далее, долг определяется по отсутствию оценки)
    1. Студентов, c долгами.
    2. Студентов, имеющих 2 и более долга.
    3. Студентов, имеющих 2 и более долга, учащихся на факультете (FacultyName).
    4. Студентов, имеющих не более 3 долгов.
  3. Напишите запросы, обновляющие данные студентов
    1. Изменение имени студента (StudentId, StudentName).
    2. Перевод студента из группы в группу по индентификаторам (StudentId, GroupId, FromGroupId).
    3. Перевод всех студентов из группы в группу по идентификаторам (GroupId, FromGroupId).
    4. Изменение имени всех студентов группы (GroupName, StudentName).
    5. Перевод всех студентов из группы в группу по названиям (GroupName, FromGroupName).
    6. Перевод всех студентов из группы в группу, только если целевая группа существует (GroupName, FromGroupName).
  4. Напишите запросы, подсчитывающие статистику по оценкам
    1. Число оценок студента (столбец Students.Marks) (StudentId).
    2. Число оценок каждого студента (столбец Students.Marks).
    3. Число оценок каждого студента факультета (столбец Students.Marks) (FacultyName).
    4. Пересчет числа оценок каждого студента, с учётом новых оценок из таблицы NewMarks, структура которой такая же как у таблицы Marks (столбец Students.Marks).
  5. Напишите запросы, подсчитывающие статистику по студентам
    1. Число сданных дисциплин каждого студента (столбец Students.Marks).
    2. Число долгов студента (столбец Students.Debts) (StudentId).
    3. Число долгов каждого студента (столбец Students.Debts).
    4. Число долгов каждого студента группы (столбец Students.Debts) (GroupName).
    5. Число долгов каждого студента у деканов (столбец Students.Debts) (GroupName).
    6. Число оценок и долгов каждого студента (столбцы Students.Marks, Students.Debts).
  6. Напишите запросы, обновляющие оценки, с учетом данных из таблицы NewMarks, имеющей такую же структуру, как таблица Marks
    1. Проставляющий новую оценку только если ранее оценки не было.
    2. Проставляющий новую оценку только если ранее оценка была.
    3. Проставляющий максимум из старой и новой оценки только если ранее оценка была.
    4. Проставляющий максимум из старой и новой оценки (если ранее оценки не было, то новую оценку).
  7. Работа с представлениями
    1. Создайте представление StudentMarks в котором для каждого студента указано число оценок (StudentId, Marks).
    2. Создайте представление AllMarks в котором для каждого студента указано число оценок, включая оценки из таблицы NewMarks (StudentId, Marks).
    3. Создайте представление Debts в котором для каждого студента, имеющего долги указано их число (StudentId, Debts).
    4. Создайте представление StudentDebts в котором для каждого студента указано число долгов (StudentId, Debts).

  8. Целостность данных.

    Обратите внимание, что задания из этого раздела надо посылать в PCMS, но они будут проверяться только вручную после окончания сдачи. То есть в PCMS вы получите + за любое решение.

    В комментарии перед каждым запросом укажите название и версию использованной СУБД.

    1. Добавьте проверку того, что у студентов есть оценки только по дисциплинам из их плана (NoExtraMarks).
    2. Добавьте проверку того, что все студенты каждой группы имеют оценку по одному и тому же набору дисциплин (SameMarks).
    3. Создайте триггер PreserveMarks, не позволяющий уменьшить оценку студента по дисциплине. При попытке такого изменения оценка изменяться не должна.

Примечания

  1. Некоторые базы данных не понимают, что * join ... using (Column) должен оставлять один экземпляр Column и не требовать указывать для него таблицу.

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

  1. Определите модифицирующие запросы, необходимые для работоспособности проекта.
  2. Запишите эти запросы на языке SQL.
  3. Определите представления, необходимые для работоспособности проекта.
  4. Запишите эти представления на языке SQL.

Часть 3. Реализация

Тем: 5

Тема 8. Хранение данных и индексирование

Содержание

  1. Подсистема хранения данных
    1. Структура подсистемы хранения данных
      1. Память и жёсткие диски
      2. Страничная организация памяти
      3. Интерфейс с СУБД
    2. Организация данных
      1. Списки страниц
      2. Хранение записей
      3. Сжатие данных
  2. Индексация данных
    1. Индексы
      1. Применение индексов
      2. Структура индекса
      3. Кластеризованные индексы
    2. Хэш-индексы
      1. Простой хэш-индекс
      2. Расширяемый хэш-индекс
    3. Упорядоченные индексы
      1. B-деревья
      2. B+-деревья
    4. Другие типы индексов
      1. Битовые индексы
      2. Индексы на R-деревьях
    5. Применение индексов
      1. Селективность индекса
      2. Покрывающие индексы
      3. Выбор индексов

Слайды

HTML

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

  1. Подсистема хранения данных
  2. Индексация данных. Упорядоченные и хэш-индексы
  3. Индексация данных. Другие типы индексов. Применение индексов

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

  1. Выбор индексов в базе данных
  2. Запись индексов на языке SQL

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

  1. Дейт К. Введение в системы баз данных (приложение Г)
  2. Кнут Д. Искусство программирования. Том 3. Сортировка и поиск
  3. Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts

Домашнее задание 8. Индексирование

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

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

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

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

  1. Определите индексы и их типы, необходимые для эффективного исполнения запросов.
  2. Запишите определения индексов на языке SQL.

Тема 9. Хранимые процедуры

Содержание

  1. Хранимые процедуры
  2. Операторы
    1. Составной и присваивания
    2. Ветвления
    3. Циклы
    4. Обработка ошибок
  3. Курсоры
  4. Хранимые функции
  5. Управление доступом

Слайды

HTML

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

  1. Хранимые процедуры и функции. Сходства и различия
  2. Императивное подмножество SQL
  3. Data Control Language

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

  1. Реализация хранимых процедур и функций
  2. Реализация сложных триггеров
  3. Управление правами доступа

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

  1. Дейт К. Введение в системы баз данных (главы 4 и 7)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.4)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 25 – SQL-Invoked Routine)
  4. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 26 – PSM: Not Just Persistent Stored Modules)
  5.  MySQL 8.0: CREATE PROCEDURE and CREATE FUNCTION Statements

Домашнее задание 9. Хранимые процедуры

В базе данных Airline информация о рейсах самолётов задана в виде таблиц 

    Flights(
        FlightId integer,
        FlightTime timestamp,
        PlaneId integer,
        -- Дополнительные столбцы, при необходимости
    )
    Seats(
        PlaneId integer,
        SeatNo varchar(4), -- 123A
        -- Дополнительные столбцы, при необходимости
    )

Реализуйте запросы к базе данных Airline с применением представлений, хранимых процедур и функций. При необходимости, вы можете создать дополнительные таблицы, представления и хранимые процедуры.

Возможность бронирования должна автоматически отключаться за трое суток до начала рейса. Продажа мест должна автоматически отключаться за три часа до начала рейса. Также должна быть предусмотрена возможность отключения бронирования и продаж вручную.

  1. Администрирование.
    1. RegisterUser(UserId, Pass) — зарегистрировать нового пользователя. Возвращает истину, если удалось и ложь — в противном случае.
    2. ManageFlight(UserId, Pass, FlightId, SellAllowed, ReservationAllowed) — изменить настройки рейса. Примечание: автоматические настройки имеют более высокий приоритет.
  2. Покупка и бронирование.
    1. FreeSeats(FlightId) — список мест рейса, доступных для продажи и для бронирования.
    2. Reserve(UserId, Pass, FlightId, SeatNo) — бронирует место на сутки начиная с момента бронирования. Возвращает истину, если удалось и ложь — в противном случае.
    3. ExtendReservation(UserId, Pass, FlightId, SeatNo) — продлевает бронь места на сутки начиная с момента продления. Возвращает истину, если удалось и ложь — в противном случае.
    4. BuyFree(FlightId, SeatNo) — покупает свободное место. Возвращает истину, если удалось и ложь — в противном случае.
    5. BuyReserved(UserId, Pass, FlightId, SeatNo) — покупает забронированное место (пользователи должны совпадать). Возвращает истину, если удалось и ложь — в противном случае.
  3. Статистика.
    1. FlightsStatistics(UserId, Pass) — статистика по рейсам: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
    2. FlightStat(UserId, Pass, FlightId) — статистика по рейсу: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
  4. CompressSeats(UserId, Pass, FlightId) — оптимизирует занятость мест в самолете. В результате оптимизации, в начале самолета должны быть купленные места, затем — забронированные, а в конце — свободные. Примечание: клиенты, которые уже выкупили билеты, также должны быть пересажены.

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

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

  1. Определите хранимые процедуры и функции, необходимые для работы проекта.
  2. Реализуйте хранимые процедуры (функции) на языке SQL.

Тема 10. Транзакции

Содержание

  1. Транзакции
    1. Определение
    2. Свойства транзакций
  2. Восстановление
    1. Сбои и их типы
    2. Журнал транзакций
    3. Классический алгоритм восстановления
    4. Алгоритм ARIES
    5. Повторные сбои
    6. Отказ оборудования
  3. Параллельное исполнение
    1. Изоляция и конфликты
    2. Блокировки
    3. Упорядочиваемость
    4. Восстановление
    5. Гранулярность блокировок
  4. Транзакции в SQL
    1. Уровни изоляции транзакций
      1. Уровни изоляции
      2. Аномалии
    2. Синтаксис
      1. Транзакции
      2. Точки сохранения

Слайды

HTML

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

  1. Транзакции. Восстановление. Классический алгоритм
  2. Транзакции. Восстановление. Алгоритм ARIES
  3. Транзакции. Параллельное исполнение. Блокировки
  4. Транзакции. Параллельное исполнение. Уровни изоляции

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

  1. Выбор границ транзакции
  2. Объявление транзакций на языке SQL

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

  1. Дейт К. Введение в системы баз данных (главы 15 и 16)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.2)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 36 – SQL Transactions)
  4. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 37 – SQL Transactions Concurrency)
  5. Mohan C., Haderle D., Lindsay B., Pirahesh H., Schwarz P. ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging
  6. Mohan C. Repeating History Beyond ARIES
  7. Bernstein P.A., Hadzilacos V., Goodman N. Concurrency Control and Recovery in Database Systems

Домашнее задание 10. Транзакции

Спланируйте транзакции и выберите их уровни изоляции для базы данных Airline.

  1. Для каждой хранимой процедуры из предыдущего домашнего задания выберите минимальный допустимый уровень изоляции транзакций (с обоснованием).
  2. Реализуйте сценарий работы:
    1. Запрос списка свободных мест.
    2. Отображение списка свободных мест пользователю.
    3. Бронирование или покупка места, выбранного пользователем.

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

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

  1. Определите минимальный уровень изоляции транзакций, необходимый для каждого запроса и хранимой процедуры.

Тема 11. Оптимизация запросов

Содержание

  1. Обработка запросов
    1. Разбор запроса
    2. Перезапись запроса
    3. Планировщик и его структура
  2. Перезапись запросов
    1. Минимизация набора операций
    2. Унарные операции
    3. Алгебраические свойства операций
    4. Обработка условий
    5. Семантические оптимизации
  3. Методы исполнения
    1. Унарные операции
    2. Операции над множествами
    3. Соединения
  4. Выбор структуры запроса
    1. Правила планирования операций
    2. Применение динамического программирования
    3. Применение других подходов
  5. Оценка размера и распределения
    1. Статистические характеристики
    2. Оценка худшего случая
    3. Оценка операций
  6. Пример оптимизации запроса
    1. Оценка путей доступа
    2. Оценка соединения

Слайды

HTML

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

  1. Этапы обработки запроса. Перезапись запросов
  2. Оптимизация запросов. Выбор структуры исполнения запроса
  3. Оптимизация запросов. Выбор методов исполнения запроса
  4. Оптимизация запросов. Оценка размера и распределения

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

  1. Перезапись плана исполнения запроса
  2. Оптимизация плана исполнения запроса
  3. Оценка времени исполнения плана запроса

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

  1. Дейт К. Введение в системы баз данных (глава 18)
  2. Ioannidis Y.E. Query Optimization
  3. Mannino M.V., Chu P., Sager T. Statistical profile estimation in database systems
  4. PostgreSQL Documentation Chapter 51. Overview of PostgreSQL Internals

Тема 12. Распределенные базы данных

Содержание

  1. Секционирование
    1. Цели секционирования
    2. Типы секционирования
    3. Методы секционирования
    4. Управление секциями
  2. Репликация
    1. Цели репликации
    2. Реализация репликации
    3. Применения репликации
  3. Распределенных базы данных
    1. Цели распределения
    2. Распределенные транзакции
    3. Проблемы распределенных баз данных

Слайды

HTML

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

  1. Секционирование
  2. Репликация
  3. Распределенные транзакции
  4. Распределенные базы данных. Цели и проблемы

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

  1. Секционирование данных
  2. Организация репликации данных
  3. Организация распределенных баз данных

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

  1. Дейт К. Введение в системы баз данных (глава 21)
  2. Partitioning in MySQL Reference Manual
  3. Replication in MySQL Reference Manual

Часть 4. Применение

Тем: 4

Тема 13. Неполные данные и null

Содержание

  1. Что означает null?
  2. Операции с null
  3. Null и SQL

Слайды

HTML

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

  1. Трактовки null и операции с ним
  2. Операции с null в SQL

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

  1. Представление неполных данных

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

  1. Дейт К. Введение в системы баз данных (глава 19)
  2. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 13 – NULLs)
  3. Celko J. SQL and the Snare of Three-Valued Logic
  4. Wikibooks SQL: NULLs and the Three Valued Logic

Тема 14. Object-relational Mapping

Содержание

  1. Ключи и ссылки
    1. Генерация ключей
    2. Ссылки и их типы
  2. Наследование
    1. «Широкая» таблицы
    2. Таблицы конкретных классов
    3. Таблицы классов
  3. Динамичные структуры и модель сущность-атрибут-значение

Слайды

HTML

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

  1. ORM. Ключи и ссылки
  2. ORM. Наследование
  3. ORM. Модель сущность-атрибут-значение

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

  1. Применение различных стратегий генерации ключей
  2. Управление ссылками
  3. Преставление наследования в БД
  4. Представление динамичных структур в БД

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

  1. Дейт К. Введение в системы баз данных (глава 26)
  2. Фаулер М. Архитектура корпоративных программных приложений (главы 3, 13)

Тема 15. Иерархические данные

Содержание

  1. Операции
    1. Базовые представления
    2. Навигационные запросы и пути
    3. Предикаты и статистика
    4. Упорядочивание
    5. Обновление
  2. Модель близости
    1. Представление данных
    2. Базовые представления
    3. Обновление
    4. Характеристики
  3. Вложенные множества
    1. Представление данных
    2. Базовые представления и упорядочивание
    3. Обновление
    4. Характеристики
  4. Модель путей
    1. Представление данных
    2. Базовые представления и упорядочивание
    3. Обновление
    4. Характеристики

Слайды

HTML

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

  1. Иерархические данные. Модель близости
  2. Иерархические данные. Вложенные множества
  3. Иерархические данные. Модель путей

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

  1. Проектирование БД для хранения иерархических данных
  2. Управление иерархическими данными

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

  1. Celko J. Деревья в SQL
  2. Celko J. SQL for Smarties (глава 28)

Тема 16. Временны́е данные

Содержание

  1. Время в SQL
  2. Временны́е данные
  3. Полутемпоральные базы данных
  4. Темпоральные базы данных
    1. Модель интервалов
    2. Модель событий

Слайды

HTML

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

  1. Временны́е данные. Полутемпоральные базы данных
  2. Временны́е данные. Модель интервалов
  3. Временны́е данные. Модель событий

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

  1. Умение работать с временны́ми данными
  2. Проектирование БД для хранения темпоральных данных

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

  1. Дейт К. Введение в системы баз данных (глава 23)
  2. Celko J. SQL for Smarties (глава 4)
  3. Gao D., Jensen C., Snodgrass R., Soo M. Join operations in temporal databases