--
--
--  Voiture
--
--
--


CREATE TYPE IMMATRICULATION AS OBJECT ( NUM NUMBER, LETTRE 
    VARCHAR2(4), DEPT NUMBER(2)     )
/

CREATE TABLE VOITURE (ID_VOITURE NUMBER NOT NULL, 
    IMMAT IMMATRICULATION,
    MARQUE VARCHAR2(500 byte), ETAT VARCHAR2(10 byte) 
    DEFAULT 'OK', NBR_PLACE NUMBER(5), DATE_ACHAT DATE, 
    CONSTRAINT PK_VOITURE PRIMARY KEY(ID_VOITURE) 
    USING INDEX  
    TABLESPACE SYSTEM 
    STORAGE ( INITIAL 12K NEXT 12K MINEXTENTS 1 MAXEXTENTS 249 
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 
    INITRANS 2 MAXTRANS 255)  
    TABLESPACE SYSTEM PCTFREE 10 PCTUSED 40 INITRANS 1 
    MAXTRANS 255 
    STORAGE ( INITIAL 12K NEXT 12K MINEXTENTS 1 MAXEXTENTS 249 
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) 
    LOGGING 
/

ALTER TABLE VOITURE 
    ADD (DESCRIPTION SYS.XMLTYPE) ;


ALTER TABLE VOITURE 
    ADD (ACHETER_PAR NUMBER, 
    CONSTRAINT VALIDE_ACHETE_PAR FOREIGN KEY(ACHETER_PAR) 
    REFERENCES CLIENT(ID_CLIENT)) ;




insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(1, IMMATRICULATION(123,'AZQ',78), 'RENAULT', 'OK', 4, TO_DATE('20/05/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(1, IMMATRICULATION(123,'ACD',78), 'RENAULT', 'OK', 4, TO_DATE('20/05/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(2, IMMATRICULATION(451,'DRS',78), 'CITROEN', 'OK', 6, TO_DATE('22/07/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(3, IMMATRICULATION(412,'ACF',78), 'CITROEN', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(4, IMMATRICULATION(413,'XC',78), 'CITROEN', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(5, IMMATRICULATION(512,'ASX',78), 'CITROEN', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(6, IMMATRICULATION(462,'EEQ',78), 'RENAULT', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(7, IMMATRICULATION(812,'AED',78), 'CITROEN', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(8, IMMATRICULATION(612,'BNQ',78), 'CITROEN', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(9, IMMATRICULATION(412,'HHQ',78), 'RENAULT', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(10, IMMATRICULATION(444,'GAQ',78), 'RENAULT', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(11, IMMATRICULATION(44,'AAR',78), 'PORCHE', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));

insert into VOITURE (ID_VOITURE, IMMAT, MARQUE, ETAT, NBR_PLACE, DATE_ACHAT) VALUES
(12, IMMATRICULATION(441,'DAQ',78), 'CITROEN', 'OK', 3, TO_DATE('21/04/1996', 'DD/MM/YYYY'));



UPDATE VOITURE set ACHETER_PAR = 7369 where id_voiture = 11;



--
--
-- CLIENT
--
--
--
CREATE TABLE CLIENT (ID_CLIENT NUMBER NOT NULL, NOM 
    VARCHAR2(25 byte), PNOM VARCHAR2(25 byte), DATE_NAIS 
    DATE, 
    CONSTRAINT PK_CLIENT PRIMARY KEY(ID_CLIENT) 
    USING INDEX  
    TABLESPACE SYSTEM 
    STORAGE ( INITIAL 12K NEXT 12K MINEXTENTS 1 MAXEXTENTS 249 
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 
    INITRANS 2 MAXTRANS 255)  
    TABLESPACE SYSTEM PCTFREE 10 PCTUSED 40 INITRANS 1 
    MAXTRANS 255 
    STORAGE ( INITIAL 12K NEXT 12K MINEXTENTS 1 MAXEXTENTS 249 
    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1) 
    LOGGING 
/
insert into CLIENT (ID_CLIENT, NOM, DATE_NAIS) SELECT EMPNO, ENAME,HIREDATE from scott.emp;



--
--
--  ACCIDENT
--
--
CREATE TABLE ACCIDENT (ID_CLIENT NUMBER NOT NULL, ID_VOITURE 
    NUMBER NOT NULL, DATE_ACCIDENT date )  ;


ALTER TABLE ACCIDENT 
    ADD (CONSTRAINT PK_ACCIDENT PRIMARY KEY(ID_CLIENT,ID_VOITURE)) ;

ALTER TABLE ACCIDENT 
    ADD (CONSTRAINT FK_CLIENT FOREIGN KEY(ID_CLIENT) 
    REFERENCES CLIENT(ID_CLIENT)) ;

ALTER TABLE VOITURE 
    ADD (CONSTRAINT FK_VOITURE FOREIGN KEY(ID_VOITURE) 
    REFERENCES VOITURE(ID_VOITURE)) ;

insert into ACCIDENT VALUES (7369,1, SYSDATE - 4512);
insert into ACCIDENT VALUES (7902,5, SYSDATE - 1);
insert into ACCIDENT VALUES (7902,10, SYSDATE - 451);

--
--
-- GARAGE
--
--

CREATE TABLE GARAGE (
ID_GARAGE NUMBER(10) NOT NULL, 
NOM_GARAGE  VARCHAR2(20 byte), 
ADR_L1 VARCHAR2(30 byte), 
ADR_L2 VARCHAR2(30 byte), 
CP VARCHAR2(5 byte), 
VILLE VARCHAR2(30 byte), 
NOM_PROPIETAIRE VARCHAR2(30 byte), 
GROUPE NUMBER(10), 
CONSTRAINT FK_GARAGE FOREIGN KEY(GROUPE) REFERENCES GARAGE(ID_GARAGE), 
CONSTRAINT PK_GARAGE PRIMARY KEY(ID_GARAGE) 
);


DELETE FROM GARAGE;
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
1, 'REPA MECA', '12 rue des fleurs', '78000', 'Versailles', 'DUCHEMIN', NULL); 
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
2, 'GGA', '5 rue de la defence', '92000', 'Naterre', 'BRISTOLLE', 6); 
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
3, 'PETIT MECA', '13 rue de la prairie', '78180', 'Montigny', 'Mr. Chaux', 2); 
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
4, 'RAPIDO', '8 rue des charmes', '78000', 'Versailles', NULL, 2); 
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
5, 'GARAGE DU CENTRE', '4 Place du centre', '78180', 'Montigny le bretonneux', 'ANTOINE'
, NULL); 
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
6, 'GGA SA', 'ILES', NULL, NULL, NULL, NULL); 
INSERT INTO GARAGE ( ID_GARAGE, NOM_GARAGE, ADR_L1, CP, VILLE, NOM_PROPIETAIRE, GROUPE ) VALUES ( 
7, 'PETITES ROUES', 'sente le la fontaines', '78350', 'Les loges en josas', 'RITA'
, 6); 
commit;


--
-- REPARE
--
--

CREATE TABLE REPARE (ID_VOITURE NUMBER(10) NOT NULL, ID_GARAGE 
    NUMBER(10) NOT NULL, COUT NUMBER(15, 2), DESCRITION 
    VARCHAR2(255 byte), DATE_REP DATE, 
    CONSTRAINT FK_REPARE_GARAGE FOREIGN KEY(ID_GARAGE) 
    REFERENCES GARAGE(ID_GARAGE), 
    CONSTRAINT FK_REPARE_VOITURE FOREIGN KEY(ID_VOITURE) 
    REFERENCES VOITURE(ID_VOITURE), 
    CONSTRAINT PK_REPARE PRIMARY KEY(ID_VOITURE, ID_GARAGE, DATE_REP)
    );



INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 1, 1245, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 2, 1527.12, 'Révison', sysdate -12); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 3, 1423, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 4, 1523, 'Révison', sysdate -55); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 5, 4557, 'Révison', sysdate -89); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 6, 7441, 'Révison', sysdate -74); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
1, 7, 1235, 'Révison', sysdate -4512); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
2, 1, 1245, 'Révison', sysdate -1475); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
3, 1, 1245, 'Révison', sysdate -4123); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
4, 1, 1245, 'Révison', sysdate -78895); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
5, 1, 1245, 'Révison', sysdate -741); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
6, 1, 1245, 'Révison', sysdate -74558); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
7, 1, 1245, 'Révison', sysdate -1452); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
8, 1, 1245, 'Révison', sysdate -1452); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
9, 1, 1245, 'Révison', sysdate -7845); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
10, 1, 1245, 'Révison', sysdate -1452); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
11, 1, 1245, 'Révison', sysdate -124); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
12, 1, 1245, 'Révison', sysdate); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
2, 4, 1561, 'Révison', sysdate -12365); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
3, 4, 1561, 'Révison', sysdate -4587); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
4, 4, 1561, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
5, 4, 1561, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
6, 4, 1561, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
2, 7, 1412, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
9, 7, 1421, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
10, 7, 1412, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
11, 7, 1421, 'Révison', sysdate -45); 
INSERT INTO REPARE ( ID_VOITURE, ID_GARAGE, COUT, DESCRITION, DATE_REP ) VALUES ( 
12, 7, 1421, 'Révison', sysdate -45);