1 / 27

Relational Algebra - PowerPoint PPT Presentation

Relational Algebra. NEU – CCIS – CSU430 Tony. Content. Set-theoretic operations Native relational operations Other join operations. 1. Set-theoretic operations. Union Intersection Difference Product. R. S. S. A. B. C. A. A. B. B. D. C. a1. b1. c1. a1. a1. b1. b1. c1.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

PowerPoint Slideshow about ' Relational Algebra' - dyllis

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

Relational Algebra

NEU – CCIS – CSU430

Tony

• Set-theoretic operations

• Native relational operations

• Other join operations

• Union

• Intersection

• Difference

• Product

S

S

A

B

C

A

A

B

B

D

C

a1

b1

c1

a1

a1

b1

b1

c1

d1

a1

b2

c2

a1

a1

b2

b2

c2

d2

1.0 Compatible tables

• Tables R and S are compatible if they have the same headings.

R U S

A

B

C

R

S

A

B

C

a1

A

B

b1

C

c1

a1

b1

c1

a1

a1

b1

b2

c1

c2

a1

b2

c2

a1

a1

b2

b2

c3

c3

1.1 Union (only for compatible tables)

R n S

A

B

C

R

S

A

B

C

a1

A

b1

B

C

c1

a1

b1

c1

a1

b1

c1

a1

b2

c2

a1

b2

c3

1.2 Intersection (only for compatible tables)

R - S

A

B

C

R

S

A

B

C

a1

A

b2

B

C

c2

a1

b1

c1

a1

b1

c1

a1

b2

c2

a1

b2

c3

1.3 Difference (only for compatible tables)

R x S

R.A

R.B

S.B

S.D

a1

b1

b1

d1

a1

b1

b1

d3

a1

b2

b1

d1

a1

b2

b1

d3

1.4 Product

R

A

B

a1

b1

a1

b2

S

B

D

b1

d1

b1

d3

• Projection

• Selection

• Join

• Division

• For a table T with the headings [A1,…,An], the projection R on attributes [A1,…,Ak], where [A1,…,Ak] belongs to (or equal to) [A1,…,An], is a table with the headings [A1,…,Ak] and all the corresponding fields values from T.

Name

Client

Staff_ID

Tony

Tony

Amanda

001

Robbie

Robbie

Smith

002

Robbie

Lily

003

2.1 Projection (cont)

T

Staff_ID

Name

Position

Client

001

Tony

Manager

Amanda

002

Robbie

Agent

Smith

003

Robbie

Agent

Lily

R := T[Safff_ID]

R:= T[Name,Client]

R:= T[Name]

• Given a table T with headings [A1,…,An], the selection (T where C) is a table S that includes all the records in T that satisfy the condition C.

• Example: Find the employee whose name is “Robbie”.

Name

Position

Client

002

Robbie

Agent

Smith

003

Robbie

Agent

Lily

Staff_ID

Name

Position

Client

002

Robbie

Agent

Smith

2.2 Selection (cont)

T

Staff_ID

Name

Position

Client

001

Tony

Manager

Amanda

002

Robbie

Agent

Smith

003

Robbie

Agent

Lily

T where name = ‘Robbie’

T where staff_id = 002

• For two tables R and S with the headings:

Head(R) = A1… An B1 … Bk Head(S) = B1… Bk C1 … Cm

The join of R and S is a table T with the headings:

Head(R) = A1… An B1 … Bk C1 … Cm.

A row t is T is and only if there two rows u in R and v in S, such that u[Bi] = v[Bi] for all i, 1 ≤ i ≤ k;

R lxl S

A

B1

B2

C

a1

b1

b1

c1

a1

b1

b1

c2

a2

b1

b2

c3

2.3 Join (cont)

A

B1

B2

a1

b1

b1

R

a1

b2

b1

a2

b1

b2

B1

B2

C

b1

b1

c1

S

b1

b1

c2

b1

b2

c3

b2

b2

c4

• For two tables R and S with the headings:

• The division R ÷ S is a table T with the headings:

• T contains exactly those rows t such that for every row s in S, the row resulting from concatenating t and s can be found in table R.

• Division is the inverse of the product operation.

• R÷S = R[A1..An] – ((R[A1..An] X S) – R)[A1..An]

Detail steps:

• Step 1 T1 := R[A1..An] (Projection)

• Step 2 T2 := T1 X S (Product)

• Step 3 T3 := T2 – R (Difference)

• Step 4 T4 := T3[A1..An] (Projection)

• Step 5 T5 := T1 – T4 (Difference)

R

S

A

B

C

C

a1

b1

c1

c1

a2

b1

c1

c2

a1

b2

c1

a1

b2

c2

a2

b1

c2

a1

b2

c3

R ÷ S = ???

a1

b2

c4

a1

b1

c5

A

B

a1

b1

a2

b1

a1

b2

2.4 Division Example – step 1

R

A

B

C

a1

b1

c1

a2

b1

c1

a1

b2

c1

a1

b2

c2

a2

b1

c2

a1

b2

c3

a1

b2

c4

a1

b1

c5

A

B

C

a1

b1

c1

a1

b1

c2

a2

b1

c1

a2

b1

c2

a1

b2

c1

a1

b2

c2

2.4 Division Example – step 2

T1

A

B

a1

b1

a2

b1

a1

b2

S

C

c1

c2

B

C

a1

b1

c2

2.4 Division Example – step 3

R

T3 := T2 - R

T2

A

B

C

A

B

C

a1

b1

c1

a1

b1

c1

a1

b1

c2

a2

b1

c1

a2

b1

c1

a1

b2

c1

a2

b1

c2

a1

b2

c2

a1

b2

c1

a2

b1

c2

a1

b2

c2

a1

b2

c3

a1

b2

c4

a1

b1

c5

T3

T4 := T3[A1..An]

A

B

C

A

B

a1

b1

c2

a1

b1

B

A

B

a2

b1

a1

b1

a1

b2

2.4 Division Example – step 5

T1

T4

T5 := T1 – T4

A

B

a1

b1

a2

b1

a1

b2

R ÷ S = T5

• Outer join

• Left outer join

• Right outer join

R lxlo S

A

B1

B2

C

a1

b1

b1

c1

R lxl S

a1

b1

b1

c2

A

B1

B2

C

a2

b1

b2

c3

a1

b1

b1

c1

a1

b2

b1

null

a1

b1

b1

c2

null

b2

b2

c4

a2

b1

b2

c3

3.1 Outer join

A

B1

B2

a1

b1

b1

R

a1

b2

b1

a2

b1

b2

B1

B2

C

b1

b1

c1

S

b1

b1

c2

b1

b2

c3

b2

b2

c4

R left outer join S

A

B1

B2

C

a1

b1

b1

c1

a1

b1

b1

c2

a2

b1

b2

c3

a1

b2

b1

null

3.1 Left outer join

A

B1

B2

a1

b1

b1

R

a1

b2

b1

a2

b1

b2

B1

B2

C

b1

b1

c1

S

b1

b1

c2

b1

b2

c3

b2

b2

c4

R right outer join S

A

B1

B2

C

a1

b1

b1

c1

a1

b1

b1

c2

a2

b1

b2

c3

null

b2

b2

c4

3.1 Right outer join

A

B1

B2

a1

b1

b1

R

a1

b2

b1

a2

b1

b2

B1

B2

C

b1

b1

c1

S

b1

b1

c2

b1

b2

c3

b2

b2

c4