# Учебный кейс «Учёт газоперекачивающих агрегатов и результатов осмотров»

## 1. Описание ситуации

На двух компрессорных станциях эксплуатируются газоперекачивающие агрегаты (ГПА). Сотрудники проводят осмотры и фиксируют результат: «Без замечаний» или «Есть замечания», при необходимости добавляют комментарий.

Сведения ведутся в общей электронной таблице: названия станций и оборудования повторяются в каждой строке. Необходимо создать небольшую базу данных, чтобы исключить лишнее повторение сведений и получать ответы на вопросы руководителя.

Все названия, записи и результаты осмотров вымышлены. Кейс не предназначен для диагностики оборудования.

## 2. Что должна позволять база данных

- Хранить перечень станций и установленного оборудования.
- Регистрировать осмотры.
- Находить осмотры с замечаниями.
- Получать сводку по каждой станции.

## 3. Структура данных

В учебной модели три таблицы:

| Таблица | Что хранится | Поля |
|---|---|---|
| `stations` | Компрессорные станции | `station_id`, `name` |
| `units` | Газоперекачивающие агрегаты | `unit_id`, `station_id`, `inventory_no`, `commissioned_year` |
| `inspections` | История осмотров | `inspection_id`, `unit_id`, `inspection_date`, `result`, `comment` |

`id` — идентификатор записи. Первичный ключ однозначно определяет запись; внешний ключ связывает её с записью другой таблицы.

```text
Компрессорная станция (1) ── (N) Агрегат (1) ── (N) Осмотр
```

Одна станция может содержать несколько агрегатов; у агрегата может быть несколько осмотров. В этом кейсе агрегат закреплён за одной станцией. Историю перемещений, сотрудников, ремонты и датчики не рассматриваем.

Исходные данные: 2 станции, 4 агрегата, 6 осмотров. Данные для загрузки приведены в SQL и JSON ниже — создавать их самостоятельно не требуется.

## 4. Создание диаграммы — готовый код

Выполните этот шаг до перехода к SQL-заданиям.

1. Откройте [генератор диаграмм dbdiagram](https://dbdiagram.io/erd-generator).
2. Найдите редактор схемы. Выберите DBML, если сервис предлагает выбор формата.
3. Удалите демонстрационный пример и вставьте весь код из блока ниже. Строки с обозначением языка блока копировать не нужно: копируйте только сам код.
4. Схема должна появиться рядом с редактором. При необходимости перейдите из страницы генератора в интерактивный редактор по предложенной сервисом ссылке.
5. Найдите три таблицы и две линии связей. Сохраните скриншот диаграммы.

DBML — текстовое описание структуры. Этот код рисует диаграмму, но не создаёт таблицы в PostgreSQL. Регистрация для просмотра интерактивной ERD по описанию сервиса не требуется; дополнительные функции полного редактора могут требовать учётную запись.

```dbml
Table stations {
  station_id integer [pk, note: 'Идентификатор станции']
  name text [not null, note: 'Название станции']
  Note: 'Компрессорные станции'
}

Table units {
  unit_id integer [pk, note: 'Идентификатор агрегата']
  station_id integer [not null, note: 'Станция установки']
  inventory_no text [not null, unique, note: 'Инвентарный номер']
  commissioned_year integer [not null, note: 'Год ввода в эксплуатацию']
  Note: 'Газоперекачивающие агрегаты'
}

Table inspections {
  inspection_id integer [pk, note: 'Идентификатор осмотра']
  unit_id integer [not null, note: 'Осмотренный агрегат']
  inspection_date date [not null, note: 'Дата осмотра']
  result text [not null, note: 'Без замечаний или Есть замечания']
  comment text [note: 'Комментарий']
  Note: 'Журнал осмотров'
}

Ref: units.station_id > stations.station_id
Ref: inspections.unit_id > units.unit_id
```

Проверьте результат:

- В `units` поле `station_id` связано с `stations.station_id`.
- В `inspections` поле `unit_id` связано с `units.unit_id`.
- Название станции не дублируется в каждой строке осмотра.

Три уровня проектирования на этом примере:

- Концептуальный: станция, агрегат, осмотр и связи между ними.
- Логический: атрибуты, идентификаторы и структура связей.
- Физический: конкретные таблицы, типы данных и ограничения PostgreSQL в следующем разделе.

Диаграмма выше показывает уже детализированную структуру; для каждого уровня отдельную диаграмму строить не требуется.

## 5. Создание и заполнение базы PostgreSQL

1. Откройте [Aiven PostgreSQL Playground](https://aiven.io/tools/pg-playground).
2. Перейдите к SQL-редактору, удалите демонстрационный код.
3. Вставьте весь следующий SQL-скрипт и выполните его кнопкой запуска в интерфейсе.
4. Дождитесь завершения без ошибок.
5. Сохраните скрипт у себя в текстовом файле. Не обновляйте и не закрывайте вкладку во время работы.

> Внимание: первые три команды удаляют учебные таблицы и их содержимое. Скрипт нужен для первоначального создания БД и возврата к исходным данным. Не применяйте его к реальной рабочей базе.

```sql
DROP TABLE IF EXISTS inspections;
DROP TABLE IF EXISTS units;
DROP TABLE IF EXISTS stations;

CREATE TABLE stations (
    station_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE units (
    unit_id INTEGER PRIMARY KEY,
    station_id INTEGER NOT NULL REFERENCES stations(station_id),
    inventory_no TEXT UNIQUE NOT NULL,
    commissioned_year INTEGER NOT NULL
);

CREATE TABLE inspections (
    inspection_id INTEGER PRIMARY KEY,
    unit_id INTEGER NOT NULL REFERENCES units(unit_id),
    inspection_date DATE NOT NULL,
    result TEXT NOT NULL CHECK (result IN ('Без замечаний', 'Есть замечания')),
    comment TEXT
);

INSERT INTO stations (station_id, name) VALUES
(1, 'КС «Северная»'),
(2, 'КС «Южная»');

INSERT INTO units (unit_id, station_id, inventory_no, commissioned_year) VALUES
(1, 1, 'ГПА-001', 2018),
(2, 1, 'ГПА-002', 2020),
(3, 2, 'ГПА-003', 2019),
(4, 2, 'ГПА-004', 2022);

INSERT INTO inspections (inspection_id, unit_id, inspection_date, result, comment) VALUES
(1, 1, '2026-10-20', 'Без замечаний', 'Видимых замечаний нет'),
(2, 2, '2026-10-20', 'Есть замечания', 'Повреждена маркировка'),
(3, 3, '2026-10-20', 'Есть замечания', 'Зафиксирован посторонний шум'),
(4, 4, '2026-10-20', 'Без замечаний', 'Видимых замечаний нет'),
(5, 2, '2026-10-21', 'Без замечаний', 'Маркировка восстановлена'),
(6, 3, '2026-10-21', 'Есть замечания', 'Требуется дополнительная проверка');
```

### Проверка загрузки

Замените текст в редакторе проверочным запросом и выполните его. Выполненный код создания не нужно повторять перед каждым запросом; при необходимости сохраните его отдельно и оставляйте в редакторе только текущий запрос.

```sql
SELECT 'stations' AS table_name, COUNT(*) AS row_count FROM stations
UNION ALL
SELECT 'units', COUNT(*) FROM units
UNION ALL
SELECT 'inspections', COUNT(*) FROM inspections;
```

Ожидается: `stations` — 2, `units` — 4, `inspections` — 6.

Если после очистки редактора сервис начинает новую сессию и таблицы недоступны, используйте в одном запуске исходный скрипт и текущий запрос; учитывайте, что это каждый раз сбрасывает внесённые изменения. Для заданий добавления и исправления записи выполняйте все связанные команды вместе после исходного скрипта. Резервная площадка — [db<>fiddle](https://dbfiddle.uk/), выберите PostgreSQL: создание и загрузку поместите в первый блок, запросы — в следующие.

## 6. Работа с реляционной базой: задания с решениями

Сначала выполните задания 1–6 на исходных данных. Затем переходите к добавлению и изменению записей. Каждый блок ниже можно скопировать в SQL-редактор.

### Задание 1. Просмотреть все агрегаты

Решение:

```sql
SELECT *
FROM units
ORDER BY unit_id;
```

Ожидаемый результат и объяснение: Появятся четыре строки: ГПА-001, ГПА-002, ГПА-003 и ГПА-004. `SELECT` означает «выбрать», `*` — все столбцы, `FROM` — из какой таблицы.

### Задание 2. Найти агрегаты, введённые после 2019 года

Решение:

```sql
SELECT inventory_no, commissioned_year
FROM units
WHERE commissioned_year > 2019
ORDER BY inventory_no;
```

Ожидаемый результат и объяснение: Две строки: ГПА-002 — 2020; ГПА-004 — 2022. `WHERE` задаёт условие. Значение 2019 не включается.

### Задание 3. Найти осмотры с замечаниями

Решение:

```sql
SELECT inspection_id, unit_id, inspection_date, comment
FROM inspections
WHERE result = 'Есть замечания'
ORDER BY inspection_id;
```

Ожидаемый результат и объяснение: Три записи: осмотры № 2, 3 и 6. Это число событий, а не число неисправных агрегатов.

### Задание 4. Показать станцию каждого агрегата

Решение:

```sql
SELECT u.inventory_no, s.name AS station
FROM units AS u
JOIN stations AS s ON s.station_id = u.station_id
ORDER BY u.inventory_no;
```

Ожидаемый результат и объяснение: ГПА-001 и ГПА-002 относятся к КС «Северная», ГПА-003 и ГПА-004 — к КС «Южная». `JOIN` соединяет таблицы, `ON` указывает связь. `u` и `s` — короткие обозначения таблиц, `AS station` — подпись столбца результата.

### Задание 5. Получить подробный список замечаний

Решение:

```sql
SELECT i.inspection_date,
       s.name AS station,
       u.inventory_no,
       i.comment
FROM inspections AS i
JOIN units AS u ON u.unit_id = i.unit_id
JOIN stations AS s ON s.station_id = u.station_id
WHERE i.result = 'Есть замечания'
ORDER BY i.inspection_date, u.inventory_no;
```

Ожидаемый результат и объяснение: Три строки: ГПА-002 на КС «Северная» — 20.10.2026; ГПА-003 на КС «Южная» — 20.10.2026 и 21.10.2026. Название станции получено через две связи.

### Задание 6. Получить сводку по всем станциям

Решение:

```sql
SELECT s.name AS station,
       COUNT(i.inspection_id) AS inspections_total,
       SUM(CASE WHEN i.result = 'Есть замечания'
                THEN 1 ELSE 0 END) AS inspections_with_remarks
FROM stations AS s
LEFT JOIN units AS u ON u.station_id = s.station_id
LEFT JOIN inspections AS i ON i.unit_id = u.unit_id
GROUP BY s.station_id, s.name
ORDER BY s.station_id;
```

Ожидаемый результат и объяснение: КС «Северная»: всего 3 осмотра, с замечаниями 1. КС «Южная»: всего 3 осмотра, с замечаниями 2. `GROUP BY` объединяет записи по станции. `COUNT` считает осмотры. `SUM(CASE ...)` считает только записи с замечаниями. `LEFT JOIN` позволяет сохранить в отчёте даже станцию без осмотров.

### Задание 7. Зарегистрировать новый осмотр

Добавьте осмотр ГПА-001 от 22.10.2026. В комментарии специально оставлена опечатка для следующего задания.

```sql
INSERT INTO inspections
    (inspection_id, unit_id, inspection_date, result, comment)
VALUES
    (7, 1, '2026-10-22', 'Без замечаний', 'Осмотр заврешён');

SELECT * FROM inspections WHERE inspection_id = 7;
```

Ожидается новая запись № 7. Общее число осмотров увеличится с 6 до 7. Этот `INSERT` выполняйте один раз: повторная вставка того же идентификатора вызовет ошибку первичного ключа.

### Задание 8. Исправить комментарий

```sql
UPDATE inspections
SET comment = 'Осмотр завершён'
WHERE inspection_id = 7;

SELECT * FROM inspections WHERE inspection_id = 7;
```

Ожидается: у записи № 7 комментарий «Осмотр завершён». `UPDATE` изменяет запись, `WHERE` ограничивает изменение конкретной записью. Не убирайте условие: иначе комментарий изменится у всех осмотров.

Повторите сводку из задания 6. После добавления осмотра у КС «Северная» будет 4 осмотра, из них 1 с замечаниями; у КС «Южная» — 3 и 2 соответственно.

## 7. Работа с документной моделью: задания с решениями

Используем тот же исходный набор из шести осмотров, до добавления записи № 7. Здесь каждый осмотр — отдельный документ. Название станции и номер агрегата записаны прямо внутри него. Это альтернативная учебная модель с намеренным повторением части сведений.

### Шаг 1. Загрузить документы

1. Откройте [Mongo Playground](https://mongoplayground.net/).
2. Выберите режим `single collection` и формат `bson`, если предлагается выбор.
3. В поле `Database` удалите пример и вставьте весь массив ниже.
4. В поле `Query` вставьте запрос из следующего шага.
5. Выполните запрос командой запуска в интерфейсе или сочетанием `Ctrl + Enter` (`Cmd + Enter` на macOS).

Поле Database задаёт исходные документы песочницы. Команда SQL или `insertMany()` здесь не нужна.

```json
[
  {
    "inspection_id": 1,
    "date": "2026-10-20",
    "station": "КС «Северная»",
    "unit": "ГПА-001",
    "result": "Без замечаний",
    "details": {
      "observation": "Видимых замечаний нет"
    }
  },
  {
    "inspection_id": 2,
    "date": "2026-10-20",
    "station": "КС «Северная»",
    "unit": "ГПА-002",
    "result": "Есть замечания",
    "details": {
      "observation": "Повреждена маркировка"
    }
  },
  {
    "inspection_id": 3,
    "date": "2026-10-20",
    "station": "КС «Южная»",
    "unit": "ГПА-003",
    "result": "Есть замечания",
    "details": {
      "observation": "Зафиксирован посторонний шум"
    }
  },
  {
    "inspection_id": 4,
    "date": "2026-10-20",
    "station": "КС «Южная»",
    "unit": "ГПА-004",
    "result": "Без замечаний",
    "details": {
      "observation": "Видимых замечаний нет"
    }
  },
  {
    "inspection_id": 5,
    "date": "2026-10-21",
    "station": "КС «Северная»",
    "unit": "ГПА-002",
    "result": "Без замечаний",
    "details": {
      "observation": "Маркировка восстановлена"
    }
  },
  {
    "inspection_id": 6,
    "date": "2026-10-21",
    "station": "КС «Южная»",
    "unit": "ГПА-003",
    "result": "Есть замечания",
    "details": {
      "observation": "Требуется дополнительная проверка"
    }
  }
]
```

### Шаг 2. Показать все осмотры

Вставьте в поле Query:

```javascript
db.collection.find({})
```

Ожидается: 6 документов. Дата в примере хранится строкой `YYYY-MM-DD`, а не специальным типом даты MongoDB.

### Шаг 3. Найти осмотры с замечаниями

```javascript
db.collection.find({
  "result": "Есть замечания"
})
```

Ожидается: 3 документа — `inspection_id` 2, 3 и 6.

### Шаг 4. Найти историю выбранного агрегата

```javascript
db.collection.find({
  "unit": "ГПА-003"
})
```

Ожидается: 2 документа — осмотры № 3 и 6.

### Шаг 5. Добавить поле к одному исходному документу

В поле Database найдите первый документ (`inspection_id` 1). После строки `"inspection_id": 1,` добавьте строку:

```json
"photo": "inspection_01.jpg",
```

Не удаляйте запятые между полями. Затем выполните:

```javascript
db.collection.find({
  "inspection_id": 1
})
```

Ожидается: документ № 1 с новым полем `photo`. Оно содержит имя файла, не саму фотографию. Это редактирование исходного набора песочницы; изменение записи командой СУБД в реляционной части показано отдельно через `UPDATE`.

### Шаг 6. Получить сводку по замечаниям

Дополнительное задание; если времени недостаточно, его демонстрирует преподаватель.

```javascript
db.collection.aggregate([
  { "$match": { "result": "Есть замечания" } },
  { "$group": { "_id": "$station", "remarks_count": { "$sum": 1 } } },
  { "$sort": { "_id": 1 } }
])
```

Ожидается: КС «Северная» — 1; КС «Южная» — 2. `$match` отбирает документы, `$group` объединяет по станции, `$sum` считает, `$sort` упорядочивает. В этой сводке появятся только станции, у которых есть замечания; станции без замечаний она не выводит.

## 8. Ссылки на инструменты

- [dbdiagram: генератор диаграмм](https://dbdiagram.io/erd-generator) — описание сервиса подтверждает приём DBML и SQL.
- [PostgreSQL Playground](https://aiven.io/tools/pg-playground).
- [Mongo Playground](https://mongoplayground.net/).
- [Документация Mongo Playground](https://mongoplayground.net/static/docs-8.html).
- [Резервная SQL-площадка db<>fiddle](https://dbfiddle.uk/).