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)
); |
Partager