University

-- Используем уже существующую базу данных
USE sandbox_db;

-- Создаем таблицы
CREATE TABLE Groups (
gr_id SERIAL PRIMARY KEY,
gr_name TEXT NOT NULL UNIQUE
);

CREATE TABLE Student (
stud_id SERIAL PRIMARY KEY,
f_name TEXT NOT NULL,
l_name TEXT NOT NULL,
gr_id INTEGER,
FOREIGN KEY (gr_id) REFERENCES Groups(gr_id) ON DELETE SET NULL
);

CREATE TABLE Teachers (
teach_id SERIAL PRIMARY KEY,
f_name TEXT NOT NULL,
l_name TEXT NOT NULL,
email TEXT UNIQUE
);

CREATE TABLE SubjectTypes (
type_id SERIAL PRIMARY KEY,
type_name TEXT NOT NULL UNIQUE
);

CREATE TABLE Subjects (
sub_id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
credits TEXT NOT NULL,
type_id INTEGER,
FOREIGN KEY (type_id) REFERENCES SubjectTypes(type_id) ON DELETE CASCADE
);

CREATE TABLE Teach (
sub_id INTEGER,
teach_id INTEGER,
PRIMARY KEY (sub_id, teach_id),
FOREIGN KEY (sub_id) REFERENCES Subjects(sub_id) ON DELETE CASCADE,
FOREIGN KEY (teach_id) REFERENCES Teachers(teach_id) ON DELETE CASCADE
);

CREATE TABLE Marks (
sub_id INTEGER,
student_id INTEGER,
mark INTEGER CHECK (mark BETWEEN 0 AND 100),
PRIMARY KEY (sub_id, student_id),
FOREIGN KEY (sub_id) REFERENCES Subjects(sub_id) ON DELETE CASCADE,
FOREIGN KEY (student_id) REFERENCES Student(stud_id) ON DELETE CASCADE
);

CREATE TABLE GPA (
gpa_id SERIAL PRIMARY KEY,
stud_id INTEGER UNIQUE,
qpa DECIMAL(3,2) CHECK (qpa BETWEEN 0.01 AND 4.00),
FOREIGN KEY (stud_id) REFERENCES Student(stud_id) ON DELETE CASCADE
);