a. Suppose in relation schema R(acc,name.branch,sal,loc) has the following functional dependencies: [(6) {{acc,loc)-nameB loc-sal; acc--branch). Find...

Transcribed Image Text:

a. Suppose in relation schema R(acc,name.branch,sal,loc) has the following functional dependencies: [(6) {{acc,loc)-nameB loc-sal; acc--branch). Find out the candidate key(s) and super key(s) which are not candidate keys. b. Consider the following relations with their attributes: [6] EMPLOYEE(fname,Iname ssn sex,salary,super_ssn.dno,address,birth_date) PROJECT(Pname.Pnumber, Plocation,Dnum) WORKS ON(Essp.Pno. Hours) Draw the initial query tree for the following SQL query. Then apply heuristic query optimization on that initial query tree to find the final query tree (if any) that is efficient to execute. SELECT Pnumber, Fname, FROM PROJECT, WORKS ON, EMPLOYEE WHERE Pnumber = Pno AND Essn = Ssn AND Lname = 'Smith'; a. Suppose in relation schema R(acc,name.branch,sal,loc) has the following functional dependencies: [(6) {{acc,loc)-nameB loc-sal; acc--branch). Find out the candidate key(s) and super key(s) which are not candidate keys. b. Consider the following relations with their attributes: [6] EMPLOYEE(fname,Iname ssn sex,salary,super_ssn.dno,address,birth_date) PROJECT(Pname.Pnumber, Plocation,Dnum) WORKS ON(Essp.Pno. Hours) Draw the initial query tree for the following SQL query. Then apply heuristic query optimization on that initial query tree to find the final query tree (if any) that is efficient to execute. SELECT Pnumber, Fname, FROM PROJECT, WORKS ON, EMPLOYEE WHERE Pnumber = Pno AND Essn = Ssn AND Lname = 'Smith';