backend/lab10s.sql - main - suhareva/web-nodejs-labs

backend/lab10s.sql
193 строки7 KB
daniilp
feat(lab1): Finish lab1 static page
15 дек 2025, 12:36
15 дек 2025, 12:3642809ee
-- Создаем схему для расписания
CREATE SCHEMA IF NOT EXISTS lab10_schedule;

-- 1. Физические лица
CREATE TABLE lab10_schedule.persons (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    birth_date DATE,
    email VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Кафедры
CREATE TABLE lab10_schedule.departments (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    faculty VARCHAR(100),
    head_person_id INTEGER REFERENCES lab10_schedule.persons(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 3. Должности
CREATE TABLE lab10_schedule.positions (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    category VARCHAR(50) CHECK (category IN ('профессорско-преподавательский состав', 'научный сотрудник', 'административный персонал')),
    min_salary INTEGER,
    max_salary INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 4. Преподаватели (наследуют от физических лиц)
CREATE TABLE lab10_schedule.teachers (
    id SERIAL PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES lab10_schedule.persons(id),
    department_id INTEGER REFERENCES lab10_schedule.departments(id),
    position_id INTEGER REFERENCES lab10_schedule.positions(id),
    academic_degree VARCHAR(100),
    academic_rank VARCHAR(100),
    employment_date DATE,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(person_id)
);

-- 5. Корпуса
CREATE TABLE lab10_schedule.buildings (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    address VARCHAR(300) NOT NULL,
    floors_count INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 6. Аудитории
CREATE TABLE lab10_schedule.classrooms (
    id SERIAL PRIMARY KEY,
    building_id INTEGER NOT NULL REFERENCES lab10_schedule.buildings(id),
    room_number VARCHAR(20) NOT NULL,
    floor INTEGER,
    capacity INTEGER,
    classroom_type VARCHAR(50) CHECK (classroom_type IN ('лекционная', 'практическая', 'лабораторная', 'компьютерный класс', 'специализированная')),
    equipment TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(building_id, room_number)
);

-- 7. Дисциплины
CREATE TABLE lab10_schedule.subjects (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(300) NOT NULL,
    description TEXT,
    total_hours INTEGER NOT NULL,
    lecture_hours INTEGER,
    practice_hours INTEGER,
    lab_hours INTEGER,
    department_id INTEGER REFERENCES lab10_schedule.departments(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 8. Группы
CREATE TABLE lab10_schedule.student_groups (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    course INTEGER CHECK (course BETWEEN 1 AND 6),
    specialization VARCHAR(200),
    department_id INTEGER REFERENCES lab10_schedule.departments(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 9. Типы занятий
CREATE TABLE lab10_schedule.lesson_types (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    short_name VARCHAR(10),
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 10. Расписание (основная таблица)
CREATE TABLE lab10_schedule.schedule (
    id SERIAL PRIMARY KEY,
    subject_id INTEGER NOT NULL REFERENCES lab10_schedule.subjects(id),
    teacher_id INTEGER NOT NULL REFERENCES lab10_schedule.teachers(id),
    group_id INTEGER NOT NULL REFERENCES lab10_schedule.student_groups(id),
    classroom_id INTEGER REFERENCES lab10_schedule.classrooms(id),
    lesson_type_id INTEGER NOT NULL REFERENCES lab10_schedule.lesson_types(id),
    
    -- Временные параметры
    lesson_date DATE NOT NULL,
    week_number INTEGER, -- номер недели от начала семестра
    day_of_week INTEGER CHECK (day_of_week BETWEEN 1 AND 7), -- 1-понедельник, 7-воскресенье
    is_even_week BOOLEAN, -- true - четная неделя, false - нечетная
    
    -- Время занятия
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    
    -- Дополнительная информация
    topic VARCHAR(500),
    notes TEXT,
    is_cancelled BOOLEAN DEFAULT false,
    cancellation_reason TEXT,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    -- Проверка времени
    CONSTRAINT valid_time CHECK (end_time > start_time)
);

-- 11. Связь Преподаватель-Дисциплина (многие-ко-многим)
CREATE TABLE lab10_schedule.teacher_subjects (
    id SERIAL PRIMARY KEY,
    teacher_id INTEGER NOT NULL REFERENCES lab10_schedule.teachers(id),
    subject_id INTEGER NOT NULL REFERENCES lab10_schedule.subjects(id),
    is_main_teacher BOOLEAN DEFAULT false,
    hours_per_semester INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(teacher_id, subject_id)
);

-- 12. Индексы для оптимизации запросов
CREATE INDEX idx_schedule_date ON lab10_schedule.schedule(lesson_date);
CREATE INDEX idx_schedule_group ON lab10_schedule.schedule(group_id);
CREATE INDEX idx_schedule_teacher ON lab10_schedule.schedule(teacher_id);
CREATE INDEX idx_schedule_classroom ON lab10_schedule.schedule(classroom_id);
CREATE INDEX idx_schedule_subject ON lab10_schedule.schedule(subject_id);
CREATE INDEX idx_schedule_week ON lab10_schedule.schedule(week_number);
CREATE INDEX idx_schedule_day_time ON lab10_schedule.schedule(day_of_week, start_time);
CREATE INDEX idx_teachers_person ON lab10_schedule.teachers(person_id);
CREATE INDEX idx_classrooms_building ON lab10_schedule.classrooms(building_id);

-- 13. Функция для обновления времени изменения
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 14. Триггер для автоматического обновления updated_at
CREATE TRIGGER update_schedule_updated_at BEFORE UPDATE
    ON lab10_schedule.schedule FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 15. Вспомогательная функция для вычисления номера недели
CREATE OR REPLACE FUNCTION lab10_schedule.get_week_number(input_date DATE, semester_start DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN EXTRACT(WEEK FROM input_date) - EXTRACT(WEEK FROM semester_start) + 1;
END;
$$ LANGUAGE plpgsql;

-- 16. Вспомогательная функция для определения четности недели
CREATE OR REPLACE FUNCTION lab10_schedule.is_even_week(input_date DATE)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (EXTRACT(WEEK FROM input_date) % 2) = 0;
END;
$$ LANGUAGE plpgsql;

ALTER TABLE lab10_schedule.positions 
ADD COLUMN short_title VARCHAR(10);