--  Script de creation de la base des locations
-- 
-- pascal berthome
-- 05/02/2009
-- 25/01/2010
-- -----------------------------------------------

-- 
-- Table PERSONNE
CREATE TABLE PERSONNE(
       NumPersonne NUMBER CONSTRAINT PK_PERSONNE PRIMARY KEY,
       NomPersonne VARCHAR(20) CONSTRAINT NN_NomPers NOT NULL,
       PrenomPersonne VARCHAR(15) CONSTRAINT NN_PrenomPers NOT NULL,
       RuePersonne VARCHAR(50),
       CPPersonne CHAR(5),
       villePersonne VARCHAR(20));

-- Table AGENCE
CREATE TABLE AGENCE(
       NumAgence NUMBER CONSTRAINT PK_AGENCE PRIMARY KEY,
       Gerant NUMBER CONSTRAINT CIR_Agence_Personne REFERENCES PERSONNE,
       RueAgence VARCHAR(50),
       CPAgence CHAR(5),
       VilleAgence VARCHAR(20));

-- Table BIEN
CREATE TABLE BIEN(
       NumBien NUMBER CONSTRAINT PK_BIEN PRIMARY KEY,
       Proprio NUMBER CONSTRAINT NN_Proprio NOT NULL 
                      CONSTRAINT CIR_BIEN_PERSONNE  REFERENCES PERSONNE,
       NumAgence NUMBER CONSTRAINT NN_Agence NOT NULL 
                      CONSTRAINT CIR_BIEN_AGENCE REFERENCES AGENCE,
       Loyer NUMBER,
       TypeBien VARCHAR(10),
       Surface NUMBER,
       NBPieces NUMBER,
       RueBien VARCHAR(50),
       CPBien CHAR(5),
       VilleBien VARCHAR(20));

-- Table LOCATION
CREATE TABLE LOCATION(
       NumLocation NUMBER CONSTRAINT PK_LOCATION PRIMARY KEY,
       NumBien NUMBER CONSTRAINT NN_NumBien NOT NULL 
                      CONSTRAINT CIR_LOC_BIEN REFERENCES BIEN,
       NumLocataire NUMBER CONSTRAINT NN_Locataire NOT NULL 
                      CONSTRAINT CIR_LOC_PERSONNE REFERENCES PERSONNE,
       DebutLocation DATE CONSTRAINT NN_Debutloc NOT NULL,
       FinLocation DATE);

----------------------
--- Des Personnes ----
----------------------
INSERT INTO PERSONNE
VALUES
(1, 'Dupont', 'Alain', '11, Bld Lahitolle', 18000, 'BOURGES');

INSERT INTO PERSONNE
VALUES
(2, 'Durant', 'Alex', '12, Bld Lahitolle', 18000, 'BOURGES');

INSERT INTO PERSONNE
VALUES
(3, 'Daccord', 'Albert', '15, Bld Lahitolle', 18000, 'BOURGES');

INSERT INTO PERSONNE
VALUES
(10, 'Langevin', 'Paul', '20, Bld Lamarck', 18000, 'BOURGES');

INSERT INTO PERSONNE
VALUES
(11, 'Lacroix', 'Pascal', '27, Bld Lamarck', 18000, 'BOURGES');

INSERT INTO PERSONNE
VALUES
(12, 'Lebon', 'Philippe', '29, Bld Lamarck', 18000, 'BOURGES');


INSERT INTO PERSONNE
VALUES
(20, 'Gravet', 'Valérie', '5, allée des Accacias', 18000, 'BOURGES');

INSERT INTO PERSONNE
VALUES
(21, 'Grandet', 'Viviane', '5, allée des Pinsons', 18000, 'BOURGES');


INSERT INTO PERSONNE 
VALUES
(30, 'Placide', 'Roger', '17, Rue des Géraniums', 18000, 'BOURGES');


--------------------
--- Des Agences ----
--------------------
INSERT INTO AGENCE
VALUES
(100, 20, '61, avenue d''Orléans', 18000, 'BOURGES');

INSERT INTO AGENCE
VALUES
(101, 21, '16, avenue de Clermont-Ferrand', 18000, 'BOURGES');


------------------
--- Des Biens ----
------------------

--- Suite plus ou moins générée automatiquement

INSERT INTO "PERSONNE"  VALUES ('22', 'Girardot', 'Vincent', '5, rue des Lilas', '18000', 'BOURGES');



INSERT INTO "AGENCE" VALUES ('102', '22', '51, avenue d''Italie', '18000', 'BOURGES');




INSERT INTO "BIEN"  VALUES ('1', '1', '101', '300', 'Villa', '150', '7', 'Rue Perdue', '18000', 'BOURGES');
INSERT INTO "BIEN"  VALUES ('2', '1', '101', '700', 'Appart', '100', '4', 'Rue des Lofts', '18000', 'BOURGES');




INSERT INTO "BIEN"  VALUES ('3', '2', '102', '400', 'Appart', '80', '4', '8, Avenue du 11 novembre', '18000', 'BOURGES');
INSERT INTO "BIEN"  VALUES ('4', '2', '102', '800', 'Villa', '75', '3', 'Petite Rue', '18000', 'BOURGES');
INSERT INTO "BIEN"  VALUES ('5', '3', '102', '1200', 'Appart', '150', '5', 'En plein centre', '18000', 'BOURGES');


INSERT INTO "BIEN"  VALUES ('6', '3', '100', '500', 'Appart', '75', '3', 'Loin de tout', '18000', 'BOURGES');
INSERT INTO "BIEN"  VALUES ('7', '3', '100', '800', 'Appart', '100', '4', '7, rue des Pinsons', '18000', 'BOURGES');



INSERT INTO "LOCATION" (NUMLOCATION, NUMBIEN, NUMLOCATAIRE, DEBUTLOCATION, FINLOCATION) VALUES ('101', '1', '12', TO_DATE('01-FEB-03', 'DD-MON-RR'), TO_DATE('31-OCT-04', 'DD-MON-RR'));
INSERT INTO "LOCATION" (NUMLOCATION, NUMBIEN, NUMLOCATAIRE, DEBUTLOCATION) VALUES ('102', '1', '11', TO_DATE('15-MAR-06', 'DD-MON-RR'));

INSERT into location
values
(100, 1, 11, TO_DATE('01-01-2000', 'DD-MM-YYYY'), TO_DATE('31-12-2002', 'DD-MM-YYYY'));

INSERT INTO "LOCATION" (NUMLOCATION, NUMBIEN, NUMLOCATAIRE, DEBUTLOCATION, FINLOCATION) VALUES ('103', '2', '10', TO_DATE('16-MAY-03', 'DD-MON-RR'), TO_DATE('15-JUN-07', 'DD-MON-RR'));



INSERT INTO "LOCATION" (NUMLOCATION, NUMBIEN, NUMLOCATAIRE, DEBUTLOCATION) VALUES ('104', '2', '12', TO_DATE('01-SEP-08', 'DD-MON-RR'));



insert into location values(105, 3, 10, sysdate, null);


-- Pour que les insertions soient bien prises en compte
COMMIT;