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	Email
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)