Домашняя контрольная работа «Проектирование и разработка базы данных для интернет-магазина»

1. Цель домашней контрольной работы:

1.1. формирование практических навыков полного цикла проектирования и разработки базы данных для интернет-магазина;

1.2. познакомиться с методами формализации требований;

1.3. созданием ER-диаграмм, нормализацией;

1.4. реализовать физическую модель в виде SQL-скриптов и проведут тестирование основных операций с данными.

2. Теоретический материал:

Проектирование базы данных начинается с анализа предметной области и сбора требований к будущей системе. На этом этапе важно выделить основные сущности (например, пользователи, товары, заказы), определить связи между ними и сформулировать бизнес-правила. Построение ER-диаграммы (диаграммы «сущность-связь») позволяет визуализировать структуру данных и отношения между сущностями. После этого выполняется нормализация данных для устранения избыточности и обеспечения целостности. Физическая реализация базы данных предполагает создание таблиц, определение связей (внешних ключей), индексов, а также написание SQL-скриптов для основных операций (создание, изменение, удаление, выборка данных). В современных интернет-магазинах важно также учитывать вопросы безопасности данных, резервного копирования и производительности при проектировании базы данных.

3. Ход работы:

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

3.2. Следующим шагом постройте ER-диаграмму, отражающую связи между сущностями, такие как «один пользователь — много заказов», «один заказ — много товаров» (через таблицу-связку), «одна категория — много товаров». Укажите типы связей и кратко опишите их логику.

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

3.4.  На этапе реализации физической модели создайте SQL-скрипты для создания таблиц, определения первичных и внешних ключей, настройки ограничений целостности и индексов. Для проверки структуры, заполните таблицы тестовыми данными — не менее 10 записей для каждой.

SQL

CREATE TABLE Users (

    user_id INT PRIMARY KEY AUTO_INCREMENT,

    username VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE,

    registration_date DATE NOT NULL

);

CREATE TABLE Orders (

    order_id INT PRIMARY KEY AUTO_INCREMENT,

    user_id INT NOT NULL,

    order_date DATE NOT NULL,

    status VARCHAR(20),

    FOREIGN KEY (user_id) REFERENCES Users(user_id)

);

3.5. Разработайте не менее пяти SQL-запросов, охватывающих основные бизнес-операции интернет-магазина. Например, выбор всех заказов определённого пользователя, получение списка товаров определённой категории, изменение статуса заказа, удаление товара, подсчёт количества товаров на складе и т.д.

SQL

SELECT Orders.order_id, Orders.order_date, Orders.status

FROM Orders

JOIN Users ON Orders.user_id = Users.user_id

WHERE Users.username = 'ivanov';

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

SQL

CREATE TRIGGER payment_after_order_status_update

AFTER UPDATE ON Orders

FOR EACH ROW

BEGIN

  IF NEW.status = 'оплачен' THEN

    INSERT INTO Payments (order_id, payment_date)

    VALUES (NEW.order_id, NOW());

  END IF;

END;

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

4. Задание для самостоятельного выполнения:

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

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

Создайте физическую модель базы данных с помощью SQL-скриптов, применяя оптимальные типы данных и ограничения целостности. Для повышения производительности реализуйте составные индексы и не менее двух представлений (views) для часто используемых аналитических запросов, например, текущие остатки по зонам и динамика движения товаров по месяцам. Заполните таблицы тестовыми данными — не менее 20 записей в каждую.

Разработайте не менее восьми сложных SQL-запросов, обязательно включающих вложенные подзапросы, агрегирующие функции, оконные функции и объединения нескольких таблиц. Реализуйте не менее двух хранимых процедур, например, перемещение партии товаров с автоматическим пересчётом остатков и записью в журнал аудита, массовую инвентаризацию по заданному критерию. Дополнительно реализуйте два триггера: например, автоматическое создание записи о возврате при поступлении рекламации и логирование всех удалённых записей о перемещениях.

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

5. Полезные SQL-запросы:

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

SQL

SELECT product_id, zone_id, SUM(quantity) AS total_quantity

FROM StockMovements

GROUP BY product_id, zone_id;

Для анализа динамики движения товаров за определённый период времени удобно использовать оконные функции: 

SQL

SELECT product_id, movement_date, quantity,

       SUM(quantity) OVER (PARTITION BY product_id ORDER BY movement_date) AS running_total

FROM StockMovements

WHERE movement_date BETWEEN '2024-01-01' AND '2024-06-30';

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

SQL

SELECT product_id, (SELECT product_name FROM Products WHERE Products.product_id = Shipments.product_id) AS product_name,

       COUNT(*) AS shipment_count

FROM Shipments

GROUP BY product_id

ORDER BY shipment_count DESC

LIMIT 10;

А для поиска всех клиентов, у которых есть невыполненные возвраты, используйте объединение таблиц с условием: 

SQL

SELECT DISTINCT Clients.client_id, Clients.client_name

FROM Returns

JOIN Clients ON Returns.client_id = Clients.client_id

WHERE Returns.status = 'pending';

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

6. Содержание отчёта:

6.1. Титул

6.2. Цель работы

6.3. Выполнение задания

6.4. Ответы на контрольные вопросы

7. Контрольные вопросы:

7.1. Какие этапы включает проектирование базы данных и какова последовательность их выполнения?

7.2. В чём заключается назначение ER-диаграммы и как она помогает при проектировании БД?

7.3. Что такое нормализация данных и какие задачи она решает?

7.4. Как реализуются связи «один-ко-многим» и «многие-ко-многим» в реляционных базах данных?

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

7.6. Каковы основные преимущества использования хранимых процедур и триггеров в бизнес-логике базы данных?

7.7. Какие меры необходимо предусмотреть для обеспечения целостности и безопасности данных в интернет-магазине?

7.8. В чём заключается отличие между логической и физической моделями базы данных?

7.9. Как осуществляется тестирование корректности работы базы данных и выполняемых запросов?

7.10. Какие перспективы развития или масштабирования следует учитывать при проектировании базы данных для интернет-магазина?