chapter 4 entity relationship (e-r) modeling

Chapter 4Entity Relationship (E-R) ModelingChapter 4Entity Relationship (E-R) Modeling

Database Systems: Design, Implementation, and Management

Peter Rob & Carlos Coronel

In this chapter, you will learn:In this chapter, you will learn:

How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process

How ERD components affect database design and implementation

How to interpret the modeling symbols for the four most popular ER modeling tools

That real-world database design often requires that you reconcile conflicting goals

The Entity Relationship (E-R) ModelThe Entity Relationship (E-R) Model

ER model forms the basis of an ER diagram

ERD represents the conceptual database as viewed by end user

Main Components Entities

In E-R models an entity refers to the entity set. An entity is represented by a rectangle containing the entity’s

name.

Attributes Attributes are represented by ovals and are connected to the

entity with a line. Each oval contains the name of the attribute it represents. Attributes have a domain -- the attribute’s set of possible values. Attributes may share a domain.

Relationships

EntitiesEntities

Refers to entity set and not to single entity occurrence

Corresponds to table and not to row in relational environment

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

Entity name, a noun, is usually written in capital letters

AttributesAttributes

Characteristics of entities

In Chen model, attributes are represented by ovals and are connected to

entity rectangle with a line Each oval contains the name of attribute it represents

In Crow’s Foot model, attributes are written in attribute box below entity rectangle

The Attributes of the STUDENT EntityThe Attributes of the STUDENT Entity

DomainsDomains

Attributes have domain Domain is attribute’s set of possible values

Attributes may share a domain

Primary KeysPrimary Keys

Underlined in the ER diagram

Key attributes are also underlined in frequently used table structure shorthand

Ideally composed of only a single attribute

Possible to use a composite key :

Primary key composed of more than one attribute

Composite Primary Keys (continued)Composite Primary Keys (continued)

• Primary Keys (CLASS_CODE) Another possible Composite Primary Key (CRS_CODE + CLASS_SECTION)

• Primary Keys (CLASS_CODE) Another possible Composite Primary Key (CRS_CODE + CLASS_SECTION)

Classes of AttributesClasses of Attributes

A simple attribute cannot be subdivided.

Examples: Age, Sex, and Marital status

A composite attribute can be further subdivided to yield additional attributes.

Examples: ADDRESS Street, City, State, Zip

PHONE NUMBER Area code, Exchange number

Classes of AttributesClasses of Attributes

A single-valued attribute can have only a single value. Examples:

A person can have only one social security number. A manufactured part can have only one serial number.

Multivalued attributes can have many values. Examples:

A person may have several college degrees. A household may have several phones with different numbers

Multivalued attributes are shown by a double line connecting to the entity.

Multivalued attributes in an Entity

Resolving Multivalued Attribute ProblemsResolving Multivalued Attribute Problems

Although conceptual model can handle M:N relationships and multivalued attributes, you should not implement them in relational DBMS.

Possible courses of action for the designer Within the original entity, create several new attributes, one for

each of the original multivalued attribute’s components ( Figure 4.4).

Create a new entity composed of the original multivalued attribute’s components ( Figure 4.5).

Splitting the Multivalued Attribute into New Attributes

A New Entity Set Composed of Multivalued Attribute’s ComponentsA New Entity Set Composed of Multivalued Attribute’s Components

A New Entity Set Composed of Multivalued Attribute’s ComponentsA New Entity Set Composed of Multivalued Attribute’s Components

A derived attribute may be calculated (derived) from other attributes

Need not be physically stored within the database

Can be derived by using an algorithm

Example: AGE can be derived from the data of birth and the current date.

Derived AttributesDerived Attributes

Depiction of a Derived AttributeDepiction of a Derived Attribute

Derived Attributes (continued)Derived Attributes (continued)

Relationships A relationship is an association between entities. Relationships are represented by diamond-shaped

symbols.

The Entity Relationship (E-R) ModelThe Entity Relationship (E-R) Model

RelationshipsRelationships

Association between entities Participants

are entities that participate in a relationship

Relationships between entities always operate in both directions

Relationship can be classified as 1:M Relationship classification is difficult to establish

if know only one side of the relationship

The term connectivity is used to describe the relationship classification (e.g., one-to-one, one-to-many, and many-to-many).

ConnectivityConnectivity

Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity.

The minimum and maximum number of entity occurrences

CardinalityCardinality

Connectivity and Cardinality in an ERDConnectivity and Cardinality in an ERD

Connectivity and CardinalityConnectivity and Cardinality

Existence Dependent If an entity’s existence depends on the existence of one or

more other entities, it is said to be existence-dependent.

CLASS is existence-dependent on COURSE (parent entity)

EMPLOYEE claims DEPENDENT—DEPENDENT is existence-dependent on EMPLOYEE

Existence independent Entity can exist apart from one or more related entities Example:

some of parts are produced “in-house” and other parts are bought from vendors.At least some of the parts are not supplied by a vender.

PART is existence-independent from VENDOR

Relationship StrengthRelationship Strength

Weak (non-identifying) relationship One entity is not existence-independent on another entity exist if the PK of the related entity doesn’t contain a PK

component of the parent entity COURSE( CRS_CODE, …) CLASS( CLASS_CODE, …)

Strong (identifying) relationship exist when the related entities are existent-dependent and

the PK of the dependent entity contains a PK component of the parent entity

COURSE( CRS_CODE, …) CLASS( CRS_CODE, CLASS_SECTION, …)

Relationship StrengthRelationship Strength

A Weak Relationship Between COURSE and CLASSA Weak Relationship Between COURSE and CLASS

A Strong (Identifying) Relationship Between COURSE and CLASSA Strong (Identifying) Relationship Between COURSE and CLASS

Weak Entities

A weak entity is an entity that Is existence-dependent and

Has a primary key that is partially or totally derived from the parent entity in the relationship.

The existence of a weak entity is indicated by a double rectangle.

The weak entity inherits all or part of its primary key from its strong counterpart.

Relationship Strength and Weak EntitiesRelationship Strength and Weak Entities

A Weak Entity in an ERD

• EMPLOYEE( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB )

• DEPENDENT( EMP_NUM, DEP_NUM, DEP_FNAME, DEP_DOB ) Primary Key

DEP_NUM

Weak entity in a Strong Relationship BetweenDEPENDENT and EMPLOYEE

( EMP_NUM + DEP_NUM )

( EMP_NUM )

Weak relationship One entity is not existence-independent on another PK of related entity doesn’t contain PK component of parent

entity COURSE( CRS_CODE, …) CLASS( CLASS_CODE, …)

Strong relationship One entity is existence-dependent on another PK of related entity contains PK component of parent entity

COURSE( CRS_CODE, …) CLASS( CRS_CODE, CLASS_SECTION, …)

In any case, CLASS is always existence-dependent on COURSE, whether or not it is defined to be weak.

Weak entity in a Strong Relationship Weak entity in a Strong Relationship

Not Weak entity

Optional participation

One entity occurrence does not require a corresponding entity occurrence in a particular relationship.

An optional entity is shown by a small circle on the side of the optional entity.

Mandatory participation

One entity occurrence requires corresponding occurrence in related entity

If no optionality symbol is shown on ERD, it is mandatory

Relationship ParticipationRelationship Participation

Relationship Participation (continued)Relationship Participation (continued)

• CLASS is Optional to PROFESSOR PROFESSOR is Mandatory to CLASS

• CLASS is Optional to COURSE COURSE is Mandatory to CLASS

• COURSE and CLASS in a Mandatory Relationship

Relationship Participation (continued)Relationship Participation (continued)

• CLASS is Optional to COURSE COURSE is Mandatory to CLASS

Relationship Participation (continued)Relationship Participation (continued)

• COURSE and CLASS in a Mandatory Relationship

Relationship Participation (continued)Relationship Participation (continued)