Bases de Données / Databases

DROP TABLE Employe IF EXISTS cascade constraints;
DROP TABLE Projet IF EXISTS cascade constraints;
DROP TABLE Embauche IF EXISTS cascade constraints;
DROP TABLE Grille_sal IF EXISTS cascade constraints;
 
CREATE TABLE Employe(       
			NumSS	NUMBER(5)		, /*contrainte D1*/       
			NomE  	varchar2(20) NOT NULL	, /*contrainte D2*/       
			PrenomE	varchar2(20) NOT NULL	, /*contrainte D2*/       
			VilleE	varchar2(20) NOT NULL	, /*contrainte D2*/       
			DateNaiss DATE 	     NOT NULL	,      
			dateCour  DATE	     DEFAULT sysdate,
					/*attribut superflu utile pour exprimer la contrainte C1*/       
			/*contraintes de cle et referentielles*/       
			CONSTRAINT pk_emp
                		   PRIMARY KEY (NumSS),      
			/*contraintes generales*/       
			CONSTRAINT limite_age       		  
                		   CHECK ((datediff(YEAR,DateNaiss, dateCour))<=70.0), 
			/*contraintes de domaine*/      
			CONSTRAINT format_nss  
                		   CHECK(LENGTH(NumSS)=5),
			CONSTRAINT ville_emp      
                		   CHECK (LOWER(villeE) IN ('paris', 'lyon', 'marseille')) 		 
			); 
 
 
 
CREATE  TABLE Projet(      
			NumProj	NUMBER(3),
			NomProj	varchar2(20)	NOT NULL,
			RespProj	NUMBER	NOT NULL,       
			VilleP	varchar2(20)	NOT NULL,       
			Budget		NUMBER,       
			/*contraintes de clés*/
			CONSTRAINT pk_proj       		  
				PRIMARY KEY(numproj),       
			CONSTRAINT fk_resp       		  
				FOREIGN KEY (RespProj) REFERENCES Employe ON DELETE cascade,               
			CONSTRAINT ville_proj       		  
				CHECK (LOWER(villeP) IN ('paris', 'lyon', 'marseille')),       
			CONSTRAINT limite_budget       		  
				CHECK (budget <1000000) 		  
		); 
 
CREATE TABLE Grille_sal(       
			profil	varchar2(20),       
			salaire	NUMBER(7,2)	NOT NULL,            
			 /*contraintes de clés*/
			CONSTRAINT pk_sal  
                PRIMARY KEY (profil),
			 /*contraintes de domaine*/
			CONSTRAINT sal_max 
                CHECK (salaire<90000) 
			); 
 
 
CREATE TABLE Embauche(       
			NumSS	NUMBER ,
			NumProj	NUMBER ,       
			DateEmb	DATE DEFAULT sysdate NOT NULL,       
			Profil	varchar2(20) NOT NULL,       
			/*contraintes de clés*/
     			CONSTRAINT pk_emb PRIMARY KEY (NumSS, NumProj),
			CONSTRAINT fk_emb_emp 
               			    FOREIGN KEY (NumSS) REFERENCES Employe ON DELETE cascade,
			CONSTRAINT fk_emb_proj 
               			    FOREIGN KEY (NumProj) REFERENCES Projet ON DELETE cascade,
			CONSTRAINT fk_emb_sal 
               			    FOREIGN KEY (Profil) REFERENCES Grille_sal ON DELETE cascade	
			);