CREATE TABLE Examen(
EID SERIAL PRIMARY KEY,
Date date NOT NULL,
Heure time NOT NULL,
CONSTRAINT UN_Examen UNIQUE(Date,Heure)
) ;

CREATE TABLE Exercice(
ExoID integer,
ExamID integer,
Sujet varchar(255) NOT NULL,
CONSTRAINT PK_Exercice PRIMARY KEY (ExoID, ExamID),
CONSTRAINT FK_Exercice FOREIGN KEY (ExamID) REFERENCES Examen(EID),
 CONSTRAINT UN_Exercice UNIQUE(Sujet)
) ;

CREATE TABLE Salle(
  SID serial,
  Capacite integer NOT NULL,
  CONSTRAINT PK_Salle PRIMARY KEY (SID)
  );
  
CREATE TABLE Amphi ( 
  AID integer,
  Numero integer NOT NULL UNIQUE,
  Etage integer NOT NULL,
  CONSTRAINT PK_Amphi PRIMARY KEY (AID),
  CONSTRAINT FK_Amphi FOREIGN KEY (AID) REFERENCES Salle(SID)
  );
  
  CREATE TABLE SalleDeCours ( 
  SID integer,
  Numero integer NOT NULL,
  Batiment varchar(1) NOT NULL,
  CONSTRAINT PK_SalleDeCours PRIMARY KEY (SID),
  CONSTRAINT FK_SalleDeCours FOREIGN KEY (SID) REFERENCES Salle(SID),
  CONSTRAINT UN_SalleDeCours UNIQUE (Numero,Batiment)
  );
  
  CREATE TABLE ExamenSalle ( 
  SID integer,
  EID integer,
  CONSTRAINT PK_ExamenSalle PRIMARY KEY (EID,SID),
  CONSTRAINT FK_ExamenSalle_EID FOREIGN KEY (EID) REFERENCES Examen(EID),
  CONSTRAINT FK_ExamenSalle_SID FOREIGN KEY (SID) REFERENCES Salle(SID)
  );


INSERT INTO Examen(Date,Heure) VALUES('2020/10/14','17:00:00');
INSERT INTO Examen(Date,Heure) VALUES('2020/10/14','17:15:00');
INSERT INTO Exercice VALUES(1,1,'definition de cle primaire');
INSERT INTO Exercice VALUES(2,1,'que veut dire SELECT ?');
INSERT INTO Exercice VALUES(1,2,'definition de cle etrangere');
INSERT INTO Salle(Capacite) VALUES(50);
INSERT INTO Salle(Capacite) VALUES(800);
INSERT INTO Amphi VALUES(2,8,2);
INSERT INTO SalleDeCours VALUES(1,204,'A');