database-2025/README.md

Движение контингента

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

Все исходники в процессе создания сохраняются в облаке

Этапы проекта:

• Описание проекта.
• Концептуальная модель.
• Логическая модель.
• Физическая модель.
• Реализация схемы DDL.
• Заполнение схемы DML.
• Составление осмысленных запросов.

Технические итерации к выполнению:

• Индексы (indexes)
• Представления (views)
• Функции и Процедуры (funcs_and_procs)
• Триггеры (triggers)

Концептуальная модель

Для редактирования и изменения можно фоспользоваться .drawio файлом сохранения.

Логическая модель

Логическая модель была создана с использованием языка DBML, а также вспомогательных инструментов (среди прочих, drawdb).

Физическая модель

Физическая модель располагается по следующим ссылкам, в зависимости от нужного формата:

• Таблица-источник, а так же описание форматирования: docs.google.com
• PDF-версия: cloud.gazakbayev.net

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. Например,

SELECT passport_no, name, surname
    FROM Physicals
    WHERE to_tsvector('russian', name || ' ' || surname) @@ to_tsquery('russian', 'Петров:*');

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 студента, выводя транскрипт, например:

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.