and this is question 1

1. (10 points) Write a relational algebra plan for the following SQL query: SELECT a.p FROM person_living AS a, male AS b WHERE a.p b.name AND NOT EXISTS (SELECT* FROM parent child AS c, female AS d WHERE c.p1-d. name AND c.p2-.) You can either draw the query plan as a tree like shown in problem 1, or write it as a linear equation in a text file. If you prefer a text file, to make precedence clear, we ask you to break down your query plan by using at most one operator on each line. For example, given the query in question, you could write it as: T1 (x,p1,p2)person_living(x) Join[x-p1] parent_child(p1,p2) T2(p3,p4)-Rename lp3,p4] parent child(p1,p2) T3 (x,p1,p2 , p3, p4 ) -T1(x,p1,p2) Join [p2-p3] T2(p3, p4 ) T4(p1,p2,y) -GroupBy [p1,p2,count()-yl T3(x, p1,p2,p3,p4) T5 (p1,y) Projectlpl, yl (T4) T6 (p1,z)GroupBy [p1,max(y)->z] T5(p1,y where T1, T2, etc are temporary relations. Note that each line has at most one relational operator. You do not need to use the Greek symbols if you prefer. You also don't need to distinguish among the different flavors of join (just make sure that you write out the full join predicate). We will be grading this part of the homework manually so don't worry too much about the exact syntax of the relation algebra function names etc, as long as we can tell what is going on. 1. (10 points) Write a relational algebra plan for the following SQL query: SELECT a.p FROM person_living AS a, male AS b WHERE a.p b.name AND NOT EXISTS (SELECT* FROM parent child AS c, female AS d WHERE c.p1-d. name AND c.p2-.) You can either draw the query plan as a tree like shown in problem 1, or write it as a linear equation in a text file. If you prefer a text file, to make precedence clear, we ask you to break down your query plan by using at most one operator on each line. For example, given the query in question, you could write it as: T1 (x,p1,p2)person_living(x) Join[x-p1] parent_child(p1,p2) T2(p3,p4)-Rename lp3,p4] parent child(p1,p2) T3 (x,p1,p2 , p3, p4 ) -T1(x,p1,p2) Join [p2-p3] T2(p3, p4 ) T4(p1,p2,y) -GroupBy [p1,p2,count()-yl T3(x, p1,p2,p3,p4) T5 (p1,y) Projectlpl, yl (T4) T6 (p1,z)GroupBy [p1,max(y)->z] T5(p1,y where T1, T2, etc are temporary relations. Note that each line has at most one relational operator. You do not need to use the Greek symbols if you prefer. You also don't need to distinguish among the different flavors of join (just make sure that you write out the full join predicate). We will be grading this part of the homework manually so don't worry too much about the exact syntax of the relation algebra function names etc, as long as we can tell what is going on