9WAwQby0

1-- MySQL Script generated by MySQL Workbench
2-- Tue Mar 24 22:57:36 2020
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema pusp2002hbg
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema pusp2002hbg
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `pusp2002hbg` DEFAULT CHARACTER SET utf8 ;
18USE `pusp2002hbg` ;
19
20-- -----------------------------------------------------
21-- Table `pusp2002hbg`.`Users`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`Users` (
24  `userId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
25  `username` VARCHAR(10) NOT NULL,
26  `password` VARCHAR(10) NOT NULL,
27  `isAdmin` TINYINT NOT NULL DEFAULT 0,
28  PRIMARY KEY (`userId`),
29  UNIQUE INDEX `username_UNIQUE` (`username` ASC))
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `pusp2002hbg`.`Projects`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`Projects` (
37  `projectId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
38  `name` VARCHAR(20) NOT NULL,
39  PRIMARY KEY (`projectId`),
40  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `pusp2002hbg`.`Roles`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`Roles` (
48  `roleId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
49  `role` VARCHAR(45) NOT NULL,
50  PRIMARY KEY (`roleId`),
51  UNIQUE INDEX `role_UNIQUE` (`role` ASC))
52ENGINE = InnoDB;
53
54
55-- -----------------------------------------------------
56-- Table `pusp2002hbg`.`ProjectUsers`
57-- -----------------------------------------------------
58CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ProjectUsers` (
59  `projectUserId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
60  `userId` INT UNSIGNED NOT NULL,
61  `projectId` INT UNSIGNED NOT NULL,
62  `roleId` INT UNSIGNED NOT NULL,
63  PRIMARY KEY (`projectUserId`),
64  INDEX `fk_ProjectUsers_Users_idx` (`userId` ASC),
65  INDEX `fk_ProjectUsers_Projects1_idx` (`projectId` ASC),
66  INDEX `fk_ProjectUsers_Roles1_idx` (`roleId` ASC),
67  UNIQUE INDEX `index5` (`userId` ASC, `projectId` ASC),
68  CONSTRAINT `fk_ProjectUsers_Users`
69    FOREIGN KEY (`userId`)
70    REFERENCES `pusp2002hbg`.`Users` (`userId`)
71    ON DELETE CASCADE
72    ON UPDATE NO ACTION,
73  CONSTRAINT `fk_ProjectUsers_Projects1`
74    FOREIGN KEY (`projectId`)
75    REFERENCES `pusp2002hbg`.`Projects` (`projectId`)
76    ON DELETE CASCADE
77    ON UPDATE NO ACTION,
78  CONSTRAINT `fk_ProjectUsers_Roles1`
79    FOREIGN KEY (`roleId`)
80    REFERENCES `pusp2002hbg`.`Roles` (`roleId`)
81    ON DELETE CASCADE
82    ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85
86-- -----------------------------------------------------
87-- Table `pusp2002hbg`.`TimeReports`
88-- -----------------------------------------------------
89CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`TimeReports` (
90  `timeReportId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
91  `projectUserId` INT UNSIGNED NOT NULL,
92  `signedById` INT UNSIGNED NULL,
93  `signedAt` TIMESTAMP NULL,
94  `year` INT(4) UNSIGNED NOT NULL,
95  `week` INT(2) UNSIGNED NOT NULL,
96  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
97  `finished` TINYINT NULL DEFAULT 0,
98  PRIMARY KEY (`timeReportId`),
99  INDEX `fk_TimeReports_ProjectUsers1_idx` (`projectUserId` ASC),
100  INDEX `fk_TimeReports_ProjectUsers2_idx` (`signedById` ASC),
101  CONSTRAINT `fk_TimeReports_ProjectUsers1`
102    FOREIGN KEY (`projectUserId`)
103    REFERENCES `pusp2002hbg`.`ProjectUsers` (`projectUserId`)
104    ON DELETE CASCADE
105    ON UPDATE NO ACTION,
106  CONSTRAINT `fk_TimeReports_ProjectUsers2`
107    FOREIGN KEY (`signedById`)
108    REFERENCES `pusp2002hbg`.`ProjectUsers` (`projectUserId`)
109    ON DELETE CASCADE
110    ON UPDATE NO ACTION)
111ENGINE = InnoDB;
112
113
114-- -----------------------------------------------------
115-- Table `pusp2002hbg`.`ActivityTypes`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ActivityTypes` (
118  `activityTypeId` INT UNSIGNED NOT NULL,
119  `type` VARCHAR(17) NOT NULL,
120  PRIMARY KEY (`activityTypeId`),
121  UNIQUE INDEX `type_UNIQUE` (`type` ASC))
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `pusp2002hbg`.`ActivitySubTypes`
127-- -----------------------------------------------------
128CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ActivitySubTypes` (
129  `activitySubTypeId` INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
130  `activityTypeId` INT UNSIGNED NOT NULL,
131  `subType` VARCHAR(1) NOT NULL,
132  PRIMARY KEY (`activitySubTypeId`),
133  INDEX `fk_ActivitySubTypes_ActivityTypes1_idx` (`activityTypeId` ASC),
134  CONSTRAINT `fk_ActivitySubTypes_ActivityTypes1`
135    FOREIGN KEY (`activityTypeId`)
136    REFERENCES `pusp2002hbg`.`ActivityTypes` (`activityTypeId`)
137    ON DELETE CASCADE
138    ON UPDATE NO ACTION)
139ENGINE = InnoDB;
140
141
142-- -----------------------------------------------------
143-- Table `pusp2002hbg`.`ActivityReports`
144-- -----------------------------------------------------
145CREATE TABLE IF NOT EXISTS `pusp2002hbg`.`ActivityReports` (
146  `activityReportId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
147  `timeReportId` INT UNSIGNED NOT NULL,
148  `activityTypeId` INT UNSIGNED NOT NULL,
149  `activitySubTypeId` INT(3) UNSIGNED NULL,
150  `reportDate` DATE NOT NULL,
151  `minutes` INT(4) UNSIGNED NOT NULL DEFAULT 0,
152  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
153  PRIMARY KEY (`activityReportId`),
154  INDEX `fk_ActivityReports_TimeReports1_idx` (`timeReportId` ASC),
155  INDEX `fk_ActivityReports_ActivitySubTypes1_idx` (`activitySubTypeId` ASC),
156  INDEX `fk_ActivityReports_ActivityTypes1_idx` (`activityTypeId` ASC),
157  CONSTRAINT `fk_ActivityReports_TimeReports1`
158    FOREIGN KEY (`timeReportId`)
159    REFERENCES `pusp2002hbg`.`TimeReports` (`timeReportId`)
160    ON DELETE CASCADE
161    ON UPDATE NO ACTION,
162  CONSTRAINT `fk_ActivityReports_ActivitySubTypes1`
163    FOREIGN KEY (`activitySubTypeId`)
164    REFERENCES `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`)
165    ON DELETE CASCADE
166    ON UPDATE NO ACTION,
167  CONSTRAINT `fk_ActivityReports_ActivityTypes1`
168    FOREIGN KEY (`activityTypeId`)
169    REFERENCES `pusp2002hbg`.`ActivityTypes` (`activityTypeId`)
170    ON DELETE CASCADE
171    ON UPDATE NO ACTION)
172ENGINE = InnoDB;
173
174
175SET SQL_MODE=@OLD_SQL_MODE;
176SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
177SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
178
179-- -----------------------------------------------------
180-- Data for table `pusp2002hbg`.`Users`
181-- -----------------------------------------------------
182START TRANSACTION;
183USE `pusp2002hbg`;
184INSERT INTO `pusp2002hbg`.`Users` (`userId`, `username`, `password`, `isAdmin`) VALUES (1, 'Admin', 'Password', 1);
185
186COMMIT;
187
188
189-- -----------------------------------------------------
190-- Data for table `pusp2002hbg`.`Roles`
191-- -----------------------------------------------------
192START TRANSACTION;
193USE `pusp2002hbg`;
194INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (1, 'Project leader');
195INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (2, 'System manager');
196INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (3, 'Developer');
197INSERT INTO `pusp2002hbg`.`Roles` (`roleId`, `role`) VALUES (4, 'Tester');
198
199COMMIT;
200
201
202-- -----------------------------------------------------
203-- Data for table `pusp2002hbg`.`ActivityTypes`
204-- -----------------------------------------------------
205START TRANSACTION;
206USE `pusp2002hbg`;
207INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (11, 'SDP');
208INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (12, 'SRS');
209INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (13, 'SVVS');
210INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (14, 'STLDD');
211INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (15, 'SVVI');
212INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (16, 'SDDD');
213INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (17, 'SVVR');
214INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (18, 'SSD');
215INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (19, 'Final Report');
216INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (21, 'Functional test');
217INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (22, 'System test');
218INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (23, 'Regression test');
219INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (30, 'Meeting');
220INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (41, 'Lecture');
221INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (42, 'Exercise');
222INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (43, 'Computer exercise');
223INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (44, 'Home reading');
224INSERT INTO `pusp2002hbg`.`ActivityTypes` (`activityTypeId`, `type`) VALUES (100, 'Other');
225
226COMMIT;
227
228
229-- -----------------------------------------------------
230-- Data for table `pusp2002hbg`.`ActivitySubTypes`
231-- -----------------------------------------------------
232START TRANSACTION;
233USE `pusp2002hbg`;
234INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (1, 11, 'D');
235INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (2, 11, 'I');
236INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (3, 11, 'F');
237INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (4, 11, 'R');
238INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (5, 12, 'D');
239INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (6, 12, 'I');
240INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (7, 12, 'F');
241INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (8, 12, 'R');
242INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (9, 13, 'D');
243INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (10, 13, 'I');
244INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (11, 13, 'F');
245INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (12, 13, 'R');
246INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (13, 14, 'D');
247INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (14, 14, 'I');
248INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (15, 14, 'F');
249INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (16, 14, 'R');
250INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (17, 15, 'D');
251INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (18, 15, 'I');
252INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (19, 15, 'F');
253INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (20, 15, 'R');
254INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (21, 16, 'D');
255INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (22, 16, 'I');
256INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (23, 16, 'F');
257INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (24, 16, 'R');
258INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (25, 17, 'D');
259INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (26, 17, 'I');
260INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (27, 17, 'F');
261INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (28, 17, 'R');
262INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (29, 18, 'D');
263INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (30, 18, 'I');
264INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (31, 18, 'F');
265INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (32, 18, 'R');
266INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (33, 19, 'D');
267INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (34, 19, 'I');
268INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (35, 19, 'F');
269INSERT INTO `pusp2002hbg`.`ActivitySubTypes` (`activitySubTypeId`, `activityTypeId`, `subType`) VALUES (36, 19, 'R');
270
271COMMIT;