home / study / engineering / computer science / computer science questions and answers / subject area: database systems language: sql our employer wants to test your knowledge of functional...

Your question has been answered

Let us know if you got a helpful answer. Rate this answer

Question: Subject Area: Database Systems Language: SQL our employer wants to test your knowledge of functio...

Subject Area: Database Systems

Language: SQL

our employer wants to test your knowledge of functional dependency: 3NF vs. BCNF. The employer has asked you to normalize the relation ROBOT_WIDGET into 3NF as well as in BCNF, given the set of functional dependencies listed below.

ROBOT_WIDGET(bot_number, manufacturer, type, batch, max_quantity, retailer)

FD1: {bot_number, manufacturer} {type, batch, max_quantity, retailer}

FD2: {type} {manufacturer}

FD3: {manufacturer, batch} {type} (Assume different manufacturers may happen to use the same batch number.)

FD4: {type} {max_quantity} (Note: we do not need manufacturer as part of the determinant, because it is functionally dependent on type.)

Your employer needs you to draw the resulting relational schema and show the functional dependencies, in order to make his dreams of the potential of the relation ROBOT_WIDGET a reality.

Show the foreign keys connecting relations, using dotted arrows.

Consider only lossless decomposition in the normalization.

Discuss the resulting schema in terms of whether it preserves all functional dependencies and whether it eliminates the risk of update anomalies.