BGD 700 - 2023

PRAGMA foreign_keys = ON;
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    firstname TEXT,
    lastname TEXT,
    address TEXT,
    phone TEXT);
CREATE TABLE curriculum (
    id INTEGER PRIMARY KEY,
    name TEXT,
    secretary INT REFERENCES person ON DELETE CASCADE,
    director INT REFERENCES person ON DELETE CASCADE);
CREATE TABLE course (
    id INTEGER PRIMARY KEY,
    teacher INT REFERENCES person ON DELETE CASCADE,
    name TEXT);
CREATE TABLE curriculum_course (
    curriculum INT REFERENCES curriculum ON DELETE CASCADE,
    course INT REFERENCES course ON DELETE CASCADE,
    coef INT,
    PRIMARY KEY (curriculum, course));
CREATE TABLE curriculum_student (
    curriculum INT REFERENCES curriculum ON DELETE CASCADE,
    student INT REFERENCES person ON DELETE CASCADE,
    PRIMARY KEY (curriculum, student));
CREATE TABLE rooms (
    id INTEGER PRIMARY KEY,
    name TEXT,
    capacity INT);
CREATE TABLE edt(
    room INT REFERENCES rooms ON DELETE CASCADE,
    course INT REFERENCES course ON DELETE CASCADE,
    start DATE,
    end DATE);
CREATE TABLE examination (
    id INTEGER PRIMARY KEY,
    course INT REFERENCES course ON DELETE CASCADE,
    name TEXT,
    date Date,
    coef INT);
CREATE TABLE grades(
    examination INT REFERENCES examination ON DELETE CASCADE,
    student INT REFERENCES person ON DELETE CASCADE,
    score DECIMAL(2,2),
    PRIMARY KEY (examination, student));