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
| CREATE TABLE TYI_type_inge(
TYI_ident INT IDENTITY,
TYI_code CHAR(2) NOT NULL,
TYI_libelle VARCHAR(50) NOT NULL,
TYI_debval DATE NOT NULL,
TYI_finval DATE NOT NULL,
PRIMARY KEY(TYI_ident),
UNIQUE(TYI_code)
);
CREATE TABLE EQU_equipe(
EQU_ident INT IDENTITY,
EQU_code CHAR(4) NOT NULL,
EQU_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(EQU_ident),
UNIQUE(EQU_code)
);
CREATE TABLE PRO_projet(
PRO_ident INT IDENTITY,
PRO_code CHAR(6) NOT NULL,
PRO_libelle VARCHAR(100) NOT NULL,
PRO_dtdeb DATE NOT NULL,
PRIMARY KEY(PRO_ident),
UNIQUE(PRO_code)
);
CREATE TABLE SEN_seniority(
SEN_ident INT IDENTITY,
SEN_xxxx VARCHAR(50) NOT NULL,
PRIMARY KEY(SEN_ident)
);
CREATE TABLE GEO_app_geo(
GEO_ident VARCHAR(50),
GEO_code CHAR(4) NOT NULL,
GEO_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(GEO_ident),
UNIQUE(GEO_code)
);
CREATE TABLE DIS_discipline(
DIS_ident INT IDENTITY,
DIS_code CHAR(4) NOT NULL,
DIS_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(DIS_ident),
UNIQUE(DIS_code)
);
CREATE TABLE ING_ingenieur(
ING_ident INT IDENTITY,
ING_matricule CHAR(8) NOT NULL,
ING_nom VARCHAR(50) NOT NULL,
ING_prenom VARCHAR(50) NOT NULL,
ING_ddn DATE NOT NULL,
DIS_ident INT NOT NULL,
TYI_ident INT NOT NULL,
PRIMARY KEY(ING_ident),
UNIQUE(ING_matricule),
FOREIGN KEY(DIS_ident) REFERENCES DIS_discipline(DIS_ident),
FOREIGN KEY(TYI_ident) REFERENCES TYI_type_inge(TYI_ident)
);
CREATE TABLE PAR_participation(
PAR_ident INT IDENTITY,
PAR_xxxx VARCHAR(50) NOT NULL,
SEN_ident INT NOT NULL,
PRO_ident INT NOT NULL,
ING_ident INT NOT NULL,
PRIMARY KEY(PAR_ident),
FOREIGN KEY(SEN_ident) REFERENCES SEN_seniority(SEN_ident),
FOREIGN KEY(PRO_ident) REFERENCES PRO_projet(PRO_ident),
FOREIGN KEY(ING_ident) REFERENCES ING_ingenieur(ING_ident)
);
CREATE TABLE AFF_affecter(
ING_ident INT,
CAL_date DATE,
AFF_dtfin DATE NOT NULL,
EQU_ident INT NOT NULL,
PRIMARY KEY(ING_ident, CAL_date),
FOREIGN KEY(ING_ident) REFERENCES ING_ingenieur(ING_ident),
FOREIGN KEY(EQU_ident) REFERENCES EQU_equipe(EQU_ident)
);
CREATE TABLE TRA_travailler(
ING_ident INT,
PRO_ident INT,
CAL_date DATE,
TRA_dtfin VARCHAR(50) NOT NULL,
PRIMARY KEY(ING_ident, PRO_ident, CAL_date),
FOREIGN KEY(ING_ident) REFERENCES ING_ingenieur(ING_ident),
FOREIGN KEY(PRO_ident) REFERENCES PRO_projet(PRO_ident)
);
CREATE TABLE R003(
ING_ident INT,
GEO_ident VARCHAR(50),
PRIMARY KEY(ING_ident, GEO_ident),
FOREIGN KEY(ING_ident) REFERENCES ING_ingenieur(ING_ident),
FOREIGN KEY(GEO_ident) REFERENCES GEO_app_geo(GEO_ident)
); |
Deux tables identiques
Répondre avec citation
Envoyé par escartefigue
Partager