RelationalDesign: schema_creation.sql

DROP TABLE IF EXISTS student_subject CASCADE;
DROP TABLE IF EXISTS advice CASCADE;
DROP TABLE IF EXISTS affiliated CASCADE;
DROP TABLE IF EXISTS subject CASCADE;
DROP TABLE IF EXISTS student CASCADE;
DROP TABLE IF EXISTS professor CASCADE;
DROP TABLE IF EXISTS faculty CASCADE;
DROP TABLE IF EXISTS university CASCADE;
DROP TYPE IF EXISTS study_field_enum CASCADE;
DROP TYPE IF EXISTS enrollment_status CASCADE;

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

CREATE TYPE enrollment_status AS ENUM ('Enrolled', 'Passed', 'Failed', 'Dropped');

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

-- Faculty
CREATE TABLE Faculty (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Location VARCHAR(100),
    Study_Field study_field_enum NOT NULL,
    University_Id INT NOT NULL,
    FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE CASCADE
);

-- Professor
CREATE TABLE Professor (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255),
    Age INT CHECK (Age BETWEEN 18 AND 80),
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE CASCADE
);

-- Student
CREATE TABLE Student (
    Id SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Surname VARCHAR(255),
    Location VARCHAR(100),
    Student_Index INT NOT NULL UNIQUE,
    Faculty_Id INT NOT NULL,
    FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT
);

-- Advice (Student ↔ Professor, M:N)
CREATE TABLE Advice (
    Student_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    Start_Date DATE,
    End_Date DATE,
    PRIMARY KEY (Student_Id, Professor_Id),
    FOREIGN KEY (Student_Id) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE CASCADE
);

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

-- Student_Subject 
CREATE TABLE Student_Subject (
    Ss_Id INT NOT NULL,
    Student_Id INT NOT NULL,
    Subject_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    Enrollment_Date DATE NOT NULL DEFAULT CURRENT_DATE,
    Status enrollment_status 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, Ss_Id),
    
    FOREIGN KEY (Student_Id) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (Subject_Id) REFERENCES Subject(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE RESTRICT
);

-- Affiliated (University ↔ Professor, M:N)
CREATE TABLE Affiliated (
    University_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    PRIMARY KEY (University_Id, Professor_Id),
    FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE CASCADE,
    FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE CASCADE
);

CREATE TABLE teach (
    professor_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    PRIMARY KEY (professor_id, subject_id),
    FOREIGN KEY (professor_id) REFERENCES professor(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subject(id) ON DELETE CASCADE
);