CSCE5370 Assignment No. 2 for Ch. 04-1 solution

Description

1. For the following query, expressed in SQL, on the example database above:
1) Simplify WHERE clause.
2) Provide its query result.
SELECT ENO
FROM ASG
WHERE RESP = “Analyst”
AND NOT(PNO=”P2″ OR DUR=12)
AND PNO ≠ “P2”
AND DUR=12

2. For the following query, expressed in SQL, on the example database above.
SELECT ENAME, PNAME
FROM EMP, ASG, PROJ
WHERE DUR > 12 AND EMP.ENO = ASG.ENO AND PROJ.PNO = ASG.PNO

1) Provide a query graph for the above query.
2) Provide a generic query (operator) tree for the above query.

3. Assume that relation PROJ of our example database above is horizontally
fragmented as follows:
PROJ1 = σ PNO≤“P2” (PROJ)
PROJ2 = σ PNO>“P2” (PROJ)
For the following query,
SELECT ENO, PNAME
FROM PROJ, ASG
WHERE PROJ.PNO = ASG.PNO AND PNO = “P4”

1) Provide a generic query (operator) tree for the above query.

2) Provide a reduced query (operator) tree on fragments of PROJ using union,

3) Provide a query (operator) tree using the result of 2) in which the join is
distributed over union.

4) Provide a query (operator) tree using the result of 3) in which the
unnecessary join is eliminated.