· 8 years ago · Nov 27, 2017, 02:12 PM
1-- MySQL Script generated by MySQL Workbench
2-- Mon Nov 27 14:09:59 2017
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema QUB
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema QUB
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `QUB` DEFAULT CHARACTER SET utf8 ;
18USE `QUB` ;
19
20-- -----------------------------------------------------
21-- Table `QUB`.`building`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `QUB`.`building` (
24 `building_id` INT NOT NULL AUTO_INCREMENT,
25 `name` VARCHAR(45) NULL,
26 `address_number` VARCHAR(5) NULL,
27 `address_street` VARCHAR(45) NULL,
28 `address_postcode` VARCHAR(45) NULL,
29 PRIMARY KEY (`building_id`))
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `QUB`.`apartment`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `QUB`.`apartment` (
37 `building_id` INT NOT NULL,
38 `apartment_number` SMALLINT NOT NULL,
39 `num_bedrooms` TINYINT NULL,
40 `num_bathrooms` TINYINT NULL,
41 `area` SMALLINT NULL,
42 PRIMARY KEY (`building_id`, `apartment_number`),
43 INDEX `fk_apartment_apartment_number_idx` (`apartment_number` ASC),
44 INDEX `fk_apartment_building_id_idx` (`building_id` ASC),
45 CONSTRAINT `fk_apartment_building_id`
46 FOREIGN KEY (`building_id`)
47 REFERENCES `QUB`.`building` (`building_id`)
48 ON DELETE NO ACTION
49 ON UPDATE NO ACTION)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `QUB`.`person`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `QUB`.`person` (
57 `person_id` INT NOT NULL AUTO_INCREMENT,
58 `first_name` VARCHAR(45) NOT NULL,
59 `last_name` VARCHAR(45) NOT NULL,
60 `emergency_contact_name` VARCHAR(45) NULL,
61 `emergency_contact_phone` VARCHAR(20) NULL,
62 PRIMARY KEY (`person_id`))
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `QUB`.`guest`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `QUB`.`guest` (
70 `person_id` INT NOT NULL,
71 PRIMARY KEY (`person_id`),
72 CONSTRAINT `fk_guest_person_id`
73 FOREIGN KEY (`person_id`)
74 REFERENCES `QUB`.`person` (`person_id`)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION)
77ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Table `QUB`.`guest_agreement`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `QUB`.`guest_agreement` (
84 `guest_agreement_id` INT NOT NULL AUTO_INCREMENT,
85 `building_id` INT NOT NULL,
86 `apartment_number` SMALLINT NOT NULL,
87 `start_date` DATE NULL,
88 `expected_duration` DATE NULL,
89 PRIMARY KEY (`guest_agreement_id`),
90 INDEX `fk_guest_agreement_building_id_idx` (`building_id` ASC),
91 INDEX `fk_guest_agreement_apartment_number_idx` (`apartment_number` ASC),
92 CONSTRAINT `fk_guest_agreement_building_id`
93 FOREIGN KEY (`building_id`)
94 REFERENCES `QUB`.`apartment` (`building_id`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION,
97 CONSTRAINT `fk_guest_agreement_apartment_number`
98 FOREIGN KEY (`apartment_number`)
99 REFERENCES `QUB`.`apartment` (`apartment_number`)
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `QUB`.`guest_agreement_guests`
107-- -----------------------------------------------------
108CREATE TABLE IF NOT EXISTS `QUB`.`guest_agreement_guests` (
109 `guest_agreement_id` INT NOT NULL,
110 `person_id` INT NOT NULL,
111 PRIMARY KEY (`guest_agreement_id`, `person_id`),
112 INDEX `fk_person_id_idx` (`person_id` ASC),
113 CONSTRAINT `fk_guest_agreement_id`
114 FOREIGN KEY (`guest_agreement_id`)
115 REFERENCES `QUB`.`guest_agreement` (`guest_agreement_id`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION,
118 CONSTRAINT `fk_guest_agreement_person_id`
119 FOREIGN KEY (`person_id`)
120 REFERENCES `QUB`.`guest` (`person_id`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION)
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `QUB`.`employee`
128-- -----------------------------------------------------
129CREATE TABLE IF NOT EXISTS `QUB`.`employee` (
130 `person_id` INT NOT NULL,
131 `salary` DECIMAL(19,4) NULL,
132 PRIMARY KEY (`person_id`),
133 CONSTRAINT `fk_employee_person_id`
134 FOREIGN KEY (`person_id`)
135 REFERENCES `QUB`.`person` (`person_id`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `QUB`.`technician`
143-- -----------------------------------------------------
144CREATE TABLE IF NOT EXISTS `QUB`.`technician` (
145 `person_id` INT NOT NULL,
146 PRIMARY KEY (`person_id`),
147 CONSTRAINT `fk_technician_person_id`
148 FOREIGN KEY (`person_id`)
149 REFERENCES `QUB`.`employee` (`person_id`)
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION)
152ENGINE = InnoDB;
153
154
155-- -----------------------------------------------------
156-- Table `QUB`.`skills`
157-- -----------------------------------------------------
158CREATE TABLE IF NOT EXISTS `QUB`.`skills` (
159 `skill_id` INT NOT NULL AUTO_INCREMENT,
160 `name` VARCHAR(45) NULL,
161 PRIMARY KEY (`skill_id`))
162ENGINE = InnoDB;
163
164
165-- -----------------------------------------------------
166-- Table `QUB`.`technician_skills`
167-- -----------------------------------------------------
168CREATE TABLE IF NOT EXISTS `QUB`.`technician_skills` (
169 `person_id` INT NOT NULL,
170 `skill_id` INT NOT NULL,
171 PRIMARY KEY (`person_id`, `skill_id`),
172 INDEX `fk_skill_id_idx` (`skill_id` ASC),
173 CONSTRAINT `fk_technician_skills_person_id`
174 FOREIGN KEY (`person_id`)
175 REFERENCES `QUB`.`technician` (`person_id`)
176 ON DELETE NO ACTION
177 ON UPDATE NO ACTION,
178 CONSTRAINT `fk_technician_skills_skill_id`
179 FOREIGN KEY (`skill_id`)
180 REFERENCES `QUB`.`skills` (`skill_id`)
181 ON DELETE NO ACTION
182 ON UPDATE NO ACTION)
183ENGINE = InnoDB;
184
185
186-- -----------------------------------------------------
187-- Table `QUB`.`manager`
188-- -----------------------------------------------------
189CREATE TABLE IF NOT EXISTS `QUB`.`manager` (
190 `person_id` INT NOT NULL,
191 PRIMARY KEY (`person_id`),
192 CONSTRAINT `fk_manager_person_id`
193 FOREIGN KEY (`person_id`)
194 REFERENCES `QUB`.`employee` (`person_id`)
195 ON DELETE NO ACTION
196 ON UPDATE NO ACTION)
197ENGINE = InnoDB;
198
199
200-- -----------------------------------------------------
201-- Table `QUB`.`tenant`
202-- -----------------------------------------------------
203CREATE TABLE IF NOT EXISTS `QUB`.`tenant` (
204 `person_id` INT NOT NULL,
205 `account_number` VARCHAR(34) NOT NULL,
206 PRIMARY KEY (`person_id`),
207 CONSTRAINT `fk_tenant_person_id`
208 FOREIGN KEY (`person_id`)
209 REFERENCES `QUB`.`person` (`person_id`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION)
212ENGINE = InnoDB;
213
214
215-- -----------------------------------------------------
216-- Table `QUB`.`lease`
217-- -----------------------------------------------------
218CREATE TABLE IF NOT EXISTS `QUB`.`lease` (
219 `lease_id` INT NOT NULL AUTO_INCREMENT,
220 `building_id` INT NOT NULL,
221 `apartment_number` SMALLINT NOT NULL,
222 `manager_id` INT NOT NULL,
223 `start_date` DATE NULL,
224 `expected_duration` DATE NULL,
225 `monthly_rent` DECIMAL(19,4) NULL,
226 PRIMARY KEY (`lease_id`),
227 INDEX `fk_building_id_idx` (`building_id` ASC),
228 INDEX `fk_apartment_number_idx` (`apartment_number` ASC),
229 INDEX `fk_manager_id_idx` (`manager_id` ASC),
230 CONSTRAINT `fk_lease_building_id`
231 FOREIGN KEY (`building_id`)
232 REFERENCES `QUB`.`apartment` (`building_id`)
233 ON DELETE NO ACTION
234 ON UPDATE NO ACTION,
235 CONSTRAINT `fk_lease_apartment_number`
236 FOREIGN KEY (`apartment_number`)
237 REFERENCES `QUB`.`apartment` (`apartment_number`)
238 ON DELETE NO ACTION
239 ON UPDATE NO ACTION,
240 CONSTRAINT `fk_lease_manager_id`
241 FOREIGN KEY (`manager_id`)
242 REFERENCES `QUB`.`manager` (`person_id`)
243 ON DELETE NO ACTION
244 ON UPDATE NO ACTION)
245ENGINE = InnoDB;
246
247
248-- -----------------------------------------------------
249-- Table `QUB`.`lease_tenants`
250-- -----------------------------------------------------
251CREATE TABLE IF NOT EXISTS `QUB`.`lease_tenants` (
252 `lease_id` INT NOT NULL,
253 `person_id` INT NOT NULL,
254 PRIMARY KEY (`lease_id`, `person_id`),
255 INDEX `fk_person_id_idx` (`person_id` ASC),
256 CONSTRAINT `fk_lease_tenants_lease_id`
257 FOREIGN KEY (`lease_id`)
258 REFERENCES `QUB`.`lease` (`lease_id`)
259 ON DELETE NO ACTION
260 ON UPDATE NO ACTION,
261 CONSTRAINT `fk_lease_tenants_person_id`
262 FOREIGN KEY (`person_id`)
263 REFERENCES `QUB`.`tenant` (`person_id`)
264 ON DELETE NO ACTION
265 ON UPDATE NO ACTION)
266ENGINE = InnoDB;
267
268
269-- -----------------------------------------------------
270-- Table `QUB`.`manages`
271-- -----------------------------------------------------
272CREATE TABLE IF NOT EXISTS `QUB`.`manages` (
273 `person_id` INT NOT NULL,
274 `building_id` INT NOT NULL,
275 `apartment_number` SMALLINT NOT NULL,
276 PRIMARY KEY (`person_id`, `building_id`, `apartment_number`),
277 INDEX `fk_building_id_idx` (`building_id` ASC),
278 INDEX `fk_apartment_number_idx` (`apartment_number` ASC),
279 CONSTRAINT `fk_manages_person_id`
280 FOREIGN KEY (`person_id`)
281 REFERENCES `QUB`.`manager` (`person_id`)
282 ON DELETE NO ACTION
283 ON UPDATE NO ACTION,
284 CONSTRAINT `fk_manages_building_id`
285 FOREIGN KEY (`building_id`)
286 REFERENCES `QUB`.`apartment` (`building_id`)
287 ON DELETE NO ACTION
288 ON UPDATE NO ACTION,
289 CONSTRAINT `fk_manages_apartment_number`
290 FOREIGN KEY (`apartment_number`)
291 REFERENCES `QUB`.`apartment` (`apartment_number`)
292 ON DELETE NO ACTION
293 ON UPDATE NO ACTION)
294ENGINE = InnoDB;
295
296
297-- -----------------------------------------------------
298-- Table `QUB`.`based_in`
299-- -----------------------------------------------------
300CREATE TABLE IF NOT EXISTS `QUB`.`based_in` (
301 `person_id` INT NOT NULL,
302 `building_id` INT NOT NULL,
303 `apartment_number` SMALLINT NOT NULL,
304 PRIMARY KEY (`person_id`, `building_id`, `apartment_number`),
305 INDEX `fk_building_id_idx` (`building_id` ASC),
306 INDEX `fk_apartment_number_idx` (`apartment_number` ASC),
307 CONSTRAINT `fk_based_in_person_id`
308 FOREIGN KEY (`person_id`)
309 REFERENCES `QUB`.`manager` (`person_id`)
310 ON DELETE NO ACTION
311 ON UPDATE NO ACTION,
312 CONSTRAINT `fk_based_in_building_id`
313 FOREIGN KEY (`building_id`)
314 REFERENCES `QUB`.`apartment` (`building_id`)
315 ON DELETE NO ACTION
316 ON UPDATE NO ACTION,
317 CONSTRAINT `fk_based_in_apartment_number`
318 FOREIGN KEY (`apartment_number`)
319 REFERENCES `QUB`.`apartment` (`apartment_number`)
320 ON DELETE NO ACTION
321 ON UPDATE NO ACTION)
322ENGINE = InnoDB;
323
324
325SET SQL_MODE=@OLD_SQL_MODE;
326SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
327SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;