edited by
27,858 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

1 votes
1 votes
Please explain clearly i am not getting the concept that how we can conclude BCNF without FD's and by theory i think we can only derive that (sname , city) is candidate key and city --> sname but how bcnf is concluded ?
1 votes
1 votes

In Supplier relation:

sid → sid, sname, city, street
sname, city → sid, sname, city, street
(given)

Primary attributes are: sname, city, sid

 

  • Check 2NF.
    Part of CK non-prime attributes? No. So, 2NF.
     
  • Check 3NF
    Non-prime attribute → Non-prime attribute? No. So, 3NF.
     
  • Check BCNF
    For every X → Y, is X a Superkey? Yes. So, BCNF.

Option A.

 

PS: We can't directly check BCNF. Start with 2NF (Assuming 1NF is always satisfied)

Answer:

Related questions

30 votes
30 votes
2 answers
3