entity relationship (er) modeling

Entity Relationship (ER) Modeling

The Entity Relationship Model (ERM)

Database Systems, 9th Edition2

ER model forms the basis of an ER diagramERD represents conceptual database as

viewed by end userERDs depict database’s main components:

EntitiesAttributesRelationships

Entities

Database Systems, 9th Edition3

Refers to entity set and not to single entity occurrence

Corresponds to table and not to row in relational environment

In Chen and Crow’s Foot models, entity is represented by rectangle with entity’s name

Entity name, a noun, written in capital letters

Keys

Database Systems, 9th Edition4

Each row in a table must be uniquely identifiable

Key is one or more attributes that determine other attributes

Key’s role is based on determinationIf you know the value of attribute A, you can

determine the value of attribute BFunctional dependence

Attribute B is functionally dependent on A if all rows in table that agree in value for A also agree in value for B

Database Systems, 9th Edition5

Keys (cont’d.)

Database Systems, 9th Edition6

Composite keyComposed of more than one attribute

Key attributeAny attribute that is part of a key

SuperkeyAny key that uniquely identifies each row

Candidate key A superkey without unnecessary attributes

Keys (cont’d.)

Database Systems, 9th Edition7

NullsNo data entryNot permitted in primary keyShould be avoided in other attributesCan represent:

An unknown attribute valueA known, but missing, attribute valueA “not applicable” condition

Keys (cont’d.)

Database Systems, 9th Edition8

Nulls (cont’d.)Can create problems when functions such as

COUNT, AVERAGE, and SUM are usedCan create logical problems when relational

tables are linked

Database Systems, 9th Edition9

Attributes

Database Systems, 9th Edition10

Characteristics of entitiesChen notation: attributes represented by

ovals connected to entity rectangle with a lineEach oval contains the name of attribute it

representsCrow’s Foot notation: attributes written in

attribute box below entity rectangle

Database Systems, 9th Edition11

Attributes (cont’d.)

Database Systems, 9th Edition12

Required attribute: must have a valueOptional attribute: may be left emptyDomain: set of possible values for an

attributeAttributes may share a domain

Identifiers: one or more attributes that uniquely identify each entity instance

Composite identifier: primary key composed of more than one attribute

Attributes (cont’d.)

Database Systems, 9th Edition13

Composite attribute can be subdividedSimple attribute cannot be subdividedSingle-value attribute can have only a

single valueMultivalued attributes can have many

values

Database Systems, 9th Edition14

Attributes (cont’d.)

Database Systems, 9th Edition15

M:N relationships and multivalued attributes should not be implementedCreate several new attributes for each of the

original multivalued attributes’ componentsCreate new entity composed of original

multivalued attributes’ componentsDerived attribute: value may be

calculated from other attributesNeed not be physically stored within

database

Database Systems, 9th Edition16

Relationships

Database Systems, 9th Edition17

Association between entitiesParticipants are entities that participate

in a relationshipRelationships between entities always

operate in both directionsRelationship can be classified as 1:MRelationship classification is difficult to

establish if only one side of the relationship is known

Connectivity and Cardinality

Database Systems, 9th Edition18

Connectivity Describes the relationship classification

Cardinality Expresses minimum and maximum number

of entity occurrences associated with one occurrence of related entity

Established by very concise statements known as business rules

Database Systems, 9th Edition19

Existence Dependence

Database Systems, 9th Edition20

Existence dependenceEntity exists in database only when it is

associated with another related entity occurrence

Existence independenceEntity can exist apart from one or more

related entitiesSometimes such an entity is referred to as a

strong or regular entity

Relationship Strength

Database Systems, 9th Edition21

Weak (non-identifying) relationshipsExists if PK of related entity does not contain

PK component of parent entityStrong (identifying) relationships

Exists when PK of related entity contains PK component of parent entity

Database Systems, 9th Edition22

Database Systems, 9th Edition23

Weak Entities

Database Systems, 9th Edition24

Weak entity meets two conditionsExistence-dependentPrimary key partially or totally derived from

parent entity in relationshipDatabase designer determines whether an

entity is weak based on business rules

Database Systems, 9th Edition25

Relationship Participation

Database Systems, 9th Edition26

Optional participationOne entity occurrence does not require

corresponding entity occurrence in particular relationship

Mandatory participationOne entity occurrence requires

corresponding entity occurrence in particular relationship

Database Systems, 9th Edition27

Database Systems, 9th Edition28

Relationship Degree

Database Systems, 9th Edition29

Indicates number of entities or participants associated with a relationship

Unary relationshipAssociation is maintained within single entity

Binary relationship Two entities are associated

Ternary relationship Three entities are associated

Database Systems, 9th Edition30

Database Systems, 9th Edition31

Developing an ER Diagram

Database Systems, 9th Edition32

Database design is an iterative processCreate detailed narrative of organization’s

description of operationsIdentify business rules based on description of

operationsIdentify main entities and relationships from

business rulesDevelop initial ERDIdentify attributes and primary keys that

adequately describe entitiesRevise and review ERD

Database Systems, 9th Edition33

Database Systems, 9th Edition34

Database Systems, 9th Edition35

Database Systems, 9th Edition36

Database Systems, 9th Edition37

Database Systems, 9th Edition38

Database Systems, 9th Edition39

Database Systems, 9th Edition40

Database Systems, 9th Edition41