db/postgresql/02_schema.sql - master - evernotenkai/DataBaseXJavaProject

db/postgresql/02_schema.sql
113 строк4 KB
evernotenkai
update: 02_schema.sql, 03_seed.sql
12 май 2026, 22:58
12 май 2026, 22:585965ca0
DROP TABLE IF EXISTS polin_md.attachments CASCADE;
DROP TABLE IF EXISTS polin_md.grades CASCADE;
DROP TABLE IF EXISTS polin_md.submissions CASCADE;
DROP TABLE IF EXISTS polin_md.assignments CASCADE;
DROP TABLE IF EXISTS polin_md.enrollments CASCADE;
DROP TABLE IF EXISTS polin_md.courses CASCADE;
DROP TABLE IF EXISTS polin_md.users CASCADE;
DROP TABLE IF EXISTS polin_md.students CASCADE;
DROP TABLE IF EXISTS polin_md.teachers CASCADE;

CREATE TABLE polin_md.teachers (
    teacher_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20)
);

CREATE TABLE polin_md.students (
    student_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    group_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20)
);

CREATE TABLE polin_md.users (
    user_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    login VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,
    teacher_id INTEGER UNIQUE,
    student_id INTEGER UNIQUE,
    CONSTRAINT chk_users_role CHECK (role IN ('admin', 'teacher', 'student')),
    CONSTRAINT fk_users_teacher FOREIGN KEY (teacher_id) REFERENCES polin_md.teachers(teacher_id) ON DELETE CASCADE,
    CONSTRAINT fk_users_student FOREIGN KEY (student_id) REFERENCES polin_md.students(student_id) ON DELETE CASCADE,
    CONSTRAINT chk_users_link CHECK (
        (role = 'admin' AND teacher_id IS NULL AND student_id IS NULL) OR
        (role = 'teacher' AND teacher_id IS NOT NULL AND student_id IS NULL) OR
        (role = 'student' AND student_id IS NOT NULL AND teacher_id IS NULL)
    )
);

CREATE TABLE polin_md.courses (
    course_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    teacher_id INTEGER NOT NULL,
    CONSTRAINT fk_courses_teacher
        FOREIGN KEY (teacher_id) REFERENCES polin_md.teachers(teacher_id) ON DELETE RESTRICT
);

CREATE TABLE polin_md.enrollments (
    enrollment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrollment_date DATE NOT NULL,
    CONSTRAINT uq_enrollment UNIQUE (student_id, course_id),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id) REFERENCES polin_md.students(student_id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id) REFERENCES polin_md.courses(course_id) ON DELETE CASCADE
);

CREATE TABLE polin_md.assignments (
    assignment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    course_id INTEGER NOT NULL,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    due_date DATE NOT NULL,
    created_at DATE NOT NULL,
    CONSTRAINT fk_assignment_course
        FOREIGN KEY (course_id) REFERENCES polin_md.courses(course_id) ON DELETE CASCADE,
    CONSTRAINT chk_assignment_dates
        CHECK (due_date >= created_at)
);

CREATE TABLE polin_md.submissions (
    submission_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    assignment_id INTEGER NOT NULL,
    student_id INTEGER NOT NULL,
    submission_date DATE NOT NULL,
    content_note TEXT,
    status VARCHAR(30) NOT NULL,
    CONSTRAINT fk_submission_assignment
        FOREIGN KEY (assignment_id) REFERENCES polin_md.assignments(assignment_id) ON DELETE CASCADE,
    CONSTRAINT fk_submission_student
        FOREIGN KEY (student_id) REFERENCES polin_md.students(student_id) ON DELETE CASCADE,
    CONSTRAINT chk_submission_status
        CHECK (status IN ('submitted', 'checked', 'returned'))
);

CREATE TABLE polin_md.attachments (
    attachment_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    submission_id INTEGER NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    uploaded_at DATE NOT NULL,
    CONSTRAINT fk_attachment_submission
        FOREIGN KEY (submission_id) REFERENCES polin_md.submissions(submission_id) ON DELETE CASCADE
);

CREATE TABLE polin_md.grades (
    grade_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    submission_id INTEGER NOT NULL UNIQUE,
    grade_value INTEGER NOT NULL,
    teacher_comment TEXT,
    graded_date DATE NOT NULL,
    CONSTRAINT fk_grade_submission
        FOREIGN KEY (submission_id) REFERENCES polin_md.submissions(submission_id) ON DELETE CASCADE,
    CONSTRAINT chk_grade_value
        CHECK (grade_value BETWEEN 1 AND 100)
);