Relational schema (DDL and Database): DML_PT.sql

CREATE TABLE User (
    id_user INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Administration (
    id_user INT PRIMARY KEY,
    department VARCHAR(255),
    FOREIGN KEY (id_user) REFERENCES User(id_user)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Teacher (
    id_teacher INT PRIMARY KEY,
    id_user INT UNIQUE,
    hire_date DATE,
    FOREIGN KEY (id_user) REFERENCES User(id_user)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Student (
    id_student INT PRIMARY KEY,
    id_user INT UNIQUE,
    progress_score FLOAT,
    FOREIGN KEY (id_user) REFERENCES User(id_user)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Rates (
    id_rating INT PRIMARY KEY,
    id_teacher INT,
    id_student INT,
    rating FLOAT NOT NULL,
    explanation TEXT,
    FOREIGN KEY (id_teacher) REFERENCES Teacher(id_teacher)
        ON UPDATE CASCADE 
        ON DELETE SET NULL,
    FOREIGN KEY (id_student) REFERENCES Student(id_student)
        ON UPDATE CASCADE 
        ON DELETE SET NULL
);

CREATE TABLE Subject (
    id_subject INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT
);

CREATE TABLE Course (
    id_Course INT PRIMARY KEY,
    num_of_classes INT,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES Teacher(id_teacher)
        ON UPDATE CASCADE 
        ON DELETE SET NULL
);

CREATE TABLE Subject_Course (
    id_subject INT,
    id_Course INT,
    PRIMARY KEY (id_subject, id_Course),
    FOREIGN KEY (id_subject) REFERENCES Subject(id_subject)
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    FOREIGN KEY (id_Course) REFERENCES Course(id_Course)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Class (
    id_class INT PRIMARY KEY,
    start_time TIME,
    end_time TIME,
    duration INT,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES Teacher(id_teacher)
        ON UPDATE CASCADE 
        ON DELETE SET NULL
);

CREATE TABLE Course_Class (
    id_Course INT,
    id_class INT,
    PRIMARY KEY (id_Course, id_class),
    FOREIGN KEY (id_Course) REFERENCES Course(id_Course)
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    FOREIGN KEY (id_class) REFERENCES Class(id_class)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Assignment (
    id_assignment INT PRIMARY KEY,
    description TEXT,
    score FLOAT,
    deadline DATE
);

CREATE TABLE Course_Assignment (
    id_Course INT,
    id_assignment INT,
    PRIMARY KEY (id_Course, id_assignment),
    FOREIGN KEY (id_Course) REFERENCES Course(id_Course)
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    FOREIGN KEY (id_assignment) REFERENCES Assignment(id_assignment)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Enrolls (
    id_enrollment INT PRIMARY KEY,
    id_student INT,
    id_Course INT,
    status VARCHAR(50),
    en_date DATE,
    FOREIGN KEY (id_student) REFERENCES Student(id_student)
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    FOREIGN KEY (id_Course) REFERENCES Course(id_Course)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Attendance (
    id_attendance INT PRIMARY KEY,
    id_student INT,
    id_class INT,
    attended BOOLEAN,
    FOREIGN KEY (id_student) REFERENCES Student(id_student)
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    FOREIGN KEY (id_class) REFERENCES Class(id_class)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);

CREATE TABLE Submits (
    id_submission INT PRIMARY KEY,
    id_student INT,
    id_assignment INT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    progress_score FLOAT,
    FOREIGN KEY (id_student) REFERENCES Student(id_student)
        ON UPDATE CASCADE 
        ON DELETE CASCADE,
    FOREIGN KEY (id_assignment) REFERENCES Assignment(id_assignment)
        ON UPDATE CASCADE 
        ON DELETE CASCADE
);