Stud_SSN

Branch_ID

Branch_Name

Branch_school

Stud_Name

Stud_Age

Stud_Street

Stud_Location

Stud_Zip

School_City

School_State

School_Zip

School_Code

Start_Date

End_Date

2146589647

20

ITM

JSOM,UTD

Joseph Marks

20

Jupiter Rd

Allen,TX

75002

Richardson

TX

75080

4243

1/1/2017

12/1/2017

2456987412

21

BA

JSOM,UTD

Mary Barnhill

21

Campbell Rd

Frisco,TX

75070

Richardson

TX

75080

4243

1/1/2017

12/1/2017

4579625879

22

CSC

ERIK,UTD

Elizabeth Smith

24

Coit Rd

Dallas,TX

75253

Richardson

TX

75080

4243

1/1/2017

12/1/2017

4123658749

21

MECH

ERIK,UTD

Martin Jones

19

Pilot Dr

Plano,TX

75025

Richardson

TX

75080

4243

1/1/2017

12/1/2017

4578562145

38

NEURO

SOBBS,UTD

Albert Lau

22

Spring Valley Rd

Richardson,TX

75080

Richardson

TX

75080

4243

1/1/2017

12/1/2017

2146589635

20

ITM

JSOM,UTD

John Lewis

21

Pilot Dr

Plano,TX

75025

Richardson

TX

75080

4243

1/1/2017

12/1/2017

2146589635

38

MIS

McComb, UTA

John Lewis

21

Pilot Dr

Plano,TX

75025

Austin

TX

78712

3658

12/2/2017

1/1/2200

Question 2 : (60 points)

The UT system is trying to create and convert their student records into digital form. The idea behind this database is to list all students in UT system colleges/universities with their Name, major, age during enrollment, address of residence and the corresponding school and university details.

There are no students who attend 2 colleges with in UT system at the same time, but there are many students who did their under-grad and grad with in UT systems. However, the variables Start_date and End_date is to mark when they went to which school. Branch_ID is not a unique value, same Branch_Id may represent another Branch in another University and a student can belong to only one branch in a college at a time.

To help understand UT systems data, an excel worksheet (UT students.xls) of current data fields they keep track of are provided. Look at the given MS Excel worksheet to see what kind of data they collect and record for each student enrollment.

Create tables for this database in 3NF form. Please note,

Look at the data in the excel sheet provided and determine the functional dependencies in the data. (20 Points)

Please provide detailed explanation about how you would proceed and split the data into multiple tables so that resulting tables do not contain redundancy so that it is in 3NF form. List each resulting table with its primary key(s), foreign key(s) properly identified. Remember to use the relational schema representation we used in class to show the list of tables, foreign keys.(40)