GATE CSE 2009 | Question: 56

Question

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?

• A. The schema is in $\text{BCNF}$
• B. The schema is in $\text{3NF}$ but not in $\text{BCNF}$
• C. The schema is in $\text{2NF}$ but not in $\text{3NF}$
• D. The schema is not in $\text{2NF}$

Comments

bro @Golla Sai Venkatesh every primary key is a ck which is superkey and minimal too . and (sname, city) they gave as ck we are not explicitly assuming this thing from our side.

---

@satvik_t The first statement says that in the schema, there are only those FDs whose LHS is a SK.

And as for your second doubt, no city-->street and city-->name are not valid fds. They are violating the concept of FDs.

City	Street
Mumbai	A
Mumbai	B

 

Now, according to the question yes Street names are unique within a city. But look at this instance:

Using Mumbai can you determine street? Like there are 2 different street values for Mumbai which is violation of FD. If there are two tuples with same A values, then their corresponding B values should be same. If their corresponding B values are different, then the FD will be invalid.

 

Same goes for City-->Sname too.

 

---

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

each supplier, within a city - unique name that means $(sname, city)$ is a candidate key.

each street, within a city - unique name that means $(street, city)$ is also a candidate key.
 

Answer 1

supp,city ->sname
street,city ->sname

since (sname,city) forms ck therefore above both lhs are sk
all fds satisfies condition of BCNF

Answer 2

  Which means LHS is primary key or candidate key therefore it is in BCNF

Answer 3

We have three relations:

1. Suppliers(sid:integer, sname:string, city:string, street:string)

2. Parts(pid:integer, pname:string, color:string)

3. Catalog(sid:integer, pid:integer, cost:real)

this GATE question doesn’t define FDs directly, which can be confusing.

Assumptions Given (Read this VERY carefully):

1. In the Suppliers relation:

   • Each supplier and each street within a city has a unique name.

   • (sname, city) forms a candidate key.

2. No other functional dependencies are implied except those from primary and candidate keys.

FD1: (sname, city) → sid, street

This means: If you know the supplier name and city, you can determine the supplier ID and street.

Candidate Keys:

• (sname, city)

• maybe sid, if it's declared as a primary key

Is this in BCNF?

• A relation is in BCNF if every non-trivial functional dependency X → Y, X is a superkey.

Check:

• (sname, city) → sid, street: X = candidate key 

• sid → sname, city, street: If sid is also a candidate key, Suppliers is in BCNF.

Parts(pid, pname, color)

Assume pid is the primary key, and no other dependencies exist.

Then:

• pid → pname, color

This means the only functional dependency is from the key.

Parts is in BCNF.

Catalog(sid, pid, cost)

Assume:

• Composite key: (sid, pid) is the primary key.

Functional dependency:

• (sid, pid) → cost 

• No other dependencies are given.

Catalog is in BCNF.

You should not assume any of the following unless the question says so:

• city, street → sname 

• sname → sid 

• street → sid 

Unless there’s an explicit hint or rule, these are not valid FDs.

Answer 4

Suppliers Relation

• Primary key: sid

• Candidate key: (sname, city)

The implied functional dependencies are:

• sid → sname, city, street

• (sname, city) → sid, street

In all cases, the determinant is a superkey. Hence, the Suppliers relation is in BCNF.

---

Parts Relation

• Primary key: pid

The functional dependency is:

• pid → pname, color

Since the determinant is a superkey, the Parts relation is in BCNF.

---

Catalog Relation

• Primary key: (sid, pid)

The functional dependency is:

• (sid, pid) → cost

Since the determinant is a superkey, the Catalog relation is in BCNF.