RelationalDesign: schema_creation3.sql

DROP TABLE IF EXISTS teach CASCADE;
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),
    Is_Private 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
CREATE TABLE Advice (
    Student_Id INT NOT NULL,
    Professor_Id INT NOT NULL,
    Start_Date DATE NOT NULL,
    End_Date DATE,
    PRIMARY KEY (Student_Id, Professor_Id, Start_Date),
    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 (Weak Entity)
CREATE TABLE Student_Subject (
    Student_Id INT NOT NULL,
    Subject_Id INT NOT NULL,
    Ss_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),
    Abesense_Count INT NOT NULL DEFAULT 0 CHECK (Abesense_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
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
);

-- Teach
CREATE TABLE Teach (
    Professor_Id INT NOT NULL,
    Subject_Id INT 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
);