Applied Course | Mock GATE | Test 1 | Question: 46

Question

Consider the relation Parts $\text{(Partno, SupplierID, Partname, Model)}$, with $\text{{Partno, SupplierID}}$ as the primary key. Suppose the following functional dependencies are defined on Parts: $F = \{ \{\text{Partno, SupplierID}\} \rightarrow \{ \text{Partname, Model} \}, \text{Partname} \rightarrow \text{SupplierID } \}$. If all the attributes of the above relation take only atomic values, which of the following statements is true?

• A. Relation Parts is in $1NF$ but not in $2NF$
• B. Relation Parts is in $2NF$ but not in $3NF$
• C. Relation Parts is in $3NF$ but not in $BCNF$
• D. Relation Parts is in $BCNF$ but not in $4NF$

Answer 1

Given that the attributes of the relation Parts take only atomic values, therefore, the relation is in $1NF$.
From the functional dependency set $F$, there are neither partial dependencies nor transitive dependencies.
Therefore, the relation is in $2NF$ as well as in $3NF$. Now consider the dependency $\text{Partname} \rightarrow \text{SupplierID}$,
which is a non-trivial functional dependency which holds in Parts. Now, from the definition of $BCNF$, Partname should be a
super key in Parts, which is not the case. Therefore, Parts in not in $BCNF$.