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;                                             