Relational algebra
Download
1 / 27

Relational Algebra - PowerPoint PPT Presentation


  • 213 Views
  • Uploaded on

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.

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

PowerPoint Slideshow about 'Relational Algebra' - dyllis


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

Relational Algebra

NEU – CCIS – CSU430

Tony


Content
Content

  • Set-theoretic operations

  • Native relational operations

  • Other join operations


1 set theoretic operations
1. Set-theoretic operations

  • Union

  • Intersection

  • Difference

  • Product


1 0 compatible tables

R

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.


1 1 union only for compatible tables

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)


1 2 intersection 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)


1 3 difference 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)


1 4 product

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


2 native relational operations
2. Native relational operations

  • Projection

  • Selection

  • Join

  • Division


2 1 projection
2.1 Projection

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


2 1 projection cont

Name

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]


2 2 selection
2.2 Selection

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


2 2 selection cont

Staff_ID

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


2 3 join
2.3 Join

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


2 3 join cont

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


2 4 division
2.4 Division

  • For two tables R and S with the headings:

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

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

    Head(R) = A1… An

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


2 4 division cont
2.4 Division (cont)

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


2 4 division example
2.4 Division Example

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


2 4 division example step 1

T1 := R[A1..An]

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


2 4 division example step 2

T2 := T1 X S

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


2 4 division example step 3

A

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


2 4 division example step 4
2.4 Division Example – step 4

T3

T4 := T3[A1..An]

A

B

C

A

B

a1

b1

c2

a1

b1


2 4 division example step 5

A

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


3 other join operations
3. Other join operations

  • Outer join

  • Left outer join

  • Right outer join


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


3 1 left outer join

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


3 1 right outer join

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