set operators

36
RELATIONAL SET OPERATORS Prepared by: Michelle A. Ogana Ronnjemmele Rivera Jane Allyza Catalla Harry Ochinang 1

Upload: manuel-s-enverga-university-foundation

Post on 15-Dec-2014

435 views

Category:

Technology


4 download

DESCRIPTION

 

TRANSCRIPT

Page 1: Set  operators

1

RELATIONAL SET OPERATORS

Prepared by:

Michelle A. Ogana

Ronnjemmele Rivera

Jane Allyza Catalla

Harry Ochinang

Page 2: Set  operators

2

Relational Set Operators This section describes the basic data

manipulation

Relational Algebra

Defines the theoretical way of

manipulating table contents using the

eight relational operators.

Page 3: Set  operators

3

Eight Relational Operators

SELECT PROJECT JOIN INTERSECT UNION DIFFERENCE PRODUCT DIVIDE

Page 4: Set  operators

4

The relational operators have the property of closure; that is, the use of relational algebra operators on existing relations (tables) produces new relations.

There is no need to examine the mathematical definitions, properties and characteristics of those relational algebra operators.

Page 5: Set  operators

5

S E L E C T

Also known as RESTRICT Yields values for all rows found in a

table. Used to list all of the row values, or

can yield only those row values that match a specified criterion.

SELECT yields a horizontal subset of a table.

Page 6: Set  operators

6

P_CODE P_DESCRIPT PRICE123456 Flashlight $5.26

123457 Lamp $25.15

123458 Box Fan $10.99

123459 9V Battery $1.29

254688 100W Bulb $1.47

311452 Powerdrill $34.99

ORIGINAL TABLE

SELECT ALL yields

P_CODE P_DESCRIPT PRICE

123456 Flashlight $5.26

123457 Lamp $25.15

123458 Box Fan $10.99

123459 9V Battery $1.29

254688 100W Bulb $1.47

311452 Powerdrill $34.99

SELECT only PRICE less than $2.00 yields

SELECT only P_CODE = 311452 yields

P_CODE P_DESCRIPT PRICE

123459 9V Battery $1.29

254688 100W Bulb $1.47

P_CODE P_DESCRIPT PRICE

311452 Powerdrill $34.99

Page 7: Set  operators

7

P R O J E C T

Yields all values for selected attributes.

It yields a vertical subset of a table.

Page 8: Set  operators

8

P_CODE P_DESCRIPT PRICE

123456 Flashlight $5.26

123457 Lamp $25.15

123458 Box Fan $10.99

123459 9V Battery $1.29

254688 100W Bulb $1.47

311452 Powerdrill $34.99

ORIGINAL TABLE

PROJECT PRICE yields

PRICE

$5.26

$25.15

$10.99

$1.92

$1.47

$34.99

NEW TABLE

P_DESCRIPT PRICE

Flashlight $5.26

Lamp $25.15

Box Fan $10.99

9v battery $1.92

100w bulb $1.47

Powerdrill $34.99

PROJECT P_DESCRIPT and PRICE yields

P_CODE PRICE

123456 $5.26

123457 $25.15

123458 $10.99

213345 $1.92

254467 $1.47

311452 $34.99

PROJECT P_CODE and PRICE yields

Page 9: Set  operators

9

U N I O N

Combines all rows from two tables, excluding duplicate rows.

To be used in UNION, tables must have the same attribute characteristics

Columns, and domains must be compatible When two or more tables share the same

number of columns, and when their corresponding columns share the same domains, they are said to be UNION-COMPATIBLE.

Page 10: Set  operators

10

P_CODE P_DESCRIPT PRICE

123456 Flashlight $5.26

123457 Lamp $25.15

123458 Box Fan $10.99

123459 9V Battery $1.29

254688 100W Bulb $1.47

311452 Powerdrill $34.99

UNION P_CODE P_DESCIPT PRICE

345678 Microwave 160.00

345679 Dishwasher 500.00

P_CODE P_DESCRIPT PRICE

123456 Flashlight $5.26

123457 Lamp $25.15

123458 Box Fan $10.99

254688 9V Battery $1.29

524789 100W Bulb $1.47

311452 Powerdrill $34.99

345678 Microwave $ 160

345679 Dishwasher $ 500

yields

Page 11: Set  operators

11

I N T E R S E C T Yields only the rows that appear in

both tables. As was true in the case of UNION, the

tables must be union-compatible to yield valid results.

For example, you cannot use INTERSECT if one of the attributes is numeric and one is character-based.

Page 12: Set  operators

12

STU_FNAME STU_LNAME

George Jones

Jane Smith

Peter Robinson

Franklin Johnson

Martin Lopez

INTERSECT

EMP_FNAME EMP_LNAME

Franklin Lopez

William Turner

Franklin Johnson

Susan Rogers

STU_FNAME STU_LNAME

Franklin Johnson

yields

Page 13: Set  operators

13

D I F F E R E N C E

Yields all rows in one table that are not found in the other table;

It subtracts one table form the other. Tables must be union-compatible to

yield valid results. Note that subtracting the first table

from the second table is not the same as subtracting the second table from the first table.

Page 14: Set  operators

14

STU_FNAME STU_LNAME

George Jones

Jane Smith

Peter Robinson

Franklin Johnson

Martin Lopez

DIFFERENCE

EMP_FNAME EMP_LNAME

Franklin Lopez

William Turner

Franklin Johnson

Susan Rogers

STU_FNAME STU_LNAME

George Jones

Jane Smith

Peter Robinson

Martin Lopez

yields

Page 15: Set  operators

15

P R O D U C T Yields all possible pairs of rows from

two tables Also known as Cartesian product Therefore, if one table has six rows

and the other table has three rows, The PRODUCT yields a list composed

of 6 x 3 = 18 rows

Page 16: Set  operators

16

P_CODE P_DESCRIPT PRICE

123456 Flashlight $5.26

123457 Lamp $25.15

123458 Box Fan $10.99

123459 9V Battery $1.29

254688 100W Bulb $1.47

311452 Powerdrill $34.99

STORE AISLE SHELF

23 W 5

24 K 9

25 Z 6

PRODUCT

yields

Page 17: Set  operators

17

J O I N

Allows information to be combined from two or more tables.

It is the real power behind the relational database, allowing the use of independent tables linked by common attributes

The CUSTOMER and AGENT tables shown will be used to illustrate several types of joins.

Page 18: Set  operators

18

CUS_CODE CUS_LNAME CUS_ZIP AGENT_CODE

1132445 Walker 32145 321

1217782 Adares 32145 125

1312243 Rakowski 34129 167

13212442 Rodriguez 37134 125

1542311 Smithson 37134 421

1657399 Vanloo 32145 231

AGENT_CODE AGENT_PHONE

125 6152439887

167 6153426778

231 6152431124

333 9041234445

TABLE name: CUSTOMER

TABLE name: AGENT

Page 19: Set  operators

19

A natural join links tables by selecting only the rows with common values in their common attribute(s).

It is the result of a three-stage process:

Page 20: Set  operators

20

a. First, a PRODUCT of the tables is created

Page 21: Set  operators

21

b. Second, a SELECT is performed on the output

of Step A to yield only the rows for which the

AGENT_CODE values are equal.

Common columns are referred to as the join

columns.

Page 22: Set  operators

22

c. A PROJECT is performed on the results of Step

B to yield a single copy of each attribute,

thereby eliminating duplicate columns.

Page 23: Set  operators

23

The final outcome of a natural join

yields a table that does not include

unmatched pairs and provides only

the copies of the matches.

Page 24: Set  operators

24

Note a few crucial features of the natural join operation:

If no match is made between the table rows, the

new table does not include the unmatched row. In

that case, neither AGENT_CODE 421 nor the

customer whose last name is Smithson is included.

Smithson’s AGENT_CODE 421 does not match any

entry in the AGENT table.

Page 25: Set  operators

25

The column on which the join was made- that is,

AGENT_CODE- occurs only once in the new table.

If the same AGENT_CODE were to occur several times

in the AGENT table, a customer would be listed for

each match

For example, if the AGENT_CODE 167 were to occur

three times in the AGENT table, the customer name

Rakowski, who is associated with AGENT_CODE 167,

would occur three times in the resulting table

Page 26: Set  operators

26

Forms of JOIN

Links tables on the basis of an equality condition that compares specified columns of each table.

It does not eliminate duplicate columns

Condition or criterion used to join the tables must be explicitly defined.

1. EQUIJOIN

Page 27: Set  operators

27

Takes its name from the equality

comparison operator (=) used in the

condition.

This join is used if any other

comparison operator is used.

2. Theta Join

Page 28: Set  operators

28

Returns matched records from the

tables that are being joined.

3. Inner Join

Page 29: Set  operators

29

Matched pairs would be retained, and any unmatched values in the other table would be left null.

Returns all of the matched records that the inner join returns, plus it returns the unmatched records from one of the tables

3. Outer Join

Page 30: Set  operators

30

3.a. Left Outer Join

Yields all rows in the CUSTOMER

table, including those that do not

have a matching value in the AGENT

table.

Page 31: Set  operators

31

Yields all the rows in the AGENT table,

including those that do not have

matching values in the CUSTOMER

table.

3.b. Right Outer Join

Page 32: Set  operators

32

Outer joins operate like equijoins.

It does not drop one copy of the common

attribute, and it requires the specification of

the join condition.

Are especially useful when you are trying to

determine what value(s) in related tables

cause(s) referential integrity problems

Page 33: Set  operators

33

You may wonder why the outer joins

are labeled left and right.

The labels refer to the order in which

the tables are listed in the SQL

command.

Page 34: Set  operators

34

D I V I D E Uses one single-column table (e.g., column “a”)

as the divisor and one 2-column table (i.e.,

columns “a” and “b”) as the dividend.

The tables must have common column

Single column with the values of column “a” from

the dividend table rows where the value of the

common column (i.e., column “a” ) in both tables.

Page 35: Set  operators

35

CODE LOC

A 5

A 9

A 4

B 5

B 3

C 6

D 7

D 8

E 8

DIVIDE CODE

A

B

LOC

5

yields

A. Table 1 is divided by Table 2 to produce Table 3. Tables 1 & 2 both contain the column CODE but do not share LOC.

B. To be included in the resulting Table 3, a value in the unshared column (LOC( must be associated (in the dividing Table 2) with every value in Table 1.

C. The only value associated with both A and B is 5.

Page 36: Set  operators

36