5,219 views

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?

1. Both $S1$ and $S2$ are true
2. $S1$ is true and $S2$ is false
3. $S1$ is false and $S2$ is true
4. Both $S1$ and $S2$ are false

### 1 comment

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

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.
by

edited

Can someone provide a better explanation for Statement 2? How can self-referencing foreign key refer to tuples? What if the foreign key is NULL? Then how can it refer to a tuple?

Abhrajyoti00

Your point is valid as well but in the question it is clearly neglecting the fact that a foreign key can never be used to refer to tuples of the same relation to prove it wrong we have plenty of examples .

But is there any possibility that this might be true so Yes there is a possibility when the foreign key elements are all distinct and NOT NULL then it CAN be  self referencing.

I hope it helps!

Can you give an example/link of self referencing foreign keys, where it is used to refer to tuples?

Thanks @Pranavpurkar. It cleared my doubt. :)

Reasoning for 2nd 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.

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

### 1 comment

How to refer to tuples of R using foreign key?

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

s2 is False by the definition of FK.

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

### 1 comment

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.