• No results found

DATABASE DESIGN I -

N/A
N/A
Protected

Academic year: 2022

Share "DATABASE DESIGN I -"

Copied!
27
0
0

Loading.... (view fulltext now)

Full text

(1)

DATABASE DESIGN I - 1DL300

Fall 2009

An introductury course on database systems http://user.it.uu.se/~udbl/dbt1-ht2009/

alt. http://www.it.uu.se/edu/course/homepage/dbastekn/ht09/

Kjell Orsborn

Uppsala Database Laboratory

Department of Information Technology, Uppsala University, Uppsala, Sweden

(2)

Introduction to Relational Algebra

Elmasri/Navathe ch 6

Padron-McCarthy/Risch ch 10

Kjell Orsborn

Department of Information Technology Uppsala University, Uppsala, Sweden

(3)

Query languages

• Languages where users can express what information to retrieve from the database.

• Categories of query languages:

– Procedural

– Non-procedural (declarative)

• Formal (“pure”) languages:

– Relational algebra – Relational calculus

• Tuple-relational calculus

• Domain-relational calculus

– Formal languages form underlying basis of query languages that people use.

(4)

Relational algebra

• Relational algebra is a procedural language

• Operations in relational algebra takes two or more relations as arguments and return a new relation.

• Relational algebraic operations:

– Operations from set theory:

• Union, Intersection, Difference, Cartesian product

– Operations specifically introduced for the relational data model:

• Select, Project, Join

• It have been shown that the select, project, union, difference, and cartesian product operations form a complete set. That is any other relational algebra operation can be expressed in these.

(5)

Operations from set theory

• Relations are required to be union compatible to be able to take part in the union, intersection and difference operations.

• Two relations R1 and R2 is said to be union-compatible if:

R1 ⊆ D1 × D2 × ... × Dn and R2 ⊆ D1 × D2 × ... × Dn

i.e. if they have the same degree and the same domains.

(6)

Union operation

• The union of two union-compatible relations R and S is the set of all tuples that either occur in R, S, or in both.

• Notation: R ∪ S

• Defined as: R ∪ S = {t | t ∈ R or t ∈ S}

• For example:

R S

A B a

a b

1 2 1

A B

a b

2 3

= A B

a a b

1 2 1

(7)

Difference operation

• The difference between two union-compatible sets R and S is the set of all tuples that occur in R but not in S.

• Notation: R − S

• Defined as: R − S = {t | t ∈ R and t ∉ S}

• For example:

R S

A B a

a

1 2

A B

a b

2 3

= A B

a b

1 1

(8)

Intersection

• The intersection of two union-compatible sets R and S, is the set of all tuples that occur in both R and S.

• Notation: R ∩ S

• Defined as: R ∩ S = {t | t ∈ R and t ∈ S}

• For example:

R S

A B a

a b

1 2 1

A B

a b

2 3

= A B

a 2

(9)

Cartesian product

• Let R and S be relations with k1 and k2 arities resp. The cartesian product of R and S is the set of all possible k1+k2 tuples where the first k1 components constitute a tuple in R and the last k2 components a tuple in S.

• Notation: R × S

• Defined as: R × S = {t q | t ∈ R and q ∈ S}

• Assume that attributes of r(R) and s(S) are disjoint. (i.e. R ∩ S = ∅). If attributes of r(R) and s(S) are not disjoint, then renaming must be used.

X =

(10)

Cartesian product example

A B a

b

1 2

× C D

a b b c

5 5 6 5

= A B

a a a a b b b b

1 1 1 1 2 2 2 2

C D a

b b c a b b c

5 5 6 5 5 5 6 5

(11)

Selection operation

• The selection operator, σ, selects a specific set of tuples from a relation according to a selection condition (or selection predicate) P.

• Notation: σp(R)

• Defined as: σp(R) = {t | t ∈ R and P(t) } (i.e. the set of tuples t in R that fulfills the condition P)

• Where P is a logical expression(*) consisting of terms connected by:

(and), (or), ¬ (not)

and each term is one of: <attribute> op <attribute> or <constant>, where op is one of: =, ≠, >, ≥, <, ≤

Example: σSALARY>30000(EMPLOYEE) (*) a formula in propositional calculus

(12)

Selection example

= A B

a a b b

a b b b

C D 1

5 2 4

7 7 3 9

R

σA=B ∧ D > 5 (R) = A B

a b

a b

C D 1

4

7 9

(13)

Projection operation

• The projection operator, Π, picks out (or projects) listed columns from a relation and creates a new relation consisting of these

columns.

• Notation: ΠA1,A2,...,Ak (R), where A1, A2 are attribute names and R is a relation name.

• The result is a new relation of k columns.

• Duplicate rows removed from result, since relations are sets.

Example: ΠLNAME,FNAME,SALARY(EMPLOYEE)

(14)

Projection example

= A B

a a b b

1 2 3 4

C 1 1 1 2

R

ΠA,C (R) = A C

a a b

1 1 1

= A C

a b b

1 1 2

(15)

Join operator

• The join operator, ⊗ (almost, correct     ), creates a new relation by joining related tuples from two relations.

• Notation: R ⊗C S

C is the join condition which has the form Ar θ As , where θ is one of {=, <, >, ≤, ≥, ≠ }. Several terms can be connected as C1

∧C2 ∧...Ck.

• A join operation with this kind of general join condition is called

“Theta join”.

(16)

Example Theta join

⊗A≤F = A B

1 1 1 6 9

2 2 2 7 7

C D 3

3 3 8 8

2 7 7 7 7 A B

1 6 9

2 7 7

C 3 8 8

D E 2

7 7

3 3 8

F 4 5 9

R ⊗A≤F S

E 3 3 8 8 8

F 4 5 9 9 9

R S

(17)

Equijoin

• The same as join but it is required that attribute Ar and attribute As should have the same value.

• Notation: R ⊗C S

C is the join condition which has the form Ar = As. Several terms can be connected as C1 ∧C2 ∧...Ck.

(18)

Example Equijoin

⊗B=C = A B

a a

2 4

C D 2

4

d d A B

a a

2 4

C D 2

4 9

d d d

E e e e

R ⊗B=C S

E e e

R S

(19)

Natural join

• Natural join is equivalent with the application of join to R and S with the equality condition Ar = As (i.e. an equijoin) and then removing the redundant column As in the result.

• Notation: R *Ar,As S

Ar,As are attribute pairs that should fulfil the join condition which has the form Ar = As. Several terms can be connected as C1 ∧C2

∧...Ck.

(20)

Example Natural join

⊗B=C = A B

a a

2 4

D d d A B

a a

2 4

C D 2

4 9

d d d

E e e e

R ∗B=C S

E e e

R S

(21)

Composition of operations

• Expressions can be built by composing multiple operations

• Example: σA=C (R × S)

σA=C (R × S) =

A B a

b

1 2

× C D

a b b c

5 5 6 5

= A B

a a a a b b b b

1 1 1 1 2 2 2 2

C D a

b b c a b b c

5 5 6 5 5 5 6 A B 5

a 1

C D a 5

R × S =

(22)

Assignment operation

• The assignment operation (← ) makes it possible to assign the result of an expression to a temporary relation variable.

• Example:

temp ← σdno = 5 (EMPLOYEE)

  result ← ∏fname,lname,salary (temp)

• The result to the right of the ← is assigned to the relation variable on the left of the ←.

• The variable may be used in subsequent expressions.

(23)

Renaming relations and attribute

• The assignment operation can also be used to rename relations and attributes.

• Example:

NEWEMP ← σdno = 5(EMPLOYEE) R(FIRSTNAME,LASTNAME,SALARY) ←

∏fname,lname,salary (NEWEMP)

(24)

Division operation

• Suited to queries that include the phrase “for all”.

• Let R and S be relations on schemas R and S respectively, where

R = (A1,...,Am ,B1,...,Bn) S = (B1,...,Bn)

• The result of R ÷ S is a relation on the schema R - S = (A1,...,Am) R ÷ S = {t | t ∈ ΠR-S (R) ∀u ∈ S ∧ tu ∈ R}

(25)

Example of Division operation

÷ = A

a e A B

a a a b c d d d d e

1 2 3 1 1 1 3 4 6 1

B 1 2

R ÷ S

R S

(26)

Relation algebra as a query language

• Relational schema: supplies(sname, iname, price)

• “What is the names of the suppliers that supply cheese?”

πsname(σiname='CHEESE'(SUPPLIES))

• “What is the name and price of the items that cost less than 5 $ and that are supplied by WALMART”

πiname,price(σsname='WALMART' price < 5 (SUPPLIES))

(27)

Additional relational operations

• Outer join and outer union (presented together with SQL)

• Aggregation operations (presented together with SQL)

• Update operations (presented together with SQL)

– (not part of pure query language)

References

Related documents

There is a class hierarchy inheriting from this base class: a class for basic programs, another for programs combinating subprograms with the and/or-combinator, other classes

The aim of the present qualitative study is to study the potential for and mechanisms of evidentiality being used for relational functions in an interactional con- text, focusing on

With regard to specific types of skin cancer, this increased risk in patients with actinic keratosis was highest (greater than 7 times higher) for squamous cell carcinoma,

Problemet för en rent instrumental musik var, att även om känslor stod i relation till handlingar genom att motivera dessa, så var det omöjligt att sluta sig till vilka handlingar

In this thesis we have explored the usage of relational symbolic execution in order to perform formal verification of security properties for WebAssembly programs.. We described

Resultatet från detta arbete kommer leda till att utvecklingsteamet får en bra inblick av vad NoSQL är för databastyp och hur bra eller dåligt den skulle prestera med deras

Given the theoretical and empirical problem, the purpose is to add to our understanding of leadership as a social phenomenon and contribute to developing a vocabulary for

The following table and graphics shows the times that the cuts need, depending on the schema used, the number of events evaluated and query applied.