· 6 years ago · Apr 07, 2019, 02:44 PM
1-- db name on https://ondras.zarovi.cz/sql/demo/ : rprtr258-hw
2
3CREATE DATABASE dashboard;
4use dashboard;
5-- ---
6-- Globals
7-- ---
8
9-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
10-- SET FOREIGN_KEY_CHECKS=0;
11
12-- ---
13-- Table 'employee'
14--
15-- ---
16
17DROP TABLE IF EXISTS `employee`;
18
19CREATE TABLE `employee` (
20 `id` INTEGER NOT NULL,
21 `full_name` VARCHAR(256) NOT NULL,
22 `email` VARCHAR(256) NOT NULL,
23 `job` VARCHAR(64) NOT NULL,
24 PRIMARY KEY (`id`)
25);
26
27-- ---
28-- Table 'projects'
29--
30-- ---
31
32DROP TABLE IF EXISTS `projects`;
33
34CREATE TABLE `projects` (
35 `name` VARCHAR(256) NOT NULL,
36 `start_time` TIMESTAMP NOT NULL,
37 `end_time` TIMESTAMP NOT NULL,
38 PRIMARY KEY (`name`)
39);
40
41-- ---
42-- Table 'assignments'
43--
44-- ---
45
46DROP TABLE IF EXISTS `assignments`;
47
48CREATE TABLE `assignments` (
49 `project_name` VARCHAR(256) NOT NULL,
50 `manager_id` INTEGER NOT NULL,
51 PRIMARY KEY (`project_name`)
52);
53
54-- ---
55-- Table 'projects_performers'
56--
57-- ---
58
59DROP TABLE IF EXISTS `projects_performers`;
60
61CREATE TABLE `projects_performers` (
62 `project_name` VARCHAR(256) NOT NULL,
63 `performer_id` INTEGER NOT NULL,
64 PRIMARY KEY (`project_name`)
65);
66
67-- ---
68-- Table 'builds'
69--
70-- ---
71
72DROP TABLE IF EXISTS `builds`;
73
74CREATE TABLE `builds` (
75 `id` INTEGER NOT NULL AUTO_INCREMENT,
76 `project_name` VARCHAR(256) NOT NULL,
77 PRIMARY KEY (`id`)
78);
79
80-- ---
81-- Table 'tasks'
82--
83-- ---
84
85DROP TABLE IF EXISTS `tasks`;
86
87CREATE TABLE `tasks` (
88 `id` INTEGER NOT NULL AUTO_INCREMENT,
89 `project_name` VARCHAR(256) NOT NULL,
90 `title` VARCHAR(256) NOT NULL,
91 `description` MEDIUMTEXT NOT NULL,
92 `priority` CHAR NOT NULL,
93 `developer_id` INTEGER NULL,
94 `tester_id` INTEGER NULL,
95 `status` VARCHAR(64) NOT NULL,
96 PRIMARY KEY (`id`)
97);
98
99-- ---
100-- Table 'bugs_history'
101--
102-- ---
103
104DROP TABLE IF EXISTS `bugs_history`;
105
106CREATE TABLE `bugs_history` (
107 `task_id` INTEGER NOT NULL,
108 `build_id` INTEGER NOT NULL,
109 `feature_id` INTEGER NOT NULL,
110 PRIMARY KEY (`task_id`)
111);
112
113-- ---
114-- Table 'features'
115--
116-- ---
117
118DROP TABLE IF EXISTS `features`;
119
120CREATE TABLE `features` (
121 `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
122 `task_id` INTEGER NOT NULL,
123 PRIMARY KEY (`id`)
124);
125
126-- ---
127-- Foreign Keys
128-- ---
129
130ALTER TABLE `projects` ADD FOREIGN KEY (name) REFERENCES `assignments` (`project_name`);
131ALTER TABLE `assignments` ADD FOREIGN KEY (project_name) REFERENCES `projects` (`name`);
132ALTER TABLE `assignments` ADD FOREIGN KEY (manager_id) REFERENCES `employee` (`id`);
133ALTER TABLE `projects_performers` ADD FOREIGN KEY (project_name) REFERENCES `projects` (`name`);
134ALTER TABLE `projects_performers` ADD FOREIGN KEY (performer_id) REFERENCES `employee` (`id`);
135ALTER TABLE `builds` ADD FOREIGN KEY (project_name) REFERENCES `projects` (`name`);
136ALTER TABLE `tasks` ADD FOREIGN KEY (developer_id) REFERENCES `employee` (`id`);
137ALTER TABLE `tasks` ADD FOREIGN KEY (tester_id) REFERENCES `employee` (`id`);
138ALTER TABLE `bugs_history` ADD FOREIGN KEY (task_id) REFERENCES `tasks` (`id`);
139ALTER TABLE `bugs_history` ADD FOREIGN KEY (build_id) REFERENCES `builds` (`id`);
140ALTER TABLE `bugs_history` ADD FOREIGN KEY (feature_id) REFERENCES `features` (`id`);
141ALTER TABLE `features` ADD FOREIGN KEY (task_id) REFERENCES `tasks` (`id`);
142
143-- ---
144-- Table Properties
145-- ---
146
147-- ALTER TABLE `employee` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
148-- ALTER TABLE `projects` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
149-- ALTER TABLE `assignments` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
150-- ALTER TABLE `projects_performers` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
151-- ALTER TABLE `builds` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
152-- ALTER TABLE `tasks` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
153-- ALTER TABLE `bugs_history` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
154-- ALTER TABLE `features` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
155
156-- ---
157-- Test Data
158-- ---
159
160-- INSERT INTO `employee` (`id`,`full_name`,`email`,`job`) VALUES
161-- ('','','','');
162-- INSERT INTO `projects` (`name`,`start_time`,`end_time`) VALUES
163-- ('','','');
164-- INSERT INTO `assignments` (`project_name`,`manager_id`) VALUES
165-- ('','');
166-- INSERT INTO `projects_performers` (`project_name`,`performer_id`) VALUES
167-- ('','');
168-- INSERT INTO `builds` (`id`,`project_name`) VALUES
169-- ('','');
170-- INSERT INTO `tasks` (`id`,`project_name`,`title`,`description`,`priority`,`developer_id`,`tester_id`,`status`) VALUES
171-- ('','','','','','','','');
172-- INSERT INTO `bugs_history` (`task_id`,`build_id`,`feature_id`) VALUES
173-- ('','','');
174-- INSERT INTO `features` (`id`,`task_id`) VALUES
175-- ('','');