Consider the following attributes for tables in a relational model designed to keep track of information for a moving company that moves residential customers, usually from one home or apartment to another:

customerID, customerName, customerCurrentAddress, customerCurrentPhone, customerNewAddress, customerNewPhone, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, driverName, driverLicNo, actualCost, amountOfDamages, truckCapacity, costOfTolls, tax, finalAmount, invoiceNumber, amountPaid, datePaid, checkNumber, amountDue

Assume:

Although in most cases the pickupLocation is the customer s old address and the dropOffLocation is the new address, there are exceptions, such as when

furniture is moved to or from storage.

An estimate is provided before the move using a pre-printed invoice containing a unique invoice number. The actual cost is recorded on the same

form once the move is complete. The actual cost may differ from the estimated cost. The final amount includes the actual cost of the move, plus tax and tolls.

In most cases, the customer pays the final amount immediately, but sometimes he or she pays only part of the bill, especially if the amount of

damages is high. In those cases, there is an amount due, which is the difference between the final amount and the amount paid.

a. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation. b. What are the candidate keys for this relation? Identify the primary key. c. Is the relation in third normal form? If not, find a 3NF lossless join decomposition of Books that preserves dependencies. d. Is the relation or resulting set of relations in Boyce-Codd Normal Form? If not, find a lossless join decomposition that is in BCNF. Identify any functional dependencies that are not preserved.

e. What tables would you actually implement? Explain any denormalization, omissions, or additions of attributes.