· 9 years ago · Sep 30, 2016, 09:34 AM
1/* Assignment #1 for DATABASE DESIGN(29638-01) */
2/* Made by SAM Tony (7000114) */
3/* Implementing database shown in web page below */
4/* http://2012books.lardbucket.org/books/getting-the-most-out-of-information-systems-v1.3/s15-02-data-information-and-knowledge.html */
5
6/* Dropping any already existing database called 'University' */
7DROP DATABASE IF EXISTS `University`;
8
9/* Creating empty database called 'University' */
10CREATE DATABASE `University`;
11USE `University`;
12
13/* Creating table 'Students' */
14CREATE TABLE `Students` (
15 `STUDENT_ID` VARCHAR(9) NOT NULL,
16 `FIRST_NAME` VARCHAR(50) NOT NULL,
17 `LAST_NAME` VARCHAR(50) NOT NULL,
18 `CAMPUS_ADDR` VARCHAR(50) NOT NULL,
19 PRIMARY KEY (`STUDENT_ID`)
20);
21
22/* Inserting data for table 'Students' */
23INSERT INTO `Students` (`STUDENT_ID`, `FIRST_NAME`, `LAST_NAME`, `CAMPUS_ADDR`) values
24('U11-00001', 'Phil', 'D''Basket', 'Weaver Hall 411'),
25('U11-00002', 'Art', 'Major', '101 Creative Commons'),
26('U11-00003', 'Anne', 'Teak', 'Stickley 014'),
27('U11-00004', 'Anita', 'Knapp', 'Highway View 303'),
28('U11-00005', 'Easton', 'West', 'N. South Hall 123'),
29('U11-00006', 'Jack', 'Hammer', 'Tule 543');
30
31/* Creating table 'CourseTitle' */
32CREATE TABLE `CourseTitle` (
33 `COURSE_NUM` VARCHAR(5) NOT NULL,
34 `COURSE_NAME` VARCHAR(50) NOT NULL,
35 PRIMARY KEY (`COURSE_NUM`)
36);
37
38/* Inserting data for table 'CourseTitle' */
39INSERT INTO `CourseTitle` (`COURSE_NUM`, `COURSE_NAME`) values
40('MI101', 'IS for Managers'),
41('MI201', 'Prog. for Mgmt.'),
42('MI202', 'Database Sys. & Bus. Intell.'),
43('EN101', 'Intro. to Lit.'),
44('CJ101', 'Fighting Cyber-Crime');
45
46/* Creating table 'Faculty' */
47CREATE TABLE `Faculty` (
48 `FAC_ID` VARCHAR(8) NOT NULL,
49 `FIRST_NAME` VARCHAR(50) NOT NULL,
50 `LAST_NAME` VARCHAR(50) NOT NULL,
51 `DEPT` VARCHAR(50) NOT NULL,
52 `CAMPUS_ADDR` VARCHAR(50) NOT NULL,
53 PRIMARY KEY (`FAC_ID`)
54);
55
56/* Inserting data for table 'Faculty' */
57INSERT INTO `Faculty` (`FAC_ID`, `FIRST_NAME`, `LAST_NAME`, `DEPT`, `CAMPUS_ADDR`) values
58('F-100005', 'Marshall' , 'Law', 'Criminal Justice', 'Warren 232'),
59('F-100006', 'Bo', 'Ring', 'Sleep Studies', 'Giles B-12'),
60('F-100007', 'Paige', 'Turner', 'English', 'Gasson 134'),
61('F-100008', 'Penny', 'Wyse', 'Finance', 'Fulton 480A'),
62('F-100009', 'Meg', 'Abe-Bight', 'Information Systems', 'Fulton 460C'),
63('F-100010', 'Mike', 'Rowe', 'Information Systems', 'Fulton 460B');
64
65/* Creating table 'CourseList' */
66CREATE TABLE `CourseList` (
67 `COURSE_INDX` INT(4) NOT NULL,
68 `COURSE_NUM` VARCHAR(5) NOT NULL,
69 `TIME` VARCHAR(10) NOT NULL,
70 `LOCATION` VARCHAR(50) NOT NULL,
71 `FAC_ID` VARCHAR(8) NOT NULL,
72 PRIMARY KEY (`COURSE_INDX`),
73 FOREIGN KEY (`COURSE_NUM`) REFERENCES `CourseTitle` (`COURSE_NUM`),
74 FOREIGN KEY (`FAC_ID`) REFERENCES `Faculty` (`FAC_ID`)
75);
76
77/* Inserting data for table 'CourseList' */
78INSERT INTO `CourseList` (`COURSE_INDX`, `COURSE_NUM`, `TIME`, `LOCATION`, `FAC_ID`) values
79(9301, 'MI101', 'MWF 9', 'Fulton 250', 'F-100009'),
80(9302, 'MI101', 'TTh 10', 'Fulton 250', 'F-100010'),
81(9303, 'MI201', 'MWF 11', 'Fulton 150', 'F-100009'),
82(9304, 'MI202', 'TTh 1:30', 'Fulton 130', 'F-100009'),
83(9305, 'EN101', 'TTh 3', 'Gasson 205', 'F-100007'),
84(9306, 'CJ101', 'MWF 3', 'Robsham 021', 'F-100005');
85
86/* Creating table 'Enrollment' */
87CREATE TABLE `Enrollment` (
88 `STUDENT_ID` VARCHAR(9) NOT NULL,
89 `COURSE_INDX` INT(4) NOT NULL,
90 FOREIGN KEY (`STUDENT_ID`) REFERENCES `Students` (`STUDENT_ID`),
91 FOREIGN KEY (`COURSE_INDX`) REFERENCES `CourseList` (`COURSE_INDX`)
92);
93
94/* Inserting data for table 'Enrollment' */
95INSERT INTO `Enrollment` (`STUDENT_ID`, `COURSE_INDX`) values
96('U11-00002', 9301),
97('U11-00002', 9303),
98('U11-00002', 9306),
99('U11-00005', 9305),
100('U11-00003', 9301);