| 1 | CREATE TABLE University (
|
|---|
| 2 | Id SERIAL PRIMARY KEY,
|
|---|
| 3 | Name VARCHAR(255) NOT NULL,
|
|---|
| 4 | Location VARCHAR(100) NOT NULL,
|
|---|
| 5 | IsPrivate BOOLEAN NOT NULL
|
|---|
| 6 | );
|
|---|
| 7 |
|
|---|
| 8 | CREATE TYPE study_field_enum AS ENUM ('ENGINEERING', 'MEDICINE', 'LAW', 'ARTS', 'SCIENCE');
|
|---|
| 9 |
|
|---|
| 10 | CREATE TABLE Faculty (
|
|---|
| 11 | Id SERIAL PRIMARY KEY,
|
|---|
| 12 | Name VARCHAR(255) NOT NULL,
|
|---|
| 13 | Location VARCHAR(100) NOT NULL,
|
|---|
| 14 | study_field study_field_enum NOT NULL,
|
|---|
| 15 | University_Id INT NOT NULL,
|
|---|
| 16 | FOREIGN KEY (University_Id) REFERENCES University(Id) ON DELETE CASCADE
|
|---|
| 17 | );
|
|---|
| 18 | CREATE TABLE Professor (
|
|---|
| 19 | Id SERIAL PRIMARY KEY,
|
|---|
| 20 | Name VARCHAR(255) NOT NULL,
|
|---|
| 21 | Surname VARCHAR(255) NOT NULL,
|
|---|
| 22 | Age INT NOT NULL CHECK (Age BETWEEN 18 AND 80),
|
|---|
| 23 | Faculty_Id INT NOT NULL,
|
|---|
| 24 | FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE CASCADE
|
|---|
| 25 | );
|
|---|
| 26 | CREATE TABLE Student (
|
|---|
| 27 | Id SERIAL PRIMARY KEY,
|
|---|
| 28 | Name VARCHAR(255) NOT NULL,
|
|---|
| 29 | Surname VARCHAR(255) NOT NULL,
|
|---|
| 30 | Location VARCHAR(100),
|
|---|
| 31 | Student_Index INT NOT NULL UNIQUE,
|
|---|
| 32 | Faculty_Id INT NOT NULL,
|
|---|
| 33 | Professor_Id INT,
|
|---|
| 34 | FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT,
|
|---|
| 35 | FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE SET NULL
|
|---|
| 36 | );
|
|---|
| 37 | CREATE TABLE Subject (
|
|---|
| 38 | Id SERIAL PRIMARY KEY,
|
|---|
| 39 | Name VARCHAR(255) NOT NULL,
|
|---|
| 40 | Semester INT NOT NULL CHECK (Semester BETWEEN 1 AND 8),
|
|---|
| 41 | Credits INT NOT NULL CHECK (Credits BETWEEN 1 AND 10),
|
|---|
| 42 | Faculty_Id INT NOT NULL,
|
|---|
| 43 | Professor_Id INT,
|
|---|
| 44 | FOREIGN KEY (Faculty_Id) REFERENCES Faculty(Id) ON DELETE RESTRICT,
|
|---|
| 45 | FOREIGN KEY (Professor_Id) REFERENCES Professor(Id) ON DELETE SET NULL
|
|---|
| 46 | );
|
|---|
| 47 |
|
|---|
| 48 | CREATE TYPE enrollment_status AS ENUM ('ENROLLED', 'PASSED', 'FAILED', 'DROPPED');
|
|---|
| 49 |
|
|---|
| 50 | CREATE TABLE Student_Subject (
|
|---|
| 51 | Student_Id INT NOT NULL,
|
|---|
| 52 | Subject_Id INT NOT NULL,
|
|---|
| 53 | Enrollment_Date DATE NOT NULL,
|
|---|
| 54 | Status enrollment_status NOT NULL DEFAULT 'ENROLLED',
|
|---|
| 55 | Final_Grade INT CHECK (Final_Grade BETWEEN 5 AND 10),
|
|---|
| 56 | Absences_Count INT NOT NULL DEFAULT 0 CHECK (Absences_Count >= 0),
|
|---|
| 57 | PRIMARY KEY (Student_Id, Subject_Id),
|
|---|
| 58 | FOREIGN KEY (Student_Id) REFERENCES Student(Id) ON DELETE CASCADE,
|
|---|
| 59 | FOREIGN KEY (Subject_Id) REFERENCES Subject(Id) ON DELETE CASCADE
|
|---|
| 60 | );
|
|---|
