drop table util ;
drop table projet ;
drop table piece ;
drop table etudiant ;

create table projet
        (P_CODE_PROJ char(4) PRIMARY KEY,
        P_NOM char(30),
        P_VILLE_REAL char(20));

create table piece
        (P_CODE_PIECE char(2) PRIMARY KEY,
        P_NOM char(20),
        P_COULEUR char(10),
        P_POIDS numeric(5,0),
        P_VILLE_PROC char(20),
        P_PRIX numeric(10,2));

create table etudiant
        (E_NUM_ETUD char(4) PRIMARY KEY,
        E_NOM char(30),
        E_PRENOM char(10),
        E_VILLE char(20),
        E_ANNEE char(4));

create table util
        (utilisateur char(4) REFERENCES etudiant,
        piece char(2) REFERENCES piece,
        destination char(4) REFERENCES projet ,
        U_QUANTITE numeric(6,0),
	PRIMARY KEY(utilisateur,piece,destination));

insert into piece VALUES ('P1','BOULON 6 * 25','NOIR',23,'LILLE',1.25);
insert into piece VALUES ('P2','ECROU 6','NOIR',3,'LILLE',.25);
insert into piece VALUES ('P3','BOULON 6 * 30','GRIS',34,'DOUAI',1.55);
insert into piece VALUES ('P4','BOULON 4 * 30','ARGENT',20,'DOUAI',1.45);
insert into piece VALUES ('P5','ECROU 5','ARGENT',2.5,'ARRAS',.75);
insert into piece VALUES ('P6','ECROU 7','OR',2.5,'ARRAS',.75);
insert into piece VALUES ('P7','BOUGIE','BLANC',100,'VILLENEUVE DASCQ',16.8);
insert into piece VALUES ('P8','RESISTANCE','JAUNE',5,'ARRAS',5.75);

insert into projet VALUES ('PJ01','MOTEUR TURBO','LILLE');
insert into projet VALUES ('PJ02','MOTEUR ATMOSPHERIQUE','ARRAS');
insert into projet VALUES ('PJ03','BOITE DE VITESSES','DOUAI');
insert into projet VALUES ('PJ04','REACTEUR','LILLE');

insert into etudiant VALUES ('8901','LECLERCQ','HERVE','LILLE','IMA1');
insert into etudiant VALUES ('8912','ALLAVOINE','ISABELLE','DOUAI','IMA3');
insert into etudiant VALUES ('8956','HOANG','MINH','VILLENEUVE DASCQ','IMA2');
insert into etudiant VALUES ('8833','MARTIN','SYLVIE','DOUAI','IMA1');
insert into etudiant VALUES ('8834','MARTIN','STEPHANE','DOUAI','IMA1');
insert into etudiant VALUES ('8902','DUBOIS','PHILIPPE','LILLE','IMA3');
insert into etudiant VALUES ('8932','DUPOND','CATHERINE','ARRAS','IMA2');
insert into etudiant VALUES ('8954','DURAND','FREDERIC','ARRAS','IMA1');
insert into etudiant VALUES ('8755','ASSET','PIERRE','LILLE','IMA3');
insert into etudiant VALUES ('8999','ANQUET','PAUL','LILLE','IMA1');

insert into util VALUES ('8834','P1','PJ04',9000);
insert into util VALUES ('8912','P2','PJ03',100);
insert into util VALUES ('8834','P7','PJ02',34);
insert into util VALUES ('8833','P3','PJ04',6);
insert into util VALUES ('8956','P4','PJ01',67);
insert into util VALUES ('8902','P2','PJ03',9);
insert into util VALUES ('8932','P1','PJ02',10);
insert into util VALUES ('8954','P5','PJ01',100);
insert into util VALUES ('8755','P6','PJ03',56);
insert into util VALUES ('8999','P4','PJ03',12);
insert into util VALUES ('8901','P2','PJ01',34);
insert into util VALUES ('8833','P2','PJ01',88);
insert into util VALUES ('8902','P7','PJ01',5);
insert into util VALUES ('8999','P6','PJ02',45);
insert into util VALUES ('8999','P1','PJ01',12);
insert into util VALUES ('8999','P7','PJ04',87);
insert into util VALUES ('8999','P1','PJ03',34);
insert into util VALUES ('8901','P1','PJ01',500);
insert into util VALUES ('8901','P1','PJ03',90);
insert into util VALUES ('8901','P5','PJ01',10);

-- GRANT SELECT ON projet, piece, etudiant,util to PUBLIC
