· 8 years ago · Mar 04, 2018, 11:46 AM
1SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
2
3--
4-- Database: `test`
5--
6
7-- --------------------------------------------------------
8
9--
10-- Table structure for table `class__student_section`
11--
12
13CREATE TABLE `class__student_section` (
14 `id` int(11) NOT NULL AUTO_INCREMENT,
15 `student_id` int(11) NOT NULL,
16 `section_id` int(11) NOT NULL,
17 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
18 PRIMARY KEY (`id`),
19 KEY `student_id` (`student_id`)
20) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;
21
22--
23-- Dumping data for table `class__student_section`
24--
25
26INSERT INTO `class__student_section` VALUES(1, 1, 1, '2011-09-19 19:26:21');
27INSERT INTO `class__student_section` VALUES(2, 1, 2, '2011-09-19 19:26:21');
28INSERT INTO `class__student_section` VALUES(3, 1, 3, '2011-09-26 17:23:32');
29INSERT INTO `class__student_section` VALUES(4, 1, 4, '2011-09-26 17:23:32');
30INSERT INTO `class__student_section` VALUES(5, 1, 5, '2011-09-26 17:23:32');
31INSERT INTO `class__student_section` VALUES(6, 2, 5, '2011-09-26 17:25:12');
32INSERT INTO `class__student_section` VALUES(7, 2, 7, '2011-09-26 17:25:12');
33INSERT INTO `class__student_section` VALUES(8, 2, 8, '2011-09-26 17:25:12');
34INSERT INTO `class__student_section` VALUES(9, 2, 9, '2011-09-26 17:25:12');
35INSERT INTO `class__student_section` VALUES(10, 2, 10, '2011-09-26 17:25:12');
36INSERT INTO `class__student_section` VALUES(11, 3, 11, '2011-09-26 17:25:12');
37INSERT INTO `class__student_section` VALUES(12, 3, 12, '2011-09-26 17:25:12');
38INSERT INTO `class__student_section` VALUES(13, 3, 13, '2011-09-26 17:25:12');
39INSERT INTO `class__student_section` VALUES(14, 3, 14, '2011-09-26 17:25:12');
40INSERT INTO `class__student_section` VALUES(15, 3, 15, '2011-09-26 17:25:12');
41INSERT INTO `class__student_section` VALUES(16, 4, 16, '2011-09-26 17:25:12');
42INSERT INTO `class__student_section` VALUES(17, 4, 17, '2011-09-26 17:25:12');
43INSERT INTO `class__student_section` VALUES(18, 4, 18, '2011-09-26 17:25:12');
44INSERT INTO `class__student_section` VALUES(19, 4, 19, '2011-09-26 17:25:12');
45INSERT INTO `class__student_section` VALUES(20, 4, 20, '2011-09-26 17:25:12');
46INSERT INTO `class__student_section` VALUES(21, 5, 21, '2011-09-26 17:25:12');
47INSERT INTO `class__student_section` VALUES(22, 5, 22, '2011-09-26 17:25:12');
48INSERT INTO `class__student_section` VALUES(23, 5, 23, '2011-09-26 17:25:12');
49INSERT INTO `class__student_section` VALUES(24, 5, 24, '2011-09-26 17:25:12');
50INSERT INTO `class__student_section` VALUES(25, 5, 25, '2011-09-26 17:25:12');
51INSERT INTO `class__student_section` VALUES(26, 6, 26, '2011-09-26 17:25:12');
52INSERT INTO `class__student_section` VALUES(27, 6, 1, '2011-09-26 17:25:12');
53INSERT INTO `class__student_section` VALUES(28, 6, 4, '2011-09-26 17:26:49');
54INSERT INTO `class__student_section` VALUES(29, 6, 5, '2011-09-26 17:26:49');
55INSERT INTO `class__student_section` VALUES(30, 6, 7, '2011-09-26 17:26:49');
56INSERT INTO `class__student_section` VALUES(31, 7, 9, '2011-09-26 17:26:49');
57INSERT INTO `class__student_section` VALUES(32, 7, 10, '2011-09-26 17:26:49');
58INSERT INTO `class__student_section` VALUES(33, 7, 11, '2011-09-26 17:26:49');
59INSERT INTO `class__student_section` VALUES(34, 7, 12, '2011-09-26 17:26:49');
60INSERT INTO `class__student_section` VALUES(35, 7, 10, '2011-09-26 17:26:49');
61INSERT INTO `class__student_section` VALUES(36, 8, 9, '2011-09-26 17:26:49');
62INSERT INTO `class__student_section` VALUES(37, 8, 8, '2011-09-26 17:26:49');
63INSERT INTO `class__student_section` VALUES(38, 8, 15, '2011-09-26 17:26:49');
64INSERT INTO `class__student_section` VALUES(39, 8, 17, '2011-09-26 17:26:49');
65INSERT INTO `class__student_section` VALUES(40, 8, 27, '2011-11-02 17:31:21');
66INSERT INTO `class__student_section` VALUES(41, 9, 20, '2011-09-26 17:26:49');
67INSERT INTO `class__student_section` VALUES(42, 9, 23, '2011-09-26 17:26:49');
68INSERT INTO `class__student_section` VALUES(43, 9, 22, '2011-09-26 17:26:49');
69INSERT INTO `class__student_section` VALUES(44, 9, 15, '2011-09-26 17:26:49');
70INSERT INTO `class__student_section` VALUES(45, 9, 18, '2011-09-26 17:26:49');
71INSERT INTO `class__student_section` VALUES(46, 10, 22, '2011-09-26 17:26:49');
72INSERT INTO `class__student_section` VALUES(47, 10, 11, '2011-09-26 17:26:49');
73INSERT INTO `class__student_section` VALUES(48, 10, 5, '2011-09-26 17:26:49');
74INSERT INTO `class__student_section` VALUES(49, 10, 3, '2011-09-26 17:26:49');
75INSERT INTO `class__student_section` VALUES(50, 10, 1, '2011-09-26 17:26:49');
76
77-- --------------------------------------------------------
78
79--
80-- Table structure for table `college`
81--
82
83CREATE TABLE `college` (
84 `id` int(11) NOT NULL AUTO_INCREMENT,
85 `descr` varchar(50) NOT NULL,
86 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
87 PRIMARY KEY (`id`),
88 KEY `descr` (`descr`)
89) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
90
91--
92-- Dumping data for table `college`
93--
94
95INSERT INTO `college` VALUES(6, 'College of the Information Sciences and Technology', '2011-09-12 18:35:32');
96INSERT INTO `college` VALUES(7, 'College of the Conservative Arts', '2011-09-12 18:35:32');
97INSERT INTO `college` VALUES(8, 'College of the Family Values', '2011-09-12 18:35:32');
98INSERT INTO `college` VALUES(10, 'College of the Women Studies', '2011-09-12 18:35:32');
99INSERT INTO `college` VALUES(11, 'College of Rock', '2011-09-12 18:42:17');
100
101-- --------------------------------------------------------
102
103--
104-- Table structure for table `course`
105--
106
107CREATE TABLE `course` (
108 `id` int(11) NOT NULL AUTO_INCREMENT,
109 `descr` varchar(50) NOT NULL,
110 `department_id` int(11) NOT NULL,
111 `number` varchar(4) NOT NULL,
112 `credits` int(11) NOT NULL,
113 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
114 PRIMARY KEY (`id`),
115 KEY `descr` (`descr`),
116 KEY `major_id` (`department_id`),
117 KEY `number` (`number`),
118 KEY `credits` (`credits`)
119) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
120
121--
122-- Dumping data for table `course`
123--
124
125INSERT INTO `course` VALUES(1, 'Fun times with Dr. G', 6, '111', 3, '2011-09-14 19:20:28');
126INSERT INTO `course` VALUES(2, 'Risk Analysis of Cooking', 6, '568', 4, '2011-09-14 19:20:28');
127INSERT INTO `course` VALUES(3, 'Cyber-Terrorism', 6, '311', 3, '2011-09-14 19:20:28');
128INSERT INTO `course` VALUES(4, 'Hacking UFOs with Macbooks', 6, '403', 4, '2011-09-14 19:20:28');
129INSERT INTO `course` VALUES(5, 'Malware and other Malware', 6, '202', 3, '2011-09-14 19:20:28');
130INSERT INTO `course` VALUES(7, 'Armageddon and Bruce Willis', 9, '111', 3, '2011-09-14 19:37:24');
131INSERT INTO `course` VALUES(8, 'Steve Tyler and Friends', 9, '211', 4, '2011-09-14 19:37:24');
132INSERT INTO `course` VALUES(9, 'How to entertain a crowd', 9, '311', 3, '2011-09-14 19:37:24');
133INSERT INTO `course` VALUES(10, 'The study of Screaming', 9, '350', 4, '2011-09-14 19:37:24');
134INSERT INTO `course` VALUES(11, 'How to Annoy People with Singing', 9, '411', 4, '2011-09-14 19:37:24');
135INSERT INTO `course` VALUES(12, 'Beer Runs at the Grocery', 7, '103', 3, '2011-09-14 19:37:24');
136INSERT INTO `course` VALUES(13, 'The Bros and Cons of Muscles', 7, '244H', 4, '2011-09-14 19:37:24');
137INSERT INTO `course` VALUES(14, 'Seduction of the Ladyfolks', 7, '303', 3, '2011-09-14 19:37:24');
138INSERT INTO `course` VALUES(15, 'The Search for Justice Outside Your Home', 7, '404', 4, '2011-09-14 19:37:24');
139INSERT INTO `course` VALUES(16, 'What is Love', 7, '458D', 4, '2011-09-14 19:37:24');
140INSERT INTO `course` VALUES(17, 'Finding Free Condoms', 8, '004', 3, '2011-09-14 19:37:24');
141INSERT INTO `course` VALUES(18, 'Phil Collins or Marvin Gaye: The music of Love', 8, '140', 4, '2011-09-14 19:37:24');
142INSERT INTO `course` VALUES(19, 'The Ethics of Being a Casanova', 8, '239', 3, '2011-09-14 19:37:24');
143INSERT INTO `course` VALUES(20, 'Dealing with Stage 5 Clingers', 8, '360', 4, '2011-09-14 19:37:24');
144INSERT INTO `course` VALUES(21, 'How to Start a Relationship', 8, '450', 4, '2011-09-14 19:37:24');
145INSERT INTO `course` VALUES(22, 'The Perfect Sandwich', 10, '003', 4, '2011-09-14 19:42:24');
146INSERT INTO `course` VALUES(23, 'How to Spot a Mysogynist', 10, '131', 3, '2011-09-14 19:42:24');
147INSERT INTO `course` VALUES(24, 'Deathcakes and Other Wonderful Poisons', 10, '330', 3, '2011-09-14 19:42:24');
148INSERT INTO `course` VALUES(25, 'Culture of Edible Goodness', 10, '399', 3, '2011-09-14 19:42:24');
149INSERT INTO `course` VALUES(26, 'Breakfast: How to make it Fit for Lil'' Wayne', 10, '480', 4, '2011-09-14 19:42:24');
150
151-- --------------------------------------------------------
152
153--
154-- Table structure for table `department`
155--
156
157CREATE TABLE `department` (
158 `id` int(11) NOT NULL AUTO_INCREMENT,
159 `descr` varchar(5) NOT NULL,
160 `description` varchar(75) NOT NULL,
161 `college_id` int(11) NOT NULL,
162 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
163 PRIMARY KEY (`id`),
164 KEY `descr` (`descr`),
165 KEY `description` (`description`),
166 KEY `college_id` (`college_id`)
167) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
168
169--
170-- Dumping data for table `department`
171--
172
173INSERT INTO `department` VALUES(6, 'SRA', 'Security Risk and Analysis', 6, '2011-09-12 18:48:05');
174INSERT INTO `department` VALUES(7, 'MS', 'Men Studies', 7, '2011-09-12 18:43:23');
175INSERT INTO `department` VALUES(8, 'PP', 'Planned Parenthood', 8, '2011-09-12 18:43:23');
176INSERT INTO `department` VALUES(9, 'AESM', 'Aerosmith Engineering', 11, '2011-09-12 18:43:23');
177INSERT INTO `department` VALUES(10, 'BAKE', 'Baking', 10, '2011-09-12 18:43:25');
178
179-- --------------------------------------------------------
180
181--
182-- Table structure for table `instructor`
183--
184
185CREATE TABLE `instructor` (
186 `id` int(11) NOT NULL AUTO_INCREMENT,
187 `first_name` varchar(15) NOT NULL,
188 `last_name` varchar(25) NOT NULL,
189 PRIMARY KEY (`id`)
190) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
191
192--
193-- Dumping data for table `instructor`
194--
195
196INSERT INTO `instructor` VALUES(1, 'James', 'Bondage');
197INSERT INTO `instructor` VALUES(2, 'Walter', 'White');
198INSERT INTO `instructor` VALUES(3, 'Walter', 'Junior');
199INSERT INTO `instructor` VALUES(4, 'Tuco', 'Padre');
200INSERT INTO `instructor` VALUES(5, 'Amilio', 'Ganzas');
201INSERT INTO `instructor` VALUES(6, 'Crazy8', 'Ganzas');
202
203-- --------------------------------------------------------
204
205--
206-- Table structure for table `location`
207--
208
209CREATE TABLE `location` (
210 `id` int(11) NOT NULL AUTO_INCREMENT,
211 `descr` varchar(45) NOT NULL,
212 PRIMARY KEY (`id`)
213) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
214
215--
216-- Dumping data for table `location`
217--
218
219INSERT INTO `location` VALUES(1, 'Kern');
220INSERT INTO `location` VALUES(2, 'Life Sciences');
221INSERT INTO `location` VALUES(3, 'IST Building');
222INSERT INTO `location` VALUES(4, 'Forum');
223INSERT INTO `location` VALUES(5, 'Sparks');
224
225-- --------------------------------------------------------
226
227--
228-- Stand-in structure for view `schedule`
229--
230CREATE TABLE `schedule` (
231`student_descr` varchar(45)
232,`department_descr` varchar(5)
233,`course_descr` varchar(50)
234,`section_descr` int(11)
235);
236-- --------------------------------------------------------
237
238--
239-- Table structure for table `section`
240--
241
242CREATE TABLE `section` (
243 `id` int(11) NOT NULL AUTO_INCREMENT,
244 `course_id` int(11) NOT NULL,
245 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
246 `semester_id` int(11) NOT NULL,
247 `min_students` int(11) NOT NULL,
248 `max_students` int(11) NOT NULL,
249 `location_id` int(11) NOT NULL,
250 `instructor_id` int(11) NOT NULL,
251 PRIMARY KEY (`id`)
252) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;
253
254--
255-- Dumping data for table `section`
256--
257
258INSERT INTO `section` VALUES(1, 1, '2011-10-13 00:45:12', 1, 1, 15, 1, 1);
259INSERT INTO `section` VALUES(2, 2, '2011-10-13 00:45:12', 2, 1, 10, 2, 2);
260INSERT INTO `section` VALUES(3, 3, '2011-10-13 00:45:12', 1, 0, 10, 2, 2);
261INSERT INTO `section` VALUES(4, 4, '2011-10-13 00:45:12', 1, 5, 10, 3, 3);
262INSERT INTO `section` VALUES(5, 5, '2011-10-13 00:45:12', 1, 0, 2, 1, 3);
263INSERT INTO `section` VALUES(7, 7, '2011-10-13 00:45:12', 1, 2, 10, 1, 3);
264INSERT INTO `section` VALUES(8, 8, '2011-10-13 00:45:12', 1, 1, 10, 3, 1);
265INSERT INTO `section` VALUES(9, 9, '2011-10-13 00:45:12', 1, 5, 10, 2, 2);
266INSERT INTO `section` VALUES(10, 10, '2011-10-13 00:45:12', 1, 0, 2, 1, 2);
267INSERT INTO `section` VALUES(11, 11, '2011-10-13 00:45:12', 1, 0, 2, 2, 3);
268INSERT INTO `section` VALUES(12, 12, '2011-10-13 00:45:12', 1, 2, 10, 2, 3);
269INSERT INTO `section` VALUES(13, 13, '2011-10-13 00:45:12', 1, 0, 10, 3, 5);
270INSERT INTO `section` VALUES(14, 14, '2011-10-13 00:45:12', 1, 2, 10, 1, 1);
271INSERT INTO `section` VALUES(15, 15, '2011-10-13 00:45:12', 2, 5, 10, 1, 2);
272INSERT INTO `section` VALUES(16, 16, '2011-10-13 00:45:12', 2, 0, 2, 1, 3);
273INSERT INTO `section` VALUES(17, 17, '2011-10-13 00:45:12', 2, 5, 10, 3, 1);
274INSERT INTO `section` VALUES(18, 18, '2011-10-13 00:45:12', 2, 10, 20, 2, 3);
275INSERT INTO `section` VALUES(19, 19, '2011-10-13 00:45:12', 2, 0, 10, 2, 4);
276INSERT INTO `section` VALUES(20, 20, '2011-10-13 00:45:12', 3, 0, 2, 3, 1);
277INSERT INTO `section` VALUES(21, 21, '2011-10-13 00:45:12', 3, 5, 10, 1, 2);
278INSERT INTO `section` VALUES(22, 22, '2011-10-13 00:45:12', 3, 0, 2, 3, 2);
279INSERT INTO `section` VALUES(23, 23, '2011-10-13 00:45:12', 3, 0, 3, 4, 2);
280INSERT INTO `section` VALUES(24, 24, '2011-10-13 00:45:12', 3, 1, 4, 2, 3);
281INSERT INTO `section` VALUES(25, 25, '2011-10-13 00:45:12', 3, 3, 4, 2, 4);
282INSERT INTO `section` VALUES(26, 26, '2011-10-13 00:45:12', 3, 5, 10, 4, 4);
283INSERT INTO `section` VALUES(27, 17, '2011-10-13 00:45:12', 3, 2, 10, 4, 1);
284
285-- --------------------------------------------------------
286
287--
288-- Table structure for table `semester`
289--
290
291CREATE TABLE `semester` (
292 `id` int(11) NOT NULL AUTO_INCREMENT,
293 `descr` varchar(11) NOT NULL,
294 `year` year(4) NOT NULL,
295 PRIMARY KEY (`id`)
296) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
297
298--
299-- Dumping data for table `semester`
300--
301
302INSERT INTO `semester` VALUES(1, 'Fall', 2011);
303INSERT INTO `semester` VALUES(2, 'Spring', 2012);
304INSERT INTO `semester` VALUES(3, 'Summer', 2012);
305
306-- --------------------------------------------------------
307
308--
309-- Table structure for table `student`
310--
311
312CREATE TABLE `student` (
313 `id` int(11) NOT NULL AUTO_INCREMENT,
314 `descr` varchar(45) NOT NULL,
315 `name_prefix` varchar(3) NOT NULL,
316 `first_name` varchar(15) NOT NULL,
317 `middle_initial` varchar(1) NOT NULL,
318 `last_name` varchar(25) NOT NULL,
319 `name_suffix` varchar(3) NOT NULL,
320 `department_id` int(11) NOT NULL,
321 `grad_student` bit(1) NOT NULL,
322 `email` varchar(50) NOT NULL,
323 `birthday` date NOT NULL,
324 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
325 PRIMARY KEY (`id`),
326 KEY `first_name` (`first_name`),
327 KEY `last_name` (`last_name`),
328 KEY `major_id` (`department_id`),
329 KEY `email` (`email`),
330 KEY `birthday` (`birthday`)
331) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
332
333--
334-- Dumping data for table `student`
335--
336
337INSERT INTO `student` VALUES(1, 'Sarah Smith', '', 'Sarah', '', 'Smith', '', 6, '\0', 'sarah.smith@example.com', '1970-01-01', '2011-09-12 18:51:32');
338INSERT INTO `student` VALUES(2, 'Joseph Novotny', '', 'Joseph', '', 'Novotny', '', 6, '\0', 'joseph.novotny@example.com', '1986-07-06', '2011-09-12 18:51:44');
339INSERT INTO `student` VALUES(3, 'Samuel Harper', '', 'Samuel', '', 'Harper', '', 9, '\0', 'samuel.harper@example.com', '1985-04-03', '2011-09-12 18:52:06');
340INSERT INTO `student` VALUES(4, 'James Thompson', '', 'James', '', 'Thompson', '', 9, '\0', 'james.thompson@example.com', '1987-12-17', '2011-09-12 18:52:25');
341INSERT INTO `student` VALUES(5, 'Tracy Murray', '', 'Tracy', '', 'Murray', '', 8, '\0', 'tracy.murray@example.com', '1987-09-14', '2011-09-14 17:48:50');
342INSERT INTO `student` VALUES(6, 'Kyle Dotson', '', 'Kyle', '', 'Dotson', '', 6, '\0', 'kyle.dotson@example.com', '1992-11-11', '2011-09-12 18:52:45');
343INSERT INTO `student` VALUES(7, 'Jeffrey Nance', '', 'Jeffrey', '', 'Nance', '', 7, '\0', 'jeffrey.nance@example.com', '1982-11-05', '2011-09-12 18:53:54');
344INSERT INTO `student` VALUES(8, 'Dazzledance Silvergaze', '', 'Dazzledance', '', 'Silvergaze', '', 10, '\0', 'dazzledance.silvergaze@example.com', '1989-08-12', '2011-09-12 18:53:15');
345INSERT INTO `student` VALUES(9, 'Michael Cutshaw', '', 'Michael', '', 'Cutshaw', '', 9, '\0', 'michael.cutshaw@example.com', '1989-05-11', '2011-09-12 18:53:28');
346INSERT INTO `student` VALUES(10, 'Shirley Wicker', '', 'Shirley', '', 'Wicker', '', 8, '\0', 'notthebees@example.com', '1969-04-20', '2011-09-12 18:59:27');
347
348-- --------------------------------------------------------
349
350--
351-- Structure for view `schedule`
352--
353DROP TABLE IF EXISTS `schedule`;
354
355CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `schedule` AS select `st`.`descr` AS `student_descr`,`d`.`descr` AS `department_descr`,`c`.`descr` AS `course_descr`,`se`.`id` AS `section_descr` from ((((`student` `st` join `class__student_section` `cl`) join `section` `se`) join `course` `c`) join `department` `d`) where ((`st`.`id` = `cl`.`student_id`) and (`se`.`id` = `cl`.`section_id`) and (`c`.`id` = `se`.`course_id`) and (`d`.`id` = `c`.`department_id`));