(Part B.) Consider the following database: 5 Apply following query by two differ

Question

(Part B.) Consider the following database: 5

Apply following query by two different methods given below and show the intermediate relations. Also describe which one of the methods is good in query optimization context and why?

Query

SELECT E.Lname, E.Bdate, D.Mgr_ssn
FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.Salary >= 30000 AND D.Dname = ‘Research’

AND E.Dno = D.Dnumber;

Method1:

Apply Join first and then Selection according to given conditions

Methdo2:

Apply Selection first and then Join according to given conditions

EMPLOYEE Ssn Bdate Fname Minit Lname John FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 Alicia JenniferS Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Ramesh KNarayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 JoyceA English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 AhmadVJabbar 9879879871969-03-29 980 Dallas, Houston, TXM 25000 9876543214 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL Sex Salary Super_ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Address JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654324 DEPARTMENT DEPT LOCATIONS Dnumber Dlocation Houston Stafford Bellaire Sugarland Houston Dnumber Dname Research Administration Headquarters Mgr ssn 333445555 987654321 888665555 Mgr_start date 1988-05-22 1995-01-01 1981-06-19 4 4

Explanation / Answer

SELECT E.Lname, E.Bdate, D.Mgr_ssn FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.Salary >= 30000 AND D.Dname = ‘Research’ AND E.Dno = D.Dnumber;

Query optimization is a function of many relational database management systems. The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans.

A query is a request for information from a database. It can be as simple as "finding the address of a person with SS# 123-45-6789," or more complex like "finding the average salary of all the employed married men in California between the ages 30 to 39, that earn less than their wives." Queries results are generated by accessing relevant database data and manipulating it in a way that yields the requested information. Since database structures are complex, in most cases, and especially for not-very-simple queries, the needed data for a query can be collected from a database by accessing it in different ways, through different data-structures, and in different orders. Each different way typically requires different processing time. Processing times of the same query may have large variance, from a fraction of a second to hours, depending on the way selected. The purpose of query optimization, which is an automated process, is to find the way to process a given query in minimum time. The large possible variance in time justifies performing query optimization, though finding the exact optimal way to execute a query, among all possibilities, is typically very complex, time consuming by itself, may be too costly, and often practically impossible. Thus query optimization typically tries to approximate the optimum by comparing several common-sense alternatives to provide in a reasonable time a "good enough" plan which typically does not deviate much from the best possible result.

A SQL query to a modern relational DBMS does more than just selections and joins. In particular, SQL queries often nest several layers of SPJ blocks (Select-Project-Join), by means of group by, exists, and not exists operators. In some cases such nested SQL queries can be flattened into a select-project-join query, but not always. Query plans for nested SQL queries can also be chosen using the same dynamic programming algorithm as used for join ordering, but this can lead to an enormous escalation in query optimization time. So some database management systems can be use an alternative rule-based approach that uses a query graph model.

from the above query

applying the mwthod 1 is:

First the operation performed on the table is Join onec we join the table all the records will be together. and based on the condition it fetch the appropriate records. it is the good approach.

In method 2 selection is first done and joine the table in this scenario selected or limited records to be fetch and join the tables it is faster than the method 1

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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