-- V1.0.0

DROP VIEW IF EXISTS Vue1;
DROP VIEW IF EXISTS Vue2;
DROP VIEW IF EXISTS Vue3;
DROP VIEW IF EXISTS Vue4;

DROP TABLE IF EXISTS Evaluation;
DROP TABLE IF EXISTS Cours;
DROP TABLE IF EXISTS Salle;
DROP TABLE IF EXISTS Etudiant;
DROP TABLE IF EXISTS Intervenant;
DROP TABLE IF EXISTS Matiere;

CREATE TABLE Matiere (
	idm INTEGER NOT NULL PRIMARY KEY, 
	intitule CHAR(16) NOT NULL,
	nbs INTEGER NOT NULL
);

CREATE TABLE Intervenant (
	idi INTEGER NOT NULL PRIMARY KEY,
	nom VARCHAR(16) NOT NULL,
	prenom VARCHAR(14), 
	statut CHAR(1),
	CHECK (statut IN ('P', 'V'))
);

CREATE TABLE Etudiant (
	ide INTEGER NOT NULL PRIMARY KEY,
	nom VARCHAR(16) NOT NULL,
	prenom VARCHAR(14), 
	groupe CHAR(2) NOT NULL,
	CHECK (groupe IN ('G1', 'G2', 'G3'))
);

CREATE TABLE Salle (
	nos VARCHAR(5) NOT NULL PRIMARY KEY, 
	typs SMALLINT,
	CHECK (typs IN (1, 2)),
	contenance INTEGER
);

CREATE TABLE Cours (
	idm INTEGER NOT NULL, 
	nums INTEGER NOT NULL, 
	idi INTEGER NOT NULL, 
	PRIMARY KEY (idm, nums, idi),
	FOREIGN KEY (idm) REFERENCES Matiere (idm),
	FOREIGN KEY (idi) REFERENCES Intervenant (idi),
	nos VARCHAR(5) NOT NULL, 
	FOREIGN KEY (nos) REFERENCES Salle (nos),
	groupe CHAR(2) NOT NULL, 
	dates DATE, 
	phor CHAR(2),
	CHECK (phor IN ('AM', 'PM'))
);

CREATE TABLE Evaluation (
	idm INTEGER NOT NULL, 
	ide INTEGER NOT NULL, 
	PRIMARY KEY (idm, ide),
	FOREIGN KEY (idm) REFERENCES Matiere (idm),
	FOREIGN KEY (ide) REFERENCES Etudiant (ide),
	note FLOAT
);