Q1)

Consider the query:

SELECT E.Lname, E.Bdate, D.Mgr_ssn

FROM EMPLOYEE AS E, DEPARTMENT AS D

WHERE E.Salary >= 30000 AND D.Dname = Administration

AND E.Dno = D.Dnumber;

1. Apply the query on two different methods given below and show the intermediate relations.

Method 1: Apply Join first and then Selection according to given conditions

Method 2: Apply Selection first and then Join according to given conditions

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
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.

EMPLOYEE Frame Minit John B Franklin T Alicia Jennifer IS Ramesh K Joyce A Ahmad V James | E Lname Ssn Bdate Bdate Address Sex Salary Super_ssn Dno Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 8886655555 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TXM 38000 333445555 5 English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 3334455555 Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 DEPARTMENT Dnumber Dname Research Administration Headquarters Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 4 1 DEPT_LOCATIONS Dnumber Dlocation Houston Stafford - 5 Bellaire Sugarland Houston