ibps-so 2016 · Question 176 of 394

The vendor delivered the empanelment data of Chandrabhaga Urban Bank as the single relation AGENT_PRODUCT shown in Figure 3 and instanced below, in which the primary key is the pair (agent_id, product_code). The agent's mobile number depends upon the agent alone and is repeated in every row for that agent; the product name depends upon the product alone and is repeated in every row for that product; only the commission rate depends upon both together. agent_id product_code agent_mobile product_name commission_rate AG-104 P-11 9820011223 Recurring deposit 0.75 AG-104 P-24 9820011223 Gold loan 1.10 AG-217 P-11 9370044556 Recurring deposit 0.90 AG-217 P-39 9370044556 Micro insurance 2.25 AG-350 P-24 7719088990 Gold loan 1.10 The highest normal form that AGENT_PRODUCT satis es as it stands, and the defect that keeps it from the next, are respectively:

1. ASecond normal form; the only remaining defect is the transitive dependency of product_name upon commission_rate
2. BSecond normal form; the only remaining defect is that commission_rate depends upon the whole key rather than upon a part of it
3. CThird normal form; the only remaining defect is that the determinant agent_id is not a superkey, so that BCNF alone is wanting
4. DBCNF, the highest of the ordinary forms; the repetition visible in the instance is a consequence of the data and not of the design
5. EFirst normal form only; agent_mobile and product_name are each functionally dependent upon a proper subset of the composite key, which is a partial dependency and a violation of second normal formCorrect