Database design Normalization Workshop Review the following excerpt of a Parket
ID: 3701171 • Letter: D
Question
Database design
Normalization Workshop
Review the following excerpt of a Parket Ticket Report produced at a local college.
(1) Assume that this the information in this report is stored in a table that is in 1st normal form. Show the complete contents of this table.
(2) Given the contents of this table, identify the determinants and indicate what is functionally dependent on each determinant.
(3) Identify anomalies that can result from using this table.
(4) Apply the normalization process to develop a set of tables in third normal form. Add a new column with the heading Violation in the appropriate table to explain the reason for each ticket. Values in this column are expired parking meter (ticket code 1), no parking permit (ticket code 2), and handicap violation (ticket code 3).
Explanation / Answer
1. 1NF
2. Functional dependencies:
STID -> LName,FName,PhoneNo,StLIc,LicNo
Ticket# -> Date,Code,Fine
3. Anomalies
Insertion --- To add a new STID , Ticket# needs to be inserted, Ticket# cannot be left NULL which is an anomaly
Deletion --- If a Ticket is to be deleted , the corresponding STID and other details will also get deleted.
Updation --- If the PhoneNo needs to be changed , it has to be modified in all the concerned rows otherwise inconsistency exists.
4. 3NF
ST(STID, LName,FName,PhoneNo,StLIc,LicNo)
Ticket(Ticket# , Date,Code,Fine,Violation)
underlined are primary keys. Violation column should be added to the Ticket table to show the reason of ticket creation.
Do ask if any doubt. Please upvote.
STID LName FName PhoneNo StLic LicNo Ticket# Date Code Fine 38249 Brown Thomas 111-7804 FL BRY123 15634 10/17/10 2 $25 38249 Brown Thomas 111-7804 FL BRY123 16017 11/13/10 1 $15 82453 Green Sally 391-1689 AL TRE141 14987 10/05/10 3 $100 82453 Green Sally 391-1689 AL TRE141 16293 11/18/10 1 $15 82453 Green Sally 391-1689 AL TRE141 17892 12/13/10 2 $25Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.