LogicalModel: schema_creation.sql

CREATE TABLE University (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL UNIQUE,
    Location VARCHAR(255) NOT NULL,
    IsPrivate BOOLEAN NOT NULL
);

CREATE TYPE study_field_enum AS ENUM ('ENGINEERING', 'MEDICINE', 'LAW', 'ARTS', 'SCIENCE');

CREATE TABLE Faculty (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Location VARCHAR(255) NOT NULL,
    study_field study_field_enum NOT NULL,
    University_Id INT NOT NULL,
        UNIQUE(Name, University_Id),
    FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE RESTRICT
);
CREATE TABLE Professor (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255) NOT NULL,
    Age INT NOT NULL CHECK (Age BETWEEN 18 AND 80),
    Faculty_Id INT NOT NULL,
        UNIQUE(Name, Surname, Faculty_Id),
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT
);
CREATE TABLE Student (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255) NOT NULL,
    Location VARCHAR(255) NOT NULL,
    Student_Index INT NOT NULL UNIQUE, 
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT
);

CREATE TABLE Subject (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Semester INT NOT NULL CHECK (Semester BETWEEN 1 AND 8),
    Credits INT NOT NULL CHECK (Credits BETWEEN 1 AND 10),
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT
);

CREATE TABLE Subject_Professor (
    Subject_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    PRIMARY KEY(Subject_Id, Professor_Id),
    FOREIGN KEY (Subject_Id) REFERENCES Subject(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE CASCADE
);

    CREATE TYPE enrollment_status AS ENUM ('ENROLLED', 'PASSED', 'FAILED', 'DROPPED');

CREATE TABLE Student_Subject (
    Student_Id INT NOT NULL,
    Subject_Id INT NOT NULL,
    Enrollment_Date DATE NOT NULL,
    Status enrollment_status NOT NULL DEFAULT 'ENROLLED',
    Final_Grade INT CHECK (Final_Grade BETWEEN 5 AND 10),
    Absences_Count INT NOT NULL DEFAULT 0 CHECK (Absences_Count >= 0),
    PRIMARY KEY (Student_Id, Subject_Id),
    FOREIGN KEY (Student_Id) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (Subject_Id) REFERENCES Subject(Id) ON DELETE CASCADE
);