BEGIN
FOR c IN (SELECT TABLE_NAME FROM user_tables) loop
EXECUTE immediate ('drop table '||c.TABLE_NAME||' cascade constraints');
END loop;
END;
/
BEGIN
FOR c IN (SELECT * FROM user_objects) loop
EXECUTE immediate ('drop '||c.object_type||' '||c.object_name);
END loop;
END;
/

-- Tables


CREATE TABLE Departments (
dabbreviation VARCHAR(10),
dname VARCHAR(30),
UNIQUE (dabbreviation),
PRIMARY KEY (dname)
);
--hur gör vi denna unique?

CREATE TABLE Programmes (
pabbreviation VARCHAR(3),
pname VARCHAR(30),
PRIMARY KEY (pname)
);

CREATE TABLE Branches (
programme REFERENCES programmes(pname),
bname VARCHAR(30),
PRIMARY KEY (bname, programme)
);

CREATE TABLE Students (
idnr INT,
sname VARCHAR(30),
cid VARCHAR(10),
programme REFERENCES programmes(pname),
UNIQUE (programme, idnr),
PRIMARY KEY (idnr)
);

CREATE TABLE Courses (
courseid VARCHAR(6),
cname VARCHAR(30),
credits INT,
CONSTRAINT NonNegativeCredits CHECK (credits > 0 ),
department REFERENCES departments(dname),
PRIMARY KEY(courseid)
);

CREATE TABLE LimitedCourses (
nrmaxstudents INT,
courseid REFERENCES courses(courseid),
CONSTRAINT NoNegativeSpace CHECK (nrmaxstudents > 0),
PRIMARY KEY (courseid)
);

CREATE TABLE classifications (
TYPE VARCHAR(30),
PRIMARY KEY (TYPE)
);

--RELATIONER--

CREATE TABLE hosts (
department REFERENCES departments(dname),
programme REFERENCES programmes(pname),
PRIMARY KEY (department, programme)
);

CREATE TABLE READ (
course REFERENCES courses(courseid),
student REFERENCES students(idnr),
grade VARCHAR(1),
CONSTRAINT PossibleGrades CHECK (grade IN ('U',3,4,5)),
PRIMARY KEY(course, student)
);

CREATE TABLE isType (
classification REFERENCES classifications(TYPE),
course REFERENCES courses(courseid),
PRIMARY KEY (classification, course)
);

CREATE TABLE waiting (
student REFERENCES students(idnr),
limitedCourse REFERENCES limitedCourses(courseid),
nrqueue INT,
UNIQUE(nrqueue, limitedCourse),
CONSTRAINT NoNegativeNrQueue CHECK (nrqueue > 0),
PRIMARY KEY (student, limitedCourse)
);

--CREATE TABLE regBranch (
--student REFERENCES students(idnr),
--FOREIGN KEY (branch, programme) REFERENCES branches(bname, programme),
--FOREIGN KEY (student, programme) REFERENCES students(idnr, programme),
--PRIMARY KEY (student)
--);
--BRANCHES är tokig? VARFÖR?

CREATE TABLE regCourse (
student REFERENCES students(idnr),
course REFERENCES courses(courseid),
PRIMARY KEY (student, course)
);

CREATE TABLE programmeMandatory (
programme REFERENCES programmes(pname),
course REFERENCES courses(courseid),
PRIMARY KEY (programme, course)
);
--
--CREATE TABLE branchMandatory (
--FOREIGN KEY (branch, programme) REFERENCES branches(bname, programme),
--course REFERENCES courses(courseid),
--PRIMARY KEY (branch, programme, course)
--);

--CREATE TABLE branchRecommended (
--FOREIGN KEY (branch, programme) REFERENCES branches(bname, programme),
--course REFERENCES courses(courseid),
--PRIMARY KEY (branch, programme, course)
--);

CREATE TABLE prerequisites (
required REFERENCES courses(courseid),
notrequired REFERENCES courses(courseid),
PRIMARY KEY (required, notrequired)
);




--INSERT
--PROGRAMME
INSERT INTO Programmes
  VALUES ('I', 'Industriell Ekonomi');
INSERT INTO Programmes
  VALUES ('V', 'Väg och Vatten');
INSERT INTO Programmes
  VALUES ('F', 'Fysik');
INSERT INTO Programmes
  VALUES ('D', 'Data');

--Branches

INSERT INTO Branches
  VALUES ('Industriell Ekonomi', 'IT');
INSERT INTO Branches
  VALUES ('Industriell Ekonomi', 'Kemi');
INSERT INTO Branches
  VALUES ('Väg och Vatten', 'IT');
INSERT INTO Branches
  VALUES ('Väg och Vatten', 'Geo');
INSERT INTO Branches
  VALUES ('Väg och Vatten', 'Båtar');
INSERT INTO Branches
  VALUES ('Fysik', 'IT');
INSERT INTO Branches
  VALUES ('Fysik', 'Matte');
INSERT INTO Branches
  VALUES ('Fysik', 'Heisenberg');
INSERT INTO Branches
  VALUES ('Data', 'Matte');
INSERT INTO Branches
  VALUES ('Data', 'Databaser');

--Students
INSERT INTO Students
  VALUES ('9309206341', 'Amanda Axman', 'AXMANA', 'Industriell Ekonomi');
INSERT INTO Students
  VALUES ('9309206342', 'Amanda Bxman', 'AXMBNA', 'Väg och Vatten');
INSERT INTO Students
  VALUES ('9309206343', 'Amanda Cxman', 'AXMCNA', 'Fysik');
INSERT INTO Students
  VALUES ('9309206344', 'Amanda Dxman', 'AXMDNA', 'Data');

--Departments
INSERT INTO Departments
  VALUES ('DA','DepartementA');
INSERT INTO Departments
  VALUES ('DB','DepartementB');
INSERT INTO Departments
  VALUES ('DC','DepartementC');

--Courses
INSERT INTO Courses
  VALUES ('TDA357', 'Databaser A', 15, 'DepartementA');
INSERT INTO Courses
  VALUES ('TDA457', 'Databaser B', 15,'DepartementA');
INSERT INTO Courses
  VALUES ('TDA666', 'Databaser C', 25, 'DepartementB');
INSERT INTO Courses
  VALUES ('ADS999', 'Databaser F', 5, 'DepartementB');
INSERT INTO Courses
  VALUES ('FDS123', 'Databaser T', 3, 'DepartementC');

--Classification
INSERT INTO Classifications
  VALUES ('Matematik');
INSERT INTO Classifications
  VALUES ('Fysik');
INSERT INTO Classifications
  VALUES ('Kemi');
INSERT INTO Classifications
  VALUES ('Ekonomi');

 --
--Limited Courses



BEGIN
FOR c IN (SELECT TABLE_NAME FROM user_tables) loop
EXECUTE immediate ('drop table '||c.TABLE_NAME||' cascade constraints');
END loop;
END;
/
BEGIN
FOR c IN (SELECT * FROM user_objects) loop
EXECUTE immediate ('drop '||c.object_type||' '||c.object_name);
END loop;
END;
/

-- Tables


CREATE TABLE Departments (
dabbreviation VARCHAR(10),
dname VARCHAR(30),
UNIQUE (dabbreviation),
PRIMARY KEY (dname)
);
--hur gör vi denna unique?

CREATE TABLE Programmes (
pabbreviation VARCHAR(3),
pname VARCHAR(30),
PRIMARY KEY (pname)
);

CREATE TABLE Branches (
programme REFERENCES programmes(pname),
bname VARCHAR(30),
PRIMARY KEY (bname, programme)
);

CREATE TABLE Students (
idnr INT,
sname VARCHAR(30),
cid VARCHAR(10),
programme REFERENCES programmes(pname),
UNIQUE (programme, idnr),
PRIMARY KEY (idnr)
);

CREATE TABLE Courses (
courseid VARCHAR(6),
cname VARCHAR(30),
credits INT,
CONSTRAINT NonNegativeCredits CHECK (credits > 0 ),
department REFERENCES departments(dname),
PRIMARY KEY(courseid)
);

CREATE TABLE LimitedCourses (
nrmaxstudents INT,
courseid REFERENCES courses(courseid),
CONSTRAINT NoNegativeSpace CHECK (nrmaxstudents > 0),
PRIMARY KEY (courseid)
);

CREATE TABLE classifications (
TYPE VARCHAR(30),
PRIMARY KEY (TYPE)
);

--RELATIONER--

CREATE TABLE hosts (
department REFERENCES departments(dname),
programme REFERENCES programmes(pname),
PRIMARY KEY (department, programme)
);

CREATE TABLE READ (
course REFERENCES courses(courseid),
student REFERENCES students(idnr),
grade VARCHAR(1),
CONSTRAINT PossibleGrades CHECK (grade IN ('U',3,4,5)),
PRIMARY KEY(course, student)
);

CREATE TABLE isType (
classification REFERENCES classifications(TYPE),
course REFERENCES courses(courseid),
PRIMARY KEY (classification, course)
);

CREATE TABLE waiting (
student REFERENCES students(idnr),
limitedCourse REFERENCES limitedCourses(courseid),
nrqueue INT,
UNIQUE(nrqueue, limitedCourse),
CONSTRAINT NoNegativeNrQueue CHECK (nrqueue > 0),
PRIMARY KEY (student, limitedCourse)
);

--CREATE TABLE regBranch (
--student REFERENCES students(idnr),
--FOREIGN KEY (branch, programme) REFERENCES branches(bname, programme),
--FOREIGN KEY (student, programme) REFERENCES students(idnr, programme),
--PRIMARY KEY (student)
--);
--BRANCHES är tokig? VARFÖR?

CREATE TABLE regCourse (
student REFERENCES students(idnr),
course REFERENCES courses(courseid),
PRIMARY KEY (student, course)
);

CREATE TABLE programmeMandatory (
programme REFERENCES programmes(pname),
course REFERENCES courses(courseid),
PRIMARY KEY (programme, course)
);
--
--CREATE TABLE branchMandatory (
--FOREIGN KEY (branch, programme) REFERENCES branches(bname, programme),
--course REFERENCES courses(courseid),
--PRIMARY KEY (branch, programme, course)
--);

--CREATE TABLE branchRecommended (
--FOREIGN KEY (branch, programme) REFERENCES branches(bname, programme),
--course REFERENCES courses(courseid),
--PRIMARY KEY (branch, programme, course)
--);

CREATE TABLE prerequisites (
required REFERENCES courses(courseid),
notrequired REFERENCES courses(courseid),
PRIMARY KEY (required, notrequired)
);




--INSERT
--PROGRAMME
INSERT INTO Programmes
  VALUES ('I', 'Industriell Ekonomi');
INSERT INTO Programmes
  VALUES ('V', 'Väg och Vatten');
INSERT INTO Programmes
  VALUES ('F', 'Fysik');
INSERT INTO Programmes
  VALUES ('D', 'Data');

--Branches

INSERT INTO Branches
  VALUES ('Industriell Ekonomi', 'IT');
INSERT INTO Branches
  VALUES ('Industriell Ekonomi', 'Kemi');
INSERT INTO Branches
  VALUES ('Väg och Vatten', 'IT');
INSERT INTO Branches
  VALUES ('Väg och Vatten', 'Geo');
INSERT INTO Branches
  VALUES ('Väg och Vatten', 'Båtar');
INSERT INTO Branches
  VALUES ('Fysik', 'IT');
INSERT INTO Branches
  VALUES ('Fysik', 'Matte');
INSERT INTO Branches
  VALUES ('Fysik', 'Heisenberg');
INSERT INTO Branches
  VALUES ('Data', 'Matte');
INSERT INTO Branches
  VALUES ('Data', 'Databaser');

--Students
INSERT INTO Students
  VALUES ('9309206341', 'Amanda Axman', 'AXMANA', 'Industriell Ekonomi');
INSERT INTO Students
  VALUES ('9309206342', 'Amanda Bxman', 'AXMBNA', 'Väg och Vatten');
INSERT INTO Students
  VALUES ('9309206343', 'Amanda Cxman', 'AXMCNA', 'Fysik');
INSERT INTO Students
  VALUES ('9309206344', 'Amanda Dxman', 'AXMDNA', 'Data');

--Departments
INSERT INTO Departments
  VALUES ('DA','DepartementA');
INSERT INTO Departments
  VALUES ('DB','DepartementB');
INSERT INTO Departments
  VALUES ('DC','DepartementC');

--Courses
INSERT INTO Courses
  VALUES ('IT101', 'Databaser A', 15, 'DepartementA');
INSERT INTO Courses
  VALUES ('MA101', 'MatteB', 15,'DepartementA');
INSERT INTO Courses
  VALUES ('FY101', 'Fysik C', 25, 'DepartementB');
INSERT INTO Courses
  VALUES ('EK101', 'Ekonomi F', 5, 'DepartementB');
INSERT INTO Courses
  VALUES ('KE101', 'Kemi T', 3, 'DepartementC');

--Classifications
INSERT INTO Classifications
  VALUES ('Matematik');
INSERT INTO Classifications
  VALUES ('Fysik');
INSERT INTO Classifications
  VALUES ('Kemi');
INSERT INTO Classifications
  VALUES ('Ekonomi');
INSERT INTO Classifications
  VALUES ('IT');

--isType
INSERT INTO isType
  VALUES ('Matematik', 'MA101');
INSERT INTO isType
  VALUES ('Fysik', 'FY101');
INSERT INTO isType
  VALUES ('Kemi', 'KE101');
INSERT INTO isType
  VALUES ('Ekonomi', 'EK101');
INSERT INTO isType
  VALUES ('IT', 'IT101');