3sqMYEW0

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