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