1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
|
--------------------------------------------------------
-- Suppression des tables
--------------------------------------------------------
drop table INFORMATION_PERSONNELLE;
drop table AGENCE;
drop table CONTRAT;
drop table EMPLOYE;
drop table CLIENT;
drop table FACTURE;
drop table VEHICULE;
drop table MARQUE;
drop table MODELE;
drop table MOTEUR;
--------------------------------------------------------
-- DDL for Table Information_Personnelle
--------------------------------------------------------
CREATE TABLE INFORMATION_PERSONNELLE (
INFO_PERS_ID INTEGER,
USERNAME VARCHAR(50) NOT NULL,
MOTDEPASSE VARCHAR(50) NOT NULL,
NOM VARCHAR(50) NOT NULL,
ADRESSE VARCHAR(150) NOT NULL,
PRENOM VARCHAR(50) NOT NULL,
SEXE Boolean NOT NULL,
CONTACT VARCHAR(50) NOT NULL,
CONSTRAINT INFO_PERS_PK PRIMARY KEY(INFO_PERS_ID)
);
--------------------------------------------------------
-- DDL for Table Agence
--------------------------------------------------------
CREATE TABLE AGENCE (
AGENCE_ID INTEGER,
SIREN VARCHAR(50) NOT NULL,
NOM VARCHAR(50) NOT NULL,
CONTACT VARCHAR(50) NOT NULL,
ADRESSE VARCHAR(50) NOT NULL
);
--------------------------------------------------------
-- DDL for Table Employe
--------------------------------------------------------
CREATE TABLE EMPLOYE (
INFO_PERS_ID INTEGER,
EMPLOYE_ID INTEGER,
DATE_EMBAUCHE DATE NULL,
CATEGORIE_EMPLOYE INTEGER,
CONSTRAINT EMPLOYE_PK PRIMARY KEY(INFO_PERS_ID),
CONSTRAINT EMPLOYE_FK FOREIGN KEY (INFO_PERS_ID) REFERENCES INFORMATION_PERSONNELLE(INFO_PERS_ID)
);
--------------------------------------------------------
-- DDL for Table Client
--------------------------------------------------------
CREATE TABLE CLIENT (
INFO_PERS_ID INTEGER,
CLIENT_ID INTEGER,
PERMIS_CONDUIRE VARCHAR(50) NOT NULL,
CONSTRAINT CLIENT_PK PRIMARY KEY(INFO_PERS_ID),
CONSTRAINT CLIENT_FK FOREIGN KEY (INFO_PERS_ID) REFERENCES INFORMATION_PERSONNELLE(INFO_PERS_ID)
);
--------------------------------------------------------
-- DDL for Table Contrat
--------------------------------------------------------
CREATE TABLE CONTRAT (
CONTRAT_ID INTEGER,
NUMERO_CONTRAT CHAR(6) NOT NULL,
DATE_CONTRAT DATE NOT NULL,
PENALITE NUMERIC NOT NULL,
INFO_PERS_ID INTEGER,
CONSTRAINT CONTRAT_PK PRIMARY KEY(CONTRAT_ID),
CONSTRAINT CONTRAT_CLIENT_FK FOREIGN KEY (INFO_PERS_ID) REFERENCES CLIENT(INFO_PERS_ID)
);
--------------------------------------------------------
-- DDL for Table Facture
--------------------------------------------------------
CREATE TABLE FACTURE (
FACTURE_ID INTEGER,
NUMERO_FACTURE CHAR(6) NOT NULL,
DATE_FACTURE DATE NOT NULL,
MONTANT_FINALE NUMERIC NOT NULL,
CONTRAT_ID INTEGER,
CONSTRAINT FACTURE_PK PRIMARY KEY(FACTURE_ID),
CONSTRAINT FACTURE_CONTRAT_FK FOREIGN KEY(CONTRAT_ID) REFERENCES CONTRAT(CONTRAT_ID)
);
--------------------------------------------------------
-- DDL for Table Marque
--------------------------------------------------------
CREATE TABLE MARQUE (
MARQUE_ID INTEGER,
REFERENCE VARCHAR(20) NOT NULL,
LIBELLE VARCHAR(50) NOT NULL,
CONSTRAINT MARQUE_PK PRIMARY KEY(MARQUE_ID)
);
--------------------------------------------------------
-- DDL for Table Modele
--------------------------------------------------------
CREATE TABLE MODELE (
MODELE_ID INTEGER,
TYPE VARCHAR(20) NOT NULL,
DATE_SORTIE DATE NOT NULL,
MARQUE_ID INTEGER,
CONSTRAINT MODELE_PK PRIMARY KEY(MODELE_ID),
CONSTRAINT MODELE_MARQUE_FK FOREIGN KEY(MARQUE_ID) REFERENCES MARQUE(MARQUE_ID)
);
--------------------------------------------------------
-- DDL for Table Moteur
--------------------------------------------------------
CREATE TABLE MOTEUR (
MOTEUR_ID INTEGER,
MOTEUR_REFERENCE VARCHAR(20) NOT NULL,
PUISSANCE VARCHAR(20) NOT NULL,
CARBURANT VARCHAR(20) NOT NULL,
MODELE_ID INTEGER,
CONSTRAINT MOTEUR_PK PRIMARY KEY(MOTEUR_ID),
CONSTRAINT MOTEUR_MODELE_FK FOREIGN KEY(MODELE_ID) REFERENCES MODELE(MODELE_ID)
);
--------------------------------------------------------
-- DDL for Table Vehicule
--------------------------------------------------------
CREATE TABLE VEHICULE (
VEHICULE_ID INTEGER,
MARQUE VARCHAR(50) NOT NULL,
NUMERO_DE_SERIE VARCHAR(15) NOT NULL,
NUMERO_IMMATRICULATION VARCHAR(20) NOT NULL,
KM_COMPTEUR NUMERIC NOT NULL,
DISPONIBLE Boolean NOT NULL,
DATE_CONTROLE_TECHNIQUE DATE NOT NULL,
DATE_ASSURANCE DATE NOT NULL,
TARIF_LOC NUMERIC NOT NULL,
CAUTION NUMERIC NOT NULL,
CONTRAT_ID INTEGER,
MOTEUR_ID INTEGER,
CONSTRAINT VEHICULE_PK PRIMARY KEY(VEHICULE_ID),
CONSTRAINT VEHICULE_MOTEUR_FK FOREIGN KEY(MOTEUR_ID) REFERENCES MOTEUR(MOTEUR_ID),
CONSTRAINT VEHICULE_CONTRAT_FK FOREIGN KEY(CONTRAT_ID) REFERENCES CONTRAT(CONTRAT_ID)
);
--------------------------------------------------------
-- DDL for Table LOUER
--------------------------------------------------------
CREATE TABLE LOUER (
CONTRAT_ID INTEGER,
VEHICULE_ID INTEGER,
INFO_PERS_ID INTEGER,
DATE_PERIODE_LOC DATERANGE NOT NULL,
ETAT_VEHIC_D VARCHAR(50) NOT NULL,
ETAT_VEHIC_R VARCHAR(50) NOT NULL,
CONSTRAINT LOUER_PK PRIMARY KEY(CONTRAT_ID, VEHICULE_ID, DATE_PERIODE_LOC),
CONSTRAINT LOUER_CONTRAT_FK FOREIGN KEY(CONTRAT_ID) REFERENCES CONTRAT(CONTRAT_ID),
CONSTRAINT LOUER_VEHICULE_FK FOREIGN KEY(VEHICULE_ID) REFERENCES VEHICULE(VEHICULE_ID),
CONSTRAINT LOUER_EMPLOYE_FK FOREIGN KEY(INFO_PERS_ID) REFERENCES EMPLOYE(INFO_PERS_ID)
);
--------------------------------------------------------
-- DDL for Table Ligne Facture
--------------------------------------------------------
CREATE TABLE LIGNE_FACTURE (
LIGNE_FACTURE_ID INTEGER,
VEHICULE_ID INTEGER,
CONTRAT_ID INTEGER,
CONSTRAINT LIGNE_FACTURE_PK PRIMARY KEY(LIGNE_FACTURE_ID),
CONSTRAINT LIGNE_FACTURE_FACTURE_FK FOREIGN KEY(CONTRAT_ID) REFERENCES CONTRAT(CONTRAT_ID),
CONSTRAINT LIGNE_FACTURE_VEHICULE_FK FOREIGN KEY(VEHICULE_ID) REFERENCES VEHICULE(VEHICULE_ID)
); |