GATE CSE 2021 Set 2 | Question: 6

Question

Consider the following statements $S1$ and $S2$ about the relational data model:

• $S1$: A relation scheme can have at most one foreign key.
• $S2$: A foreign key in a relation scheme $R$ cannot be used to refer to tuples of $R.$

Which one of the following choices is correct?

• A. Both $S1$ and $S2$ are true
• B. $S1$ is true and $S2$ is false
• C. $S1$ is false and $S2$ is true
• D. Both $S1$ and $S2$ are false

Comments

What if the foreign key is NULL? Can we still be able to refer the tuples?

---

Why they have to call them tuples and create confusion. Why can't they say it as attributes.

Answer 1

Both $S1$ and $S2$ are FALSE.

In a relation scheme multiple foreign attributes can be present referring to primary keys of other relation schemes. A typical example is an $\text{EXAM_RESULTS(sid,eid,marks)}$ scheme where $\text{sid}$ and $\text{eid}$ are foreign keys referring to the primary keys in $\text{STUDENT}$ and $\text{EXAM}$ schemes respectively.

$S2$ is FALSE because a foreign key can refer to the same scheme (self-referencing foreign key). A typical example is an $\text{EMPLOYEE(eid, mid, $\ldots$)}$ scheme where $\text{mid}$ is the Manager ID referring to the primary key $\text{eid}$ of the same scheme.

Comments

Abhrajyoti00

 

You can refer this :

https://pencilprogrammer.com/self-referencing-foreign-key-in-mysql/

I found it useful.

---

Thanks @Pranavpurkar. It cleared my doubt. :)

---

Reasoning for 2^nd statement.

By saying cannot they are doing condition stricter. So we have to find out only one case in which Statement 2 becomes false. So by seeing foreign key, definitely a thought of null value strikes i.e. if foreign key has null value then we can’t refer the tuples of the R but think in this way, if it is self referencing foreign key & referring to a primary key of the table then we can refer tuples of R through primary key via foreign key indirectly because at the end of the day foreign key itself referring from primary key.

Answer 2

Option $(D)$. Both are false. We can refer to the same table and there can be more than one foreign key.

Comments

How to refer to tuples of R using foreign key?

Answer 3

s1 is False since a Relation may have more than one FK.

s2 is False by the definition of FK. 

Answer 4

S1 is false because there is no rule that a relation schema can have atmost one foreign key in minium. It can have more than 1 foreign key as well.

S2 is false because in a relation a foreign key cant identify a tuple uniquely. so they cant be used to refer to a tuple of R

 

answer is D both are false

Comments

S2 is false because in a relation a foreign key cant identify a tuple uniquely. so they cant be used to refer to a tuple of R

Statement $S2$ also says we CANNOT use a foreign key to refer to tuples of relation $R$.

So as per your explanation Statement $S2$ should be true.