Дисциплина: Базы данных
Автор: Минеева Т. А.
Модуль 2: Разработка внутренней структуры базы данных Тема 1: Создание моделиПродуктивное учебное задание № 2
«Разработка реляционной модели данных. Разработка запросов»
*1) Разработать реляционную модель данных по своей проблемной области:* a) Разработать таблицы и поля в них. b) Использовать типы связей 1:1, 1:М. c) Модель проверить на нормальные формы. *2) Разработать запросы по анализу и обработке данных:* a) Сделать запросы для каждого вида: выборка, создание таблицы, групповой, перекрестный, обновление, удаление. b) Описать условия отбора записей в каждом запросе. c) Оценить правильность выполнения запросов, т. е. для каждого запроса должно быть сформулировано, что будет получено в результате его выполнения. *Для выполнения задания рекомендуется использовать источники:* 1. *Гарсиа-Молина Г., Ульман Дж.Д., Уидом Дж.* Системы баз данных. Полный курс: Пер. с англ. М.: Изд. дом «Вильямс», 2004. 1088 с. Гл. 2, 3. С. 51–149, 683–753. 2. *Ульман Д., Уидом Д.* Введение в системы баз данных. М.: Лори, 2000. 376 с. Гл. 3. С. 67–139. 3. *Бекаревич Ю. Б., Пушкина Н. В.* Самоучитель MS ACCESS 2002. СПб.: BHV-Петербург, 2002. 720 с. Гл. 4. С. 53–71. 4. *Грофф Дж., Вайнберг П.* SQL: Полное руководство: Пер. с англ. 2-е изд., перераб. и доп. Киев: Изд. Группа BHV, 2001. 816 с. Гл. 2, 3. С. 63–156, Гл. 4, 5, 8. С. 156–237, 334–350. Полное и правильное выполнение данного продуктивного задания оценивается в 10 баллов. *Критерии оценки:* 1\. Наличие пяти и более таблиц, в каждой количество полей ≥ 3. 2\. Наличие типов связей: 1:1, 1:М. 3\. Проверка модели на нормальные формы. 4\. Наличие двух запросов для каждого вида: выборка, создание таблицы, групповой, перекрестный, обновление, удаление. 5\. Высокая степень подробности указанных в каждом запросе условий отбора записей. 6\. Правильность выполнения запросов. *** {% hint style="info" %} Трейден Полина Витальевна, 2й курс, Прикладная информатика, заочное ДО {% endhint %} ### 1. Предметная область В качестве предметной области в работе рассматривается **информационная система Telegram-бота онлайн-курса**, предназначенная для учёта пользователей, оплаты доступа, уроков, прогресса обучения, домашних заданий, вопросов, отзывов и записи на занятия.\ Реализация базы данных выполнена с использованием **СУБД SQLite**. *** ### 2. Реляционная модель данных База данных онлайн-курса реализована в виде реляционной модели и включает следующие основные таблицы: * `users` — пользователи курса; * `lessons` — уроки курса; * `user_lessons` — прогресс пользователей по урокам; * `questions` — вопросы пользователей; * `homeworks` — домашние задания; * `reviews` — отзывы; * `slots` — слоты для записи на занятия. *** ### 3. Описание таблиц #### Таблица `users` **Назначение** Таблица `users` предназначена для хранения информации о пользователях онлайн-курса и используется как основная таблица для идентификации участников учебного процесса и управления доступом к функционалу Telegram-бота. **Основные поля** * `user_id` — первичный ключ, уникальный идентификатор пользователя; * `full_name` — имя пользователя; * `username` — имя пользователя в Telegram; * `paid` — признак оплаты курса (0 — не оплачен, 1 — оплачен); * `vpn_date`, `vpn_uuid`, `vpn_name` — данные, связанные с предоставлением сервисного доступа; * `timestamp` — дата и время регистрации пользователя. **Особенности предметной области** * Таблица `users` является **главной** для большинства связей в БД. * Один пользователь может иметь несколько вопросов, домашних заданий, отзывов и записей на занятия. * Статус `paid` используется для контроля доступа к материалам курса. * Идентификатор `user_id` используется для связывания данных во всех зависимых таблицах. *** #### Таблица `lessons` **Назначение** Таблица `lessons` предназначена для хранения информации об учебных уроках онлайн-курса и используется для организации учебного контента. **Основные поля** * `id` — первичный ключ, уникальный идентификатор урока; * `title` — название урока; * `description` — описание содержания урока. **Особенности предметной области** * Каждый урок является самостоятельной учебной единицей курса. * Урок может быть связан с несколькими пользователями через таблицу прогресса. * Таблица используется при формировании расписания, прогресса и отчётов о прохождении курса. *** #### Таблица `user_lessons` **Назначение** Таблица `user_lessons` предназначена для хранения информации о прохождении уроков пользователями и используется для учёта прогресса обучения. **Основные поля** * `user_id` — идентификатор пользователя; * `lesson_number` — идентификатор или номер урока; * `completed` — признак завершения урока (0 — не завершён, 1 — завершён); * `timestamp` — дата и время фиксации результата. **Особенности предметной области** * Таблица реализует связь **многие-ко-многим** между пользователями и уроками. * Используется составной первичный ключ (`user_id`, `lesson_number`). * Таблица является основой для расчёта прогресса обучения и формирования аналитических отчётов. * Исключает дублирование информации о прохождении уроков. *** #### Таблица `questions` **Назначение** Таблица `questions` предназначена для хранения вопросов пользователей онлайн-курса и используется для организации обратной связи. **Основные поля** * `id` — первичный ключ; * `user_id` — идентификатор пользователя, задавшего вопрос; * `question_text` — текст вопроса; * `answered` — признак обработки вопроса (0 — не обработан, 1 — обработан); * `timestamp` — дата и время создания вопроса. **Особенности предметной области** * Один пользователь может задать несколько вопросов. * Таблица используется для контроля поддержки пользователей. * Поле `answered` позволяет формировать списки необработанных обращений. *** #### Таблица `homeworks` **Назначение** Таблица `homeworks` предназначена для хранения информации о домашних заданиях пользователей и используется для контроля выполнения учебных заданий. **Основные поля** * `id` — первичный ключ; * `user_id` — идентификатор пользователя; * `lesson_number` — номер урока; * `homework_text` — содержание выполненного задания; * `completed` — признак выполнения задания; * `timestamp` — дата и время отправки задания. **Особенности предметной области** * Каждое задание связано с конкретным уроком и пользователем. * Таблица используется для учёта учебной активности. * Флаг `completed` применяется для автоматического анализа прогресса. *** #### Таблица `reviews` **Назначение** Таблица `reviews` предназначена для хранения отзывов пользователей об онлайн-курсе и используется для анализа качества обучения. **Основные поля** * `id` — первичный ключ; * `user_id` — идентификатор пользователя; * `review_text` — текст отзыва; * `rating` — оценка курса (при наличии); * `timestamp` — дата и время добавления отзыва. **Особенности предметной области** * Каждый отзыв принадлежит одному пользователю. * Используется для анализа удовлетворённости обучающихся. * Данные таблицы применяются при принятии решений по улучшению курса. *** #### Таблица `slots` **Назначение** Таблица `slots` предназначена для хранения информации о временных слотах для записи пользователей на занятия или консультации. **Основные поля** * `id` — первичный ключ; * `date` — дата проведения занятия; * `time` — время проведения занятия; * `booked_by` — идентификатор пользователя, забронировавшего слот; * `reminded` — признак отправки напоминания пользователю. **Особенности предметной области** * Один слот может быть забронирован **только одним пользователем**. * Поле `booked_by` логически связано с таблицей `users`. * Таблица используется для управления расписанием и предотвращения конфликтов бронирования. *** #### 4. Типы связей В базе данных онлайн-курса используются следующие типы связей: * **1:М** — между пользователями и вопросами, домашними заданиями, отзывами, слотами; * **М:М** — между пользователями и уроками, реализованная через таблицу `user_lessons`. ```mermaid erDiagram USERS { int user_id PK string full_name string username boolean paid date vpn_date string vpn_uuid datetime timestamp } LESSONS { int id PK string title string description } USER_LESSONS { int user_id FK int lesson_id FK boolean completed datetime timestamp } HOMEWORKS { int id PK int user_id FK int lesson_id FK text homework_text boolean completed datetime timestamp } SLOTS { int id PK date date time time int user_id FK boolean reminded } QUESTIONS { int id PK int user_id FK text question_text boolean answered datetime timestamp } REVIEWS { int id PK int user_id FK text review_text int rating datetime timestamp } USERS ||--o{ HOMEWORKS : "1:M" USERS ||--o{ QUESTIONS : "1:M" USERS ||--o{ REVIEWS : "1:M" USERS ||--o{ SLOTS : "1:M (booked_by)" USERS ||--o{ USER_LESSONS : "1:M" LESSONS ||--o{ USER_LESSONS : "1:M" %% Users <-> Lessons = M:N via USER_LESSONS ``` *** #### 5. Нормализация данных * **Первая нормальная форма (1НФ):**\ все таблицы содержат атомарные значения, повторяющиеся группы отсутствуют. * **Вторая нормальная форма (2НФ):**\ в таблице `user_lessons` все неключевые атрибуты функционально зависят от полного составного ключа. * **Третья нормальная форма (3НФ):**\ в базе данных отсутствуют транзитивные зависимости; данные разделены по сущностям предметной области. *** #### 6. Запросы к базе данных **6.1. Запросы на выборку (SELECT)** **Запрос 1:** выбор пользователей с оплаченным доступом ```sql SELECT * FROM users WHERE paid = 1; ``` **Результат:** список пользователей, оплативших курс. **Запрос 2:** выбор свободных слотов ```sql SELECT * FROM slots WHERE booked_by IS NULL; ``` **Результат:** список доступных для записи слотов. *** **6.2. Запросы создания таблиц** ```sql CREATE TABLE paid_users AS SELECT user_id, full_name FROM users WHERE paid = 1; ``` **Результат:** создана таблица пользователей с оплаченным доступом. *** **6.3. Групповые запросы** ```sql SELECT user_id, COUNT(*) AS questions_count FROM questions GROUP BY user_id; ``` **Результат:** количество вопросов по каждому пользователю. *** **6.4. Перекрёстные запросы** ```sql SELECT user_id, SUM(CASE WHEN completed = 1 THEN 1 ELSE 0 END) AS completed_lessons FROM user_lessons GROUP BY user_id; ``` **Результат:** количество завершённых уроков по каждому пользователю. *** **6.5. Запросы обновления** ```sql UPDATE users SET paid = 1 WHERE user_id = 123; ``` **Результат:** пользователь отмечен как оплативший курс. *** **6.6. Запросы удаления** ```sql DELETE FROM slots WHERE booked_by IS NULL AND reminded = 0; ``` **Результат:** удалены неиспользуемые слоты без напоминаний. *** #### 7. Особенности использования SQLite Внешние ключи в базе данных используются **логически** и обеспечиваются на уровне запросов и прикладной логики Telegram-бота. *** #### 8. Заключение Реляционная модель базы данных онлайн-курса обеспечивает структурированное хранение данных, логическую целостность и возможность эффективной обработки информации о пользователях, уроках и учебном процессе. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://letas-organization.gitbook.io/letaats-lessons-online/untitled-1/bazy-dannykh/produktivnoe-zadanie-2-k-modulyu-2.md?ask=