St ID
38249
82453
L Name
Brown
Green
F Name
Thomas
Sally
Phone No
111-7804
391-1689
St Lic
FL
AL
Lic No
BRY 123
TRE 141
Ticket #
15634
16017
14987
16293
17892
Date
10/17/2015
11/13/2015
10/05/2015
11/18/2015
12/13/2015
Code Fine
2 $25
1 $15
3 $100
1 $15
2 $25
Attribute names are abbreviated for brevity. St ID is a student ID, St Lic is the student's car license
sate, Lic No is the car license number, Ticket# is the parking ticket number, Date is the date the ticket
is issued, Code is the fine code, and so on.
(1) Identify the current normal form of the table. Is this table a relation? Justify your answer.
(2) Convert the table into a relation by assigning a relation name, StudentParkingTicket and entering
appropriate values in the relation.
(3) Show the relational schema in the format R(A1, A2, ...) where a composite key (StID, Ticket#)
serves as the primary key.
(4) Draw a functional dependency diagram based on the sample data, using common sense assumptions.
Use X→ Y notation to represent functional dependencies.
(5) Identify all possible determinants in the relation. Derive them from the functional dependencies.
(6) List any partial dependencies present in the relation, StudentParkingTicket.
(7) List any transitive dependencies present in the relation.
(8) Normalize this relation into 3NF, decomposing it into a set of relations. Clearly specify the primary
key for each relation and any foreign keys and their referencing relations.