- By
**peggy** - Follow User

- 83 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about ' Query Design' - peggy

**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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

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 :

- 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.

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.

EmpNo

EName

M-S

Salary

DeptNo

Dept

DeptNo

DName

Budget

MgrNo

Alloc

Project

ProjNo

EmpNo

ProjNo

Start

End

Example DatabaseIt is notnecessary to know the data values in any relation, or any integrity constraints except attribute data types.

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.

Project[ M-S, EName, Salary ]

Restrict[ M-S = ‘M’ Or M-S = ‘W’ ]

Employee

Example 1 : Derive One RelationGet rid of all attributes except those containing the determinant & dependent.

Answer !

Get rid of all tuples except those containing the determinant (& dependent).

Project[ M-S, EName, Salary ]

Restrict[ M-S = ‘M’ Or M-S = ‘W’ ]

Employee

Example 1 : Convert to SQLSELECT M_S, EName, Salary

FROM Employee

WHERE M_S = ‘M’ OR M_S = ‘W’ ;

M-S

EName

...

...

M

...

M

W

Jane

Ali

Sid

.......

.......

.......

3100

.....

3000

.....

.....

4400

Dependant

Example 1 : ResultRelation contains only the determinant & dependent.

Determinant

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.

‘Merging’ Relations

1. ‘Horizontally’

Join

==>

2. ‘Vertically’

Union

==>

Example needs a horizontal merge.

Project[ ProjNo,EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

Example 2 : Derive One RelationAnswer !

Get rid of all attributes except those containing the determinant & dependent.

Merge relations ‘horizontally’.

Project[ ProjNo, EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

Example 2 : Convert to SQLSELECT ProjNo, EName, Salary

FROM Employee NATURALJOIN Alloc ;

ProjNo

EName

P2

Joan

2900

P2

Uli

3200

...

...

...

.......

.......

.......

.....

.....

.....

P4

Ryan

4400

Dependant

Example 2 : ResultRelation contains only the determinant & dependent.

Determinant

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.

Calculating Data

1. ‘Horizontally’

Extend

==>

2. ‘Vertically’

Example needs a vertical calculation.

==>

GroupBy

GroupBy[ M-S ] With[ Total <-- Bag[Salary]Sum]

Restrict[ M-S = ‘M’ ]

Employee

Example 3 : Derive One RelationCalculate the dependent, & get rid of all other attributes except the determinant.

Answer !

Get rid of all tuples except those containing the determinant (& dependent).

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

Total

M

120,000

Example 3 : ResultRelation contains only the determinant & dependent.

Dependant

Determinant

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.

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.

EName

Jane

3000

Ali

3100

.......

.....

.......

.....

Revised E.G. 1 : Required ResultRelation 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.

Project[ EName, Salary ]

Restrict[ M-S = ‘M’ ]

Employee

Revised E.G. 1 : Derive One RelationGet rid of all attributes except the dependent ones.

Get rid of all tuples not containing the single ‘married’determinant value.

Project[ EName, Salary ]

Restrict[ M-S = ‘M’ ]

Employee

Revised E.G. 1 : Convert to SQLSELECT EName, Salary

FROM Employee

WHERE M_S = ‘M’;

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.

EName

Joan

2900

Uli

3200

.......

.....

.......

.....

.......

.....

Ryan

4400

Revised E.G. 2 : Required ResultRelation 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.

Project[ EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

Revised E.G. 2 : Derive One RelationGet rid of all attributes except the dependents.

Merge relations ‘horizontally’.

Project[ EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

Revised E.G. 2 : Convert to SQLSELECT EName, Salary

FROM Employee NATURALJOIN Alloc ;

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.

120,000

Revised E.G. 3 : Required ResultRelation 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.

Restrict[ M-S = ‘M’ ]

Employee

Revised E.G. 3 : Derive One RelationCalculate 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.

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 !

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.

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’.

M-S

M

M

M

M

M

Required Result : First AttemptOR

Answer if there are ‘married’ employees.

Answer if there are no ‘married’ employees.

Relation(s) contain only the determinant. Dependant is of no interest.

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).

Project[ M-S ]

Restrict[ M-S = ‘M’ ]

Employee

Derive One RelationGet 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’.

Project[ M-S ]

Restrict[ M-S = ‘M’ ]

Employee

Convert to SQLSELECT M_S

FROM Employee

WHERE M_S = ‘M’ ;

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.

Download Presentation

Connecting to Server..