HiTech Computer Security performs computer security audits for contracted customers. Each customer has a unique customer number and an address (more than one customer may be at any given address). When a HiTech engineer has to perform audits they are allocated a van to use on that day. If the engineer finishes auditing all their customers for that day, the van could be reallocated to another engineer. Each van has a unique van number and each engineer has a unique staff number. On any given day, HiTech will only audit a given customer once.

An initial schema for HiTech is:

Audit(custAddress, custNum, date, staffNum, staffName, vanNum)

Q2.1

a) Identify and describe two sources of redundancy in the Audit relation.

b) Illustrate, using examples, how the Audit relation could be subject to an

i) insert anomaly

ii) deletion anomaly

iii) update anomaly

Q2.2

a) List the functional dependencies. Do not include trivial functional dependencies.

b) Indicate possible candidate key(s) of the Audit relation.

Q2.3

a) Explain, with the assistance of functional dependencies, the normal form of the Audit relation.

Q2.4

Perform a lossless decomposition of the Audit relation into relations in 3NF. For each resulting relation, underline the primary key and denote any foreign keys with asterisks (*).