Functional Dependencies & Normalisation Answer the following questions based on
ID: 3750389 • Letter: F
Question
Functional Dependencies & Normalisation Answer the following questions based on the relation & description below: PRODUCTS[MatchID, ProductID, ProductType, Description, Price, Alcoholic, VendorlD, VendorName, Quantity, SeniorDiscount] Products sold at the World Cup have an identification number which identifies their product type and description, senior discount eligibility and alcoholic nature. Vendors in stadiums are identified by an ID which informs their name. The matchlD and vendorlD should inform the quantity of products sold during the game. Each product has a set price in each match. Part 3: List all functional dependencies in the PRODUCTS relation Part 4: Normalise the PRODUCTS relation to BCNFExplanation / Answer
Answer)
PRODUCTS(MatchID, ProductID, ProductType, Description, Price, Alchoholic, VendorID, VendorName, Quantity, SeniorDiscount)
Based on the description in the question:
The functional dependencies are:
ProductID -> ProductType, Description, Alcoholic, SeniorDiscount
VendorID -> VendorName
MatchID, VendorID -> Quantity
ProductID, MatchID -> Price
The FD [ProductID --> ProductType,Description,Alchoholic,SeniorDiscount] violates BCNF, thus table is split into the two below:
R2 (ProductID,ProductType,Description,Alchoholic,SeniorDiscount )
R3 (MatchID,ProductID,Price,VendorID,VendorName,Quantity )
R2 is in BCNF
In R3, FD [MatchID,ProductID --> Price] violates BCNF, thus table is split further;
R4 (MatchID,ProductID,Price )
R5 (MatchID,ProductID,VendorID,VendorName,Quantity )
R4 is in BCNF.
FD [VendorID --> VendorName] violates BCNF , in R5, thus table is split:
R6 (VendorID,VendorName )
R7 (MatchID,ProductID,VendorID,Quantity )
R6 is in BCNF.
R7 is not in BCNF as FD [MatchID,VendorID --> Quantity] violates, thus table is split:
R8 (MatchID,VendorID,Quantity )
R9 (MatchID,ProductID,VendorID )
R8 and R9 are both in BCNF.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.