Briefly describe the following terms:

Well-structured relation

Normalization

Functional dependency

Describe conditions which imply a relation is in the third normal form.

Below table shows a portion of data regarding parking tickets issued to vehicles parked illegally on the campus. Attribute names are abbreviated to conserve space.

Identify the normal form in which the table currently is. Is this table is a relation?

Convert this table to a relation by giving a proper relation name and entering appropriate data in the table.

What is the primary key (i.e., a determinant) of this relation?

The table in Q5 shows a poor structure for relational databases. Describe anomaly examples that can result in because of this relation. Give the example for each case; insertion, deletion and modification (i.e., update) operation.

dependency diagram that shows all functional dependencies in the relation, based on the sample data shown [with common sense]. For the dependency diagram, use or X Y notation. We assume the fine amount is determined by the fine code. ^{X Y Z}

What are all possible determinants in this relation? You may know them from the functional dependencies.

Among the functional dependencies, list partial dependencies if any.

Among the functional dependencies, list transitive dependencies if any.

Take the actions (if any) necessary to convert the relation into the third normal form (3NF). Draw a set of the relations using relational schema diagram. (Clearly specify the primary key (PK) of each relation, and foreign keys (e.g., FK, FK1, K2) if available. Denote the reference table of a foreign key using arrow.

Phone No St Lic Lic No Ticket # 15634 16017 14987 16293 17892 Code Fine $25 $15 $100 $15 $25 St ID F Name Date 10/17/2015 11/13/2015 10/05/2015 11/18/2015 12/13/2015 2 L Name 2 38249 Brown Thomas 111-7804 FL BRY 123 82453 Green Sally 391-1689 AL TRE 141