db/firebird/01_schema.sql - master - evernotenkai/DataBaseXJavaProject

db/firebird/01_schema.sql
93 строки4 KB
Makson4ik
Весь проект с src db и прочим делом
12 май 2026, 22:43
12 май 2026, 22:43431f4aa
CREATE TABLE 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 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 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 teachers(teacher_id) ON DELETE CASCADE,
    CONSTRAINT fk_users_student FOREIGN KEY (student_id) REFERENCES 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 courses (
    course_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    description BLOB SUB_TYPE TEXT,
    teacher_id INTEGER NOT NULL,
    CONSTRAINT fk_courses_teacher
        FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

CREATE TABLE 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 students(student_id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

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

CREATE TABLE 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 BLOB SUB_TYPE TEXT,
    status VARCHAR(30) NOT NULL,
    CONSTRAINT fk_submission_assignment
        FOREIGN KEY (assignment_id) REFERENCES assignments(assignment_id) ON DELETE CASCADE,
    CONSTRAINT fk_submission_student
        FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    CONSTRAINT chk_submission_status
        CHECK (status IN ('submitted', 'checked', 'returned'))
);

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