PRODUCTS[MatchID, ProductID, ProductType, Description, Price, Alcoholic, VendorI

Question

PRODUCTS[MatchID, ProductID, ProductType, Description, Price, Alcoholic, VendorID, 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 matchID and vendorID should inform the quantity of products sold during the game. Each product has a set price in each match.

1. List all functional dependencies in the PRODUCTS relation

2. Normalise the PRODUCTS relation to BCNF

Explanation / Answer

Answer)

PRODUCTS(MatchID, ProductID, ProductType, Description, Price, Alchoholic, VendorID, VendorName, Quantity, SeniorDiscount)

Based in the description in the question:

The functional dependencies are:

ProductID -> ProductType, Description, Alchoholic, 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

• Browse (All)
• Browse P
• Subjects

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