Часть 1. Моделирование | Тем: 4 Тема 1. Введение | Содержание- Структура курса
- Развитие баз данных
- Требования к системам управления базами данных
- База данных на основе текстового файла
- База данных на основе файловой системы
- Иерархические базы данных
- Сетевые базы данных
- Реляционные базы данных
- Объектные базы данных
- Архитектура современных СУБД
- Обзор современных РСУБД
- Oracle (Сайт, Wikipedia)
- DB2 (Сайт, Wikipedia)
- Microsoft SQL Server (Сайт, Wikipedia)
- MySQL (Сайт, Wikipedia)
- PostgreSQL (Сайт, Wikipedia)
- Firebird (Сайт, Wikipedia)
- Derby (Сайт, Wikipedia)
- HSQLDB (Сайт, Wikipedia)
- Microsoft Access (Сайт, Wikipedia)
- Введение в SQL
- Ревизии стандарта SQL
- Пример создания базы данных
Экзаменационные вопросы- Развитие баз данных
- Архитектура современной СУБД
Библиография- Дейт К. Введение в системы баз данных
- Уидом Д., Ульман Д. Основы реляционных баз данных
- Гарсиа Молина Г., Уидом Д., Ульман Д. Системы баз данных полный курс
- Фаулер М. Архитектура корпоративных программных приложений
- Эмблер С. В., Садаладж П. Дж. Рефакторинг баз данных. Эволюционное проектирование
- Garcia-Molina H., Ullman J., Widom J. Database System Implementation
- Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts
- Gulutzan P., Pelzer T. SQL-99 complete, really
- Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts (Slides)
- Werstein P. Database Theory and Applications (Slides)
Домашнее задание 1. Установка и использование СУБДУстановка и использование СУБД-
Установите систему управления реляционными базами данных.
-
Узнайте, как в вашей СУБД исполнять SQL в интерактивном режиме.
-
Узнайте, как в вашей СУБД исполнять SQL в пакетном режиме.
-
Разберитесь, как в вашей СУБД осуществляется поддержка русского языка.
-
Создайте базу данных и наполните ее в соответствии с примерами из презентации.
Ожидаемая структура проекта -
Текстовая часть
- Описание предметной области с кратким описанием неочевидных сущностей и атрибутов.
- Предварительное разбиение на отношения (может отсутствовать).
- Для каждого отношения:
определение функциональных зависимостей,
нормализация до 5НФ
денормализация (при необходимости).
- Модель сущность-связь.
- Физическая модель (должна соответствовать ERM) с указанием типов для доменов.
-
Часть на SQL
ddl.sql – описание таблиц и индексов.data.sql – добавление тестовых данных.selects.sql – запросы на получение данных
и представления.
updates.sql – запросы на изменение данных,
хранимые процедуры и триггеры.
|
Тема 2. Моделирование баз данных | Содержание- Физическая модель базы данных
- Таблицы
- Ключи
- Ссылки
- Графическая нотация
- Модель сущность-связь
- Сущности
- Связи
- Ассоциации
- Слабые сущности
- Альтернативные нотации
- Преобразование модели сущность-связь в физическую модель
- Запись физической модели на языке DDL
- DDL как часть SQL
- Типы данных
- Описание ключей
- Описание ссылок
- Создание, изменение и удаление таблиц
Экзаменационные вопросы- Физическая модель базы данных
- Модель сущность-связь
- Преобразование модели сущность-связь в физическую модель
Практические навыки- Создание модели сущность-связь по словесному описанию
- Создание модели физической модели по словесному описанию
- Построение физической модели по модели сущность-связь
- Запись физической модели на DDL
Домашнее задание 2. Моделирование БД «Университет»Моделирование БД «Университет»
Спроектируйте базу данных «Университет»,
позволяющую хранить информацию о студентах,
группах, преподавателях, дисциплинах и оценках.
Поддержка дисциплин по выбору не требуется.
-
Составьте модель сущность-связь.
-
Преобразуйте модель сущность-связь в физическую модель.
-
Запишите физическую модель на языке SQL.
Запись должна включать объявления ограничений.
-
Создайте базу данных по спроектированной модели.
-
Запишите операторы SQL, заполняющие базу тестовыми
данными.
Форма для сдачи ДЗ В рамках проекта: - Выберите тему проекта.
- Сделайте предварительную схему для БД проекта на основе моделей.
- Форма для тем проектов
|
Тема 3. Реляционная модель и функциональные зависимости | Содержание- Реляционная модель данных
- Отношение
- Кортежи
- Представление null'ов
- Отличия таблиц и отношений
- Ключи
- Надключи
- Ключи
- Требования к ключам
- Естественные и суррогатные ключи
- Выбор ключей
- Функциональные зависимости
- Определение и примеры
- Замыкание множества функциональных зависимостей
- Эквивалентность множеств функциональных зависимостей
- Правила вывода функциональных зависимостей
- Замыкание множество атрибутов
- Неприводимые множества функциональных зависимостей
Экзаменационные вопросы- Реляционная модель данных. Ключи
- Функциональные зависимости: замыкание, эквивалентность и правила вывода
- Функциональные зависимости: замыкание атрибутов, неприводимые множества функциональных зависимостей, их построение
Практические навыки- Выделение надключей и ключей отношения
- Определение функциональных зависимостей в отношении
- Построение замыкания множества атрибутов
- Построение неприводимого множества функциональных зависимостей, эквивалентного данному
Домашнее задание 3. Функциональные зависимости в БД «Университет»Функциональные зависимости в БД «Университет»
Дано отношение с атрибутами
StudentId,
StudentName,
GroupId,
GroupName,
CourseId,
CourseName,
LecturerId,
LecturerName,
Mark.
-
Найдите функциональные зависимости в данном отношении.
-
Найдите все ключи данного отношения.
-
Найдите замыкание множеств атрибутов:
- GroupId, CourseId;
- StudentId, CourseId;
- StudentId, LecturerId.
-
Найдите неприводимое множество функциональных зависимостей
для данного отношения.
Форма для сдачи ДЗ В рамках проекта: -
Определите набор атрибутов, необходимых для проекта,
и определите отношения на них.
- Найдите функциональные зависимости полученных отношений.
- Найдите все ключи полученных отношений.
-
Найдите неприводимые множества функциональных зависимостей
для полученных отношений.
|
Тема 4. Нормализация баз данных | Содержание- Цели и средства нормализации
- Нормализация
- Проекции и соединения
- Декомпозиция отношений
- Первые нормальные формы
- Аномалии
- Первая нормальная форма
- Вторая нормальная форма
- Третья нормальная форма
- Нормальная форма Бойса-Кодда
- Многозначные зависmимости и четвертая нормальная форма
- Многозначные зависимости
- Четвертая нормальная форма
- Зависимости соединения и пятая нормальная форма
- Зависимости соединения
- Пятая нормальная форма
- Процесс нормализации и другие нормальные формы
- Иерархия нормальных форм
- Связи нормальных форм
- Другие нормальные формы
- Денормализация
Экзаменационные вопросы- Цели и средства нормализации
- Нормальные формы: первая и вторая
- Нормальные формы: третья и Бойса-Кодда
- Многозначные зависимости и четвертая нормальная форма
- Зависимости соединения и пятая нормальная форма
Практические навыки- Нормализация баз данных
- Проектирование высококачественных баз данных
Домашнее задание 4. Нормализация БД «Университет»Нормализация БД «Университет»
Дано отношение с атрибутами
StudentId,
StudentName,
GroupId,
GroupName,
CourseId,
CourseName,
LecturerId,
LecturerName,
Mark.
-
Инкрементально приведите данное отношение в
пятую нормальную форму.
-
Постройте соответствующую модель сущность-связь.
-
Постройте соответствующую физическую модель.
-
Реализуйте SQL-скрипты, создающие схему базы данных.
-
Создайте базу данных по спроектированной модели.
-
Заполните базу тестовыми данными.
Форма для сдачи ДЗ В рамках проекта: - Приведите схему базы в пятую нормальную форму.
- Если итоговая схема не будет в НФ-5, то обоснуйте принятое решение.
- Запишите определения таблиц на языке SQL.
- Запишите на языке SQL наполнение таблиц тестовым данными.
|
|
Часть 2. Запросы | Тем: 3 Тема 5. Реляционная алгебра | Содержание- Реляционная алгебра
- Предназначение
- Замкнутость
- Унарные операции
- Проекция
- Фильтрация
- Переименование
- Операции над множествами
- Объединение
- Пересечение
- Разность
- Соединения
- Полное соединение
- Естественное соединение
- Внешние соединения
- Полусоединения
- Условные соединения
- Деление
- Операции над данными
- Расширение
- Агрегирование
- Свойства реляционной алгебры
- Базис операций
- Ограничения реляционной алгебры
- Реляционная алгебра и SQL
- Простые операции
- Соединения
Экзаменационные вопросы- Реляционная алгебра: предназначение и свойства
- Реляционная алгебра: унарные и множественные операции
- Реляционная алгебра: соединения
- Реляционная алгебра: деление и операции над данными
Практические навыки- Построение запросов в терминах реляционной алгебры
- Преобразование запросов
- Перевод запросов из терминов реляционной алгебры в термины SQL и обратно
- Построение запросов на SQL (без подзапросов)
Библиография- Дейт К. Введение в системы баз данных (глава 7)
- Уидом Д., Ульман Д. Основы реляционных баз данных (главы 4 и 5)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 28 – Introduction to SQL-data operations)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 29 – Simple Search Conditions)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 30 – Searching with Joins)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 32 – Searching with Set Operators)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 33 – Searching with Groups)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 34 – Sorting Search Results)
- Select Syntax in MySQL Reference Manual
Домашнее задание 5. Реляционная алгебраРеляционная алгебра
Структура базы данных «Университет»:
- Students(StudentId, StudentName, GroupId)
- Groups(GroupId, GroupName)
- Courses(CourseId, CourseName)
- Lecturers(LecturerId, LecturerName)
- Plan(GroupId, CourseId, LecturerId)
- Marks(StudentId, CourseId, Mark)
Составьте выражения реляционной алгебры и соответствующие SQL-запросы,
позволяющие получать
-
Информацию о студентах
-
С заданным идентификатором
(StudentId, StudentName, GroupId по :StudentId).
-
С заданным ФИО
(StudentId, StudentName, GroupId по :StudentName).
-
Полную информацию о студентах
-
С заданным идентификатором
(StudentId, StudentName, GroupName по :StudentId).
-
С заданным ФИО
(StudentId, StudentName, GroupName по :StudentName).
-
Информацию о студентах с заданной оценкой по дисциплине
-
С заданным идентификатором
(StudentId, StudentName, GroupId по :Mark, :CourseId).
-
С заданным названием
(StudentId, StudentName, GroupId по :Mark, :CourseName).
-
Которую у него вёл лектор заданный идентификатором
(StudentId, StudentName, GroupId по :Mark, :LecturerId).
-
Которую у него вёл лектор, заданный ФИО
(StudentId, StudentName, GroupId по :Mark, :LecturerName).
-
Которую вёл лектор, заданный идентификатором
(StudentId, StudentName, GroupId по :Mark, :LecturerId).
-
Которую вёл лектор, заданный ФИО
(StudentId, StudentName, GroupId по :Mark, :LecturerName).
-
Информацию о студентах не имеющих оценки по дисциплине
-
Среди всех студентов
(StudentId, StudentName, GroupId по :CourseName).
-
Среди студентов, у которых есть эта дисциплина
(StudentId, StudentName, GroupId по :CourseName).
-
Для каждого студента ФИО и названия дисциплин
-
Которые у него есть по плану
(StudentName, CourseName).
-
Есть, но у него нет оценки
(StudentName, CourseName).
-
Есть, но у него не 4 или 5
(StudentName, CourseName).
-
Идентификаторы студентов по преподавателю
-
Имеющих хотя бы одну оценку у преподавателя
(StudentId по :LecturerName).
-
Не имеющих ни одной оценки у преподавателя
(StudentId по :LecturerName).
-
Имеющих оценки по всем дисциплинам преподавателя
(StudentId по :LecturerName).
-
Имеющих оценки по всем дисциплинам преподавателя, которые он вёл у этого студента
(StudentId по :LecturerName).
-
Группы и дисциплины, такие что все студенты группы сдали эту дисциплину
-
Идентификаторы
(GroupId, CourseId).
-
Названия
(GroupName, CourseName).
Составьте SQL-запросы, позволяющие получать
-
Суммарный балл
-
Одного студента
(SumMark по :StudentId).
-
Каждого студента
(StudentName, SumMark).
-
Каждой группы
(GroupName, SumMark).
-
Средний балл
-
Одного студента
(AvgMark по :StudentId).
-
Каждого студента
(StudentName, AvgMark).
-
Каждой группы
(GroupName, AvgMark).
-
Средний балл средних баллов студентов каждой группы
(GroupName, AvgAvgMark).
-
Для каждого студента:
число дисциплин, которые у него были,
число сданных дисциплин и
число несданных дисциплин
(StudentId, Total, Passed, Failed).
Тестовый полигон
Технические особенности проверки.
- Сдача — в PCMS.
- Проверяться и оцениваться будет последняя посланная версия.
-
Проверка разделена на 4 фазы:
- пустые таблицы (синтаксис и набор столбцов);
- таблицы с не более чем одной записью;
- таблицы с простыми данными;
- таблицы со сложными данными.
-
В случае проблем с синтаксисом или набором столбцов вы будете получать
Presentaion Error.
-
Реляционная алгебра проверяется одним тестом на фазу,
движком из тестового полигона.
-
SQL проверяется тремя тестами на фазу — с разными СУБД.
Первая СУБД — SQLite,
как на тестовом полигоне.
-
Известные спецэффекты:
|
Тема 6. Реляционное исчисление | Содержание- Реляционное исчисление
- Типы реляционного исчисления
- Структура запроса
- Исчисление кортежей
- Реляционные переменные
- Условия
- Простые
- С кванторами
- Связь реляционной алгебры и реляционного исчисления
- Выражение алгебры через исчисление
- Выражение исчисления через алгебру
- Реляционная полнота
- Исчисление доменов
- Доменные переменные
- Условие принадлежности
- Язык Datalog
- Определение отношений
- Реляционная полнота
- Рекурсия
- Реляционное исчисление и SQL
- Структура запроса
- Подзапросы
- Существования
- Вхождения
- Условные
- Скалярные
- Рекурсия
Экзаменационные вопросы- Исчисление кортежей и его реляционная полнота
- Исчисление доменов и его реляционная полнота
- Datalog и рекурсия
Практические навыки- Построение запросов в терминах исчисления кортежей
- Построение запросов в терминах исчисления доменов
- Перевод запросов из терминов реляционного исчисления в SQL и обратно
- Построение запросов на SQL (с подзапросами)
Домашнее задание 6. Реляционное исчислениеРеляционное исчисление
Составьте запросы в терминах языков Datalog и SQL для базы данных «Университет»,
позволяющие получать:
-
Информацию о студентах
-
С заданным ФИО
(StudentId, StudentName, GroupId по :StudentName).
-
Учащихся в заданной группе
(StudentId, StudentName, GroupId по :GroupName).
-
C заданной оценкой по дисциплине, заданной идентификатором
(StudentId, StudentName, GroupId по :Mark, :CourseId).
-
C заданной оценкой по дисциплине, заданной названием
(StudentId, StudentName, GroupId по :Mark, :CourseName).
-
Полную информацию о студентах
-
Для всех студентов
(StudentId, StudentName, GroupName).
-
Студентов, не имеющих оценки по дисциплине, заданной идентификатором
(StudentId, StudentName, GroupName по :CourseId).
-
Студентов, не имеющих оценки по дисциплине, заданной названием
(StudentId, StudentName, GroupName по :CourseName).
-
Студентов, не имеющих оценки по дисциплине, у которых есть эта дисциплина
(StudentId, StudentName, GroupName по :CourseId).
-
Студентов, не имеющих оценки по дисциплине, у которых есть эта дисциплина
(StudentId, StudentName, GroupName по :CourseName).
-
Студенты и дисциплины, такие что у студента была дисциплина (по плану или есть оценка)
-
Идентификаторы
(StudentId, CourseId).
-
Имя и название
(StudentName, CourseName).
-
Студенты и дисциплины, такие что дисциплина есть в его плане и у студента долг по этой дисциплине
-
Долгом считается отсутствие оценки
(StudentName, CourseName).
-
Долгом считается оценка не выше 2
(StudentName, CourseName).
-
Долгом считается и отсутствие оценки и оценка не выше 2
(StudentName, CourseName).
-
Идентификаторы студентов по преподавателю
-
Имеющих хотя бы одну оценку у преподавателя
(StudentId по :LecturerName).
-
Не имеющих ни одной оценки у преподавателя
(StudentId по :LecturerName).
-
Имеющих оценки по всем дисциплинам преподавателя
(StudentId по :LecturerName).
-
Имеющих оценки по всем дисциплинам преподавателя, которые он вёл у этого студента
(StudentId по :LecturerName).
-
Группы и дисциплины, такие что все студенты группы сдали дисциплину
-
Идентификаторы
(GroupId, CourseId).
-
Названия
(GroupName, CourseName).
Примечания - В Datalog итоговым считается последнее объявленное отношение.
- Текущая реализация Datalog не поддерживает рекурсивные определения.
- В SQL-запросах нельзя использовать
* join .
В рамках проекта: -
Определите запросы (в том числе, агрегирующие),
необходимые для работы проекта.
- Реализуйте запросы на языке SQL.
|
Тема 7. Изменение данных | Содержание- Операторы
- Вставка
- Обновление
- Объединение
- Удаление
- Целостность данных
- Корректность и целостность
- Типы ограничений целостности
- Типов и атрибутов
- Отношений
- Баз данных
- Компенсирующие действия
- Триггеры
- Представления
- Объявление и применения
- Обновление представлений
- Материализованные представления
- Управление доступом
- Схемы управления доступом
- Пользователи и группы
- Data Control Language
- Представления и права
Экзаменационные вопросы- Целостность данных. Триггеры
- Представления и их обновление
- Управление доступом к данным
Практические навыки- Построение изменяющих запросов на SQL
- Задание ограничений целостности
- Работа с представлениями
- Управление доступом к данным
Библиография- Дейт К. Введение в системы баз данных (главы 9, 10, 17)
- Уидом Д., Ульман Д. Основы реляционных баз данных (глава 6)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 35 – Changing SQL-data)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 18 – Table and View)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 20 – SQL Constraint and Assertion)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 24 – SQL Trigger)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 15 – SQL Authorization)
Домашнее задание 7. Изменение данныхИзменение данных
Реализуйте указанные запросы, представления,
проверки и триггеры на языке SQL.
-
Напишите запросы, удаляющие студентов
-
Учащихся в группе, заданной идентификатором
(GroupId).
-
Учащихся в группе, заданной названием
(GroupName).
-
Без оценок.
-
Имеющих 3 и более оценки.
-
Имеющих 3 и менее оценки.
-
Студентов, c долгами (здесь и далее — по отсутствию оценки).
-
Студентов, имеющих 2 и более долга.
-
Студентов, имеющих не более 2 долгов.
-
Напишите запросы, обновляющие данные студентов
-
Изменение имени студента
(StudentId, StudentName).
-
Перевод студента из группы в группу по индентификаторам
(StudentId, GroupId, FromGroupId).
-
Перевод всех студентов из группы в группу по идентификаторам
(GroupId, FromGroupId).
-
Перевод студента из группы в группу по названиям
(GroupName, FromGroupName).
-
Перевод всех студентов из группы в группу,
только если целевая группа существует
(GroupName, FromGroupName).
-
Напишите запросы, подсчитывающие статистику по студентам
-
Число оценок студента
(столбец Students.Marks)
(StudentId).
-
Число оценок каждого студента
(столбец Students.Marks).
-
Пересчет числа оценок каждого студента
по данным из таблицы NewMarks
(столбец Students.Marks).
-
Число сданных дисциплин каждого студента
(столбец Students.Marks).
-
Число долгов студента
(столбец Students.Debts)
(StudentId).
-
Число долгов каждого студента
(столбец Students.Debts).
-
Число долгов каждого студента группы (столбец Students.Debts)
(GroupName).
-
Число оценок и долгов каждого студента
(столбцы Students.Marks, Students.Debts).
-
Напишите запросы, обновляющие оценки,
с учетом данных из таблицы NewMarks,
имеющей такую же структуру, как таблица Marks
-
Проставляющий новую оценку только если ранее оценки не было.
-
Проставляющий новую оценку только если ранее оценка была.
-
Проставляющий максимум из старой и новой оценки
только если ранее оценка была.
-
Проставляющий максимум из старой и новой оценки
(если ранее оценки не было, то новую оценку).
-
Работа с представлениями
-
Создайте представление StudentMarks в котором
для каждого студента указано число оценок
(StudentId, Marks).
-
Создайте представление AllMarks в котором
для каждого студента указано число оценок,
включая оценки из таблицы NewMarks
(StudentId, Marks).
-
Создайте представление Debts в котором для каждого
студента, имеющего долги указано их число
(StudentId, Debts).
-
Создайте представление StudentDebts в котором
для каждого студента указано число долгов
(StudentId, Debts).
Целостность данных.
Обратите внимание, что задания из этого раздела
надо посылать в PCMS, но они будут проверяться
только вручную после окончания сдачи.
То есть в PCMS вы получите + за любое решение.
В комментарии перед запросом укажите версию
использованной СУБД.
-
Добавьте проверку того, что у студентов есть оценки
только по дисциплинам из их плана (NoExtraMarks)
(StudentId, CourseId).
-
Добавьте проверку того, что все студенты каждой группы
имею оценку по одному и тому же набору дисциплин (SameMarks).
(StudentId).
-
Создайте триггер PreserveMarks,
не позволяющий уменьшить оценку студента по дисциплине.
При попытке такого изменения оценка изменяться не должна.
(StudentId).
-
Напишите запросы, удаляющие студентов:
-
Учащихся в группе :GroupId;
-
Учащихся в группе :GroupName;
-
Без оценок;
-
Имеющих 3 и более оценки;
-
Имеющих 3 и менее оценки;
-
Студентов, c долгами (здесь и далее — по отсутствию оценки);
-
Студентов, имеющих 2 и более долга;
-
Студентов, имеющих не более 2 долгов.
-
Напишите запросы, обновляющие данные студентов:
-
Изменение имени студента :StudentId
на :StudentName;
-
Перевод студента :StudentId
из группы :FromGroupId в группу :GroupId;
-
Перевод всех студентов из группы :FromGroupId
в группу :GroupId;
-
Перевод всех студентов из группы :FromGroupName
в группу :GroupName;
-
Перевод всех студентов из группы :FromGroupName
в группу :GroupName только если целевая группа существует;
-
Напишите запросы, подсчитывающие статистику по студентам:
-
Число оценок студента :StudentId
(столбец Marks);
-
Число оценок каждого студента
(столбец Marks);
-
Пересчет числа оценок каждого студента по данным из
таблицы NewMarks
(столбец Marks);
-
Число сданных дисциплин каждого студента
(столбец Marks);
-
Число долгов студента :StudentId
(столбец Debts);
-
Число долгов каждого студента
(столбец Debts);
-
Число долгов каждого студента группы :GroupName
(столбец Debts);
-
Число оценок и долгов каждого студента
(столбцы Marks, Debts);
-
Напишите запросы, обновляющие оценки, с учетом данных из таблицы
NewMarks.
-
Проставляющий новую оценку только если ранее оценки не было.
-
Проставляющий новую оценку только если ранее оценка была.
-
Проставляющий максимум из старой и новой оценки
только если ранее оценка была.
-
Проставляющий максимум из старой и новой оценки
(если ранее оценки не было, то новую оценку).
-
Работа с представлениями
-
Создайте представление StudentMarks
в котором для каждого студента указано число оценок
(столбцы StudentId, Marks);
-
Создайте представление AllMarks
в котором для каждого студента указано число оценок,
включая оценки из таблицы NewMarks
(столбцы StudentId, Marks);
-
Создайте представление Debts в котором для каждого
студента, имеющего долги указано их число
(столбцы StudentId, Debts);
-
Создайте представление StudentDebts
в котором для каждого студента указано число долгов
(столбцы StudentId, Debts);
Целостность данных.
Обратите внимание, что задания из этого раздела надо посылать в PCMS,
но они будут проверяться только вручную после окончания сдачи.
То есть в PCMS вы получите + за любое решение.
В комментарии перед запросом укажите версию использованной СУБД.
-
Добавьте проверку того,
что у студентов есть оценки только по дисциплинам из их плана
(NoExtraMarks).
-
Добавьте проверку того, что все студенты каждой группы
имею оценку по одному и тому же набору дисциплин
(SameMarks).
-
Создайте триггер PreserveMarks,
не позволяющий уменьшить оценку студента по дисциплине.
При попытке такого изменения оценка изменяться не должна.
В рамках проекта: -
Определите модифицирующие запросы, необходимые для работоспособности
проекта.
- Запишите эти запросы на языке SQL.
|
|
Часть 3. Реализация | Тем: 5 Тема 8. Хранение данных и индексирование | Содержание- Подсистема хранения данных
- Структура подсистемы хранения данных
- Память и жёсткие диски
- Страничная организация памяти
- Интерфейс с СУБД
- Организация данных
- Списки страниц
- Хранение записей
- Сжатие данных
- Индексация данных
- Индексы
- Применение индексов
- Структура индекса
- Кластеризованные индексы
- Хеш-индексы
- Простой хеш-индекс
- Расширяемый хеш-индекс
- Упорядоченные индексы
- B-деревья
- B+-деревья
- Другие типы индексов
- Битовые индексы
- Индексы на R-деревьях
- Применение индексов
- Селективность индекса
- Покрывающие индексы
- Выбор индексов
Экзаменационные вопросы- Подсистема хранения данных
- Индексация данных. Упорядоченные и хеш-индексы
- Индексация данных. Другие типы индексов. Применение индексов
Практические навыки- Выбор индексов в базе данных
- Запись индексов на языке SQL
Библиография- Дейт К. Введение в системы баз данных (приложение Г)
- Кнут Д. Искусство программирования. Том 3. Сортировка и поиск
- Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts
Домашнее задание 8. ИндексированиеИндексирование-
Определите, какие индексы требуется добавить
к таблицам базы данных Университет» на основе
запросов из ДЗ-5, 6 и 7.
-
Пусть частым запросом является определение
среднего балла студентов группы по дисциплине.
Как будет выглядеть запрос и какие индексы
могут помочь при его исполнении?
-
Придумайте три запроса, требующих новых индексов
и запишите их.
Если в результате, некоторые из старых индексов
станут бесполезными, удалите их.
При выполнении задания считайте, что ФЗ соответствуют полученным в ДЗ-3 и 4.
Форма для сдачи ДЗ В рамках проекта: -
Определите индексы (и их типы),
необходимые для эффективного исполнения запросов.
- Запишите определения индексов на языке SQL.
|
Тема 9. Хранимые процедуры | Содержание- Хранимые процедуры
- Операторы
- Составной и присваивания
- Ветвления
- Циклы
- Обработка ошибок
- Курсоры
- Хранимые функции
- Управление доступом
Экзаменационные вопросы- Хранимые процедуры и функции. Сходства и различия
- Императивное подмножество SQL
- Data Control Language
Практические навыки- Реализация хранимых процедур и функций
- Реализация сложных триггеров
- Управление правами доступа
Домашнее задание 9. Хранимые процедурыХранимые процедуры
В базе данных Airline информация о рейсах
самолётов задана в виде таблиц
Flights(
FlightId integer,
FlightTime timestamp,
PlaneId integer,
-- Дополнительные столбцы, при необходимости
)
Seats(
PlaneId integer,
SeatNo varchar(4), -- 123A
-- Дополнительные столбцы, при необходимости
)
Реализуйте запросы к базе данных Airline
с применением представлений, хранимых процедур и функций.
При необходимости, вы можете создать дополнительные
таблицы, представления и хранимые процедуры.
FreeSeats(FlightId)
— список мест, доступных для продажи и для бронирования.
Reserve(UserId, Pass, FlightId, SeatNo)
— пытается забронировать место
на трое суток начиная с момента бронирования.
Возвращает истину, если удалось и
ложь — в противном случае.
ExtendReservation(UserId, Pass, FlightId, SeatNo)
— пытается продлить бронь места
на трое суток начиная с момента продления.
Возвращает истину, если удалось и
ложь — в противном случае.
BuyFree(FlightId, SeatNo)
— пытается купить свободное место.
Возвращает истину, если удалось и
ложь — в противном случае.
BuyReserved(UserId, Pass, FlightId, SeatNo)
— пытается выкупить забронированное место
(пользователи должны совпадать).
Возвращает истину, если удалось и
ложь — в противном случае.
FlightsStatistics(UserId, Pass)
— статистика по рейсам: возможность
бронирования и покупки, число
свободных, забронированных и проданных мест.
FlightStat(UserId, Pass, FlightId)
— статистика по рейсу: возможность
бронирования и покупки, число
свободных, забронированных и проданных мест.
CompressSeats(FlightId)
— оптимизирует занятость мест в самолете.
В результате оптимизации, в начале
самолета должны быть купленные места,
затем — забронированные, а в конце — свободные.
Примечание: клиенты, которые уже выкупили
билеты также должны быть пересажены.
Форма для сдачи ДЗ В рамках проекта: -
Определите храниемые процедуры и функции,
необходимые для работы проекта.
- Реализуйте хранимые процедуры (функций) на языке SQL.
|
Тема 10. Транзакции | Содержание- Транзакции
- Определение
- Свойства транзакций
- Восстановление
- Сбои и их типы
- Журнал транзакций
- Классический алгоритм восстановления
- Алгоритм ARIES
- Повторные сбои
- Отказ оборудования
- Параллельное исполнение
- Изоляция и конфликты
- Блокировки
- Упорядочиваемость
- Восстановление
- Гранулярность блокировок
- Транзакции в SQL
- Уровни изоляции транзакций
- Уровни изоляции
- Аномалии
- Синтаксис
- Транзакции
- Точки сохранения
Экзаменационные вопросы- Транзакции. Восстановление. Классический алгоритм
- Транзакции. Восстановление. Алгоритм ARIES
- Транзакции. Параллельное исполнение. Блокировки
- Транзакции. Параллельное исполнение. Уровни изоляции
Практические навыки- Выбор границ транзакции
- Объявление транзакций на языке SQL
Библиография- Дейт К. Введение в системы баз данных (главы 15 и 16)
- Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.2)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 36 – SQL Transactions)
- Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 37 – SQL Transactions Concurrency)
- 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
- Mohan C. Repeating History Beyond ARIES
- Bernstein P.A., Hadzilacos V., Goodman N. Concurrency Control and Recovery in Database Systems
Домашнее задание 10. ТранзакцииТранзакции
Спланируйте транзакции и выберите их уровни изоляции для
базы данных Airline.
-
Для каждой хранимой процедуры из предыдущего домашнего задания
выберите минимальный допустимый уровень изоляции транзакций
(с обоснованием).
-
Реализуйте сценарий работы:
-
Запрос списка свободных мест.
-
Отображение списка свободных мест пользователю.
-
Бронирование или покупка места, выбранного пользователем.
Форма для сдачи ДЗ В рамках проекта: -
Определите минимальный уровень изоляции транзакций,
необходимый для каждого запроса и хранимой процедуры.
|
Тема 11. Оптимизация запросов | Содержание- Обработка запросов
- Разбор запроса
- Перезапись запроса
- Планировщик и его структура
- Перезапись запросов
- Минимизация набора операций
- Унарные операции
- Алгебраические свойства операций
- Обработка условий
- Семантические оптимизации
- Методы исполнения
- Унарные операции
- Операции над множествами
- Соединения
- Выбор структуры запроса
- Правила планирования операций
- Применение динамического программирования
- Применение других подходов
- Оценка размера и распределения
- Статистические характеристики
- Оценка худшего случая
- Оценка операций
- Пример оптимизации запроса
- Оценка путей доступа
- Оценка соединения
Экзаменационные вопросы- Этапы обработки запроса. Перезапись запросов
- Оптимизация запросов. Выбор структуры исполнения запроса
- Оптимизация запросов. Выбор методов исполнения запроса
- Оптимизация запросов. Оценка размера и распределения
Практические навыки- Перезапись плана исполнения запроса
- Оптимизация плана исполнения запроса
- Оценка времени исполнения плана запроса
|
Тема 12. Распределенные базы данных | Содержание- Секционирование
- Цели секционирования
- Типы секционирования
- Методы секционирования
- Управление секциями
- Репликация
- Цели репликации
- Реализация репликации
- Применения репликации
- Распределенных базы данных
- Цели распределения
- Распределенные транзакции
- Проблемы распределенных баз данных
Экзаменационные вопросы- Секционирование
- Репликация
- Распределенные транзакции
- Распределенные базы данных. Цели и проблемы
Практические навыки- Секционирование данных
- Организация репликации данных
- Организация распределенных баз данных
Домашнее задание 11. Онлайн-активностиОнлайн-активности
Виды активностей:
- разбор домашнего задания (очная);
- разбор новой темы (очная);
- разметка видео;
- написание wiki-конспекта.
Можно участвовать только в одной активности.
Задания распределяются в порядке записи.
Форма для записи.
Разметка видео
- Размечаются оба видео за неделю.
-
Лекция:
- должны быть обозначены (под)разделы, выделенные в презентации;
- (под)разделы длиннее 6 минут должны быть разбиты на логические фрагменты.
-
Разбор новой темы:
- должно быть обозначено каждое задание;
- (под)задания длиннее 6 минут должны быть разбиты на логические фрагменты.
-
Разбор домашнего задания:
- пункты задания короче 2 минут можно объединять в логические блоки;
- пункты задания длиннее 6 минут должны быть разбиты на логические фрагменты.
Конспекты
- Конспект должен быть разбит на страницы в соответствии с разделами презентации.
- На странице должны быть выделены подразделы, соответствующие презентации (если такие есть).
- Можно и нужно использовать картинки и примеры из презентаций.
- Вместо скриншотов лучше брать оригинальные svg-файлы.
|
|
Часть 4. Применение | Тем: 4 Тема 13. Неполные данные и null | Содержание- Что означает null?
- Операции с null
- Null и SQL
Экзаменационные вопросы- Трактовки null и операции с ним
- Операции с null в SQL
Практические навыки- Представление неполных данных
|
Тема 14. Object-relational Mapping | Содержание- Ключи и ссылки
- Генерация ключей
- Ссылки и их типы
- Наследование
- «Широкая» таблицы
- Таблицы конкретных классов
- Таблицы классов
- Динамичные структуры и модель сущность-атрибут-значение
Экзаменационные вопросы- ORM. Ключи и ссылки
- ORM. Наследование
- ORM. Модель сущность-атрибут-значение
Практические навыки- Применение различных стратегий генерации ключей
- Управление ссылками
- Преставление наследования в БД
- Представление динамичных структур в БД
Библиография- Дейт К. Введение в системы баз данных (глава 26)
- Фаулер М. Архитектура корпоративных программных приложений (главы 3, 13)
|
Тема 15. Иерархические данные | Содержание- Операции
- Базовые представления
- Навигационные запросы и пути
- Предикаты и статистика
- Упорядочивание
- Обновление
- Модель близости
- Представление данных
- Базовые представления
- Обновление
- Характеристики
- Вложенные множества
- Представление данных
- Базовые представления и упорядочивание
- Обновление
- Характеристики
- Модель путей
- Представление данных
- Базовые представления и упорядочивание
- Обновление
- Характеристики
Экзаменационные вопросы- Иерархические данные. Модель близости
- Иерархические данные. Вложенные множества
- Иерархические данные. Модель путей
Практические навыки- Проектирование БД для хранения иерархических данных
- Управление иерархическими данными
Библиография- Celko J. Деревья в SQL
- Celko J. SQL for Smarties (глава 28)
|
Тема 16. Временны́е данные | Содержание- Время в SQL
- Временны́е данные
- Полутемпоральные базы данных
- Темпоральные базы данных
- Модель интервалов
- Модель событий
Экзаменационные вопросы- Временны́е данные. Полутемпоральные базы данных
- Временны́е данные. Модель интервалов
- Временны́е данные. Модель событий
Практические навыки- Умение работать с временны́ми данными
- Проектирование БД для хранения темпоральных данных
Библиография- Дейт К. Введение в системы баз данных (глава 23)
- Celko J. SQL for Smarties (глава 4)
- Gao D., Jensen C., Snodgrass R., Soo M. Join operations in temporal databases
|
|
|