· 9 years ago · Jan 25, 2017, 10:36 PM
1-- MySQL Script generated by MySQL Workbench
2-- 01/25/17 22:29:03
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 AUTO_INCREMENT,
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 AUTO_INCREMENT,
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 AUTO_INCREMENT,
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 AUTO_INCREMENT,
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 AUTO_INCREMENT,
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 AUTO_INCREMENT,
99 `building_name` VARCHAR(45) NULL,
100 `rooms` INT NULL,
101 `rent` INT NULL,
102 `Headquarterscol` VARCHAR(45) NULL,
103 PRIMARY KEY (`HQID`))
104ENGINE = InnoDB;
105
106
107-- -----------------------------------------------------
108-- Table `mydb`.`EmployeeAddress`
109-- -----------------------------------------------------
110DROP TABLE IF EXISTS `mydb`.`EmployeeAddress` ;
111
112CREATE TABLE IF NOT EXISTS `mydb`.`EmployeeAddress` (
113 `EmployeeID` INT NOT NULL,
114 `addressID` INT NULL,
115 PRIMARY KEY (`EmployeeID`),
116 INDEX `FK_Address_JOIN_idx` (`addressID` ASC),
117 CONSTRAINT `FK_Employee_JOIN`
118 FOREIGN KEY (`EmployeeID`)
119 REFERENCES `mydb`.`Employees` (`employeeID`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION,
122 CONSTRAINT `FK_Address_JOIN`
123 FOREIGN KEY (`addressID`)
124 REFERENCES `mydb`.`Addresses` (`addressID`)
125 ON DELETE NO ACTION
126 ON UPDATE NO ACTION)
127ENGINE = InnoDB;
128
129
130-- -----------------------------------------------------
131-- Table `mydb`.`EmployeeDegree`
132-- -----------------------------------------------------
133DROP TABLE IF EXISTS `mydb`.`EmployeeDegree` ;
134
135CREATE TABLE IF NOT EXISTS `mydb`.`EmployeeDegree` (
136 `EmployeeID` INT NOT NULL,
137 `DegreeID` INT NULL,
138 PRIMARY KEY (`EmployeeID`),
139 INDEX `FK_Degree_JOIN_idx` (`DegreeID` ASC),
140 CONSTRAINT `FK_EmployeeDegree_JOIN`
141 FOREIGN KEY (`EmployeeID`)
142 REFERENCES `mydb`.`Employees` (`employeeID`)
143 ON DELETE NO ACTION
144 ON UPDATE NO ACTION,
145 CONSTRAINT `FK_DegreeEmployee_JOIN`
146 FOREIGN KEY (`DegreeID`)
147 REFERENCES `mydb`.`Degrees` (`degreeID`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION)
150ENGINE = InnoDB;
151
152
153-- -----------------------------------------------------
154-- Table `mydb`.`EmployeeProject`
155-- -----------------------------------------------------
156DROP TABLE IF EXISTS `mydb`.`EmployeeProject` ;
157
158CREATE TABLE IF NOT EXISTS `mydb`.`EmployeeProject` (
159 `EmployeeID` INT NOT NULL,
160 `ProjectID` INT NULL,
161 PRIMARY KEY (`EmployeeID`),
162 INDEX `FK_Employee_JOIN_idx` (`ProjectID` ASC),
163 CONSTRAINT `FK_EmployeeProject_JOIN`
164 FOREIGN KEY (`EmployeeID`)
165 REFERENCES `mydb`.`Employees` (`employeeID`)
166 ON DELETE NO ACTION
167 ON UPDATE NO ACTION,
168 CONSTRAINT `FK_ProjectEmployee_JOIN`
169 FOREIGN KEY (`ProjectID`)
170 REFERENCES `mydb`.`Projects` (`projectID`)
171 ON DELETE NO ACTION
172 ON UPDATE NO ACTION)
173ENGINE = InnoDB;
174
175
176-- -----------------------------------------------------
177-- Table `mydb`.`EmployeePosition`
178-- -----------------------------------------------------
179DROP TABLE IF EXISTS `mydb`.`EmployeePosition` ;
180
181CREATE TABLE IF NOT EXISTS `mydb`.`EmployeePosition` (
182 `EmployeeID` INT NOT NULL,
183 `PositionID` INT NULL,
184 PRIMARY KEY (`EmployeeID`),
185 INDEX `FK_Positions_JOIN_idx` (`PositionID` ASC),
186 CONSTRAINT `FK_EmployeePosition_JOIN`
187 FOREIGN KEY (`EmployeeID`)
188 REFERENCES `mydb`.`Employees` (`employeeID`)
189 ON DELETE NO ACTION
190 ON UPDATE NO ACTION,
191 CONSTRAINT `FK_EmployeePositions_JOIN`
192 FOREIGN KEY (`PositionID`)
193 REFERENCES `mydb`.`Positions` (`positionID`)
194 ON DELETE NO ACTION
195 ON UPDATE NO ACTION)
196ENGINE = InnoDB;
197
198
199-- -----------------------------------------------------
200-- Table `mydb`.`HeadquartersAddress`
201-- -----------------------------------------------------
202DROP TABLE IF EXISTS `mydb`.`HeadquartersAddress` ;
203
204CREATE TABLE IF NOT EXISTS `mydb`.`HeadquartersAddress` (
205 `HQID` INT NOT NULL,
206 `AddressID` INT NULL,
207 PRIMARY KEY (`HQID`),
208 INDEX `FK_Address_JOIN_idx` (`AddressID` ASC),
209 CONSTRAINT `FK_HeadquarterAdress_JOIN`
210 FOREIGN KEY (`HQID`)
211 REFERENCES `mydb`.`Headquarters` (`HQID`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION,
214 CONSTRAINT `FK_AddressHeadquarter_JOIN`
215 FOREIGN KEY (`AddressID`)
216 REFERENCES `mydb`.`Addresses` (`addressID`)
217 ON DELETE NO ACTION
218 ON UPDATE NO ACTION)
219ENGINE = InnoDB;
220
221
222SET SQL_MODE=@OLD_SQL_MODE;
223SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
224SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;