& THE E R MODEL

(1)

CHAPTER 6

Intro to DB

CHAPTER 6 DATABASE DESIGN DATABASE DESIGN

& THE E R MODEL

& THE E-R MODEL

(2)

Chapter 6. Entity Relationship Model p y p

Design Process g

Modeling

Constraints

E-R Diagram

Design Issues

Weak Entity Sets y

Extended E-R Features

Design of the Bank Database

Reduction to Relation Schemas

Database Design

UML

(3)

Introduction

Proposed by P. Chen in 1976 p y

“ The Entity-Relationship Model: Toward a Unified View of Data”, ACM Transactions On Database Systems, Jan.1976.

Data , ACM Transactions On Database Systems, Jan.1976.

A very powerful tool in the design of databases

à Simple model

à Effective means of communication between user designer and Effective means of communication between user, designer, and implementer

E-R model is not an implementation model

à i.e., there is no DBMS whose internal structures are based on the E-R i.e., there is no DBMS whose internal structures are based on the E R

model

(4)

Database Modelingg

A database can be modeled as:

à a collection of entities,

à relationship among entities.

An entity is an object that exists and is distinguishable from other objects (entity instance). j ( y )

Example: specific person, company, event, plant

Entiti s h tt ib t

Entities have attributes

Example: people have names and addresses

A i i f i i f h h h h

An entity set is a set of entities of the same type that share the same properties.

Example: set of all persons, companies, trees, holidays

(5)

Entity & Entity Sets - examples y y p

customer-id customer- customer- customer- loan- amount

name street city number

(6)

Attributes

The descriptive properties of an entity p p p y

An entity is represented by a set of attributes

à Student = (id, name, dept, address, …) Student (id, name, dept, address, …)

Value set (domain)

à set of permitted values for an attribute

à Formally: Attribute is a mapping from the entity set to the value set

Entity Set1 Domain1

e1

Domain2

e1 e2 e3

e4

A1 A2

(7)

Types of Attributes yp

Simple vs Composite attributes p p

à Simple attribute

values cannot be divided into subparts

firstname, lastname, phone#

à Composite attribute

composed of multiple parts

name = (lastnm, firstnm)

Phone# = (number extension) Phone# = (number, extension)

Null attributes

à ll l i l l i “ i i ” “ k ”

à null value: a special value meaning “missing” or “unknown”

à some attributes are not allowed to have null values

(8)

Types of Attributes yp (cont.) ( )

Single-valued vs multivalued : g

à Single-valued attribute

each attribute has a single value for an entity

id, name, dept

à Multivalued attribute

an attribute may have more than one value for an instance

children = {john, tom}, phone#={5567, 5568}

D i d ib

Derived attributes

à value can be derived from the values of other related attributes or entities

d i

à duration, count, sum, …

(9)

Relationships p

Relationships are defined between entities p

Relationship set:

R = { [e ₁ e ] | e ₁ ∈ E ₁ e ∈ E } R { [e ₁ , ..., e _n ] | e ₁ ∈ E ₁ , …, e _n ∈ E _n }

E _i : entity set,

[e ₁ e ] : relationship [e ₁ ,..., e _n ] : relationship

Entity Set1 Entity Set2

Entity Set1

e1 e2 e3

y

f1 f2 f3 e3

e4

f3 f4

f5

Relationship Set

(10)

Relationship Set borrower p

(11)

Attribute of Relationshipp

Relationships can have attributes p

à An attribute can also be property of a relationship set.

(12)

Mapping Constraints pp g

Relationship cardinality p d y

à Number of entities to which another entity can be associated via a relationship set

relationship set

Generic types

à 1 : 1

à 1 : m

à m : 1

à m : n

Relationship cardinality can affect the placement of relationship e at o s p ca d a ty ca a ect t e p ace e t o e at o s p

attributes

(13)

Mapping Cardinalities pp g

One to one One to many

Note: Some elements in A and B may not be mapped to any elements in the other set

(14)

Mapping Cardinalities pp g (cont.) ( )

Many to one Many to many

Note: Some elements in A and B may not be mapped to any elements in the other set

Note: So e e e e ts a d ay ot be apped to a y e e e ts the othe set

(15)

Mapping Cardinalities affect ER Design pp g g

Can make access-date an attribute of account, instead of a relationship p

attribute, if each account can have only one customer

(16)

E-R Diagrams g

Rectangles represent entity sets.

Diamonds represent relationship sets.

Lines link attributes to entity sets and entity sets to relationship sets.

Ellipses represent attributes

D bl lli l i l d ib

- Double ellipses represent multivalued attributes.

- Dashed ellipses denote derived attributes.

Underline indicates primary key attributes p y y

(17)

Attributes

(18)

Relationship Sets with Attributes p

(19)

Roles

Entity sets of a relationship need not be distinct y p

Role labels are optional, and are used to clarify semantics of the relationship

relationship

(20)

m-ary Relationships y p

Most relationships are binary p y

à R = { [e ₁ , e ₂ ] | e ₁ ∈ E ₁ , e ₂ ∈ E ₂ }

You can define non-binary relationships You can define non binary relationships

à R = { [e ₁ , e ₂ , e ₃ ] | e ₁ ∈ E ₁ , e ₂ ∈ E ₂ , e ₃ ∈ E ₃ } : ternary

(21)

Cardinality Constraints y

Express cardinality constraints by p y y

à a directed line (→): signifying “one”

à an undirected line (—): signifying “many”

(22)

Many-To-Many Relationship y y p

A customer is associated with several (possibly 0) loans via (p y ) borrower

A loan is associated with several (possibly 0) customers via A loan is associated with several (possibly 0) customers via

borrower

(23)

Participation in a Relationship p p

Total participation (indicated by double line): every entity in the p p ( y ) y y entity set participates in at least one relationship in the

relationship set

Partial participation: some entities may not participate in any

relationship in the relationship set p p

(24)

Alternative Notation for Cardinality

Cardinality limits can also express participation constraints

y

y p p p

à min .. max

(25)

Weak Entity Sets

Strong entity

y

g y

à Regular entity with its own primary key

Weak entity Weak entity

à An entity set that does not have sufficient attributes to form a primary key

B# Bus Seat S#

Time Type

A weak entity set is dependent on a strong entity set

Primary key of a weak entity set = primary key of its dominant

Primary key of a weak entity set = primary key of its dominant entity set + its descriminator

à B# + S#

(26)

Weak Entity Sets y (Cont.) ( )

Depict a weak entity set by double rectangles. p y y g

Underline the discriminator with a dashed line.

Primary key for payment

(loan-number, payment-number)

(27)

Existence Dependencies p

If the existence of entity x depends on the existence of entity y, y p y y then x is said to be existence dependent on y.

à y is a dominant entity (in example below, loan) y y ( p )

à x is a subordinate entity (in example below, payment)

l loan-payment payment

loan

à If a loan entity is deleted, then all its associated payment entities

must be deleted also. ust be de eted a so.

(28)

Extended E-R Features

Specialization

Th f d i i b i i hi i

à The process of designating subgroupings within an entity set

(ex. account - savings-account, checking account)

à a subentity will share common attributes

à a subentity will have its own specific attributes

Generalization

à combine a number of entity sets that share the same features into a higher-level combine a number of entity sets that share the same features into a higher level entity set

à opp: specialization - depends on where you start

Inheritance

à The attributes and relationships of the higher-level entity sets are inherited by (applies to) the lower-level entity sets

T f li i ( b i i )

Types of generalization (super-sub entities)

à Disjoint vs Overlapping:

whether an entity can belong to more than two sub entity set y g y

à Total vs Partial:

whether every higher level entity belong to a lower level entity set

(29)

(30)

E-R Notations

(31)

Alternative E-R Notations

(32)

UML Class Diagram Notation g

(33)

UML Class Diagram Notation g (cont.) ( )

*Note reversal of position in cardinality constraint depiction p y p

(34)

Design Issues g

Entity vs Attribute y

à an employee’s telephone

as an attribute : simple

as an entity : independent à Decision should be based on

whether the telephone must be treated as an independent entity

the number of telephones an employee can have

whether telephones are shared between employees whether telephones are shared between employees

Entity vs Relationship

" t h i t t b h"

"customer having an account at a branch"

à account as relationship : simple but limited (cannot participate in other relationships)

relationships)

à account as entity : account can act as separate entity

(35)

Design Issues (cont.)

Binary vs n-ary relationships

g ( )

y y p

à all n-ary relationships can be represented by binary relationships by adding additional entities and corresponding relationships

à however, this is not always desirable =>

decision should be based on how the model best represents the real world

i i

situation

Strong or weak entity set

Generalization and specialization

Aggregation gg g

(36)

Design Phases

Requirement specification Requirement specification

g

q p f

à identify data needs of user

Conceptual design Conceptual design Conceptual design Conceptual design

à translate into a conceptual schema

Logical design Logical design

à map onto the implementation data model of the DBMS

Ph i l d i n Ph i l d i n

Physical design Physical design

à specify physical features of the database (issues pertaining to performance rather than information contents; index sequential order etc )

rather than information contents; index, sequential order, etc.)

(37)

Reducing ER schema to tables

Basic rule

g

à each entity set => unique table

à each relationship set => unique table

Strong entity set E with attributes a ₁ , ..., a _n

à table E with n distinct columns each of which corresponds to a p _i _i

à D _i : set of all values (domain) for a _i

à table E will contain elements of

D ₁ X . . . X D _n

(38)

ER schema to tables (cont.) ( )

A strong entity set reduces to a table with the same attributes. g y

(39)

ER schema to tables (cont.)

Relationship set R

( )

p

involving E ₁ , ..., E _k

=> table R with columns corresponding to

=> table R with columns corresponding to PK(E ₁ ) U ... U PK(E _k ) U attr(R)

if one-to-many or one-to-one

E ₁ R E ₂

> dd l i PK(E ) U tt (R) bl i E

=> add columns representing PK(E ₁ ) U attr(R) to table representing E ₂

(40)

Representing Weak Entity Sets p g y

A weak entity set becomes a table that includes a column for the y

primary key of the identifying strong entity set

(41)

Representing Relationship Sets p g p

many-to-many relationship set: y y p

as a table with the primary keys of the two participating entity sets + any descriptive attributes

sets any descriptive attributes

(42)

Redundancy of Tables y

Many-to-one and one-to-many relationship sets that are total on

the many-side can be represented by adding an extra attribute to

the many side, containing the primary key of the one side

(43)

Generalization

Account <= savings, checking g g

General case

à Create a table for the higher level entity set

à For each subentity create a table that includes the attributes of that entity

à For each subentity, create a table that includes the attributes of that entity set plus the primary key of the higher level entity

savings account

checking

If disjoint and complete

à For each subentity, create a table that includes the attributes of that entity plus the super entity

& THE E R MODEL

CHAPTER 6

Intro to DB

CHAPTER 6

DATABASE DESIGN DATABASE DESIGN

& THE E R MODEL

& THE E-R MODEL

Chapter 6. Entity Relationship Model p y p

 Design Process g

 Modeling

 Constraints

 Constraints

 E-R Diagram

 Design Issues

 Weak Entity Sets y

 Extended E-R Features

 Design of the Bank Database

 Design of the Bank Database

 Reduction to Relation Schemas

 Database Design

 UML

Introduction

 Proposed by P. Chen in 1976 p y

“ The Entity-Relationship Model: Toward a Unified View of Data”, ACM Transactions On Database Systems, Jan.1976.

Data , ACM Transactions On Database Systems, Jan.1976.

 A very powerful tool in the design of databases

 A very powerful tool in the design of databases

à Simple model

à Effective means of communication between user designer and Effective means of communication between user, designer, and implementer

 E-R model is not an implementation model

à i.e., there is no DBMS whose internal structures are based on the E-R i.e., there is no DBMS whose internal structures are based on the E R

model

Database Modelingg

 A database can be modeled as:

à a collection of entities,

à relationship among entities.

 An entity is an object that exists and is distinguishable from other objects (entity instance). j ( y )

Example: specific person, company, event, plant

 Entiti s h tt ib t

 Entities have attributes

Example: people have names and addresses

A i i f i i f h h h h

 An entity set is a set of entities of the same type that share the same properties.

Example: set of all persons, companies, trees, holidays

Entity & Entity Sets - examples y y p

customer-id customer- customer- customer- loan- amount

name street city number

Attributes

 The descriptive properties of an entity p p p y

 An entity is represented by a set of attributes

à Student = (id, name, dept, address, …) Student (id, name, dept, address, …)

 Value set (domain)

à set of permitted values for an attribute

à set of permitted values for an attribute

à Formally: Attribute is a mapping from the entity set to the value set

Entity Set1 Domain1

e1

Domain2

e1 e2 e3

e4

A1 A2

Types of Attributes yp

 Simple vs Composite attributes p p

à Simple attribute

 values cannot be divided into subparts

 firstname, lastname, phone#

à Composite attribute

 composed of multiple parts

 name = (lastnm, firstnm)

 Phone# = (number extension) Phone# = (number, extension)

 Null attributes

à ll l i l l i “ i i ” “ k ”

à null value: a special value meaning “missing” or “unknown”

à some attributes are not allowed to have null values

Types of Attributes yp (cont.) ( )

 Single-valued vs multivalued : g

à Single-valued attribute

 each attribute has a single value for an entity

 id, name, dept

à Multivalued attribute

Design Process g

Modeling

Constraints

Constraints

E-R Diagram

Design Issues

Weak Entity Sets y

Extended E-R Features

Design of the Bank Database

Design of the Bank Database

Reduction to Relation Schemas

Database Design

UML

Proposed by P. Chen in 1976 p y

A very powerful tool in the design of databases

A very powerful tool in the design of databases

E-R model is not an implementation model

A database can be modeled as:

An entity is an object that exists and is distinguishable from other objects (entity instance). j ( y )

Entiti s h tt ib t

Entities have attributes

An entity set is a set of entities of the same type that share the same properties.

The descriptive properties of an entity p p p y

An entity is represented by a set of attributes

Value set (domain)

Simple vs Composite attributes p p

values cannot be divided into subparts

firstname, lastname, phone#

composed of multiple parts

name = (lastnm, firstnm)

Phone# = (number extension) Phone# = (number, extension)

Null attributes

Single-valued vs multivalued : g

each attribute has a single value for an entity

id, name, dept

an attribute may have more than one value for an instance

children = {john, tom}, phone#={5567, 5568}

Derived attributes

Relationships are defined between entities p

Relationship set:

R = { [e ₁ e ] | e ₁ ∈ E ₁ e ∈ E } R { [e ₁ , ..., e _n ] | e ₁ ∈ E ₁ , …, e _n ∈ E _n }

E _i : entity set,

[e ₁ e ] : relationship [e ₁ ,..., e _n ] : relationship

Relationships can have attributes p

Relationship cardinality p d y

Generic types

Relationship cardinality can affect the placement of relationship e at o s p ca d a ty ca a ect t e p ace e t o e at o s p

Can make access-date an attribute of account, instead of a relationship p

Rectangles represent entity sets.

Diamonds represent relationship sets.

Lines link attributes to entity sets and entity sets to relationship sets.

Ellipses represent attributes

Underline indicates primary key attributes p y y

Entity sets of a relationship need not be distinct y p

Role labels are optional, and are used to clarify semantics of the relationship

Most relationships are binary p y

à R = { [e ₁ , e ₂ ] | e ₁ ∈ E ₁ , e ₂ ∈ E ₂ }

You can define non-binary relationships You can define non binary relationships

à R = { [e ₁ , e ₂ , e ₃ ] | e ₁ ∈ E ₁ , e ₂ ∈ E ₂ , e ₃ ∈ E ₃ } : ternary

Express cardinality constraints by p y y