Модуль 06: Моделирование данных и SQL — Практическое задание

Общая информация

Кейс: Система управления задачами (Task Manager) — проектирование и анализ данных.

Цель задания: Применить навыки моделирования данных (ERD, нормализация) и SQL для решения реальных задач аналитика: от проектирования схемы до валидации требований через запросы.

Формат сдачи: Один PDF или документ Markdown, содержащий ответы на все задания.

Баллы: 10 баллов.

Ориентировочное время выполнения: 3–4 часа.

---

Задание 1: ER-диаграмма (3 балла)

Постановка задачи

Постройте ER-диаграмму (в нотации Crow's Foot) для системы Task Manager. Используйте расширенный список требований ниже.

Список сущностей (обязательные, 9 шт.)

№	Сущность	Атрибуты	Примечание
1	User	id, name, email, role, password_hash, created_at	Пользователь системы
2	Project	id, name, description, status, created_at	Проект-контейнер для задач
3	Task	id, title, description, status, priority, created_at, deadline, order	Задача
4	Comment	id, text, created_at, updated_at	Комментарий к задаче
5	Label	id, name, color	Метка (тег) для группировки задач
6	Attachment	id, file_name, file_url, file_size, uploaded_at	Вложение к задаче
7	TaskHistory	id, task_id, changed_field, old_value, new_value, changed_by, changed_at	Аудит: при каждом изменении задачи записывается строка в историю
8	Notification	id, user_id, task_id, type, message, is_read, created_at	Уведомление пользователю
9	ProjectMember	project_id, user_id, role, joined_at	Участник проекта (N:M User–Project)

Связи между сущностями

От	Тип	К	Через	Пояснение
Project	1 → *	Task	—	Проект содержит много задач
Project	* → *	User	ProjectMember	Участники проекта
Task	1 → *	Comment	—	У задачи много комментариев
Task	1 → *	Attachment	—	У задачи много вложений
Task	1 → *	TaskHistory	—	У задачи много записей истории
Task	1 → *	Notification	—	У задачи много уведомлений
Task	* → *	Label	TaskLabel	Метки задачи
User	1 → *	Task (author)	—	Пользователь — автор задач
User	1 → *	Task (assignee)	—	Пользователь — исполнитель задач
User	1 → *	Comment	—	Пользователь — автор комментария
User	1 → *	TaskHistory	—	Пользователь — инициатор изменений
User	1 → *	Notification	—	Пользователь — получатель уведомлений

Требования к диаграмме

№	Требование	За что снижаются баллы
1	Все 9+ сущностей присутствуют	Отсутствие сущности → −0,3 балла
2	У каждой сущности перечислены атрибуты с типами	Нет типов → −0,2 балла
3	Первичные ключи (PK) отмечены (подчёркиванием или (PK))	Нет PK → −0,2 балла
4	Внешние ключи (FK) отмечены явно	Нет FK → −0,2 балла
5	Кратность (кардинальность) на каждом конце связи	Нет кратности → −0,2 балла
6	Промежуточные таблицы TaskLabel и ProjectMember показаны	Отсутствие → −0,3 балла
7	Связи Task → User (author) и Task → User (assignee) показаны отдельно	Одна связь вместо двух → −0,2 балла

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

Шаг	Действие	Подсказка
1.1	Начните с сильных сущностей: User, Project, Task, Label	У них есть свой PK, они не зависят от других
1.2	Добавьте зависимые сущности: Comment, Attachment, TaskHistory, Notification	Все они ссылаются на Task через FK
1.3	Добавьте промежуточные: ProjectMember, TaskLabel	Реализуют связи N:M
1.4	Для каждой сущности добавьте атрибуты из таблицы	Минимум PK + 3 атрибута
1.5	Нарисуйте связи с кратностью	Не забудьте про две связи Task → User (author + assignee)
1.6	Проверьте: все ли стрелки подписаны? Для каждой линии должно быть понятно, почему она существует

Инструменты (рекомендуемые)

• dbdiagram.io — онлайн, простой, синтаксис DSL
• Draw.io — бесплатно, но нужно рисовать вручную
• PlantUML — текстовый формат (код + генерация диаграммы)
• Lucidchart — красивый, но ограничен бесплатный тариф

Шаблон ответа

[СКРИНШОТ ER-ДИАГРАММЫ ИЛИ ССЫЛКА НА dbdiagram.io]

Сущности (9 шт.): _______________________________________________.

Ключевые решения:
- PK для TaskHistory: __________________ (почему выбрали такой?)
- TaskLabel PK: __________________ (composite или surrogate?)
- Связь User–Task (assignee): NULL или NOT NULL? Почему?

Детальная таблица баллов

Критерий	Баллы	Условие получения	Типичная ошибка
Все 9+ сущностей присутствуют	1,0	Все 9 обязательных сущностей на диаграмме	Нет TaskHistory — 0,7 балла
Атрибуты с типами, PK, FK отмечены	0,5	У каждой сущности ≥3 атрибутов, PK подчёркнут, FK помечены	FK не отмечены — 0,2 балла
Связи с корректной кратностью	1,0	На каждом конце линии указана кардинальность	Нет кратности на Comment→Task — 0,2 балла
Промежуточные таблицы (TaskLabel, ProjectMember)	0,5	Явно показаны обе, с PK и FK	TaskLabel отсутствует — 0 баллов
Итого	3,0

---

Задание 2: Нормализация (2,5 балла)

Дана ненормализованная таблица для отчёта «Активность сотрудников»:

report_id	dept_name	dept_head	employee_id	employee_name	position	task_list	hours_logged
1	IT	Иванов	101	Анна	Разработчик	«Настроить CI/CD», «Написать API»	40
1	IT	Иванов	102	Иван	Разработчик	«Сверстать форму»	20
2	HR	Петрова	201	Мария	HR-менеджер	«Собеседование #1», «Собеседование #2», «Оффер»	30
2	HR	Петрова	202	Сергей	HR-менеджер	«Собеседование #3»	15
1	IT	Иванов	101	Анна	Разработчик	«Рефакторинг»	10

Задание 2.1. Приведение к 3НФ — пошагово (1,5 балла)

Шаг 1: Определите проблемы текущей таблицы

Признак	В этой таблице
Повторяющиеся данные?	dept_name и dept_head повторяются для каждого сотрудника
Список в колонке?	task_list — несколько значений через запятую
Составной PK?	Возможен? Смотрите внимательно

Подсказка: task_list — нарушение 1НФ (не атомарно). Одна колонка содержит несколько названий задач.

Шаг 2: Приведите к 1НФ

Что сделать: Развернуть task_list в отдельные строки. Каждая задача — отдельная строка.

Вопрос: Какой теперь PRIMARY KEY? Одного report_id недостаточно. Нужен составной ключ.

Результат 1НФ: Таблица, где каждая строка — один факт «сотрудник работал над задачей в рамках отчёта».

Шаг 3: Приведите к 2НФ

Что искать: Частичные зависимости от части составного PK.

Подсказка:

• employee_name зависит от employee_id (часть ключа) → выносим
• dept_name зависит от ...? (часть ключа) → выносим
• position зависит от employee_id → выносим
• hours_logged — зависит от всего PK? Да, это часы по конкретной задаче сотрудника

Результат 2НФ: Отдельные таблицы для сотрудников, отделов и фактов работы.

Шаг 4: Приведите к 3НФ

Что искать: Транзитивные зависимости.

Подсказка: dept_head зависит от dept_name, а не от PK. Если отдел IT возглавляет Иванов, а два сотрудника из IT — dept_head повторяется дважды. Выносим руководителя отдела в отдельную таблицу.

Результат 3НФ: Минимум 4 таблицы.

Шаг 5: Оформите ответ

Для каждого шага укажите:

Шаг	Что изменили	Какие зависимости обнаружены	Итоговый набор таблиц (с PK)
1НФ	...	...	...
2НФ	...	...	...
3НФ	...	...	...

Задание 2.2. SQL DDL для 3НФ (1 балл)

Напишите CREATE TABLE для всех таблиц, полученных после 3НФ (минимум 4 таблицы).

Требования к DDL:

• PRIMARY KEY для каждой таблицы
• FOREIGN KEY с REFERENCES для связей
• NOT NULL / NULL для обязательных/опциональных полей
• DEFAULT для полей, где это уместно

Пример оформления:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(100) NOT NULL,
    department_id INT REFERENCES departments(id)
);

Детальная таблица баллов

Критерий	Баллы	Условие получения	Типичная ошибка
Верный анализ 1НФ (указана проблема с task_list)	0,4	Описано, почему таблица не в 1НФ, что сделали	Не указана проблема списка в колонке — 0,2 балла
Верный анализ 2НФ (частичные зависимости)	0,4	Перечислены все частичные зависимости, правильно выделены таблицы	Не вынесен position — 0,2 балла
Верный анализ 3НФ (транзитивные зависимости)	0,4	dept_head вынесен в отдельную таблицу	dept_head остался в dept — 0,1 балла
Корректный DDL (CREATE TABLE, PK, FK)	0,8	≥4 таблицы, PK и FK указаны, типы данных разумные	Нет FK — 0,3 балла
Итого	2,0

---

Задание 3: SQL-запросы (3 балла)

Схема базы данных

⚠️ ВНИМАНИЕ (критическое исправление): В схеме ниже FOREIGN KEY (REFERENCES) для assignee_id в таблице tasks намеренно убран, чтобы в таблице могли существовать задачи, ссылающиеся на несуществующих пользователей. Это сделано для того, чтобы задание 4.3 имело технический смысл — если бы REFERENCES стоял, СУБД сама бы блокировала вставку «висячих» ссылок, и задание 4.3 стало бы бессмысленным.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    role VARCHAR(50)
);

CREATE TABLE projects (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    status VARCHAR(50)
);

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    status VARCHAR(50),
    priority VARCHAR(50),
    assignee_id INT,             -- ⚠️ НЕТ REFERENCES users(id) — намеренно!
    project_id INT REFERENCES projects(id),
    created_at TIMESTAMP,
    deadline DATE
);

CREATE TABLE comments (
    id INT PRIMARY KEY,
    task_id INT REFERENCES tasks(id),
    author_id INT REFERENCES users(id),
    text TEXT,
    created_at TIMESTAMP
);

CREATE TABLE attachments (
    id INT PRIMARY KEY,
    task_id INT REFERENCES tasks(id),
    file_name VARCHAR(255),
    file_url VARCHAR(500),
    file_size INT,
    uploaded_at TIMESTAMP
);

CREATE TABLE notifications (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    task_id INT REFERENCES tasks(id),
    type VARCHAR(100),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP
);

Запрос 3.1 (0,5 балла)

Описание: Найти все задачи, которые назначены на пользователя с email 'ivan@mail.com' и имеют статус 'In Progress'.

Подсказки:

• Нужен JOIN таблиц tasks и users
• Два условия: email = 'ivan@mail.com' и status = 'In Progress'
• Какие колонки вывести? Хотя бы: id задачи, название, статус

Шаблон ответа:

SELECT t.id, t.title, t.status
FROM tasks t
JOIN users u ON t.assignee_id = u.id   -- присоединяем, чтобы получить email
WHERE _________________________________
  AND _________________________________;

Запрос 3.2 (0,5 балла)

Описание: Найти все проекты (название) и количество задач в каждом проекте. Включить проекты, в которых нет задач (должно быть 0).

Подсказки:

• LEFT JOIN, не INNER JOIN! Иначе проекты без задач пропадут
• COUNT(t.id) считает только не-NULL, поэтому для пустых проектов будет 0
• GROUP BY p.id, p.name
• ⚠️ Ловушка LEFT JOIN: Если бы у вас было условие WHERE t.status = 'Active', проект без задач (с NULL в t.id) был бы отфильтрован. Но здесь нет фильтра по правой таблице — всё безопасно.

Шаблон ответа:

SELECT p.name AS project_name, COUNT(t.id) AS task_count
FROM projects p
_____ JOIN tasks t ON p.id = t.project_id
______ BY p.id, p.name
ORDER BY p.name;

Запрос 3.3 (1 балл) — Детальный разбор

Описание: Для каждого пользователя вывести:

1. Имя пользователя
2. Количество НЕпрочитанных уведомлений (is_read = false)
3. Количество задач, где он назначен исполнителем и статус не 'Done' (активные задачи)
4. Отсортировать по количеству активных задач (сначала те, у кого больше)

🧠 Разбор логики: что мы здесь делаем?

Этот запрос соединяет три таблицы (users → notifications → tasks) и считает две разные агрегации для каждого пользователя.

Почему LEFT JOIN?

• Мы хотим всех пользователей, даже у кого нет уведомлений или задач
• Если использовать INNER JOIN — пользователи без уведомлений (или без задач) пропадут
• Но нам нужно два LEFT JOIN, и они могут создать дублирование строк (один пользователь → много уведомлений И много задач)

Как считать непрочитанные уведомления? Два варианта:

Вариант А: COUNT + FILTER (PostgreSQL):

COUNT(n.id) FILTER (WHERE n.is_read = false) AS unread_notifications

Вариант Б: COUNT + CASE WHEN (универсальный):

COUNT(CASE WHEN n.is_read = false THEN 1 END) AS unread_notifications

COUNT(CASE WHEN ... THEN 1 END) считает только те строки, где условие истинно. Если условие ложно — CASE возвращает NULL, COUNT игнорирует NULL. Гениально просто.

🧠 Как не допустить дублирования при подсчёте задач?

Если сделать обычный LEFT JOIN на tasks, то для одного пользователя может быть:

• 3 уведомления
• 5 задач

И после JOIN получится 3 × 5 = 15 строк для этого пользователя — декартово произведение. COUNT(t.id) посчитает не 5, а 15!

Решение: Использовать подзапросы в SELECT (скалярные подзапросы), которые считают каждую агрегацию независимо:

SELECT
    u.name,
    (SELECT COUNT(*) FROM notifications n
     WHERE n.user_id = u.id AND n.is_read = false) AS unread_count,
    (SELECT COUNT(*) FROM tasks t
     WHERE t.assignee_id = u.id AND t.status != 'Done') AS active_task_count
FROM users u
ORDER BY active_task_count DESC;

Плюсы: Каждый подзапрос выполняется один раз для строки пользователя — нет декартова произведения, точные счётчики.

Минусы: Для 1000 пользователей — 2001 запрос (1 внешний + 1000 × 2 подзапроса). Но для аналитических отчётов — ок.

Альтернатива с LEFT JOIN + GROUP BY: Если вы уверены, что у вас нет дублирования или вы используете DISTINCT — можно через JOIN, но сложнее.

🧠 Почему ORDER BY по псевдониму?

В ORDER BY можно использовать псевдонимы из SELECT, потому что ORDER BY выполняется после SELECT (см. порядок выполнения SQL). ORDER BY active_task_count DESC — работает.

🧠 Полный запрос

SELECT
    u.name,
    (SELECT COUNT(*)
     FROM notifications n
     WHERE n.user_id = u.id AND n.is_read = false) AS unread_notifications,
    (SELECT COUNT(*)
     FROM tasks t
     WHERE t.assignee_id = u.id AND t.status != 'Done') AS active_tasks
FROM users u
ORDER BY active_tasks DESC;

Запрос 3.4 (1 балл) — Детальный разбор

Описание: Найти «забытые задачи» — задачи со статусом 'In Progress', у которых:

1. deadline был 7 или более дней назад (просрочены на неделю и больше)
2. нет ни одного комментария
3. нет ни одного вложения

Вывести: id задачи, название, дедлайн, имя исполнителя.

🧠 Разбор логики: три условия

Условие 1: deadline просрочен на 7+ дней

WHERE t.deadline <= CURRENT_DATE - INTERVAL '7 days'

или (зависит от СУБД):

WHERE t.deadline <= CURRENT_DATE - 7    -- в PostgreSQL DATE + INT работает
WHERE t.deadline <= DATE('now', '-7 days')  -- SQLite
WHERE t.deadline <= DATEADD(DAY, -7, GETDATE())  -- MSSQL

Логика: Если сегодня 29 мая, то CURRENT_DATE - 7 = 22 мая. Задачи с дедлайном ≤ 22 мая считаются «просроченными на неделю и более». То есть задача с дедлайном 15 мая — подходит, с дедлайном 28 мая — НЕТ.

Почему <=, а не <?

• <= включает задачи, у которых прошло ровно 7 дней (deadline = 22 мая, сегодня 29 мая — 7 дней)
• < включала бы только те, у которых прошло строго больше 7 дней (deadline ≤ 21 мая)

Условие 2: нет комментариев

Здесь ключевой оператор — NOT EXISTS. Почему не LEFT JOIN + IS NULL?

-- Вариант 1 (рекомендуемый): NOT EXISTS
WHERE NOT EXISTS (
    SELECT 1 FROM comments c WHERE c.task_id = t.id
)

-- Вариант 2 (работает, но медленнее на больших таблицах):
LEFT JOIN comments c ON t.id = c.task_id
WHERE c.id IS NULL

Как работает NOT EXISTS (пошагово):

Для каждой строки в tasks (после фильтра по status и deadline) СУБД проверяет:

1. Берёт t.id (например, 42)
2. Выполняет подзапрос: SELECT 1 FROM comments WHERE task_id = 42
3. Если подзапрос возвращает хотя бы одну строку (комментарий есть) → NOT EXISTS даёт FALSE → строка задачи отбрасывается
4. Если подзапрос возвращает ноль строк (комментариев нет) → NOT EXISTS даёт TRUE → строка задачи проходит в результат

Преимущество NOT EXISTS перед LEFT JOIN:

• NOT EXISTS использует Semi Join (полусоединение) — СУБД может остановиться на первой же найденной строке в comments
• LEFT JOIN читает все найденные строки (даже если не нужны)
• На больших таблицах NOT EXISTS обычно быстрее

Условие 3: нет вложений

Аналогично, через NOT EXISTS:

AND NOT EXISTS (
    SELECT 1 FROM attachments a WHERE a.task_id = t.id
)

🧠 Собираем всё вместе: полный запрос

SELECT
    t.id,
    t.title,
    t.deadline,
    u.name AS assignee_name
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id       -- LEFT, чтобы показать
                                                 -- задачи без исполнителя
WHERE t.status = 'In Progress'
  AND t.deadline <= CURRENT_DATE - INTERVAL '7 days'
  AND NOT EXISTS (
      SELECT 1 FROM comments c WHERE c.task_id = t.id
  )
  AND NOT EXISTS (
      SELECT 1 FROM attachments a WHERE a.task_id = t.id
  )
ORDER BY t.deadline ASC;

Почему LEFT JOIN для исполнителя? Если у задачи нет исполнителя (assignee_id = NULL), она всё равно должна попасть в отчёт — как «забытая и ни на кого не назначенная».

🧠 Что выводим?

Поле	Откуда	Комментарий
t.id	tasks	Идентификатор задачи
t.title	tasks	Название задачи
t.deadline	tasks	Дедлайн — чтобы видеть, насколько просрочена
u.name	users (через LEFT JOIN)	Имя исполнителя (может быть NULL)

---

Итоговая таблица баллов (Задание 3)

Запрос	Баллы	Условие получения	Типичная ошибка
3.1 Поиск задач по email + статус	0,5	Использован JOIN, верные условия WHERE	Нет JOIN — 0 баллов (нельзя получить email без users)
3.2 Проекты с задачами	0,5	LEFT JOIN, COUNT(t.id), GROUP BY, проекты без задач присутствуют	INNER JOIN — 0 баллов (Mobile пропадёт)
3.3 НЕпрочитанные уведомления + активные задачи	1,0	LEFT JOIN или подзапросы, верные условия фильтрации, нет декартова произведения	COUNT без фильтра — 0,3 балла
3.4 Забытые задачи	1,0	Все 3 условия: deadline, NOT EXISTS на comments, NOT EXISTS на attachments	Не все 3 условия — 0,3 балла за каждое пропущенное
Итого	3,0

---

Задание 4: Анализ данных (1,5 балла)

4.1. Определение нормальной формы (0,5 балла)

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(255),
    product_id INT,
    product_name VARCHAR(255),
    quantity INT,
    price DECIMAL(10,2)
);

Вопрос: В какой нормальной форме находится эта таблица? Обоснуйте.

Подсказки:

• 1НФ? Атомарность есть? Повторяющиеся группы есть? PK есть?
• 2НФ? А есть ли составной PK? Если PK — одна колонка, 2НФ удовлетворена автоматически
• 3НФ? Есть ли транзитивные зависимости? order_id → customer_id → customer_name? product_id → product_name?

Разбор функциональных зависимостей:

Если PK =	То какие зависимости есть	Вывод
order_id (один)	order_id → customer_id, order_id → product_id — но это странно для заказа с несколькими товарами	⚠️ product_name зависит от product_id, а product_id не является ключом → нарушение 2НФ? Нет, 2НФ требует составного PK.
(order_id, product_id) (составной)	customer_name зависит от customer_id, который зависит от order_id — частичная зависимость	→ Не в 2НФ

Какой PK вы видите? Задумайтесь: может ли в одном заказе быть два разных товара?

Формат ответа:

Таблица находится в _____НФ, потому что:
1. 1НФ: _________________________________________________________
2. 2НФ: _________________________________________________________
3. 3НФ: _________________________________________________________
Исправление: ____________________________________________________.

4.2. Денормализация для Highload (0,5 балла)

Ситуация: У вас есть БД в 3НФ. Каждый день выполняется отчёт «Топ-10 проектов по количеству задач». Сейчас запрос делает JOIN 4 таблиц и выполняется 5 секунд. Отчёт запускается 200 раз в день.

Вопрос: Ваше предложение по оптимизации? (2–3 предложения)

Подсказки:

• 5 секунд × 200 раз = 1000 секунд (17 минут) в день — терпимо?
• Можно ли денормализовать данные?
• Можно ли использовать Materialized View?
• Может быть, достаточно индекса?

Шаблон ответа:

Я предлагаю: ____________________________________________________
_______________________________________________________________.

Риски: _________________________________________________________.

4.3. Проверка целостности данных (0,5 балла)

Контекст: В таблице tasks поле assignee_id объявлено как INT без FOREIGN KEY (REFERENCES). Это намеренное решение — в задании 3 мы специально убрали REFERENCES, чтобы в базе могли появиться задачи с assignee_id, указывающим на несуществующего пользователя.

Задание: Напишите SQL-запрос, который находит «висячие ссылки» — задачи, у которых assignee_id указывает на пользователя, которого нет в таблице users.

🧠 Разбор логики

Идея: Найти строки из tasks, для которых нет соответствующей строки в users.

Вариант с NOT EXISTS:

SELECT t.id, t.title, t.assignee_id
FROM tasks t
WHERE t.assignee_id IS NOT NULL          -- игнорируем NULL (нет исполнителя)
  AND NOT EXISTS (
      SELECT 1 FROM users u WHERE u.id = t.assignee_id
  );

Вариант с LEFT JOIN + IS NULL:

SELECT t.id, t.title, t.assignee_id
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id
WHERE t.assignee_id IS NOT NULL
  AND u.id IS NULL;                       -- пользователь не найден

Оба варианта корректны. LEFT JOIN + IS NULL интуитивно понятнее новичкам, NOT EXISTS — быстрее на больших таблицах.

Почему условие IS NOT NULL? Если assignee_id = NULL, то LEFT JOIN даст u.id = NULL, и строка попадёт в результат. Но это не ошибка целостности — задача без исполнителя это нормально (валидное бизнес-состояние). Нам нужны только те задачи, где указан id, но такого пользователя нет.

Формат ответа:

SELECT _________________________________
FROM __________________________________
WHERE _________________________________
  AND _________________________________;

Детальная таблица баллов

Критерий	Баллы	Условие получения	Типичная ошибка
4.1 Определение НФ	0,5	Верно определена форма + обоснование	Просто «3НФ» без обоснования — 0,2 балла
4.2 Денормализация	0,5	Конкретное предложение с обоснованием	«Добавить индексы» — не решает проблему JOIN 4 таблиц — 0,2 балла
4.3 Поиск «висячих» assignee_id	0,5	NOT EXISTS или LEFT JOIN + IS NULL, учтён NULL	Нет IS NOT NULL — возвращает задачи без исполнителя — 0,2 балла
Итого	1,5

---

Итоговые критерии оценки

Распределение баллов

Задание	Макс. балл	Вес	Ориентировочное время
Задание 1: ER-диаграмма	3,0	30%	60 мин
Задание 2: Нормализация	2,5	25%	50 мин
Задание 3: SQL-запросы	3,0	30%	60 мин
Задание 4: Анализ данных	1,5	15%	30 мин
Всего	10,0	100%	~3,5 часа

Шкала перевода в оценку

Набрано баллов	Оценка	Уровень
9,0–10,0	Отлично	🟢 Моделирование и SQL на уровне Junior Analyst
7,0–8,9	Хорошо	🟡 Требуется практика на сложных JOIN и нормализации
5,0–6,9	Удовлетворительно	🟠 Рекомендуется повторение тем LEFT JOIN trap и ERD
0–4,9	Требуется доработка	🔜 Пересдать после доработки

Штрафы за общие нарушения

Нарушение	Штраф
SQL-запрос без форматирования (нечитаем)	−0,2 балла
В SELECT используется * вместо колонок	−0,1 балла
Нет GROUP BY для неагрегированных колонок	−0,3 балла
LEFT JOIN без необходимости (где нужен INNER)	−0,2 балла
INNER JOIN там, где нужен LEFT	−0,3 балла
Не указан PRIMARY KEY в CREATE TABLE	−0,2 балла

---

Чек-лист самопроверки перед сдачей

Задание 1 (ERD)

• 9+ сущностей присутствуют (включая TaskHistory, ProjectMember, TaskLabel)
• У каждой сущности есть атрибуты (минимум 3)
• PK отмечены (подчёркиванием или (PK))
• FK отмечены явно
• Кардинальность указана на каждом конце линии
• TaskLabel и ProjectMember показаны явно
• Связи Task → User (author) и Task → User (assignee) показаны отдельно
• Диаграмма читаема (не наложена, читаемые подписи)

Задание 2 (Нормализация)

• Описано, почему таблица не в 1НФ (task_list — не атомарно)
• Перечислены частичные зависимости для 2НФ
• Обнаружена транзитивная зависимость dept_head → dept_name
• Итоговый набор таблиц — минимум 4
• DDL содержит PK, FK, NOT NULL, DEFAULT
• Каждая таблица имеет осмысленное имя

Задание 3 (SQL)

• 3.1: Использован JOIN users + tasks, два условия в WHERE
• 3.2: LEFT JOIN (не INNER), COUNT(t.id), GROUP BY, проекты без задач видны
• 3.3: Нет декартова произведения (подзапросы или аккуратный GROUP BY), unread_count < 5 — разные фильтры для каждой агрегации
• 3.4: Все три условия: deadline −7 дней + NOT EXISTS для comments + NOT EXISTS для attachments
• Все запросы проверены на синтаксис (можно запустить)

Задание 4 (Анализ)

• 4.1: Указана нормальная форма + развёрнутое обоснование по каждой НФ
• 4.2: Конкретное предложение (не «добавить индексы»)
• 4.3: LEFT JOIN + IS NULL или NOT EXISTS, учтён NULL в assignee_id

---

Дополнительные материалы

• ERD: dbdiagram.io — онлайн-редактор с синтаксисом DSL
• SQL песочница: db-fiddle.com — можно запустить CREATE + INSERT + SELECT
• SQL практика: pgexercises.com — бесплатные упражнения по PostgreSQL
• Шпаргалка Crow's Foot: поиск «Crow's Foot notation cheat sheet» — карта символов
• Шпаргалка нормализации: «Database Normalization Explained» на essentialsql.com
• LEFT JOIN trap: раздел 4 урока 06-03 — пошаговый разбор ошибки