Referential Integrity

Question

Consider the join of a relation R with a relation S. If R has 100 tuples and S has 9 tuples then the maximum and minimum sizes of the join respectively under referential integrity constraint:-

(A) 9 and 0

(B) 9 and 9

(C) 9+100 and 0

(D) 9*100 and 9

Comments

is it B) 9 and 9?

---

If it is M:N then maximum 900 and for minimum foreign key can contain all NULL.I will go with c

---

@Ashwin , yes answer is given as B..Please explain..

---

Here In case of referntial integrity, One will be prime key from R, and one will be forign key from S.

Prime key attribute has all different values.

Now in case of maximum, Lets say S has all different values. Still all will be from R only. Hence join will be only on 9 unique tuples.

Now in case of minimum, Lets say S has all same values. Hence join will be done only between 1 value of R and all 9 values of S.

Hence anyhow tuples will be 9.

---

@Ashwin , got it,, Thanks :)

---

@Ashwin ,What if foreign key reference is NULL? Referential integrity says that F.Key can be NULL or refer to primary key as per my knowledge.

---

From wiki:-

For referential integrity to hold in a relational database, any column in a base table that is declared a foreign key can contain either a null value, or only values from a parent table's primary key or a candidate key.^[

---

@Rahul consider only one attribute in table S. Then all NULL won't work.

---

I think having a key is must for a DBMS table.If you have one attribute and that too only foreign key.Then there is no key on the table.Then is it a valid scenario?

---

Yes it is. Then that table is totally dependent on its parent table. And that entity will be known as weak entity.

And then that weak entity will form a key using parent's PK with its own foreign key attribute.

---

One will be prime key from R, and one will be forign key from S.
 

But if you consider it other way...prime key from S and foreign key from R. 

Then We will get 100 tuples.

Any specific rules to consider while calculating maximum scenario

Answer 1

1. To get the maximum 

assume Referencing Table(table having foreign key, referencing an attribute in relation S) as  R (with 100 tuples)

assume Referenced Table as S(with 9 tuples)

then maximum will be 100 for join on referential integrity constrainst

this will happen when the there is no null values in R for referencing attribute(then every the tuple will be joined with the corresponding matching tuple in the relation S).

hence maximum is 100

2. we get the minimum number of  tuples on join  when for all the tuples referencing attribute is NULL in 

referencing relation. 

then there will be no tuples on  join 

hence minimum will be 0

correct me if am wrong.