This is a "completion" type assignment to give you practice with normalization. You will earn all points by completing all tasks defined in this assignment. An answer key will be posted after the due date.

Objective

This assignment will give you practice in the identification of functional dependencies and in normalizing a table to $3$ NF $.$

Use the following table and sample data to complete the exercises listed below:

Description:

The Public Safety office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on the campus. The above table shows a portion of this list for the fall semester.

Business Rules:

$-$ Each student can have more than one ticket. Each ticket is for a single student.

$-$ The student's name may not be unique within the university, but each student has a unique student id number $($STID$).$

$-$ A phone number may be shared by more than one student.

$-$ Multiple tickets may be issued each day to different students.

$-$ For a single ticket, multiple violations may apply.

$-$ Each student may have more than one license $($car tag$)$ registered with Parking Services

$-$ License numbers $($car tag numbers$)$ can be shared by more than one student.

$-$ A license number $($car tag number$)$ could be used by more than one state, but each license number $($car tag number$)$ is unique within a state.

Complete the Following Tasks Using the Table Shown Above:

$1.(3)$ Convert the above table to a relation in $\backslash (1$ N F $\backslash ).$ Show the new table.

$2.(2)$ What is$/$are the candidate key$($s$)$ of the table? If you found more than one candidate, which key is the 'better' choice?

$3.(7)$ Identify all functional dependencies shown in the diagram. You may show the functional dependencies using ONE of the $3$ following formats:

a$.$ Adding arrows to the table above indicating the determinants and the functional dependencies $-$ see slide $\backslash$#$5$ in $006$b$-$NormalizationExamples

b$.$ Listing a determent followed by an arrow followed by the attribute determined by the determinant dependencies $-$ see slide $\backslash$#$5$ in $006$b$-$NormalizationExamples

c$.$ A Functional Dependency Diagram $-$ see slide $\backslash$#$8$ in $006$b$-$NormalizationExamples

$4.(3)$ Give an example of an anomaly that can occur if the data is stored in a single table as shown above.

$5.(7)$ Normalize the above table to $\backslash (3$ N F $\backslash ).$ Show the relations that result from your normalization process $-$ see slide $\backslash$#$13$ in $006$b$-$NormalizationExamples