Question

Part 2: Relational Algebra Given the relations M and N: M A B D X Y B David Matt 14 4 Henry 5 Helen 2 Mary Tom 8 Mary 2 6 Jane Susan 61 Paul 61 Tom Lisa 2 John 2 5 Lisa Mary 5 8 Mark John 24 9 13 Tom 42 Paul 42 Henry 8 1 Amy 2 5 2 Dan Matt For each relational algebra statement a. calculate the following output tables and be careful in labeling the attributes b. write the corresponding efficient (be careful!) SQL code and using subqueries as appropriate. (M N) 1. lA+B as E, C, X as G O X>B 2. Yc, D, sum(A+M.B+X) as Total (M XM.B=N.x N) 3. TR.X,R.Y,N.B (N D N.x R.X and N.Y R.Y Pr (Ox>5 (N) - TnB as X,D as Y,A as B OA>B (M)))

Answer 1

If you have any doubts, please give me comment...

1)

a)

M $\Join$ N

A	B	C	D	X	Y
8	2	Mary	Helen	8	Lisa
8	2	Mary	Helen	42	Henry
2	5	Lisa	Mary	3	Matt
5	2	Don	Matt	8	Lisa
5	2	Don	Matt	42	Henry

TT A+B as E, C, X as G O x»B (M WN)

Result:

E	C	X
10	Mary	8
10	Mary	42
7	Don	8
7	Don	42

b)

SELECT A+B AS E, C, X

FROM M SELF JOIN N

WHERE X>B;

2)

a)

M $\Join$_{M.B = N.X} N

A	B	C	D	X	Y	B
8	2	Mary	Helen	2	Tom	8
61	3	Tom	Paul	3	Matt	5
2	5	Lisa	Mary	5	John	8
5	2	Dan	Matt	2	Tom	8

Result:

C	D	Total
Mary	Helen	12
Tom	Paul	67
Lisa	Mary	12
Dan	Matt	9

b)

SELECT C, D, SUM(A+M.B+X) AS Total

FROM M JOIN N ON M.B = N.X

GROUP BY C, D;

3)

a)

$\sigma$_X>5(N)

X	Y	B
8	Mary	61
8	Lisa	2
13	Tom	41
42	Henry	2

TTB as X,D as Y,A as B OA> (M)

X	Y	B
4	David	14
2	Helen	8
3	Paul	61
9	John	24
1	Amy	8
2	Mat	5

PR (Ox>5 (N) - TB as X,D as Y,A as B OAB (M)))

X	Y	B
8	Mary	61
8	Lisa	2
13	Tom	41
42	Henry	2

Result:

R.X	R.Y	N.B
8	Mary	61
8	Lisa	2
13	Tom	41
42	Henry	2

2)

SELECT R.X, R.Y, N.B

FROM N JOIN (SELECT * FROM N WHERE X>5 EXCEPT SELECT B AS X, D AS Y, A AS B FROM M) AS R ON N.X = R.X AND N.Y = R.Y;