Solution Library

Drawing Initial and Optimized Data Tree

Question

Consider the database schema

emp(eid, did, sal, hobby)

dept(did, dname, floor, phone)

finance(did, budget, sales, expenses)

and the following query.

Select D.dname, F.budget

From emp E, dept D, finance F

Where E.did=D.did and D.did=F.did and D.floor=1

and E.sal>=49000 and E.hobby=’tennis’

(a) Draw the initial query tree and an optimized query tree produced by a decent query

(b) Assume that the optimizer will not consider any evaluation plan involving a cross product, list join orders that the optimizer will consider.

(c) Consider the following scenario. The attributes that have unclustered B+ tree indexes are emp.did, emp.sal, dept.floor, dept.did, and finance.did. The data in emp.sal ranges between 10,000 and 60,000. Employees have 200 different hobbies. The building of the company has 5 floors. The company has 50,000 employees and 2,500 departments, each of which has some financial information. The DBMS uses only the indexed nested loop join methods including possible pipelining. Assume that all B+ tree indexes have 3 levels.

i. For each base relation (i.e., emp, dept, and finance), estimate the number of tuples that will satisfy all non-join conditions relevant to that relation.

ii. Which join order considered by the optimizer has the lowest estimated cost?

Summary

This question belongs to computer science and discusses about query tree for a database schema.

Word count: NA

 

Download Full Solution

Comments

  • HWA
    Rasha

    this is a very good website

  • HWA
    maani

    I have 50 questions for the same test your page is showing only 28

  • HWA
    joeanne

    hi can you please help or guide me to answer my assignments. thanks

  • HWA
    joeanne

    hi can anyone help or guide me to my assignments. thanks

  • HWA
    Monik


  • HWA
    Cristina

    This solution is perfect ...thanks

  • HWA
    Janete

    Hello Allison,I love the 2nd image that you did! I also, had never heard of SumoPaint, is something that I will have to exolpre a bit! I understand completely the 52 (or so) youtube videos that you probably watched. Sometimes they have what you want, sometimes they don't! However, it is always satisfying when you are able to produce something that you have taught yourself. Great job!Debra 0 likes

  • HWA
    Sandeep

    Perfect bank of solution. 

  • HWA
    Oxana

    great !

  • HWA
    Paul Brandon-Fritzius

    thanks for the quick response. the solution looks good. :)

  • HWA
    tina Johnson

    thnx for the answer. it was perfect. just the way i wanted it. 

  • HWA
    Giuseppe

    works fine.