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.

Name 20 11M 21 BA 20 Jupiter Rd Allen TX 21 Ca 4243 2017/1/1 2017/12/1 75002 Richardson TX 75070 Richardson TX Joseph Marks 22146589647 2456987412 75080 75080 Elizabeth Smith 22 CSC 211MECH 24 Coit RdDallas 19 Pilot Dr Plano,TX ERIK,UTD 75253 Richardson TX 75025 Richardson TX 75080 75080 4579625879 5 4123658749 4243 2017/1/12017/12/1 5 4578562145 7 2146589635 s 2146589635 Spring 22 Valley Rd Richardson,TX 75080Richardson 21 Pilot Dr Plano,TX 21 Pilot Dr 75080 75080 78712 20 ITM 38 MIS JSOM,UTD McComb, UTA John Lewis John Lewis 4243 2017/1/1 2017/12/1 3658 2017/12/2 2200/1/1 75025 Richardson TX 75025 Austin TX