DB_Normalization/DB_Schema.sql at master · heeseonim/DB_Normalization · GitHub

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
CREATE TABLE `student` (
	`STUDENT_ID` INT(11) NOT NULL,
	`ACCOUNT` VARCHAR(20),
	`NAME` VARCHAR(100),
	`HP` VARCHAR(20),
	`AGE` INT(11),
	`GENDER` VARCHAR(20),
	`CURRICULUM` INT(11),
	`REGION` VARCHAR(20),
	`GROUP` INT(11),
	`TEAM_ID` INT(11) NOT NULL,
	`TEACHER_ID` INT(11) NOT NULL,
    PRIMARY KEY (`STUDENT_ID`),
    FOREIGN KEY (`TEAM_ID`) REFERENCES TEAM (`TEAM_ID`),
    FOREIGN KEY (`TEACHER_ID`) REFERENCES TEACHER (`TEACHER_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `teacher` (
	`TEACHER_ID` INT(11) NOT NULL,
	`ACCOUNT` VARCHAR(50),
	`NAME` VARCHAR(50),
	`HP` VARCHAR(50),
	PRIMARY KEY (`TEACHER_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `team` (
	`TEAM_ID` INT(11) NOT NULL,
	`TITLE` VARCHAR(50),
    PRIMARY KEY (`TEAM_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `attendance` (
	`STUDENT_ID` INT(11) NOT NULL,
	`DAY` VARCHAR(50) NOT NULL,
	`CHECK` INT(11) NULL,
	`SIGN_IN` DATE NULL,
	`SIGN_OUT` DATE NULL,
    PRIMARY KEY (`STUDENT_ID`),
    FOREIGN KEY (`STUDENT_ID`) REFERENCES STUDENT (`STUDENT_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `grade` (
	`STUDENT_ID` INT(11) NOT NULL,
	`FIRST_EXAM` INT(11) NULL,
	`SECOND_EXAM` INT(11) NULL,
	`THIRD_EXAM` INT(11) NULL,
	`FIRST_PROJECT` INT(11) NULL,
	PRIMARY KEY (`STUDENT_ID`),
    FOREIGN KEY (`STUDENT_ID`) REFERENCES STUDENT (`STUDENT_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `algorithm` (
	`STUDENT_ID` INT(11) NOT NULL,
	`GRADE` VARCHAR(50) NULL,
	`ACQUISITION_DATE` DATE NULL,
    PRIMARY KEY (`STUDENT_ID`),
    FOREIGN KEY (`STUDENT_ID`) REFERENCES STUDENT (`STUDENT_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `reg` (
	`STUDENT_ID` INT(11) NOT NULL,
	`REG_USER` VARCHAR(50) NULL,
	`REG_DT` DATE NULL,
	`LAST_USER` VARCHAR(50) NULL,
	`LAST_DT` DATE NULL,
	PRIMARY KEY (`STUDENT_ID`),
    FOREIGN KEY (`STUDENT_ID`) REFERENCES STUDENT (`STUDENT_ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;