Загрузка данных

Государственное профессиональное образовательное учреждение
«Сибирский политехнический техникум»






ОТЧЁТ ПО БАЗЕ ДАННЫХ

«Кофейня (CoffeeShopDB)»





Работу выполнил(а): 
Прокошин Андрей Вячеславович,
Группа ПР-24
Руководитель:
ФИО,
Редько Алексей Дмитриевич






Кемерово, 2026
 





























 
ВВЕДЕНИЕ
Цель работы: закрепление навыков проектирования реляционных баз данных и написания SQL-запросов.
Выбранный вариант: Вариант 11 – Кофейня (CoffeeShopDB).
Используемые инструменты: PostreSQL 18, pgAdmin.
Краткое описание предметной области:
База данных автоматизирует работу кофейни. В системе хранятся сведения о сотрудниках (бариста), меню заведения (напитки, десерты, выпечка, еда), заказах клиентов и детальном составе каждого заказа. Система позволяет отслеживать выручку по сотрудникам, анализировать популярность позиций меню и управлять их доступностью.
 
ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ
ER-Диаграмма: 

Связи:
Таблица 1	Таблица 2	Тип связи	Реализация
baristas	orders	1 : N	Внешний ключ barista_id в таблице orders
orders	order_details	1 : N	Внешний ключ order_id в таблице order_details
menu_items	order_details	1 : N	Внешний ключ menu_item_id в таблице order_details

Описание таблиц:
Таблица baristas – сотрудники кофейни
Поле	Тип	Ограничение	Описание
id	SERIAL	PRIMARY KEY	Уникальный идентификатор
full_name	VARCHAR (150)	NOT NULL	ФИО бариста
hire_date	DATE	NOT NULL	Дата приёма на работу
is_active	BOOALEN	NOT NULL	Работает ли в данный момент

Таблица menu_items – позиции меню
Поле	Тип	Ограничение	Описание
id	SERIAL	PRIMARY KEY	Уникальный идентификатор
name	VARCHAR (150)	NOT NULL	Название
позиции
category	VARCHAR (300)	NOT NULL	Категория (Кофе, Чай, Десерты и т.д.)
price	DECIMAL (10,2)	NOT NULL	Цена в рублях
is_available	BOOALEN	NOT NULL	Доступна ли к заказу


Таблица orders – заказы
Поле	Тип	Ограничение	Описание
id	SERIAL	PRIMARY KEY	Уникальный идентификатор
barista_id	INT	FOREIGN KEY	Ссылка на бариста, принявшего заказ
order_time	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Время оформления заказа
total_amount	DECIMAL (10,2)	NOT NULL	Общая сумма заказа
payment_method	VARCHAR (50)	NOT NULL	Способ оплаты

Нормализация:
1NF – в каждой ячейке одно значение, нет списков
2NF – всё зависит от полного ключа 
3NF – нет зависимостей между полями
 
РЕАЛИЗАЦИЯ
Создание структуры (DDL):
CREATE TABLE baristas (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    hire_date DATE NOT NULL,
    is_active BOOLEAN NOT NULL
);

CREATE TABLE menu_items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(300) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    is_available BOOLEAN NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    barista_id INT NOT NULL,
    FOREIGN KEY (barista_id) REFERENCES baristas(id),
    order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL
);

CREATE TABLE order_details (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    menu_item_id INT NOT NULL,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id),
    quantity INT NOT NULL,
    note TEXT
);

CREATE INDEX idx_orders_barista_id ON orders(barista_id);
CREATE INDEX idx_order_details_order_id ON order_details(order_id);
CREATE INDEX idx_order_details_menu_item_id ON order_details(menu_item_id);

Наполнение тестовыми данными:

Объём данных:
· baristas – 15 записей
· menu_items – 31 запись 
· orders – 61 запись 
· order_details – 87 записей

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

Пример вставки данных:
Добавление бариста:
INSERT INTO baristas (full_name, hire_date, is_active)
VALUES ('Иванов Александр', '2023-01-15', TRUE);

Добавление позиции меню:
INSERT INTO menu_items (name, category, price, is_available)
VALUES ('Эспрессо', 'Кофе', 150.00, TRUE);

Создание заказа:
INSERT INTO orders (barista_id, order_time, total_amount, payment_method)
VALUES (1, '2024-03-01 08:15:00', 480.00, 'Наличные');

Добавление состава заказа:
INSERT INTO order_details (order_id, menu_item_id, quantity, note)
VALUES (1, 1, 2, 'Без сахара');
 
ВЫПОЛНЕНИЕ ЗАДАНИЙ
Задание 1: добавить новый напиток в меню и создать заказ с этим напитком
Цель: добавить в меню позицию «Фраппучино» и оформить заказ с этим напитком.
SQL-запрос:
INSERT INTO menu_items (name, category, price, is_available) VALUES 
('Фраппучино', 'Кофе', 320.00, TRUE);

INSERT INTO orders (barista_id, order_time, total_amount, payment_method) VALUES
(3, NOW(), 320.00, 'Карта');

INSERT INTO order_details(order_id, menu_item_id, quantity, note) VALUES 
(62, 31, 1, 'Со льдом');
Пояснение логики: использован оператор INSERT INTO…VALUES для добавления строк. Функция NOW() автоматически подставляет текущие дату и время.
 
Проверка: добавлено по 1 строке в каждую таблицу.


Задание 2: вывести все заказы за сегодня с именами бариста, которые их приняли
Цель: получить список всех заказов за сегодня с именами бариста.
SQL-запрос:
SELECT
    o.id AS order_id,
    o.order_time,
    o.total_amount,
    o.payment_method,
    b.full_name
FROM orders o
JOIN baristas b ON o.barista_id = b.id
WHERE DATE(o.order_time) = CURRENT_DATE;
Пояснение логики: 
INNER JOIN соединяет таблицы по внешнему ключу. Функция DATE() выделяет дату из TIMESTAMP, CURRENT_DATE возвращает сегодняшнюю дату.
 
Проверка: выводятся только заказы за текущий день.

Задание 3: посчитать выручку по каждому бариста за неделю
Цель: подсчитать суммарную выручку по каждому бариста за последние 7 дней.
SQL-запрос:
SELECT
    b.id,
    b.full_name,
    SUM(o.total_amount) AS weekly_revenue
FROM orders o
JOIN baristas b ON o.barista_id = b.id
WHERE o.order_time >= NOW() - INTERVAL '7 days'
GROUP BY b.id, b.full_name
ORDER BY weekly_revenue DESC;
Пояснение логики: JOIN соединяет заказы с бариста. WHERE фильтрует заказы за 7 дней. GROUP BY группирует по бариста. Агрегатная функция SUM() считает общую выручку. ORDER BY DESC сортирует по убыванию.
 
Проверка: выводится список бариста с суммой их заказов за неделю.
Задание 4: найти самые популярные напитки (по количеству заказов), топ-5
Цель: определить 5 самых заказываемых позиций меню.
SQL-запрос:
SELECT
mi.id,
mi.name,
SUM(od.quantity) AS total_ordered
FROM order_details od
JOIN menu_items mi ON od.menu_item_id = mi.id
GROUP BY mi.id, mi.name
ORDER BY total_ordered DESC
LIMIT 5;
Пояснение логики: JOIN соединяет состав заказов с меню. GROUP BY группирует по позициям. SUM() суммирует проданное количество. ORDER BY DESC и LIMIT 5 оставляют только топ-5.
 
Проверка: выводится ровно 5 строк, отсортированных по убыванию.


Задание 5: cкрыть из меню (is_available = false) напиток, который закончился
Цель: пометить «Чизкейк» как недоступный для заказа.
SQL-запрос:
UPDATE menu_items SET is_available = FALSE
WHERE name = 'Чизкейк';
Пояснение логики: оператор UPDATE изменяет поле is_available. WHERE ограничивает изменение только позицией с указанным названием.
 
Проверка: поле is_available для «Чизкейк» изменилось с true на false.
 
ЗАКЛЮЧЕНИЕ
В ходе выполнения лабораторной работы была спроектирована и реализована реляционная база данных для кофейни CoffeeShopDB. Схема базы данных приведена к третьей нормальной форме (3NF), что исключает избыточность данных и аномалии при их обновлении.

В процессе работы освоены следующие навыки:
· проектирование ER-диаграмм и определение связей между таблицами (1:N, связующая таблица для M:N);
· написание DDL-скриптов для создания таблиц, внешних ключей и индексов;
· наполнение таблиц тестовыми данными с помощью оператора INSERT;
· составление запросов с использованием JOIN, WHERE, GROUP BY, ORDER BY и агрегатных функций (SUM, COUNT);
· фильтрация данных по дате с помощью DATE(), NOW(), INTERVAL;
· обновление данных с помощью оператора UPDATE.

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