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
| CREATE TABLE OZ_organization(
OZ_ident INT UNSIGNED AUTO_INCREMENT,
OZ_organization VARCHAR(30),
PRIMARY KEY(OZ_ident)
);
CREATE TABLE US_user(
US_ident INT UNSIGNED AUTO_INCREMENT,
US_sesa INT,
US_firstname VARCHAR(50),
US_lastname VARCHAR(50),
OZ_ident INT UNSIGNED,
COU_ident INT UNSIGNED,
PRIMARY KEY(US_ident),
UNIQUE(US_sesa),
FOREIGN KEY(OZ_ident) REFERENCES OZ_organization(OZ_ident),
FOREIGN KEY(COU_ident) REFERENCES COU_country(COU_ident)
);
CREATE TABLE TI_ticket(
TI_ident INT UNSIGNED AUTO_INCREMENT,
TI_num_ticket VARCHAR(30) NOT NULL,
TI_submitted_date DATE NOT NULL,
TI_last_resolved_date DATE,
TI_summary TEXT NOT NULL,
TI_priority CHAR(6) NOT NULL,
TI_status CHAR(13) NOT NULL,
TI_type_incident CHAR(10) NOT NULL,
TI_source CHAR(12) NOT NULL,
TI_first_country VARCHAR(30) NOT NULL,
US_ident_rq INT UNSIGNED NOT NULL,
US_ident_sub INT UNSIGNED NOT NULL,
AP_ident INT UNSIGNED NOT NULL,
AG_ident INT UNSIGNED NOT NULL,
PRIMARY KEY(TI_ident),
UNIQUE(TI_num_ticket),
FOREIGN KEY(US_ident_rq) REFERENCES US_user(US_ident),
FOREIGN KEY(US_ident_sub) REFERENCES US_user(US_ident),
FOREIGN KEY(AP_ident) REFERENCES AP_application(AP_ident),
FOREIGN KEY(AG_ident) REFERENCES AG_assigned_group(AG_ident)
); |
Partager