init-db.sql - master - thorrienn/web-nodejs-labs

init-db.sql
286 строк11 KB
thorrienn
first_commit
04 июн 2026, 10:44
04 июн 2026, 10:4423629dc
-- =====================================================
-- ЛАБОРАТОРНАЯ РАБОТА №10 - МАГАЗИН И РАСПИСАНИЕ
-- =====================================================

-- =====================
-- СХЕМА LAB10 (МАГАЗИН)
-- =====================
CREATE SCHEMA IF NOT EXISTS lab10;

-- Категории товаров
CREATE TABLE IF NOT EXISTS lab10.categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Товары
CREATE TABLE IF NOT EXISTS lab10.products (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price INTEGER NOT NULL DEFAULT 0,
    amount INTEGER NOT NULL DEFAULT 0,
    category_id INTEGER REFERENCES lab10.categories(id),
    image_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Заказы
CREATE TABLE IF NOT EXISTS lab10.orders (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES lab10.products(id),
    quantity INTEGER NOT NULL DEFAULT 1,
    total_price INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Тестовые данные для магазина
INSERT INTO lab10.categories (name) VALUES 
    ('Ноутбуки'),
    ('Смартфоны'),
    ('Планшеты'),
    ('Аксессуары'),
    ('Мониторы')
ON CONFLICT DO NOTHING;

INSERT INTO lab10.products (title, description, price, amount, category_id, image_url) VALUES
    ('MacBook Pro 14', 'Apple MacBook Pro с чипом M3 Pro', 199000, 5, 1, 'https://placehold.co/300x200?text=MacBook+Pro'),
    ('MacBook Air 13', 'Apple MacBook Air с чипом M2', 129000, 10, 1, 'https://placehold.co/300x200?text=MacBook+Air'),
    ('iPhone 15 Pro', 'Apple iPhone 15 Pro 256GB', 149000, 15, 2, 'https://placehold.co/300x200?text=iPhone+15+Pro'),
    ('iPhone 15', 'Apple iPhone 15 128GB', 99900, 20, 2, 'https://placehold.co/300x200?text=iPhone+15'),
    ('iPad Pro 12.9', 'Apple iPad Pro 12.9 M2', 149000, 8, 3, 'https://placehold.co/300x200?text=iPad+Pro'),
    ('iPad Air', 'Apple iPad Air M1 10.9', 79000, 12, 3, 'https://placehold.co/300x200?text=iPad+Air'),
    ('AirPods Pro', 'Apple AirPods Pro 2-го поколения', 29000, 30, 4, 'https://placehold.co/300x200?text=AirPods+Pro'),
    ('Magic Mouse', 'Apple Magic Mouse', 9900, 25, 4, 'https://placehold.co/300x200?text=Magic+Mouse'),
    ('Samsung Galaxy S24', 'Samsung Galaxy S24 256GB', 99900, 15, 2, 'https://placehold.co/300x200?text=Galaxy+S24'),
    ('Dell UltraSharp 27', 'Монитор Dell 27" 4K', 89000, 7, 5, 'https://placehold.co/300x200?text=Dell+Monitor'),
    ('ASUS ROG Laptop', 'Игровой ноутбук ASUS ROG', 179000, 6, 1, 'https://placehold.co/300x200?text=ASUS+ROG'),
    ('Logitech MX Master', 'Беспроводная мышь Logitech', 12900, 40, 4, 'https://placehold.co/300x200?text=Logitech+Mouse')
ON CONFLICT DO NOTHING;

-- ==========================
-- СХЕМА LAB10_SCHEDULE (РАСПИСАНИЕ)
-- ==========================
CREATE SCHEMA IF NOT EXISTS lab10_schedule;

-- persons - Люди (преподаватели, студенты)
CREATE TABLE IF NOT EXISTS lab10_schedule.persons (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- positions - Должности
CREATE TABLE IF NOT EXISTS lab10_schedule.positions (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    salary INTEGER
);

-- departments - Кафедры
CREATE TABLE IF NOT EXISTS lab10_schedule.departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50)
);

-- teachers - Преподаватели
CREATE TABLE IF NOT EXISTS lab10_schedule.teachers (
    id SERIAL PRIMARY KEY,
    person_id INTEGER REFERENCES lab10_schedule.persons(id),
    position_id INTEGER REFERENCES lab10_schedule.positions(id),
    department_id INTEGER REFERENCES lab10_schedule.departments(id),
    rate NUMERIC(3,2) DEFAULT 1.0
);

-- buildings - Корпуса
CREATE TABLE IF NOT EXISTS lab10_schedule.buildings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(500)
);

-- classrooms - Аудитории
CREATE TABLE IF NOT EXISTS lab10_schedule.classrooms (
    id SERIAL PRIMARY KEY,
    number VARCHAR(20) NOT NULL,
    capacity INTEGER DEFAULT 30,
    building_id INTEGER REFERENCES lab10_schedule.buildings(id)
);

-- disciplines - Дисциплины
CREATE TABLE IF NOT EXISTS lab10_schedule.disciplines (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    hours INTEGER,
    department_id INTEGER REFERENCES lab10_schedule.departments(id)
);

-- groups - Учебные группы
CREATE TABLE IF NOT EXISTS lab10_schedule.groups (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course INTEGER,
    speciality VARCHAR(255)
);

-- lesson_types - Типы занятий
CREATE TABLE IF NOT EXISTS lab10_schedule.lesson_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- timetable - Расписание занятий
CREATE TABLE IF NOT EXISTS lab10_schedule.timetable (
    id SERIAL PRIMARY KEY,
    group_id INTEGER REFERENCES lab10_schedule.groups(id),
    discipline_id INTEGER REFERENCES lab10_schedule.disciplines(id),
    teacher_id INTEGER REFERENCES lab10_schedule.teachers(id),
    classroom_id INTEGER REFERENCES lab10_schedule.classrooms(id),
    lesson_type_id INTEGER REFERENCES lab10_schedule.lesson_types(id),
    weekday INTEGER NOT NULL CHECK (weekday >= 1 AND weekday <= 7),
    time_start TIME NOT NULL,
    time_end TIME NOT NULL,
    week_parity INTEGER CHECK (week_parity IS NULL OR week_parity IN (1, 2))
);

-- Тестовые данные для расписания
-- Кафедры
INSERT INTO lab10_schedule.departments (name, phone) VALUES
    ('Кафедра информационных технологий', '123-45-67'),
    ('Кафедра математики', '123-45-68'),
    ('Кафедра физики', '123-45-69'),
    ('Кафедра иностранных языков', '123-45-70')
ON CONFLICT DO NOTHING;

-- Должности
INSERT INTO lab10_schedule.positions (title, salary) VALUES
    ('Профессор', 150000),
    ('Доцент', 120000),
    ('Старший преподаватель', 90000),
    ('Ассистент', 70000)
ON CONFLICT DO NOTHING;

-- Корпуса
INSERT INTO lab10_schedule.buildings (name, address) VALUES
    ('Главный корпус', 'ул. Университетская, 1'),
    ('Корпус №2', 'ул. Строителей, 15'),
    ('Корпус №3', 'пр. Ленина, 50')
ON CONFLICT DO NOTHING;

-- Аудитории
INSERT INTO lab10_schedule.classrooms (number, capacity, building_id) VALUES
    ('101', 30, 1),
    ('102', 25, 1),
    ('201', 50, 1),
    ('301', 20, 2),
    ('302', 15, 2),
    ('401', 100, 3),
    ('402', 40, 3)
ON CONFLICT DO NOTHING;

-- Дисциплины
INSERT INTO lab10_schedule.disciplines (name, hours, department_id) VALUES
    ('Программирование', 180, 1),
    ('Базы данных', 120, 1),
    ('Web-разработка', 90, 1),
    ('Высшая математика', 144, 2),
    ('Линейная алгебра', 72, 2),
    ('Физика', 108, 3),
    ('Английский язык', 72, 4)
ON CONFLICT DO NOTHING;

-- Группы
INSERT INTO lab10_schedule.groups (name, course, speciality) VALUES
    ('ИС-21', 2, 'Информационные системы'),
    ('ИС-22', 2, 'Информационные системы'),
    ('ПИ-21', 3, 'Программная инженерия'),
    ('КС-21', 4, 'Компьютерная безопасность')
ON CONFLICT DO NOTHING;

-- Типы занятий
INSERT INTO lab10_schedule.lesson_types (name) VALUES
    ('Лекция'),
    ('Практика'),
    ('Лабораторная работа'),
    ('Семинар')
ON CONFLICT DO NOTHING;

-- Люди (преподаватели)
INSERT INTO lab10_schedule.persons (full_name, email, phone) VALUES
    ('Иванов Иван Иванович', 'ivanov@university.ru', '555-01-01'),
    ('Петрова Анна Сергеевна', 'petrova@university.ru', '555-01-02'),
    ('Сидоров Алексей Петрович', 'sidorov@university.ru', '555-01-03'),
    ('Козлова Мария Дмитриевна', 'kozlova@university.ru', '555-01-04'),
    ('Смирнов Олег Викторович', 'smirnov@university.ru', '555-01-05'),
    ('Фёдорова Елена Николаевна', 'fedorova@university.ru', '555-01-06')
ON CONFLICT DO NOTHING;

-- =====================================================
-- ЛАБОРАТОРНАЯ РАБОТА №11 - АУТЕНТИФИКАЦИЯ
-- =====================================================

-- СХЕМА LAB11
CREATE SCHEMA IF NOT EXISTS lab11;

-- Роли пользователей
CREATE TABLE IF NOT EXISTS lab11.roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

-- Добавить колонку description, если её нет
ALTER TABLE lab11.roles ADD COLUMN IF NOT EXISTS description TEXT;

-- Пользователи
CREATE TABLE IF NOT EXISTS lab11.users (
    id SERIAL PRIMARY KEY,
    login VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id INTEGER REFERENCES lab11.roles(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Тестовые данные для ролей
INSERT INTO lab11.roles (name, description) VALUES
    ('admin', 'Администратор с полным доступом'),
    ('user', 'Обычный пользователь'),
    ('viewer', 'Пользователь с доступом только для просмотра')
ON CONFLICT DO NOTHING;

-- Тестовый администратор
INSERT INTO lab11.users (login, password_hash, role_id) VALUES
    ('admin', '$2b$10$rN9uGSz5Eagj3jLgAXlN7.dElp8TApHuusAlb6DpHZLefcgGs7uGa', (SELECT id FROM lab11.roles WHERE name = 'admin'))
ON CONFLICT DO NOTHING;

-- Преподаватели
INSERT INTO lab10_schedule.teachers (person_id, position_id, department_id, rate) VALUES
    (1, 1, 1, 1.0),
    (2, 2, 1, 0.75),
    (3, 2, 2, 1.0),
    (4, 3, 1, 0.5),
    (5, 4, 3, 1.0),
    (6, 3, 4, 0.5)
ON CONFLICT DO NOTHING;

-- Расписание
INSERT INTO lab10_schedule.timetable (group_id, discipline_id, teacher_id, classroom_id, lesson_type_id, weekday, time_start, time_end, week_parity) VALUES
    (1, 1, 1, 1, 1, 1, '09:00', '10:30', NULL),
    (1, 2, 2, 2, 2, 1, '10:45', '12:15', NULL),
    (1, 4, 3, 3, 1, 2, '09:00', '10:30', 1),
    (1, 1, 1, 1, 3, 2, '10:45', '12:15', 2),
    (1, 7, 6, 4, 2, 3, '09:00', '10:30', NULL),
    (2, 1, 4, 5, 2, 1, '14:00', '15:30', NULL),
    (2, 3, 2, 2, 3, 2, '14:00', '15:30', NULL),
    (2, 4, 3, 3, 1, 3, '14:00', '15:30', 1),
    (3, 1, 1, 1, 1, 4, '09:00', '10:30', NULL),
    (3, 2, 2, 6, 2, 4, '10:45', '12:15', NULL),
    (3, 3, 2, 7, 3, 5, '09:00', '10:30', NULL),
    (4, 5, 3, 3, 2, 1, '15:45', '17:15', NULL),
    (4, 6, 5, 4, 1, 2, '15:45', '17:15', 1)
ON CONFLICT DO NOTHING;