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
|
CREATE TABLE TYPE_MACHINE(
TM_KEY BYTE,
ETQ CHAR(16) NOT NULL,
DOC VARCHAR(50),
PRIMARY KEY(TM_KEY),
UNIQUE(ETQ)
);
CREATE TABLE PARA_DEF(
TM_KEY BYTE,
PD_KEY BYTE,
ETQ CHAR(16),
DOC VARCHAR(50),
TYPE BYTE,
TM_KEY_1 BYTE NOT NULL,
PRIMARY KEY(TM_KEY, PD_KEY),
FOREIGN KEY(TM_KEY_1) REFERENCES TYPE_MACHINE(TM_KEY)
);
CREATE TABLE LOCALISATION(
L_KEY BYTE,
ETQ CHAR(8),
DOC VARCHAR(50),
PRIMARY KEY(L_KEY)
);
CREATE TABLE ERR_DEF(
TM_KEY BYTE,
ED_KEY BYTE,
ETQ CHAR(16),
DOC VARCHAR(50),
TYPE BYTE,
TM_KEY_1 BYTE NOT NULL,
PRIMARY KEY(TM_KEY, ED_KEY),
FOREIGN KEY(TM_KEY_1) REFERENCES TYPE_MACHINE(TM_KEY)
);
CREATE TABLE UTILISATEUR(
U_KEY BYTE,
NOM VARCHAR(50) NOT NULL,
ADMIN LOGICAL,
PRIMARY KEY(U_KEY),
UNIQUE(NOM)
);
CREATE TABLE MACHINE(
TM_KEY BYTE,
L_KEY BYTE,
ETQ CHAR(16),
DOC CHAR(50),
L_KEY_1 BYTE NOT NULL,
TM_KEY_1 BYTE NOT NULL,
PRIMARY KEY(TM_KEY, L_KEY),
UNIQUE(L_KEY_1),
FOREIGN KEY(L_KEY_1) REFERENCES LOCALISATION(L_KEY),
FOREIGN KEY(TM_KEY_1) REFERENCES TYPE_MACHINE(TM_KEY)
);
CREATE TABLE PARA(
TM_KEY BYTE,
L_KEY BYTE,
PD_KEY BYTE,
VAL VARIABLE,
TM_KEY_1 BYTE NOT NULL,
PD_KEY_1 BYTE NOT NULL,
TM_KEY_2 BYTE NOT NULL,
L_KEY_1 BYTE NOT NULL,
PRIMARY KEY(TM_KEY, L_KEY, PD_KEY),
FOREIGN KEY(TM_KEY_1, PD_KEY_1) REFERENCES PARA_DEF(TM_KEY, PD_KEY),
FOREIGN KEY(TM_KEY_2, L_KEY_1) REFERENCES MACHINE(TM_KEY, L_KEY)
);
CREATE TABLE ERR(
E_KEY INT,
TM_KEY BYTE NOT NULL,
L_KEY BYTE NOT NULL,
ED_KEY BYTE NOT NULL,
DEB DATETIME,
FIN DATETIME,
TM_KEY_1 BYTE NOT NULL,
ED_KEY_1 BYTE NOT NULL,
TM_KEY_2 BYTE NOT NULL,
L_KEY_1 BYTE NOT NULL,
PRIMARY KEY(E_KEY),
FOREIGN KEY(TM_KEY_1, ED_KEY_1) REFERENCES ERR_DEF(TM_KEY, ED_KEY),
FOREIGN KEY(TM_KEY_2, L_KEY_1) REFERENCES MACHINE(TM_KEY, L_KEY)
);
CREATE TABLE MODIFIER(
TM_KEY_1 BYTE,
L_KEY_1 BYTE,
PD_KEY_1 BYTE,
U_KEY_1 BYTE,
TM_KEY BYTE NOT NULL,
PD_KEY BYTE,
L_KEY BYTE,
MEM_PARA VARCHAR(50),
NEW_PARA VARCHAR(50),
DATE_MODIF DATETIME,
U_KEY VARCHAR(50),
PRIMARY KEY(TM_KEY_1, L_KEY_1, PD_KEY_1, U_KEY_1),
FOREIGN KEY(TM_KEY_1, L_KEY_1, PD_KEY_1) REFERENCES PARA(TM_KEY, L_KEY, PD_KEY),
FOREIGN KEY(U_KEY_1) REFERENCES UTILISATEUR(U_KEY)
);
CREATE TABLE ACQUITER(
E_KEY_1 INT,
U_KEY_1 BYTE,
E_KEY INT NOT NULL,
DATE_ACQ DATE,
U_KEY BYTE,
PRIMARY KEY(E_KEY_1, U_KEY_1),
FOREIGN KEY(E_KEY_1) REFERENCES ERR(E_KEY),
FOREIGN KEY(U_KEY_1) REFERENCES UTILISATEUR(U_KEY)
);
CREATE TABLE CONSULTER_PARA(
TM_KEY BYTE,
L_KEY BYTE,
PD_KEY BYTE,
U_KEY BYTE,
PRIMARY KEY(TM_KEY, L_KEY, PD_KEY, U_KEY),
FOREIGN KEY(TM_KEY, L_KEY, PD_KEY) REFERENCES PARA(TM_KEY, L_KEY, PD_KEY),
FOREIGN KEY(U_KEY) REFERENCES UTILISATEUR(U_KEY)
);
CREATE TABLE CONSULTER_ERR(
E_KEY INT,
U_KEY BYTE,
PRIMARY KEY(E_KEY, U_KEY),
FOREIGN KEY(E_KEY) REFERENCES ERR(E_KEY),
FOREIGN KEY(U_KEY) REFERENCES UTILISATEUR(U_KEY)
); |
Partager