GATE2009-56

10.3k views

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 $3NF$ but not in $\text{BCNF}$
3. The schema is in $2NF$ but not in $3NF$
4. The schema is not in $2NF$

edited
1
Option A in BCNF
2
How (street,city) is key because two suppliers can live in the same city and same [email protected] [email protected] mam
0

" Each street within a city also has a unique name and so (street, city) is also a candidate key"

In the Arjun sir answer it is mentioned @ srestha mam

5
yes, I was wrong

here dependencies are

$sid\rightarrow sid,same,city,street$

$same,city\rightarrow sid,same,city,street$

$street,city\rightarrow sid,same,city,street$

all left side are candidate key
1

My doubt it....How (street,city) is key because two suppliers can live in the same city and same street then (street,city)--->sname fails...please clarify this @srestha mam!

5

"No other functional dependencies are implied other then those implied by primary key and candidate keys"

Isn't this line enough to say that the schema is BCNF!

If not then what does this line mean?

2

Yes the above line is enough.

All the LHS should be a key for the relation to be in BCNF.

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

1. $(sname, city) \to street$
2. sid → street
3. (sname, city) → sid
4. sid → sname
5. sid → 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
3
sir,

from above explanaion (Street,city) -> sname also becuase (street,city ) is unique.

can't it be candidate key??

also if its a candidate key then no problem its in BCNF,

otherwise its not in bcnf but in 3NF

piyush
3
Yes, that is a candidate key as it is unique. I corrected the last part.
0
Sir is it not having partial dependency? Because (sname,city) is C.K and (city,street) is also a C.K. City is present in both the dependency!. So option d should be correct?

Please correct me if am wrong.
1
how (STREET,CITY) is candidate key??
0
"each supplier and each street within a city has unique name" because of this statement.
0
each supplier and each street within a city has unique name

It means CITY -> Street,Supplier.Am i right ???

10

each supplier and each street within a city has unique name

This means when supplier name cannot repeat within a city and same for street.

CITY -> Street,Supplier is wrong as it basically says all street and supplier must be same for a city.

0
sir this line "This means when supplier name cannot repeat within a city and same for street." is not clear .please explain..
2
Sir what i understood from the statement is that within a city streets and suppliers are unique. So the combination of city,street and city,supplier should be unique.
4
(street,city) not a candidate key.. two suppliers can live on the same street and this does not violate the condition.
0
because each street is unique within a city and hence combination of street and city will be unique in ech tuple of the relation making it a candidate key.
0
Can two $sid$ point to same $city$? is that allowed, or will it break the super key property?
0
" each supplier and each street within a city has unique name "

means supplier, street and city are unique combination of 3 elements.

And unique means , it could represent a key by it

So, any two combination of them will be unique candidate key.
0
How can we take out the non trivial FD?
0
But two different suppliers can live on same street and city combination in that case street and city combination will not be key right
0

Each street within a city also has a unique name and so (street, city) is also a candidate key.

but according to question

"each supplier and each street within a city has unique name"
this statement can also mean that given a street name within a city can also have more than 1 suppliers(different name).

0
@Arjun
Sir if (street, city) is candidate key. Then by same argument (sid, city) is also a key[superkey because sid is a candidate key]. Therefore (sid, city) -> sname and (street, city) -> sid are true.

But how can we say that (sname, city) ->sid
0
this question is concern about only Suppliers schema???
0

yes, only supplier table.

chk this line

Assume that, in the suppliers relation above,

0

each supplier and each street within a city has unique name

gives you following FDs in addition to those implied:

1. (sname,city)=>sid
2. (street,city)=>sid

Hence, making both of them {(sname,city);(street,city)} Candidate keys.

i  think ans is d

because street and sname functionally depend on city (acc to question)
1 flag:
✌ Edit necessary (Gupta731 “wrong answer”)
0
D can not be possible since that combination is given as candidate key .so it satisfy bcnf
2

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

@indrajeet
I guess here you think that,  City->name , City->street
It is wrong.

It means
name,City -> sid
street,City -> sid
So BCNF.

5

No other functional dependencies are implied other than those implied by primary and candidate keys.

dont this directly says that it is in BCNF.

0
@manav ,exactly i also think the same.

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.

1 vote
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 ?
0
What i understood is in a city the street names are unique. But in a street there can be multiple suppliers residing. And candidate key basically means it should identify a row uniquely. But (street, city) returns multiple sid's. How can it be candidate key?

Please correct me if i am wrong.
0

I still do not understand how (street,city) can uniquely identify a supplier.
1 vote
"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.

1 vote

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)

Related questions

1
13k views
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})$ ... the names of all suppliers who have supplied only non-blue part. Find the names of all suppliers who have not supplied only blue parts.
The following key values are inserted into a $B+$ - tree in which order of the internal nodes is $3$, and that of the leaf nodes is $2$, in the sequence given below. The order of internal nodes is the maximum number of tree pointers in each node, and the order of leaf nodes is ... $2$, $1$ The maximum number of times leaf nodes would get split up as a result of these insertions is $2$ $3$ $4$ $5$
Consider two transactions $T_1$ and $T_2$, and four schedules $S_1, S_2, S_3, S_4$, of $T_1$ and $T_2$ as given below: $T_1: R_1[x]W_1[x]W_1[y]$ $T_2: R_2[x]R_2[y]W_2[y]$ $S_1: R_1[x]R_2[x]R_2[y] W_1[x] W_1[y] W_2[y]$ ... $S_1 \text{ and } S_2$ $S_2 \text{ and } S_3$ $S_3$ only $S_4$ only
Let $R$ and $S$ be relational schemes such that $R=\{a,b,c\}$ and $S=\{c\}.$ Now consider the following queries on the database: $\pi_{R-S}(r) - \pi_{R-S} \left (\pi_{R-S} (r) \times s - \pi_{R-S,S}(r)\right )$ ... Select R.a,R.b From R,S Where R.c = S.c Which of the above queries are equivalent? $1$ and $2$ $1$ and $3$ $2$ and $4$ $3$ and $4$