· 8 years ago · Dec 02, 2017, 03:44 AM
1-- MySQL Script generated by MySQL Workbench
2-- 11/30/17 13:08:41
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema ns_Pepperoni
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema ns_Pepperoni
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `ns_Pepperoni` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
18USE `ns_Pepperoni` ;
19
20-- -----------------------------------------------------
21-- Table `ns_Pepperoni`.`Students`
22-- -----------------------------------------------------
23DROP TABLE IF EXISTS `ns_Pepperoni`.`Students` ;
24
25CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Students` (
26 `studentID` INT NOT NULL,
27 `email` VARCHAR(45) NULL,
28 `ugamyid` VARCHAR(8) NULL,
29 `yeartitle` VARCHAR(12) NULL,
30 PRIMARY KEY (`studentID`))
31ENGINE = InnoDB;
32
33INSERT INTO Students VALUES (8442486591, "ske56525@uga.edu", "ske56525", "junior");
34INSERT INTO Students VALUES (8124514598, "kke55225@uga.edu", "kke55225", "freshman");
35INSERT INTO Students VALUES (8445249595, "ske45225@uga.edu", "ske45225", "freshman");
36INSERT INTO Students VALUES (8125486598, "kuh35825@uga.edu", "kuh35825", "sophmore");
37INSERT INTO Students VALUES (8442458257, "jke98515@uga.edu", "jke98515", "senior");
38INSERT INTO Students VALUES (8142468598, "ukt25495@uga.edu", "ukt25495", "junior");
39INSERT INTO Students VALUES (8144587569, "gth56254@uga.edu", "gth56254", "senior");
40
41-- -----------------------------------------------------
42-- Table `ns_Pepperoni`.`ClassTimes`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `ns_Pepperoni`.`ClassTimes` ;
45
46CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`ClassTimes` (
47 `timeslotID` VARCHAR(45) NOT NULL,
48 `days` VARCHAR(35) NULL,
49 `starttime` TIMESTAMP(4) NULL,
50 `durationminutes` INT NULL,
51 PRIMARY KEY (`timeslotID`))
52ENGINE = InnoDB;
53
54INSERT INTO ClassTimes VALUES (1, MWF, "10:10", 50);
55INSERT INTO ClassTimes VALUES (6, TR, "11:00", 75);
56INSERT INTO ClassTimes VALUES (2, MWF, "9:05", 50);
57INSERT INTO ClassTimes VALUES (5, TR, "8:00", 75);
58INSERT INTO ClassTimes VALUES (4, TR, "3:30", 75);
59INSERT INTO ClassTimes VALUES (3, MWF, "12:05", 50);
60-- -----------------------------------------------------
61-- Table `ns_Pepperoni`.`Classes`
62-- -----------------------------------------------------
63DROP TABLE IF EXISTS `ns_Pepperoni`.`Classes` ;
64
65CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Classes` (
66 `CRN` INT NOT NULL,
67 `coursetag` VARCHAR(4) NULL,
68 `Class_times_days` VARCHAR(35) NOT NULL,
69 PRIMARY KEY (`CRN`),
70 INDEX `fk_Classes_Class_times_idx` (`Class_times_days` ASC),
71 CONSTRAINT `fk_Classes_Class_times`
72 FOREIGN KEY (`Class_times_days`)
73 REFERENCES `ns_Pepperoni`.`ClassTimes` (`days`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78INSERT INTO Classes VALUES (425474, MIST, "2090_11:00_TR");
79INSERT INTO Classes VALUES (585474, MIST, "4610_3:30_TR");
80INSERT INTO Classes VALUES (548674, MIST, "5710_12:05_MWF");
81INSERT INTO Classes VALUES (158474, MIST, "4620_8:00_TR");
82INSERT INTO Classes VALUES (786544, MIST, "5720_9:05_MWF");
83INSERT INTO Classes VALUES (445842, MIST, "5510_10:10_MWF");
84
85-- -----------------------------------------------------
86-- Table `ns_Pepperoni`.`Professors`
87-- -----------------------------------------------------
88DROP TABLE IF EXISTS `ns_Pepperoni`.`Professors` ;
89
90CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Professors` (
91 `profID` INT NOT NULL,
92 `prof_fname` VARCHAR(20) NULL,
93 `prof_lname` VARCHAR(20) NULL,
94 `dateofhire` DATE NULL,
95 `tenure` VARCHAR(5) NULL,
96 `ugaID` INT NULL,
97 PRIMARY KEY (`profID`))
98ENGINE = InnoDB;
99
100INSERT INTO Professors VALUES (0001,"Nikhil", "Shriny", 10/21/2002, "10", 5856244125);
101INSERT INTO Professors VALUES (0006,"Timothy", "Chesters", 9/11/2003, "10", 2545851325);
102INSERT INTO Professors VALUES (0085,"Margret", "Christ", 8/7/2005, "10", 8758934125);
103INSERT INTO Professors VALUES (0951,"Robert", "Roseteck", 3/17/2006, "10", 9658522125);
104
105-- -----------------------------------------------------
106-- Table `ns_Pepperoni`.`OverallGrade`
107-- -----------------------------------------------------
108DROP TABLE IF EXISTS `ns_Pepperoni`.`OverallGrade` ;
109
110CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`OverallGrade` (
111 `ClassCRN` INT NOT NULL,
112 `gradeavg` INT NULL,
113 `gpaWeight` INT NULL,
114 PRIMARY KEY (`ClassCRN`),
115 INDEX `fk_Grades_Classes1_idx` (`ClassCRN` ASC),
116 CONSTRAINT `fk_Grades_Classes1`
117 FOREIGN KEY (`ClassCRN`)
118 REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION)
121ENGINE = InnoDB;
122
123INSERT INTO OverallGrade VALUES (425474, 92, 3.7);
124INSERT INTO OverallGrade VALUES (585474, 89, 3.2);
125INSERT INTO OverallGrade VALUES (548674, 72, 2.3);
126INSERT INTO OverallGrade VALUES (158474, 60, 1.0);
127INSERT INTO OverallGrade VALUES (786544, 95, 3.7);
128INSERT INTO OverallGrade VALUES (445842, 100, 4.0);
129
130-- -----------------------------------------------------
131-- Table `ns_Pepperoni`.`Announcements`
132-- -----------------------------------------------------
133DROP TABLE IF EXISTS `ns_Pepperoni`.`Announcements` ;
134
135CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Announcements` (
136 `announcementID` INT NOT NULL,
137 `content` VARCHAR(512) NULL,
138 `date` DATE NULL,
139 `CRN` INT NOT NULL,
140 PRIMARY KEY (`announcementID`),
141 INDEX `fk_Announcements_Classes1_idx` (`CRN` ASC),
142 CONSTRAINT `fk_Announcements_Classes1`
143 FOREIGN KEY (`CRN`)
144 REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION)
147ENGINE = InnoDB;
148
149INSERT INTO Announcements VALUES (002,"Do Your HW", 10/20/2017, 425474);
150INSERT INTO Announcements VALUES (212,"Do Your HW Now", 6/17/2017, 585474);
151INSERT INTO Announcements VALUES (852,"Do Your HW Tomorow", 5/14/2017, 548674);
152INSERT INTO Announcements VALUES (692,"Do Your HW Next Week", 2/9/2017, 158474);
153INSERT INTO Announcements VALUES (032,"Do Your HW Next Month", 8/29/2017, 786544);
154INSERT INTO Announcements VALUES (842,"Do Your HW Next Year", 7/25/2017, 445842);
155
156-- -----------------------------------------------------
157-- Table `ns_Pepperoni`.`Chat`
158-- -----------------------------------------------------
159DROP TABLE IF EXISTS `ns_Pepperoni`.`Chat` ;
160
161CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Chat` (
162 `conversationID` INT NOT NULL,
163 `chatpartner` VARCHAR(50) NULL,
164 `chatpartnerUGAid` INT NULL,
165 `totalmessages` INT NULL,
166 `studentID` INT NOT NULL,
167 PRIMARY KEY (`conversationID`),
168 INDEX `fk_Chat_Students1_idx` (`studentID` ASC),
169 CONSTRAINT `fk_Chat_Students1`
170 FOREIGN KEY (`studentID`)
171 REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
172 ON DELETE NO ACTION
173 ON UPDATE NO ACTION)
174ENGINE = InnoDB;
175
176INSERT INTO Chat VALUES (1, "Sam", "sam12451", 5, 4512147854);
177INSERT INTO Chat VALUES (2, "James", "jam14521", 6, 6584327854);
178INSERT INTO Chat VALUES (4, "Cardinal", "car45221", 17, 8759654854);
179INSERT INTO Chat VALUES (6, "John", "jon78651", 52, 7895467854);
180INSERT INTO Chat VALUES (8, "Snow", "sno95651", 4, 3214587854);
181INSERT INTO Chat VALUES (10, "Bob", "bob64521", 9, 9587462154);
182
183-- -----------------------------------------------------
184-- Table `ns_Pepperoni`.`Assignments`
185-- -----------------------------------------------------
186DROP TABLE IF EXISTS `ns_Pepperoni`.`Assignments` ;
187
188CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Assignments` (
189 `assignmentID` INT NOT NULL,
190 `assigned` DATE NULL,
191 `due` DATE NULL,
192 `submissions` INT NULL,
193 `score` INT NULL,
194 `assignmentTitle` VARCHAR(45) NULL,
195 `ClassCRN` INT NOT NULL,
196 `studentID` INT NOT NULL,
197 PRIMARY KEY (`assignmentID`),
198 INDEX `fk_Assignments_Classes1_idx` (`ClassCRN` ASC),
199 INDEX `fk_Assignments_Students1_idx` (`studentID` ASC),
200 CONSTRAINT `fk_Assignments_Classes1`
201 FOREIGN KEY (`ClassCRN`)
202 REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
203 ON DELETE NO ACTION
204 ON UPDATE NO ACTION,
205 CONSTRAINT `fk_Assignments_Students1`
206 FOREIGN KEY (`studentID`)
207 REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
208 ON DELETE NO ACTION
209 ON UPDATE NO ACTION)
210ENGINE = InnoDB;
211
212INSERT INTO Assignments VALUES (046, 10/25/2017, 11/25/2017, 3, 92, "HW 1", 425474, 4512147854);
213INSERT INTO Assignments VALUES (982, 6/19/2017, 7/19/2017, 2, 85, "HW 5", 585474, 6584327854);
214INSERT INTO Assignments VALUES (983, 5/19/2017, 6/19/2017, 2, 68, "HW 6", 548674, 8759654854);
215INSERT INTO Assignments VALUES (942, 2/12/2017, 3/12/2017, 5, 98, "HW 9", 158474, 7895467854);
216INSERT INTO Assignments VALUES (325, 8/30/2017, 9/30/2017, 6, 75, "HW 7", 786544, 3214587854);
217INSERT INTO Assignments VALUES (865, 7/29/2017, 8/29/2017, 9, 87, "HW 4", 445842, 9587462154);
218
219 -- -----------------------------------------------------
220-- Table `ns_Pepperoni`.`Calendar`
221-- -----------------------------------------------------
222DROP TABLE IF EXISTS `ns_Pepperoni`.`Calendar` ;
223
224CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Calendar` (
225 `Date` DATE NOT NULL,
226 PRIMARY KEY (`Date`))
227ENGINE = InnoDB;
228
229INSERT INTO Calendar VALUES (10/01/2017);
230INSERT INTO Calendar VALUES (11/01/2017);
231INSERT INTO Calendar VALUES (12/01/2017);
232INSERT INTO Calendar VALUES (1/01/2017);
233INSERT INTO Calendar VALUES (2/01/2017);
234INSERT INTO Calendar VALUES (3/01/2017);
235INSERT INTO Calendar VALUES (4/01/2017);
236INSERT INTO Calendar VALUES (5/01/2017);
237
238-- -----------------------------------------------------
239-- Table `ns_Pepperoni`.`inbox`
240-- -----------------------------------------------------
241DROP TABLE IF EXISTS `ns_Pepperoni`.`inbox` ;
242
243CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`inbox` (
244 `InboxID` INT NOT NULL,
245 `studentID` INT NOT NULL,
246 `address` VARCHAR(45) NULL,
247 PRIMARY KEY (`InboxID`),
248 INDEX `fk_email_Students1_idx` (`studentID` ASC),
249 CONSTRAINT `fk_email_Students1`
250 FOREIGN KEY (`studentID`)
251 REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
252 ON DELETE NO ACTION
253 ON UPDATE NO ACTION)
254ENGINE = InnoDB;
255
256INSERT INTO inbox VALUES (046, 4512147854, "123 6th St. Melbourne, FL 32904");
257INSERT INTO inbox VALUES (982, 6584327854, "4 Goldfield Rd. Honolulu, HI 96815");
258INSERT INTO inbox VALUES (983, 8759654854, "71 Pilgrim Avenue Chevy Chase, MD 20815");
259INSERT INTO inbox VALUES (942, 7895467854, "44 Shirley Ave. West Chicago, IL 60185");
260INSERT INTO inbox VALUES (325, 3214587854, "70 Bowman St. South Windsor, CT 06074");
261INSERT INTO inbox VALUES (865, 9587462154, "514 S. Magnolia St. Orlando, FL 32806");
262
263
264-- -----------------------------------------------------
265-- Table `ns_Pepperoni`.`emails`
266-- -----------------------------------------------------
267DROP TABLE IF EXISTS `ns_Pepperoni`.`emails` ;
268
269CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`emails` (
270 `emailID` INT NOT NULL,
271 `senderaddress` VARCHAR(100) NULL,
272 `subject` VARCHAR(25) NULL,
273 `datereceived` DATE NULL,
274 `InboxID` INT NOT NULL,
275 PRIMARY KEY (`emailID`),
276 INDEX `fk_emails_email1_idx` (`InboxID` ASC),
277 CONSTRAINT `fk_emails_email1`
278 FOREIGN KEY (`InboxID`)
279 REFERENCES `ns_Pepperoni`.`inbox` (`InboxID`)
280 ON DELETE NO ACTION
281 ON UPDATE NO ACTION)
282ENGINE = InnoDB;
283
284INSERT INTO emails VALUES ("ske56525@uga.edu", "kds56525@uga.edu", "abs", 6/19/2017, 001);
285INSERT INTO emails VALUES ("kke55225@uga.edu", "mdj55225@uga.edu", "biceps", 6/19/2017, 032);
286INSERT INTO emails VALUES ("ske45225@uga.edu", "lks45225@uga.edu", "arms", 8/30/2017, 562);
287INSERT INTO emails VALUES ("kuh35825@uga.edu", "kdi35825@uga.edu", "legs", 3/12/2017, 458);
288INSERT INTO emails VALUES ("jke98515@uga.edu", "oss98515@uga.edu", "thighs", 11/25/2017, 284);
289INSERT INTO emails VALUES ("ukt25495@uga.edu", "ijs25495@uga.edu", "feet", 5/19/2017, 251);
290INSERT INTO emails VALUES ("gth56254@uga.edu", "los56254@uga.edu", "hands", 9/30/2017, 958);
291
292
293-- -----------------------------------------------------
294-- Table `ns_Pepperoni`.`ClassRoster`
295-- -----------------------------------------------------
296DROP TABLE IF EXISTS `ns_Pepperoni`.`ClassRoster` ;
297
298CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`ClassRoster` (
299 `studentID` INT NOT NULL,
300 `CRN` INT NOT NULL,
301 PRIMARY KEY (`studentID`, `CRN`),
302 INDEX `fk_Students_has_Classes_Classes1_idx` (`CRN` ASC),
303 INDEX `fk_Students_has_Classes_Students1_idx` (`studentID` ASC),
304 CONSTRAINT `fk_Students_has_Classes_Students1`
305 FOREIGN KEY (`studentID`)
306 REFERENCES `ns_Pepperoni`.`Students` (`studentID`)
307 ON DELETE NO ACTION
308 ON UPDATE NO ACTION,
309 CONSTRAINT `fk_Students_has_Classes_Classes1`
310 FOREIGN KEY (`CRN`)
311 REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
312 ON DELETE NO ACTION
313 ON UPDATE NO ACTION)
314ENGINE = InnoDB;
315
316INSERT INTO ClassRoster VALUES (4512147854, 425474);
317INSERT INTO ClassRoster VALUES (6584327854, 585474);
318INSERT INTO ClassRoster VALUES (8759654854, 548674);
319INSERT INTO ClassRoster VALUES (7895467854, 158474);
320INSERT INTO ClassRoster VALUES (3214587854, 786544);
321INSERT INTO ClassRoster VALUES (8958746215, 445842);
322
323-- -----------------------------------------------------
324-- Table `ns_Pepperoni`.`Classes_has_Professors`
325-- -----------------------------------------------------
326DROP TABLE IF EXISTS `ns_Pepperoni`.`Classes_has_Professors` ;
327
328CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Classes_has_Professors` (
329 `CRN` INT NOT NULL,
330 `profID` INT NOT NULL,
331 PRIMARY KEY (`CRN`, `profID`),
332 INDEX `fk_Classes_has_Professors_Professors1_idx` (`profID` ASC),
333 INDEX `fk_Classes_has_Professors_Classes1_idx` (`CRN` ASC),
334 CONSTRAINT `fk_Classes_has_Professors_Classes1`
335 FOREIGN KEY (`CRN`)
336 REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
337 ON DELETE NO ACTION
338 ON UPDATE NO ACTION,
339 CONSTRAINT `fk_Classes_has_Professors_Professors1`
340 FOREIGN KEY (`profID`)
341 REFERENCES `ns_Pepperoni`.`Professors` (`profID`)
342 ON DELETE NO ACTION
343 ON UPDATE NO ACTION)
344ENGINE = InnoDB;
345
346INSERT INTO Classes_has_Professors VALUES (425474, 0001);
347INSERT INTO Classes_has_Professors VALUES (585474, 0006);
348INSERT INTO Classes_has_Professors VALUES (548674, 0085);
349INSERT INTO Classes_has_Professors VALUES (158474, 0951);
350INSERT INTO Classes_has_Professors VALUES (786544, 0006);
351INSERT INTO Classes_has_Professors VALUES (445842, 0951);
352
353
354-- -----------------------------------------------------
355-- Table `ns_Pepperoni`.`Content`
356-- -----------------------------------------------------
357DROP TABLE IF EXISTS `ns_Pepperoni`.`Content` ;
358
359CREATE TABLE IF NOT EXISTS `ns_Pepperoni`.`Content` (
360 `contentID` INT NOT NULL,
361 `name` VARCHAR(45) NULL,
362 `filesize(MB)` INT NULL,
363 `CRN` INT NOT NULL,
364 PRIMARY KEY (`contentID`),
365 INDEX `fk_Content_Classes1_idx` (`CRN` ASC),
366 CONSTRAINT `fk_Content_Classes1`
367 FOREIGN KEY (`CRN`)
368 REFERENCES `ns_Pepperoni`.`Classes` (`CRN`)
369 ON DELETE NO ACTION
370 ON UPDATE NO ACTION)
371ENGINE = InnoDB;
372
373INSERT INTO Content VALUES (0021, "One thing", 20, 425474);
374INSERT INTO Content VALUES (0621, "Two thing", 65, 585474);
375INSERT INTO Content VALUES (0584, "Four thing", 52, 548674);
376INSERT INTO Content VALUES (0745, "Three thing", 85, 158474);
377INSERT INTO Content VALUES (4582, "Six thing", 45, 786544);
378INSERT INTO Content VALUES (9856, "Five thing", 36, 445842);
379
380SET SQL_MODE=@OLD_SQL_MODE;
381SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
382SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;