LEARNING MODULE

IT 105. Fundamentals of Database Management System (DBMS)

BSIT 1

Module 2. DATABASE ANALYSIS

Chapter 3: ENTITY RELATIONSHIP MODELING (ERM)
PART 1

INTRODUCTION:

This chapter expands coverage of the data-modeling aspect of database

design. Data modeling is the first step in the database design journey, serving as a
bridge between real-world objects and the database model that is implemented in
the computer. Therefore, the importance of data-modeling details, expressed
graphically through entity relationship diagrams (ERDs), cannot be overstated.

Most of the basic concepts and definitions used in the entity relationship
model (ERM) were introduced in Chapter 2, Data Models. For example, the basic
components of entities and relationships and their representation should now be
familiar to you. This chapter goes much deeper and further, analyzing the graphic
depiction of relationships among the entities and showing how those depictions help
you summarize the wealth of data required to implement a successful design.

Finally, the chapter illustrates how conflicting goals can be a challenge in

database design, possibly requiring you to make design compromises.

LEARNING OBJECTIVES:

At the end of the lesson, students should be able to:

 Enumerate the main characteristics of entity relationship components

 Describe how relationships between entities are defined, refined, and
incorporated into the database design process
 Explain how ERD components affect database design and
implementation
 Create an Entity-Relationship Diagram

3.1 THE E-R MODEL

We have learned in chapter 2 on Data Models that ERM forms the basis of an
ERD.

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 1

By: RBBangat
 LEARNING MODULE
IT 105. Fundamentals of Database Management System (DBMS)
BSIT 1

• Entity-relationship model (or E-R model) – is a detailed, logical

representation of the data for an organization or for a business area.

- It is expressed in terms of entities in the business environment, the

relationships (or associations) among those entities, and attributes (or
properties) of both the entities and their relationship.

• Entity Relationship Diagram (ERD) – is a graphical representation of an E-R

model. It represents the conceptual database as viewed by the end user.

3.1.1 ENTITIES

Entity - is an object of interest to the end user. It can be a person, place, object,
event, or concept in the user environment about which the organization wishes to
maintain data.

- It refers to the entity set and not to a single entity occurrence. In other words,
the word entity in the ERM corresponds to a table—not to a row—in the
relational environment.
 In ERM, a table row refers to an entity instance or entity occurrence.
 In both the Chen and Crow’s Foot notations, an entity is represented by a
rectangle containing the entity’s name.
 The entity name, a noun, is usually written in all capital letters.
 Since the name represents a collection (or set) of items, it is always singular.
 Use capital letters for the name of the entity.

Examples:

Person – EMPLOYEE, STUDENT, PATIENT

Place – STORE, WAREHOUSE, STATE

Object – MACHINE, BUILDING, AUTOMOBILE

Event – SALE, REGISTRATION, RENEWAL

Concept- ACCOUNT, COURSE, WORK CENTER

Strong and Weak Entity Types

• Strong Entity - an entity that exists independently of other entities.

Example: STUDENT, EMPLOYEE, COURSE

• Weak Entity type - an entity whose existence depends on some other

entities.

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 2

By: RBBangat
 LEARNING MODULE
IT 105. Fundamentals of Database Management System (DBMS)
BSIT 1

• Identifying owner - is the entity type on which the weak entity type depends.

• Identifying relationship - the relationship between weak entity type and its
owner.

• The relationship between the weak entity type and its owner is identified by
the double lined diamond symbol. See Example in Figure 3.1

Figure 3.1

3.1.2 ATTRIBUTES

Attributes - are characteristics of entities.

For example: The STUDENT entity includes, among many others, the attributes
STU_LNAME, STU_FNAME, and STU_INITIAL.

 In the original Chen notation, attributes are represented by ovals and are
connected to the entity rectangle with a line. Each oval contains the name of
the attribute it represents.
 In the Crow’s Foot notation, the attributes are written in the attribute box
below the entity rectangle. (See Figure 3.2.) Because the Chen representation
is rather space-consuming, software vendors have adopted the Crow’s Foot
attribute display.

Figure 3.2 The attributes of the STUDENT entity: Chen and Crow’s Foot

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 3

By: RBBangat
 LEARNING MODULE
IT 105. Fundamentals of Database Management System (DBMS)
BSIT 1

Required vs Optional Attributes

Required attribute - is an attribute that must have a value for each entity
instance. As shown in Figure 1.1, there are two boldfaced attributes in
the Crow’s Foot notation. This indicates that a data entry will be
required.

Example: STU_LNAME, STU_FNAME

These are required attributes because of the assumption that all

students have a last name and a first name.

Optional attribute - is an attribute that does not require a value; therefore, it

can be left empty.

Example: STU_PHONE, STU_EMAIL, MAJOR, ETC

Domain

 Attributes have a domain.

 A domain is the set of possible values for a given attribute.
 Attributes may share a domain. For instance, a student address and a
professor address share the same domain of all possible addresses.

For example, the PROFESSOR and STUDENT entities may each have an
attribute named ADDRESS and could therefore share a domain.

Identifiers (Primary Keys)

 The ERM uses identifiers to uniquely identify each entity instance. In the
relational model, such identifiers are mapped to primary keys in tables.

Identifiers are underlined in the ERD

 Key attributes are also underlined in a frequently used table structure

shorthand notation using the format below:

TABLE NAME (KEY_ATTRIBUTE 1, ATTRIBUTE 2, ATTRIBUTE 3,

...ATTRIBUTE K)

For example, a CAR entity may be represented by:

CAR (CAR_VIN, MOD_CODE, CAR_YEAR, CAR_COLOR)

(Each car is identified by a unique vehicle identification number, or CAR_VIN.)

Composite Identifier or Composite key - a primary key composed of more

than one attribute.

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 4

By: RBBangat
 LEARNING MODULE
IT 105. Fundamentals of Database Management System (DBMS)
BSIT 1

For instance, the CLASS entity can use a composite primary key composed
of the combination of CRS_CODE and CLASS_SECTION instead of using
CLASS_CODE.

Example: In Figure 3.3, CLASS_CODE is the primary key, and the

combination of CRS_CODE and CLASS_SECTION is a proper candidate key.
If the CLASS_CODE attribute is deleted from the CLASS entity, the candidate
key (CRS_CODE and CLASS_SECTION) becomes an acceptable composite
key.

Figure 3.3 The CLASS table(entity) components and contents

If the CLASS_CODE is used as the primary key, the shorthand form is:

CLASS(CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME,..)

On the other hand if CLASS-CODE is deleted, the composite key

CRS_CODE and CLASS_SECTION can be used to identify each entity
instance. And it may be represented by:

CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM,

PROF_NUM)

Composite and Simple Attributes

Composite attribute - is an attribute that can be further subdivided to yield

additional attributes.

Example: The attribute ADDRESS can be subdivided into street, city, state,
and zip code. Similarly, the attribute PHONE_NUMBER can be subdivided into area
code and exchange number.

Simple attribute - is an attribute that cannot be subdivided.

Example: age, sex, and marital status

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 5

By: RBBangat
 LEARNING MODULE
IT 105. Fundamentals of Database Management System (DBMS)
BSIT 1

Note: To facilitate detailed queries, it is wise to change composite attributes into a

series of simple attributes.

Single-Valued Attributes and Multivalued Attributes

Single-Valued Attributes- is an attribute that can have only a single value.

Example, a person can have only one Social Security number.

 Keep in mind that a single-valued attribute is not necessarily a simple

attribute. It can also be a composite attribute
Example: A part’s serial number, such as SE-08-02-189935, is single-valued,
but it is a composite attribute because it can be subdivided into the region in
which the part was produced (SE), the plant within that region (08), the shift
within the plant (02), and the part number (189935).

Multivalued Attributes - are attributes that can have many values.

Example: A person may have several college degrees, and a household may
have several different phones, each with its own number.

 In the Chen ERM, the multivalued attributes are shown by a double line
connecting the attribute to the entity. The Crow’s Foot notation does not
identify multivalued attributes.
 The ERD in Figure 3.4 contains all of the components. In Figure 3.4, note that
CAR_VIN is the primary key, and CAR_COLOR is a multivalued attribute of
the CAR entity.

Figure 3.4 A Multivalued attribute in an entity

Derived Attributes

Derived Attributes - is an attribute whose value is calculated (derived) from other

attributes. The derived attribute need not be physically stored within the database;
instead, it can be derived by using an algorithm.

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 6

By: RBBangat
 LEARNING MODULE
IT 105. Fundamentals of Database Management System (DBMS)
BSIT 1

Example: An employee’s age, EMP_AGE, may be found by computing the

integer value of the difference between the current date and the EMP_DOB.

 A derived attribute is indicated in the Chen notation by a dashed line

connecting the attribute and the entity. (See Figure 3.5.) The Crow’s Foot
notation does not have a method for distinguishing the derived attribute from
other attributes.
 Derived attributes are sometimes referred to as computed attributes.

Figure 3.5

REFERENCES:

• Modern Database Management 7th Edition By: Jeffrey A. Hoffer, et. Al

• Database Principles, Design Implementation and Management Fundamentals

by: Carlos Coronel, et.al.

Module 3 Chapter 4. Entity-Relationship (ER) Modeling Page 7

By: RBBangat