-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathpdca.sql
126 lines (119 loc) · 4.96 KB
/
pdca.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
/*
Navicat MySQL Data Transfer
Source Server : 127.0.0.1
Source Server Type : MySQL
Source Server Version : 50721
Source Host : localhost:3306
Source Schema : pdca
Target Server Type : MySQL
Target Server Version : 50721
File Encoding : 65001
Date: 01/02/2018 09:41:59
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for keyword
-- ----------------------------
DROP TABLE
IF
EXISTS `keyword`;
CREATE TABLE `keyword` (
`KeywordID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`keywordValue` VARCHAR ( 25 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY ( `KeywordID` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Table structure for mid_keyword_file
-- ----------------------------
-- Table structure for project
-- ----------------------------
DROP TABLE
IF
EXISTS `project`;
CREATE TABLE `project` (
`ProjectID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`Name` VARCHAR ( 25 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`CreateDate` TIMESTAMP ( 1 ) NULL DEFAULT NULL,
PRIMARY KEY ( `ProjectID` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Table structure for task
-- ----------------------------
DROP TABLE
IF
EXISTS `task`;
CREATE TABLE `task` (
`TaskID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`TaskName` VARCHAR ( 25 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`CreateDate` TIMESTAMP ( 1 ) NULL DEFAULT NULL,
`UpdateDate` TIMESTAMP ( 1 ) NULL DEFAULT NULL,
`TaskType` VARCHAR ( 5 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`Description` VARCHAR ( 250 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`PTaskID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL ,
`ProjectID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL ,
PRIMARY KEY ( `TaskID` ) USING BTREE,
INDEX `FK_task_ptask` ( `PTaskID` ) USING BTREE,
INDEX `FK_project_task` ( `ProjectID` ) USING BTREE,
CONSTRAINT `FK_project_task` FOREIGN KEY ( `ProjectID` ) REFERENCES `project` ( `ProjectID` ) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_task_ptask` FOREIGN KEY ( `PTaskID` ) REFERENCES `task` ( `TaskID` ) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE
IF
EXISTS `user`;
CREATE TABLE `user` (
`UserID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`UserName` VARCHAR ( 25 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` VARCHAR ( 25 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY ( `UserID` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE
IF
EXISTS `mid_keyword_file`;
CREATE TABLE `mid_keyword_file` (
`FileID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL ,
`KeywordID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL ,
PRIMARY KEY ( `FileID`, `KeywordID` ) USING BTREE,
INDEX `FK_mid_keyword` ( `KeywordID` ) USING BTREE,
CONSTRAINT `FK_mid_file` FOREIGN KEY ( `FileID` ) REFERENCES `file` ( `FileID` ) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_mid_keyword` FOREIGN KEY ( `KeywordID` ) REFERENCES `keyword` ( `KeywordID` ) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Table structure for mid_task_keyword
-- ----------------------------
DROP TABLE
IF
EXISTS `mid_task_keyword`;
CREATE TABLE `mid_task_keyword` (
`TaskID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL,
`KeywordID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL,
PRIMARY KEY ( `TaskID`, `KeywordID` ) USING BTREE,
INDEX `FK_mid_keyword_task` ( `KeywordID` ) USING BTREE,
CONSTRAINT `FK_mid_keyword_task` FOREIGN KEY ( `KeywordID` ) REFERENCES `keyword` ( `KeywordID` ) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_mid_task` FOREIGN KEY ( `TaskID` ) REFERENCES `task` ( `TaskID` ) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Table structure for mid_user_project
-- ----------------------------
DROP TABLE
IF
EXISTS `mid_user_project`;
CREATE TABLE `mid_user_project` (
`UserID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL ,
`ProjectID` INT ( 12 ) UNSIGNED ZEROFILL NOT NULL,
PRIMARY KEY ( `UserID`, `ProjectID` ) USING BTREE,
INDEX `FK_mid_project` ( `ProjectID` ) USING BTREE,
CONSTRAINT `FK_mid_project` FOREIGN KEY ( `ProjectID` ) REFERENCES `project` ( `ProjectID` ) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;