CS2300: File Structures and

Introduction to Database Systems

Lecture 12: ER-to-Relational Mapping

Doug McGeehan

1

2222

Relational Algebra

• Basic operations

– Selection ()

– Projection (π)

– Cross-product (X)

– Set-difference or Minus (-)

– Union (U)

• Syntactic operation: Renaming

• Derived operations: Intersection, Join, Division

• Additional operations: enhance the expressive power– Aggregation and grouping

– Generalized projection

– Outer join

Outer Join

• NATURAL JOIN and EQUIJOIN

– Tuples without a matching tuple eliminated from join result

– Tuples with null in the join attributes also eliminated

– Result: lost information

• OUTER joins

– Keep all the tuples in R

– … or all those in S

– … or all those in both relations in the result of the join

– Disregards absence of matching tuples in other relation

3

4

Outer Join

• Left outer join ( )

– Keep every tuple in the left relation in the result

• Right outer join ( )

– Keep every tuple in the right relation in the result

• Full outer join ( )

– Keep all tuples in the left and right relations in the

result

5

Example Relations

CBA

X10020

Y30010

Z 40030

EDB

XX1100

YY3200

ZZ2100

R2

R1R1 R2

EDCBA

XX1X10020

ZZ2X10020

NULLNULLY30010

NULLNULLZ40030

5

6

Example Relations

CBA

X10020

Y30010

Z 40030

EDB

XX1100

YY3200

ZZ2100

R2

R1R1 R2

EDCBA

XX1X10020

ZZ2X10020

YY3NULL200NULL

6

7

Example Relations

CBA

X10020

Y30010

Z 40030

EDB

XX1100

YY3200

ZZ2100

R2

R1R1 R2

EDCBA

XX1X10020

ZZ2X10020

NULLNULLY30010

NULLNULLZ40030

YY3NULL200NULL

7

8

Equivalence Among

Relational Algebra Expressions

8

9

What Does Equivalence Mean?

• Expressions E1 and E2 are equivalent if expressions

always return same answer

• Regardless of the tuples in the participating relations

• Simple example: Consider the relation R(A,B,C)

The following three expressions are equivalent

A R A A,B R A A,C R

9

Why?

• Why do we care that the expressions are

equivalent?

• Mostly, for optimizations.

• Which expression is the most efficient?

1010

11

Question

• Suppose that we have R(A,B) and S(A,B)

• Is A R A S equivalent to A (R S) ?

• Is A R - A S equivalent to A (R - S) ?

11

1212

Expressions in a Single Assignment

• Precedence of relational operators:1. Unary operators: highest precedence,

bind first

2. Then come products and joins

3. Then intersection

4. Finally, union and set difference bind last

• You can always insert parentheses to

force a desired order

13

Query Trees

• A query tree: a tree data structure that

corresponds to a relational algebra

expression

• Leaf nodes are operands – input relations.

• Interior nodes are operators, applied to their

child or children.

1313

14

Example

• Consider the relations

Bars(name, addr)

Sells(bar, beer, price).

• Find the names of all the bars that are

either on `Maple St.’ or sell `Bud’ for

less than $3.

1414

1515

Bars Sells

addr = “Maple St.” price<3 AND beer=“Bud”

π name π bar

U

Example

(π name(addr = “Maple St.” Bars)) U (π bar (price<3 AND beer=“Bud” Sells))

15

ER-to-Relational Mapping

16

17171717

Brief Review

• ER and EER model– Entity

– Weak entity

– Relationship

– Superclass / subclass

Mapping of Regular Entity Types

• Entity sets become relations with the

same set of attributes.

– Primary key of the entity set becomes

primary key of the relation

– Composite attributes: keep its simple

component attributes in the relation

– Multivalued attributes: create a new

relation for each individual attribute

18

19

Entity Set -> Relation

name manf

Miller xxx

Bud yyy

Beers

name manf Beers

Relation: Beers(name, manf)

20

Example

Students

IDname

phone

height

FnMn

Ln

PreviousDegrees

college year degree field

Students(ID,height,Fn,Mn,Ln)

Students2(ID,phone)

Students3(ID,college,year,

degree,field)

Relation Schema

20

21

Multivalued attributes

ID Phone

001 1235

001 2243

002 1093

003 3417

003 4501

Students2• For each multivalued attribute, create a

new relation R.

• The attributes in the new relation R include

the primary key of the entity set and the

multivalued attribute.

• The primary key of R is the combination of

all attributes in R.

• Need to specify the foreign key constraint!

22

Weak Entity -> Relation

Crew Studio

number studionamename

1WorkFor

N

address

year

Studio(studioname,address, year)

Crew(studioname, address, number, name)

22

23

Mapping of Weak Entity Types

• Weak entity attribute mapping is similar to regular

entity attribute mapping

• What’s different: must include the primary key of

its owner entity type

• The primary key of each relation of the weak

entity type is the combination of

– The primary key(s) of the owner(s) and

– The partial key of the weak entity type

Mapping of Relationship Set

2424

25

Relationship Set -> Relation

Likes (Drinker, Beer, comment)

25

Drinker Beer

name addr name manf

comment

M NLIKES

26

Mapping of Relationship Set

• General approach:

– A relationship becomes a relation R whose

attributes are only:

• The primary keys of the connected entity sets.

– May need to rename some attributes.

• Attributes of the relationship itself.

– The primary key of the relation R: combo of the

primary keys of the participating entities

– Need to specify foreign keys!

26

27

Multiway Relationship Set

Bar Drinker Beer

Joe’s Bar Ann Miller

Sue’s Bar Ann Bud

Sue’s Bar Ann Pete’s Ale

Joe’s Bar Bob Bud

Joe’s Bar Bob Miller

Joe’s Bar Cal Miller

Sue’s Bar Cal Bud Lite

Bar Beer

Drinker

name nameaddr manf

name addr

license

Preferences

Preferences

Preferences(Bar, Drinker, Beer)

27

Mapping of Super/Subclass

28

Mapping of Super/Subclass Relationship

29

Beer

name manf

Alecolor

General approach: Create a relation for each class;

include the primary key of the superclass in the

subclass.

29

name manfBud Anheuser-Busch

Summerbrew Pete’s

Ales

name colorSummerbrew dark

Beers

29

Mapping of Super/Subclass Relationship

30

Approach 2: Create relations for subclasses only.

• Only when every entity in the superclass is included in

at least one sub-class

• Need to include the attributes in the superclass into

each relation

• An entity may be duplicated in several relations

30

31

Car Truck

Number_of_passenger

weight

price License# owner

Vehicle

d

Car(License#, price, owner, Number_of_passenger)

Truck(License#, price, owner, weight)

31

Mapping of Super/Subclass Relationship

32

Approach 3: Create a single relation including all

attributes in the superclass and the subclasses.

• Add one or more type attributes to indicate the subclass to which

each tuple belongs to

– e.g. Student(grade), grade = “Freshman” or “Sophomore” or ...

• Primary key of the relation is the primary key of the superclass

• Consequence: many NULL values if many specific

attributes exist across subclasses

32

33

Car Truck

Number_of_passenger

weight

price License# owner

Vehicle

d

Vehicle (License#, price, owner,

Number_of_passenger, weight, type)

33

34

Some Issues

• Shared subclasses: use any of the three

approaches

– Its superclasses must all have the same

primary keys

• Multi-level specialization:

– Do not need to follow the same mapping

option for all the specializations

Example

35

36

One Possible Mapping

course