· 8 years ago · Dec 15, 2017, 10:34 AM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Dec 14 15:52:14 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 reptrace
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema reptrace
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `reptrace` DEFAULT CHARACTER SET utf8 ;
18USE `reptrace` ;
19
20-- -----------------------------------------------------
21-- Table `reptrace`.`ORGANISATION`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `reptrace`.`ORGANISATION` (
24 `organisation_id` INT NOT NULL,
25 `coc_number` INT NULL,
26 `name` VARCHAR(30) NULL,
27 `address` VARCHAR(45) NULL,
28 `house_number` VARCHAR(6) NULL,
29 `postal_code` CHAR(6) NULL,
30 `city` VARCHAR(30) NULL,
31 `license_activation_date` DATE NULL,
32 PRIMARY KEY (`organisation_id`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `reptrace`.`PERSON`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `reptrace`.`PERSON` (
40 `person_id` INT NOT NULL,
41 `first_name` VARCHAR(20) NULL,
42 `last_name` VARCHAR(45) NULL,
43 `address` VARCHAR(45) NULL,
44 `house_number` VARCHAR(6) NULL,
45 `postal_code` CHAR(6) NULL,
46 `city` VARCHAR(30) NULL,
47 `email_address` VARCHAR(45) NULL,
48 PRIMARY KEY (`person_id`))
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `reptrace`.`EMPLOYEE`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `reptrace`.`EMPLOYEE` (
56 `employee_id` INT NOT NULL,
57 `organisation_id` INT NOT NULL,
58 `person_id` INT NOT NULL,
59 `username` VARCHAR(60) NOT NULL,
60 `password` CHAR(60) BINARY NOT NULL,
61 PRIMARY KEY (`employee_id`),
62 CONSTRAINT `organisation_id`
63 FOREIGN KEY ()
64 REFERENCES `reptrace`.`ORGANISATION` ()
65 ON DELETE SET NULL
66 ON UPDATE CASCADE,
67 CONSTRAINT `person_id`
68 FOREIGN KEY ()
69 REFERENCES `reptrace`.`PERSON` ()
70 ON DELETE SET NULL
71 ON UPDATE CASCADE)
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `reptrace`.`CUSTOMER`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `reptrace`.`CUSTOMER` (
79 `customer_id` INT NOT NULL,
80 `person_id` INT NOT NULL,
81 PRIMARY KEY (`customer_id`),
82 CONSTRAINT `person_id`
83 FOREIGN KEY ()
84 REFERENCES `reptrace`.`PERSON` ()
85 ON DELETE SET NULL
86 ON UPDATE CASCADE)
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `reptrace`.`PHONENUMBER`
92-- -----------------------------------------------------
93CREATE TABLE IF NOT EXISTS `reptrace`.`PHONENUMBER` (
94 `phone_number_id` INT NOT NULL,
95 `person_id` INT NOT NULL,
96 `phone_number` INT NULL,
97 `type` VARCHAR(10) NULL,
98 PRIMARY KEY (`phone_number_id`),
99 CONSTRAINT `person_id`
100 FOREIGN KEY ()
101 REFERENCES `reptrace`.`PERSON` ()
102 ON DELETE SET NULL
103 ON UPDATE CASCADE)
104ENGINE = InnoDB;
105
106
107-- -----------------------------------------------------
108-- Table `reptrace`.`VEHICLE`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `reptrace`.`VEHICLE` (
111 `frame_number` INT NOT NULL,
112 `number_plate` VARCHAR(6) NOT NULL,
113 `brand` VARCHAR(15) NULL,
114 `type` VARCHAR(15) NULL,
115 `type_of_fuel` VARCHAR(7) NULL,
116 `color` VARCHAR(10) NULL,
117 PRIMARY KEY (`frame_number`))
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `reptrace`.`WORKORDER`
123-- -----------------------------------------------------
124CREATE TABLE IF NOT EXISTS `reptrace`.`WORKORDER` (
125 `reptrace_id` INT NOT NULL,
126 `employee_id` INT NOT NULL,
127 `customer_id` INT NOT NULL,
128 `frame_number` INT NOT NULL,
129 `date` DATE NULL,
130 `type_of_work` VARCHAR(50) NULL,
131 PRIMARY KEY (`reptrace_id`),
132 CONSTRAINT `employee_id`
133 FOREIGN KEY ()
134 REFERENCES `reptrace`.`EMPLOYEE` ()
135 ON DELETE SET NULL
136 ON UPDATE CASCADE,
137 CONSTRAINT `customer_id`
138 FOREIGN KEY ()
139 REFERENCES `reptrace`.`CUSTOMER` ()
140 ON DELETE SET NULL
141 ON UPDATE CASCADE,
142 CONSTRAINT `frame_number`
143 FOREIGN KEY ()
144 REFERENCES `reptrace`.`VEHICLE` ()
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION)
147ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `reptrace`.`PART`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `reptrace`.`PART` (
154 `part_id` INT NULL,
155 `part_name` VARCHAR(30) NULL,
156 `part_type` VARCHAR(30) NULL,
157 PRIMARY KEY (`part_id`))
158ENGINE = InnoDB;
159
160
161-- -----------------------------------------------------
162-- Table `reptrace`.`WORKORDER_PART_INTERSECTION`
163-- -----------------------------------------------------
164CREATE TABLE IF NOT EXISTS `reptrace`.`WORKORDER_PART_INTERSECTION` (
165 `part_id` INT NULL,
166 `reptrace_id` VARCHAR(45) NOT NULL,
167 `quantity` INT NULL,
168 PRIMARY KEY (`reptrace_id`),
169 CONSTRAINT `reptrace_id`
170 FOREIGN KEY ()
171 REFERENCES `reptrace`.`WORKORDER` ()
172 ON DELETE SET NULL
173 ON UPDATE CASCADE,
174 CONSTRAINT `part_id`
175 FOREIGN KEY ()
176 REFERENCES `reptrace`.`PART` ()
177 ON DELETE SET NULL
178 ON UPDATE CASCADE)
179ENGINE = InnoDB;
180
181
182SET SQL_MODE=@OLD_SQL_MODE;
183SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
184SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;