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
|
CREATE TABLE client (
client_id INT AUTO_INCREMENT NOT NULL ,
client_sexe CHAR(1),
client_nom VARCHAR(40) NOT NULL,
client_prenom VARCHAR(40),
client_adresse VARCHAR(80),
client_cde_postal VARCHAR(8),
client_ville VARCHAR(40),
client_permis_conduire VARCHAR(20),
client_date_permis_conduire DATE,
client_date_naissance date,
PRIMARY KEY (client_id)
);
CREATE TABLE vehicule (
vehicule_id varchar(11) NOT NULL,
vehicule_type INT,
vehicule_marque char(32),
vehicule_immatriculation char(10) NOT NULL UNIQUE,
vehicule_couleur char(16) ,
vehicule_date_derniere_revision DATE,
type_id int,
PRIMARY KEY (vehicule_id)
);
CREATE TABLE type_vehicule (
type_id int NOT NULL,
type_marque VARCHAR(30),
type_modele VARCHAR(20),
type_energie VARCHAR(20),
type_prix_km FLOAT,
type_permis_requis VARCHAR(2),
type_anciennete_permis_requise int,
type_franchise FLOAT,
type_rachat_franchise FLOAT,
PRIMARY KEY (type_id)
);
CREATE TABLE tarif (
tarif_id int NOT NULL,
tarif_typevehicule INT,
tarif_dureeenjour FLOAT,
tarif_prix FLOAT,
tarif_forfaitkm INT,
PRIMARY KEY (tarif_id)
);
CREATE TABLE concession (
concession_id int NOT NULL,
concession_nom varchar(40),
concession_adresse varchar(80),
concession_cde_postal VARCHAR(8),
client_ville VARCHAR(40),
concession_telephone varchar(12),
PRIMARY KEY (concession_id)
);
CREATE TABLE tarif8 (
tarif8_id int AUTO_INCREMENT NOT NULL,
tarif8_pdtid int,
tarif8_prix float,
tarif8_dureejoursemaine int,
tarif8_dureejourwe int,
tarif8_date date NOT NULL,
tarif8_kmsupp float,
PRIMARY KEY (tarif8_id)
);
CREATE TABLE tarif11 (
tarif11_id int AUTO_INCREMENT NOT NULL,
tarif11_prdtid int,
tarif11_prix float,
tarif_11_dureejourssemaine int,
tarif_11dureejourwe int,
tarif11_date date NOT NULL,
tarif11_kmsupp float,
PRIMARY KEY (tarif11_id)
);
CREATE TABLE loue (
location_id int AUTO_INCREMENT,
location_client BIGINT,
location_vehicule BIGINT,
location_km_depart BIGINT,
location_km_fin BIGINT,
location_date_debut DATETIME,
location_date_fin DATETIME,
location_montanttotal DECIMAL(16,2),
PRIMARY KEY (location_id)
);
CREATE TABLE coute (
tarif8_id int NOT NULL,
tarif11_id int NOT NULL,
PRIMARY KEY (tarif8_id, tarif11_id)
);
CREATE TABLE intervention (
intervention_id int,
intervention_duree date,
intervention_objet varchar(30),
PRIMARY KEY (intervention_id)
); |
