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

why did they use tuple instead of attribute ? i marked it is false thinking that its asking whether we can refer a tuple using foriegn key of a table which is false because Foreign key can be $NULL$ as well.

---

"Can" means "possible" isn't?!

If yes it is possible that foreign key can be atmost 1 so S1 should be true statement

Because there didn't mention foreign key must be 1 then S1 will be false.

Please let me know where I am missing ??

---

D. Both S1 and S2 are false is the correct choice.

• S1: A relation scheme can have at most one foreign key.
  • False: A single table (relation) can have multiple foreign keys linking to several different tables. For example, an Enrollment table might contain both a student_id (referencing a Students table) and a course_id (referencing a Courses table).
• S2: A foreign key in a relation scheme R cannot be used to refer to tuples of R.
  • False: A table can absolutely reference itself through a self-referencing (recursive) foreign key. For example, an Employees table often contains a manager_id column that acts as a foreign key pointing back to the employee_id column within that exact same table.

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

@Pranavpurkar,

Foreign key can be Null in self referencing tables too.

---

One good example where a foreign key is used to identify tuples is this:

In the case of a defining relationship for a weak entity, if the relationship is one-to-one, then the primary key of the main entity can be used as a foreign key in the dependent entity, which alone will be sufficient to identify tuples uniquely since it is one-to-one.

---

In relational algaebra , referencing is done tuple by tuple . You can reference the whole attribute but atomically it has to be done 1 tuple ata time . So tuples and attributes used here, synonymously.

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.

Answer 5

Both S1 and S2 are false.

S1 is false because you can have more than 1 FK in one relation for example if u see the case of many to many realtion with both the entities are paritally participating in that case you form one extra table for relationship in which you have PK of both the entity relatiuon as FK so u are having more than one FK in one relation which is possible.

S2 is false because you can have self refrential FK also for example take case of self refrential relationship with both partial participation you will get 1 FK in the table itself refrening the PK of the table.

Answer 6

Option D is correct:

S1 is false we can have more than one foreign key as per requirement
S2 is false  foregin key act upon columns so we can use it to refer to a tuple.