
CREATE DATABASE 'university.fdb';

CREATE TABLE Departments (
    DName VARCHAR(100),
    Address VARCHAR(100),
    PRIMARY KEY (DName) 
);

CREATE TABLE Programs (
    PName VARCHAR(100),
    Abbr VARCHAR(10),
    PRIMARY KEY (PName) 
);

CREATE TABLE Branches (
    BID INT,
    BName VARCHAR(100),
    BProgram VARCHAR(100),
    PRIMARY KEY (BID),
    FOREIGN KEY (BProgram) REFERENCES Programs(PName),
    UNIQUE (BName, BProgram)
);

CREATE TABLE Students (
    SID INT,
    SName VARCHAR(100),
    SBranch INT,
    PRIMARY KEY (SID),
    FOREIGN KEY (SBranch) REFERENCES Branches(BID)
);

CREATE TABLE Courses (
    Code CHAR(10),
    CName VARCHAR(100),
    Credit INT,
    CDepartment VARCHAR(100),
    NStudents INT DEFAULT NULL,
    PRIMARY KEY (Code),
    FOREIGN KEY (CDepartment) REFERENCES Departments(DName)
);

CREATE TABLE ClassifiedCourses (
    CCourse CHAR(10),
    CType CHAR(20),
    PRIMARY KEY (CCourse, CType),
    FOREIGN KEY (CCourse) REFERENCES Courses(Code)
);

CREATE TABLE DepartmentsPrograms (
    DPDepartment VARCHAR(100),
    DPProgram VARCHAR(100),
    PRIMARY KEY (DPDepartment, DPProgram),
    FOREIGN KEY (DPDepartment) REFERENCES Departments(DName),
    FOREIGN KEY (DPProgram) REFERENCES Programs(PName)
);    

CREATE TABLE BranchRecommendedCourses (
    RCourse CHAR(10),
    RBranch INT,
    PRIMARY KEY (RCourse,RBranch),
    FOREIGN KEY (RCourse) REFERENCES Courses(Code),
    FOREIGN KEY (RBranch) REFERENCES Branches(BID)
);

CREATE TABLE BranchMandatoryCourses (
    BMCourse CHAR(10),
    BMBranch INT,
    PRIMARY KEY (BMCourse,BMBranch),
    FOREIGN KEY (BMCourse) REFERENCES Courses(Code),
    FOREIGN KEY (BMBranch) REFERENCES Branches(BID)
);

CREATE TABLE ProgramMandatoryCourses (
    PMCourse CHAR(10),
    PMProgram VARCHAR(100),
    PRIMARY KEY (PMCourse,PMProgram),
    FOREIGN KEY (PMCourse) REFERENCES Courses(Code),
    FOREIGN KEY (PMProgram) REFERENCES Programs(PName)
);

CREATE TABLE RequiredCourses (
    Dependent CHAR(10),
    Required CHAR(10),
    PRIMARY KEY (Dependent,Required),
    FOREIGN KEY (Dependent) REFERENCES Courses(Code),
    FOREIGN KEY (Required) REFERENCES Courses(Code)
);

CREATE TABLE CoursesStudents (
    CSCourse CHAR(10),
    CSStudent INT,
    EventDate CHAR(19),
    Status INT CONSTRAINT check_status CHECK ( Status in (0, 1, 2, 3, 4, 5)), -- 0 - waiting, 1 - registered, 2 - not passed, 3..5 - grade
    PRIMARY KEY (CSCourse, CSStudent),
    FOREIGN KEY (CSCourse) REFERENCES Courses(Code),
    FOREIGN KEY (CSStudent) REFERENCES Students(SID)
);

COMMIT;
