Chapter Four Entity Relationship (ER) Modeling Lec. Manal F.
Younis
Chapter Four
Entity Relationship (ER) Modeling
4.1. OVERVIEW OF DATABASE DESIGN
The database design process can be divided into five steps. The ER model is most relevant to
the first three steps:
(1) Requirements Analysis: The very first step in designing a database application is to
understand what data is to be stored in the database, we must find out what the users want from
the database. Several methodologies have been proposed for organizing and presenting the
information gathered in this step, and some automated tools have been developed to support
this process.
(2) Conceptual Database Design: This step is often carried out using the ER model The
information gathered in the requirements analysis step is used to develop a high-level
description of the data to be stored in the database.
(3) Logical Database Design: The task in the logical design step is to convert an ER schema into a
relational database schema. We must choose a DBMS to implement our database design.
The remaining two steps of database design are briefly described below:
(4) Schema Refinement: The fourth step in database design is to analyze the collection of relations
in our relational database schema to identify potential problems, and to refine it. We discuss the
theory of normalizing relations to ensure some desirable properties later.
(5) Physical Database Design: In this step we must consider typical expected workloads that our
database must support and further refine the database design to ensure that it meets desired
performance criteria.
4.2. The Entity Relationship (E-R) Model
The ERD represents the conceptual database as viewed by the end user. ERDs depict the
database’s main components: entities, attributes, and relationships. Because an entity represents
a real-world object, the words entity and object are often used interchangeably.
4.2.1. Entities:
• Refers to entity set and not to single entity occurrence
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 1
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
• 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
4.2.2. Attributes:
• 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.
As you examine Figure 4.1, STU_LNAME and STU_FNAME require data entries because of
the assumption that all students have a last name and first name. But students might not have a
middle name, and perhaps they do not have a phone number and e-mail address. Therefore,
those attributes are not presented in boldface in the entity box.
Domains
• Attributes have domain
– Domain is attribute’s set of possible values. For example, the domain for the
(numeric) attribute grade point average (GPA) is written (0,4) because the lowest
possible GPA value is 0 and the highest possible value is 4. Sex consists of only two
possibilities: M or F (or some other equivalent code).
• Attributes may share a domain. For instance, a student address and professor address share
the same domain of all possible addresses.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 2
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
• Attribute Classification
Attribute is used to describe the properties of the entity. This attribute can be broadly
classified based on value and structure.
• Single value attribute
Single value attribute means, there is only one value associated with that attribute. examples
of single value attribute is age of a person and a manufactured part can have only one serial
number.
• Multivalue attribute
In the case of multivalue attribute; more than one value will be associated with that
attribute. Consider an entity EMPLOYEE. An Employee can have many skills; hence skills
associated to an employee are a multivalue attribute.
A multivalue attribute (skills)
Multivalued attributes are shown by a double line connecting to the entity.
As shown in figure 4.3, the CAR_VIN is the primary key and CAR_COLOR is a multivalued
attribute of the CAR entity.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 3
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Resolving Multivalued Attribute Problems
• Although conceptual model can handle M: N relationships and multivalued attributes, you
should not implement them in relational DBMS.
If multivalued attribute exists, the designer must decide on one of two possible courses of
action:
1. Within original entity, create several new attributes; one for each of the original
multivalued attribute’s components can lead to major structural problems in table.
CAR-IN
Car Car-Color
has
2. Create new entity composed of original multivalued attribute’s components
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 4
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Note that ERM in figure 4.5 reflects the components listed in table 4.1. This is preferred way to
deal with multivalued attributes. Creating a new entity in a 1:M relationship with the original entity
yields several benefits: it’s a more flexible, expandable solution, and it is compatible with the
relational model.
• Derived Attribute
• The value of the derived attribute can be derived from the values of other related attributes
or entities.
• Need not be physically stored within database
• Can be derived by using an algorithm
For example, EMP_AGE may be found by computing the integer value of the difference
between the current date and the EMP_DOB (Date of birth).
If MS Access is used you would use INT ((DATE () – EMP_DOB)/365).
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 5
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Table 4.2 shows the advantages and disadvantages of storing (or not storing) derived attributes in
the database.
• Null Value Attribute
In some cases, a particular entity may not have any applicable value for an attribute. For
such situation, a special value called null value is created.
Example
In application forms, there is one column called phone no. if a person do not have phone then a
null value is entered in that column.
• Composite Attribute
Composite attribute is one which can be further subdivided into simple attributes.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 6
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Example: Consider the attribute “address” which can be further subdivided into Street name,
City, and State.
Identifiers (Primary Keys)
• The ERD uses identifiers to uniquely identity 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 frequently used table structure shorthand notation
using the format:
TABLE NAME (KEY-ATTRIBUTE 1, ATTRIBUTE 2, ATTRIBUTE 3, …
ATTRIBUTE k)
For example, a CAR entity may be presented by:
CAR(CAR-VIN, MOD_CODE, CAR_YEAR, CAR_COLOR)
(VIN is vehicle identification number)
Composite Primary Keys
• Primary keys ideally composed of only single attribute.
• Possible to use a composite key
– Primary key composed of more than one attribute
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 7
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
If the CLASS_CODE in figure 4.2 is used as the primary key, the CLASS entity may be
represented in the below form:
CLASS(CLASS-CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME,
CLASS_ROOM, PROF_NUM)
In other hand, if CLASS_CODE is deleted and the composite primary key is the
combination of CRS_CODE and CLASS_SECTION, the CLASS entity may be represented
by:
CLASS(CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM,
PROF_NUM)
4.1.3 Relationships
• Relationship is an association between entities
• Participants are entities that participate in a relationship
• Relationships between entities always operate in both directions. That is, to define the
relationship between the entities named CUSTOMER and INVOICE, you would specify:
• A CUSTOMER may generate many INVOICES.
• Each INVOICE is generated by one CUSTOMER.
Because you know both direction of the relationship between CUSTOMER and INVOICE,
it is easy to see that this relationship can be classified as 1: M.
• Relationship classification is difficult to establish if know only one side of the relationship.
Customer 1 M Invoice
C_No generates
(1,1) (1,20)
For example, if you specify that:
• A Division is managed by one Employee
You do not know if the relationship is 1:1 or 1:M. Therefore, you should ask the question
“Can any employee manage more than one division?” If the answer is yes, the relationship
is 1:M, and the second part of the relationship is then written as:
An Employee may manage many Divisions
If an employee cannot manage more than one division, the relationship is 1:1, and the
second part of the relationship is then written as:
An Employee may manage only one Division.
4.1.4 Connectivity and Cardinality
• Connectivity
– Used to describe the relationship classification.
• Cardinality
– Expresses minimum and maximum number of entity occurrences associated with
one occurrence of related entity
•
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 8
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
(min,max)=(1,1)-(1,4)
4.1.5 Existence Dependence
• Existence dependence
– Exist in database only when it is associated with another related entity occurrence
• Existence independence
– Entity can exist apart from one or more related entities
– Sometimes refers to such an entity as strong or regular entity
4.1.6 Relationship Strength
The concept of the relationship strength is based on how the primary key of a related entity is
defined.
• Weak (non-identifying) relationships
– Exists if PK of related entity does not contain PK component of parent entity. By
default, relationships are established by having the PK of the parent entity appear as a
FK on the related entity. For example, suppose that the COURSE and CLASS entities
are defined as:
COURSE (CRS-CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)
CLASS (CLASS-CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME,
ROOM_CODE, PROF_NUM)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 9
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
In this case, a weak relationship exists between COURSE and CLASS because the
CLASS_CODE is the CLASS entity’s PK, while the CRS_CODE in CLASS is only an
FK. In this example, the CLASS PK did not inherit the PK component from the
COURSE entity.
Weak (Non-Identifying) Relationships
• Strong (Identifying) Relationships
– Exists when PK of related entity contains PK component of parent entity.
For example, the definitions of the COURSE and CLASS entities
COURSE (CRS-CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)
CLASS (CLASS-CODE, CRS_CODE (F.K.), CLASS_SECTION, CLASS_TIME,
ROOM_CODE, PROF_NUM)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 10
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Indicates that is strong relationship exists between COURSE and CLASS entity’s
composite PK is composed of CRS_CODE + CLASS_SECTION.
Strong (Identifying) Relationships
4.1.7 Weak Entities
• Weak entity meets two conditions
– Existence-dependent
• Cannot exist without entity with which it has a relationship
– Has primary key that is partially or totally derived from parent entity in relationship
• Database designer usually determines whether an entity can be described as weak based on
business rules
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 11
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
weak entity = strong relationship
strong entity= weak relationship
Remember that the weak entity inherit part of its primary key from its strong counterpart. For
example, at least part of the DEPENDENT entity’s key shown in figure 4.11 was inherited from the
Employee entity:
EMPLOYEE (EMP-NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB,
EMP_HIREDATE)
DEPENDENT(EMP_NUM, DEP_NUM, DEP_FNAME, DEP_DOB)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 12
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
The tabular comparison between Strong Entity Set and Weak Entity Set is as
follows:
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 13
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
4.1.8 Relationship Participation
• Optional participation
– One entity occurrence does not require corresponding entity occurrence in particular
relationship
• Mandatory participation
– One entity occurrence requires corresponding entity occurrence in particular
relationship
–
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 14
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
4.1.9 Relationship Degree
• Indicates number of entities or participants associated with a relationship
• Unary relationship is otherwise known as recursive relationship
– Association is maintained within single entity
• Binary relationship
– Two entities are associated
• Ternary relationship
– Three entities are associated
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 15
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
1 M
Employee
Employee manages
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 16
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
4.1.10 Recursive Relationships
• Relationship can exist between occurrences of the same entity set
• Naturally found within unary relationship
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 17
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Unary relationships are common in manufacturing industries. For example, Figure 4.19 illustrates
that a rotor assembly (C-130) is composed of many parts, but each part is used to create only one
rotor assembly. Figure 4.19 indicates that a rotor assembly is composed of four 2.5-cm washers,
two cotter pins, one 2.5-cm steel shank, four 10.25-cm rotor blades, and two 2.5-cm hex nuts. The
relationship implemented in Figure 4.19 thus enables you to track each part within each rotor
assembly.
If a part can be used to assemble several different kinds of other parts and is itself composed of
many parts, two tables.
The M:N recursive relationship might be more familiar in a school environment. For instance, note
how the M:N “COURSE requires COURSE” relationship illustrated in Figure 4.17 is implemented
in Figure 4.21. In this example, MATH-243 is a prerequisite to QM-261 and QM-362, while both
MATH-243 and QM-261 are prerequisites to QM-362.
Finally, the 1:M recursive relationship “EMPLOYEE manages EMPLOYEE,” shown in Figure
4.17, is implemented in Figure 4.23.
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 18
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
4.1.11 Composite Entities
• Also known as bridge entities
• Composed of primary keys of each of the entities to be connected
• May also contain additional attributes that play no role in connective process
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 19
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Developing an ER Diagram
• Database design is iterative rather than linear or sequential process
• Iterative process
– Based on repetition of processes and procedures
• Building an ERD usually involves the following activities:
– Create detailed narrative of organization’s description of operations
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 20
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
– Identify business rules based on description of operations
– Identify main entities and relationships from business rules
– Develop initial ERD
– Identify attributes and primary keys that adequately describe entities
– Revise and review ERD
Developing an ER Diagram of Tiny College:
– Tiny College is divided into several schools
• Each school is composed of several departments
– Each department may offer courses
– Each department may have many professors assigned to it
– Each professor may teach up to four classes; each class is section of course
– Student may enroll in several classes, but (s)he takes each class only once during
any given enrollment period
– Each department has several students
• Each student has only a single major and is associated with a single
department
– Each student has an advisor in his or her department
• Each advisor counsels several students
– The relationship between class is taught in a room and the room in the building
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 21
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 22
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 23
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 24
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Database Design Challenges:
Conflicting Goals
• Database design must conform to design standards
• High processing speeds are often a top priority in database design
• Quest for timely information might be focus of database design
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 25
Chapter Four Entity Relationship (ER) Modeling Lec. Manal F. Younis
Summary
• Entity relationship (ER) model
– Uses ERD to represent conceptual database as viewed by end user
– ERM’s main components:
• Entities
• Relationships
• Attributes
– Includes connectivity and cardinality notations
• Connectivity and cardinality are based on business rules
• In ERM, M:N relationship is valid at conceptual level
• ERDs may be based on many different ERMs
• Database designers are often forced to make design compromises
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel 26