Case Study: Normalization
In this assignment, please examine the data table below and transform it into 3NF. Please address any data quality problems that you note in the data table. For example, look for and eliminate composite attributes, derived attributes, and multivalued attributes before creating your 1NF tables.
Imagine the following scenario: The Public Safety office at Millennium College maintains a list of parking tickets issued to students for the fall semester. As an experienced database designer, you are asked to help the Public Safety office create a better database design in order to eliminate data redundancy.
Hint: A primary key is not identified in the table below. As a first step, find the attribute that uniquely identifies each row in the table of data. A single attribute can serve as the primary key for this table. In other words, the table of data provided does not need a composite primary key.
Table 1: Parking Tickets
STID Name FName PhoneNo St Lic LicNo 38249 Brown, Thomas 111-7804 FL BRY 123 38249 Brown, Thomas 111-7804 FL BRY 123 82453 Green, Sally 391-1689 AL TRE 141 82453 Green, Sally 391-1689 AL TRE 141 82453 Green, Sally 391-1689 AL TRE-141
JicketNo Date 15634 09/17/17 16017 09/13/17 14987 09/05/17 16293 09/18/17 17892 09/13/17
Code Fine 2 $25 1 $15 3 $100 1 $15 2 $25
Please examine the table of data and perform the following tasks:
1. Depict the full key, partial, and transitive functional dependencies in the data table using the written notation (e.g., ID -> Name, Address).
2. Describe whether the data table is in 1NF: if it is not in 1NF, transform it into 1NF and include actual data in the 1NF table(s).
3. Describe whether the data table is in 2NF; if it is not in 2NF, transform it into 2NF and include actual data in the 2NF table(s).
4. Describe whether the data table is in 3NF; if it is not in 3NF, transform it into 3NF and include actual data in the 3NF table(s).
5. Draw a relational schema for the 3NF table(s).