newnewLab10Lab11.sql - main - Cooolprogrammers/web-nodejs-labs

newnewLab10Lab11.sql
137 строк4 KB
darkvoid6@mail.ru
ЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫЫ
18 дек 2025, 07:59
18 дек 2025, 07:59f2c4f06
-- 1. СБРОС (ВНИМАНИЕ: УДАЛЯЕТ ВСЕ ДАННЫЕ В ЭТИХ СХЕМАХ)
DROP SCHEMA IF EXISTS lab10 CASCADE;
DROP SCHEMA IF EXISTS lab11 CASCADE;

-- ==========================================
-- 2. СХЕМА LAB11 (Авторизация)
-- ==========================================
CREATE SCHEMA lab11;

-- Роли
CREATE TABLE lab11.roles (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

INSERT INTO lab11.roles (name) VALUES ('user'), ('admin');

-- Пользователи
CREATE TABLE lab11.users (
    id SERIAL PRIMARY KEY,
    login TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role_id INT NOT NULL REFERENCES lab11.roles(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Создаем Админа
-- Логин: admin
-- Пароль: admin123 (хэш ниже соответствует этому паролю)
INSERT INTO lab11.users (login, password_hash, role_id)
SELECT 'admin', '$2b$10$B98IcCjr2NGHUPZPGZwgBesRXyM8ZoHdpiBojQic9RTrdsbssY1DS', id
FROM lab11.roles WHERE name = 'admin';

-- Создаем простого Юзера
-- Логин: user1
-- Пароль: user1234
INSERT INTO lab11.users (login, password_hash, role_id)
SELECT 'user1', '$2b$10$B98IcCjr2NGHUPZPGZwgBesRXyM8ZoHdpiBojQic9RTrdsbssY1DS', id
FROM lab11.roles WHERE name = 'user';


-- ==========================================
-- 3. СХЕМА LAB10 (Магазин и Расписание)
-- ==========================================
CREATE SCHEMA lab10;

-- --- МАГАЗИН (SHOP) ---

CREATE TABLE lab10.categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE lab10.products (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    price INT NOT NULL,
    amount INT NOT NULL DEFAULT 0,
    category_id INT REFERENCES lab10.categories(id) ON DELETE SET NULL
);

CREATE TABLE lab10.orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    total_price INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE lab10.order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES lab10.orders(id) ON DELETE CASCADE,
    product_id INT REFERENCES lab10.products(id) ON DELETE CASCADE, -- <--- ВОТ ЭТО РЕШАЕТ ТВОЮ ПРОБЛЕМУ
    quantity INT NOT NULL,
    price_at_purchase INT NOT NULL
);

-- Данные Магазина
INSERT INTO lab10.categories (name) VALUES ('Электроника'), ('Аксессуары');

INSERT INTO lab10.products (title, price, amount, category_id) VALUES
('iPhone 15', 100000, 10, 1),
('MacBook Air', 120000, 5, 1),
('Чехол', 1000, 50, 2),
('Зарядка', 2000, 30, 2);


-- --- РАСПИСАНИЕ (SCHEDULE) ---

CREATE TABLE lab10.buildings (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE lab10.rooms (
    id SERIAL PRIMARY KEY,
    number TEXT NOT NULL,
    building_id INT REFERENCES lab10.buildings(id)
);

CREATE TABLE lab10.groups (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE lab10.teachers (
    id SERIAL PRIMARY KEY,
    fullname TEXT NOT NULL
);

CREATE TABLE lab10.subjects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE lab10.schedule (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    lesson_num INT NOT NULL, -- номер пары (1-6)
    group_id INT REFERENCES lab10.groups(id) ON DELETE CASCADE,
    teacher_id INT REFERENCES lab10.teachers(id) ON DELETE SET NULL,
    subject_id INT REFERENCES lab10.subjects(id) ON DELETE SET NULL,
    room_id INT REFERENCES lab10.rooms(id) ON DELETE SET NULL,
    lesson_type TEXT -- Лекция/Практика
);

-- Данные Расписания
INSERT INTO lab10.buildings (name) VALUES ('Главный корпус');
INSERT INTO lab10.rooms (number, building_id) VALUES ('101', 1), ('205', 1);
INSERT INTO lab10.groups (name) VALUES ('ИВТ-41'), ('ПИ-41');
INSERT INTO lab10.teachers (fullname) VALUES ('Иванов И.И.'), ('Петров П.П.');
INSERT INTO lab10.subjects (name) VALUES ('Веб-разработка'), ('Базы данных');

-- Пара тестовых уроков
INSERT INTO lab10.schedule (date, lesson_num, group_id, teacher_id, subject_id, room_id, lesson_type)
VALUES 
('2025-12-16', 1, 1, 1, 1, 1, 'Лекция'),
('2025-12-16', 2, 1, 2, 2, 2, 'Практика');