· 9 years ago · Jan 26, 2017, 01:08 AM
1-- MySQL Script generated by MySQL Workbench
2-- 01/26/17 02:05:42
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 mydb
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `mydb` ;
14
15-- -----------------------------------------------------
16-- Schema mydb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19USE `mydb` ;
20
21-- -----------------------------------------------------
22-- Table `mydb`.`Employees`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `mydb`.`Employees` ;
25
26CREATE TABLE IF NOT EXISTS `mydb`.`Employees` (
27 `employeeID` INT NOT NULL,
28 `bsn` INT NULL,
29 `name` VARCHAR(45) NULL,
30 `surname` VARCHAR(45) NULL,
31 PRIMARY KEY (`employeeID`))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `mydb`.`Addresses`
37-- -----------------------------------------------------
38DROP TABLE IF EXISTS `mydb`.`Addresses` ;
39
40CREATE TABLE IF NOT EXISTS `mydb`.`Addresses` (
41 `addressID` INT NOT NULL,
42 `Country` VARCHAR(45) NULL,
43 `city` VARCHAR(45) NULL,
44 `street` VARCHAR(45) NULL,
45 `number` INT NULL,
46 `postal_code` VARCHAR(45) NULL,
47 PRIMARY KEY (`addressID`))
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `mydb`.`Positions`
53-- -----------------------------------------------------
54DROP TABLE IF EXISTS `mydb`.`Positions` ;
55
56CREATE TABLE IF NOT EXISTS `mydb`.`Positions` (
57 `positionID` INT NOT NULL,
58 `name` VARCHAR(45) NULL,
59 `description` VARCHAR(45) NULL,
60 `hour_fee` INT NULL,
61 PRIMARY KEY (`positionID`))
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `mydb`.`Degrees`
67-- -----------------------------------------------------
68DROP TABLE IF EXISTS `mydb`.`Degrees` ;
69
70CREATE TABLE IF NOT EXISTS `mydb`.`Degrees` (
71 `degreeID` INT NOT NULL,
72 `course` VARCHAR(45) NULL,
73 `level` VARCHAR(45) NULL,
74 `school` VARCHAR(45) NULL,
75 PRIMARY KEY (`degreeID`))
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`Projects`
81-- -----------------------------------------------------
82DROP TABLE IF EXISTS `mydb`.`Projects` ;
83
84CREATE TABLE IF NOT EXISTS `mydb`.`Projects` (
85 `projectID` INT NOT NULL,
86 `budget` INT NULL,
87 `allocated_hours` VARCHAR(45) NULL,
88 PRIMARY KEY (`projectID`))
89ENGINE = InnoDB;
90
91
92-- -----------------------------------------------------
93-- Table `mydb`.`Headquarters`
94-- -----------------------------------------------------
95DROP TABLE IF EXISTS `mydb`.`Headquarters` ;
96
97CREATE TABLE IF NOT EXISTS `mydb`.`Headquarters` (
98 `HQID` INT NOT NULL,
99 `building_name` VARCHAR(45) NULL,
100 `rooms` INT NULL,
101 `rent` INT NULL,
102 PRIMARY KEY (`HQID`))
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `mydb`.`EmployeeAddress`
108-- -----------------------------------------------------
109DROP TABLE IF EXISTS `mydb`.`EmployeeAddress` ;
110
111CREATE TABLE IF NOT EXISTS `mydb`.`EmployeeAddress` (
112 `EmployeeID` INT NOT NULL,
113 `addressID` INT NULL,
114 PRIMARY KEY (`EmployeeID`),
115 INDEX `FK_Address_JOIN_idx` (`addressID` ASC),
116 CONSTRAINT `FK_Employee_JOIN`
117 FOREIGN KEY (`EmployeeID`)
118 REFERENCES `mydb`.`Employees` (`employeeID`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION,
121 CONSTRAINT `FK_Address_JOIN`
122 FOREIGN KEY (`addressID`)
123 REFERENCES `mydb`.`Addresses` (`addressID`)
124 ON DELETE NO ACTION
125 ON UPDATE NO ACTION)
126ENGINE = InnoDB;
127
128
129-- -----------------------------------------------------
130-- Table `mydb`.`EmployeeDegree`
131-- -----------------------------------------------------
132DROP TABLE IF EXISTS `mydb`.`EmployeeDegree` ;
133
134CREATE TABLE IF NOT EXISTS `mydb`.`EmployeeDegree` (
135 `EmployeeID` INT NOT NULL,
136 `DegreeID` INT NULL,
137 PRIMARY KEY (`EmployeeID`),
138 INDEX `FK_Degree_JOIN_idx` (`DegreeID` ASC),
139 CONSTRAINT `FK_Employee_JOIN`
140 FOREIGN KEY (`EmployeeID`)
141 REFERENCES `mydb`.`Employees` (`employeeID`)
142 ON DELETE NO ACTION
143 ON UPDATE NO ACTION,
144 CONSTRAINT `FK_Degree_JOIN`
145 FOREIGN KEY (`DegreeID`)
146 REFERENCES `mydb`.`Degrees` (`degreeID`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION)
149ENGINE = InnoDB;
150
151
152-- -----------------------------------------------------
153-- Table `mydb`.`EmployeeProject`
154-- -----------------------------------------------------
155DROP TABLE IF EXISTS `mydb`.`EmployeeProject` ;
156
157CREATE TABLE IF NOT EXISTS `mydb`.`EmployeeProject` (
158 `EmployeeID` INT NOT NULL,
159 `ProjectID` INT NULL,
160 PRIMARY KEY (`EmployeeID`),
161 INDEX `FK_Employee_JOIN_idx` (`ProjectID` ASC),
162 CONSTRAINT `FK_Employee_JOIN`
163 FOREIGN KEY (`EmployeeID`)
164 REFERENCES `mydb`.`Employees` (`employeeID`)
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION,
167 CONSTRAINT `FK_Employee_JOIN`
168 FOREIGN KEY (`ProjectID`)
169 REFERENCES `mydb`.`Projects` (`projectID`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION)
172ENGINE = InnoDB;
173
174
175-- -----------------------------------------------------
176-- Table `mydb`.`EmployeePosition`
177-- -----------------------------------------------------
178DROP TABLE IF EXISTS `mydb`.`EmployeePosition` ;
179
180CREATE TABLE IF NOT EXISTS `mydb`.`EmployeePosition` (
181 `EmployeeID` INT NOT NULL,
182 `PositionID` INT NULL,
183 PRIMARY KEY (`EmployeeID`),
184 INDEX `FK_Positions_JOIN_idx` (`PositionID` ASC),
185 CONSTRAINT `FK_Employee_JOIN`
186 FOREIGN KEY (`EmployeeID`)
187 REFERENCES `mydb`.`Employees` (`employeeID`)
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION,
190 CONSTRAINT `FK_Positions_JOIN`
191 FOREIGN KEY (`PositionID`)
192 REFERENCES `mydb`.`Positions` (`positionID`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196
197
198-- -----------------------------------------------------
199-- Table `mydb`.`HeadquartersAddress`
200-- -----------------------------------------------------
201DROP TABLE IF EXISTS `mydb`.`HeadquartersAddress` ;
202
203CREATE TABLE IF NOT EXISTS `mydb`.`HeadquartersAddress` (
204 `HQID` INT NOT NULL,
205 `AddressID` INT NULL,
206 PRIMARY KEY (`HQID`),
207 INDEX `FK_Address_JOIN_idx` (`AddressID` ASC),
208 CONSTRAINT `FK_Headquarter_JOIN`
209 FOREIGN KEY (`HQID`)
210 REFERENCES `mydb`.`Headquarters` (`HQID`)
211 ON DELETE NO ACTION
212 ON UPDATE NO ACTION,
213 CONSTRAINT `FK_Address_JOIN`
214 FOREIGN KEY (`AddressID`)
215 REFERENCES `mydb`.`Addresses` (`addressID`)
216 ON DELETE NO ACTION
217 ON UPDATE NO ACTION)
218ENGINE = InnoDB;
219
220
221-- -----------------------------------------------------
222-- Table `mydb`.`ProjectHeadquarters`
223-- -----------------------------------------------------
224DROP TABLE IF EXISTS `mydb`.`ProjectHeadquarters` ;
225
226CREATE TABLE IF NOT EXISTS `mydb`.`ProjectHeadquarters` (
227 `ProjectID` INT NOT NULL,
228 `HQID` INT NOT NULL,
229 PRIMARY KEY (`ProjectID`),
230 INDEX `FK_HeadquartersProject_JOIN_idx` (`HQID` ASC),
231 CONSTRAINT `FK_ProjectHeadquarters_JOIN`
232 FOREIGN KEY (`ProjectID`)
233 REFERENCES `mydb`.`Projects` (`projectID`)
234 ON DELETE NO ACTION
235 ON UPDATE NO ACTION,
236 CONSTRAINT `FK_HeadquartersProject_JOIN`
237 FOREIGN KEY (`HQID`)
238 REFERENCES `mydb`.`Headquarters` (`HQID`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION)
241ENGINE = InnoDB;
242
243
244SET SQL_MODE=@OLD_SQL_MODE;
245SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
246SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;