- 49 Views
- Uploaded on
- Presentation posted in: General

Query Design

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

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.

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.

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.

Employee

EmpNo

EName

M-S

Salary

DeptNo

Dept

DeptNo

DName

Budget

MgrNo

Alloc

Project

ProjNo

EmpNo

ProjNo

Start

End

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

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

Get 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

SELECT M_S, EName, Salary

FROM Employee

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

Salary

M-S

EName

...

...

M

...

M

W

Jane

Ali

Sid

.......

.......

.......

3100

.....

3000

.....

.....

4400

Dependant

Relation contains only the determinant & dependent.

Determinant

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.

1. ‘Horizontally’

Join

==>

2. ‘Vertically’

Union

==>

Example needs a horizontal merge.

Project[ ProjNo,EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

Answer !

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

Merge relations ‘horizontally’.

Project[ ProjNo, EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

SELECT ProjNo, EName, Salary

FROM Employee NATURALJOIN Alloc ;

Salary

ProjNo

EName

P2

Joan

2900

P2

Uli

3200

...

...

...

.......

.......

.......

.....

.....

.....

P4

Ryan

4400

Dependant

Relation contains only the determinant & dependent.

Determinant

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.

1. ‘Horizontally’

Extend

==>

2. ‘Vertically’

Example needs a vertical calculation.

==>

GroupBy

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

Restrict[ M-S = ‘M’ ]

Employee

Calculate the dependent, & get rid of all other attributes except the determinant.

Answer !

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

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

M-S

Total

M

120,000

Relation contains only the determinant & dependent.

Dependant

Determinant

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.

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.

Salary

EName

Jane

3000

Ali

3100

.......

.....

.......

.....

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.

Project[ EName, Salary ]

Restrict[ M-S = ‘M’ ]

Employee

Get 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

SELECT EName, Salary

FROM Employee

WHERE M_S = ‘M’;

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.

Salary

EName

Joan

2900

Uli

3200

.......

.....

.......

.....

.......

.....

Ryan

4400

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.

Project[ EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

Get rid of all attributes except the dependents.

Merge relations ‘horizontally’.

Project[ EName, Salary ]

Join[ EmpNo ]

Employee

Alloc

SELECT EName, Salary

FROM Employee NATURALJOIN Alloc ;

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.

Total

120,000

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.

Restrict[ M-S = ‘M’ ]

Employee

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.

SELECT SUM(ALL Salary) AS Total

FROM Employee

WHERE M_S = ‘M’ ;

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

Restrict[ M-S = ‘M’ ]

Employee

Nothing !

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.

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

M

M

M

M

M

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.

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

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

Project[ M-S ]

Restrict[ M-S = ‘M’ ]

Employee

SELECTM_S

FROMEmployee

WHEREM_S = ‘M’ ;

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.