Download Solution: Click to Download Solution
Solution File Name: HomeWk5-Normalization-recheck_2CCC1BBB.docx
Unzip Password: prestobear.com
1.Problem 1(20 pts)
This exercise asks you to convert business statements into functional dependencies.
Product_Repair(serialNumber, type, versionNum, capacity, repairInvoiceID, repairDate, partNum, partType, partDescription)
Write a functional dependency for each of the following statements:
• serialNumber of a product determines its type and versionNum
• product type and version determines its capacity
• a product may be repaired many times, but never more than once on a given date. For each repair, there is a repair invoice. One invoice only records the repair for one product. A product may contain many parts that needed to be repaired on one repair invoice.
• for a component part, its type and description are unique for a partNum
2. Problem 2 (40 pts)
Consider the following relation
patientDrug(patientID, patientName, drugNum, drugName, Dosage, MethodOfAdministration, unitPerDay, startDate, endDate)
Given the functional dependencies that you have identified (see the practice exercise 9 key if yours is incorrect) , please
• identify the key of the table,
• then explain what normal form this relation is, and why
• Normalize it to 3NF. Please do not jump directly to the final decomposition. You need to show what 2NF this relations can be decomposed to (if it is not in 2NF), and then ifurther decomposition is needed to make all resultant relations 3NF relations.
3. Problem 4(40 pts)
carRental(customerID, customerName, customerAddress,carNum, carModel, carMake, carYear, carType, dailyRate, weeklyRate, monthlyRate, rentalDate, returnDate)
And the following functional dependecies:
customerID–> (customerName, customerAddress)
carNum –>(carModel, carMake, carYear, carType)
carType –>(dailyRate, weeklyRate, monthlyRate)
(customerID, carNum, rentDate)–> returnDate
o Find the key of the table. Explain why you chose it.
o what normal form is the relation in? Why?
o normalize the relation to 3NF. State the reason behind each decomposition. Again Do not jump directly to the final decomposition of R. You need to show the process of decomposing R into each appropriate normal form and explain why such decomposition is needed. For instance, if R is not in 2NF, decompose it into 2NF. If the resultant relations are not in 3NF, explain why and decompose further until 3NF
