[PDF] - Faloutsos CMU SCS 15-415 CMU SCS Carnegie Mellon Univ. School of PDF Document

SLIDE 1

Faloutsos CMU SCS 15-415 1

CMU SCS

Carnegie Mellon Univ. School of Computer Science 15-415 - Database Applications

C. Faloutsos

Lecture #4: Relational Algebra

CMU SCS

Faloutsos CMU SCS 15-415 #2

Overview

history
concepts
Formal query languages

– relational algebra – rel. tuple calculus – rel. domain calculus

CMU SCS

Faloutsos CMU SCS 15-415 #3

History

before: records, pointers, sets etc
introduced by E.F. Codd in 1970
revolutionary!
first systems: 1977-8 (System R; Ingres)
Turing award in 1981

SLIDE 2

Faloutsos CMU SCS 15-415 2

CMU SCS

Faloutsos CMU SCS 15-415 #4

Concepts - reminder

Database: a set of relations (= tables)
rows: tuples
columns: attributes (or keys)
superkey, candidate key, primary key

CMU SCS

Faloutsos CMU SCS 15-415 #5

Example

Database:

CMU SCS

Faloutsos CMU SCS 15-415 #6

Example: cont’d

Database:

rel. schema (attr+domains)

tuple k-th attribute (Dk domain)

SLIDE 3

Faloutsos CMU SCS 15-415 3

CMU SCS

Faloutsos CMU SCS 15-415 #7

Example: cont’d

rel. schema (attr+domains)

instance

CMU SCS

Faloutsos CMU SCS 15-415 #8

Example: cont’d

rel. schema (attr+domains)

instance

Di: the domain of the i-th attribute (eg., char(10)

CMU SCS

Faloutsos CMU SCS 15-415 #9

Overview

history
concepts
Formal query languages

– relational algebra – rel. tuple calculus – rel. domain calculus

SLIDE 4

Faloutsos CMU SCS 15-415 4

CMU SCS

Faloutsos CMU SCS 15-415 #10

Formal query languages

How do we collect information?
Eg., find ssn’s of people in 415
(recall: everything is a set!)
One solution: Rel. algebra, ie., set operators
Q1: Which ones??
Q2: what is a minimal set of operators?

CMU SCS

Faloutsos CMU SCS 15-415 #11

.
.
.
set union U
set difference ‘-’

Relational operators

CMU SCS

Faloutsos CMU SCS 15-415 #12

Example:

Q: find all students (part or full time)
A: PT-STUDENT union FT-STUDENT

SLIDE 5

Faloutsos CMU SCS 15-415 5

CMU SCS

Faloutsos CMU SCS 15-415 #13

Observations:

two tables are ‘union compatible’ if they

have the same attributes (‘domains’)

Q: how about intersection

U

CMU SCS

Faloutsos CMU SCS 15-415 #14

Observations:

A: redundant:
STUDENT intersection STAFF =

STUDENT STAFF

CMU SCS

Faloutsos CMU SCS 15-415 #15

Observations:

A: redundant:
STUDENT intersection STAFF =

STUDENT STAFF

SLIDE 6

Faloutsos CMU SCS 15-415 6

CMU SCS

Faloutsos CMU SCS 15-415 #16

Observations:

A: redundant:
STUDENT intersection STAFF =

STUDENT - (STUDENT - STAFF)

STUDENT STAFF

CMU SCS

Faloutsos CMU SCS 15-415 #17

Observations:

A: redundant:
STUDENT intersection STAFF =

STUDENT - (STUDENT - STAFF) Double negation: We’ll see it again, later…

CMU SCS

Faloutsos CMU SCS 15-415 #18

.
.
.
set union
set difference ‘-’

Relational operators

U

SLIDE 7

Faloutsos CMU SCS 15-415 7

CMU SCS

Faloutsos CMU SCS 15-415 #19

Other operators?

eg, find all students on ‘Main street’
A: ‘selection’

CMU SCS

Faloutsos CMU SCS 15-415 #20

Other operators?

Notice: selection (and rest of operators)

expect tables, and produce tables (-> can be cascaded!!)

For selection, in general:

CMU SCS

Faloutsos CMU SCS 15-415 #21

Selection - examples

Find all ‘Smiths’ on ‘Forbes Ave’

‘condition’ can be any boolean combination of ‘=‘, ‘>’, ‘>=‘, ...

SLIDE 8

Faloutsos CMU SCS 15-415 8

CMU SCS

Faloutsos CMU SCS 15-415 #22

selection
.
.
set union
set difference R - S

Relational operators

R U S

CMU SCS

Faloutsos CMU SCS 15-415 #23

selection picks rows - how about columns?
A: ‘projection’ - eg.:

finds all the ‘ssn’ - removing duplicates

Relational operators

CMU SCS

Faloutsos CMU SCS 15-415 #24

Cascading: ‘find ssn of students on ‘forbes ave’

Relational operators

SLIDE 9

Faloutsos CMU SCS 15-415 9

CMU SCS

Faloutsos CMU SCS 15-415 #25

selection
projection
.
set union
set difference R - S

Relational operators

R U S

CMU SCS

Faloutsos CMU SCS 15-415 #26

Are we done yet? Q: Give a query we can not answer yet!

Relational operators

CMU SCS

Faloutsos CMU SCS 15-415 #27

A: any query across two or more tables,

eg., ‘find names of students in 15-415’

Q: what extra operator do we need??

Relational operators

SLIDE 10

Faloutsos CMU SCS 15-415 10

CMU SCS

Faloutsos CMU SCS 15-415 #28

A: any query across two or more tables,

eg., ‘find names of students in 15-415’

Q: what extra operator do we need?? A: surprisingly, cartesian product is enough!

Relational operators

CMU SCS

Faloutsos CMU SCS 15-415 #29

Cartesian product

eg., dog-breeding: MALE x FEMALE
gives all possible couples

x =

CMU SCS

Faloutsos CMU SCS 15-415 #30

so what?

Eg., how do we find names of students taking

415?

SLIDE 11

Faloutsos CMU SCS 15-415 11

CMU SCS

Faloutsos CMU SCS 15-415 #31

Cartesian product

A:

CMU SCS

Faloutsos CMU SCS 15-415 #32

Cartesian product

CMU SCS

Faloutsos CMU SCS 15-415 #33

SLIDE 12

Faloutsos CMU SCS 15-415 12

CMU SCS

Faloutsos CMU SCS 15-415 #34

selection
projection
cartesian product MALE x FEMALE
set union
set difference R - S

FUNDAMENTAL Relational operators

R U S

CMU SCS

Faloutsos CMU SCS 15-415 #35

Relational ops

Surprisingly, they are enough, to help us

answer almost any query we want!!

derived/convenience operators:

– set intersection – join (theta join, equi-join, natural join) – ‘rename’ operator – division

CMU SCS

Faloutsos CMU SCS 15-415 #36

Joins

Equijoin:

SLIDE 13

Faloutsos CMU SCS 15-415 13

CMU SCS

Faloutsos CMU SCS 15-415 #37

Cartesian product

A:

CMU SCS

Faloutsos CMU SCS 15-415 #38

Joins

Equijoin:
theta-joins:

generalization of equi-join - any condition

CMU SCS

Faloutsos CMU SCS 15-415 #39

Joins

very popular: natural join: R S
like equi-join, but it drops duplicate

columns: STUDENT (ssn, name, address) TAKES (ssn, cid, grade)

SLIDE 14

Faloutsos CMU SCS 15-415 14

CMU SCS

Faloutsos CMU SCS 15-415 #40

Joins

nat. join has 5 attributes

equi-join: 6

CMU SCS

Faloutsos CMU SCS 15-415 #41

Natural Joins - nit-picking

if no attributes in common between R, S:
nat. join -> cartesian product

CMU SCS

Faloutsos CMU SCS 15-415 #42

Overview - rel. algebra

fundamental operators
derived operators

– joins etc – rename – division

examples

SLIDE 15

Faloutsos CMU SCS 15-415 15

CMU SCS

Faloutsos CMU SCS 15-415 #43

Rename op.

Q: why?
A: shorthand; self-joins; …
for example, find the grand-parents of

‘Tom’, given PC (parent-id, child-id)

CMU SCS

Faloutsos CMU SCS 15-415 #44

Rename op.

PC (parent-id, child-id)

CMU SCS

Faloutsos CMU SCS 15-415 #45

Rename op.

first, WRONG attempt:
(why? how many columns?)
Second WRONG attempt:

SLIDE 16

Faloutsos CMU SCS 15-415 16

CMU SCS

Faloutsos CMU SCS 15-415 #46

Rename op.

we clearly need two different names for the

same table - hence, the ‘rename’ op.

CMU SCS

Faloutsos CMU SCS 15-415 #47

Overview - rel. algebra

fundamental operators
derived operators

– joins etc – rename – division

examples

CMU SCS

Faloutsos CMU SCS 15-415 #48

Division

Rarely used, but powerful.
Example: find suspicious suppliers, ie.,

suppliers that supplied all the parts in A_BOMB

SLIDE 17

Faloutsos CMU SCS 15-415 17

CMU SCS

Faloutsos CMU SCS 15-415 #49

Division

CMU SCS

Faloutsos CMU SCS 15-415 #50

Division

Observations: ~reverse of cartesian product
It can be derived from the 5 fundamental
perators (!!)
How?

CMU SCS

Faloutsos CMU SCS 15-415 #51

Division

Answer:
Observation: find ‘good’ suppliers, and

subtract! (double negation)

SLIDE 18

Faloutsos CMU SCS 15-415 18

CMU SCS

Faloutsos CMU SCS 15-415 #52

Division

Answer:
Observation: find ‘good’ suppliers, and

subtract! (double negation)

CMU SCS

Faloutsos CMU SCS 15-415 #53

Division

Answer:

All suppliers All bad parts

CMU SCS

Faloutsos CMU SCS 15-415 #54

Division

Answer:

all possible suspicious shipments

SLIDE 19

Faloutsos CMU SCS 15-415 19

CMU SCS

Faloutsos CMU SCS 15-415 #55

Division

Answer:

all possible suspicious shipments that didn’t happen

CMU SCS

Faloutsos CMU SCS 15-415 #56

Division

Answer:

all suppliers who missed at least one suspicious shipment, i.e.: ‘good’ suppliers

CMU SCS

Faloutsos CMU SCS 15-415 #57

Overview - rel. algebra

fundamental operators
derived operators

– joins etc – rename – division

examples

SLIDE 20

Faloutsos CMU SCS 15-415 20

CMU SCS

Faloutsos CMU SCS 15-415 #58

Sample schema

find names of students that take 15-415

CMU SCS

Faloutsos CMU SCS 15-415 #59

Examples

find names of students that take 15-415

CMU SCS

Faloutsos CMU SCS 15-415 #60

Examples

find names of students that take 15-415

SLIDE 21

Faloutsos CMU SCS 15-415 21

CMU SCS

Faloutsos CMU SCS 15-415 #61

Sample schema

find course names of ‘smith’

CMU SCS

Faloutsos CMU SCS 15-415 #62

Examples

find course names of ‘smith’

CMU SCS

Faloutsos CMU SCS 15-415 #63

Examples

find ssn of ‘overworked’ students, ie., that

take 412, 413, 415

SLIDE 22

Faloutsos CMU SCS 15-415 22

CMU SCS

Faloutsos CMU SCS 15-415 #64

Examples

find ssn of ‘overworked’ students, ie., that

take 412, 413, 415: almost correct answer:

CMU SCS

Faloutsos CMU SCS 15-415 #65

Examples

find ssn of ‘overworked’ students, ie., that

take 412, 413, 415 - Correct answer:

c-name=413 c-name=415

CMU SCS

Faloutsos CMU SCS 15-415 #66

Examples

find ssn of students that work at least as

hard as ssn=123, ie., they take all the courses of ssn=123, and maybe more

SLIDE 23

Faloutsos CMU SCS 15-415 23

CMU SCS

Faloutsos CMU SCS 15-415 #67

Sample schema

CMU SCS

Faloutsos CMU SCS 15-415 #68

Examples

find ssn of students that work at least as

hard as ssn=123 (ie., they take all the courses of ssn=123, and maybe more

CMU SCS

Faloutsos CMU SCS 15-415 #69

Conclusions

Relational model: only tables (‘relations’)
relational algebra: powerful, minimal: 5
perators can handle almost any query!