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
| CREATE TABLE CLI_client(
CLI_ident INT IDENTITY,
CLI_dtcre DATE NOT NULL,
CLI_ident_payeur INT NOT NULL,
PRIMARY KEY(CLI_ident),
FOREIGN KEY(CLI_ident_payeur) REFERENCES CLI_client(CLI_ident)
);
CREATE TABLE CLP_client_part(
CLI_ident INT,
CLP_nom VARCHAR(50) NOT NULL,
CLP_prenom VARCHAR(50) NOT NULL,
CLP_ddn DATE NOT NULL,
CLP_nni CHAR(13) NOT NULL,
PRIMARY KEY(CLI_ident),
UNIQUE(CLP_nni),
FOREIGN KEY(CLI_ident) REFERENCES CLI_client(CLI_ident)
);
CREATE TABLE CLS_client_ste(
CLI_ident INT,
CLS_raison_soc VARCHAR(128) NOT NULL,
CLS_siren CHAR(9) NOT NULL,
PRIMARY KEY(CLI_ident),
FOREIGN KEY(CLI_ident) REFERENCES CLI_client(CLI_ident)
);
CREATE TABLE YAD_type_adr(
YAD_ident INT IDENTITY,
YAD_code CHAR(3) NOT NULL,
YAD_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(YAD_ident),
UNIQUE(YAD_code)
);
CREATE TABLE VIL_ville(
VIL_ident INT IDENTITY,
VIL_insee CHAR(5) NOT NULL,
VIL_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(VIL_ident)
);
CREATE TABLE CPO_code_post(
CPO_ident INT IDENTITY,
CPO_code CHAR(5) NOT NULL,
PRIMARY KEY(CPO_ident),
UNIQUE(CPO_code)
);
CREATE TABLE CON_contact(
CON_ident INT IDENTITY,
CON_nom VARCHAR(50) NOT NULL,
CON_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(CON_ident)
);
CREATE TABLE YME_type_media(
YME_ident INT IDENTITY,
YME_code CHAR(4) NOT NULL,
YME_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(YME_ident),
UNIQUE(YME_code)
);
CREATE TABLE CTR_contrat(
CTR_ident INT IDENTITY,
CTR_date DATE,
CLI_ident INT NOT NULL,
PRIMARY KEY(CTR_ident),
FOREIGN KEY(CLI_ident) REFERENCES CLI_client(CLI_ident)
);
CREATE TABLE CPT_competence(
CPT_ident INT IDENTITY,
CPT_code CHAR(4) NOT NULL,
CPT_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(CPT_ident),
UNIQUE(CPT_code)
);
CREATE TABLE ADR_adresse(
ADR_ident INT IDENTITY,
ADR_ligne1 VARCHAR(35) NOT NULL,
ADR_ligne2 VARCHAR(35),
ADR_ligne3 VARCHAR(35),
ADR_ligne4 VARCHAR(35),
ADR_ligne5 VARCHAR(35),
ADR_ident_principal INT NOT NULL,
CPO_ident INT NOT NULL,
VIL_ident INT NOT NULL,
PRIMARY KEY(ADR_ident),
FOREIGN KEY(ADR_ident_principal) REFERENCES ADR_adresse(ADR_ident),
FOREIGN KEY(CPO_ident) REFERENCES CPO_code_post(CPO_ident),
FOREIGN KEY(VIL_ident) REFERENCES VIL_ville(VIL_ident)
);
CREATE TABLE MED_media(
CON_ident INT,
MED_ident INT IDENTITY,
MED_numero VARCHAR(255) NOT NULL,
YME_ident INT NOT NULL,
PRIMARY KEY(CON_ident, MED_ident),
FOREIGN KEY(CON_ident) REFERENCES CON_contact(CON_ident),
FOREIGN KEY(YME_ident) REFERENCES YME_type_media(YME_ident)
);
CREATE TABLE PRS_prestation(
CTR_ident INT,
PRS_seq SMALLINT,
ADR_ident INT NOT NULL,
PRIMARY KEY(CTR_ident, PRS_seq),
FOREIGN KEY(CTR_ident) REFERENCES CTR_contrat(CTR_ident),
FOREIGN KEY(ADR_ident) REFERENCES ADR_adresse(ADR_ident)
);
CREATE TABLE INT_intervention(
CTR_ident INT,
PRS_seq SMALLINT,
INT_seq SMALLINT,
INT_dtprevue DATE NOT NULL,
INT_dtreelle DATE NOT NULL,
INT_duree TINYINT NOT NULL,
CPT_ident INT NOT NULL,
PRIMARY KEY(CTR_ident, PRS_seq, INT_seq),
FOREIGN KEY(CTR_ident, PRS_seq) REFERENCES PRS_prestation(CTR_ident, PRS_seq),
FOREIGN KEY(CPT_ident) REFERENCES CPT_competence(CPT_ident)
);
CREATE TABLE SIC_situer_cli(
CLI_ident INT,
ADR_ident INT,
PRIMARY KEY(CLI_ident, ADR_ident),
FOREIGN KEY(CLI_ident) REFERENCES CLI_client(CLI_ident),
FOREIGN KEY(ADR_ident) REFERENCES ADR_adresse(ADR_ident)
);
CREATE TABLE TAD_typer_adr(
ADR_ident INT,
YAD_ident INT,
PRIMARY KEY(ADR_ident, YAD_ident),
FOREIGN KEY(ADR_ident) REFERENCES ADR_adresse(ADR_ident),
FOREIGN KEY(YAD_ident) REFERENCES YAD_type_adr(YAD_ident)
);
CREATE TABLE VIC_vil_cpo(
VIL_ident INT,
CPO_ident INT,
PRIMARY KEY(VIL_ident, CPO_ident),
FOREIGN KEY(VIL_ident) REFERENCES VIL_ville(VIL_ident),
FOREIGN KEY(CPO_ident) REFERENCES CPO_code_post(CPO_ident)
);
CREATE TABLE CCO_cli_con(
CLI_ident INT,
CON_ident INT,
PRIMARY KEY(CLI_ident, CON_ident),
FOREIGN KEY(CLI_ident) REFERENCES CLI_client(CLI_ident),
FOREIGN KEY(CON_ident) REFERENCES CON_contact(CON_ident)
);
alter table ADR_adresse
foreign key (VIL_ident, CPO_ident)
references VIC_vil_cpo (VIL_ident, CPO_ident) |
