sistem basis data i - bag6 - relational mapping

Skema Relasi

Entity-Relationship to

Relational Mapping

DATABASE Modules

2

Module 8: Database Systems Architecture

Fundamentals

Data

Modeling

Data

Access

Data

Design

Architecture

Module 1: Database Systems

Module 2: Relational Model

Module 3: Entity Relationship Model

Module 4: ER to Relational Mapping

Module 5: FDs and Normalization

Module 6: Relational Algebra

Module 7: SQL

Database Desain

Conceptual

perspective

User’s perspective

Database

Requirements

Logical

Schema

(Relational)

Conceptual

Design

The Entity

Relationalship (ER)

Model is one of the

most widely used

mthod for

conceptual design

Logical

Design

(Mapping)

Conceptual

Schema

(ER)

The Relational Model

is the basic for

several commercial

DBMSs

Internal

Schema

http://images.google.com/imgres?imgurl=www.abc.net.au/foreign/images/world.jpg&imgrefurl=http://www.abc.net.au/foreign/archstor.htm&h=243&w=410&prev=/images%3Fq%3Dworld%26start%3D20%26svnum%3D10%26hl%3Den%26lr%3D%26ie%3DUTF-8%26sa%3DN

Contents

Entity-Relationship to Relational Mapping

• Steps for mapping a basic ER diagram to

a relational schema

– Uses the Company database example to

illustrate the concepts

• Design choices in the ER Model and their

impact on the resulting relational schema

4

Mapping Method

Method for mapping a conceptual schema

developed using the ER model to a

relational database schema comprises 7

steps

[ CASE tools also exist for this task ]

5

Steps for Mapping

1. Entity Mapping

2. Weak Entity Mapping

3. Binary 1:1 Relationship Mapping

4. Binary 1: N Relationship Mapping

5. Binary M:N Relationship Mapping

6. Multi-valued Attribute Mapping

7. N-ary Relationship Mapping

6

7

PROJECT

EMPLOYEE

Fname

Minit

Name

Lname

Bdate

Ssn

Sex

SalaryAddress

DEPENDENT

Name Number

DEPARTMENT

MANAGES

WORKS_FOR

WORK_ON

CONTROLS

SUPERVISIONN

Relationship

Name

BirthDateSex

Name

PNumber Location

Number Of

Employee

StartDate

supervisor

supervise

1

N

N

N M

N

1

1

1

1

1

Hours

Example

ER Model:

Company

Database

DEPENDENTS_OF

Locations

8

PROJECT

EMPLOYEE

Fname

Minit

Name

Lname

Bdate

Ssn

Sex

SalaryAddress

Name Number

DEPARTMENT

MANAGES

WORKS_FOR

WORK_ON

CONTROLS

SUPERVISION

Relationship

Name

BirthDateSex

Name

PNumber Location

Number Of

Employee

StartDate

Hours

Company

Database in

alternative

notation

(1,N)(1,1)

(1,1)(0,1)

(0,N)

(1,1)(1,N)

(1,N)(0,N)

(0,1)(0,N)

(1,1)

LocationsLocations

DEPENDENTS_OF

supervisor supervise

employeedepartement

manager

Departement-

managed

worker

project

Controlling-

departement

employee

dependent

Controlled-

project

DEPENDENT

Step 1: Entity Mapping

For each regular (non-weak) entity type E, create a relation R that includes all simple attributes of E

– Include only simple component attributes of a

composite attribute

– Choose one key attribute of E as primary key

for R. If key of E is composite, the set of

simple attributes together should form the key

– Add following attributes in subsequent steps :

Foreign key, Relationship, Multi-valued

9

Step 1: Example

Entity Types in the Company Database:

EMPLOYEE, DEPARTMENT, PROJECT

10

EMPLOYEE

Fname

Minit

Name

Lname

Bdate

Ssn

Sex

SalaryAddress

EMPLOYEE [ Ssn, Fname, Minit, Lname, Bdate,

Address, Sex, Salary ]

Step 1: Example



11

DEPARTMENT [ Dnumber, Dname, DnumEmp ]

Name Number

Locations

DEPARTMENT

Number of

employee

Step 1: Example



12

PROJECT

Name

PNumber Location

PROJECT [ Pno, Pname, Plocation ]

Schema (in progress)

• EMPLOYEE [ Ssn, Fname, Minit, Lname, Bdate,


• DEPARTMENT [ Dnumber, Dname, DnumEmp ]

• PROJECT [ Pno, Pname, Plocation ]

13

Step 2: Weak Entity Mapping

For each weak entity type W with owner entity type E create a relation R that includes all simple attributes of W

– Include as foreign key attributes in R the

primary key attributes of the relation(s) that

correspond to the owner entity types. (This

maps the identifying relationship type of W)

– The primary key of R is the combination of the

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

key of the weak entity type W (if any)

14

Step 2: Example

Weak Entity Types in the Company

Database: DEPENDENT

15

DEPENDENT [ ESsn, DepName,

Sex, Birthdate, Relationship ]

where Primary Key {ESSN, DepName}

includes

• SSN, the primary key of the

EMPLOYEE relation, which is

the owner entity type, as a

foreign key attribute of

DEPENDENT (renamed ESSN)

• DepName, the partial key of

DEPENDENT

EMPLOYEE

DEPENDENTS_OF

Relationship

Name

BirthDateSex

DEPENDENTS_OF

DEPENDENT

Ssn

• EMPLOYEE [ Ssn, Fname, Minit, Lname, Bdate,


• DEPARTMENT [ Dnumber, Dname, DnumEmp ]


• DEPENDENT [ ESsn, DepName, Sex, BirthDate, Relationship ]


16

Step 3: Binary 1:1 Relationship

For each binary 1:1 relationship type (RT), identify relations S & T that correspond to the entity types participating in RT– Choose one relation (say S) and include as foreign key

in S the primary key of T

– It is better to choose as S, the entity type with

total participation in RT

– Include all the simple attributes (or simple components

of composite attributes) of the 1:1 relationship type RT

as attributes of S

17

Ssn Number

Step 3: ExampleBinary 1:1 relationship type in the Company

Database: MANAGES

18

DEPARTMENT [ Dnumber, Dname, DnumEmp, #MGRSsn, MgrStartDate ]

• DEPARTMENT serves in the role of “S” because its participation in the

MANAGES relationship type is total (every department has a manager)

• Include the primary key of the EMPLOYEE relation as a foreign key in the

DEPARTMENT relation (renamed MGRSsn)

• Include the simple attribute StartDate of the MANAGES relation (renamed

MGRStartDate)

EMPLOYEE

DEPARTMENTMANAGES

StartDate


• EMPLOYEE [ Ssn, Fname, Minit, Lname, Bdate, Address, Sex, Salary ]

• DEPARTMENT [ Dnumber, Dname, DnumEmp, #MGRSsn, MgrStartDate ]


• DEPENDENT [ ESsn, DepName, Sex, BirthDate, Relationship ]

19

Step 4: Binary 1:N Relationship

For each (non-weak) binary 1:N relationship type (RT), identify relation S that represents the participating entity type at the N-side of the relationship type

– Include as foreign key of S the primary key of

relation T that represents the other entity type

participating in RT

– Include any simple attributes (or simple

components of composite attributes) of the

1:N relationship type as attributes of S

20

Ssn Number

Step 4: ExampleBinary 1:N relationship types in the Company Database: WORKS_FOR, CONTROLS and SUPERVISION

21

EMPLOYEE [ Ssn, Fname, Minit, Lname, Bdate, Address, Sex,

Salary , #Dnumber ]

Where primary key of the DEPARTMENT relation is included

as a foreign key in the EMPLOYEE relation (rename Dnumber)

EMPLOYEE

DEPARTMENTWORK_FOR

N 1

Number

PNumber

Step 4: Example

Binary 1:N relationship types in the Company Database: WORKS_FOR, CONTROLS and SUPERVISION

22

PROJECT [ Pno, Pname, Plocation, #Dnumber ]

Where primary key of the DEPARTMENT relation

is included as a foreign key in the PROJECT

relation (renamed Dnumber)

PROJECT

DEPARTMENT

CONTROLS

N

1

Ssn SSsn

Step 4: ExampleBinary I:N relationship types in the Company Database: WORKS_FOR, CONTROLS and SUPERVISION

23

EMPLOYEE [Ssn, Fname, Minit, Lname,

Bdate, Address, Sex, Salary, #Dnumber,

#SuperSsn ]

Where primary key of the EMPLOYEE

relation is included as a foreign key within

the EMPLOYEE relation (called SuperSsn)

Note the recursive relationship!

EMPLOYEE

SUPERVISION

supervisor

supervise

N1

24

• EMPLOYEE [ Ssn, Fname, Minit, Lname, Bdate, Address, Sex, Salary, #Dnumber, #SuperSsn ]


• PROJECT [ Pno, Pname, Plocation, #Dnumber ]• DEPENDENT [ ESsn, DepName, Sex, BirthDate,

Relationship ]


Step 5: Binary M:N Relationship

For each binary M:N relationship type (RT), create a new relation S to represent RT

– Include as foreign key of S the primary keys of the

relations that represent the participating entity types in

RT

– The combination of foreign keys will form the primary key

of S (Note: cannot represent the M:N using a single

foreign key in one relation because of the M:N

cardinality ratio)

– Include any simple attributes (or simple components of

composite attributes) of the M:N relationship type as

attributes of S.

25

Ssn

Number

Step 5: Example

Binary M:N relationship type in the Company Database: WORKS_ON

26

WORKS_ON [ ESsn, Pno, Hours ]

Where WORKS_ON includes the primary

keys of the PROJECT and EMPLOYEE

relations as foreign keys

The primary key of WORKS_ON is the

combination of the foreign key attributes

(renamed to Pno and ESsn respectively)

HOURS in WORKS_ON represents the

attribute of the relationship type

Hours

EMPLOYEE

DEPARTMENT

WORK_FOR

N

N

27




Relationship ]

• WORKS_ON [ ESsn, Pno, Hours ]


More on M:N Mapping

• Note that 1:1 and 1:N relationships can be

mapped in the same way as M:N

• Advantageous when few relationship

instances exist (Sparse 1:1 Relationship)

as it reduces the number of “nulls” that

appear as foreign key values

28

Sparse 1:1 Relationship

29

PK2 NK2 PK1 as FK

Null

Null

A X

Null

B Y

Null

C Y

PK1 NK1

X

Y

PK2 NK2

A

B

C

PK1 NK1

X

Y

A X

B Y

C Y

No Nulls as

Foreign Keys

Standard Implementation M:N Implementation

Step 6: Multivalued Attributes

For each multi-valued attribute A, create a new relation R that includes an attribute corresponding to A plus the primary key K (as a foreign key of R) of the relation that represents the entity type or relationship type that has A as an attribute

– The primary key of R is the combination of

attributes A & K

– If the multi-valued attribute is composite,

include its simple components

30

Step 6: Example

Multi-valued attributes in the Company Database: Locations

31

Name Number

Locations

DEPARTMENT

DEPT_LOCS [ DNumber, Dlocation ]

Where primary key of DEPT_LOCS is the

combination of {DNumber, DLocation}

• Attribute DLocation will represent the

multivalued attributes Locations of

DEPARTMENT

• Attribute DNumber (as foreign key)

represents the primary key of the

DEPARTMENT relation

32




Relationship ]

• WORKS_ON [ ESsn, Pno, Hours ]

• DEPT_LOCS [ DNumber, Dlocation ]

Final Schema

Step 7: N-ary Relationship Type

For each “n-ary” relationship type (RT) , create a new relation S to represent RT.

– Include as foreign key attributes of S the

primary keys of the relations that represent

the participating entity types in RT

– Include any simple attributes of the n-ary

relationship type

– The combination of foreign keys referencing

the relations representing the participating

entity types is used to form primary key of S

33

Special case:N-ary Relationship

If the participation constraint (min,max) of

one of the entity types E participating in

RT has max = 1, then the primary key of S

need not include the foreign key attribute

that references the relation E

34

N-ary Mapping Examples

• Ternary relationship

• Ternary relationship with participation

constraint of one entity type having max=1

• Weak entity with three owners

• Semantically different representation of

relationship between 3 entities

35

Ternary relationship

36

SUPPLIER

PART

SUPPLY(1,N)

Sname

PartNo

ProjName

Quantity

PROJECT(1,N)

(1,N)

SUPPLIER [ Sname, ... ]

PROJECT [ ProjName, ... ]

PART [ PartNo, ... ]

SUPPLY [ SName, ProjName, PartNo, Quantity ]

Ternary relationship (max=1)

37

Participation constraint with max = 1 (Only one Supplier for each Project/Part)

SUPPLIER [ Sname, ... ]PROJECT [ ProjName, ... ]PART [ PartNo, ... ]SUPPLY [ ProjName, PartNo, #Sname, Quantity ]

SUPPLIER

PART

SUPPLY(1,N)

Sname

PartNo

ProjName

Quantity

PROJECT(1,1)

(1,N)

Weak entity with three owners

• SUPPLIER [ Sname, ... ]

• PROJECT [ ProjName, ... ]

• PART [ PartNo, ... ]

• SUPPLY [ SName, ProjName , PartNo, Quantity ]

38

Same asternary

relationship

SUPPLIER

PART

SS

1

Sname

PartNo

ProjNameQuantity

PROJECT

N

SPJSUPPLY

SP

NN 11

Relationships between 3 Entities

39

SUPPLIER [Sname, ... ]PROJECT [ProjName, ... ]PART [PartNo, ... ]

SUPPLIES [SName, ProjName]CANSUPPLY [SName, PartNo]USES [PartNo, ProjName]

Semanticallydifferent fromternaryrelationship

SUPPLIER SUPPLIES

ProjNameSname

PROJECT

PART

PartNo

M N

CAN_SUPPLY USES

M

N

M

N

Contents

Entity-Relationship to Relational Mapping

• Steps for mapping a basic ER diagram to

a relational schema

– Uses the Company database example to

illustrate the concepts

• Design choices in the ER Model and their

impact on the resulting relational schema

40

Expressibility of ERConstraints play an important role in determining the best

database design for an enterprise.– Several kinds of integrity constraints can be expressed

in the ER model, e.g., key constraints, participation constraints.

– Some foreign key constraints are also implicit in thedefinition of a relationship set.

– Some constraints (notably, functional dependencies)cannot be expressed in the ER model.

– Some additional constructs have not been discussed:ISA hierarchies, and aggregation.

– There are many variations on ER model

41

Subjectivity of ER Design

• ER model is a means of capturing user’s data requirements. However, different designers may interpret the semantics of the user’s requirements differently

• This may result in the same UoD being represented by different ER diagrams because of different Design Choices

• These design choices in the ER Modelimpact on the resulting relational schema

42

What are Design Choices ?

• Should a concept be modeled as an entity or an attribute?

• Should a concept be modeled as an entity or a relationship?

• Should a concept be modeled as a weak entity or a complex (composite, multivalued) attribute

• Is a relationship binary or ternary?

• … ?43

Entity vs. Attributeenr-dept gives the

enrolling departmentfor a Student

ofr-dept gives theoffering departmentfor a Course

A designer may chooseto create an entitytype Department witha single attributedname. Otherattributes forDepartment (Hod,dbudget) may bediscovered later

44

Student Studies

sno

name

Course

Enr-deptccd

ctitle

ofr-dept

enrol offerDepartment

dbudgetHod

dname

Student Studies

snoname

Course

Enr-deptccd

ctitle

ofr-dept

Entity vs. Relationship

• Works_In2 does not allow an employee to work in a project more than once.

• Works_In3 allows an employee to work in the same project more than once.

• Can an employee work in the same project, for the same period under two different positions?

45

Employees Works_In2

ssn

name

Projects

add pid

pname

budget

from

to

pos

Employees EPH

ssn

name

Projects

add pid

pname

budget

Works-In3

posfrom

to

Weak Entity vs. Complex Attrib

• If a weak entity participates in other relationship types, besides the identifying relationship, then it has to be modeled as a weak entity

• If the weak entity has only one attribute, then it may be modeled as a multivalued attribute of the owner entity

46

Employees Works_In2

ssn

name

adddname

Employees

ssn

name

add

Dependents

dname

Binary vs. Ternary Relationships

• If a project is controlled by, and an employee works in only one department, the ternary relationship is inappropriate

• EPD [ssn, dname, projid](90, CSEE, WF99)(90, CSEE, Hydro88)(87, CSEE, Hydro88)(87, CSEE, Spark4)(32, Biology,Gen2000)

47

Employees EPD

ssn

name

Department

add dbudgetdname

projid

cost

Project

MN

1

Employees

Project

Works-In

ssn

name

Department

add dbudgetdname

projid cost

M

N

1

Assigned-toControlled

M

N

1

From UoD to Database

• Correctness (How can we be sure?)

– semantic non-ambiguity (unique name and

elementary value)

– minimum representation (no redundancy and

no derivables)

• Completeness (Is it good for all applications?)

– Everything in UoD is represented

– Everything expressed by the model is true in

UoD

48

Example Exercise

• Extract the conceptual model (ER DIAGRAM)

from a given user specification

• Map the conceptual model to a RELATIONAL

SCHEMA

• Refine the relational schema using functional

dependencies and normalization (Next Module)

49

Specifications

• Sebuah bank, yang dikenali dari code, name dan head office address, dapat memiliki beberapa branches. Setiap branch untuk bank tertentu memiliki branch number and address.

• Satu cabang dapat memiliki beberapa account, masing-masing diidentifikasi dengan AC number. Setiap account memiliki type, current balance, dan satu atau lebih account holder.

• Satu cabang dapat memilik pinjaman, masing-masing pinjaman dikenali dari unique loan number, type, amount dan satu atau lebih loan holders

• Nama, alamat, telepon dan id dari semua pelanggan (account dan pinjaman) dari setiap bank dicatat dan dipelihara.

50

ER Diagram

51

BANK BRANCHES

Code

Name

BANK-BRANCH

HO-Addr Addr Branch-No

N

1

ER Diagram

52

1

ACCTS

ACCOUNTN

ACNo

Type

Balance

BANK BRANCHES

Code

Name

BANK-BRANCH


N

1

ER Diagram

53

LOANS

LOAN

LoanNo

Type

Amount

1

N

1

ACCTS

ACCOUNTN

ACNo

Type

Balance

BANK BRANCHES

Code

Name

BANK-BRANCH


N

1

ER Diagram

54

AHOLDER

LHOLDER

CUSTOMER

N

M

N

M

Name

Addr

Phones

SSN

LOANS

LOAN

LoanNo

Type

Amount

1

N

1

ACCTS

ACCOUNTN

ACNo

Type

Balance

BANK BRANCHES

Code

Name

BANK-BRANCH


N

1

Relational Schema

• BANK [Code, Name, HOAddr]

• BRANCH [BankCode, BranchNo, Addr]

• ACCOUNT [ACNo, Type, Balance, BankCode, BranchNo]

• LOAN [LoanNo, Type, Amount, BankCode, BranchNo]

• CUSTOMER [SSN, Name, Address]

• CUSTPHONE [SSN, Phone]

• ACCOUNT-HOLDER [ACNo, SSN]

• LOAN-HOLDER [LoanNo, SSN]

55

Review• Seven mapping steps address basic constructs that

appear on ER diagrams– The result is a relational database schema that exhibits

good design characteristics– There are other ER constructs that we have not

addressed• ER design is subjective. There are often several ways to model

a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise.

• ER to Relational Mapping is a starting point. Resultingrelational schema has to be analyzed and refined furtherto achieve optimal design, using functional dependencyinformation and techniques such as normalization (NextModule)

56

sistem basis data i - bag6 - relational mapping

Documents