-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_commands
171 lines (157 loc) · 6.7 KB
/
create_commands
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
CREATE TABLE `account` (
`USERNAME` varchar(20) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`ROLE` varchar(10) NOT NULL,
PRIMARY KEY (`USERNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `admin` (
`ID` int NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) NOT NULL,
`EMAIL` varchar(35) NOT NULL,
`PHONE NUMBER` varchar(10) NOT NULL,
`USERNAME` varchar(20) NOT NULL,
`TYPE` varchar(10) NOT NULL,
PRIMARY KEY (`ID`),
KEY `USERNAME` (`USERNAME`),
CONSTRAINT `admin_ibfk_1` FOREIGN KEY (`USERNAME`) REFERENCES `account` (`USERNAME`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `class` (
`ID` varchar(6) NOT NULL,
`NAME` varchar(20) NOT NULL,
`ROOM ID` int DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`),
KEY `ROOM ID` (`ROOM ID`),
CONSTRAINT `class_ibfk_1` FOREIGN KEY (`ROOM ID`) REFERENCES `room` (`ID`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `class_subject_faculty` (
`ID` int NOT NULL AUTO_INCREMENT,
`CLASS ID` varchar(6) NOT NULL,
`FACULTY ID` varchar(10) NOT NULL,
`SUBJECT ID` varchar(10) NOT NULL,
`ROOM ID` int DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `CLASS ID` (`CLASS ID`),
KEY `FACULTY ID` (`FACULTY ID`),
KEY `SUBJECT ID` (`SUBJECT ID`),
KEY `ROOM ID` (`ROOM ID`),
CONSTRAINT `class_subject_faculty_ibfk_1` FOREIGN KEY (`CLASS ID`) REFERENCES `class` (`ID`),
CONSTRAINT `class_subject_faculty_ibfk_2` FOREIGN KEY (`FACULTY ID`) REFERENCES `faculty` (`FACULTY ID`),
CONSTRAINT `class_subject_faculty_ibfk_3` FOREIGN KEY (`SUBJECT ID`) REFERENCES `subject` (`SUBJECT ID`),
CONSTRAINT `class_subject_faculty_ibfk_4` FOREIGN KEY (`ROOM ID`) REFERENCES `room` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `day` (
`DAY ID` char(2) NOT NULL,
`DAY NAME` varchar(10) NOT NULL,
`DAY NUMBER` int NOT NULL,
PRIMARY KEY (`DAY ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `faculty` (
`FACULTY ID` varchar(10) NOT NULL,
`NAME` varchar(20) NOT NULL,
`EMAIL` varchar(35) NOT NULL,
`USERNAME` varchar(20) NOT NULL,
`PERMANENT ADDRESS` varchar(60) NOT NULL,
`CORR. ADDRESS` varchar(60) NOT NULL,
`DATE OF BIRTH` date NOT NULL,
`date of registration` date NOT NULL DEFAULT (curdate()),
`MOBILE NUMBER` varchar(10) NOT NULL,
`GENDER` char(1) NOT NULL,
`blood group` varchar(3) DEFAULT NULL,
`experience` varchar(50) NOT NULL DEFAULT ' ',
`subject speciality` varchar(25) NOT NULL DEFAULT ' ',
PRIMARY KEY (`FACULTY ID`),
KEY `USERNAME` (`USERNAME`),
CONSTRAINT `faculty_ibfk_1` FOREIGN KEY (`USERNAME`) REFERENCES `account` (`USERNAME`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `parent details` (
`PARENT ID` varchar(10) NOT NULL,
`father name` varchar(20) DEFAULT NULL,
`father email id` varchar(35) DEFAULT NULL,
`father mobile number` varchar(10) DEFAULT NULL,
`FATHER DOB` date DEFAULT NULL,
`mother name` varchar(20) DEFAULT NULL,
`mother email` varchar(35) DEFAULT NULL,
`mother mobile number` varchar(10) DEFAULT NULL,
`MOTHER DOB` date DEFAULT NULL,
`family income` varchar(21) DEFAULT NULL,
PRIMARY KEY (`PARENT ID`),
CONSTRAINT `parent_emails` CHECK (((`father email id` is not null) or (`mother email` is not null))),
CONSTRAINT `parent_mobiles` CHECK (((`father mobile number` is not null) or (`mother mobile number` is not null)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `qualification` (
`FACULTY ID` varchar(10) NOT NULL,
`degree` varchar(20) DEFAULT NULL,
`YEAR` char(4) NOT NULL,
`institute` varchar(30) DEFAULT NULL,
`PERCENTAGE` decimal(5,2) DEFAULT NULL,
KEY `FACULTY ID` (`FACULTY ID`),
CONSTRAINT `qualification_ibfk_1` FOREIGN KEY (`FACULTY ID`) REFERENCES `faculty` (`FACULTY ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `room` (
`ID` int NOT NULL,
`SITTING CAPACITY` int NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `schedule` (
`SCHEDULE ID` int NOT NULL AUTO_INCREMENT,
`LAST UPDATED` date DEFAULT NULL,
`WEF` date NOT NULL,
`DAY ID` char(2) NOT NULL,
`TIMESLOT ID` varchar(10) NOT NULL,
`CLASS ID` varchar(6) NOT NULL,
`SUBJECT ID` varchar(10) NOT NULL,
`FACULTY ID` varchar(10) NOT NULL,
`ROOM ID` int DEFAULT NULL,
PRIMARY KEY (`SCHEDULE ID`)
) ENGINE=InnoDB AUTO_INCREMENT=629 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `schedule_records` (
`SCHEDULE ID` int NOT NULL AUTO_INCREMENT,
`TIME TABLE ID` varchar(10) NOT NULL,
`FROM` date DEFAULT NULL,
`TO` date DEFAULT NULL,
`DAY ID` char(2) NOT NULL,
`TIMESLOT ID` varchar(10) NOT NULL,
`CLASS ID` varchar(6) NOT NULL,
`SUBJECT ID` varchar(10) NOT NULL,
`FACULTY ID` varchar(10) NOT NULL,
`ROOM ID` int DEFAULT NULL,
PRIMARY KEY (`SCHEDULE ID`)
) ENGINE=InnoDB AUTO_INCREMENT=449 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `student` (
`REGISTRATION ID` varchar(6) NOT NULL,
`STUDENT NAME` varchar(20) NOT NULL,
`EMAIL` varchar(35) NOT NULL,
`USERNAME` varchar(20) NOT NULL,
`mobile number` varchar(10) DEFAULT NULL,
`CLASS` varchar(6) DEFAULT NULL,
`PERMANENT Address` varchar(60) NOT NULL,
`CORR. ADDRESS` varchar(60) NOT NULL,
`ROLL NUMBER` int NOT NULL,
`DATE OF BIRTH` date NOT NULL,
`date of registration` date DEFAULT (curdate()),
`GENDER` char(1) NOT NULL,
`BLOOD GROUP` varchar(3) DEFAULT NULL,
`PARENT ID` varchar(10) DEFAULT NULL,
PRIMARY KEY (`REGISTRATION ID`),
KEY `USERNAME` (`USERNAME`),
KEY `PARENT ID` (`PARENT ID`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`USERNAME`) REFERENCES `account` (`USERNAME`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `student_ibfk_2` FOREIGN KEY (`PARENT ID`) REFERENCES `parent details` (`PARENT ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `subject` (
`SUBJECT ID` varchar(10) NOT NULL,
`SUBJECT NAME` varchar(25) NOT NULL,
`LECTURES REQUIRED` int NOT NULL,
`TOTAL LECTURES REQUIRED` int DEFAULT NULL,
`CREDITS` int DEFAULT NULL,
`syllabus` varchar(2000) DEFAULT NULL,
`EVALUATION CRETERIA` varchar(200) DEFAULT NULL,
PRIMARY KEY (`SUBJECT ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `time slot` (
`ID` varchar(10) NOT NULL,
`FROM` varchar(4) NOT NULL,
`TO` varchar(4) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci