Databases. TDDB77 Lecture 4. ER to Relations. Todays lecture. ER to Relations. ER to Relations

Databases User 4 Real World TDDB77 Lecture 4 Model Database Database management system Part 1: Translation between models. Part 2: Joins - append...
Author: Aldous Warner
0 downloads 0 Views 30KB Size
Databases User 4

Real World

TDDB77 Lecture 4

Model

Database Database management system

Part 1: Translation between models. Part 2: Joins - appendix to the relational models

User Queries 3 Updates Answers User Queries 2 Updates Answers User Queries 1 Updates Answers Updates Queries Answers

Processing of queries and updates

Access to stored data

Paweá Pietrzak Physical database

Todays lecture

ER to Relations

• Translation from ER to Relations • Translation from EER to relations • Joins

ER to Relations

ER to Relations

Step 1

Step 2

For each (strong) entity R, create a relation E that has the same simple attributes as R.

For each weak entity W with owner entity E, create a relation R that has the same simple attributes as W, also add the primary key attributes from the relation that corresponds to E.

1

ER to Relations

ER to Relations

Step 3

Step 4

For each binary 1:1 relation, identify the relations that corresponds to the incoming entities, S and T. Choose one of these and add the primary key from one of them as foreign key in the other.

For each binary 1:N relation, identify the relations that correspond to the incoming entities, S and T. Choose the relation on the relation on the 1 side and add its primary key as foreign key in the relation on the N side.

ER to Relations

ER to Relations

Step 5

Step 6

For each binary N:M relation, identify the relations that correspond to the incoming entities, S and T. Create a new relation R and use the primary keys from S and T as foreign keys in R. If there are attributes on the relation these are also added to R.

For each complex attribute A in R, create a new relation R that contains an attribute for each attribute in A and the primary key for R as foreign key.

ER to Relations

Example Startdate

Address

ENr

1

Step 7

1

Employee

Sex

1

Name GName

Relat. to N

Sex

Relative Name

Supervises

M

Works at

1

FName

Name

N N

For each n-ary relation with n>2, create a new relation S that contains the primary keys from the incoming relations as foreign keys.

NrEmploy

Manages

Department

Number

1 N

Hours Controls

Works with M

N

Project

Name

Address

2

Translate strong entities • Employee • Department • Project Employee: ENr

Gname

Departm:

Name

Project:

Name

Translate weak entities • Relative

FName Address Sex

Employee: ENr

Number

Relative:

Binary 1:1 relations • Manages - Employee and Department

Employee: ENr Departm:

Gname

ENr

Name

FName Address Sex Address Sex

Binary 1:N relations • Supervises - Employee and Employee • Controls - Department and Project

GName FName Address Sex

Name

Number

Manag.

Startdate

Foreign key to the Employee relation

Binary 1:N relations • Supervises - Employee and Employee

Employee: ENr

GName FName Address Sex

Superv. by

Binary 1:N relations • Controls - Department and Project

Departm:

Name

Number

Project:

Name

ContrBy

Manag.

Startdate

3

Binary N:M relations • Works at - Employee and Department • Works with - Employee and Project

WorksAt:

Employ

WorksWith: Employ

Department Project

Hours

Complete relational schema •

Employee: ENr

GName FName Address Sex

Departm:

Name

Number

Project:

Name

ContrBy Name

Man.

Relative:

Enr

WorksAt:

Employ.

Departm.

WorksFor:

Employ.

Project

SupervisedBy

Startdate

GName FName Address Sex

Hours

EER to relations

EER to relations

Assume that we have a superclass C with a number of subclasses Si

Assume that we have a superclass C with a number of subclasses Si

– Four options for translation

Example for option 1 Employee:

ENr

Technician:

ENr

Comp.

ENr

Lang.

Administrator:

Teleph. Salary

Option 1: Create a relation R for C with all attributes in C. Then create a relation Ri for each subclass Si with all attributes from Si and the primary key from R

EER to relations Assume that we have a superclass C with a number of subclasses Si Option 2: Create a relation for each subclass Si. The attributes in each relations are the attributes from C and the attributes from Si.

Works well for disjoint or overlapping, total or partial.

4

Example for option 2 Technician:

ENr

Teleph.

Administrator:

ENr

Teleph.

Salary

EER to relations Assume that we have a superclass C with a number of subclasses Si

Comp.

Salary

Lang.

Option 3: For disjoint subclasses. Create a relation R with all attributes from C and all the subclasses Si. Also add a type attribute, used to discriminate between subclasses.

Works well only for disjoint and total. Why?

Example for option 3

EER to relations Assume that we have a superclass C with a number of subclasses Si

Employee:

ENr

Teleph.

Salary

JobType Comp.

Lang.

Option 4: Create a relation R with all attributes from C and all the subclasses S i. Also add a flag Fi attribute to R for each subclass Si. Good for disjoint and total or partial. Why?

Example for option 4

Join operations • Cartesian product • (Inner) Join

Employee:

ENr

Teleph.

Salary

TFlag

Comp.

AFlag

Lang.

– EquiJoin – Natural Join – Theta Join

• Outer Join – Left, Right or Full Good for disjoint or overlapping, total or partial.

5

Cartesian product

Cartesian product, x

A

A’

A’

D’

A

A’

B’

E’

A

A’

A’

D’

A

A’

C’

F’

B

B’

B’

E’

B

B’

A’

D’

C

X’

C’

F’

B

B’

B’

E’

B

B’

C’

F’

C

X’

A’

D’

C

X’

B’

E’

C

X’

C’

F’

Inner Join

Inner Join

Join-condition:: Attribute 2 in relation 1 should match up attribute 1 in relation 2.

A

A’

A’

D’

A

A’

A’

D’

B

B’

B’

E’

B

B’

B’

E’

C

A’

C’

F’

C

A’

A’ D’

Join - example Key

Join - example City

Name

STATE

Los Angeles

Calif

Key

5 San Fransisco

City

Los Angeles

Calif

7 Oakland

Los Angeles

Calif

8 Boston

Name

STATE

Los Angeles

Calif

5 San Fransisco

Oakland

Calif

7 Oakland

Oakland

Calif

5 San Fransisco

Atlanta

Ga

8 Boston

Oakland

Calif

7 Oakland

San Fransisco

Calif

Oakland

Calif

8 Boston

Boston

Mass

Atlanta

Ga

5 San Fransisco

Atlanta

Ga

7 Oakland

Atlanta

Ga

8 Boston

Condition: Name = City (equi-join)

San Fransisco Calif

5 San Fransisco

San Fransisco Calif

7 Oakland

San Fransisco Calif

8 Boston

Boston

Mass

5 San Fransisco

Boston

Mass

7 Oakland

Boston

Mass

8 Boston

6

Join - example Name

STATE

Key

Los Angeles

Calif

5 San Fransisco

Los Angeles

Calif

7 Oakland

Los Angeles

Calif

8 Boston

Oakland

Calif

5 San Fransisco

Oakland

Calif

7 Oakland

Oakland

Calif

8 Boston

Atlanta

Ga

5 San Fransisco

Atlanta

Ga

7 Oakland

Atlanta

Ga

8 Boston

Join - example

City

San Fransisco Calif

5 San Fransisco

San Fransisco Calif

7 Oakland

San Fransisco Calif

8 Boston

Boston

Mass

5 San Fransisco

Boston

Mass

7 Oakland

Boston

Mass

8 Boston

STATE

Oakland

Calif

Key

Oakland

Calif

City

7 Oakland

5 San Fransisco

Mass

Boston

8 Boston

Join - example of theta join

City

5 San Fransisco

Mass

8 Boston

Name

Join - example of theta join Area

Key

City

Key

Area

City

Los Angeles

2

5 San Fransisco

Oakland

9

7 Oakland

Atlanta

7

8 Boston

San Fransisco

11

Boston

16

Natural join – equi-join with one copy of the duplicated column eliminated

Name

Key

7 Oakland

San Fransisco Calif Boston

STATE

San Fransisco Calif

Join - example Name

Name

Condition: Area