alter session set NLS_DATE_FORMAT='DD-MM-YYYY'; CREATE TABLE Personne ( idpers INTEGER NOT NULL, nom VARCHAR2 (30) NOT NULL, prenom VARCHAR2 (30) NOT NULL, datenaiss VARCHAR2 (30) NOT NULL, numtel INTEGER, CONSTRAINT pk_pers PRIMARY KEY(idpers), UNIQUE(nom,prenom,datenaiss) ); INSERT INTO Personne VALUES (1,'Scott','Ridley','1978-05-25',0477184759); INSERT INTO Personne VALUES (2,'Hitchcock','Alfred','1979-11-25',0498564785); INSERT INTO Personne VALUES (3,'Kurosawa','Akira','1983-04-25',0456258974); INSERT INTO Personne VALUES (4,'Woo','John','1982-09-25',0487546984); INSERT INTO Personne VALUES (5,'Tarantino','Quentin','1975-09-25',0472654123); INSERT INTO Personne VALUES (6,'Cameron','James','1963-10-25',0478954631); CREATE TABLE Type ( numtp INTEGER NOT NULL, type VARCHAR2 (15), description VARCHAR2 (40), chargeforf INTEGER NOT NULL, CONSTRAINT pk_type PRIMARY KEY(numtp) ); INSERT INTO Type VALUES (1 ,'T1','1 piece',50); INSERT INTO Type VALUES (2 ,'T2','2 piece',55); INSERT INTO Type VALUES (3 ,'T3','3 piece',60); INSERT INTO Type VALUES (4 ,'studio','1 piece',55); INSERT INTO Type VALUES (5 ,'T4','4 piece',65); INSERT INTO Type VALUES (6 ,'T5','5 piece',70); INSERT INTO Type VALUES (7 ,'T6','6 et plus',70); CREATE TABLE Commune ( idcom INTEGER NOT NULL, nomcom VARCHAR2 (30) NOT NULL, distagenc INTEGER, nbrlog INTEGER, CONSTRAINT pk_commune PRIMARY KEY(idcom) ); INSERT INTO Commune VALUES (1,'aiseau',5,5); INSERT INTO Commune VALUES (2,'chatelet',23,15); INSERT INTO Commune VALUES (3,'namur',10,2); INSERT INTO Commune VALUES (4,'courcelle',15,10); INSERT INTO Commune VALUES (5,'marche',9,5); INSERT INTO Commune VALUES (6,'farciennes',42,7); INSERT INTO Commune VALUES (7,'charleroi',65,0); INSERT INTO Commune VALUES (8,'couillet',34,0); INSERT INTO Commune VALUES (9,'baulet',20,2); INSERT INTO Commune VALUES (10,'fleurus',22,5); CREATE TABLE Quartier( idquart INTEGER NOT NULL, nomquart VARCHAR2 (30) NOT NULL, idcom INTEGER NOT NULL, CONSTRAINT pk_quart PRIMARY KEY (idquart) ON DELETE CASCADE, CONSTRAINT fk_quart FOREIGN KEY (idcom) REFERENCES Commune ON DELETE CASCADE ); INSERT INTO Quartier VALUES (1 ,'rue des fagnes' ,1); INSERT INTO Quartier VALUES (2 ,'rue des iblis' ,1); INSERT INTO Quartier VALUES (3 ,'rue des payday' ,1); INSERT INTO Quartier VALUES (4 ,'rue de la roche' ,1); INSERT INTO Quartier VALUES (5 ,'rue des bacheres' ,10); INSERT INTO Quartier VALUES (6 ,'rue des etriers' ,10); INSERT INTO Quartier VALUES (7 ,'avenue brouqueville' ,10); INSERT INTO Quartier VALUES (8 ,'avenue des garces' ,10); INSERT INTO Quartier VALUES (9 ,'rue de la gare' ,9); INSERT INTO Quartier VALUES (10 ,'rue lambot' ,9); INSERT INTO Quartier VALUES (11 ,'rue joseph bolle' ,9); INSERT INTO Quartier VALUES (12 ,'avenue massart' ,7); INSERT INTO Quartier VALUES (13 ,'rue neuve' ,7); INSERT INTO Quartier VALUES (14 ,'rue des combatants' ,7); INSERT INTO Quartier VALUES (15 ,'rue d oignies' ,7); INSERT INTO Quartier VALUES (16 ,'rue de la station' ,2); INSERT INTO Quartier VALUES (17 ,'rue armand boucquet' ,2); INSERT INTO Quartier VALUES (18 ,'rue de la liberte' ,2); INSERT INTO Quartier VALUES (19 ,'rue des joncquilles' ,4); INSERT INTO Quartier VALUES (20 ,'avenue des roses' ,4); INSERT INTO Quartier VALUES (21 ,'rue des enclos' ,4); INSERT INTO Quartier VALUES (22 ,'rue d auvelais' ,3); INSERT INTO Quartier VALUES (23 ,'avenue de basker' ,3); INSERT INTO Quartier VALUES (24 ,'avenue des mailleux' ,3); INSERT INTO Quartier VALUES (25 ,'rue de falisole' ,5); INSERT INTO Quartier VALUES (26 ,'avenue des poissons' ,5); INSERT INTO Quartier VALUES (27 ,'rue des marcheurs' ,5); INSERT INTO Quartier VALUES (28 ,'rue des roches' ,6); INSERT INTO Quartier VALUES (29 ,'avenue des baleines' ,6); INSERT INTO Quartier VALUES (30 ,'rue albert fred' ,6); INSERT INTO Quartier VALUES (31 ,'avenues des rois' ,8); INSERT INTO Quartier VALUES (32 ,'rue de dejjal' ,8); INSERT INTO Quartier VALUES (33 ,'avenue des djins' ,8); INSERT INTO Quartier VALUES (34 ,'rue du centre' ,8); INSERT INTO Quartier VALUES (35 ,'rue de la tayette' ,6); CREATE TABLE Logement ( idlog INTEGER NOT NULL, superficie INTEGER NOT NULL, loyer INTEGER NOT NULL, numtp INTEGER NOT NULL, idquart INTEGER NOT NULL, CONSTRAINT pk_log PRIMARY KEY (idlog), CONSTRAINT fk_log1 FOREIGN KEY(numtp) REFERENCES Type ON DELETE CASCADE, CONSTRAINT fk_log2 FOREIGN KEY(idquart) REFERENCES Quartier ON DELETE CASCADE ); INSERT INTO Logement VALUES (1,120,640,5,4); INSERT INTO Logement VALUES (2,17,250,4,2); INSERT INTO Logement VALUES (3,70,470,3,9); CREATE TABLE Location ( idlog INTEGER NOT NULL, idpers INTEGER NOT NULL, numcon VARCHAR2 (30) UNIQUE, datedbt DATE, datefin DATE, datesig DATE, CONSTRAINT pk_loca PRIMARY KEY (idlog, idpers) PRIMARY KEY(idlog,idpers), CONSTRAINT fk_loca1 FOREIGN KEY(idlog) REFERENCES Location ON DELETE CASCADE, CONSTRAINT fk_loca2 FOREIGN KEY(idpers) REFERENCES Personne ON DELETE CASCADE ); INSERT INTO Location VALUES (1,3,'ref7','2007-12-21','20-12-2009','02-12-2007'); INSERT INTO Location VALUES (2,2,'ref6','2006-02-02','2010-02-01','2006-01-01'); INSERT INTO Location VALUES (3,1,'ref5','2007-02-21','2010-02-20','2007-01-20'); SELECT * FROM Personne; SELECT * FROM Logement; SELECT * FROM Location; SELECT * FROM Quartier; SELECT * FROM Commune;