· 8 years ago · Dec 15, 2017, 12:14 PM
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`))
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `reptrace`.`CUSTOMER`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `reptrace`.`CUSTOMER` (
69 `customer_id` INT NOT NULL,
70 `person_id` INT NOT NULL,
71 PRIMARY KEY (`customer_id`))
72 ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `reptrace`.`PHONENUMBER`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `reptrace`.`PHONENUMBER` (
79 `phone_number_id` INT NOT NULL,
80 `person_id` INT NOT NULL,
81 `phone_number` INT NULL,
82 `type` VARCHAR(10) NULL,
83 PRIMARY KEY (`phone_number_id`))
84 ENGINE = InnoDB;
85
86-- -----------------------------------------------------
87-- Table `reptrace`.`VEHICLE`
88-- -----------------------------------------------------
89CREATE TABLE IF NOT EXISTS `reptrace`.`VEHICLE` (
90 `frame_number` INT NOT NULL,
91 `number_plate` VARCHAR(6) NOT NULL,
92 `brand` VARCHAR(15) NULL,
93 `type` VARCHAR(15) NULL,
94 `type_of_fuel` VARCHAR(7) NULL,
95 `color` VARCHAR(10) NULL,
96 PRIMARY KEY (`frame_number`))
97ENGINE = InnoDB;
98
99
100-- -----------------------------------------------------
101-- Table `reptrace`.`WORKORDER`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `reptrace`.`WORKORDER` (
104 `reptrace_id` INT NOT NULL,
105 `employee_id` INT NOT NULL,
106 `customer_id` INT NOT NULL,
107 `frame_number` INT NOT NULL,
108 `date` DATE NULL,
109 `type_of_work` VARCHAR(50) NULL,
110 PRIMARY KEY (`reptrace_id`))
111 ENGINE = InnoDB;
112
113
114-- -----------------------------------------------------
115-- Table `reptrace`.`PART`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `reptrace`.`PART` (
118 `part_id` INT NULL,
119 `part_name` VARCHAR(30) NULL,
120 `part_type` VARCHAR(30) NULL,
121 PRIMARY KEY (`part_id`))
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `reptrace`.`WORKORDER_PART_INTERSECTION`
127-- -----------------------------------------------------
128CREATE TABLE IF NOT EXISTS `reptrace`.`WORKORDER_PART_INTERSECTION` (
129 `part_id` INT NULL,
130 `reptrace_id` VARCHAR(45) NOT NULL,
131 `quantity` INT NULL,
132 PRIMARY KEY (`reptrace_id`))
133 ENGINE = InnoDB;
134
135
136SET SQL_MODE=@OLD_SQL_MODE;
137SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
138SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;