Question

Consider the following relations: Students(snum: string, sname:string, major:string, level:string, age:int); Enrolled(...


Consider the following relations:

Students(snum: string, sname:string, major:string, level:string, age:int);

Enrolled(snum: string, cname: string)

And Consider the following query

SELCT s.sname, s.age

FROM Student s, Enrolled e

WHERE s.snum = e.snum and s.level = 'JR' and e.cname = 'Database Management';

Given the following info:

T(student) = 10000 records

t(Enrolled) = 20000 record

D(Student) = 300 pages

B(enrolled) = 200 pages

V(Student, level) = 5 // (FR, SP, SR, GRAD)

V(Enrolled, cname) = 500 //there are 500 different courses)

a. please draw a query plan for the above query: join two tables first, then apply selection finaally apply projection.

 

 

 

b. Please modify the query plan in (a) to apply push selection rule.

Answer

a. SELECT sname FROM Student s

INNER JOIN Enrolled e ON e.snum=s.snum INNER JOIN Class c ON c.name = e.cname INNER JOIN Faculty f ON f.fid = c.fid WHERE s.level = "JR" AND fname = "Jerry

Jones"; 

 

b. SELECT MAX(S.age)

FROM Student S

WHERE (S.major = 'History') OR S.snum IN (SELECT E.snum

FROM Class C, Enrolled E, Faculty F WHERE E.cname = C.name AND C.fid = F.fid

AND F.fname = 'erry Jones'); 

 

c. SELECT level, avg(age) FROM Student WHERE level <> "JR" GROUP BY level;

SELECT F.fname, COUNT(*) AS CourseCount FROM Faculty F, Class C

GROUP BY F.fid, F.fname

WHERE F.fid = C.fid HAVING (C.room = 'R128' ); e. SELECT sname FROM Student WHERE snum NOT IN

(SELECT snum FROM Enrolled);

Recent Questions