Theta join explanation

Take the following relational schemes:

-
R(A, B, C)

-
S(D, E, F)

where the attributes A and D are the primary keys, respectively.

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.

Now, I was asked the following questions:



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

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


My answers:

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

-
zero since there's no common attributes

Is my reasoning correct?

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

StackExchange Database Administrators Q#11708, answer score: 4