GATE CS 2009 Q56 — Normal Forms

To determine the highest normal form of the given relational schema, we analyze each relation individually. Recall that a relation is in Boyce-Codd Normal Form (BCNF) if, for every non-trivial functional dependency $X \rightarrow Y$, the determinant $X$ is a superkey.

1. Relation: $\text{Suppliers}(\underline{\text{sid}}, \text{sname}, \text{city}, \text{street})$

• Primary Key: $\text{sid}$
• Candidate Keys:
  • $\{\text{sid}\}$ (Primary Key)
  • $\{\text{sname}, \text{city}\}$ (Given as a candidate key)
• Functional Dependencies (FDs): Since no other FDs are implied other than those by the primary and candidate keys, the non-trivial FDs are:
  • $\text{sid} \rightarrow \text{sname}, \text{city}, \text{street}$
  • $(\text{sname}, \text{city}) \rightarrow \text{sid}, \text{street}$

For both FDs, the left-hand side (determinant) is a candidate key (and hence a superkey). Thus, the $\text{Suppliers}$ relation is in BCNF.

2. Relation: $\text{Parts}(\underline{\text{pid}}, \text{pname}, \text{color})$

• Candidate Key: $\{\text{pid}\}$
• Functional Dependencies:
  • $\text{pid} \rightarrow \text{pname}, \text{color}$

The determinant $\text{pid}$ is a superkey. Thus, the $\text{Parts}$ relation is in BCNF.

3. Relation: $\text{Catalog}(\underline{\text{sid}, \text{pid}}, \text{cost})$

• Candidate Key: $\{\text{sid}, \text{pid}\}$
• Functional Dependencies:
  • $(\text{sid}, \text{pid}) \rightarrow \text{cost}$

The determinant $\{\text{sid}, \text{pid}\}$ is a superkey. Thus, the $\text{Catalog}$ relation is in BCNF.

Conclusion

Since all three relations in the schema are in BCNF, the entire schema is in BCNF.