· 8 years ago · Mar 04, 2018, 01:46 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='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema sys
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema sys
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `sys` DEFAULT CHARACTER SET utf8 ;
18USE `sys` ;
19
20-- -----------------------------------------------------
21-- Table `sys`.`band`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `sys`.`band` (
24 `BandID` INT(10) NOT NULL AUTO_INCREMENT,
25 `lowerBound` DOUBLE NOT NULL,
26 `upperBound` DOUBLE NOT NULL,
27 `discount` DOUBLE NOT NULL,
28 PRIMARY KEY (`BandID`))
29ENGINE = InnoDB
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `sys`.`basetask`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `sys`.`basetask` (
37 `taskName` VARCHAR(255) NOT NULL,
38 `department` VARCHAR(255) NOT NULL,
39 `description` VARCHAR(255) NOT NULL,
40 `price` DOUBLE NOT NULL,
41 `baseTaskID` INT(10) NOT NULL AUTO_INCREMENT,
42 PRIMARY KEY (`baseTaskID`))
43ENGINE = InnoDB
44AUTO_INCREMENT = 9
45DEFAULT CHARACTER SET = utf8;
46
47
48-- -----------------------------------------------------
49-- Table `sys`.`card_information`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `sys`.`card_information` (
52 `PaymentpaymentId` INT(10) NOT NULL,
53 `cardNumber` INT(16) NOT NULL,
54 `cardType` VARCHAR(255) NOT NULL,
55 `expiryDate` DATE NOT NULL)
56ENGINE = InnoDB
57DEFAULT CHARACTER SET = utf8;
58
59
60-- -----------------------------------------------------
61-- Table `sys`.`customer`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `sys`.`customer` (
64 `customerID` INT(10) NOT NULL AUTO_INCREMENT,
65 `name` VARCHAR(255) NOT NULL,
66 `surname` VARCHAR(255) NOT NULL,
67 `phoneNo` VARCHAR(255) NOT NULL,
68 `email` VARCHAR(255) NOT NULL,
69 `address` VARCHAR(255) NOT NULL,
70 `postcode` VARCHAR(255) NOT NULL,
71 PRIMARY KEY (`customerID`))
72ENGINE = InnoDB
73AUTO_INCREMENT = 9
74DEFAULT CHARACTER SET = utf8;
75
76
77-- -----------------------------------------------------
78-- Table `sys`.`job`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `sys`.`job` (
81 `JobID` INT(10) NOT NULL AUTO_INCREMENT,
82 `CustomercustomerID` INT(10) NOT NULL,
83 `orderDate` DATE NOT NULL,
84 `collectionDate` DATE NULL DEFAULT NULL,
85 `specInstructions` VARCHAR(255) NOT NULL,
86 `deadline` DATE NOT NULL,
87 `surcharge` DOUBLE NOT NULL,
88 `value` DOUBLE NOT NULL,
89 PRIMARY KEY (`JobID`),
90 INDEX `FKJob102033` (`CustomercustomerID` ASC),
91 CONSTRAINT `FKJob102033`
92 FOREIGN KEY (`CustomercustomerID`)
93 REFERENCES `sys`.`customer` (`customerID`))
94ENGINE = InnoDB
95AUTO_INCREMENT = 12
96DEFAULT CHARACTER SET = utf8;
97
98
99-- -----------------------------------------------------
100-- Table `sys`.`payment`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `sys`.`payment` (
103 `JobJobID` INT(10) NOT NULL,
104 `paymentDate` INT(10) NOT NULL,
105 PRIMARY KEY (`JobJobID`),
106 INDEX `FKPayment170102` (`JobJobID` ASC),
107 CONSTRAINT `FKPayment170102`
108 FOREIGN KEY (`JobJobID`)
109 REFERENCES `sys`.`job` (`JobID`))
110ENGINE = InnoDB
111DEFAULT CHARACTER SET = utf8;
112
113
114-- -----------------------------------------------------
115-- Table `sys`.`cardpayment`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `sys`.`cardpayment` (
118 `PaymentJobJobID` INT(10) NOT NULL,
119 `cardNo` INT(10) NOT NULL,
120 `expDate` DATE NOT NULL,
121 `type` INT(10) NOT NULL,
122 PRIMARY KEY (`PaymentJobJobID`),
123 INDEX `FKcardPaymen338903` (`PaymentJobJobID` ASC),
124 CONSTRAINT `FKcardPaymen338903`
125 FOREIGN KEY (`PaymentJobJobID`)
126 REFERENCES `sys`.`payment` (`JobJobID`))
127ENGINE = InnoDB
128DEFAULT CHARACTER SET = utf8;
129
130
131-- -----------------------------------------------------
132-- Table `sys`.`discount`
133-- -----------------------------------------------------
134CREATE TABLE IF NOT EXISTS `sys`.`discount` (
135 `discountID` INT(10) NOT NULL AUTO_INCREMENT,
136 `discountType` VARCHAR(255) NOT NULL,
137 PRIMARY KEY (`discountID`))
138ENGINE = InnoDB
139AUTO_INCREMENT = 12
140DEFAULT CHARACTER SET = utf8;
141
142
143-- -----------------------------------------------------
144-- Table `sys`.`valuedcustomer`
145-- -----------------------------------------------------
146CREATE TABLE IF NOT EXISTS `sys`.`valuedcustomer` (
147 `CustomercustomerID` INT(10) NOT NULL,
148 `DiscountdiscountID` INT(10) NULL DEFAULT NULL,
149 PRIMARY KEY (`CustomercustomerID`),
150 INDEX `FKValuedCust496434` (`DiscountdiscountID` ASC),
151 INDEX `FKValuedCust741192` (`CustomercustomerID` ASC),
152 CONSTRAINT `FKValuedCust496434`
153 FOREIGN KEY (`DiscountdiscountID`)
154 REFERENCES `sys`.`discount` (`discountID`),
155 CONSTRAINT `FKValuedCust741192`
156 FOREIGN KEY (`CustomercustomerID`)
157 REFERENCES `sys`.`customer` (`customerID`))
158ENGINE = InnoDB
159DEFAULT CHARACTER SET = utf8;
160
161
162-- -----------------------------------------------------
163-- Table `sys`.`suspendedcustomer`
164-- -----------------------------------------------------
165CREATE TABLE IF NOT EXISTS `sys`.`suspendedcustomer` (
166 `ValuedCustomerCustomercustomerID` INT(10) NOT NULL,
167 PRIMARY KEY (`ValuedCustomerCustomercustomerID`),
168 INDEX `FKsuspendedC671399` (`ValuedCustomerCustomercustomerID` ASC),
169 CONSTRAINT `FKsuspendedC671399`
170 FOREIGN KEY (`ValuedCustomerCustomercustomerID`)
171 REFERENCES `sys`.`valuedcustomer` (`CustomercustomerID`))
172ENGINE = InnoDB
173DEFAULT CHARACTER SET = utf8;
174
175
176-- -----------------------------------------------------
177-- Table `sys`.`defaultcustomer`
178-- -----------------------------------------------------
179CREATE TABLE IF NOT EXISTS `sys`.`defaultcustomer` (
180 `suspendedCustomerValuedCustomerCustomercustomerID` INT(10) NOT NULL,
181 PRIMARY KEY (`suspendedCustomerValuedCustomerCustomercustomerID`),
182 INDEX `FKdefaultCus575149` (`suspendedCustomerValuedCustomerCustomercustomerID` ASC),
183 CONSTRAINT `FKdefaultCus575149`
184 FOREIGN KEY (`suspendedCustomerValuedCustomerCustomercustomerID`)
185 REFERENCES `sys`.`suspendedcustomer` (`ValuedCustomerCustomercustomerID`))
186ENGINE = InnoDB
187DEFAULT CHARACTER SET = utf8;
188
189
190-- -----------------------------------------------------
191-- Table `sys`.`staff`
192-- -----------------------------------------------------
193CREATE TABLE IF NOT EXISTS `sys`.`staff` (
194 `staffID` INT(10) NOT NULL AUTO_INCREMENT,
195 `firstName` VARCHAR(255) NOT NULL,
196 `surName` VARCHAR(255) NOT NULL,
197 `employeeType` VARCHAR(255) NOT NULL,
198 PRIMARY KEY (`staffID`))
199ENGINE = InnoDB
200AUTO_INCREMENT = 8
201DEFAULT CHARACTER SET = utf8;
202
203
204-- -----------------------------------------------------
205-- Table `sys`.`task`
206-- -----------------------------------------------------
207CREATE TABLE IF NOT EXISTS `sys`.`task` (
208 `taskID` INT(10) NOT NULL AUTO_INCREMENT,
209 `shelf` VARCHAR(10) NULL DEFAULT NULL,
210 `startDate` DATETIME NULL DEFAULT NULL,
211 `endDate` DATETIME NULL DEFAULT NULL,
212 `StaffstaffID` INT(10) NULL DEFAULT NULL,
213 `JobJobID` INT(10) NOT NULL,
214 `baseTaskbaseTaskID` INT(10) NOT NULL,
215 PRIMARY KEY (`taskID`),
216 INDEX `FKTask954031` (`StaffstaffID` ASC),
217 INDEX `FKTask543586` (`JobJobID` ASC),
218 INDEX `FKTask168614` (`baseTaskbaseTaskID` ASC),
219 CONSTRAINT `FKTask168614`
220 FOREIGN KEY (`baseTaskbaseTaskID`)
221 REFERENCES `sys`.`basetask` (`baseTaskID`),
222 CONSTRAINT `FKTask543586`
223 FOREIGN KEY (`JobJobID`)
224 REFERENCES `sys`.`job` (`JobID`),
225 CONSTRAINT `FKTask954031`
226 FOREIGN KEY (`StaffstaffID`)
227 REFERENCES `sys`.`staff` (`staffID`))
228ENGINE = InnoDB
229AUTO_INCREMENT = 13
230DEFAULT CHARACTER SET = utf8;
231
232
233-- -----------------------------------------------------
234-- Table `sys`.`enquire`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `sys`.`enquire` (
237 `TasktaskID` INT(10) NOT NULL,
238 `enquireNumber` INT(11) NOT NULL,
239 `information` VARCHAR(255) NOT NULL,
240 PRIMARY KEY (`TasktaskID`, `enquireNumber`),
241 INDEX `FKenquire217603` (`TasktaskID` ASC),
242 CONSTRAINT `FKenquire217603`
243 FOREIGN KEY (`TasktaskID`)
244 REFERENCES `sys`.`task` (`taskID`))
245ENGINE = InnoDB
246DEFAULT CHARACTER SET = utf8;
247
248
249-- -----------------------------------------------------
250-- Table `sys`.`fixeddiscount`
251-- -----------------------------------------------------
252CREATE TABLE IF NOT EXISTS `sys`.`fixeddiscount` (
253 `DiscountdiscountID` INT(10) NOT NULL,
254 `percentDiscount` INT(10) NULL DEFAULT NULL,
255 PRIMARY KEY (`DiscountdiscountID`),
256 INDEX `FKFixedDisco316205` (`DiscountdiscountID` ASC),
257 CONSTRAINT `FKFixedDisco316205`
258 FOREIGN KEY (`DiscountdiscountID`)
259 REFERENCES `sys`.`discount` (`discountID`))
260ENGINE = InnoDB
261DEFAULT CHARACTER SET = utf8;
262
263
264-- -----------------------------------------------------
265-- Table `sys`.`flexiblediscount`
266-- -----------------------------------------------------
267CREATE TABLE IF NOT EXISTS `sys`.`flexiblediscount` (
268 `DiscountdiscountID` INT(10) NOT NULL,
269 `aquiredValue` INT(10) NOT NULL,
270 `bandBandID` INT(10) NOT NULL,
271 PRIMARY KEY (`DiscountdiscountID`),
272 INDEX `FKflexibleDi276331` (`DiscountdiscountID` ASC),
273 INDEX `FKflexibleDi397436` (`bandBandID` ASC),
274 CONSTRAINT `FKflexibleDi276331`
275 FOREIGN KEY (`DiscountdiscountID`)
276 REFERENCES `sys`.`discount` (`discountID`),
277 CONSTRAINT `FKflexibleDi397436`
278 FOREIGN KEY (`bandBandID`)
279 REFERENCES `sys`.`band` (`BandID`))
280ENGINE = InnoDB
281DEFAULT CHARACTER SET = utf8;
282
283
284-- -----------------------------------------------------
285-- Table `sys`.`variablediscount`
286-- -----------------------------------------------------
287CREATE TABLE IF NOT EXISTS `sys`.`variablediscount` (
288 `DiscountdiscountID` INT(10) NOT NULL,
289 `TasktaskID` INT(10) NOT NULL,
290 `amount` DOUBLE NOT NULL,
291 PRIMARY KEY (`DiscountdiscountID`, `TasktaskID`),
292 UNIQUE INDEX `DiscountdiscountID` (`DiscountdiscountID` ASC),
293 UNIQUE INDEX `TasktaskID` (`TasktaskID` ASC),
294 INDEX `FKvariableDi1347` (`DiscountdiscountID` ASC),
295 INDEX `FKvariableDi557826` (`TasktaskID` ASC),
296 CONSTRAINT `FKvariableDi1347`
297 FOREIGN KEY (`DiscountdiscountID`)
298 REFERENCES `sys`.`discount` (`discountID`),
299 CONSTRAINT `FKvariableDi557826`
300 FOREIGN KEY (`TasktaskID`)
301 REFERENCES `sys`.`task` (`taskID`))
302ENGINE = InnoDB
303DEFAULT CHARACTER SET = utf8;
304
305
306SET SQL_MODE=@OLD_SQL_MODE;
307SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
308SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;