Theta join explanation

Question

Given the following question, I'd be glad if someone could help me.

Take the following relational schemes:

R(A, B , C) 

S(D, E, F) 

where A and D attributes are the primary keys.

Assume to have an instance r of R with n tuples and an instance s of S with m tuples.

Moreover, assume to have a referential integrity constraint between C and the primary key of S.

1) How many tuples does the θ-join between s and r contain if the join predicate is C = D? 

2) How many tuples does the θ-join between s and r contain if the join predicate is B = E? 

My answers:

1) n tuples since it all depends on the number of records belonging to the second relation that match the records which are part of the first one

2) zero since there's no common attributes

Is my reasoning correct?

Any help will be highly appreciated...thanks in advance

Answer 1

I think you need to think some more :)

2) "there's no common attributes" is certainly true but irrelevant. We know there is a correspondence between C and D (and therefore are of the same type) because of the referential integrity constraint even though they have difference names.

e.g. here's an example in SQL that satisfies the conditions yet returns a row:

CREATE TABLE s
(
 D INTEGER NOT NULL, 
 E INTEGER NOT NULL, 
 F INTEGER NOT NULL,  
 PRIMARY KEY (D)
);

CREATE TABLE r
(
 A INTEGER NOT NULL, 
 B INTEGER NOT NULL, 
 C INTEGER NOT NULL,  
 PRIMARY KEY (A), 
 FOREIGN KEY (C)
    REFERENCES s (D)
);

INSERT INTO s (D, E, F) VALUES (1, 1, 1);
INSERT INTO r (A, B, C) VALUES (1, 1, 1);
INSERT INTO r (A, B, C) VALUES (2, 2, 1);

SELECT * 
  FROM s JOIN r ON B = E;

p.s. where you used the term 'records' you should actually use the term 'tuples'.