· 6 years ago · Dec 02, 2019, 03:44 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema assignment 1
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema assignment 1
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `assignment 1` ;
18USE `assignment 1` ;
19
20-- -----------------------------------------------------
21-- Table `assignment 1`.`Suppliers`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `assignment 1`.`Suppliers` ;
24
25CREATE TABLE IF NOT EXISTS `assignment 1`.`Suppliers` (
26 `supplier_id` INT NOT NULL,
27 `supplier_name` VARCHAR(45) NULL,
28 `supplier_address` VARCHAR(45) NULL,
29 `supplier_city` VARCHAR(45) NULL,
30 `supplier_province` VARCHAR(2) NULL,
31 `supplier_contact_number` VARCHAR(15) NULL,
32 PRIMARY KEY (`supplier_id`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `assignment 1`.`Members`
38-- -----------------------------------------------------
39DROP TABLE IF EXISTS `assignment 1`.`Members` ;
40
41CREATE TABLE IF NOT EXISTS `assignment 1`.`Members` (
42 `member_id` INT NOT NULL,
43 `member_name` VARCHAR(45) NULL,
44 `member_address` VARCHAR(45) NULL,
45 `member_city` VARCHAR(45) NULL,
46 `member_province` VARCHAR(2) NULL,
47 `member_contact_number` VARCHAR(15) NULL,
48 `member_subscription` VARCHAR(25) NULL,
49 PRIMARY KEY (`member_id`))
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `assignment 1`.`Member_Account`
55-- -----------------------------------------------------
56DROP TABLE IF EXISTS `assignment 1`.`Member_Account` ;
57
58CREATE TABLE IF NOT EXISTS `assignment 1`.`Member_Account` (
59 `member_id` INT NOT NULL,
60 `member_username` VARCHAR(30) NULL,
61 `member_password` VARCHAR(50) NULL,
62 PRIMARY KEY (`member_id`),
63 CONSTRAINT `fk_Member_Account_Members`
64 FOREIGN KEY (`member_id`)
65 REFERENCES `assignment 1`.`Members` (`member_id`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION)
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `assignment 1`.`Employees`
73-- -----------------------------------------------------
74DROP TABLE IF EXISTS `assignment 1`.`Employees` ;
75
76CREATE TABLE IF NOT EXISTS `assignment 1`.`Employees` (
77 `employee_id` INT NOT NULL,
78 `employee_name` VARCHAR(45) NULL,
79 `employee_contact` VARCHAR(15) NULL,
80 `employee_address` VARCHAR(45) NULL,
81 `employee_city` VARCHAR(45) NULL,
82 `employee_province` VARCHAR(2) NULL,
83 PRIMARY KEY (`employee_id`))
84ENGINE = InnoDB;
85
86
87-- -----------------------------------------------------
88-- Table `assignment 1`.`Invoices`
89-- -----------------------------------------------------
90DROP TABLE IF EXISTS `assignment 1`.`Invoices` ;
91
92CREATE TABLE IF NOT EXISTS `assignment 1`.`Invoices` (
93 `invoice_id` INT NOT NULL,
94 `invoice_amount` FLOAT NULL,
95 `invoice_payment` FLOAT NULL,
96 `invoice_credit` FLOAT NULL,
97 `invoice_due_date` DATE NULL,
98 `employee_id` INT NOT NULL,
99 PRIMARY KEY (`invoice_id`),
100 INDEX `fk_Invoices_Employees1_idx` (`employee_id` ASC) VISIBLE,
101 CONSTRAINT `fk_Invoices_Employees1`
102 FOREIGN KEY (`employee_id`)
103 REFERENCES `assignment 1`.`Employees` (`employee_id`)
104 ON DELETE NO ACTION
105 ON UPDATE NO ACTION)
106ENGINE = InnoDB;
107
108
109-- -----------------------------------------------------
110-- Table `assignment 1`.`Inventory`
111-- -----------------------------------------------------
112DROP TABLE IF EXISTS `assignment 1`.`Inventory` ;
113
114CREATE TABLE IF NOT EXISTS `assignment 1`.`Inventory` (
115 `inventory_item_id` INT NOT NULL,
116 `inventory_item_description` VARCHAR(50) NULL,
117 `inventory_item_qty` INT NULL,
118 `supplier_id` INT NOT NULL,
119 `invoice_id` INT NOT NULL,
120 PRIMARY KEY (`inventory_item_id`),
121 INDEX `fk_Inventory_Suppliers1_idx` (`supplier_id` ASC) VISIBLE,
122 INDEX `fk_Inventory_Invoices1_idx` (`invoice_id` ASC) VISIBLE,
123 CONSTRAINT `fk_Inventory_Suppliers1`
124 FOREIGN KEY (`supplier_id`)
125 REFERENCES `assignment 1`.`Suppliers` (`supplier_id`)
126 ON DELETE NO ACTION
127 ON UPDATE NO ACTION,
128 CONSTRAINT `fk_Inventory_Invoices1`
129 FOREIGN KEY (`invoice_id`)
130 REFERENCES `assignment 1`.`Invoices` (`invoice_id`)
131 ON DELETE NO ACTION
132 ON UPDATE NO ACTION)
133ENGINE = InnoDB;
134
135
136-- -----------------------------------------------------
137-- Table `assignment 1`.`Calendar`
138-- -----------------------------------------------------
139DROP TABLE IF EXISTS `assignment 1`.`Calendar` ;
140
141CREATE TABLE IF NOT EXISTS `assignment 1`.`Calendar` (
142 `full_date` DATETIME NOT NULL,
143 `isHoliday` TINYINT NULL,
144 PRIMARY KEY (`full_date`))
145ENGINE = InnoDB;
146
147
148-- -----------------------------------------------------
149-- Table `assignment 1`.`Events`
150-- -----------------------------------------------------
151DROP TABLE IF EXISTS `assignment 1`.`Events` ;
152
153CREATE TABLE IF NOT EXISTS `assignment 1`.`Events` (
154 `event_id` INT NOT NULL,
155 `event_date` DATE NULL,
156 `employee_id` INT NULL,
157 `event_type` VARCHAR(20) NULL,
158 `event_decription` VARCHAR(50) NULL,
159 `full_date` DATETIME NOT NULL,
160 PRIMARY KEY (`event_id`),
161 INDEX `fk_Events_Calendar1_idx` (`full_date` ASC) VISIBLE,
162 CONSTRAINT `fk_Events_Calendar1`
163 FOREIGN KEY (`full_date`)
164 REFERENCES `assignment 1`.`Calendar` (`full_date`)
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION)
167ENGINE = InnoDB;
168
169
170-- -----------------------------------------------------
171-- Table `assignment 1`.`Reservations`
172-- -----------------------------------------------------
173DROP TABLE IF EXISTS `assignment 1`.`Reservations` ;
174
175CREATE TABLE IF NOT EXISTS `assignment 1`.`Reservations` (
176 `reso_id` INT NOT NULL,
177 `invoice_id` INT NULL,
178 `reso_time` DATETIME NULL,
179 `reso_type` VARCHAR(10) NULL,
180 `member_id` INT NOT NULL,
181 `invoice_id` INT NOT NULL,
182 `employee_id` INT NOT NULL,
183 PRIMARY KEY (`reso_id`),
184 INDEX `fk_Reservations_Members1_idx` (`member_id` ASC) VISIBLE,
185 INDEX `fk_Reservations_Invoices1_idx` (`invoice_id` ASC) VISIBLE,
186 INDEX `fk_Reservations_Employees1_idx` (`employee_id` ASC) VISIBLE,
187 CONSTRAINT `fk_Reservations_Members1`
188 FOREIGN KEY (`member_id`)
189 REFERENCES `assignment 1`.`Members` (`member_id`)
190 ON DELETE NO ACTION
191 ON UPDATE NO ACTION,
192 CONSTRAINT `fk_Reservations_Invoices1`
193 FOREIGN KEY (`invoice_id`)
194 REFERENCES `assignment 1`.`Invoices` (`invoice_id`)
195 ON DELETE NO ACTION
196 ON UPDATE NO ACTION,
197 CONSTRAINT `fk_Reservations_Employees1`
198 FOREIGN KEY (`employee_id`)
199 REFERENCES `assignment 1`.`Employees` (`employee_id`)
200 ON DELETE NO ACTION
201 ON UPDATE NO ACTION)
202ENGINE = InnoDB;
203
204
205-- -----------------------------------------------------
206-- Table `assignment 1`.`Tournaments`
207-- -----------------------------------------------------
208DROP TABLE IF EXISTS `assignment 1`.`Tournaments` ;
209
210CREATE TABLE IF NOT EXISTS `assignment 1`.`Tournaments` (
211 `tourney_id` INT NOT NULL,
212 `tourney_name` VARCHAR(50) NULL,
213 `tourney_cost` FLOAT NULL,
214 `employee_id` INT NOT NULL,
215 PRIMARY KEY (`tourney_id`),
216 INDEX `fk_Tournaments_Employees1_idx` (`employee_id` ASC) VISIBLE,
217 CONSTRAINT `fk_Tournaments_Employees1`
218 FOREIGN KEY (`employee_id`)
219 REFERENCES `assignment 1`.`Employees` (`employee_id`)
220 ON DELETE NO ACTION
221 ON UPDATE NO ACTION)
222ENGINE = InnoDB;
223
224
225-- -----------------------------------------------------
226-- Table `assignment 1`.`Membership_Invoice`
227-- -----------------------------------------------------
228DROP TABLE IF EXISTS `assignment 1`.`Membership_Invoice` ;
229
230CREATE TABLE IF NOT EXISTS `assignment 1`.`Membership_Invoice` (
231 `invoice_id` INT NOT NULL,
232 `member_id` INT NOT NULL,
233 PRIMARY KEY (`invoice_id`, `member_id`),
234 INDEX `fk_Invoices_has_Members_Members1_idx` (`member_id` ASC) VISIBLE,
235 INDEX `fk_Invoices_has_Members_Invoices1_idx` (`invoice_id` ASC) VISIBLE,
236 CONSTRAINT `fk_Invoices_has_Members_Invoices1`
237 FOREIGN KEY (`invoice_id`)
238 REFERENCES `assignment 1`.`Invoices` (`invoice_id`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION,
241 CONSTRAINT `fk_Invoices_has_Members_Members1`
242 FOREIGN KEY (`member_id`)
243 REFERENCES `assignment 1`.`Members` (`member_id`)
244 ON DELETE NO ACTION
245 ON UPDATE NO ACTION)
246ENGINE = InnoDB;
247
248
249-- -----------------------------------------------------
250-- Table `assignment 1`.`Tourney_Games`
251-- -----------------------------------------------------
252DROP TABLE IF EXISTS `assignment 1`.`Tourney_Games` ;
253
254CREATE TABLE IF NOT EXISTS `assignment 1`.`Tourney_Games` (
255 `event_id` INT NOT NULL,
256 `tourney_id` INT NOT NULL,
257 PRIMARY KEY (`event_id`, `tourney_id`),
258 INDEX `fk_Events_has_Tournaments_Tournaments1_idx` (`tourney_id` ASC) VISIBLE,
259 INDEX `fk_Events_has_Tournaments_Events1_idx` (`event_id` ASC) VISIBLE,
260 CONSTRAINT `fk_Events_has_Tournaments_Events1`
261 FOREIGN KEY (`event_id`)
262 REFERENCES `assignment 1`.`Events` (`event_id`)
263 ON DELETE NO ACTION
264 ON UPDATE NO ACTION,
265 CONSTRAINT `fk_Events_has_Tournaments_Tournaments1`
266 FOREIGN KEY (`tourney_id`)
267 REFERENCES `assignment 1`.`Tournaments` (`tourney_id`)
268 ON DELETE NO ACTION
269 ON UPDATE NO ACTION)
270ENGINE = InnoDB;
271
272
273-- -----------------------------------------------------
274-- Table `assignment 1`.`Tourney_Membership`
275-- -----------------------------------------------------
276DROP TABLE IF EXISTS `assignment 1`.`Tourney_Membership` ;
277
278CREATE TABLE IF NOT EXISTS `assignment 1`.`Tourney_Membership` (
279 `invoice_id` INT NOT NULL,
280 `member_id` INT NOT NULL,
281 `tourney_id` INT NOT NULL,
282 PRIMARY KEY (`invoice_id`, `member_id`, `tourney_id`),
283 INDEX `fk_Membership_Invoice_has_Tournaments_Tournaments1_idx` (`tourney_id` ASC) VISIBLE,
284 INDEX `fk_Membership_Invoice_has_Tournaments_Membership_Invoice1_idx` (`invoice_id` ASC, `member_id` ASC) VISIBLE,
285 CONSTRAINT `fk_Membership_Invoice_has_Tournaments_Membership_Invoice1`
286 FOREIGN KEY (`invoice_id` , `member_id`)
287 REFERENCES `assignment 1`.`Membership_Invoice` (`invoice_id` , `member_id`)
288 ON DELETE NO ACTION
289 ON UPDATE NO ACTION,
290 CONSTRAINT `fk_Membership_Invoice_has_Tournaments_Tournaments1`
291 FOREIGN KEY (`tourney_id`)
292 REFERENCES `assignment 1`.`Tournaments` (`tourney_id`)
293 ON DELETE NO ACTION
294 ON UPDATE NO ACTION)
295ENGINE = InnoDB;
296
297
298-- -----------------------------------------------------
299-- Table `assignment 1`.`Employee_Schedules`
300-- -----------------------------------------------------
301DROP TABLE IF EXISTS `assignment 1`.`Employee_Schedules` ;
302
303CREATE TABLE IF NOT EXISTS `assignment 1`.`Employee_Schedules` (
304 `Employees_employee_id` INT NOT NULL,
305 `Calendar_full_date` DATETIME NOT NULL,
306 `shift_hours` FLOAT NULL,
307 PRIMARY KEY (`Employees_employee_id`, `Calendar_full_date`),
308 INDEX `fk_Employees_has_Calendar_Calendar1_idx` (`Calendar_full_date` ASC) VISIBLE,
309 INDEX `fk_Employees_has_Calendar_Employees1_idx` (`Employees_employee_id` ASC) VISIBLE,
310 CONSTRAINT `fk_Employees_has_Calendar_Employees1`
311 FOREIGN KEY (`Employees_employee_id`)
312 REFERENCES `assignment 1`.`Employees` (`employee_id`)
313 ON DELETE NO ACTION
314 ON UPDATE NO ACTION,
315 CONSTRAINT `fk_Employees_has_Calendar_Calendar1`
316 FOREIGN KEY (`Calendar_full_date`)
317 REFERENCES `assignment 1`.`Calendar` (`full_date`)
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;