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.

Create tables for this database in 3NF form.

a. Look at the data in the excel sheet provided and determine the functional dependencies in the data.

b. 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 to show the list of tables, foreign keys.

Stud_SSN Branch_ID Branch Name 2146589647 20ITM 2456987412 21 BA 4579625879 22 CSC 4123658749 21 MECH Branch school Stud Name JSOMUTD Joseph Marks JSOM,UTD Mary Barnhill ERIK UTD Elizabeth Smith ERIK UTD Martin Jones Stud Age Stud Street Stud Location Stud Zip School City School State School Zip School Code Start Date End Date 20 Jupiter Rd Allen, TX 75002 Richardson TX 75080 4243 1/1/2017 12/1/2017 21 Campbell Rd Frisco, TX 75070 Richardson TX 75080 4243 1/1/2017 12/1/2017 24 Coit Rd Dallas, TX 75253 Richardson TX 75080 4243 1/1/2017 12/1/2017 19 Pilot Dr Plano, TX 75025 Richardson TX 75080 4243 1/1/2017 12/1/2017 Spring Valley 22 Rd Richardson, TX 75080 Richardson TX 75080 4243 1/1/2017 12/1/2017 21 Pilot Dr Plano, TX 75025 Richardson TX 75080 4243 1/1/2017 12/1/2017 21 Pilot Dr Plano, TX 75025 Austin TX 78712 3658 12/2/2017 1/1/2200 4578562145 2146589635 2146589635 38 NEURO 20 ITM 38 MIS SOBBS, UTD JSOM UTD McComb. UTA Albert Lau John Lewis John Lewis