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
| CREATE TABLE CL_client(
CL_ident INT IDENTITY,
CL_nom VARCHAR(50) NOT NULL,
CL_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(CL_ident)
);
CREATE TABLE TJ_type_justif(
TJ_ident INT IDENTITY,
TJ_code CHAR(4) NOT NULL,
TJ_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(TJ_ident),
UNIQUE(TJ_code)
);
CREATE TABLE CO_commande(
CL_ident INT,
CO_seq SMALLINT,
CO_date DATE NOT NULL,
CO_statut CHAR(1) NOT NULL,
PRIMARY KEY(CL_ident, CO_seq),
FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident)
);
CREATE TABLE YS_type_sl(
YS_ident INT IDENTITY,
YS_code CHAR(4) NOT NULL,
YS_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(YS_ident),
UNIQUE(YS_code)
);
CREATE TABLE YM_type_me(
YM_ident INT IDENTITY,
YM_code CHAR(4) NOT NULL,
YM_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(YM_ident),
UNIQUE(YM_code)
);
CREATE TABLE DM_domaine(
DM_ident INT IDENTITY,
DM_code CHAR(6) NOT NULL,
DM_adresse VARCHAR(253) NOT NULL,
PRIMARY KEY(DM_ident),
UNIQUE(DM_code)
);
CREATE TABLE YC_type_permis(
YC_ident INT IDENTITY,
YC_code CHAR(4) NOT NULL,
YC_lib VARCHAR(50) NOT NULL,
PRIMARY KEY(YC_ident),
UNIQUE(YC_code)
);
CREATE TABLE JS_justificatif(
PJ_ident INT IDENTITY,
PJ_numero CHAR(20) NOT NULL,
PJ_date DATE NOT NULL,
TJ_ident INT NOT NULL,
CL_ident INT NOT NULL,
PRIMARY KEY(PJ_ident),
FOREIGN KEY(TJ_ident) REFERENCES TJ_type_justif(TJ_ident),
FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident)
);
CREATE TABLE SL_salle(
SL_ident INT IDENTITY,
SL_numero CHAR(5) NOT NULL,
SL_nom VARCHAR(50) NOT NULL,
YS_ident INT NOT NULL,
PRIMARY KEY(SL_ident),
UNIQUE(SL_numero),
FOREIGN KEY(YS_ident) REFERENCES YS_type_sl(YS_ident)
);
CREATE TABLE YV_type_vh(
YV_ident INT IDENTITY,
YV_code CHAR(4) NOT NULL,
YV_libelle VARCHAR(50) NOT NULL,
YC_ident INT NOT NULL,
PRIMARY KEY(YV_ident),
UNIQUE(YV_code),
FOREIGN KEY(YC_ident) REFERENCES YC_type_permis(YC_ident)
);
CREATE TABLE ME_media(
CL_ident INT,
ME_seq INT IDENTITY,
ME_adresse VARCHAR(64) NOT NULL,
DM_ident INT NOT NULL,
YM_ident INT NOT NULL,
PRIMARY KEY(CL_ident, ME_seq),
FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident),
FOREIGN KEY(DM_ident) REFERENCES DM_domaine(DM_ident),
FOREIGN KEY(YM_ident) REFERENCES YM_type_me(YM_ident)
);
CREATE TABLE VH_vehicule(
VH_ident INT IDENTITY,
VH_immat CHAR(9) NOT NULL,
YV_ident INT NOT NULL,
PRIMARY KEY(VH_ident),
UNIQUE(VH_immat),
FOREIGN KEY(YV_ident) REFERENCES YV_type_vh(YV_ident)
);
CREATE TABLE RV_reserver_vh(
VH_ident INT,
CA_date DATE,
RV_dtfin DATE NOT NULL,
CL_ident INT NOT NULL,
CO_seq SMALLINT NOT NULL,
PRIMARY KEY(VH_ident, CA_date),
FOREIGN KEY(VH_ident) REFERENCES VH_vehicule(VH_ident),
FOREIGN KEY(CL_ident, CO_seq) REFERENCES CO_commande(CL_ident, CO_seq)
);
CREATE TABLE RS_reserver_sl(
SL_ident INT,
CA_date DATE,
RS_dtfin DATE NOT NULL,
CL_ident INT NOT NULL,
CO_seq SMALLINT NOT NULL,
PRIMARY KEY(SL_ident, CA_date),
FOREIGN KEY(SL_ident) REFERENCES SL_salle(SL_ident),
FOREIGN KEY(CL_ident, CO_seq) REFERENCES CO_commande(CL_ident, CO_seq)
); |
creation d'une barre de recherche pour affichage des véhicules/salle existants
Répondre avec citation
Envoyé par gabinou7
Partager