CREATE TABLE T_EMPLOYES (
	EMP_ID INTEGER IDENTITY NOT NULL,
	SAL_NOM VARCHAR (32) NOT NULL,
	SAL_PRENOM VARCHAR (32) NOT NULL,
	CONSTRAINT PK_EMP_ID PRIMARY KEY (EMP_ID)
);

CREATE TABLE T_CLIENTS (
	CLI_ID INTEGER IDENTITY NOT NULL,
	CLI_NOM VARCHAR (32) NOT NULL,
	CLI_PRENOM VARCHAR (32) NOT NULL,
	CONSTRAINT PK_CLI_ID PRIMARY KEY (CLI_ID)
);

CREATE TABLE T_AFFECTATIONS (
	AFF_ID INTEGER IDENTITY NOT NULL, 
	EMP_ID INTEGER NOT NULL,
	CLI_ID INTEGER NOT NULL,
	AFF_HR DECIMAL (6,2) NOT NULL,
	CONSTRAINT PK_AFF_ID PRIMARY KEY (AFF_ID),
	CONSTRAINT FK_AFF_EMP FOREIGN KEY (EMP_ID) REFERENCES T_EMPLOYES (EMP_ID),
	CONSTRAINT FK_AFF_CLI FOREIGN KEY (CLI_ID) REFERENCES T_CLIENTS (CLI_ID)
);

CREATE TABLE T_JOURS (
	JOU_ID INTEGER NOT NULL,
	JOU_LABEL VARCHAR (10) NOT NULL,
	CONSTRAINT PK_JOU_ID PRIMARY KEY (JOU_ID)
);

CREATE TABLE T_HEURES (
	HEU_ID INTEGER NOT NULL,
	HEU_HR TIME NOT NULL,
	CONSTRAINT PK_HEU_ID PRIMARY KEY (HEU_ID)
);

CREATE TABLE T_PLANNINGS (
	PLA_ID INTEGER IDENTITY NOT NULL,
	EMP_ID INTEGER NOT NULL,
	CONSTRAINT PK_PLA_ID PRIMARY KEY (PLA_ID),
	CONSTRAINT FK_PLA_EMP FOREIGN KEY (EMP_ID) REFERENCES T_EMPLOYES (EMP_ID),
	CONSTRAINT UK_PLA_EMP UNIQUE (EMP_ID)
);

CREATE TABLE T_CRENEAUX (
	CRE_ID INTEGER IDENTITY NOT NULL,
	PLA_ID INTEGER NOT NULL,
	CLI_ID INTEGER NOT NULL,
	JOU_ID INTEGER NOT NULL,
	CRE_DEBUT TIME NOT NULL,
	CRE_FIN TIME NOT NULL,
	CONSTRAINT PK_CRE_ID PRIMARY KEY (CRE_ID),
	CONSTRAINT FK_CRE_PLA FOREIGN KEY (PLA_ID) REFERENCES T_PLANNINGS (PLA_ID),
	CONSTRAINT FK_CRE_CLI FOREIGN KEY (CLI_ID) REFERENCES T_CLIENTS (CLI_ID),
	CONSTRAINT FK_CRE_JOU FOREIGN KEY (JOU_ID) REFERENCES T_JOURS (JOU_ID),
	CONSTRAINT UK_PLA_JOU_DEB UNIQUE (PLA_ID, JOU_ID, CRE_DEBUT),
	CONSTRAINT UK_PLA_JOU_FIN UNIQUE (PLA_ID, JOU_ID, CRE_FIN),
	CONSTRAINT CK_DEB_FIN CHECK (CRE_FIN > CRE_DEBUT)
);

CREATE TABLE Produit (RefProduit INT, Designation VARCHAR(50) NOT NULL, Prix INT NOT NULL, 
CONSTRAINT Produit_PK PRIMARY KEY (RefProduit)) ;
CREATE TABLE Devis (NumDevis INT, DateDevis DATE NOT NULL, CONSTRAINT Devis_PK PRIMARY KEY (NumDevis)) ;
CREATE TABLE LigneDevis (NumDevis INT, RefProduit INT, Quantite INT NOT NULL, CONSTRAINT LigneDevis_PK PRIMARY KEY (NumDevis, RefProduit),
CONSTRAINT LigneDevisProduit_FK FOREIGN KEY (RefProduit) REFERENCES Produit,
CONSTRAINT LigneDevisDevis_FK FOREIGN KEY (NumDevis) REFERENCES Devis ON DELETE CASCADE) ;

 
VAR Produit BASE RELATION {RefProduit INT, Designation CHAR, Prix INT} KEY {RefProduit} ;
VAR Devis BASE RELATION {NumDevis INT, DateDevis CHAR} KEY {NumDevis} ;
VAR LigneDevis BASE RELATION {NumDevis INT, RefProduit CHAR, Quantite INT} KEY {NumDevis, RefProduit} ;

INSERT Produit RELATION 
  {  TUPLE {RefProduit 1, Designation "boulons de 8", Prix 100}
   , TUPLE {RefProduit 2, Designation "écrous", Prix 200}
  } ;

INSERT Devis RELATION 
  {TUPLE {NumDevis 1, DateDevis "d01"}} ;

   INSERT Devis RELATION 
  {
     TUPLE {NumDevis 1, DateDevis "d01"}
  }
 , 
   INSERT LigneDevis RELATION 
  {
     TUPLE {NumDevis 1, RefProduit 1, Quantite 100}
   , TUPLE {NumDevis 1, RefProduit 2, Quantite 150}
  }
 ;

DELETE Devis WHERE NumDevis = 1
, 
DELETE LigneDevis WHERE NumDevis = 1
;