Convert the following relations into BCNF. Make sure you underline the primary k
ID: 3814693 • Letter: C
Question
Convert the following relations into BCNF. Make sure you underline the primary key of each relation and also identify the foreign keys (By hand you can circle them or use arrows).
1) Invoice (invoiceNumber, pickupLocation, dropOffLocation, dateOfMove, startingTime, estimatedWeight, estimatedCost, truck#Assigned, truckCapacity, driverLicNo, driverName, actualCost, amountOfDamages, costOfTolls, tax, finalAmount, datePaid, checkNumber, customerID, customerName, customerCurrentAddress)
Write down the functional dependency for customerID and driverLicNo.
customerID ®
truck#Assigned ® truckCapacity
driverLicNo ®
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)
Write down the functional dependency for clientID
menuNumberChosen ® menuAppetizer, menuSalad, menuMain, menuDessert)
musicContact ® musicContactPhone, musicType
clientID ®
3) CARSALE (car#, salesperson#, dateSold, commissionRate, discount)
Given that: Date_sold ® discount
salesperson# ® commissionRate
car# ® Date_sold
4) R (a,b,c,d,e,f,g)
Given that: d ® a
e ® f,g
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#(commissionRate, discount)
4)R(a,b,c,d,e,f,g)
R(a,b,c,d,e)
d(a)
e()