PHASE 3 DATA REQUIREMENTS The following pages contain the data requirements for Phase 3. A couple
Question:
PHASE 3
DATA REQUIREMENTS
The following pages contain the data requirements for Phase 3. A couple of things to note.
As these are typical representations of the way data may be supplied, you have to pay attention to the columns as the names are not necessarily the same as the column names in your create statements. Also, it is possible that the columns in the table are not in the same order in which the tables were created. Do not recreate the tables to match the data, you will need to figure out how to set up the insert statements to accommodate the input.
In order for everyone to be working with the same table structures, you will need to use the SQL file that is attached to the assignment. You may have to manually drop the tables you have already created if the names do not match what is in the SQL. Once you have run the SQL and created the tables you can then insert the data.
You need to inspect the data closely, especially the tables that have composite primary keys and individual foreign keys for each column. Remember the rules that apply; you need to decide if you should omit any rows that are missing data. In some instances you may have data that is required but omitting the row should not be a consideration. You have to be able to overcome the not null restriction on the column.
Professor | ||||||
ProfID | Specialty | Rank | Last Name | First Name | Initial | |
SJ001 | Information S..s@trinity.edu | |||||
NS001 | Data A..h@trinity.edu | |||||
RA001 | Microbiology | Adjunct | Adams | Robert | R | RAdams@t..s@trinity.edu |
SM001 | Discrete M..d@trinity.edu | |||||
SW001 | Choreography | Professor | Winsor | Stephen | A | SWinsor@t..y@trinity.edu |
JW001 | Child D..r@trinity.edu | |||||
TH001 | Statics | Professor | Hastings | Timothy | THastings@trinity.edu | |
MY001 | Quantum M..g@trinity.edu | |||||
CM001 | Software E..a@trinity.edu | |||||
DW001 | Voice C..r@trinity.edu | |||||
HB001 | Technology in E..n@trinity.edu | |||||
CN001 | Genetics | Professor | Noble | Charles | E | CNoble@trinity.edu |
FL001 | Statistical M..n@trinity.edu | |||||
PH001 | Hydraulics and H..l@trinity.edu | |||||
KY001 | Aerodynamics | Professor | Yang | Kim | KYang@t..o@trinity.edu |
Department | |
DeptID | Name |
MSIS | MSIS |
BIO | Biology |
PHY | Physics |
MKT | Marketing |
ECO | Economics |
CHE | Chemistry |
DAN | Dance |
MUS | Music |
AER | Aerospace |
CE | Civil Engineering |
CS | Computer Science |
AM | Applied Mathematics |
EE | Elementary Education |
SE | Special Education |
Student | ||||
StudID | Last Name | First N..e@trinity.edu | ||
AA001 | Adams | Abigail | W | aadams@t..n@trinity.edu |
AB001 | Bonner | Allison | W | abonner@t..y@trinity.edu |
KN001 | Nelson | Kenneth | A | knelson@t..r@trinity.edu |
TN001 | Nguyen | Trin | tnguyen@t..t@trinity.edu | |
JE001 | Espanet | Jesse | ||
JE002 | Espanet | Jordan | ||
EL001 | Lawton | Eleanor | elawton@t..g@trinity.edu | |
RK002 | Kershaw | Rowena | C | rkershaw@t..a@trinity.edu |
LC001 | Chin | Lori | lchin@t..z@trinity.edu | |
MB002 | Brown | Marcus | L |
COURSE | |||
CourseID | Title | Description | Credits |
MGT105 | Intro to Information Systems | Lecture | 3 |
MGT475 | Strategic Marketing | Lecture | 4 |
ENG210 | Engineering Mecahnics I | Lab | 3 |
ENG320 | Electric Circuits | Lab | 4 |
EDU117 | Educational Equity and the Law | Lecture | 3 |
EDU256 | Education and Religion | Lecture | 3 |
MATH300 | Statistics | Lecture | 4 |
MATH325 | Calculus II | Lecture | 4 |
SCI110 | Physics I | Lab | 3 |
SCI415 | Thermodynamics | Lab | 4 |
CLASS | |||||
Classid | ClassSection | ClassDays | ClassTime | CourseID | ProfID |
CL001 | 1 | MWF | 8:00 - 9:00 | MGT105 | SJ001 |
CL002 | 2 | TTh | 1:30 - 3:00 | MGT105 | NS001 |
CL003 | 1 | MWF | 1:00 - 2:00 | MGT475 | SJ001 |
CL004 | 1 | MWF | 11:00 - 12:00 | ENG210 | KY001 |
CL005 | 2 | TTh | 11:30 - 1:00 | ENG210 | KY001 |
CL006 | 3 | W | 6:00 - 9:00 | ENG210 | BB001 |
CL007 | 1 | TTh | 8:30 - 11:00 | MATH300 | SM001 |
CL008 | 1 | MWF | 10:00 - 11:00 | MATH325 | MY001 |
CL009 | 1 | M | 6:00 - 9:00 | SCI110 | FL001 |
CL010 | 1 | Th | 6:00 - 9:00 | SCI415 | MY001 |
ENROLL | ||||
ClassID | StudIentD | EnrollmentDate | Grade | CourseID |
CL001 | PP001 | 3/9/2020 | MGT105 | |
CL002 | PP001 | 1/4/2020 | MGT105 | |
CL003 | PP001 | 1/21/2020 | MGT475 | |
CL009 | AA001 | 3/12/2020 | SCI110 | |
CL010 | AA001 | 2/29/2020 | SCI415 | |
CL004 | WS001 | 2/15/2020 | ENG210 | |
CL005 | AB001 | 1/17/2020 | ENG210 | |
CL006 | AB001 | 3/31/2020 | ENG210 | |
CL008 | AB001 | 2/2/2020 | MATH325 | |
CL009 | JE001 | 1/20/2020 | SCI110 | |
CL010 | JE002 | 3/31/2020 | SCI415 | |
CL001 | JE002 | 1/15/2020 | MGT105 | |
CL004 | LC001 | 1/5/2020 | ENG210 | |
CL008 | JM001 | 3/17/2020 | MATH325 | |
CL009 | MB002 | 3/15/2020 | SCI110 | |
CL006 | MB001 | 4/1/2020 | ENG210 | |
CL005 | RK002 | 2/12/2020 | ENG210 | |
CL006 | RK002 | 4/5/2020 | ENG210 | |
CL003 | KN001 | 3/3/2020 | MGT475 | |
CL010 | CA001 | 2/12/2020 | SCI415 |
DEPTCHAIR | |
DeptID | ProfID |
MSIS | SJ001 |
BIO | |
PHY | JS001 |
MKT | NS001 |
ECO | |
CHE | MY001 |
DAN | SW001 |
MUS | DW001 |
AER | TH001 |
CE | |
CS | CM001 |
AM | SM001 |
EE | JW001 |
SE | JW001 |
ADVISOR | |
StudentID | ProfID |
PP001 | NS001 |
AA001 | CN001 |
WS001 | TA001 |
AB001 | |
MB001 | JW001 |
KN001 | SJ001 |
CA001 | NS001 |
TN001 | CM001 |
SB001 | TH001 |
JE001 | |
JE002 | SW001 |
EL001 | MY001 |
RK001 | |
RK002 | CM001 |
RM001 | HB001 |
LC001 | |
JM001 | HB001 |
MB002 |
STUDENTDEPT | |
StudentID | DeptID |
PP001 | MSIS |
AA001 | BIO |
WS001 | ECO |
AB001 | CS |
MB001 | SE |
KN001 | |
CA001 | MSIS |
TN001 | CS |
SB001 | |
JE001 | PHY |
JE002 | DAN |
EL001 | |
RK001 | PHY |
RK002 | CS |
RM001 | EE |
LC001 | MUS |
JM001 | EE |
MB002 | MKT |
COURSEDEPT | |
CourseID | DeptID |
MGT105 | MSIS |
MGT475 | MKT |
ENG210 | AER |
ENG320 | CE |
EDU117 | EE |
EDU256 | EE |
MATH300 | AM |
MATH325 | AM |
SCI110 | PHY |
SCI415 | PHY |
In order to have a consistent database you will need to use the attached SQL file to create the tables. You may have to manually drop the tables in MySQL in order to run this. Once all tables are created then you will need to insert the data that is in the second document. All insert statements should be in one .sql file and submitted in the drop box.
TABLE TO BE USED
USE MSIS230;
DROP TABLE IF EXISTS ENROLL;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS COURSEDEPT;
DROP TABLE IF EXISTS ADVISOR;
DROP TABLE IF EXISTS STUDENTDEPT;
DROP TABLE IF EXISTS DEPTCHAIR;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS PROFESSOR;
CREATE TABLE IF NOT EXISTS PROFESSOR
(
PROFID VARCHAR(45) PRIMARY KEY,
PROFSPECIALTY VARCHAR(45),
PROFRANK VARCHAR(45),
PROFLNAME VARCHAR(45) NOT NULL,
PROFFNAME VARCHAR(45) NOT NULL,
PROFINITIAL CHAR(2),
PROFEMAIL VARCHAR(45)
);
CREATE TABLE IF NOT EXISTS DEPARTMENT
(
DEPTID VARCHAR(45) PRIMARY KEY,
DEPTNAME VARCHAR(45) NOT NULL
);
CREATE TABLE IF NOT EXISTS COURSE
(
COURSEID VARCHAR(10) PRIMARY KEY,
COURSETITLE VARCHAR(45) NOT NULL,
COURSEDESCRIPTION VARCHAR(100),
COURSECREDITS INT
);
CREATE TABLE IF NOT EXISTS STUDENT
(
STUDENTID VARCHAR(10) PRIMARY KEY,
STUDENTLNAME VARCHAR(45) NOT NULL,
STUDENTFNAME VARCHAR(45) NOT NULL,
STUDENTINITIAL CHAR(2),
STUDENTEMAIL VARCHAR(45)
);
##### TABLES W/ FOREIGN KEYS #####
CREATE TABLE IF NOT EXISTS DEPTCHAIR
(
PROFESSORID VARCHAR(45),
DEPTID VARCHAR(45),
CONSTRAINT PK_DEPTCHAIR PRIMARY KEY (PROFESSORID, DEPTID),
CONSTRAINT FK_DEPT FOREIGN KEY (PROFESSORID) REFERENCES PROFESSOR (PROFID),
CONSTRAINT FK_CHAIR FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)
);
CREATE TABLE IF NOT EXISTS STUDENTDEPT
(
STUDENTID VARCHAR(10),
DEPTID VARCHAR(45),
CONSTRAINT PK_STUDENTDEPT PRIMARY KEY (STUDENTID, DEPTID),
CONSTRAINT FK_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),
CONSTRAINT FK_DEPARTMENT FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)
);
CREATE TABLE IF NOT EXISTS ADVISOR
(
PROFESSORID VARCHAR(45),
STUDENTID VARCHAR(10),
CONSTRAINT PK_ADVISOR PRIMARY KEY (PROFESSORID, STUDENTID),
CONSTRAINT FK_PROF FOREIGN KEY (PROFESSORID) REFERENCES PROFESSOR (PROFID),
CONSTRAINT FK2_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID)
);
CREATE TABLE IF NOT EXISTS COURSEDEPT
(
COURSEID VARCHAR(10),
DEPTID VARCHAR(45),
CONSTRAINT PK_COURSEDEPT PRIMARY KEY (COURSEID, DEPTID),
CONSTRAINT FK2_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
CONSTRAINT FK2_DEPT FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)
);
CREATE TABLE IF NOT EXISTS CLASS
(
CLASSID VARCHAR(10),
COURSEID VARCHAR(10),
PROFID VARCHAR(45),
CLASSSECTION CHAR(2),
CLASSDAYS VARCHAR (25),
CLASSTIME VARCHAR (25),
CONSTRAINT PK_CLASS PRIMARY KEY (CLASSID, COURSEID),
CONSTRAINT FK3_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID)
);
CREATE TABLE IF NOT EXISTS ENROLL
(
STUDENTID VARCHAR(10),
COURSEID VARCHAR(10),
CLASSID VARCHAR(10),
ENROLL_DATE DATETIME NOT NULL,
GRADE CHAR(2) NOT NULL,
CONSTRAINT PK_ENROLL PRIMARY KEY (STUDENTID, COURSEID, CLASSID),
CONSTRAINT FK3_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),
CONSTRAINT FK4_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),
CONSTRAINT FK_CLASS FOREIGN KEY (CLASSID) REFERENCES CLASS (CLASSID)