CODEWITHSUNDEEP
sqlmathrelational-algebra
hellojoshhhy
hellojoshhhy

Reputation: 958

Database- Relational Algebra natural joins 2 tables consist 2 same column names

natural joins 2 relations

I have read through an example of Natural Join involving 2 relations that have 2 identical columns name. I understand those with 1 identical columns name in 2 tables, but really no idea about for this case. Could anyone explain to me how to get that output (not in Sql command)?

Thanks!

Upvotes: 2

Views: 5026

Answers (2)

philipxy
philipxy

Reputation: 15148

Every tuple is a set with a value for every attribute. When two tuples agree on all the values of their common attributes then there's a tuple that is their set union. Eg r's {A α B 1 C α D a} U s's {B 1 D a E α} = {A α B 1 C α D a E α}. Otherwise there is no tuple that is their set union. Eg for r's {A α B 1 C α D a} and s's {B 3 D a E β}.

The natural join of two relations is the set of tuples that are the set union of a tuple from each. We calculate it by looking at every pair of a tuple from each and if there is a tuple that is their set union then it is in the result.

So r ⋈ s is the set of tuples that are the set union of a tuple from r and a tuple from s. We calculate it by looking at every pair of a tuple from r a tuple from s and if there is a tuple that is their set union then it is in the result.

First, r's {A α B 1 C α D a} and s's {B 1 D a E α} agree on the values of their common attributes. So their set union {A α B 1 C α D a E α} is in r ⋈ s.

Next, r's {A α B 1 C α D a} and s's {B 3 D a E β} have different values for their common attributes. So there is no tuple that is their set union. So that pair of tuples doesn't have a set union in r ⋈ s.

Continue for every pair of tuples from r & s.

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

The common column are B and D, so rows from both sides are matched if they have the same B and D values.
The common (B,D) values in this example are (1,a) and (2,b).
2 rows from r' with (1,a) are matched to 2 rows from s', creating 4 combinations.
1 row from r' with (2,b) is matched to 1 row from s', creating a single combination.
Therefore, 5 rows in the result set.

Upvotes: 6

Related Questions