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