SELF DOUBT GATE2009-56

Question

I AM NOT GETTING THIS QUESTION CONCEPT . PLEASE HELP

Consider the following relational schema:

Suppliers(sid:integer−−−−−−−−, sname:string, city:string, street:string)

Parts(pid:integer−−−−−−−−−, pname:string, color:string)

Catalog(sid:integer, pid:integer−−−−−−−−−−−−−−−−−−,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 TRUE about the above schema?

1. The schema is in BCNF
2. The schema is in 3NF but not in BCNF
3. The schema is in 2NF but not in 3NF
4. The schema is not in 2NF

Comments

sir, explained it nicely, didn't you get it?

what's your doubt on it?

---

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

this statement meaning i am not getting this thing

even from sir answer not getting it

---

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

it means,

1) with in a city, supplier name is unique

city	supplier_name
A	Ramesh
A	Deepanshu
B	Ramesh
B	Deepanshu
B	Ramesh --------> This can't possible due to city B have already Ramesh

2) with in a city, street name is unique

---

(sname, city) forms a candidate key

 it means, with these pair of attributes, we can uniquely identify the row.

 

SID	CITY	SUPP_NAME	STREET_NAME
1	A	RAM	MG STREET
2	B	SHYAM	NEHRU NAGAR
3	B	BHEEM	MG STREET
4	B	RAM	YV STREET
5	C	RAM	YV STREET
6	C	SHYAM	HANUMAN NAGAR
7	D	BHEEM	MG STREET

in the above table, sid is primary key

city can't be a candidate key, supp_name can't be a candidate key, STREET_NAME  can't be a candidate key,

with in the city, SUPPLIER is unique

with in the city, Street_NAME is unique

(city,sname) forming the candidate key

---

---

Most of the people, have doubt that how (city,Street) is a candidate key? ( yes it should be candidate key )

their argument is " With in a street, there can be more than one supplier_name repeat with in the city"

is that argument is valid? NOOOO

why?

if it is valid we should have a two rows in table like

8) jeevan, temple_road

9) jeevan,temple_road 

what is the City corresponds to 8 ----> let it as E

what is the city corresponds to 9 ----> it should be E as per your argument

But we know that E,jeevan should be uniquely find a row in the table, therefore their argumnet is invalid.

---

VERY NICE EXPLANATION BRO

THANKU @ Shaik Masthan

---

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

arent we supposed to directly infer from these lines that the relations gonna be BCNF?

---

@Shaik Masthan,

Are you considering jeevan as the sname and temple-road as street?

If yes, then pls explain why are you considering this?

The argument put by people- " With in a street, there can be more than one supplier_name repeat with in the city" seems to be valid to me.

I think if "jeevan" is the sname in your case, then you have taken a wrong example.

consider this->

sname: "jeevan", street: "temple-road", city: "E"

sname: "ravi", street: "temple-road", city: "E"

Now, how would you explain this? Pls help me clearing this concept. Either i am not able to get what you are saying or you have understood wrongly.

Pls share your views.

---

  given that, with in a city, street name is unique

what it means ?

with in a city, you can't have same street name more than once !

 

sname: "jeevan", street: "temple-road", city: "E"

sname: "ravi", street: "temple-road", city: "E"

these two tuples have same city and same street ===> These tuples can't allowed in the DATABASE

---

within a city, you can't have same street name more than once !

With this statement, it cannot be concluded that two tuples with same city and same street are disallowed in database.