edited by
27,860 views
61 votes
61 votes

Consider the following relational schema:

$\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$ 

$\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$ 

$\text{Catalog}(\underline {\text{sid:integer, pid:integer}}, \text{cost:real})$

Assume that, in the suppliers relation above, each supplier and each street within a city has unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is $\text{TRUE}$ about the above schema?

  1. The schema is in $\text{BCNF}$
  2. The schema is in $\text{3NF}$ but not in $\text{BCNF}$
  3. The schema is in $\text{2NF}$ but not in $\text{3NF}$
  4. The schema is not in $\text{2NF}$
edited by

6 Answers

Best answer
79 votes
79 votes

The non-trivial $\text{FD}$s are 

  1. (sname, city) $\to$ street
  2. sid $\to$ street
  3. (sname, city) $\to$ sid
  4. sid $\to$ sname
  5. sid $\to$ city

For all these, $\text{LHS}$ is a super key and hence $\text{BCNF}$ condition is satisfied. But we have some more dependencies here:

"each supplier and each street within a city has unique name"

This basically means each supplier in a city has unique name making (sname, city) determine sid and hence making it a candidate key. Each street within a city also has a unique name and so (street, city) is also a candidate key. Even then with all $3$ candidate keys (for Suppliers schema), for any $\text{FD}$, the $\text{LHS}$ is a super key here, and hence the relation schema (for other two relations it is straight forward) is in $\text{BCNF}$.

http://db.grussell.org/section009.html

Correct Answer: $A$

edited by
12 votes
12 votes
"No other functional dependencies are implied other than those implied by primary and candidate keys"

This line is sufficient to indicate that we have superkeys on LHS always which is the necessary condition to be the relation in BCNF.

So, option A is correct.

4 votes
4 votes

From the given data the FDs will be ,

(Sid, Street) $\rightarrow$  Sname

As Sid is a primary key, then
(Sid, Street) will be super key.
Hence, it is in BCNF.

3 votes
3 votes
i  think ans is d

because street and sname functionally depend on city (acc to question)
Answer:

Related questions

30 votes
30 votes
2 answers
3