Soal Latihan

 

Employee (person_name, street, city)

Works (­person_name, company_name, salary)

Company (company_name, city)

Manages (person_name, manager_name)

 

I.Expression in the relational algebra to express each of the queries.

  1. Πperson_name (σperson_name = manager_name ᴧ employee.city = manages.person_name.city(employee ⋈ manages))
  2. Пperson_name (σcompany_name ≠ “First Bank Corporation”(works))
  3. Пperson_name (σsalary > (σmax(salary) ᴧ company_name = “Small Bank Corporation(works))

 

II.Expression in the relational algebra to express each of the queries.

  1. Пperson_name, street, city (σmanager_name = “Jones” (employee x manages))
  2. Пcity (σmanager_name = “Jones” (employee x manages))
  3. Gmanages.manager_name = (Gmanages.person_name = “Jones”(manages))
  4. Пperson_name (σsalary > (σmax(salary) ᴧ city = “Mumbai” (employee ⋈works))

 

III.–

  1. Пperson_name (σcompany_name = “Small Bank Corporation”(works))
  2. Пperson_name, city (σcompany_name ≠ “First Bank Corporation”(employee ⋈works))
  3. Пperson_name, street, city (σcompany_name = “Small Bank Corporation” ᴧ salary > 45000 (employee ⋈works))
  4. Пperson_name (σemployee.city = company.city((employee ⋈works) ⋈ company))
  5. Пcity (σcompany_name = “First Bank Corporation”(company))

 

IV. Query Evaluation Plan

Пperson_name (σcompany_name ≠ “First Bank Corporation”(works))

  • Пperson_name [sort the person name]
  • (σcompany_name ≠ “First Bank Corporation”(works)) [A4, Secondary Index, Equality on nonkey)

 

Пperson_name, city (σcompany_name ≠ “First Bank Corporation”(employee ⋈works))

  • Пperson_name, city [sort the person name and group with the city]
  • (σcompany_name ≠ “First Bank Corporation”(employee ⋈works)) [joining the employee and works table]

 

V. Example, R1 has 20000 tuples and R2 has 45000 tuples. 25 tuples of R1 fit on one block and 30 tuples of R2 fit on one block. Estimate the number of block transfer and seeks required using each of the following join strategies.

Nested Loop Join

From the example we can know that R1 has 20.000 records and 800 blocks. R2 has 45.000 records and 1.500 blocks.

Formula for R1 = nr1 * br2 + br1 blocks transfer and nr1 + br1 seeks

Formula for R2 = nr2 * br1 + br2 blocks transfer and nr2 + br2 seeks

R1 = 20.000 * 1.500 + 800 = 30.000.800 blocks transfer and 20.000 + 800 = 20.800 seeks.

R2 = 45.000 * 800 + 1.500 = 36.001.500 blocks transfer and 45.000 + 1.500 = 46.500 seeks.

 

Block Nested Loop Join

From the example we can know that R1 has 20.000 records and 800 blocks. R2 has 45.000 records and 1.500 blocks.

Formula for R1 = br1 * br2 + br1 blocks transfer and 2 * br1 seeks

Formula for R2 = br2 * br1 + br2 blocks transfer and 2 * br2 seeks

R1 = 800 * 1500 + 800 = 1.200.800 blocks transfer and 2 * 800 = 1.600 seeks.

R2 = 1500 * 800 + 1500 = 1.201.500 blocks transfer and 2 * 1500 = 3.000 seeks.