Consider the following attributes for tables in a relational model designed to k

Question

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.

Explanation / Answer

Normalization: Normalization in a databse is a way of organizing the data to avoid data redundancy, insertion,update & deletion anomalies.

Update anomalies If data items that are stored in tables that are not linked to each other properly, then it could become worst to perform relational operations on that data.

Deletion anomalies Deletion anomalies causes situations like,if we tried to delete a record, but some parts of data was left undeleted.

Insert anomalies Insert anomalies causes situations like, we tried to insert data in a record but does not exist at all.

Given relationships are:

TRANSACTION (AccountNumber, AccountType, AccountBalance, TranscationNumber, TranscationType, TransactionAmount, CustomerID).

To make above relation BCNF it should follow 1st NF, 2nd NF and 3rd NF.

1st NF: No two Rows of data must contain repeating group of information.

2nd NF: Every non-prime attribute should be fully functionally dependent on prime key attribute.

3rd NF: There should not be situations that a non-prime attribute is determined by another non-prime attribute.

BCNF: For each and every non-trivial functional dependency, X A, X must be a super-key.

Given relationships are:

Invoice (invoiceNumber, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, truckCapacity, driverLicNo, driverName, actualCost, amountOfDamages, costOfTolls, tax, finalAmount, datePaid, checkNumber, customerID, customerName, customerCurrentAddress)

By applying BCNF,

Invoice (invoiceNumber, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost)

truck#Assigned(truck#Assigned, truckCapacity,invoiceNumber)

driverLicNo(driverLicNo, driverName, actualCost, amountOfDamages, costOfTolls, tax, finalAmount, datePaid, checkNumber, customerID)

custome(customerID,customerName, customerCurrentAddress, invoiceNumber)

2) Event (clientID, eventDate, eventStartTime, clientLName, cFName, eventDuration, eventType, numberGuests, locationName, linenColorRequested, numberWaiters, numberBartenders, totalPrice, floristName, floristCost, musicContact, musicContactPhone, musicType, musicCost, entertainerName, entertainerCost, photographerName, photographerCost, menuNumberChosen, menuAppetizer, menuSalad, menuMain, menuDessert)

Event ( eventDate, eventStartTime,eventDuration, eventType, numberGuests, locationName, linenColorRequested, numberWaiters, numberBartenders, totalPrice, floristName, floristCost)

client(clientID,clientLName,locationName)

music(musicContact, musicContactPhone, musicType, musicCost, entertainerName, entertainerCost, photographerName, photographerCost)

menuNumberChosen(menuNumberChosen, menuAppetizer, menuSalad, menuMain, menuDessert,lientID)

3)CARSALE (car#, salesperson#, dateSold, commissionRate, discount)

CARSALE (car#, salesperson#, dateSold)

Date_sold(discount,car#)
car#(car#,dateSold, commissionRate, discount)

salesperson#(com

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.