Проектирование и Реализация Реляционной Базы Данных для ИС «Компьютерные курсы»: От Академической Теории до Соответствия ФЗ-152

В 2023 году PostgreSQL четырежды (2017, 2018, 2020, 2023) признавался DB-Engines «СУБД года» за самый высокий годовой прирост популярности среди всех 417 отслеживаемых систем. Это не просто цифра, а яркий индикатор сдвига в ландшафте баз данных, отражающий растущий спрос на гибкие, стандартизированные и открытые решения в условиях стремительного развития цифровых технологий и, в частности, информационных систем для образовательного сектора.

Введение: Актуальность, Цели и Обзор Проектных Решений

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

Целью настоящей работы является разработка всеобъемлющей методологической основы для создания и защиты академической работы (курсового проекта), охватывающей полный цикл проектирования реляционной базы данных (БД) для информационной системы (ИС) «Компьютерные курсы». Это включает в себя анализ предметной области, концептуальное и логическое моделирование, физическую реализацию с использованием современных стандартов SQL, а также интеграцию механизмов безопасности в соответствии с актуальным российским законодательством.

Данный отчет структурирован таким образом, чтобы последовательно провести читателя через все этапы проектирования. Мы начнем с теоретических основ реляционных баз данных и принципов нормализации, затем перейдем к функциональному анализу и созданию инфологической модели ИС «Компьютерные курсы». Отдельное внимание будет уделено обоснованию выбора СУБД (PostgreSQL) и детальному даталогическому проектированию с формальным доказательством нормализации. Далее последует раздел о практической реализации дополнительной логики с использованием триггеров, хранимых процедур и представлений. Завершающим, но не менее важным блоком станет глубокий анализ обеспечения информационной безопасности и соответствия нормативным требованиям ФЗ-152, что является критически важным аспектом для любой современной ИС, обрабатывающей персональные данные.

Теоретические и Методологические Основы Проектирования Реляционных БД

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

Система управления базами данных (СУБД) представляет собой комплекс программного обеспечения, предназначенный для создания, управления, модификации и доступа к данным в базе данных. Она выступает в роли посредника между пользователями (или приложениями) и самой базой данных, обеспечивая не только хранение, но и целостность, безопасность и восстановление данных. От выбора СУБД зависит производительность, масштабируемость и функциональные возможности всей информационной системы.

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

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

Принципы Нормализации: От 3НФ к НФБК

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

Третья нормальная форма (3НФ) была впервые сформулирована Э. Ф. Коддом в 1971 году и является одним из наиболее часто достигаемых уровней нормализации в большинстве практических проектов. Отношение находится в 3НФ, если оно удовлетворяет двум условиям:

1. Оно находится во второй нормальной форме (2НФ).
2. Ни один неключевой атрибут не находится в транзитивной функциональной зависимости от потенциального ключа отношения.

Транзитивная зависимость возникает, когда неключевой атрибут зависит от другого неключевого атрибута, который, в свою очередь, зависит от первичного ключа. Иными словами, если A → B и B → C, то существует транзитивная зависимость A → C. Суть Третьей нормальной формы часто резюмируется в правиле Билла Кента: «Каждый неключевой атрибут должен предоставлять информацию о ключе, полном ключе и ни о чём, кроме ключа». Это означает, что каждый неключевой атрибут должен напрямую зависеть только от первичного ключа и не зависеть от других неключевых атрибутов. Например, в таблице со студентами и их факультетами, если «НазваниеФакультета» зависит от «ID_Факультета», а «ID_Факультета» зависит от «ID_Студента», то «НазваниеФакультета» транзитивно зависит от «ID_Студента». Для приведения к 3НФ необходимо вынести информацию о факультетах в отдельную таблицу.

Нормальная форма Бойса-Кодда (НФБК) является более строгой нормальной формой, чем 3НФ. Отношение находится в НФБК тогда и только тогда, когда детерминанты всех его нетривиальных функциональных зависимостей являются потенциальными ключами. НФБК устраняет некоторые типы аномалий, которые могут оставаться в 3НФ, особенно в случаях, когда отношение имеет два или более потенциальных составных ключа, которые пересекаются. НФБК необходима в тех редких случаях, когда есть несколько потенциальных ключей, и один из неключевых атрибутов является детерминантом части потенциального ключа, которая не является первичным ключом. Если 3НФ фокусируется на зависимостях неключевых атрибутов от первичного ключа, то НФБК распространяет это правило на все детерминанты, требуя, чтобы они были потенциальными ключами. Для большинства практических приложений достижение 3НФ достаточно, но для систем с высокой критичностью к целостности данных и сложными зависимостями НФБК является предпочтительной целью.

Функциональный Анализ и Концептуальное Моделирование ИС «Компьютерные курсы»

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

Функциональные Требования и Сценарии Использования (Use Cases)

Ключевые функциональные требования ИС «Компьютерные курсы» охватывают стандартные операции CRUD (Create, Read, Update, Delete) для основных сущностей, а также более сложные, транзакционные сценарии. Основные операции включают:

• Корректировка, удаление и добавление данных для студентов, преподавателей, курсов и групп.
• Просмотр данных в виде детализированной карточки (например, профиль студента) или общего списка (например, список всех доступных курсов).

Однако для полноценной и масштабируемой системы этих базовых требований недостаточно. Необходимо учесть специфическую бизнес-логику образовательного процесса, которая формирует следующие, более детализированные сценарии использования (Use Cases):

1. FRQ1: Заключение договора на участие в курсе. Система должна предоставить Студенту возможность выбрать один или несколько курсов и сформировать на их основе официальный договор. Этот сценарий включает проверку доступности мест, фиксацию выбора и генерацию документа.
2. FRQ4: Оплата через внешний шлюз. После заключения договора Студент должен иметь возможность произвести оплату через интегрированный внешний платежный шлюз (например, банковская карта, электронные деньги). Система должна корректно обработать статус платежа и обновить финансовые записи.
3. FRQ5: Применение промо-кода. Для стимулирования продаж и проведения акций система должна поддерживать механизм применения промо-кодов, которые предоставляют скидку на итоговую сумму договора. Это требует проверки валидности промо-кода, расчета новой суммы и фиксации скидки.
4. Регистрация Студента на Курс: Этот комплексный Use Case объединяет несколько подшагов:
   • Студент выбирает желаемый курс и группу.
   • Система проверяет наличие свободных мест в выбранной группе.
   • При наличии мест, система фиксирует «ЗаписьНаКурс» и инициирует процесс заключения договора/оплаты.
   • В случае успешной регистрации, количество свободных мест в группе автоматически уменьшается.
5. Управление Расписанием: Преподаватели и администраторы должны иметь возможность просматривать и корректировать расписание занятий, привязывая группы к конкретным временным слотам и аудиториям.
6. Управление Оценками и Прогрессом: (Гипотетический, для будущего расширения) Система может фиксировать оценки студентов по модулям курса и отслеживать их прогресс обучения.

Инфологическая Модель Предметной Области (ERD/UML-диаграмма)

На основе описанных функциональных требований и сценариев использования формируется инфологическая модель, которая является абстрактным представлением данных без привязки к конкретной СУБД. Наиболее распространенными инструментами для такого моделирования являются ER-диаграммы (Entity-Relationship Diagram) или UML-диаграммы классов.

Для ИС «Компьютерные курсы» инфологическая модель должна включать следующие основные сущности и связи, отражающие сложную бизнес-логику:

1. Сущности:
   • Студент (Student): Хранит персональные данные (ФИО, дата рождения, контакты, паспортные данные) студента. ID_Студента — первичный ключ.
   • Преподаватель (Teacher): Хранит данные о сотрудниках, ведущих курсы (ФИО, квалификация, контакты). ID_Преподавателя — первичный ключ.
   • Курс (Course): Хранит информацию о программе обучения (название курса, описание, продолжительность в часах, базовая стоимость). ID_Курса — первичный ключ.
   • Группа (Group): Объединяет студентов по определенному курсу и расписанию. Содержит ID_Группы, ID_Курса (внешний ключ к Курс), ID_Преподавателя (внешний ключ к Преподаватель), ДатаНачала, ДатаОкончания, МаксМест, ЗанятоМест.
   • Расписание (Schedule): Определяет время и место проведения занятий для конкретной Группы. Содержит ID_Расписания, ID_Группы (внешний ключ), ДеньНедели, ВремяНачала, ВремяОкончания, Аудитория.
   • Договор (Contract): Фиксирует факт заключения договора между Студентом и Учебным Центром. Содержит ID_Договора, ID_Студента (внешний ключ), ДатаЗаключения, СуммаДоговора, СуммаСкидки (если применен промо-код), ИтоговаяСумма.
   • ПромоКод (PromoCode): Содержит информацию о доступных промо-кодах (Код, РазмерСкидки, ДатаНачалаДействия, ДатаОкончанияДействия, Активен).
   • ЗаписьНаКурс (Enrollment): Фиксирует факт регистрации студента на конкретный курс в рамках определенного договора. Эта сущность связывает Студент с Группа через Договор. Содержит ID_Записи, ID_Договора (внешний ключ), ID_Группы (внешний ключ), ДатаЗаписи, СтатусЗаписи (например, «Ожидает оплаты», «Зарегистрирован», «Отменен»).
   • Оплата (Payment): Отслеживает финансовые транзакции Студента по Договору. Содержит ID_Оплаты, ID_Договора (внешний ключ), СуммаОплаты, ДатаОплаты, МетодОплаты, СтатусОплаты.
2. Связи:
   • Студент — Договор: 1:M (Один студент может заключить несколько договоров).
   • Договор — ЗаписьНаКурс: 1:M (Один договор может охватывать запись на несколько курсов/групп).
   • Договор — Оплата: 1:M (Один договор может быть оплачен несколькими частями).
   • Курс — Группа: 1:M (Один курс может иметь несколько групп).
   • Преподаватель — Группа: 1:M (Один преподаватель может вести несколько групп).
   • Группа — Расписание: 1:M (Одна группа может иметь несколько занятий по расписанию).
   • Группа — ЗаписьНаКурс: 1:M (Одна группа может иметь множество записей студентов).
   • Договор — ПромоКод: M:1 (Несколько договоров могут использовать один промо-код).

ERD/UML-диаграмма классов (концептуальная):

erDiagram
    Студент ||--o{ Договор : "1:M"
    Преподаватель ||--o{ Группа : "1:M"
    Курс ||--o{ Группа : "1:M"
    Группа ||--o{ Расписание : "1:M"
    Группа ||--o{ ЗаписьНаКурс : "1:M"
    Договор ||--o{ Оплата : "1:M"
    Договор ||--o{ ЗаписьНаКурс : "1:M"
    ПромоКод }o--|| Договор : "M:1"

    Студент {
        INT ID_Студента PK
        VARCHAR ФИО
        DATE ДатаРождения
        VARCHAR Контакты
        VARCHAR ПаспортныеДанные
    }
    Преподаватель {
        INT ID_Преподавателя PK
        VARCHAR ФИО
        VARCHAR Квалификация
        VARCHAR Контакты
    }
    Курс {
        INT ID_Курса PK
        VARCHAR НазваниеКурса
        TEXT Описание
        INT ПродолжительностьЧасов
        DECIMAL БазоваяСтоимость
    }
    Группа {
        INT ID_Группы PK
        INT ID_Курса FK
        INT ID_Преподавателя FK
        DATE ДатаНачала
        DATE ДатаОкончания
        INT МаксМест
        INT ЗанятоМест
    }
    Расписание {
        INT ID_Расписания PK
        INT ID_Группы FK
        VARCHAR ДеньНедели
        TIME ВремяНачала
        TIME ВремяОкончания
        VARCHAR Аудитория
    }
    Договор {
        INT ID_Договора PK
        INT ID_Студента FK
        DATE ДатаЗаключения
        DECIMAL СуммаДоговора
        DECIMAL СуммаСкидки
        DECIMAL ИтоговаяСумма
        VARCHAR КодПромоКода FK "opt"
    }
    ПромоКод {
        VARCHAR Код PK
        DECIMAL РазмерСкидки
        DATE ДатаНачалаДействия
        DATE ДатаОкончанияДействия
        BOOLEAN Активен
    }
    ЗаписьНаКурс {
        INT ID_Записи PK
        INT ID_Договора FK
        INT ID_Группы FK
        DATE ДатаЗаписи
        VARCHAR СтатусЗаписи
    }
    Оплата {
        INT ID_Оплаты PK
        INT ID_Договора FK
        DECIMAL СуммаОплаты
        DATE ДатаОплаты
        VARCHAR МетодОплаты
        VARCHAR СтатусОплаты
    }

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

Обоснование Выбора СУБД и Даталогическое Проектирование

Выбор конкретной системы управления базами данных (СУБД) является одним из ключевых решений на этапе проектирования информационной системы. Этот выбор существенно влияет на производительность, масштабируемость, стоимость владения и возможности реализации сложной бизнес-логики.

Критерии Выбора и Сравнительный Анализ РСУБД

При выборе СУБД для ИС «Компьютерные курсы» мы будем руководствоваться следующими критериями:

• Тип данных: Поддержка различных типов данных, включая расширенные (например, для хранения JSON-объектов или массивов).
• Производительность: Способность обрабатывать запросы с минимальной задержкой, особенно при увеличении нагрузки.
• Масштабируемость: Возможность эффективно наращивать объемы данных и количество пользователей.
• Стоимость лицензирования: Общие затраты на использование СУБД (лицензии, поддержка).
• Надежность: Устойчивость к сбоям, наличие механизмов восстановления и обеспечения целостности данных.
• Поддержка расширенных функций: Наличие хранимых процедур, триггеров, представлений, а также возможности расширения функционала.
• Соответствие стандартам: Приверженность СУБД стандартам SQL (например, ISO/IEC 9075:2023).
• Региональные тренды: Популярность и поддержка СУБД в конкретном регионе (например, РФ), особенно в контексте импортозамещения.

Проведем сравнительный анализ трех популярных реляционных СУБД: MySQL, PostgreSQL и MS SQL Server.

Критерий	MySQL	PostgreSQL	MS SQL Server
Лицензирование	Открытый исходный код (Community Edition), принадлежит Oracle. Есть коммерческие версии.	Открытый код (управляется сообществом), бесплатный.	Коммерческая (принадлежит Microsoft). Есть бесплатная Express Edition для небольших проектов, но с ограничениями по размеру БД и ЦПУ/RAM.
Расширенные типы данных	Ограниченный набор, ориентирован на простые веб-приложения. Поддерживает JSON, но менее гибко, чем PostgreSQL.	Поддержи��ает широкий спектр (JSONB, массивы, hstore, XML, геометрические типы), высокая гибкость и расширяемость.	Ограниченная поддержка пользовательских типов данных по сравнению с PostgreSQL. Поддерживает XML, JSON.
Хранимые процедуры/Триггеры	Поддерживает, в основном на SQL-подобном языке.	Поддерживает на нескольких языках (например, PL/pgSQL, PL/Python, PL/Tcl, PL/Perl), что дает большую гибкость для сложной логики.	Поддерживает, в основном на T-SQL (Transact-SQL).
Производительность/Масштабируемость	Хорошо подходит для горизонтальной масштабируемости (веб-приложения), средние проекты. Высокая скорость чтения, но может быть менее эффективен для сложных запросов.	Высокая надежность и целостность данных, подходит для сложных структур и больших проектов с высокой нагрузкой. Отличная производительность для сложных аналитических запросов.	Высокая производительность, корпоративный уровень, предлагает средства аналитики и отчетности. Оптимизирован для Windows-инфраструктуры.
Соответствие стандартам SQL	Не полное соответствие стандарту ANSI SQL.	Стремится к полному соответствию последней версии стандарта ISO/IEC 9075:2023 (SQL:2023), реализуя большую часть его основной функциональности.	Высокое соответствие стандартам SQL, но с собственными расширениями T-SQL.
Популярность в РФ (2023 г. по Google Trends)	Ниже, чем PostgreSQL и Oracle.	Значительно выше: на 65% выше, чем у Oracle, и на 76% выше, чем у MySQL. Это связано с тенденцией импортозамещения и уходом западных вендоров.	Ниже, чем PostgreSQL. Привязка к продуктам Microsoft.
Использование для академических проектов	Часто используется для простых веб-проектов из-за простоты освоения.	Идеален для академических проектов, требующих глубокой проработки теории БД, стандартов и расширенных функций.	Обычно для коммерческих проектов на платформе Microsoft.

Вывод по выбору СУБД: Для академического проекта с акцентом на расширенные функции, строгое соответствие стандартам SQL, целостность данных и гибкость в реализации сложной бизнес-логики, PostgreSQL является предпочтительным выбором. Его приверженность стандартам (ISO/IEC 9075:2023), поддержка широкого спектра типов данных и возможность использования различных языков для хранимых процедур делают его мощным инструментом для демонстрации глубоких знаний в области проектирования БД. Кроме того, бесплатная лицензия и растущая популярность в России (обусловленная трендами импортозамещения) делают его прагматичным выбором.

Даталогическая Структура и Формальное Доказательство Нормализации

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

Схема реляционной структуры (DDL-код для PostgreSQL):

-- Таблица Студент
CREATE TABLE Students (
    student_id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    contact_info VARCHAR(255),
    passport_data VARCHAR(255) UNIQUE NOT NULL
);

-- Таблица Преподаватель
CREATE TABLE Teachers (
    teacher_id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    qualification VARCHAR(255),
    contact_info VARCHAR(255)
);

-- Таблица Курс
CREATE TABLE Courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    duration_hours INT CHECK (duration_hours > 0),
    base_cost NUMERIC(10, 2) CHECK (base_cost >= 0)
);

-- Таблица ПромоКод
CREATE TABLE PromoCodes (
    promo_code VARCHAR(50) PRIMARY KEY,
    discount_percentage NUMERIC(5, 2) CHECK (discount_percentage >= 0 AND discount_percentage <= 100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    CHECK (end_date >= start_date)
);

-- Таблица Группа
CREATE TABLE Groups (
    group_id SERIAL PRIMARY KEY,
    course_id INT NOT NULL REFERENCES Courses(course_id),
    teacher_id INT NOT NULL REFERENCES Teachers(teacher_id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    max_capacity INT CHECK (max_capacity > 0),
    current_enrollment INT DEFAULT 0 CHECK (current_enrollment >= 0 AND current_enrollment <= max_capacity),
    CHECK (end_date >= start_date)
);

-- Таблица Договор
CREATE TABLE Contracts (
    contract_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES Students(student_id),
    contract_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10, 2) CHECK (total_amount >= 0) NOT NULL,
    discount_amount NUMERIC(10, 2) DEFAULT 0 CHECK (discount_amount >= 0),
    final_amount NUMERIC(10, 2) CHECK (final_amount >= 0) NOT NULL,
    promo_code VARCHAR(50) REFERENCES PromoCodes(promo_code),
    CHECK (final_amount = total_amount - discount_amount)
);

-- Таблица ЗаписьНаКурс
CREATE TABLE Enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    contract_id INT NOT NULL REFERENCES Contracts(contract_id),
    group_id INT NOT NULL REFERENCES Groups(group_id),
    enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(50) DEFAULT 'Pending Payment' CHECK (status IN ('Pending Payment', 'Enrolled', 'Completed', 'Cancelled')),
    UNIQUE (contract_id, group_id) -- Студент не может быть записан в одну и ту же группу по одному и тому же договору дважды
);

-- Таблица Оплата
CREATE TABLE Payments (
    payment_id SERIAL PRIMARY KEY,
    contract_id INT NOT NULL REFERENCES Contracts(contract_id),
    payment_amount NUMERIC(10, 2) CHECK (payment_amount > 0) NOT NULL,
    payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    payment_method VARCHAR(50),
    status VARCHAR(50) DEFAULT 'Success' CHECK (status IN ('Success', 'Failed', 'Refunded'))
);

-- Таблица Расписание
CREATE TABLE Schedules (
    schedule_id SERIAL PRIMARY KEY,
    group_id INT NOT NULL REFERENCES Groups(group_id),
    day_of_week VARCHAR(20) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    classroom VARCHAR(50),
    UNIQUE (group_id, day_of_week, start_time) -- Занятие для группы в одно и то же время не может быть в один и тот же день
);

Формальное Доказательство Нормализации (3НФ/НФБК):

Для демонстрации соответствия структуры БД Третьей нормальной форме (3НФ) и Нормальной форме Бойса-Кодда (НФБК) необходимо выявить все функциональные зависимости (ФЗ) в каждом отношении и показать, как были устранены транзитивные зависимости и зависимости неключевых атрибутов от части составного ключа. Рассмотрим пример для нескольких ключевых таблиц.

1. Таблица Students (Студент)

• Атрибуты: student_id, full_name, date_of_birth, contact_info, passport_data
• Функциональные зависимости (ФЗ):
  • student_id → full_name, date_of_birth, contact_info, passport_data (Первичный ключ определяет все остальные атрибуты)
  • passport_data → student_id, full_name, date_of_birth, contact_info (так как passport_data UNIQUE, он также является потенциальным ключом)
• Потенциальные ключи: {student_id}, {passport_data}
• Проверка 3НФ: Все неключевые атрибуты (full_name, date_of_birth, contact_info) напрямую зависят от первичного ключа (student_id). Нет транзитивных зависимостей.
• Проверка НФБК: Все детерминанты ФЗ (student_id, passport_data) являются потенциальными ключами.
• Вывод: Таблица Students находится в НФБК.

2. Таблица Courses (Курс)

• Атрибуты: course_id, course_name, description, duration_hours, base_cost
• Функциональные зависимости (ФЗ):
  • course_id → course_name, description, duration_hours, base_cost
  • course_name → course_id, description, duration_hours, base_cost (так как course_name UNIQUE)
• Потенциальные ключи: {course_id}, {course_name}
• Проверка 3НФ/НФБК: Аналогично Students, все неключевые атрибуты зависят только от потенциальных ключей, и все детерминанты являются потенциальными ключами.
• Вывод: Таблица Courses находится в НФБК.

3. Таблица Groups (Группа)

• Атрибуты: group_id, course_id, teacher_id, start_date, end_date, max_capacity, current_enrollment
• Функциональные зависимости (ФЗ):
  • group_id → course_id, teacher_id, start_date, end_date, max_capacity, current_enrollment (Первичный ключ определяет все)
• Потенциальный ключ: {group_id}
• Проверка 3НФ: Все неключевые атрибуты напрямую зависят от group_id. Нет транзитивных зависимостей (например, course_id не зависит от teacher_id).
• Проверка НФБК: Единственный детерминант ФЗ (group_id) является потенциальным ключом.
• Вывод: Таблица Groups находится в НФБК.

4. Таблица Enrollments (ЗаписьНаКурс)

• Атрибуты: enrollment_id, contract_id, group_id, enrollment_date, status
• Первичный ключ: enrollment_id
• Ключ-кандидат: {contract_id, group_id} (так как уникален)
• Функциональные зависимости (ФЗ):
  • enrollment_id → contract_id, group_id, enrollment_date, status
  • {contract_id, group_id} → enrollment_id, enrollment_date, status
• Потенциальные ключи: {enrollment_id}, {contract_id, group_id}
• Проверка 3НФ/НФБК: Все неключевые атрибуты зависят только от потенциальных ключей, и все детерминанты являются потенциальными ключами. Нет частичных или транзитивных зависимостей.
• Вывод: Таблица Enrollments находится в НФБК.

Пример декомпозиции для устранения транзитивных зависимостей (гипотетический случай):
Предположим, у нас была бы таблица StudentEnrollments со следующими атрибутами:
{StudentID, CourseName, TeacherName, TeacherContact, EnrollmentDate}

• ФЗ:
  • StudentID → EnrollmentDate
  • CourseName → TeacherName, TeacherContact (предположим, каждый курс ведет один преподаватель)
  • {StudentID, CourseName} → TeacherName, TeacherContact, EnrollmentDate (первичный ключ)

Здесь TeacherName и TeacherContact транзитивно зависят от CourseName, который не является частью первичного ключа {StudentID, CourseName} и не является первичным ключом сам по себе. Это нарушает 3НФ.

• Декомпозиция для 3НФ:
  1. Таблица Enrollments (StudentID, CourseName, EnrollmentDate)
  2. Таблица CoursesTaughtBy (CourseName, TeacherName, TeacherContact)

Теперь CoursesTaughtBy находится в 3НФ, а Enrollments ссылается на CourseName.

Данный подход демонстрирует, как каждая таблица в спроектированной БД анализируется на предмет функциональных зависимостей и как принципы 3НФ и НФБК применяются для создания оптимальной, не избыточной и целостной реляционной структуры. А что это дает на практике? Позволяет избежать ошибок при вводе данных, что напрямую влияет на надежность и точность всей информационной системы.

Практическая Реализация Дополнительной Логики и Целостности (SQL)

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

Применение Триггеров для Обеспечения Бизнес-Правил

Триггеры — это специальные хранимые процедуры, которые автоматически выполняются при наступлении определенного события (INSERT, UPDATE, DELETE) над таблицей или представлением. Они идеально подходят для реализации бизнес-правил, которые должны быть применены немедленно после изменения данных.

Пример SQL-кода для триггера, автоматически уменьшающего количество мест в таблице Groups после успешной регистрации в Enrollments:

-- Функция-триггер для обновления количества занятых мест в группе
CREATE OR REPLACE FUNCTION update_group_enrollment_count()
RETURNS TRIGGER AS $$
BEGIN
    -- При вставке новой записи в Enrollments
    IF TG_OP = 'INSERT' THEN
        UPDATE Groups
        SET current_enrollment = current_enrollment + 1
        WHERE group_id = NEW.group_id;
        -- Проверка на переполнение группы (можно добавить здесь или в отдельном триггере/процедуре)
        -- SELECT max_capacity, current_enrollment INTO v_max_capacity, v_current_enrollment FROM Groups WHERE group_id = NEW.group_id;
        -- IF v_current_enrollment > v_max_capacity THEN
        --    RAISE EXCEPTION 'Группа % переполнена, регистрация невозможна.', NEW.group_id;
        -- END IF;
        RETURN NEW;
    -- При удалении записи из Enrollments
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE Groups
        SET current_enrollment = current_enrollment - 1
        WHERE group_id = OLD.group_id;
        RETURN OLD;
    END IF;
    RETURN NULL; -- Для других операций (UPDATE) не требуется
END;
$$ LANGUAGE plpgsql;

-- Создание триггера, который будет запускаться после INSERT или DELETE в таблице Enrollments
CREATE TRIGGER trg_update_group_enrollment
AFTER INSERT OR DELETE ON Enrollments
FOR EACH ROW
EXECUTE FUNCTION update_group_enrollment_count();

Этот триггер гарантирует, что поле current_enrollment в таблице Groups всегда будет актуальным, автоматически обновляясь при регистрации или отмене записи студента на курс. Дополнительно, триггеры могут использоваться для проверки данных перед вставкой, например, для запрета регистрации на курс, если студенту меньше 18 лет, или для более сложной валидации, не предусмотренной CHECK ограничениями.

Хранимые Процедуры для Транзакционной Логики

Хранимые процедуры (Stored Procedures) — это предварительно скомпилированные наборы SQL-операторов и логики, хранящиеся на сервере базы данных. Они используются для инкапсуляции сложной бизнес-логики, выполнения транзакций, снижения сетевого трафика и повышения безопасности, так как клиентскому приложению можно предоставить права только на выполнение процедуры, а не на прямую манипуляцию таблицами.

Пример SQL-кода для хранимой процедуры Procedures_Student_Enrollment, инкапсулирующей транзакционную логику регистрации студента на курс, включая проверку доступности мест, создание договора (если необходимо) и запись на курс:

CREATE OR REPLACE FUNCTION Procedures_Student_Enrollment(
    p_student_id INT,
    p_course_id INT,
    p_promo_code VARCHAR(50) DEFAULT NULL
)
RETURNS INT AS $$
DECLARE
    v_group_id INT;
    v_max_capacity INT;
    v_current_enrollment INT;
    v_course_cost NUMERIC(10, 2);
    v_discount_percentage NUMERIC(5, 2) DEFAULT 0;
    v_contract_id INT;
    v_total_amount NUMERIC(10, 2);
    v_discount_amount NUMERIC(10, 2);
    v_final_amount NUMERIC(10, 2);
BEGIN
    -- 1. Выбор свободной группы для курса
    SELECT group_id, max_capacity, current_enrollment, c.base_cost
    INTO v_group_id, v_max_capacity, v_current_enrollment, v_course_cost
    FROM Groups g
    JOIN Courses c ON g.course_id = c.course_id
    WHERE g.course_id = p_course_id
      AND g.current_enrollment < g.max_capacity
    ORDER BY g.start_date ASC -- Выбираем группу, которая начинается раньше
    LIMIT 1;

    IF v_group_id IS NULL THEN
        RAISE EXCEPTION 'Нет свободных мест в группах для курса ID: %', p_course_id;
    END IF;

    -- 2. Проверка промо-кода и расчет скидки
    IF p_promo_code IS NOT NULL THEN
        SELECT discount_percentage
        INTO v_discount_percentage
        FROM PromoCodes
        WHERE promo_code = p_promo_code AND is_active = TRUE AND NOW() BETWEEN start_date AND end_date;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Недействительный или истекший промо-код: %', p_promo_code;
        END IF;
    END IF;

    -- 3. Расчет суммы договора
    v_total_amount := v_course_cost;
    v_discount_amount := v_total_amount * (v_discount_percentage / 100);
    v_final_amount := v_total_amount - v_discount_amount;

    -- 4. Создание нового договора
    INSERT INTO Contracts (student_id, total_amount, discount_amount, final_amount, promo_code)
    VALUES (p_student_id, v_total_amount, v_discount_amount, v_final_amount, p_promo_code)
    RETURNING contract_id INTO v_contract_id;

    -- 5. Запись студента на курс
    INSERT INTO Enrollments (contract_id, group_id, status)
    VALUES (v_contract_id, v_group_id, 'Pending Payment');

    -- Триггер trg_update_group_enrollment автоматически обновит current_enrollment в таблице Groups

    RETURN v_contract_id; -- Возвращаем ID созданного договора
END;
$$ LANGUAGE plpgsql;

Эта процедура выполняет следующие действия атомарно (в рамках одной транзакции, хотя явный BEGIN/COMMIT не показан, PostgreSQL функции выполняются в транзакции по умолчанию):

• Выбирает группу с доступными местами для указанного курса.
• Проверяет и применяет промо-код, рассчитывая скидку.
• Создает новый договор для студента с рассчитанной стоимостью.
• Добавляет запись студента на курс в выбранную группу.
• Автоматически обновляет количество занятых мест в группе благодаря ранее созданному триггеру.

Использование Представлений (Views) для Отчетности и Доступа

Представления (Views) — это виртуальные таблицы, которые не хранят данные самостоятельно, но представляют собой результат SQL-запроса. Они используются для упрощения сложных запросов, разграничения доступа к данным (показывая только релевантные поля или строки) и создания пользовательских агрегированных отчетов.

Пример представления для получения полной информации о студенте, его курсе, преподавателе и статусе оплаты:

CREATE OR REPLACE VIEW View_Students_Courses_and_Payments AS
SELECT
    s.student_id,
    s.full_name AS student_name,
    s.contact_info AS student_contact,
    c.contract_id,
    cn.course_name,
    cn.description AS course_description,
    g.start_date AS group_start_date,
    g.end_date AS group_end_date,
    t.full_name AS teacher_name,
    e.enrollment_date,
    e.status AS enrollment_status,
    p.payment_amount,
    p.payment_date,
    p.status AS payment_status,
    contr.final_amount AS contract_final_amount
FROM Students s
JOIN Contracts contr ON s.student_id = contr.student_id
JOIN Enrollments e ON contr.contract_id = e.contract_id
JOIN Groups g ON e.group_id = g.group_id
JOIN Courses cn ON g.course_id = cn.course_id
JOIN Teachers t ON g.teacher_id = t.teacher_id
LEFT JOIN Payments p ON contr.contract_id = p.contract_id;

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

Обеспечение Информационной Безопасности и Соответствие Нормативным Требованиям

Информационная безопасность является неотъемлемой частью проектирования любой современной ИС, особенно той, что обрабатывает персональные данные. Для ИС «Компьютерные курсы» это означает не только техническую реализацию механизмов защиты, но и строгое соответствие российскому законодательству, в частности ФЗ-152.

Идентификация, Аутентификация и Авторизация в СУБД

Основа безопасности доступа к данным в СУБД строится на трех ключевых концепциях:

• Идентификация — это процесс, при котором пользователь или система заявляет о своей личности (например, путем ввода логина или имени пользователя).
• Аутентификация — это процесс проверки подлинности заявленного идентификатора. Обычно это достигается путем предоставления секрета (пароля, биометрических данных, токена), который только заявленный пользователь знает или имеет.
• Авторизация — это процесс предоставления прав доступа (привилегий) идентифицированному и аутентифицированному пользователю к определенным объектам и функциям в СУБД (таблицам, представлениям, хранимым процедурам, триггерам).

В PostgreSQL эти механизмы реализуются через систему ролей и пользователей.

Реализация ролевой модели в PostgreSQL:

-- 1. Создание ролей
-- Роль для администраторов системы (полный доступ к БД)
CREATE ROLE admin_role WITH
    LOGIN
    PASSWORD 'secure_admin_password';

-- Роль для преподавателей (доступ к своим группам и расписанию)
CREATE ROLE teacher_role WITH
    LOGIN
    PASSWORD 'secure_teacher_password';

-- Роль для студентов (доступ к своим данным, курсам и регистрации)
CREATE ROLE student_role WITH
    LOGIN
    PASSWORD 'secure_student_password';

-- 2. Назначение привилегий ролям

-- Для admin_role: полный доступ
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin_role;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO admin_role;
-- При необходимости можно ограничить схему или определенные объекты

-- Для teacher_role:
GRANT SELECT ON Teachers, Courses, Groups, Schedules TO teacher_role;
-- Возможность обновлять расписание своих групп
GRANT UPDATE ON Schedules TO teacher_role WHERE group_id IN (SELECT group_id FROM Groups WHERE teacher_id = CURRENT_USER); -- Более сложная логика, часто через представления или хранимые процедуры
-- Или проще, если преподавать будут иметь доступ к своей записи через JOIN
GRANT SELECT ON View_Teachers_Courses_Groups TO teacher_role; -- Если такое представление создано
GRANT EXECUTE ON FUNCTION Procedures_Update_Schedule_By_Teacher(INT, VARCHAR, TIME, TIME, VARCHAR) TO teacher_role; -- Если есть процедура для обновления расписания

-- Для student_role:
GRANT SELECT ON Students, Courses, Groups TO student_role; -- Просмотр своих данных, доступных курсов и групп
GRANT SELECT ON View_Students_Courses_and_Payments TO student_role; -- Доступ к своим записям и оплатам
GRANT EXECUTE ON FUNCTION Procedures_Student_Enrollment(INT, INT, VARCHAR) TO student_role; -- Регистрация на курс
-- Запрет на прямой SELECT из таблиц, содержащих ПД других студентов
REVOKE SELECT ON Students FROM student_role; -- Если требуется строгая изоляция, доступ через представления

ГОСТ Р 58833-2020 «Защита информации. Идентификация и аутентификация. Общие положения» устанавливает общие положения по идентификации и аутентификации в системах защиты информации. При реализации механизмов аутентификации в БД необходимо следовать этим принципам, включая использование стойких алгоритмов хеширования паролей, механизм смены паролей, блокировку учетных записей при многократных неуспешных попытках входа и т.д.

Соответствие Требованиям Защиты ПД (ФЗ-152)

Защита персональных данных (ПД) является одним из наиболее критичных аспектов при проектировании ИС в Российской Федерации. Она регулируется Федеральным законом № 152-ФЗ «О персональных данных» и рядом подзаконных актов, среди которых ключевым является Постановление Правительства РФ № 1119 «Об утверждении требований к защите персональных данных при их обработке в информационных системах персональных данных» и Приказ ФСТЭК России № 21 «Об утверждении Состава и содержания организационных и технических мер по обеспечению безопасности персональных данных при их обработке в информационных системах персональных данных».

Обоснование необходимости защиты ПД в ИС «Компьютерные курсы»:
ИС «Компьютерные курсы» обрабатывает персональные данные студентов (ФИО, дата рождения, контакты, паспортные данные) и преподавателей. Согласно ФЗ-152, такая система является информационной системой персональных данных (ИСПДн) и требует соответствующего уровня защиты.

Определение Уровня Защищенности (УЗ):
Согласно ПП № 1119, необходимый Уровень Защищенности (УЗ-1, УЗ-2, УЗ-3 или УЗ-4) определяется на основе трех критериев:

1. Категория обрабатываемых ПДн: ИС «Компьютерные курсы» обрабатывает иные категории персональных данных (не специальные, не биометрические, не общедоступные).
2. Количество субъектов ПДн: Предположим, что число субъектов (студентов, преподавателей) менее 100 000 человек.
3. Тип актуальных угроз: Для образовательных организаций, как правило, определяются Угрозы 3-го типа (угрозы не связаны с наличием недекларированных возможностей в системном ПО, используются стандартные СУБД).

На основе этих критериев для ИС «Компьютерные курсы» должен быть установлен Уровень Защищенности 3 (УЗ-3).

Минимальный перечень технических и организационных мер (согласно Приказу ФСТЭК России № 21 для УЗ-3):
Для УЗ-3 ИСПДн необходимо реализовать следующий перечень базовых мер защиты:

• Идентификация и аутентификация субъектов доступа и объектов доступа: Использование логинов и паролей, соответствие ГОСТ Р 58833-2020.
• Управление доступом субъектов доступа к объектам доступа: Разграничение прав доступа на основе ролевой модели (как показано выше), принцип наименьших привилегий.
• Ограничение доступа к информации: Запрет несанкционированного доступа к ПД других студентов или к административным данным.
• Регистрация событий безопасности: Ведение журналов регистрации входа/выхода из системы, попыток доступа к ПД, изменения прав доступа.
• Контроль целостности и доступности информации: Использование механизмов резервного копирования и восстановления данных.
• Защита среды виртуализации: Если СУБД развернута в виртуальной среде, необходимо обеспечить защиту виртуальных машин.
• Антивирусная защита: Установка и регулярное обновление антивирусного ПО на сервере БД.
• Обнаружение вторжений (предотвращение атак): Использование систем обнаружения/предотвращения вторжений.
• Управление конфигурацией: Контроль за изменениями в конфигурации СУБД и ОС.

Механизм резервного копирования и восстановления данных (Backup & Restore):
Является фундаментальным элементом обеспечения доступности и целостности данных. PostgreSQL предоставляет встроенные инструменты для резервного копирования (например, pg_dump для логического бэкапа и pg_basebackup для физического). Необходимо настроить регулярное автоматическое резервное копирование и разработать план восстановления данных на случай сбоев.

Пример команды для логического бэкапа:

pg_dump -U admin_role -F c -b -v -f /path/to/backup/db_courses_$(date +%Y%m%d%H%M%S).backup db_courses

Этот комплекс мер, включающий техническую реализацию в СУБД и организационные политики, гарантирует соответствие ИС «Компьютерные курсы» требованиям российского законодательства и обеспечивает высокий уровень защиты персональных данных. Но достаточно ли этого? Ведь даже самые совершенные технические средства бессильны без осознанного подхода к безопасности со стороны пользователей и администраторов.

Заключение

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

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

Путем глубокого сравнительного анализа популярных реляционных СУБД, учитывая не только технические характеристики, но и актуальные региональные тренды (в частности, растущую популярность PostgreSQL в РФ), был обоснован выбор PostgreSQL как оптимального решения для данного проекта. Мы продемонстрировали даталогическую структуру БД с помощью DDL-кода и, что критически важно, формально доказали соответствие всех отношений Третьей нормальной форме и Нормальной форме Бойса-Кодда, выявив функциональные зависимости и продемонстрировав принципы декомпозиции.

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

• Триггеры были применены для автоматического управления количеством мест в группах, обеспечивая ссылочную целостность.
• Хранимые процедуры инкапсулировали комплексную транзакционную логику регистрации студента на курс, повышая эффективность и безопасность.
• Представления были использованы для упрощения доступа к данным и формирования отчетов, демонстрируя возможности разграничения доступа.

Особое внимание было уделено вопросам информационной безопасности. Мы не только описали механизмы идентификации, аутентификации и авторизации в PostgreSQL с использованием ролевой модели и ссылками на ГОСТ Р 58833-2020, но и провели глубокий анализ соответствия требованиям Федерального закона № 152-ФЗ «О персональных данных». Было формально определено, что для ИС «Компьютерные курсы» необходим Уровень Защищенности 3 (УЗ-3), и описан минимальный перечень технических и организационных мер в соответствии с Постановлением Правительства РФ № 1119 и Приказом ФСТЭК России № 21.

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

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

Список использованной литературы

1. ГОСТ 2.105-95 Единая система конструкторской документации. Общие требования к тестовым документам.
2. ГОСТ 2.106-95 Единая система конструкторской документации. Текстовые документы.
3. ГОСТ 2.301-68. Единая система конструкторской документации. Форматы.
4. ГОСТ Р 58833-2020 Защита информации. Идентификация и аутентификация. Общие положения.
5. Аткинсон Л. MySQL. Библиотека профессионала. М.: Вильямс, 2008. 624 c.
6. Грофф Дж., Вайнберг П. SQL: полное руководство. Киев: BHV, 2005. 608 c.
7. Нанда А. и др. Oracle PL/SQL для администраторов баз данных. Символ, 2008. 496 c.
8. Стоунз Р., Мэттью Н. PostgreSQL. Основы. СПб: Символ-Плюс, 2007. 640 c.
9. Фейерштейн С., Прибыл Б. Oracle PL/SQL для профессионалов. СПб: Питер, 2005. 941 c.
10. Шнайдер Р. Microsoft SQL Server 6.5. Проектирование высокопроизводительных баз данных. М.: Лори, 2010. 361 c.
11. Яргер Р.Дж., Риз Дж., Кинг Т. MySQL и mSQL: Базы данных для небольших предприятий и Интернета. СПб: Символ-Плюс, 2013. 560 c.
12. Сравнение MySQL и PostgreSQL в 2023 году. URL: https://habr.com/ru/companies/selectel/articles/731302/ (дата обращения: 07.10.2025).
13. Как выбрать систему управления базами данных: сравнение лучших СУБД. URL: https://webhost1.ru/baza-znaniy/chto-takoe-subd (дата обращения: 07.10.2025).
14. Описание нормализации базы данных. URL: https://learn.microsoft.com/ru-ru/office/troubleshoot/access/database-normalization-description (дата обращения: 07.10.2025).
15. Понятие «Нормальные формы баз данных» и примеры. URL: https://habr.com/ru/articles/734326/ (дата обращения: 07.10.2025).
16. Привилегии в SQL. URL: https://otus.ru/nest/post/1944/ (дата обращения: 07.10.2025).
17. Третья нормальная форма. URL: https://ru.wikipedia.org/wiki/%D0%A2%D1%80%D0%B8%D0%B3%D0%B3%D0%B5%D1%80_(%D0%B1%D0%B0%D0%B7%D1%8B_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85) (дата обращения: 07.10.2025).
18. Что такое нормализация базы данных? URL: https://merionet.ru/chto-takoe-normalizatsiya-bazy-dannykh/ (дата обращения: 07.10.2025).