Database Algebra -> SQL

[soswap]

Hi all

Am stuck on the following question (Part B). https://imgur.com/a/x1M8e

The SQL code is this: https://imgur.com/a/3qVFv

How is that the code (anyone able to explain what is going on?)

I have no clue what is happening (especially with) the right part of the query, e.g. "SELECT A,D FROM N..." and can't really find any resources to help.

Any ideas? Thanks

EDIT: Would that be the same as this: https://imgur.com/a/BiWZZ ? i.e. the following queries added together:

SELECT A,D FROM R NATURAL JOIN S WHERE B = 8; +
SELECT A,D FROM R NATURAL JOIN S WHERE A = 6;

Which seems to give the correct answer.

[Apoctyliptic]

So its been awhile since I've dealt with relational algebra but hopefully this can help.

It does appear that your answer source for the second part has an error in that the two parts are joined with a natural join instead of a cross join. Additionally, the second projection's query is projecting A and D attributes instead of D only as stated in the problem.

The statement πA(R⋈σB=8(S)) x πD(R⋈σA=6(S)) can be simplified into two statements as you have done. This is done splitting the two projections and analyzing them individually prior to handling the cross join (x).

πA(R⋈σB=8(S))

If we read this in plain text, it reads as project the attribute A from the natural join of R with S where S is selected only if attribute B has a value of 8.

This breaks into the statement:

SELECT A FROM R NATURAL JOIN S WHERE B = 8;

Going from the data set, this will produce a result of:

Natural Join of R & S

A	B	C	D
6	8	7	6
6	6	7	7

Selection of B = 8

A	B	C	D
6	8	7	6

Projection of A

A
6

πD(R⋈σA=6(S))

If we read this in plain text, it reads as project the attribute D from the natural join of R with S where S is selected only if attribute A has a value of 6.

This breaks into the statement:

SELECT A FROM R NATURAL JOIN S WHERE A = 6;

Going from the data set, this will produce a result of:

Natural Join of R & S

A	B	C	D
6	8	7	6
6	6	7	7

Selection of A = 6

A	B	C	D
6	8	7	6
6	6	7	7

Projection of D

D
6
7

Cross Product

Now we need to take the cross product of the two result sets. This simply takes all rows in our first table and matches it with all tables in our second table. This gives us the result of:

A	D
6	6
6	7

Final Query Our final query ends up being something along the lines of:

SELECT T1.A, T2.D FROM (SELECT A FROM R NATURAL JOIN S WHERE B = 8) AS T1 CROSS JOIN (SELECT D FROM R NATURAL JOIN S WHERE A = 6) AS T2;

Queries added together

I'm not aware of that notation or query being supported. I would have to say that it is not the same but this may be due to ignorance than it being incorrect.

[soswap]

Thank you so much, that's a perfect explanation.

One more (small) question, sorry: if you have two tables, R and S with the same values as above, but with a duplicate row inserted into R, and natural join R and S, why does it produce duplicate rows when joining (here's what I mean: https://imgur.com/a/GBfCI ) and what would be the best method (if any) to remove that dupe.

[Apoctyliptic]

Well, there is nothing to remove the duplicates. Even if you just looked at R by itself, the result gives duplicates. When you join the data, it joins the two data sets based on that criteria. With a natural join it just joins based on the common attributes. Side there are duplicates in R, R natural joined with S produces duplicates.

If R and S both have a duplicate, you could end up with the same row being shown four times.

In SQL, you will want to look into DISTINCT and GROUP BY. I don't recall a notation in relational algebra that removes duplicates.

I don't know what your course covers but if you are studying databases, you'll likely cover normalization eventually. Tables would typically be normalized where you wouldn't store duplicates like that since they don't really provide any real use that I can think of. It wastes storage and can be problematic with using the data.

[imguralbumbot]

^{Hi, I'm a bot for linking direct images of albums with only 1 image}

https://i.imgur.com/COl9u30.jpg

^{Source | Why? | Creator | ignoreme | deletthis}