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
| CREATE TABLE PE_personne(
PE_ident INT IDENTITY,
PE_nom VARCHAR(50) NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE MM_medicament(
MM_ident INT IDENTITY,
MM_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(MM_ident),
UNIQUE(MM_nom)
);
CREATE TABLE EI_effet_indes(
EI_ident INT IDENTITY,
EI_description VARCHAR(512) NOT NULL,
PRIMARY KEY(EI_ident)
);
CREATE TABLE CO_composant(
CO_ident INT IDENTITY,
CO_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(CO_ident)
);
CREATE TABLE MX_exemplaire(
MM_ident INT,
MX_ident INT IDENTITY,
MX_numlot CHAR(10) NOT NULL,
MX_dtfab DATE NOT NULL, --date de fabrication
MX_dtexp VARCHAR(50) NOT NULL, --date d'expiration
PRIMARY KEY(MM_ident, MX_ident),
FOREIGN KEY(MM_ident) REFERENCES MM_medicament(MM_ident)
);
CREATE TABLE ME_medecin(
PE_ident_ME INT,
ME_rpps CHAR(11) NOT NULL, --numéro du Répertoire Partagé des Professionnels de Santé (RPPS)
PRIMARY KEY(PE_ident_ME),
UNIQUE(ME_rpps),
FOREIGN KEY(PE_ident_ME) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE PA_patient(
PE_ident_PA INT,
PA_ddn DATE NOT NULL,
PRIMARY KEY(PE_ident_PA),
FOREIGN KEY(PE_ident_PA) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE OR_ordonnance(
OR_ident INT IDENTITY,
OR_date DATE NOT NULL,
PE_ident_PA INT NOT NULL,
PE_ident_ME INT NOT NULL,
PRIMARY KEY(OR_ident),
FOREIGN KEY(PE_ident_PA) REFERENCES PA_patient(PE_ident_PA),
FOREIGN KEY(PE_ident_ME) REFERENCES ME_medecin(PE_ident_ME)
);
CREATE TABLE PR_prescrire(
OR_ident INT,
MM_ident INT,
PR_posologie VARCHAR(256) NOT NULL,
PRIMARY KEY(OR_ident, MM_ident),
FOREIGN KEY(OR_ident) REFERENCES OR_ordonnance(OR_ident),
FOREIGN KEY(MM_ident) REFERENCES MM_medicament(MM_ident)
);
CREATE TABLE PS_presenter(
MM_ident INT,
EI_ident INT,
PRIMARY KEY(MM_ident, EI_ident),
FOREIGN KEY(MM_ident) REFERENCES MM_medicament(MM_ident),
FOREIGN KEY(EI_ident) REFERENCES EI_effet_indes(EI_ident)
);
CREATE TABLE MC_composer(
MM_ident INT,
CO_ident INT,
PRIMARY KEY(MM_ident, CO_ident),
FOREIGN KEY(MM_ident) REFERENCES MM_medicament(MM_ident),
FOREIGN KEY(CO_ident) REFERENCES CO_composant(CO_ident)
); |
