/* Database from Elmasri and Navathe, 5th edition Figure 1.2 */

CREATE DOMAIN department as varchar(10);
CREATE DOMAIN courseNumber as varchar(10);
CREATE DOMAIN semester as varchar(10)
CHECK (value = 'first' OR value = 'second' OR value = 'Summer');


CREATE TABLE student (
       name varchar(20),
       studentNumber int,
       class int,
       major department,
       PRIMARY KEY (studentNumber)
);

CREATE TABLE course (
       courseName varchar(30),
       courseNumber courseNumber,
       creditHours int,
       department department,
       PRIMARY KEY (courseNumber)
);

CREATE TABLE section (
       sectionID int,
       courseNumber courseNumber,
       semester semester,
       year int,
       instructor varchar(20),
       PRIMARY KEY (sectionID),
       FOREIGN KEY (courseNumber) REFERENCES course(courseNumber)
);

CREATE TABLE gradeReport (
       studentNumber int,
       sectionID int,
       grade char,
       PRIMARY KEY (studentNumber, sectionID),
       FOREIGN KEY (studentNumber) REFERENCES student(studentNumber),
       FOREIGN KEY (sectionID) REFERENCES section(sectionID)
);

CREATE TABLE prerequisite (
       courseNumber courseNumber,
       prerequisiteNumber courseNumber,
       PRIMARY KEY (courseNumber, prerequisiteNumber),
       FOREIGN KEY (courseNumber) REFERENCES course(courseNumber),
       FOREIGN KEY (prerequisiteNumber) REFERENCES course(courseNumber)
);