View the step-by-step solution to:

For the final phase of the project you will be adding data to the various tables and performing SQL statements to return information from those...

For the final phase of the project you will be adding data to the various tables and performing SQL statements to return information from those tables. I will be providing the data you need to add to the tables as well as a description of the information needed from which you will write the SQL statements. Before that can be done however you will need to make sure that your tables are set up correctly. The first document posted is the table structure with the PK and FK fields identified. It is important that you review your Phase 2 create statements and make sure you have the same set up. Please note that the names I have chosen for the fields do not need to match what you have. What is important is that you have all of the PK and FK fields in each table. Also, it will not be necessary for you to redo Phase 2 if your tables are not quite correct; each phase starts from a standard set of information.

  1. Review the Phase 3 Table Keys document. If you need to fix any of your tables, open your Phase 2 SQL file in Management Studio, fix the Create statements to include the missing key fields, and rerun the SQL. This will insure your tables are in the proper format for adding data and running the SQL
  2. Review the Phase 3 Data Requirements document. You will need to insert all of the data in this document. As there is a large amount of data, you will be required to use INSERT statements for the first five rows of each table. After that, you can use the edit feature of Management Studio to enter the data into the tables.
  3. Review the Phase 3 SQL document. You must write nd execute a SQL statement for each of the requirements in the document.
  4. As with Phase 2, you will be required to create one SQL file for the Insert statements. 
  5. For the SQL, you will need to submit a Word document that has each of the SQL statements and the output from the statements.  
  6. Submit the SQL file and the Word document in the assignment.




IT230

Phase 2 Requirements



PROFESSOR

Professsor (ProfID PK)

Department (DeptID FK)

Professor Specialty

Professor Rank

Professor Last Name

Professor First Name

Professor Initial

Professor Email


SCHOOL

SchoolID PK

School Name


SCHOOLDEAN

School (SchoolID PK FK)

Dean (ProfID PK FK)

DEPARTMENT

Department Code (DeptID PK)

Department Name

School (SchoolID FK)


DEPARTMENTCHAIR

Department (DeptID PK FK)

Chair (ProfID PK FK)

STUDENT

Student ID (StudID PK)

Department

Student Last Name

Student First Name

Student Initial

Student Email

Advisor (ProfID FK)

COURSE

Course Code (PK)

Department (DeptID FK)

Course Title

Course Description

Course Credits

CLASS

Class Code (PK)

Class Section

Class Time

Professor (ProfID FK)

Course (CourseCode FK)

Room (RoomCode FK)

BUILDIING

BldgID (PK)

Building Name

Building Location

ROOM

Room Code (PK)

Room Type

Building (BldgID FK)

ENROLL

Class (ClassCode PK FK)

Student (StudID PK FK)

Enrollment Date

Enrollment Grade

IT230 - Phase 3 Data Requirements

Professor

ProfID

Department

Rank

Last Name

First Name

Initial

Email

SJ001

MSIS

Professor

Jones

Samuel

L

NS001

MSIS

Professor

Smith

Nancy

P

RA001

BIO

Adjunct

Adams

Robert

R

BB001

AER

Professor

Brooks

Bradford

C

SM001

AM

Adjunct

McDonald

Susan

W

SW001

DAN

Professor

Winsor

Stephen

A

TA001

ECO

Professor

Anthony

Todd

T

JW001

SE

Professor

Walker

Joanne

L

TH001

AER

Professor

Hastings

Timothy

 

MY001

PHY

Professor

Young

Margaret

J

CM001

CS

Professor

MacNamara

Christine

F

DW001

MUS

Professor

Winsor

Deborah

 

HB001

EE

Professor

Brown

Harold

R

CN001

BIO

Professor

Noble

Charles

E

FL001

PHY

Professor

Lawton

Francis

X

PH001

CE

Adjunct

Hohl

Patrick

 

KY001

AER

Professor

Yang

Kim

 

JS001

PHY

Adjunct

Santiago

Jose

M

Shool

SchoolID

School Name

Mgt

Management

Eng

Engineering

Edu

Education

FA

Fine Arts

Math

Mathematics

Sci

Science

SchoolDean

SchoolID

ProfID

Mgt

NS001

Eng

KY001

Edu

HB001

FA

DW001

Math

CM001

Sci

JS001

DepartmentChair

DeptID

ProfID

MSIS

SJ001

BIO

CN001

PHY

JS001

MKT

NS001

ECO

TA001

CHE

MY001

DAN

SW001

MUS

DW001

AER

TH001

CE

PH001

CS

CM001

AM

SM001

EE

JW001

SE

JW001

Department

DeptID

Name

SchoolID

MSIS

MSIS

Mgt

BIO

Biology

Sci

PHY

Physics

Sci

MKT

Marketing

Mgt

ECO

Economics

Mgt

CHE

Chemistry

Sci

DAN

Dance

FA

MUS

Music

FA

AER

Aerospace

Eng

CE

Civil Engineering

Eng

CS

Computer Science

Math

AM

Applied Mathematics

Math

EE

Elementary Education

Edu

SE

Special Education

Edu


Student

StudID

DeptID

Last Name

First Name

MI

Email

Advisor

PP001

MSIS

Pierce

Paul

A

NS001

AA001

BIO

Adams

Abigail

W

CN001

WS001

ECO

Smithson

William

P

TA001

AB001

CS

Bonner

Allison

W

CM001

MB001

SE

Brady

Marcia

 

JW001

KN001

MSIS

Nelson

Kenneth

A

SJ001

CA001

MSIS

Alexander

Constance

C

NS001

TN001

CS

Nguyen

Trin

 

CM001

SB001

AER

Brandt

Stephen

R

TH001

JE001

PHY

Espanet

Jesse

 

 

 

JE002

DA

Espanet

Jordan

 

 

SW001

EL001

PHY

Lawton

Eleanor

 

MY001

RK001

PHY

King

Robert

L

 

RK002

CS

Kershaw

Rowena

C

CM001

RM001

EE

McNamara

Richard

 

HB001

LC001

MUS

Chin

Lori

 

 

JM001

EE

Martinez

Jose

 

HB001

MB002

MKT

Brown

Marcus

L

 

 

Course

CourseCode

DeptID

Title

Description

Credits

MGT105

MSIS

Intro to Information Systems

Lecture

3

MGT475

MKT

Strategic Marketing

Lecture

4

ENG210

AER

Engineering Mecahnics I

Lab

3

ENG320

CE

Electric Circuits

Lab

4

EDU117

EE

Educational Equity and the Law

Lecture

3

EDU256

EE

Education and Religion

Lecture

3

MATH300

AM

Statistics

Lecture

4

MATH325

AM

Calculus II

Lecture

4

SCI110

PHY

Physics I

Lab

3

SCI415

PHY

Thermodynamics

Lab

4



Class

ClassCode

Class Section

Class Time

CourseCode

ProfID

RoomCode

CL001

1

MWF 8:00 - 9:00

MGT105

SJ001

MCLH001

CL002

2

TTh 1:30 - 3:00

MGT105

NS001

KEC001

CL003

1

MWF 1:00 - 2:00

MGT475

SJ001

SLH001

CL004

1

MWF 11:00 - 12:00

ENG210

KY001

PAC001

CL005

2

TTh 11:30 - 1:00

ENG210

KY001

MCL001

CL006

3

W 6:00 - 9:00

ENG210

BB001

PAL001

CL007

1

TTh 8:30 - 11:00

MATH300

SM001

MCC001

CL008

1

MWF 10:00 - 11:00

MATH325

MY001

SLL001

CL009

1

M 6:00 - 9:00

SCI110

FL001

SAL001

CL010

1

Th 6:00 - 9:00

SCI415

MY001

SAL001

ENROLL

ClassCode

StudID

Enrollment Date

Grade

CL001

PP001

9/1/2017

 

CL002

PP001

9/1/2017

 

CL003

PP001

9/1/2017

 

CL009

AA001

9/1/2017

 

CL010

AA001

9/1/2017

 

CL004

WS001

9/1/2017

 

CL005

AB001

9/1/2017

 

CL006

AB001

9/1/2017

 

CL008

AB001

9/1/2017

 

CL009

JE001

9/1/2017

 

CL010

JE002

9/1/2017

 

CL001

JE002

9/1/2017

 

CL004

LC001

9/1/2017

 

CL008

JM001

9/1/2017

 

CL009

MB002

9/1/2017

 

CL006

MG001

9/1/2017

 

CL005

AA002

9/1/2017

 

CL006

AA002

9/1/2017

 

CL003

KN001

9/1/2017

 

CL010

CA001

9/1/2017

 


Building

Room

BldgID

Name

Location

RoomCode

Type

BldgID

MC

McCormack

Boston

MCL001

Lab

MC

QU

Quinn

Boston

MCC001

Classroom

MC

KE

Kennedy

Worcester

MCLH001

Lecture Hall

MC

SL

Sloan

Worcester

QUL001

Lab

QU

SA

Saltonstall

Boston

QUC001

Classroom

QU

PA

Parks

Amherst

QULH001

Lecture Hall

QU

KEL001

Lab

KE

KEC001

Classroom

KE

KELH001

Lecture Hall

KE

SLL001

Lab

SL

SLC001

Classroom

SL

SLLH001

Lecture Hall

SL

SAL001

Lab

SA

SAC001

Classroom

SA

SALH001

Lecture Hall

SA

PAL001

Lab

PA

PAC001

Classroom

PA

PALH001

Lecture Hall

PA

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question