· 10 years ago · Sep 15, 2016, 07:00 PM
1DROP DATABASE IF EXISTS Courses1;
2CREATE DATABASE Courses1;
3USE Courses1;
4
5/* Schema */
6
7CREATE TABLE `Person` (
8 `id` INT(11) NOT NULL AUTO_INCREMENT,
9 `lastName` VARCHAR(15) DEFAULT NULL,
10 `firstName` VARCHAR(15) DEFAULT NULL,
11 `gender` CHAR(1) NOT NULL,
12 `major` VARCHAR(30) DEFAULT NULL,
13 `unitsCompleted` INT(11) DEFAULT NULL,
14 `dept` VARCHAR(30) DEFAULT NULL,
15 PRIMARY KEY (`id`)
16);
17
18CREATE TABLE `Term` (
19 `id` INT(11) NOT NULL,
20 `name` VARCHAR(10) NOT NULL,
21 `startDate` DATE NOT NULL,
22 `endDate` DATE NOT NULL,
23 PRIMARY KEY (`id`)
24);
25
26CREATE TABLE `Section` (
27 `id` INT(11) NOT NULL,
28 `title` VARCHAR(20) NOT NULL,
29 `number` VARCHAR(5) NOT NULL,
30 `numUnits` INT(11) DEFAULT NULL,
31 `dept` VARCHAR(30) DEFAULT NULL,
32 `termId` INT(11) NOT NULL,
33 PRIMARY KEY (`id`),
34 CONSTRAINT `FKSection_termId` FOREIGN KEY (`termId`)
35 REFERENCES `Term` (`id`)
36);
37
38CREATE TABLE `Teaches` (
39 `teacherId` INT(11) NOT NULL,
40 `sectionId` INT(11) NOT NULL,
41 PRIMARY KEY (`teacherId`,`sectionId`),
42 CONSTRAINT `FKTeaches_teacherId` FOREIGN KEY (`teacherId`)
43 REFERENCES `Person` (`id`),
44 CONSTRAINT `FKTeaches_sectionId` FOREIGN KEY (`sectionId`)
45 REFERENCES `Section` (`id`)
46);
47
48CREATE TABLE `Takes` (
49 `studentId` INT(11) NOT NULL,
50 `sectionId` INT(11) NOT NULL,
51 PRIMARY KEY (`studentId`,`sectionId`),
52 CONSTRAINT `FKTakes_studentId` FOREIGN KEY (`studentId`)
53 REFERENCES `Person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
54 CONSTRAINT `FKTakes_sectionId` FOREIGN KEY (`sectionId`)
55 REFERENCES `Section` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
56);
57
58/* Dataset */
59
60INSERT INTO `Person` VALUES
61 (1, 'Logan', 'Juliet', 'F', 'Math', 60, NULL),
62 (2, 'Arzt', 'Terrell', 'M', NULL, NULL, 'English'),
63 (3, 'Esposita', 'Travis', 'M', 'Math', 15, NULL),
64 (4, 'Engley', 'Sixta', 'F', NULL, NULL, 'Computer Science'),
65 (5, 'Dunlow', 'Travis', 'M', 'English', 40, NULL),
66 (6, 'Slingland', 'Josette', 'F', 'Computer Science', 10, 'Math'),
67 (7, 'Toussand', 'Sharron', 'F', 'English', 20, NULL),
68 (8, 'Esposita', 'Rupert', 'M', 'English', 125, NULL),
69 (9, 'Hafferkamp', 'Cuc', 'F', 'Math', 0, NULL),
70 (10, 'Dukelow', 'Coretta', 'F', 'Computer Science', 120, NULL);
71
72INSERT INTO `Term` VALUES
73 (1, 'S13', '2013-4-5', '2013-6-15'),
74 (2, 'F13', '2013-9-20', '2013-12-11'),
75 (3, 'W14', '2014-1-4', '2014-3-20');
76
77INSERT INTO `Section` VALUES
78 (1, 'Calculus 1', '101', 4, 'Math', 2),
79 (2, 'Calculus 1', '101', 4, 'Math', 1),
80 (3, 'Calculus 2', '102', 4, 'Math', 3),
81 (4, 'Shakespeare', '222', 5, 'English', 1),
82 (5, 'Shakespeare', '222', 5, 'English', 3),
83 (6, 'Databases', '465', 4, 'Computer Science', 2),
84 (7, 'Databases', '465', 4, 'Computer Science', 3),
85 (8, 'Databases', '465', 4, 'Computer Science', 3);
86
87INSERT INTO `Teaches` VALUES
88 (4, 6), (6, 7), (6, 1), (4, 2), (4, 3), (2, 4), (2, 5);
89
90INSERT INTO `Takes` VALUES
91 (1, 1), (1, 4), (1, 6), (1, 3),
92 (10, 1), (10, 4), (10, 6), (10, 3),
93 (6, 4), (6, 6), (6, 3),
94 (8, 1), (8, 2), (8, 6), (8, 7),
95 (9, 4), (9, 5), (9, 2), (9, 3),
96 (3, 3), (3, 5), (3, 7),
97 (5, 1), (5, 2), (5, 3), (5, 4), (5, 5), (5, 6), (5, 7),
98 (7, 8);