1 / 38

Query Design

Query Design. Objectives of the Lecture :. To learn a strategy for designing queries. To learn how to use relational algebra concepts to implement the strategy. To learn how to translate the resulting query design into SQL. Relational Queries. A query has two parts :

peggy
Télécharger la présentation

Query Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Query Design Objectives of the Lecture : • To learn a strategy for designing queries. • To learn how to use relational algebra concepts to implement the strategy. • To learn how to translate the resulting query design into SQL.

  2. Relational Queries A query has two parts : • derive a relation whose contents are the answer to your query; • retrieve that derived relation. In SQL : SELECT ........................................................................................................................... ; Trivial ! Retrieve Derivation of relation Queries so far have retrieved relations whose derivations were relatively simple. Extremely sophisticated derivations - i.e. complex queries - can be written for relational DBs.

  3. Query Design Strategy 1. Determine which relations in the DB hold relevant data : Which relation holds the Determinant data ?Which relation holds the Dependent data ? 2. Use relational algebra concepts to conceptually derive one relation that holds just the determinant & the dependent.This relation holds the answer to the query.Each algebra operator represents a conceptually natural manipulation of relations.Simpler to use than SQL clauses & phrases. 3. Translate design into SQL.Most (not all) relational DBMSs use SQL, so translate into SQL in order to execute query.

  4. Employee EmpNo EName M-S Salary DeptNo Dept DeptNo DName Budget MgrNo Alloc Project ProjNo EmpNo ProjNo Start End Example Database It is notnecessary to know the data values in any relation, or any integrity constraints except attribute data types.

  5. Example 1 : Determinant & Dependent Query : Get the names & salaries of all married & widowed employees. Determinant – who/what is it we want to know about ?Married & widowed employees. Dependent– what do we want to know about the ‘determinant’ ? Names & salaries. Q. In which relation(s) are the determinant & dependent ? A. Both in relation Employee. Need to form one relation from Employee with just the determinant & dependent data in. Do this by ‘reducing’ Employee to just the relevant attributes & tuples. Get rid of the rest.

  6. Project[ M-S, EName, Salary ] Restrict[ M-S = ‘M’ Or M-S = ‘W’ ] Employee Example 1 : Derive One Relation Get rid of all attributes except those containing the determinant & dependent. Answer ! Get rid of all tuples except those containing the determinant (& dependent).

  7. Project[ M-S, EName, Salary ] Restrict[ M-S = ‘M’ Or M-S = ‘W’ ] Employee Example 1 : Convert to SQL SELECT M_S, EName, Salary FROM Employee WHERE M_S = ‘M’ OR M_S = ‘W’ ;

  8. Salary M-S EName ... ... M ... M W Jane Ali Sid ....... ....... ....... 3100 ..... 3000 ..... ..... 4400 Dependant Example 1 : Result Relation contains only the determinant & dependent. Determinant

  9. Example 2 : Determinant & Dependent Query : Get the names, salaries & project numbers of employees who work on projects. Determinant – who/what is it we want to know about ?Employees who work on projects. Dependent – what do we want to know about the ‘determinant’ ?Names, salaries and project numbers. Q. In which relation(s) are the determinant & dependent ? A. Determinant in relation Alloc. Dependent in relations Employee and Alloc. Need to form one relation from Employee and Alloc with just the determinant & dependent data in. Do this by ‘merging’ Employee and Alloc together & ‘reducing’ the result to just the required attributes & tuples.

  10. ‘Merging’ Relations 1. ‘Horizontally’ Join ==> 2. ‘Vertically’ Union ==> Example needs a horizontal merge.

  11. Project[ ProjNo,EName, Salary ] Join[ EmpNo ] Employee Alloc Example 2 : Derive One Relation Answer ! Get rid of all attributes except those containing the determinant & dependent. Merge relations ‘horizontally’.

  12. Project[ ProjNo, EName, Salary ] Join[ EmpNo ] Employee Alloc Example 2 : Convert to SQL SELECT ProjNo, EName, Salary FROM Employee NATURALJOIN Alloc ;

  13. Salary ProjNo EName P2 Joan 2900 P2 Uli 3200 ... ... ... ....... ....... ....... ..... ..... ..... P4 Ryan 4400 Dependant Example 2 : Result Relation contains only the determinant & dependent. Determinant

  14. Example 3 : Determinant & Dependent Query : Get the total salary of all married employees. Determinant – who/what is it we want to know about ?Married employees. Dependent – what do we want to know about the ‘determinant’ ?Salaries : but a calculation is needed to get the total ! Q. In which relation(s) are the determinant & dependent ? A. Both in relation Employee. Need to form one relation from Employee with just the determinant & calculated dependent data in. Do this by ‘reducing’ Employee to just the required tuples, and calculating what is required.

  15. Calculating Data 1. ‘Horizontally’ Extend ==> 2. ‘Vertically’ Example needs a vertical calculation. ==> GroupBy

  16. GroupBy[ M-S ] With[ Total <-- Bag[Salary]Sum] Restrict[ M-S = ‘M’ ] Employee Example 3 : Derive One Relation Calculate the dependent, & get rid of all other attributes except the determinant. Answer ! Get rid of all tuples except those containing the determinant (& dependent).

  17. Example 3 : Convert to SQL SELECT M_S, SUM(ALL Salary) AS Total FROM Employee WHERE M_S = ‘M’ GROUP BY M_S ; GroupBy[ M-S ]With[ Total <-- Bag[Salary]Sum] Restrict[ M-S = ‘M’ ] Employee

  18. M-S Total M 120,000 Example 3 : Result Relation contains only the determinant & dependent. Dependant Determinant

  19. Determinants Always need a determinant and a dependent to design a query. However determinant does not always need to be in the answer. Typically there are 2 cases where this arises : 1. If the determinant is a single value, then it can be left out of the answer, because the query designer knows the determinant to which the dependent data refers, and so doesn’t need it in the answer. 2. If the query designer is not interested in distinguishing between different determining values, then they can be left out,because they don’t matter.

  20. Revised E.G. 1 : Determinant/Dependent Query : Get the names & salaries of all married& widowed employees. Determinant – who/what is it we want to know about ?Married & widowed employees. Dependent – what do we want to know about the ‘determinant’ ?Names & salaries. Q. In which relation(s) are the determinant & dependent ? A. Both in relation Employee. Need to form one relation from Employee with just the determinant & dependent data in. Then remove the determinant data as well - it will have just one value, the ‘married’ value.

  21. Salary EName Jane 3000 Ali 3100 ....... ..... ....... ..... Revised E.G. 1 : Required Result Relation contains only dependent data. No determinant because the query designer knows that all the dependent values now refer to ‘married’ people. Before some were married and some were widowed. Therefore they had to be distinguished.

  22. Project[ EName, Salary ] Restrict[ M-S = ‘M’ ] Employee Revised E.G. 1 : Derive One Relation Get rid of all attributes except the dependent ones. Get rid of all tuples not containing the single ‘married’determinant value.

  23. Project[ EName, Salary ] Restrict[ M-S = ‘M’ ] Employee Revised E.G. 1 : Convert to SQL SELECT EName, Salary FROM Employee WHERE M_S = ‘M’;

  24. Revised E.G. 2 : Determinant/Dependent Query : Get the names, salaries & project numbers of employees who work on projects. Determinant – who/what is it we want to know about ?Employees who work on projects. Dependent – what do we want to know about the ‘determinant’ ?Names & salaries. Q. In which relation(s) are the determinant & dependent ? A. Determinant in relation Alloc. Dependent in relation Employee. Form one relation from Employee and Alloc, by joining them. The result will contain all data about employees and their projects. Then ‘reduce’ the result to just the dependent attributes.

  25. Salary EName Joan 2900 Uli 3200 ....... ..... ....... ..... ....... ..... Ryan 4400 Revised E.G. 2 : Required Result Relation contains only dependent data. No determinant because the query designer is not interested in the particular projects that employees work on. Before the individual projects were relevant. Therefore they had to be distinguished.

  26. Project[ EName, Salary ] Join[ EmpNo ] Employee Alloc Revised E.G. 2 : Derive One Relation Get rid of all attributes except the dependents. Merge relations ‘horizontally’.

  27. Project[ EName, Salary ] Join[ EmpNo ] Employee Alloc Revised E.G. 2 : Convert to SQL SELECT EName, Salary FROM Employee NATURALJOIN Alloc ;

  28. Revised E.G. 3 : Determinant/Dependent Query : Get the total salary of all married employees. DeterminantMarried employees DependentSalaries, but totalled. Q. In which relation(s) are the determinant & dependent ? A. Both in relation Employee. Need to form one relation from Employee with just the calculated dependent data in. Do this by ‘reducing’ Employee to just the required tuples, and calculating what is required. Don’t actually need to include ‘married’ in the result, since we know the employees referred to.

  29. Total 120,000 Revised E.G. 3 : Required Result Relation contains only the calculated dependent. No determinant because the query designer knows that the dependent result refers to ‘married’ people. Didn’t really need to include the ‘married’ marital-status value before.

  30. Restrict[ M-S = ‘M’ ] Employee Revised E.G. 3 : Derive One Relation Calculate the total from the whole result of the restriction, because it only holds data about ‘married’ employees. GroupBy[ ]With[ Total <-- Bag[Salary]Sum] Get rid of all tuples except those referring to the ‘married’ determinant.

  31. Revised E.G. 3 : Convert to SQL SELECT SUM(ALL Salary) AS Total FROM Employee WHERE M_S = ‘M’ ; GroupBy[ ]With[ Total <-- Bag[Salary]Sum] Restrict[ M-S = ‘M’ ] Employee Nothing !

  32. Dependants Always need a determinant and a dependent to design a query. If there are queries where only the dependant needs to be in the result, are there queries where only the determinant needs to be in the result ? The answer is, not very often. The typical case is a query to discover if a determinant exists.Such a query typically requires an answer of yes/no or true/false.

  33. Example with No Dependant in Answer Query : Are there any married employees ? Determinant – who/what is it we want to know about ?Married employees. Dependent – what do we want to know about the ‘determinant’ ?Nothing ! Only whether they exist. Q. In which relation(s) is the determinant? A. Relation Employee : contains data for all employees, including their marital-status. Need to form one relation from Employee with just the determinant data in. Do this by ‘reducing’ Employee to just the marital-status attribute with tuples whose marital-status = ‘married’.

  34. M-S M-S M M M M M Required Result : First Attempt OR Answer if there are ‘married’ employees. Answer if there are no ‘married’ employees. Relation(s) contain only the determinant. Dependant is of no interest.

  35. Result Really Required because the previous Yes answer could be very large (!)and the previous No answer could be misleading. OR No Yes Unfortunately, SQL has no easy way to derive Yes/No answers.  design the query to deliver the previous answer(s).

  36. Project[ M-S ] Restrict[ M-S = ‘M’ ] Employee Derive One Relation Get rid of all attributes except the determinant value ‘married’. Answer ! Do we need this step ? Only to prevent a Yes answer from being too big. Get rid of all tuples except those containing the determinant value ‘married’.

  37. Project[ M-S ] Restrict[ M-S = ‘M’ ] Employee Convert to SQL SELECT M_S FROM Employee WHERE M_S = ‘M’ ;

  38. Conclusion Strategy : • Determine which relations hold the determinant & dependent data. • Use relational algebra to derive one relation that holds both. • Decide whether determinant or dependant data can be pruned out. • Convert to SQL. Further Developments • Suppose determinant and/or dependent data is itself split over 2 or more relations ? Combine them into one relation using the above approach. Then continue as before. • May need both horizontal and vertical calculations in one query. • Even more advanced queries can be built on these foundations. Design them with the same approach, applied recursively.

More Related