· 7 years ago · Mar 04, 2019, 12:46 PM
1-- -----------------------------------------------------
2-- Table mydb.Professor
3-- -----------------------------------------------------
4
5CREATE TABLE IF NOT EXISTS mydb.Professor (
6 name VARCHAR(50) NOT NULL ,
7 ID INT NOT NULL ,
8 PRIMARY KEY (ID) )
9
10
11
12-- -----------------------------------------------------
13-- Table mydb.Course
14-- -----------------------------------------------------
15
16CREATE TABLE IF NOT EXISTS mydb.Course (
17 semester VARCHAR(10) NOT NULL ,
18 ID INT NOT NULL ,
19 name VARCHAR(45) NOT NULL ,
20 coursenumber VARCHAR(45) NOT NULL ,
21 PRIMARY KEY (ID) )
22
23
24
25-- -----------------------------------------------------
26-- Table mydb.teaches
27-- -----------------------------------------------------
28DROP TABLE IF EXISTS mydb.teaches ;
29
30CREATE TABLE IF NOT EXISTS mydb.teaches (
31 profID INT NOT NULL ,
32 semID INT NOT NULL ,
33 ID INT NOT NULL ,
34 courseID INT NOT NULL ,
35 PRIMARY KEY (ID) ,
36 UNIQUE INDEX ID_UNIQUE (ID ASC) ,
37 INDEX profID (profID ASC) ,
38 INDEX courseID (ID ASC) ,
39 CONSTRAINT profID
40 FOREIGN KEY (profID )
41 REFERENCES mydb.Professor (ID )
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION,
44 CONSTRAINT courseID
45 FOREIGN KEY (ID )
46 REFERENCES mydb.Course (ID )
47 ON DELETE NO ACTION
48 ON UPDATE NO ACTION)
49
50
51
52-- -----------------------------------------------------
53-- Table mydb.User
54-- -----------------------------------------------------
55
56
57CREATE TABLE IF NOT EXISTS mydb.User (
58 ID INT NOT NULL AUTO_INCREMENT ,
59 salt VARCHAR(45) NOT NULL ,
60 pw VARCHAR(45) NOT NULL ,
61 username VARCHAR(50) NOT NULL ,
62 PRIMARY KEY (ID) )
63
64
65
66-- -----------------------------------------------------
67-- Table mydb.takes
68-- -----------------------------------------------------
69
70CREATE TABLE IF NOT EXISTS mydb.takes (
71 courseID INT NOT NULL ,
72 ID INT NOT NULL ,
73 userID INT NOT NULL ,
74 INDEX courseID (courseID ASC) ,
75 PRIMARY KEY (ID) ,
76 UNIQUE INDEX ID_UNIQUE (ID ASC) ,
77 INDEX userID (userID ASC) ,
78 CONSTRAINT courseID
79 FOREIGN KEY (courseID )
80 REFERENCES mydb.Course (ID )
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION,
83 CONSTRAINT userID
84 FOREIGN KEY (userID )
85 REFERENCES mydb.User (ID )
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION)
88
89
90
91-- -----------------------------------------------------
92-- Table mydb.Document
93-- -----------------------------------------------------
94
95
96CREATE TABLE IF NOT EXISTS mydb.Document (
97 ID INT NOT NULL AUTO_INCREMENT ,
98 datesubmitted TIME NOT NULL ,
99 type VARCHAR(45) NOT NULL COMMENT ' ' ,
100 ownerID INT NOT NULL ,
101 PRIMARY KEY (ID) ,
102 INDEX ownerID (ownerID ASC) ,
103 CONSTRAINT ownerID
104 FOREIGN KEY (ownerID )
105 REFERENCES mydb.User (ID )
106 ON DELETE NO ACTION
107 ON UPDATE NO ACTION)
108
109
110
111-- -----------------------------------------------------
112-- Table mydb.DocCourse
113-- -----------------------------------------------------
114
115CREATE TABLE IF NOT EXISTS mydb.DocCourse (
116 CourseID INT NOT NULL ,
117 DocID INT NOT NULL ,
118 INDEX CourseID (CourseID ASC) ,
119 INDEX DocID (DocID ASC) ,
120 CONSTRAINT CourseID
121 FOREIGN KEY (CourseID )
122 REFERENCES mydb.Course (ID )
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION,
125 CONSTRAINT DocID
126 FOREIGN KEY (DocID )
127 REFERENCES mydb.Document (ID )
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION)
130
131
132
133-- -----------------------------------------------------
134-- Table mydb.Role
135-- -----------------------------------------------------
136
137CREATE TABLE IF NOT EXISTS mydb.Role (
138 ID INT NOT NULL AUTO_INCREMENT ,
139 name VARCHAR(45) NOT NULL ,
140 userID INT NOT NULL ,
141 PRIMARY KEY (ID) ,
142 INDEX userID (userID ASC) ,
143 CONSTRAINT userID
144 FOREIGN KEY (userID )
145 REFERENCES mydb.User (ID )
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION)
148
149
150
151
152SET SQL_MODE=@OLD_SQL_MODE;
153SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
154SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;