CREATE TABLE Students
(
  idnr CHAR(10) NOT NULL,
  student_name TEXT NOT NULL,
  login CHAR(7) NOT NULL,
  UNIQUE(login),
  program TEXT NOT NULL,
  PRIMARY KEY  (idnr)
);

CREATE TABLE Branches
(
	branch_name TEXT,
	program TEXT,
	PRIMARY KEY(branch_name, program)
);

CREATE TABLE Courses
(
	code CHAR(6) NOT NULL,
	name TEXT NOT NULL,
	credits INT NOT NULL,
	CHECK (credits >0),
	department TEXT NOT NULL,
	PRIMARY KEY(code)
);

CREATE TABLE LimitedCourses(
	code CHAR(6) NOT NULL,
	seats INT NOT NULL,
	CHECK(seats >= 0),
	PRIMARY KEY(code),
	FOREIGN KEY(code) REFERENCES Courses(code)
);

CREATE TABLE Classifications
(
	name TEXT NOT NULL,
	PRIMARY KEY(name)
);

CREATE TABLE StudentBranches
(
	student CHAR(10) NOT NULL,
	branch TEXT NOT NULL,
	program TEXT NOT NULL,
	PRIMARY KEY(student),
	FOREIGN KEY (student) REFERENCES Students(idnr),
	FOREIGN KEY(branch, program) REFERENCES Branches(branch_name, program)
);

CREATE TABLE Classified
(

	course CHAR(6) NOT NULL,
	classifications TEXT NOT NULL,
	PRIMARY KEY(course, classifications),
	FOREIGN KEY(course) REFERENCES Courses(code),
	FOREIGN KEY(classifications) REFERENCES Classifications(name)
);

CREATE TABLE MandatoryProgram
(
	course CHAR(6) NOT NULL,
	program TEXT NOT NULL,
	PRIMARY KEY(course, program),
	FOREIGN KEY(course) REFERENCES Courses(code)
);

CREATE TABLE MandatoryBranch
(
	course CHAR(6) NOT NULL,
	branch TEXT NOT NULL,
	program TEXT NOT NULL,
	PRIMARY KEY(course, branch, program),
	FOREIGN KEY(course) REFERENCES Courses(code),
	FOREIGN KEY(branch, program) REFERENCES Branches(branch_name, program)
);

CREATE TABLE RecommendedBranch
(
	course CHAR(6) NOT NULL,
	branch TEXT NOT NULL,
	program TEXT NOT NULL,
	PRIMARY KEY(course, branch, program),
	FOREIGN KEY(course) REFERENCES Courses(code),
	FOREIGN KEY(branch, program) REFERENCES Branches(branch_name, program)
);

CREATE TABLE Registered
(
	student CHAR(10) NOT NULL,
	course CHAR(6) NOT NULL,
	PRIMARY KEY(student, course),
	FOREIGN KEY(student) REFERENCES Students(idnr),
	FOREIGN KEY(course) REFERENCES Courses(code)
);

CREATE TABLE Taken
(
	student CHAR(10) NOT NULL,
	course CHAR(6) NOT NULL,
	grade CHAR(1),
	CHECK (grade IN ('U','3','4','5')),
	PRIMARY KEY(student, course),
	FOREIGN KEY(student) REFERENCES Students(idnr),
	FOREIGN KEY(course) REFERENCES Courses(code)
);

CREATE TABLE WaitingList
(
	student CHAR(10) NOT NULL,
	course CHAR(6) NOT NULL,
	position SERIAL NOT NULL,
	PRIMARY KEY(student, course),
	FOREIGN KEY(student) REFERENCES Students(idnr),
	FOREIGN KEY(course) REFERENCES LimitedCourses(code)
)

;