167 lines
11 KiB
Markdown
167 lines
11 KiB
Markdown
|
||
# Движение контингента
|
||
|
||
Данный проект является упрощенным вариантом базы данных обучающихся, которая используется образовательными учреждениями для контроля контингента.
|
||
|
||
> Все исходники в процессе создания сохраняются в [облаке](https://cloud.gazakbayev.net/index.php/s/5XpDS4Zx287bejj)
|
||
|
||
**Этапы проекта:**
|
||
- [x] Описание проекта.
|
||
- [x] Концептуальная модель.
|
||
- [x] Логическая модель.
|
||
- [x] Физическая модель.
|
||
- [x] Реализация схемы DDL.
|
||
- [x] Заполнение схемы DML.
|
||
- [x] Составление осмысленных запросов.
|
||
|
||
**Технические итерации к выполнению:**
|
||
- [x] Индексы (indexes)
|
||
- [x] Представления (views)
|
||
- [x] Функции и Процедуры (funcs_and_procs)
|
||
- [x] Триггеры (triggers)
|
||
|
||
## Концептуальная модель
|
||
|
||
|
||
|
||
Для редактирования и изменения можно фоспользоваться [.drawio](https://cloud.gazakbayev.net/index.php/s/5XpDS4Zx287bejj?dir=/) файлом сохранения.
|
||
|
||
## Логическая модель
|
||
|
||
|
||
|
||
Логическая модель была создана с использованием языка DBML, а также вспомогательных инструментов (среди прочих, drawdb).
|
||
|
||
## Физическая модель
|
||
|
||
Физическая модель располагается по следующим ссылкам, в зависимости от нужного формата:
|
||
|
||
- Таблица-источник, а так же описание форматирования: [docs.google.com](https://docs.google.com/spreadsheets/d/1tKWyQ4CsW6sF9kxRUULqEuPtOp6bD87Umzqr1hbn7I8/edit?usp=sharing)
|
||
- PDF-версия: [cloud.gazakbayev.net](https://cloud.gazakbayev.net/index.php/s/5XpDS4Zx287bejj?dir=/&openfile=true)
|
||
|
||
## Data Definition
|
||
|
||
По вышеприведённым моделям был составлен скрипт schema.sql, которая создаёт отношения с проверкой условий, описанных в физической модели.
|
||
|
||
Например,
|
||
|
||
```
|
||
access_card VARCHAR(20) NOT NULL CHECK (access_card ~ '^[A-F0-9]{2}(:[A-F0-9]{2}){5}$'),
|
||
access_level VARCHAR(10) NOT NULL CHECK (access_level IN ('КАМПУС', 'ОБЩЕЖИТИЯ', 'ПОЛНЫЙ'))
|
||
```
|
||
|
||
```
|
||
group_id VARCHAR(10) PRIMARY KEY CHECK (group_id ~ '^[АБМ]\d{2}-\d{3}[а-я]?$'),
|
||
```
|
||
|
||
|
||
|
||
## Data Manipulation
|
||
|
||
С помощью библиотеку Faker и скриптов на языке Python были сгенерированы данные для дальнейшего взаимодействия с базой.
|
||
|
||
|
||
|
||
|
||
|
||
*Да, можно посмеяться с того, что фамилии и имена не согласуются, и вообще Faker - генерирует какую-то абсолютно рандомную фигню :)*
|
||
|
||
## Requests
|
||
|
||
В файле requests.sql приведены достаточно интересные запросы с использованием всех требуемых операторов, подзапросов и оконных функций:
|
||
|
||
- Студенты с одинаковыми фамилиями
|
||
|
||
|
||
|
||
- Средний балл студентов
|
||
|
||
|
||
|
||
- Топ 5 лучших
|
||
|
||
|
||
|
||
- Количества у кафедры дисциплин и студентов
|
||
|
||
|
||
|
||
- ФИ обучающихся (статус Учится)
|
||
|
||
|
||
|
||
- Прогресс изменения среднего балла по студентам
|
||
|
||
|
||
|
||
- Список группы с обучающимися, с указанием оценки успеваемости студента в группе "Ниже среднего" или "Выше среднего"
|
||
|
||
|
||
|
||
- Статистика отчислений по месяцам, основываясь на движении контингента (приказах)
|
||
|
||
|
||
|
||
- Научные руководители студентов
|
||
|
||
|
||
|
||
- Вывод преподавателей из ведомости по ФИО, принимающим предметам, средней оценки и уровня халявности
|
||
|
||
|
||
|
||
## Техническая итерация : Индексы
|
||
|
||
Созданы индексы `idx_physicals_fulltext_name` и `idx_movement_recent`.
|
||
|
||
Первый ускоряет полнотекстовый поиск, позволяя быстро находить лица, содержащие нужные имена в Personals. Например,
|
||
|
||
```sql
|
||
SELECT passport_no, name, surname
|
||
FROM Physicals
|
||
WHERE to_tsvector('russian', name || ' ' || surname) @@ to_tsquery('russian', 'Петров:*');
|
||
```
|
||
```sql
|
||
SELECT passport_no, name, surname
|
||
FROM Physicals
|
||
WHERE to_tsvector('russian', name || ' ' || surname) @@ plainto_tsquery('russian', 'Иван');
|
||
```
|
||
|
||
Второй ускоряет поиск по приказам из движения контингента, затрагивая интервал - последние 30 дней.
|
||
|
||
## Техническая итерация : Представления
|
||
|
||
В проекте представлено два представления:
|
||
|
||
`vw_student_performance` - представление, предоставляющее доступ к ID студенческого, ФИО студента и количество его проваленных/пройденных экзаменов/зачётных мероприятий, а также выводящий средний балл студентов.
|
||
|
||
`vw_birthdays_month` - просто забавное представление, выдающее список людей (студентов, преподавателей и пр.), у которых в этом месяце день рождения. Можно будет использовать для создания календаря дней рождений.
|
||
|
||
## Техническая итерация : Функции и Процедуры
|
||
|
||
`fn_low_enrollment(threshold INTEGER) -> TABLE` - функция, выводящая дисциплины с малым количеством студентов (основываясь на закрытых ведомостях). В аргумент принимает threshold - порог количества студентов для того, чтобы считать дисциплину малопосещаемой.
|
||
|
||
`fn_generate_transcript(p_student INTEGER) -> TEXT` - функция для вывода листа оценок студента. В аргумент принимает ID студента, выводя транскрипт, например:
|
||
|
||
```sql
|
||
SELECT fn_generate_transcript(42) AS transcript;
|
||
|
||
-- Transcript for student 42
|
||
-- Name: Иван Иванов
|
||
-- Group: А01-001
|
||
-- Status: УЧИТСЯ
|
||
--
|
||
-- 2024-12-15 - Математический анализ: 7
|
||
-- 2025-01-20 - Программирование: 9
|
||
-- 2025-03-05 - Математический анализ: 5
|
||
```
|
||
|
||
`sp_graduate_by_admission_year(p_year INTEGER) -> None` - процедура, присваивающая статус "Выпущен" занесением записи в Movements всему году поступления, указанным в p_year (срабатывает trigger 3).
|
||
|
||
## Техническая итерация : Триггеры
|
||
|
||
**Триггер 1** - предотвращает удаление группы с находящимися там студентами.
|
||
|
||
**Триггер 2** - стилизует вводимую электронную почту под lowercase, унифицируя тем самым все записи, облегчая восприятие почты.
|
||
|
||
**Триггер 3** - по обновлению статуса в Movement, проивзодит изменения в основном объекте студента Students. |