- 213 Views
- Uploaded on

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

Content

- Set-theoretic operations
- Native relational operations
- Other join operations

1. Set-theoretic 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 ProductR

A

B

a1

b1

a1

b2

S

B

D

b1

d1

b1

d3

2. Native relational operations

- Projection
- Selection
- Join
- Division

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.

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

- 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

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;

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

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

- 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

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

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

A

B

a1

b1

a2

b1

a1

b2

S

C

c1

c2

B

C

a1

b1

c2

2.4 Division Example – step 3R

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

B

A

B

a2

b1

a1

b1

a1

b2

2.4 Division Example – step 5T1

T4

T5 := T1 – T4

A

B

a1

b1

a2

b1

a1

b2

R ÷ S = T5

3. Other join operations

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

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 joinA

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 joinA

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

Download Presentation

Connecting to Server..