Page |1
INFORMATION TECHNOLOGY AND INFORMATION SYSTEM
DEPARTMENT
DATABASE MANAGEMENT SYSTEM
EXERCISE
2
MODELING DATA IN AN ORGANIZATION
NAME SECTION
DATE PERFORMED DATE FINISHED
Page |2
I. OBJECTIVES
At the end of this exercise, students must be able to:
a) Examine business rules
b) Analyze and model relationship based on a set of information
requirements
c) Use a relationship matrix to track the existence or relationships between
entities
II. BACKGROUND INFORMATION
Logical Data Model
The goal of a logical data model is to develop an entity relationship
diagram that represents the information requirements of the business. Logical
data modeling is independent of the hardware or software to be used for the
implementation.
Entity-Relationship Diagram (ERD) is a detailed, logical and graphical
representation of the entities, associations and data elements for an
organization or business area.
Table 2.1 Components of an ERD
Component Purpose Example
Entity An object or concept - DEPARTMENT
about which you want - EMPLOYEE
to store information. - JOB
Relationship A natural association - DEPARTMENT may
that exists between contain one or many
two or more entities. EMPLOYEEs.
- An EMPLOYEE must
be assigned to one
and only one
DEPARTMENT.
Attributes Description of entities EMPLOYEE
and specific pieces of - First name
information that must - Last name
be known. - Email
Associative entity is an entity type that associates the instances of one or
more entity types and contains attributes that are peculiar to the relationship
Page |3
between those entity instances. It is a relationship that can be modeled as an
entity type.
Relationship Types
• One-to-One (1:1)
• One-to-Many (1:M) or Many-to-One (M:1)
• Many-to-Many (M:M)
Cardinality is the minimum and maximum number of values in the
relationship.
• Minimum values can be either optional (zero) or mandatory
(at least one).
• Maximum values can be either one or many.
Figure 2.1 Cardinalities
Step-by-Step Construction of ERD
1. Identify entities
2. Find relationships
3. Draw rough ERD
4. Fill in cardinality
5. Define primary keys
6. Draw key-based ERD
7. Identify attributes
8. Map attributes
9. Draw fully attributed ERD
10. Check results
III. EXPERIMENTAL PROCEDURE
Prepare an E-R diagram for a real estate firm that lists property for sale. The
following describes this organization:
The firm has a number of sales offices in several states. Attributes of sales
office include Office_Number (identifier/key) and Location.
Each sales office is assigned one or more employees. Attributes of
employee include Employee_ID (identifier/key) and Employee_Name. An
employee must be assigned to only one sales office.
Page |4
For each sales office, there is always one employee assigned to manage
that office. An employee may manage only the sales office to which
he/she is assigned.
The firm lists property for sale. Attributes of property include Property_ID
(identifier) and Location. Components of Location include Address, City,
State, and Zip_Code.
Each unit of property must be listed with one (and only one) of the sales
offices. A sales office may have any number of properties listed, or may
have no properties listed.
Each unit of property has one or more owners. Attributes of owners are
Owner_ID (identifier) and Owner_Name. An owner may own one or more
units of property. An attribute of the association between property and
owner is Percent_Owned.
Task 1
Analyze the business rule and fill-in the relationship matrix in Table 2.2 to
track the existence of relationships between entities.
Table 2.2 Relationship Matrix
Task 2
Using CASE TOOL create a fully attributed ERD with keys based from the
relationship matrix you have created in Task 1.
Note: Be sure to include all attributes, relationships, and minimum and maximum
cardinalities
Page |5
IV. QUESTION AND ANSWER
1. Give three reasons why many system designers believe that data modeling is the
most important part of the system development process?
2. State a rule that says when to extract an attribute from one entity type and place
it in a linked entity type.
[ CITATION 20ht \l 13321 ]
V. REFERENCES
Hoffer, J.A., Prescott, M.B., McFadden, F.R. (2016). Modern Database Management
12th Edition, Prentice Hall.
(2020). Retrieved from http://users.csc.calpoly.edu/~jdalbey/205/Lectures/HOWTO-
ERD.htm
