& 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

‚ 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, …

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

Relationship Set borrower p

Attribute of Relationshipp

ƒ Relationships can have attributes p

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

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 : 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

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

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

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

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

Attributes

Relationship Sets with Attributes p

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

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

Cardinality Constraints y

ƒ Express cardinality constraints by p y y

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

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

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

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

Alternative Notation for Cardinality

ƒ Cardinality limits can also express participation constraints

y

y p p p

à min .. max

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#

à B# + S#

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

ƒ Primary key for payment

(loan-number, payment-number)

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.

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

ƒ 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

E-R Notations

Alternative E-R Notations

UML Class Diagram Notation g

UML Class Diagram Notation g (cont.) ( )

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

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

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

Design Phases

ƒƒ Requirement specification Requirement specification

g

q p f

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

ƒƒ 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.)

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

ER schema to tables (cont.) ( )

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

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 ₂

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

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

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

Generalization

Account <= savings, checking g g

ƒ General case

ƒ 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

savings account

checking

ƒ If disjoint and complete

ƒ If disjoint and complete

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

plus the super entity

savings checking

END OF CHAPTER 6

END OF CHAPTER 6