Question
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
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). (DONOT NEED TO WRITE ANY CODE FOR ANSWER)
Following are some things to consider during the normalization process:
- You are not designing a new database, only fixing what exists. You need to consider what is the purpose of the database and at the end of the normalization process, are you able to answer queries required by the business case. This helps you focus on the scenario and not slow down the process getting into unnecessary details.
- If you see a column with comma separated value, break them into multiple rows in case of a 1:M relationship or multiple attributes in case of 1:1 relationship.
- Consider the rules for the primary keys discussed in class to identify the determinants and start recognizing functional dependencies keeping the ideal primary keys as LHS of the functional dependency.
- Always read the narrative of the database description and add additional data rows to consider multiple scenarios. This will help you eliminate multiple functional dependencies that might exist with the given data.
- You can start with one attribute or a set of attributes at a time and find its closure (i.e. all functional dependencies relating to them). The aim of this exercise is to find what attributes depend on a given set of attributes and therefore ought to be together.
- Look for possible attributes in the sample data. Attributes are additional columns that describe an entity. If you have identified the attributes, they exist on the RHS of the functional dependency.
- Create an ERD using the sample data and narrative. This will help you identify entities, their attributes and their relationships with other entities. Identifying relationships between entities helps identify the determinants and dependents in a functional dependency.
Sometimes the attributes in a given relation are sure not to be the primary keys and hence not appear in the LHS of a functional dependency. In the example about start_date and end_date depend on student attending a branch in a school. So, the start_date and end_date dont exist until student attends. Its clear that start_date and end_date are dependents and would not determine other attributes.
| 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 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Database Directions From Relational To Distributed Multimedia And Object Oriented Database Systems
Authors: James Larson
1st Edition
0132908670, 978-0132908672
