УПРАВЛЕНИЕ ДАННЫМИ

Лекция 1. Основные понятия баз данных и СУБД

План занятия

2. Основные понятия

2.1. Данные

Данные — это то, что уже записано: число, слово, дата. Сами по себе они часто мало о чём говорят.

Чтобы число стало понятным, нужен контекст: кто, что, когда, в каких единицах. Об этом — в разделе про пирамиду.

2.2. База данных

База данных (БД) — это большой упорядоченный набор связанных данных по одной теме. Не одна таблица на листочке, а система хранения.

Пример из вуза: в одной базе UniversityDB лежат студенты, вузы, предметы, оценки — и они связаны: у каждого студента есть вуз, у каждой оценки — студент и предмет.

Пример из жизни: каталог интернет-магазина — товары, цены, заказы, покупатели. Это тоже база, только тема другая.

Аналогия: база данных — как картотека в библиотеке: не одна книга, а весь фонд с правилами, где что лежит.

2.3. СУБД

СУБД — программа, которая хранит базу, принимает запросы, следит, чтобы данные не потерялись и не испортились.

Примеры СУБД в курсе:

• Microsoft SQL Server — отдельная программа-сервер на компьютере или в сети.
• SQLite — маленькая СУБД внутри приложения; часто вся база — один файл .db.

Аналогия: если база — картотека, то СУБД — библиотекарь и правила работы с картотекой: выдаёт книги, не даёт двум людям одновременно испортить одну карточку, следит за порядком.

2.4. Что часто путают

3. От данных к информации — пирамида DIKW

Мы уже выучили три слова: данные, база, СУБД. Теперь логичный вопрос: зачем всё это нужно, если можно записать оценки в тетрадь? Организации хотят не просто цифры, а понимание и решения — для этого данные проходят несколько шагов. Их часто рисуют пирамидой DIKW.

Разберём три разные ситуации — так проще запомнить, чем одной длинной таблицей.

Где здесь СУБД?

• Хранит данные (оценки, температуры, суммы).
• Помогает получить информацию — когда вы делаете запрос и видите связный ответ (фамилия + предмет + оценка).
• Может посчитать среднее, сумму, количество — это уже помощь в знаниях, но правило «что делать дальше» задаёт человек или программа по правилам организации.
• Решения (мудрость) СУБД не принимает сама.

4. Почему одного Excel мало

Если данные — это фундамент, то Excel часто даёт «кривой фундамент»: удобно на первый день, больно через месяц. Представьте: староста ведёт список группы, кафедра — оценки, методист — нагрузку. В каждом файле есть Иванов и телефон деканата.

• Дублирование. Телефон деканата записан в трёх файлах. Поменяли в одном — в двух других старый.
• Ошибки при обновлении. Иванов перевели на 2 курс — править нужно везде.
• Трудно связать. Чтобы узнать «оценки всех студентов ВГУ», приходится вручную сопоставлять два файла.
• Несколько человек пишут одновременно — файл легко повредить.
• Нет общих правил. В одном листе оценка «5», в другом «отлично».

База данных решает это так:

• Каждый факт хранится один раз (один Иванов, один телефон вуза).
• Таблицы связаны ключами (студент связан с вузом, оценка — со студентом и предметом).
• Запросы пишут на SQL — одном языке для всех отчётов.
• СУБД следит, чтобы два человека не испортили данные одновременно.

5. Три «этажа» одной базы

Это не пирамида DIKW из раздела 3. Здесь другая мысль: одни и те же данные в СУБД можно описать на трёх этажах — от «что видит человек» до «как всё записано на диске». Это трёхуровневая архитектура ANSI/SPARC — три взгляда на одну базу.

Зачем это знать? Чтобы не путать «таблицу в Object Explorer» и «файл на диске». Таблица STUDENT для вас — логическая. Если администратор добавит индекс для ускорения, столбцы таблицы не меняются — меняется только устройство на 3-м этаже.

6. Какие бывают СУБД и кто с ними работает

Кратко — «карта» на весь семестр, без деталей.

• Реляционные (наши основные): SQL Server, SQLite, PostgreSQL, Oracle — данные в таблицах.
• Документные, графовые и др. — упомянем позже; в этом курсе опора на таблицы.
• Клиент–сервер (SQL Server): программа на компьютере пользователя + сервер в сети.
• Встраиваемые (SQLite): база внутри приложения, часто один файл.

Кто работает с базой:

• Пользователь — смотрит отчёты, вводит данные.
• Разработчик — пишет запросы и программы.
• Администратор БД — следит, чтобы сервер работал, делает копии, выдаёт права.

7. Подготовка к лабораторной №1 — что сделаете в SQL Server Management Studio (SSMS)

7.1. Пошаговая инструкция

1. Запустить SQL Server Management Studio (SSMS).
2. Окно подключения: тип Database Engine, имя сервера — как скажет преподаватель, проверка Windows или SQL-логина.
3. Слева откройте Object Explorer, затем узел Databases.
4. Развернуть System Databases: master, model, msdb, tempdb — их не трогаем, только смотрим.
5. Посмотреть список пользовательских баз (если есть чужие от прошлых групп — не удалять без спроса).
6. Создать свою базу: правый клик Databases, New Database…, имя UniversityDB, OK.
   Или в окне запроса: CREATE DATABASE UniversityDB;
7. Открыть New Query, выбрать в списке базу UniversityDB, выполнить:

Проверьте для себя: вы в клиенте SSMS; запрос выполняется на сервере SQL Server; база UniversityDB создана или открыта для работы.

Шаг 1. Подключение: Connect to Server, тип Database Engine.

Шаг 2. После подключения — Object Explorer с базами сервера.

Шаг 3. Узел System Databases (master, model, msdb, tempdb).

Шаг 4. Создание UniversityDB (или команда CREATE DATABASE).

Рисунок 5. Основные окна SSMS на ЛР №1.

7.2. Слова, которые встретятся на ЛР №2 (кратко)

• Таблица — как лист Excel, но с жёсткими правилами.
• Строка — одна запись (один студент).
• Столбец — одно поле (фамилия, курс, стипендия).
• PRIMARY KEY — уникальный номер строки. Пример: STUDENT_ID.
• FOREIGN KEY — ссылка на другую таблицу. Пример: UNIVERSITY_ID у студента.

8. Вопросы для закрепления

1. Что такое данные? Приведите свой пример.
2. Чем база данных отличается от СУБД?
3. SQL Server Management Studio (SSMS) — это база, СУБД или клиент?
4. Чем информация отличается от данных? Пример из вуза или из жизни.
5. Назовите два недостатка хранения всего в Excel.
6. Зачем нужен PRIMARY KEY?

Лекция 2. Реляционная модель. Функциональные зависимости

План занятия

1. Зачем не один файл Excel

Успеваемость группы часто ведут в Excel: файл Университет.xlsx, лист Группа. В столбце A — фамилии (со 2-й строки), в первой строке начиная с B — названия предметов, в ячейках — оценки. У каждого студента одна строка, фамилия не повторяется — для журнала это удобно и логично.

База данных решает другие задачи: связать студентов с вузами, преподавателями, множеством предметов и оценок; отвечать на запросы без переделки листа; не дублировать справочную информацию. Поэтому сравнение «Excel vs SQL» — не «фамилия три раза на листе», а ограничения такой схемы при росте данных.

Решение в UniversityDB: разделить данные по смыслу и связать таблицы числовыми ключами:

• UNIVERSITY — справочник вузов (название и город хранятся один раз);
• STUDENT — кто учится (ссылка UNIVERSITY_ID на вуз, без повторения названия);
• EXAM_MARKS — оценки (отдельная строка на каждую пару «студент + предмет»).

2. Таблица в теории (и в SQL)

В учебниках таблицу называют отношением, строку — кортежем, столбец — атрибутом. В SSMS вы по-прежнему видите обычную таблицу — термины нужны для экзамена и курсовой, когда описываете модель.

2.1. Три правила реляционной таблицы

1. Атомарность. В ячейке одно значение. Нельзя писать «Москва, Санкт-Петербург» в поле «город» — для нескольких городов нужна отдельная таблица или отдельные строки.
2. Нет дубликатов строк. Две полностью одинаковые строки не имеют смысла; первичный ключ как раз не даёт «слить» двух людей в одну запись.
3. Порядок не важен. Строки можно вывести в любом порядке (ORDER BY — отдельная команда). Столбцы тоже можно переставить в SELECT, смысл данных не меняется.

2.2. Мини-пример таблицы STUDENT

Здесь одна строка — один студент. Номер STUDENT_ID не повторяется. По UNIVERSITY_ID видно, что Иванов и Петров из одного вуза (10), но это не ключ студента: у одного вуза много студентов.

3. Ключи

Ключ — это столбец или набор столбцов, по которым строку можно однозначно найти или связать с другой таблицей.

3.1. Первичный ключ (PRIMARY KEY)

У каждой строки свой уникальный идентификатор. В STUDENT это STUDENT_ID.

• Два студента не могут иметь один STUDENT_ID.
• Значение ключа не должно быть пустым (NULL).
• Фамилия ключом не подходит: в группе могут быть два Иванова.

3.2. Внешний ключ (FOREIGN KEY)

Столбец в одной таблице ссылается на первичный ключ другой. STUDENT.UNIVERSITY_ID ссылается на UNIVERSITY.UNIVERSITY_ID.

Смысл: «студент ссылается на существующий вуз». Если в справочнике нет вуза с номером 999, вставить студента с UNIVERSITY_ID = 999 нельзя — СУБД вернёт ошибку. Так сохраняется целостность данных.

4. Функциональная зависимость

Правило:STUDENT_IDопределяетSURNAME. Оно читается так: если в двух строках таблицы совпадает номер студента, совпадает и фамилия. Ещё проще: по номеру студента фамилия определяется однозначно — не «может быть Иванов, а может Петров».

Если правило задают два столбца сразу: «STUDENT_ID и SUBJECT_ID вместе определяют MARK» — у одного студента по одному предмету одна оценка.

Когда зависимости нет: по UNIVERSITY_ID нельзя однозначно узнать фамилию студента — в одном вузе учатся и Иванов, и Петров. Правила «UNIVERSITY_ID определяет SURNAME» для таблицы STUDENT нет.

4.1. Примеры зависимостей в UniversityDB

4.2. Типичная ошибка проектирования

Если добавить в STUDENT столбец UNIVERSITY_NAME «для удобства», возникает зависимость UNIVERSITY_ID определяет UNIVERSITY_NAME, но название вуза будет копироваться у каждого студента.

Переименовали вуз — нужно обновить все строки студентов. Правильно: имя вуза только в UNIVERSITY, у студента — только UNIVERSITY_ID.

Важно: функциональная зависимость — это правило предметной области («один человек — одна фамилия»), а не команда SQL. Сначала формулируем правила, потом создаём таблицы на ЛР.

5. Замыкание — как проверить, «хватает ли» столбец на роль ключа

Иногда ключ составной (из нескольких столбцов). Чтобы проверить, может ли один столбец (или набор столбцов) быть ключом всей таблицы, строят замыкание — список всех столбцов, которые из него однозначно следуют по вашим правилам зависимостей.

На экзамене задачу часто дают в «абстрактном» виде — столбцы A, B, C, D и зависимости A определяет B. Это то же самое, что STUDENT_ID определяет SURNAME, только без имён из базы. Ниже — такой учебный пример; в работе с UniversityDB мысленно подставляйте свои столбцы.

5.1. Алгоритм словами

1. Взять стартовый набор столбцов (например, только STUDENT_ID или, в абстрактной задаче, только {A}).
2. Для каждой зависимости «если известны левые столбцы, однозначно известны правые»: если все левые уже в наборе, добавить правые.
3. Повторять шаг 2, пока набор растёт.
4. Если в конце получились все столбцы таблицы — стартовый набор суперключ (подходит в качестве первичного ключа).

5.2. Разобранный пример (с буквами A, B, C, D)

Таблица с столбцами A, B, C, D (без привязки к предметной области — типичная учебная задача на замыкание). Зависимости:

• A определяет B
• B определяет C
• A определяет D

Шаг 1. Старт: {A}.

Шаг 2. Из правила «A определяет B»: добавляем B — получаем {A, B}.

Шаг 3. Из правила «B определяет C»: добавляем C — получаем {A, B, C}.

Шаг 4. Из правила «A определяет D»: добавляем D — получаем {A, B, C, D} — все столбцы таблицы.

Вывод: A — ключ (суперключ) этой таблицы.

Формальные правила вывода (аксиомы Армстронга) в программе экзамена упоминаются; для практики достаточно уметь пройти один пример, как выше, и понимать смысл «X определяет Y».

6. ЛР №2

На ЛР №2 вы создаёте таблицы UNIVERSITY, STUDENT, SUBJECT и другие: команды SQL берутся из приложения, раздел «Сборка базы по шагам». Затем выполняете первые SELECT.

Вопросы для самопроверки:

1. Чем таблица в SQL Server отличается от одного листа Excel с «всём подряд»?
2. Зачем STUDENT_ID, если есть фамилия?
3. Зачем UNIVERSITY_ID, если можно хранить название вуза текстом в строке студента?
4. Что произойдёт при INSERT студента с UNIVERSITY_ID, которого нет в UNIVERSITY?
5. Что означает запись STUDENT_ID определяет SURNAME?

Краткие ответы:

• Таблицы разделены по смыслу, связаны ключами, нет лишних повторов.
• Фамилия не уникальна; номер студента — уникальный идентификатор.
• Название вуза хранится один раз; при переименовании правится одна строка.
• СУБД отклонит INSERT — нарушение внешнего ключа.
• По номеру студента однозначно определяется фамилия.

Лекция 3. Нормализация. Аномалии обновления

План занятия

1. Одна «широкая» таблица — и три проблемы

Дальше держим в голове одну учебную таблицу ОЦЕНКИ_ПЛОХО. Каждая строка — оценка студента по предмету, но в таблицу «навешали» ещё данные о группе и старосте:

Фамилия «Петрова» (староста 31-й группы) повторяется в двух строках. Это не «мелочь Excel» — при работе через SQL Server те же правила: если факт записан много раз, его трудно менять согласованно.

1.1. Аномалия обновления

Группе 31 назначили нового старосту. Нужно найти все строки с GROUP_NO = 31 и везде заменить MONITOR. Забали одну строку — в базе две разные «истины» об одной группе.

1.2. Аномалия вставки

Завели новый предмет «Архитектура БД», но пока никто не сдал — строк с этим предметом нет. Если предмет «живёт» только внутри строк с оценками, справочник предметов в базу не добавить.

1.3. Аномалия удаления

Из группы 32 ушли все студенты (удалили последние строки с GROUP_NO = 32). Вместе с оценками исчезла и единственная запись «староста группы 32 — Козлов», хотя группа как учебная единица могла бы остаться в справочнике.

Общий вывод шага 1: в одной строке смешаны факты разного типа (оценка, студент, группа, староста). Разделим их — аномалии исчезнут. Как именно делить — шаги 2–4.

2. Сущность и связь — как назвать то, что вы уже делали при проектировании таблиц

Вы уже создавали отдельные таблицы UNIVERSITY, STUDENT, EXAM_MARKS. Каждая описывает свой тип объектов из предметной области (вуз, студент, факт сдачи). В проектировании БД такой тип называют сущностью.

Пример на UniversityDB: сущности «Вуз», «Студент», «Предмет»; связь студент–вуз — 1:N; связь студент–предмет через оценку — M:N и вынесена в EXAM_MARKS. Название вуза хранится один раз в UNIVERSITY — это уже следствие нормализации, к формальным правилам переходим дальше.

3. Нормализация и нормальные формы

3.1. Первая нормальная форма (1НФ)

Сначала таблица должна быть «атомарной»: в каждой ячейке одно значение, без списков «SQL, БД, физика» в одной клетке и без повторяющихся столбцов «Предмет1, Предмет2, Предмет3» (как в журнале Excel с новым предметом = новым столбцом).

3.2. Вторая нормальная форма (2НФ)

1НФ выполнена. Дальше смотрим на составной первичный ключ (два столбца и больше). Правило: каждый неключевой столбец должен зависеть от всего ключа, а не от одной его части.

Пример. В EXAM_MARKS ключ — пара (STUDENT_ID, SUBJ_ID). Оценка MARK имеет смысл только когда известны и студент, и предмет. Зависимость «только от STUDENT_ID» для оценки была бы ошибкой (один студент — одна оценка на все предметы).

3.3. Третья нормальная форма (3НФ)

2НФ выполнена. Запрет «цепочек» между неключевыми столбцами: если A → B и B → C, то C не должно висеть в той же таблице, что и A, когда B — не ключ.

Пример из проектирования таблиц. Таблица STUDENT(STUDENT_ID, SURNAME, UNIVERSITY_ID, UNIVERSITY_NAME) нарушает 3НФ: UNIVERSITY_ID → UNIVERSITY_NAME, но UNIVERSITY_ID не ключ всей таблицы студентов. Исправление — вынести вуз в UNIVERSITY, оставить только UNIVERSITY_ID.

3.4. НФБК — для справки

Нормальная форма Бойса — Кодда (НФБК) строже 3НФ: любая нетривиальная зависимость «X определяет Y» должна исходить из суперключа. На экзамене часто достаточно 3НФ; UniversityDB спроектирована в духе 3НФ/НФБК.

4. Декомпозиция — как из «плохой» таблицы получить несколько хороших

На скриншоте ищите:

1. Узел Databases → UniversityDB → Tables.
2. Отдельные таблицы UNIVERSITY, STUDENT, EXAM_MARKS — не одна «широкая».
3. Смысл: каждая таблица — одна сущность; связи — через PK/FK.

Рисунок 2. Эталон на сервере уже декомпозирован — так должна выглядеть схема после нормализации.

Цена и выигрыш: в запросах чаще нужен JOIN; зато переименование вуза или смена старосты — одна правка в справочнике, без аномалий обновления.

Связь с практикой: схема из ЛР №2 уже декомпозирована. На ЛР №3 вы спроектируете схему «Библиотека» с PK, FK, UNIQUE и CHECK и сможете аргументировать, какие нормальные формы выполняет каждая таблица.

5. Мини-задача (устно или в тетради)

Интернет-магазин свалили в одну таблицу: (order_id, customer_name, customer_city, product_name, price, qty).

1. Какие сущности выделите? (Подсказка: покупатель, товар, заказ, строка заказа.)
2. Какие аномалии возможны, если город покупателя повторяется в каждой строке заказа?
3. Какие таблицы получатся после декомпозиции?

6. Вопросы для самопроверки

1. Сформулируйте своими словами сущность и приведите пример из UniversityDB.
2. Чем аномалия обновления отличается от аномалии удаления на примере ОЦЕНКИ_ПЛОХО?
3. Почему EXAM_MARKS — отдельная таблица, а не столбцы «математика», «физика» в STUDENT?
4. Нарушает ли 3НФ таблица STUDENT(UNIVERSITY_ID, UNIVERSITY_NAME) без UNIVERSITY?

Лекция 4. Многопользовательские и распределённые БД

План занятия

1. Многопользовательская СУБД

Как разобрать тему. Идите по материалу на странице: рис. 1 → таблица §1.1 → задание §1.3 в тетради; затем рис. 2 и таблица §3.1. Так вы свяжете «один работал один» с моделью «много сеансов — одни таблицы на сервере».

Вы уже создали UniversityDB, таблицы и первые SELECT — каждый выполнял задания сам по себе. В этом разделе разберём по схемам и таблицам ниже, что меняется, когда к одному экземпляру СУБД на сервере одновременно обращаются много пользователей: у каждого свой сеанс (session) — свой запрос, свой результат, свои права, но общие таблицы на диске сервера.

Тот же принцип в вузе: деканат, бухгалтерия, электронная библиотека обращаются к одной серверной базе через разные программы (учётные системы, порталы), а не через «личный файл на ноутбуке». Промышленные СУБД (в том числе SQL Server) изначально рассчитаны на такой режим.

1.1. Аналогия и контраст с SQLite

Аналогия: банк — не один кассир с тетрадкой, а общая система учёта, к которой обращаются многие операторы; SQLite ближе к личной записной книжке на одном столе.

1.2. Клиент, сервер и где лежат данные

Клиент — программа на компьютере пользователя (или приложение вуза): формирует запрос на языке SQL и показывает результат. Сервер СУБД — служба на машине сервера: принимает запрос, выполняет его и возвращает ответ или сообщение об ошибке. Каждое подключение клиента — отдельный сеанс.

Важно: таблицы UniversityDB, с которыми вы уже работали при сборке схемы, физически хранятся на диске сервера, а не «внутри окна программы» на вашем ПК. У SQL Server это обычно пара файлов:

• .mdf — основной файл данных (таблицы, индексы);
• .ldf — журнал транзакций (протокол изменений до их окончательной фиксации в базе).

Закрыли клиент — база на сервере осталась; другие сеансы продолжают работу.

Рисунок 1а. Клиент: диалог подключения к серверу (Database Engine).

Рисунок 1б. Клиент: окно запроса — сюда пишут SQL; выполняет сервер, результат показывает клиент. Логическая цепочка «два сеанса — один сервер — .mdf/.ldf» — на рис. 1.

1.3. Мини-задача (в тетради, без сервера)

Нарисуйте схему: два «Клиента», один «SQL Server», стрелки запрос–ответ, справа — блок «.mdf + .ldf». Подпишите: у клиента 1 сеанс 52, у клиента 2 сеанс 61; оба читают таблицу STUDENT с теми же 19 строками, из эталонного набора (19 студентов).

Вопрос для ответа устно: если клиент 1 только читает, а клиент 2 меняет стипендию одной строки — почему без правил СУБД возможен конфликт? (Разбор — в §3 ниже.)

2. Распределённая база данных

Распределённая БД — логически одна база для приложения, физически данные на нескольких узлах (серверах, городах, дата-центрах). Идеал — прозрачность размещения: формулируется обычный запрос к таблице STUDENT, а система сама обращается к нужному узлу.

2.1. Три ситуации — не путать

2.2. Зачем распределять

• Близость к пользователю — филиал в другом городе читает «свой» узел быстрее.
• Разгрузка — тяжёлые отчёты с реплики (копии для чтения), оперативный учёт — на основном сервере.
• Резервирование — копия данных на другой площадке на случай сбоя узла.

2.3. Фрагментация

Горизонтальная — строки таблицы делят по правилу. Пример для UniversityDB: студенты с CITY = N'Воронеж' на узле 1, остальные — на узле 2. Для пользователя запрос SELECT COUNT(*) FROM STUDENT может прозрачно собрать сумму с обоих узлов.

Вертикальная — редкие или «тяжёлые» столбцы выносят на отдельный узел. Пример: в STUDENT оставить STUDENT_ID, SURNAME, KURS на основном сервере, а BIRTHDAY — на узле архива. На курсе вертикальную фрагментацию достаточно понимать на уровне идеи.

3. Проблема одновременного доступа

Когда два сеанса меняют одни и те же строки, без правил координации возможны сбои. СУБД решает это через транзакции (группа команд «всё или ничего»), блокировки и настраиваемые уровни изоляции — их свойства разберём по курсу отдельно; здесь — картина целиком и один наглядный пример.

3.1. Пример «два UPDATE одной стипендии»

Студенту STUDENT_ID = 1 стипендия 150. Сеанс A и сеанс B одновременно делают разные изменения:

Оба изменения «имели право» быть учтёнными (+50 и +30), но в базе осталось 180 — приращение A потерялось. Это потерянное обновление. Решение — согласованные транзакции и блокировки при записи.

3.2. Что уже защищает UniversityDB

• PRIMARY KEY — два сеанса не вставят одного и того же STUDENT_ID.
• FOREIGN KEY — нельзя ссылаться на несуществующий вуз (вы уже проверяли при создании ограничений).
• CHECK — нельзя записать оценку 6 или курс 9.

Но FK не мешает двум корректным UPDATE «перезаписать» друг друга — для этого нужны транзакции.

4. Мини-задачи для разбора (§4–5)

1. Клиент или сервер? «Программа на моём ПК, через которую пишу SQL» — клиент. «Служба SQL Server на кафедральном сервере» — сервер. «Файл UniversityDB.mdf на диске сервера» — хранилище данных.
2. Распределённость. У вуза сайт с расписанием в JSON и отдельная SQL-база с оценками без обмена — это две системы, не распределённая БД. Почему?
3. Сеансы. «Удалить базу соседа из своего окна» — возможно? Нет: у каждого свой сеанс и свои права; опасны административные команды вроде DROP DATABASE, если их выдали по ошибке.

Ответы: (2) нет единой схемы и транзакций между JSON и SQL; (3) сосед работает с тем же сервером, но не «внутри» вашего окна — права ограничивает администратор.

5. Частые ошибки в понимании

6. Вопросы для самопроверки

1. Где физически хранятся данные UniversityDB — на диске сервера или «внутри программы-клиента» на ПК?
2. Чем распределённая БД отличается от «Excel у кафедры + Access у деканата»?
3. Чем горизонтальная фрагментация отличается от вертикальной — своими словами на примере STUDENT.
4. Что такое сеанс (session) и почему у двух пользователей, подключённых к одному серверу, сеансы различаются?
5. Как называется аномалия из примера §3.1 и какие два средства СУБД упомянуты для борьбы с параллельными конфликтами?
6. Назовите три приёма изменить готовую схему без пересоздания таблицы (ALTER TABLE, индекс, разделитель пакетов GO).

Лекция 5. Транзакции. Свойства ACID

План занятия

1. Зачем нужна транзакция

Вы уже видели потерянное обновление: два сеанса читают одну стипендию и пишут разные суммы — одно изменение пропадает. Транзакция — способ сказать серверу: «эти команды — одно целое».

Транзакция — группа операций, которые выполняются как одно целое: либо все изменения сохраняются (COMMIT), либо ни одно (ROLLBACK).

1.1. Пример из UniversityDB

Перевод 1000 рублей стипендии от студента STUDENT_ID = 1 студенту STUDENT_ID = 3:

Если второй UPDATE упадёт (например, нарушится CHECK на отрицательную стипендию), первый тоже должен откатиться — иначе деньги «исчезнут» из системы.

2. Свойства ACID

2.1. Consistency — что гарантирует СУБД, а что нет

Гарантирует: ограничения, объявленные в схеме — STIPEND >= 0, оценка 2–5, существующий UNIVERSITY_ID.

Не гарантирует автоматически: «суммарная стипендия группы не больше N», «число отличников не меньше 50%» — такие правила программируют в процедурах или проверяют в приложении.

2.2. Durability и файл журнала

После успешного COMMIT SQL Server сначала записывает изменения в журнал транзакций (.ldf), затем подтверждает клиенту. При сбое питания восстановление идёт по журналу (подробнее — в разделах о резервном копировании и восстановлении).

3. Команды в SQL Server — разбор по шагам

На ЛР №6 вы будете выполнять опасные UPDATE и DELETE внутри транзакции с ROLLBACK, чтобы не испортить эталон. Сейчас — та же техника на простом примере.

3.1. Успешная транзакция с откатом для учебы

После ROLLBACK стипендии должны совпасть с тем, что было до BEGIN TRAN. Вкладка Results — два одинаковых набора строк; на Messages — «Rollback Tran».

Окно запроса: транзакция с ROLLBACK — безопасный способ «попробовать» UPDATE на учебном сервере.

Два одинаковых результата SELECT — признак успешного отката.

3.2. Точка сохранения SAVE TRAN

SAVE TRAN — промежуточная «закладка». ROLLBACK TRAN step1 откатывает до неё, но транзакция продолжается до COMMIT или полного ROLLBACK.

3.3. Ошибка CHECK внутри транзакции

На ЛР №16 разберёте TRY/CATCH подробнее. Здесь важно: при ошибке нужен ROLLBACK, иначе транзакция «висит» открытой.

4. Мини-задачи и границы ACID

1. Atomicity: в переводе стипендии между двумя студентами сколько UPDATE должно войти в одну транзакцию?
2. Consistency: можно ли закоммитить студента с UNIVERSITY_ID = 999? Почему?
3. Isolation: пока ваш BEGIN TRAN не завершён, видят ли соседи ваши незафиксированные строки? (Зависит от уровня изоляции — по умолчанию частично защищены.)

ACID не обещает: высокую скорость, отсутствие тупиков, защиту от удаления диска без резервной копии.

5. Частые ошибки

6. Вопросы для самопроверки

1. Что произойдёт со вторым UPDATE, если первый уже закоммичен отдельно и второй упал с ошибкой?
2. Какое свойство ACID гарантирует сохранность данных после сбоя питания?
3. Почему CHECK на STIPEND >= 0 — про Consistency, а «фонд группы ≤ N» — уже нет?
4. Когда на лабораторной нужен ROLLBACK вместо COMMIT?
5. Чем SAVE TRAN отличается от полного ROLLBACK?
6. Какая лабораторная закрепит BEGIN TRAN … ROLLBACK на практике?

Лекция 6. Аномалии параллельного доступа

План занятия

1. Четыре классические аномалии

Вы уже группировали команды в транзакции. Если две транзакции идут параллельно, а изоляция недостаточна, результат может не совпасть ни с одним «честным» последовательным порядком. Такие эффекты имеют имена — их нужно узнавать на слух.

1.1. Потерянное обновление (lost update)

Два сеанса читают одно значение, оба считают от него, оба пишут — одно изменение затирается.

Пример со складом: 40 учебников. T1 продаёт 10 → пишет 30. T2 продаёт 5, но считала от 40 → пишет 35. Продано 15, в базе 35 — продажа T1 потеряна.

Пример из UniversityDB: два сеанса одновременно меняют STIPEND одного студента — при одновременной работе нескольких сеансов.

1.2. Грязное чтение (dirty read)

T1 изменила строку, но ещё не сделала COMMIT. T2 прочитала «новое» значение. T1 откатилась — T2 опиралась на данные, которых «не было».

Пример: T1 в транзакции начислила стипендию +500 и напечатала ведомость для проверки; T2 по этой сумме выдала справку; T1 сделала ROLLBACK — справка ложная.

1.3. Неповторяемое чтение (non-repeatable read)

T1 дважды читает одну и ту же строку — между чтениями T2 изменила и зафиксировала её.

Пример: T1 дважды читает RATING вуза ВГУ (UNIVERSITY_ID = 10): сначала 450, потом 470 — между чтениями другой сеанс обновил строку и сделал COMMIT.

1.4. Фантомное чтение (phantom read)

T1 дважды выполняет один и тот же запрос с условием — второй раз набор строк другой: появились или исчезли строки, подходящие под условие.

Пример: T1: SELECT COUNT(*) FROM dbo.STUDENT WHERE KURS = 1 → 4. T2 вставила нового первокурсника и сделала COMMIT. T1 повторяет COUNT → 5. «Лишняя» строка — фантом.

2. Сводная таблица на UniversityDB

3. Расписание в текстовом виде

4. Как отличить похожие случаи

Вопрос для разбора: отчёт дважды посчитал средний балл по предмету: 4.1 и 3.9, потому что между расчётами исправили одну оценку. Это неповторяемое чтение (изменилась конкретная строка в EXAM_MARKS).

Если между двумя COUNT добавили новую ведомость (новую строку) — это фантом.

Если справку напечатали по стипендии до чужого COMMIT, а потом транзакция откатилась — грязное чтение.

5. Мини-задачи

1. Классифицируйте: «два кассира одновременно списали товар с одного остатка» — какая аномалия?
2. Деканат дважды запросил число студентов на 1 курсе за минуту — между запросами зачислили нового. Фантом или неповторяемое чтение?
3. Почему на ЛР №6 все UPDATE/DELETE делают с ROLLBACK — связь с грязным чтением для соседей?

Ответы: (1) потерянное обновление; (2) фантом; (3) незафиксированные изменения не должны остаться в общей базе — откат убирает «мусор» для других сеансов.

6. Частые ошибки в понимании

7. Вопросы для самопроверки

1. Нарисуйте расписание двух транзакций с потерянным обновлением.
2. Чем грязное чтение отличается от неповторяемого?
3. Приведите пример фантома на таблице STUDENT.
4. Какая аномалия опаснее для печати справки о стипендии?
5. Какую таблицу в этом разделе заполняют уровни изоляции?
6. Как на ЛР №6 избегают порчи эталона при UPDATE?

Лекция 7. Уровни изоляции транзакций

План занятия

1. Зачем ослаблять полную изоляцию

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

Уровень изоляции — явный договор: «я согласен на часть аномалий ради скорости». Вы выбираете, какие из четырёх аномалий допустимы в вашем сценарии.

1.1. Пример на UniversityDB

Деканат строит отчёт «средний балл по предметам» — ему не нужны незафиксированные оценки (грязное чтение), но допустимо, что между двумя SELECT кто-то добавит новую ведомость (фантом). Кассовая операция «списать стипендию и зачислить на другой счёт» — наоборот, нужна жёсткая атомарность и минимум параллельных изменений одних строк.

2. Таблица уровней изоляции SQL-92

Стандарт SQL-92 задаёт четыре уровня. В SQL Server по умолчанию — READ COMMITTED. Столбец «потерянное обновление» — тот же конфликт двух UPDATE одной строки).

* Упрощённо для курса. ** При обычных блокировках строк SQL Server на READ COMMITTED два одновременных UPDATE одной строки сериализуются — второй сеанс ждёт первый.

2.1. Сопоставление с четырьмя аномалиями

3. Блокировки S и X — упрощённо

СУБД реализует уровни через блокировки на строках, страницах или таблицах.

• S (shared) — «я читаю». Несколько сеансов могут одновременно держать S на одной строке.
• X (exclusive) — «я пишу». Только один сеанс; несовместима ни с другой X, ни с S на той же строке.

Если T1 держит X на строке STUDENT_ID = 1, T2 не сможет ни прочитать её «для изменения», ни обновить, пока T1 не сделает COMMIT или ROLLBACK.

3.1. Мини-пример: два SELECT и один UPDATE

На READ COMMITTED второй сеанс не увидит «грязную» стипендию до COMMIT первого — но может подождать, если первый ещё держит X.

4. Команда в SQL Server

Уровень задаётся для текущего сеанса до следующего изменения:

Окно New Query: команда SET TRANSACTION ISOLATION LEVEL перед BEGIN TRAN.

Результат агрегирующего SELECT — на следующей лабораторной (№7) вы считаете такие же средние без смены уровня изоляции.

4.1. Снимки (snapshot) в SQL Server

Начиная с SQL Server 2005, есть версионность строк: читатели могут видеть согласованный «снимок» на момент начала транзакции, не блокируя писателей так жёстко, как при SERIALIZABLE. Режим включают на уровне базы (ALLOW_SNAPSHOT_ISOLATION) и задают SET TRANSACTION ISOLATION LEVEL SNAPSHOT. Для длинных отчётов по UniversityDB это часто удобнее, чем максимальная блокировка.

4.2. SQLite (кратко)

В SQLite нет полного набора четырёх уровней. BEGIN DEFERRED / IMMEDIATE / EXCLUSIVE задают, когда сеанс получает право записи. Для курса основной стенд — SQL Server; SQLite упоминаем только для сравнения.

5. Как выбирать уровень на практике

6. Мини-задачи и частые ошибки

1. Какой минимальный уровень запрещает грязное чтение справки о стипендии до чужого COMMIT?
2. Два раза подряд SELECT COUNT(*) FROM dbo.STUDENT WHERE KURS = 1 дали 4 и 5. Какая аномалия и какой уровень её убирает?
3. Зачем не ставить SERIALIZABLE «везде по умолчанию»?

Ответы: (1) READ COMMITTED; (2) фантом, SERIALIZABLE; (3) лишние ожидания и риск тупиков — блокировки и тупики.

7. Вопросы для самопроверки

1. Какой уровень изоляции по умолчанию в SQL Server?
2. Какие аномалии допускает READ COMMITTED?
3. Чем блокировка X несовместима с блокировкой S на одной строке?
4. Когда отчёту достаточно snapshot, а не SERIALIZABLE?
5. Как уровень изоляции связан с таблицей четырёх аномалий?
6. Зачем на ЛР №6 все изменения делали с ROLLBACK, хотя уровень по умолчанию уже READ COMMITTED?

Лекция 8. Блокировки, тупики, репликация

План занятия

1. От уровня изоляции к блокировкам

Вы уже выбирали уровень изоляции по таблице аномалий. Реализует это СУБД через блокировки: S на чтение, X на запись. Чем строже уровень, тем дольше блокировки держатся и тем чаще сеансы ждут друг друга.

Идея: блокировка — «бронь» на строку или страницу. Пока T1 держит X на строке STUDENT_ID = 1, T2 не может её изменить — это защита от потерянного обновления и грязного чтения.

1.1. Гранулярность

Блокировать можно строку, страницу, всю таблицу. Без индекса фильтр WHERE CITY = N'Воронеж' иногда блокирует больше строк, чем нужно — отсюда рекомендация «нужные индексы» при проектировании отчётов по STUDENT.

2. Двухфазный протокол (2PL)

Двухфазный протокол блокировок — правило, которому следуют многие СУБД:

1. Фаза роста — транзакция только получает новые блокировки.
2. Фаза спада — только освобождает блокировки; новых не берёт.

Строгий вариант (Strict 2PL): все X-блокировки снимаются только при COMMIT или ROLLBACK. Так гарантируется, что никто не прочитает незафиксированную запись.

3. Тупик (deadlock)

Тупик — цикл ожидания: T1 держит ресурс A и ждёт B; T2 держит B и ждёт A. Без вмешательства обе транзакции зависнут навсегда.

3.1. Классический пример на UniversityDB

Два сеанса переводят стипендию между студентами, но в разном порядке блокируют строки:

Каждый сеанс держит «свою» строку и ждёт вторую — получается цикл. SQL Server строит граф ожидания, находит цикл и выбирает жертву.

3.2. Сообщение SQL Server

Код ошибки — 1205. Клиентское приложение может повторить транзакцию (обычно ограниченное число раз). На учебном сервере для демонстрации тупика нужны два окна SSMS — выполняйте только по указанию преподавателя.

Для демонстрации тупика открывают два окна New Query к одной базе — два независимых сеанса.

4. Профилактика тупиков

5. Репликация

Репликация — копирование данных (или изменений) на другой сервер. Зачем: отчёты не нагружают основной сервер, резервная копия «живых» данных, филиал в другом городе.

Реплика не заменяет транзакции и блокировки на основном сервере — это отдельный механизм доставки данных. Распределённая БД может хранить студентов по регионам, репликация — один из способов синхронизации узлов.

6. Мини-задачи

1. Две транзакции обновляют EXAM_MARKS, затем STUDENT — в разном порядке. Возможен тупик?
2. Почему короткая транзакция с ROLLBACK на ЛР №6 снижает риск конфликтов на общем сервере?
3. Чем транзакционная репликация отличается от снимковой по задержке данных?

Ответы: (1) да, если блокируют пересекающиеся ресурсы в разном порядке; (2) меньше времени держит X-блокировки; (3) транзакционная ближе к «онлайн», снимковая — с большим лагом между копиями.

7. Вопросы для самопроверки

1. Опишите тупик «T1: STUDENT → EXAM_MARKS, T2: наоборот».
2. Что означает ошибка 1205 и что должен сделать клиент?
3. Зачем обращаться к таблицам в одном порядке во всём коде?
4. Чем транзакционная репликация отличается от снимковой?
5. Как блокировки связаны с уровнями изоляции?
6. Зачем отчёты иногда читают с реплики, а не с основного сервера?

Лекция 9. Структурированные и неструктурированные данные

План занятия

1. Три типа данных

До сих пор курс опирался на структурированные данные — таблицы SQL с фиксированными столбцами. Реальная информационная система вуза смешивает и другие форматы.

1.1. Структурированные

Строки и столбцы с типами, ограничениями, связями FK. Весь эталон UniversityDB: STUDENT, EXAM_MARKS, UNIVERSITY. Запросы SELECT, GROUP BY (ЛР №7–8), подзапросы (ЛР №9–10) работают именно с такими данными.

1.2. Полуструктурированные

Есть именованные поля и вложенность, но схема гибче таблицы:

• JSON — ответ REST API расписания: {"pair": 3, "subject": "Базы данных", "room": "220"}.
• XML — обмен с госуслугами или банком; разбор в разделе про XML и на ЛР №17.
• Лог веб-сервера с полями timestamp, level, message, но набор полей может меняться.

1.3. Неструктурированные

Нет фиксированной таблицы полей: PDF заявления, фото студбилета, видеолекция. В SQL Server обычно хранят метаданные (имя файла, дата, автор, ссылка), а сам файл — в файловом или объектном хранилище (S3, SharePoint, сетевой диск).

2. Модели хранения

2.1. Почему не «всё в MongoDB»

Отчёт «средний балл по кафедрам с JOIN трёх таблиц» на нормализованной схеме проще и надёжнее в SQL. Документная модель хороша, когда структура записи часто меняется или глубоко вложена — например, черновик анкеты абитуриента с опциональными блоками.

Гибрид: основные учётные данные — SQL Server; кэш или лог — Redis; обмен — XML; прототип мобильного приложения — MongoDB (ЛР №19).

3. Файлы и метаданные в БД

Хранить большой PDF в столбце VARBINARY(MAX)можно, но на практике:

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

Типичная схема: таблица DocumentMeta(FileId, StudentId, FileName, StoredAt, StorageUrl) — структурированные метаданные; путь или URL — на неструктурированный файл снаружи.

3.1. Пример JSON рядом с SQL

На ЛР №17 вы загрузите настоящий XML в тип XML — это полуструктурированные данные внутри реляционной СУБД.

4. Big Data — обзорно

Когда данных слишком много для одного SQL Server на одном диске, говорят о Big Data. Три «V» (без формул на экзамене — только смысл):

Для учебной UniversityDB (19 студентов, 24 оценки) достаточно одного SQL Server. Big Data — контекст, зачем в индустрии появляются Hadoop, Spark, озёра данных.

5. Мини-задачи

1. Классифицируйте: строка в EXAM_MARKS, JSON расписания, скан диплома.
2. Зачем оценки хранить в таблице, а не в JSON-файле на диске?
3. Когда документная БД лучше новой таблицы «на каждый тип анкеты»?

Ответы: (1) структурированные, полуструктурированные, неструктурированные (+ метаданные в SQL); (2) целостность FK, транзакции, отчёты GROUP BY; (3) когда набор полей у записей сильно различается.

6. Вопросы для самопроверки

1. Приведите пример структурированных данных из UniversityDB.
2. Где в вузе встречаются полуструктурированные JSON или XML?
3. Почему скан PDF хранят в файловом хранилище, а не в VARBINARY «на всякий случай»?
4. Когда MongoDB уместнее, чем новая таблица в SQL Server?
5. Чем полуструктурированные данные отличаются от структурированных в нашей таблице «три типа»?
6. Как эта тема связана с ЛР №9–10, если там снова SQL по таблицам?

Лекция 10. Язык XML

План занятия

1. Зачем XML в курсе управления данными

Вы уже различали типы данных на структурированные таблицы и полуструктурированные форматы. XML — один из стандартов обмена иерархическими данными между организациями: банк, деканат, госуслуги могут требовать файл по XSD-схеме, а не «произвольный Excel».

UniversityDB остаётся реляционной; XML понадобится, когда нужно передать фрагмент данных (список студентов вуза) в систему партнёра или сохранить документ в столбце XML рядом с ключами SQL.

2. Базовый синтаксис

• Элемент — пара тегов <Name>…</Name>.
• Атрибут — id="22" в открывающем теге; не путать с дочерним элементом.
• Корень — ровно один корневой элемент на документ (University).
• Прolog — строка <?xml …?> с версией и кодировкой (UTF-8 для кириллицы).

3. Well-formed и valid

Парсер SQL Server при вставке в XML сначала проверяет well-formed. Valid — ответственность обмена: партнёр отклонит файл, не прошедший XSD.

3.1. Фрагмент XSD

maxOccurs="unbounded" — сколько угодно студентов; use="required" — атрибут id обязателен.

4. XPath — навигация по дереву

XPath — язык путей к узлам. Примеры для документа выше:

5. XML в SQL Server

Столбец типа XML хранит документ внутри таблицы. Основные методы T-SQL:

.value() — одно скалярное значение (как подзапрос с одной строкой). .query() — фрагмент XML (несколько узлов). На ЛР №17 вы создадите таблицу dbo.UnivXml и загрузите похожий документ.

Окно New Query: переменная типа XML и метод .value().

5.1. XML и JSON

6. Мини-задачи

1. Исправьте: <Student>Иванов<Stipend>150</Student> — что не так?
2. Каким XPath прочитать атрибут id у University?
3. Зачем хранить XML в типе XML, а не в NVARCHAR(MAX)?

Ответы: (1) не закрыт Stipend; (2) (/University/@id)[1]; (3) проверка well-formed, индексы, методы XPath.

7. Вопросы для самопроверки

1. Чем элемент XML отличается от атрибута на примере <Student id="101">?
2. Что проверяет well-formed парсер, а что — valid по XSD?
3. Зачем в SQL Server столбец типа XML, если есть NVARCHAR?
4. Почему в REST-API чаще JSON, а в банковском обмене — XML?
5. Как XPath связан с методом .value() на ЛР №17?

Лекция 11. Администрирование и мониторинг СУБД

План занятия

1. Зачем нужен администратор БД

На ЛР №1 вы подключались к SQL Server и создавали UniversityDB. База будет жить на сервере месяцами: её нужно копировать, защищать, наблюдать и чинить после сбоев.

Администратор баз данных (DBA) отвечает за:

• доступность сервера и резервное копирование;
• учётные записи и права (кто может читать STUDENT, кто — только VIEW);
• мониторинг нагрузки и «медленных» запросов;
• место на диске, обновления, миграции.

Разработчик пишет запросы; DBA следит, чтобы сервер выдержал нагрузку и данные не пропали. На маленькой учебной базе роли часто совмещают — но различать их полезно.

2. Экземпляр и файлы базы

Экземпляр (instance) — одна установленная служба SQL Server на машине. В Object Explorer вы видите экземпляр → базы данных.

Object Explorer → System Databases: master, model, msdb, tempdb. Пользовательские базы (в т.ч. UniversityDB) — в том же узле Databases.

У UniversityDB два основных файла: .mdf (данные) и .ldf (журнал транзакций). Журнал связан с транзакциями и блокировками; без него нельзя надёжно восстанавливать базу в модели FULL.

2.1. Модель восстановления

SIMPLE — журнал усечён автоматически; point-in-time restore ограничен. FULL — нужен для цепочки BACKUP LOG и восстановления «на минуту» (раздел о резервном копировании).

3. Мониторинг в SSMS

Activity Monitor (правый клик на экземпляре) показывает:

• активные процессы и «висящие» запросы;
• блокировки;
• использование CPU и I/O.

Для текстового отчёта — динамическое представление sys.dm_exec_requests:

wait_type — чего ждёт запрос: PAGEIOLATCH (диск), LCK_M_* (блокировка), NETWORKIO (сеть). Если отчёт по JOIN трёх таблиц «висит», здесь видна причина.

Activity Monitor открывают правым кликом по имени экземпляра в Object Explorer (тот же узел, что на рисунке в §2).

4. Плановое обслуживание

На учебном сервере не работайте под sa без необходимости и не запускайте CHECKDB в часы сдачи лабораторных без согласования.

5. Мини-задачи

1. Какая системная база хранит расписание заданий Agent?
2. Чем файл .ldf связан с транзакциями?
3. Почему DBA смотрит blocking_session_id вместе с wait_type?

Ответы: (1) msdb; (2) журнал фиксирует изменения до COMMIT; (3) блокировка — частая причина ожидания при параллельных UPDATE.

6. Вопросы для самопроверки

1. Назовите четыре системные базы SQL Server и их роль.
2. Что покажет wait_type = PAGEIOLATCH?
3. Зачем запускать DBCC CHECKDB по расписанию?
4. Чем модель FULL отличается от SIMPLE для резервного копирования?
5. Как администрирование связано с ЛР №23–24?

Лекция 12. Программирование в БД: процедуры, триггеры

План занятия

1. Представление VIEW

На ЛР №11 вы писали длинный JOIN «студент + вуз + оценки». Пользователю отчётов неудобно каждый раз повторять связи — им нужна «виртуальная таблица» с понятными столбцами.

VIEW — сохранённый SELECT с именем. Данные не копируются: при SELECT * FROM v_StudentUniv сервер выполняет запрос из определения VIEW.

Не путать с таблицей: в Object Explorer VIEW лежит в папке Views; INSERT в простой VIEW с JOIN обычно невозможен — помогает триггер INSTEAD OF (ЛР №22).

2. Хранимая процедура

Процедура — программа T-SQL на сервере с параметрами. Один вызов EXEC вместо копирования текста запроса в каждое приложение.

Плюсы: меньше сетевого трафика; единая логика; можно выдать GRANT EXECUTE без права SELECT на таблицу (ЛР №23).

3. Триггеры DML

Триггер — код, который SQL Server запускает при INSERT, UPDATE или DELETE.

Внутри триггера доступны псевдотаблицы inserted (новые значения) и deleted (старые). На ЛР №22 вы запишете изменения в EXAM_MARKS и запретите понижение оценки.

4. План выполнения и индексы

В SSMS: кнопка Include Actual Execution Plan (Ctrl+M). Смотрите операторы:

• Index Seek — точечный поиск по индексу (быстро при селективном WHERE).
• Index Scan — просмотр всего индекса/таблицы.

Индекс на STUDENT(UNIVERSITY_ID) ускорит JOIN с UNIVERSITY. Выражение в WHERE «ломает» индекс: WHERE YEAR(BIRTHDAY) = 2005 — лучше диапазон BIRTHDAY BETWEEN '2005-01-01' AND '2005-12-31'.

В SSMS включите Include Actual Execution Plan (Ctrl+M), выполните JOIN из §1 и откройте вкладку Execution plan под результатами — ищите операторы Index Seek по ключу FK или Index Scan при его отсутствии. Готового скриншота плана в материалах нет: на ЛР №20–21 сделайте свой.

5. Мини-задачи

1. Обновится ли VIEW, если изменить фамилию в STUDENT?
2. Зачем SET NOCOUNT ON в процедуре?
3. Чем триггер отличается от CHECK на столбце MARK?

Ответы: (1) да, VIEW показывает актуальные данные; (2) не слать клиенту «(N rows affected)» на каждый внутренний SELECT; (3) триггер может писать в другую таблицу и откатывать транзакцию.

6. Вопросы для самопроверки

1. Чем VIEW отличается от копии таблицы?
2. Зачем вызывать usp_StudentByKurs, если можно написать тот же SELECT?
3. Когда нужен INSTEAD OF, а не AFTER?
4. Почему функция в WHERE мешает Index Seek?
5. Как программирование в БД связано с администрированием?

Лекция 13. Резервное копирование

План занятия

1. Зачем резервные копии

На ЛР №6 вы делали UPDATE и DELETE. Одна ошибка в WHERE без транзакции может стереть половину EXAM_MARKS. Диск выходит из строя, ransomware шифрует .mdf — без копии восстановить нельзя.

Резервная копия — не «галочка для отчёта», а часть RPO/RTO: сколько данных можно потерять и как быстро поднять сервис.

2. RPO и RTO

3. Виды копий SQL Server

Цепочка FULL → DIFF → LOG позволяет восстановиться на конкретную минуту — только в модели восстановления FULL.

4. SIMPLE vs FULL

SIMPLE — журнал усекается автоматически; point-in-time restore ограничен. FULL — нужен для регулярного BACKUP LOG и STOPAT при восстановлении.

4.1. Правило 3-2-1

Три копии данных, на двух носителях, одна копия вне площадки (облако, другой офис).

5. Вопросы для самопроверки

1. Чем FULL backup отличается от DIFFERENTIAL?
2. Зачем BACKUP LOG в модели FULL?
3. Что означает RPO = 1 час для деканата?
4. Почему копию пробуют восстанавливать на тестовом сервере?
5. Как резервное копирование связано с журналом транзакций?

Лекция 14. Восстановление данных и защита

План занятия

1. Порядок восстановления

Копия бесполезна, если вы ни разу не делали RESTORE. Общий порядок:

1. Последняя полная копия — с NORECOVERY.
2. Последняя разностная (если есть) — с NORECOVERY.
3. Цепочка файлов журнала — последний шаг с RECOVERY.

NORECOVERY оставляет базу в режиме «ещё накатываем файлы». RECOVERY открывает базу для работы.

2. Восстановление на момент времени

Если в 10:15 выполнили ошибочный DELETE FROM EXAM_MARKS WHERE …, а цепочка лога сохранена — можно откатиться на секунду до ошибки.

2.1. Логическая ошибка

Если после ошибки уже наработали новые данные, не откатывайте всю боевую базу на вчера. Поднимите копию рядом (UniversityDB_restore), вытащите нужные строки, вставьте в основную базу — так делают на ЛР №24.

3. Защита доступа

Login — учётная запись на уровне сервера. User — сопоставление login внутри базы. Role — набор прав для группы пользователей.

Принцип минимальных привилегий: только то, что нужно для работы. В UniversityDB есть ФИО и даты рождения — персональные данные; в проекте нужна политика доступа и аудит (триггер на ЛР №22).

4. Вопросы для самопроверки

1. Зачем первый RESTORE с NORECOVERY?
2. Как STOPAT помогает после ошибочного DELETE?
3. Чем login отличается от user внутри базы?
4. Зачем REVOKE DELETE на EXAM_MARKS для роли «читатель»?
5. Почему GRANT EXECUTE на процедуру безопаснее SELECT на всю таблицу?

Лекция 15. Распределённые БД и двухфазная фиксация

План занятия

1. Логически единая БД на нескольких узлах

Студенты ВГУ в Воронеже, филиал в другом городе — данные могут лежать на разных серверах, но приложение видит одну базу. Важна прозрачность: разработчик пишет обычный SQL, система направляет запрос на нужный узел.

2. Архитектуры

1. Файл-сервер — клиент читает файл целиком (устарело, плохо масштабируется).
2. Клиент–сервер — клиент шлёт SQL, сервер возвращает строки (наш SSMS + UniversityDB).
3. Трёхуровневая — браузер → сервер приложений → СУБД.

3. Репликация

Реплика — способ масштабировать чтение и повышать доступность.

4. Двухфазная фиксация (2PC)

Одна бизнес-операция затрагивает два узла (списание и зачисление на разных серверах). Протокол:

1. Prepare: координатор спрашивает участников «готовы зафиксировать?»
2. Commit: если все «да» — COMMIT всем; иначе ROLLBACK всем.

Минус: при сбое координатора участники могут долго ждать. Для курса достаточно понимать идею согласованности vs доступности.

4.1. Фрагментация

Горизонтальная — строки по регионам (STUDENT Москвы на узле A). Вертикальная — редкие столбцы на отдельном узле. Цель — баланс нагрузки и близость к пользователю.

5. Вопросы для самопроверки

1. Опишите две фазы 2PC своими словами.
2. Когда merge-репликация уместнее транзакционной?
3. Чем горизонтальная фрагментация отличается от вертикальной?
4. Почему распределённость усложняет согласованность?
5. Как тема связана с транзакциями ACID?

Лекция 16. Хранилища данных и подготовка к экзамену

План занятия

1. OLTP и OLAP

Факт — измеримое событие (оценка, сумма). Измерение — контекст (время, предмет, вуз). Операции OLAP-куба: срез, детализация, свёртка.

2. Хранилище данных

DWH собирает данные из учётных систем (ETL). Схема «звезда»: центральная таблица фактов (FactExam), вокруг — справочники (DimStudent, DimSubject, DimDate). Витрины — готовые срезы для конкретного отдела.

3. Data Mining

• Классификация: предсказать «сдаст / не сдаст» по курсу и посещаемости.
• Кластеризация: сгруппировать вузы по профилю рейтинга без заранее заданных меток.
• Ассоциации: «кто сдал физику, чаще имеет высокий балл по матану» — гипотеза для проверки.

4. Итоги курса

Цепочка семестра: модель и нормализация → SQL и JOIN → транзакции и блокировки → XML, иерархии, MongoDB → VIEW, процедуры, триггеры → бэкап, права, аналитика.

UniversityDB — сквозной пример от CREATE TABLE до GRANT и RESTORE.

5. Экзамен

24 контрольных вопроса и билеты — на странице «Экзамен»; учебный план — на вкладке «Учебный план».

Типичные темы SQL: подзапросы, JOIN, GROUP BY, EXISTS, NULL, VIEW.

6. Вопросы для самопроверки

1. Чем OLTP-нагрузка UniversityDB отличается от OLAP-отчёта?
2. Что такое факт и измерение в схеме «звезда»?
3. Назовите три типа задач Data Mining из §3.
4. Какие три темы SQL чаще встречаются на экзамене?
5. Какой лабораторной работой завершается семестр?

Учебный план

Соответствие лекций и лабораторных

Краткая карта: какая лекция подводит к каким ЛР на UniversityDB (ЛР №19 — MongoDB).

Объём занятий

Курсовая работа

Что должно быть в любой курсовой (минимум)

1. Предметная область — краткое описание задачи «своими словами» (1–2 страницы): кто пользуется системой, какие данные хранятся, зачем нужна БД.
2. Проектирование — ER-диаграмма (сущности, связи 1:N и M:N), список функциональных зависимостей, обоснование нормализации (1НФ–3НФ) и выбор ключей.
3. Реализация в SQL Server — отдельная база Kursovaya_FIO (или согласованное имя); не менее 5 таблиц с PRIMARY KEY, FOREIGN KEY, минимум два ограничения CHECK или UNIQUE; осмысленное наполнение (не «пустые таблицы»).
4. Запросы и логика — не менее 8 SQL-запросов разного типа: SELECT с WHERE, JOIN, агрегаты с GROUP BY/HAVING, подзапрос или EXISTS; плюс один из элементов серверного программирования: VIEW, хранимая процедура или AFTER-триггер (аудит или бизнес-правило).
5. Надёжность данных — один сценарий с BEGIN TRAN … COMMIT/ROLLBACK (операция из нескольких шагов) или демонстрация резервного копирования своей базы (BACKUP / описание RESTORE).
6. Отчёт — скрипт kursovaya_FIO.sql (ФИО латиницей) + пояснительная записка со скриншотами SSMS (схема, примеры запросов, при наличии — план выполнения или права доступа).

Свою тему (не из списка) можно предложить преподавателю, если она укладывается в минимум выше и не дублирует UniversityDB «одной таблицей Excel».

Приложение. Учебная база UniversityDB

UniversityDB — единая учебная база курса: вузы, студенты, преподаватели, предметы, оценки.

Что такое SQL-скрипт и откуда он взялся

SQL-скрипт — это обычный текст с командами T-SQL: создание таблиц, вставка строк, запросы. В SSMS его набирают или вставляют в окно New Query и выполняют клавишей F5 (или кнопкой Execute). Сервер выполняет команды и сохраняет результат в файлах базы на диске — не в окне SSMS.

Эталонный скрипт UniversityDB собран для этого курса и лежит на этой странице — отдельного файла скачивать не нужно. Ниже он разбит на блоки с пояснениями. На ЛР №2 вы копируете блоки по порядку в SSMS и сохраняете свою копию в файл lab02_FIO.sql (ФИО латиницей).

1. Откройте SSMS, подключитесь к серверу, выберите базу UniversityDB.
2. New Query — вставьте блок 1 (или пропустите, если база уже создана на ЛР №1).
3. Блоки 3–5 — CREATE TABLE. Блок 6 — INSERT. Между частями с GO можно выполнять по очереди.
4. Таблицы ниже на странице — те же данные в виде, удобном для чтения; в SSMS нужен именно SQL из блоков.

Схема связей

UNIVERSITY 1—∞ STUDENT, UNIVERSITY 1—∞ LECTURER, UNIVERSITY 1—∞ ORG_UNIT.
SUBJECT 1—∞ EXAM_MARKS, STUDENT 1—∞ EXAM_MARKS.
LECTURER ∞—∞ SUBJECT через SUBJ_LECT.
ORG_UNIT.PARENT_ID ссылается на ORG_UNIT.UNIT_ID (иерархия).

Сборка базы по шагам (SQL Server)

Выполняйте блоки по порядку. Если база пустая после ЛР №1, блок 2 можно пропустить.

Данные в таблицах (для просмотра)

Те же строки, что в блоке 6, в удобном виде — можно сверить результат после INSERT.

На телефоне широкие таблицы прокручиваются пальцем влево и вправо.

UNIVERSITY

STUDENT (19 строк)

LECTURER

SUBJECT

EXAM_MARKS (24 строки)

Студент 19 (Медведев) специально без строк в EXAM_MARKS и с CITY NULL: на нём проверяют IS NULL, NOT EXISTS и LEFT JOIN … IS NULL.

SUBJ_LECT

ORG_UNIT (для ЛР №18)

Шпаргалка диалектов

Экзамен

Экзаменационные билеты

Скачать билеты (.docx)

Билеты к экзамену

На экзамене выдаётся один билет из списка ниже: два теоретических вопроса + одна SQL-задача по UniversityDB. Нажмите на номер билета, чтобы открыть.