-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS enrollment;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS program;

CREATE TABLE student (
	num INTEGER PRIMARY KEY,  -- Student number
	name TEXT NOT NULL			  -- Student name
);

CREATE TABLE program (
	id INTEGER PRIMARY KEY,	-- Program id
	name TEXT NOT NULL			-- Program name (e.g. MIEIC)
);

CREATE TABLE course (
	ref INTEGER PRIMARY KEY,                -- Course reference
	name TEXT NOT NULL,                     -- Course name
	id INTEGER NOT NULL REFERENCES program  -- Program to which it belongs
);

CREATE TABLE enrollment (
	num INTEGER REFERENCES student, -- This student is enrolled
	ref INTEGER REFERENCES course,	-- in this course
	year INTEGER,				            -- year (2007 means 2007/08)
	grade1 INTEGER,   		 	        -- Grade after first exam
	grade2 INTEGER, 			          -- Grade after second exam
	PRIMARY KEY (num, ref, year)    -- Students cannot enroll twice in the same course and year
);

INSERT INTO student (num, name) VALUES (1, 'Jacob');
INSERT INTO student (num, name) VALUES (2, 'William');
INSERT INTO student (num, name) VALUES (3, 'Olivia');
INSERT INTO student (num, name) VALUES (4, 'Alexander');
INSERT INTO student (num, name) VALUES (5, 'Michael');
INSERT INTO student (num, name) VALUES (6, 'Charlotte');
INSERT INTO student (num, name) VALUES (7, 'Emily');
INSERT INTO student (num, name) VALUES (8, 'Emma');
INSERT INTO student (num, name) VALUES (9, 'Noah');
INSERT INTO student (num, name) VALUES (10, 'Isabella');
INSERT INTO student (num, name) VALUES (11, 'Benjamin');

INSERT INTO program (id, name) VALUES(1, 'MIEEC');
INSERT INTO program (id, name) VALUES(2, 'MIEIC');
INSERT INTO program (id, name) VALUES(3, 'MM');

INSERT INTO course (ref, name, id) VALUES (1, 'SINF', 1);
INSERT INTO course (ref, name, id) VALUES (2, 'SIBD', 1);
INSERT INTO course (ref, name, id) VALUES (3, 'SIEM', 1);
INSERT INTO course (ref, name, id) VALUES (4, 'BDAD', 2);
INSERT INTO course (ref, name, id) VALUES (5, 'LTW',  2);
INSERT INTO course (ref, name, id) VALUES (6, 'TW',   3);

INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (1, 1, 2007, 10, 14);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (2, 1, 2007, 8, 12);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (3, 1, 2007, NULL, 16);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (4, 1, 2007, 14, 12);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (5, 1, 2007, 15, NULL);

INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (6, 1, 2008, 14, 14);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (7, 1, 2008, 14, 12);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (8, 1, 2008, 12, NULL);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (9, 1, 2008, 14, 8);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (10, 1, 2008, 12, 14);

INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (1, 2, 2007, 11, NULL);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (2, 2, 2007, 8, 9);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (3, 2, 2007, 12, 14);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (4, 2, 2007, 15, 19);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (5, 2, 2007, 12, 13);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (11, 2, 2007, 15, 12);

INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (6, 2, 2008, 13, 11);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (7, 2, 2008, 11, 13);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (8, 2, 2008, 12, 16);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (9, 2, 2008, NULL, 8);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (10, 2, 2008, NULL, NULL);

INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (6, 3, 2007, 14, 12);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (2, 3, 2007, 11, 12);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (4, 3, 2007, 19, NULL);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (5, 3, 2007, 13, 14);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (7, 3, 2007, 14, 10);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (11, 3, 2007, 9, 9);

INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (1, 3, 2008, 9, 11);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (3, 3, 2008, 15, 18);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (8, 3, 2008, 14, 12);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (9, 3, 2008, 13, NULL);
INSERT INTO enrollment (num, ref, year, grade1, grade2) VALUES (10, 3, 2008, 12, NULL);
