2. Projection, selection and join operations. Witold Rekuć Data Processing Technology 29

(1)

2. Projection, selection and join operations

(2)

Set theoretic operations Operations on tables

Union Projection

Relational operations

Selection

Join

Division

Intersection

Difference

Complement

(3)

Projection

Projection results in a table that has a header restricted to the given set of column names

r(R) - table r of a header R that is a projection argument

X – a header of a new table – a set of column names (X ⊆ R) q(X) =

π

^X( r (R) ) = { t[X] : t ∈ r }

where t[X] – denotes a row obtained by a restriction of the row t to values of X R

X

(4)

Example

A B a5 b2 a6 b4 a1 b2 A B C D

a5 b2 c1 d1 a6 b4 c5 d2 a6 b4 c1 d3 a6 b4 c1 d2 a1 b2 c2 d1

π

^{A,B}( r ({A,B,C,D}) ) = q({A,B}) r({A,B,C,D})

C D c1 d1 c5 d2 c1 d3 c1 d2 c2 d1

π

^{C,D}( r ({A,B,C,D}) ) = q({C,D})

(5)

q

π

{Name, Telephone}

256 12 234 55 234 23 Telephone

Dolmed Iase Pafawag Audi Abeja

Our Company Name

Example

Give names and telephone numbers of all companies

q({Name, Telephone}) =

π

{Name, Telephone} (Companies ({Id, Name, Street, City Id, Telephone}))

rows restricted to X={Name, Telephone}

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Medical Street Energetic Lane Wagon Place Audi Blvd.

Abejow Street Our Street Street

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

Companies

(6)

(7)

Example:

Give city ids of the companies

q({CityId}) = π _{{City Id}}(Company ({Id,Name,Street,City Id,Telephone}))

(8)

q

1 3 2 2 3 1 City Id

π

^{{City Id}}

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

Companies

Why there are duplictes of rows in the table q?

(9)

(10)

Example:

Give unique list of city ids of the companies

q({CityId}) = π _{{City Id}}(Company ({Id,Name,Street,City Id,Telephone}))

(11)

q

π

^{KodMjc}

2 3 1 City Id

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Medical Street Energetic Lane Wagon Place Audi Blvd.

Abejow Street Our Street Street

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

Companies

(12)

Example

Having a table MonthTurnover we would like to calculate an average value of the month turnover.

q({TurnOver}) =

π

^{Obrót}( MonthTurnover ({Month,Turnover}) )

100 300 200 200 100 100 Turnover

June Mai April March February January Month

MonthTurnover

100 300 200 200 100 100 Turnover

π

^{Turnover} Leave duplicates

in the result table

q

Avg1 = (100+100+200+200+300+100)/6 = 166,67 Avg2 = (200+300+100)/3 = 200

(13)

Expressing projection by means of SQL

select Name, Telephone from Companies;

select distinct Name, Telephone from Companies;

select *

from Companies;

(14)

Selection

Selection results in a table that has a header equal to the header of the

argument table but with rows restricted to those satisfying a given condition r(R) – table r of a header R that is a selection argument

F – selection condition (logical expression) q(R) =

σ

^F( r (R) ) = { t : t ∈ r i F(t) = true }

rows satisfying the selection condition

(15)

Przykład

A B C D a5 2 a1 1

a6 4 a5 2 a6 4 a1 1 a6 4 a1 2 a1 2 a1 1

r({A,B,C,D}) A B C D

a6 4 a5 2 a6 4 a1 1 a6 4 a1 2

σ

⁽ r ({A,B,C,D}) ) = q ({A,B,C,D})

B=4

A B C D a6 4 a1 1

σ

⁽ r ({A,B,C,D}) ) = q ({A,B,C,D})

B=4 and D<2

σ

⁽ r ({A,B,C,D}) ) = q ({A,B,C,D})

B=4 or

D<2 A B C D a5 2 a1 1 a6 4 a5 2 a6 4 a1 1 a6 4 a1 2 a1 2 a1 1

(16)

MP Kraków

6

SL Katowice

5

MZ Warszawa

4

DL OP DL Prov Id

Wałbrzych 3

Opole 2

Wrocław 1

Name Id

Cities Example

Give the list of cities from the province “DL”

q({Id,Name,Prov Id}) =

σ

Prov Id = “DL” ( Cities ({Id,Name,Prov Id}) )

σ

Prov Id = “DL”

DL DL Prov Id

Wałbrzych 3

Wrocław 1

Name Id

q

(17)

(18)

q({ ... })=

σ

(City Id=2) and (Companies({Id,Name,Street,City Id,Telephone}))

(Telephone is NULL)

(19)

q({ ... })=

σ

(City Id=2) or (Companies({Id,Name,Street,City Id,Telephone}))

(20)

Expressing selection by means of the SQL

select * from Cities

where [Prov Id] = “DL”;

select *

from Companies

where ([City Id] = 2) and (Telephone IS NULL);

select *

from Companies

where ([City Id] = 2) or (Telephone IS NULL);

(21)

Composition of projection and selection operations

q(X) =

π

^X⁽

σ

^F^{( r (R) )}

Pafawag Audi Name

q

π

^{Name}⁽

σ

(City Id=2) and (Telephone is NULL)

Example

Give names of companies from Opole, which telephone number is unknown.

q({Name})=

π

^{Name}⁽

σ

(City id=2) and(Companies({Id,Name,Street,City id,Telephone})))

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

Companie s

(22)

q({Name})=

π

^{Name}⁽

σ

(City Id=2) and(Companies({Id,Name,Stret,City Id,Telephone})))

(23)

select Name

from Companies

where [City Id] = 2 and Telephone IS NULL;

Expressing projection and selection by means of the SQL

(24)

Join operation

The problem: we would like to get the list of companies and city names Companies

Wałbrzych 3

Opole 2

Wrocław 1

Name Id

Cities

Opolskie OP

Dolnośląskie DL

Name Id

Province

13.05.02 17.02.02 31.04.02 03.01.02 21.03.02 13.02.02 Date

4 0 1 0 3 2 BuyerId

0 4/02

3 2/02

0 3/02

1 1/02

0 2/02

0 1/02

SellerId Id

Invoices

30 200

35 125

45 220 120 300 150 200 20 Quantity

12,00 3,20 7,80 4,50 11,00

7,50 12,00 13,00 7,00 4,50 12,00 NetPrice

001 1

3 2/02

005 4

0 3/02

004 3

0 3/02

003 2

0 3/02

001 1

0 3/02

004 001 002 004 003 001 ProdId

3 2 1 1 2 1 ItemN

o

1 1/02

0 2/02

0 1/02

BuyerId DocI

d

Invoice items

7%

VatRate

kg kg Unit

Sausage 002

Pâté 001

Name id

Products

Comp2007.accdb

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

(25)

Companies

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Medical Street Energetic Lane Wagon Place Audi Blvd.

Abejow Street Our Street Street

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

DL OP DL Prov Id

Wałbrzych 3

Opole 2

Wrocław 1

Name Id

Cities

(26)

Equijoin (equi-join) operation

r(R) – table r having header R, attribute A ∈ R s(S) – table s having header S, attribute B ∈ S

R i S are disjoint, don't have common names (R∩S = Ø)

v v

r s

R S

A B

q(R∪S) = r [ A=B ] s = { t : t[R] ∈ r, t[S] ∈ s i t[A] = t[B] }

(27)

Companies [ City Id = Id ] Cities

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Medcal Street Energetic Lane Wagon Lane Audi Bvld.

Abejow Street Our Comapny Street

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

List of compnies

Wrocław

DL OP OP DL DL Cities.Name

DL Wałbrzych

Opole Opole Wałbrzych

Wrocław

Prov Id

1 3 2 2 3 1 Cities.Id

Companies

1 3 2 2 3 1 City Id

256 12 234 55 234 23 Telephone

Dolmed 5

Iase 4

Pafawag 3

Audi 2

Abeja 1

Our Company 0

Name Id

Wałbrzych 3

Opole 2

Wrocław 1

Name Id

Cities

(28)

Companies [ City Id = Id ] Cities

(29)

Companies [ ] Cities = Companies × Cities

(30)

Composition of projection, selection and equi-join operations q(X) = π

_X

( σ

_F

( r [A=B] s )

Example

π

{Invoices.Date}

( σ

Cities.Name=”Wałbrzych”

(

Invoices [Buyer Id=Id] Companies [City Id=Id ] Cities ))

(31)

Composition of projection, selection and equi-join operations

(32)

Cartesian product

(33)

Equi-join of many tables

(34)

Expressing projection, selection and equi-join by means of SQL

select Companies.Name, Cities.Name

from Companies inner join Cities on Companies.[City Id]=Cities.Id;

select Companies.Name, Cities.Name

from Companies inner join Cities on Companies.[City Id]=Cities.Id where Companies.Telephone is Null;

select Invoices.Id, Companies.Name, Invoices.Date from

(

[Invoice Items] inner join Invoices

on ([Invoice Items].[Seller Id] = Invoices.[Seller Id]) and ([Invoice Items].Id = Invoices.Id)

)

inner join Companies

on Invoices.[Seller Id] = Companies.Id;

(35)

Examples

C( Id, Name ) P ( Id, Name )

1 n1 a na

2 n2 b nb

3 n3 c nc

4 n4

In (Id, SiD, BiD,Date) InI ( InvId, SiD, Id, PiD Pr Qua)

1 1 2 d1 1 1 1 a 10 2

2 1 3 d1 1 1 2 b 15 3

2 2 1 d2 2 1 1 a 20 2

3 1 4 d2 2 2 1 a 5 3

4 4 1 d3 2 2 2 b 7 2

2 2 3 c 4 5

3 1 1 a 5 2

3 1 2 c 10 5

4 4 1 b 22 2

4 4 2 c 10 5

(36)

Compute:

1)

π

^{Date}

^(In)

2)

σ

SiD=1 and BiD=2

(In), σ

SiD=1 or BiD=2

(In)

3)

π

^{NrD,NrS}

⁽ σ

^Smb=a

^(InI))

4)

In [SiD=Id] C, In [BiD=Id] C, In [Id=InvId, SiD=SiD] InI

Write expressions for the following queries given in the natural language:

1) Give names of the companies, which bought something on 'd1'.

2) Give names of the products bought by the company of the name 'n3'.

3) Give dates at which product of the name 'na' was bought.

4) Give the names of products that was bought at price > 2.