Question

Normalize the following relations. Show possible candidate/primary keys and the functional dependencies. Explain the normal form in which the relation is currently in (based on the sample data) and how do you break the relations to get 3^rd Normal Form. Show the new relations obtained after normalization and underline the candidate/primary key in each new relation and italic the foreign key.

Relation 1:

COLLEGE PARKING TICKET (STID, LName, FName, PhoneNo, StateLic, LicNo, Ticket#, Date, Code, Fine)

(Illustrated with sample data)

STID	LName	FName	PhoneNo	State Lic	LicNo	Ticket#	Date	Code	Fine
38249	Brown	Thomas	111-7804	FL	BRY 123	15634	10/17/10	2	$25
38249	Brown	Thomas	111-7804	FL	BRY 123	16017	11/13/10	1	$15
82453	Green	Sally	391-1689	AL	TRE 141	14987	10/05/10	3	$100
82453	Green	Sally	391-1689	AL	TRE 141	16293	11/18/10	1	$15
82453	Green	Sally	391-1689	AL	TRE-141	17892	12/13/10	2	$25

Answer 1

In the above table we can see that the attributes on which the entire table data is relying on are student ID and Ticket #.

The reason for the above being

The student last name, first name , phone number, license number, state could be retrieved by giving the student ID.

Similarly the ticket # could fetch you the details like

Date on which the ticket was assigned, the category code ( for the offence),the appropriate fine amount.

Hence the dependancy diagram would be something like as shown in the image attached.

Here we could see a transitive dependency existing between the attributes code and the appropriate fine amount.

Note : Since the code decides the amount of fine to be paid ( the fine amount is dependent on the assigned code)

Thus a 3 NF results in splitting the table to Student, Ticket and Code tables with the primary keys student id, ticket#, code. The foreign keys are marked in the attached image.