Q2)

1. Consider the following SQL query and related relations

SELECT StudId, Lname

FROM Student s, Grades g, Course c

WHERE s.StudId=g.StudId AND g.CrsId=c.CrsId

AND Grade=A + AND Dept =Comp AND Major= Math;

Where :

Student({Lname,Fnamem,StudId,Major})

Grades({StudId , CrsId ,Grade})

Course({Cname, CrsId ,Points,Dept})

1. Translate the above SQL query to a relational algebra expression.
2. Draw the Query tree for above relational algebra expression in part (a).

Q3)

Query statements are used to retrieve data from the database. Sometimes queries require query tuning because they suffer from performance issues in workspace. Apply query tuning on the following SQL queries to provide efficient performance:

1. SELECT SID, FNAME, AGE, GPA FROM STUDENT WHERE GPA != 0;
2. SELECT SID, FNAME, AGE, GPA FROM STUDENT WHERE GPA + 0.5 < 2.5;
3. SELECT SID, FNAME, AGE, GPA FROM STUDENT WHERE GPA > 3 OR AGE > 25;

Q4)

Discuss the choice of primary index while you are creating an index on a relation.