a) Using relational algebra laws, perform heuristic optimization on the query tr

Question

a) Using relational algebra laws, perform heuristic optimization on the query trees above. Explain the optimizations (laws) that you apply.

b) Using the database statistics given below, create the best physical query tree for each query above. Note that you may start from your optimized logical query tree in Question 2a, but you do not necessarily have to use the exact logical query tree produced by heuristic optimization if cost-based analysis indicates differently. Explain your answer using performance calculations.

Explanation / Answer

Create a Relation/Table in SQL

CREATE TABLE Student

(sid CHAR(4),

name VARCHAR(20),

major CHAR(10),

age INTEGER,

PRIMARY KEY (sid)

);

CREATE TABLE Class

(cname VARCHAR(20),

meet_at CHAR(10),

room INTEGER,

facultyId CHAR(4)

);

CREATE TABLE Enrolled

(studentId CHAR(4),

classname VARCHAR(20),

);

CREATE TABLE Faculty

(fId CHAR(4),

fname VARCHAR(20),

deptid VARCHAR(10)

PRIMARY KEY (fId)

);

eg:-

CREATE INDEX primaryindex ON Class(cname);

CREATE INDEX secondaryindex ON Class(facultyID);

CREATE INDEX primaryindex ON Enrolled(studentId,className);

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.