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 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149
| CREATE TABLE country(
id_country INT AUTO_INCREMENT UNSIGNED,
country CHAR(3) NOT NULL,
region CHAR(4) NOT NULL,
PRIMARY KEY(id_country),
UNIQUE(country)
);
CREATE TABLE file_uploaded(
id_hash INT AUTO_INCREMENT UNSIGNED,
hash VARCHAR(128) NOT NULL,
PRIMARY KEY(id_hash)
);
CREATE TABLE location(
id_country INT AUTO_INCREMENT UNSIGNED,
id_location INT AUTO_INCREMENT UNSIGNED,
location VARCHAR(30) NOT NULL,
PRIMARY KEY(id_country, id_location),
UNIQUE(location),
FOREIGN KEY(id_country) REFERENCES country(id_country)
);
CREATE TABLE user_table(
id_country INT AUTO_INCREMENT UNSIGNED,
id_location INT AUTO_INCREMENT UNSIGNED,
id_user INT AUTO_INCREMENT UNSIGNED,
sesa INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
PRIMARY KEY(id_country, id_location, id_user),
UNIQUE(sesa),
FOREIGN KEY(id_country, id_location) REFERENCES location(id_country, id_location)
);
CREATE TABLE submitter(
id_country INT AUTO_INCREMENT UNSIGNED,
id_location INT AUTO_INCREMENT UNSIGNED,
id_user INT AUTO_INCREMENT UNSIGNED,
PRIMARY KEY(id_country, id_location, id_user),
FOREIGN KEY(id_country, id_location, id_user) REFERENCES user_table(id_country, id_location, id_user)
);
CREATE TABLE platform(
id_platform INT AUTO_INCREMENT UNSIGNED,
platform VARCHAR(30) NOT NULL,
platformowner VARCHAR(30) NOT NULL,
PRIMARY KEY(id_platform)
);
CREATE TABLE assign_group(
id_assign_group INT AUTO_INCREMENT UNSIGNED,
assign_group VARCHAR(30) NOT NULL,
PRIMARY KEY(id_assign_group)
);
CREATE TABLE company(
id_company INT AUTO_INCREMENT UNSIGNED,
company VARCHAR(30) NOT NULL,
buunitname VARCHAR(30),
PRIMARY KEY(id_company)
);
CREATE TABLE application(
id_platform INT AUTO_INCREMENT UNSIGNED,
id_application INT AUTO_INCREMENT UNSIGNED,
appli_name VARCHAR(30) NOT NULL,
publisher VARCHAR(30) NOT NULL,
PRIMARY KEY(id_platform, id_application),
UNIQUE(appli_name),
FOREIGN KEY(id_platform) REFERENCES platform(id_platform)
);
CREATE TABLE license_owner(
id_country INT AUTO_INCREMENT UNSIGNED,
id_location INT AUTO_INCREMENT UNSIGNED,
id_user INT AUTO_INCREMENT UNSIGNED,
id_country_manager INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_location_manager INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_user_manager INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_company INT AUTO_INCREMENT UNSIGNED NOT NULL,
PRIMARY KEY(id_country, id_location, id_user),
UNIQUE(id_country_manager, id_location_manager, id_user_manager),
FOREIGN KEY(id_country, id_location, id_user) REFERENCES user_table(id_country, id_location, id_user),
FOREIGN KEY(id_country_manager, id_location_manager, id_user_manager) REFERENCES license_owner(id_country, id_location, id_user),
FOREIGN KEY(id_company) REFERENCES company(id_company)
);
CREATE TABLE customer(
id_country INT AUTO_INCREMENT UNSIGNED,
id_location INT AUTO_INCREMENT UNSIGNED,
id_user INT AUTO_INCREMENT UNSIGNED,
id_company INT AUTO_INCREMENT UNSIGNED NOT NULL,
PRIMARY KEY(id_country, id_location, id_user),
FOREIGN KEY(id_country, id_location, id_user) REFERENCES user_table(id_country, id_location, id_user),
FOREIGN KEY(id_company) REFERENCES company(id_company)
);
CREATE TABLE mail(
id_country INT AUTO_INCREMENT UNSIGNED,
id_location INT AUTO_INCREMENT UNSIGNED,
id_user INT AUTO_INCREMENT UNSIGNED,
id_mail INT AUTO_INCREMENT UNSIGNED,
email VARCHAR(30) NOT NULL,
PRIMARY KEY(id_country, id_location, id_user, id_mail),
FOREIGN KEY(id_country, id_location, id_user) REFERENCES license_owner(id_country, id_location, id_user)
);
CREATE TABLE license(
id_license INT AUTO_INCREMENT UNSIGNED,
activate_date DATE NOT NULL,
deactivate_date DATE,
id_country INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_location INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_user INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_platform INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_application INT AUTO_INCREMENT UNSIGNED NOT NULL,
PRIMARY KEY(id_license),
FOREIGN KEY(id_country, id_location, id_user) REFERENCES license_owner(id_country, id_location, id_user),
FOREIGN KEY(id_platform, id_application) REFERENCES application(id_platform, id_application)
);
CREATE TABLE ticket(
id_assign_group INT AUTO_INCREMENT UNSIGNED,
id_ticket INT AUTO_INCREMENT UNSIGNED,
num_ticket BIGINT NOT NULL,
submitted_date DATE NOT NULL,
last_resolved_date DATE NOT NULL,
summary VARCHAR(200) NOT NULL,
priority CHAR(6) NOT NULL,
status CHAR(13) NOT NULL,
type_incident CHAR(10) NOT NULL,
source CHAR(12) NOT NULL,
first_country VARCHAR(30) NOT NULL,
id_country INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_location INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_user INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_country_1 INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_location_1 INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_user_1 INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_platform INT AUTO_INCREMENT UNSIGNED NOT NULL,
id_application INT AUTO_INCREMENT UNSIGNED NOT NULL,
PRIMARY KEY(id_assign_group, id_ticket),
UNIQUE(num_ticket),
FOREIGN KEY(id_assign_group) REFERENCES assign_group(id_assign_group),
FOREIGN KEY(id_country, id_location, id_user) REFERENCES submitter(id_country, id_location, id_user),
FOREIGN KEY(id_country_1, id_location_1, id_user_1) REFERENCES customer(id_country, id_location, id_user),
FOREIGN KEY(id_platform, id_application) REFERENCES application(id_platform, id_application)
); |
Partager