Information Management System (CC213 E)

Ms. Maricris R. Manaloto

MODULE 2:i DATA MODELS

Why is Data Models are Important?
Data Modeling and Data Models
The goal of the data model is to make sure that all the data objects required by
the database are completely and accurately represented. Because the data model
What is Data Modeling and Data Models? uses easily understood notations and natural language, it can be reviewed and
verified as correct by the end-users.
Data modeling, the first step in designing a database, refers to the process of
creating a specific data model for a determined problem domain. (A problem The data model is also detailed enough to be used by the database developers as
domain is a clearly defined area within the real world environment, with well- a “blueprint” for building the physical database.
defined scope and boundaries that is to be systematically addressed.) The information contained in a data model will be used to define the relational
tables, the primary and the foreign keys, stored procedures, and triggers.
A data model is a relatively simple representation, usually graphical, of more
complex real-world data structures. In general terms, a model is an abstraction A poorly designed database will require more time in the long-run. Without a
of a more complex real-world object or event. A model’s main function is to careful planning you may create a database that omits data required to create
help you understand the complexities of the real-world environment. Within the critical reports, produces results that are incorrect or inconsistent, and is unable
database environment, a data model represents data structures and their to accommodate changes in user’s requirements
characteristics, relations, constraints, transformations, and other constructs with
the purpose of supporting a specific problem domain.
Data Model Basic Building Blocks
Traditionally, database designers relied on good judgment to help them develop a
good data model. Unfortunately, good judgment is often in the eye of the The basic building blocks of all data models are entities, attributes,
beholder, and it often develops after much trial and error. Different individuals relationships, and constraints.
may come up with a different model. Which one would be the correct one? The
simple answer is “the one that meets all the end-user requirements,” and there Entities
may be more than one correct solution!
Entities are the principal data object about which information is to be collected.
Entities are usually recognizable concepts, either concrete or abstract, such as
The Importance of Data Models person, places, things, or events which have relevance to the database. Some
specific examples of entities are:
Data models can facilitate interaction among the designer, the applications
programmer, and the end user. A well-developed data model can even foster ▪ EMPLOYEES
improved understanding of the organization for which the database design is ▪ PROJECTS
developed. In short, data models are a communication tool. ▪ CUSTOMER
▪ ORGANIZATION
▪ PART
▪ INGREDIENT

1 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto
▪ PURCHASE ORDER Relationships
▪ CUSTOMER ORDER PRODUCT
▪ INVOICES A Relationship represents an association between two or more entities. An
example of a relationship would be:
Attributes
employees are assigned to projects
An attribute is a characteristic of an entity. For example, a CUSTOMER entity
would be described by attributes such as customer last name, customer first projects have subtasks departments
name, customer phone, customer address, and customer credit limit. Attributes
are the equivalent of fields in file systems. manage one or more projects

Attributes describe the entity of which they are associated. i.e., properties used Relationships are classified by their degree, connectivity, cardinality, direction,
to distinguish one entity instance from another. type, and existence. Not all modeling methodologies use all these classifications.

Attributes of entity EMPLOYEE might include: Connectivity and Cardinality:

▪ EmployeeID The connectivity of a relationship describes the mapping of associated entity
▪ First Name
instances in the relationship. The values of connectivity are "one" or "many". The
▪ Last Name
▪ Street Address cardinality of a relationship is the actual number of related occurrences for each
▪ City of the two entities. The basic types of connectivity for relations are: one-to-one,
▪ Local Government Area one-to-many, and many-to-many.
▪ State
▪ Date of First Appointment ▪ One-to-many (1:M or 1..*) relationship. A painter paints many
▪ Current Status different paintings, but each one of them is painted by only one painter.
▪ Date of Birth Thus, the painter (the “one”) is related to the paintings (the “many”).
Therefore, database designers label the relationship “PAINTER paints
Attributes of entity PRODUCT might include: PAINTING” as 1:M. (Note that entity names are often capitalized as a
convention so they are easily identified.) Similarly, a customer (the “one”)
▪ ProductID may generate many invoices, but each invoice (the “many”) is generated
▪ Product_Description by only a single customer. The “CUSTOMER generates INVOICE”
▪ Weight relationship would also be labeled 1:M.
▪ Size
▪ Cost ▪ Many-to-many (M:N or *..*) relationship. An employee may learn
many job skills, and each job skill may be learned by many employees.
Database designers label the relationship “EMPLOYEE learns SKILL” as
M:N. Similarly, a student can take many classes and each class can be

2 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto
taken by many students, thus yielding the M:N relationship label for the Examples of business rules are as follows:
relationship expressed by “STUDENT takes CLASS.”
A customer may generate many invoices.
▪ One-to-one (1:1 or 1..1) relationship. A retail company’s management
structure may require that each of its stores be managed by a single An invoice is generated by only one customer.
employee. In turn, each store manager, who is an employee, manages
only a single store. Therefore, the relationship “EMPLOYEE manages A training session cannot be scheduled for fewer than 10 employees or for
STORE” is labeled 1:1. more than 30 employees.

Constraints Discovering Business Rules

A constraint is a restriction placed on the data. Constraints are important The main sources of business rules are company managers, policy makers,
because they help to ensure data integrity. Constraints are normally expressed in department managers, and written documentation such as a company’s
the form of rules. For example: procedures, standards, or operations manuals. A faster and more direct source of
business rules is direct interviews with end users. Unfortunately, because
An employee’s salary must have values that are between 6,000 and perceptions differ, end users sometimes are a less reliable source when it comes
350,000. to specifying business rules.

A student’s GPA must be between 0.00 and 4.00. Translating Business Rules into Data Model Components
Each class must have one and only one teacher. Business rules set the stage for the proper identification of entities, attributes,
relationships, and constraints. In the real world, names are used to identify
Business Rules objects. If the business environment wants to keep track of the objects, there will
be specific business rules for them.
What is a business rule?
As a general rule,
A business rule is a brief, precise, and unambiguous description of a policy,
procedure, or principle within a specific organization. In a sense, business rules A noun in a business rule will translate into an entity in the model, and
are misnamed: they apply to any organization, large or small—a business, a
government unit, a religious group, or a research laboratory—that stores and A verb (active or passive) associating nouns will translate into a
uses data to generate information. relationship among the entities.
For example, the business rule “a customer may generate many invoices”
Business rules, derived from a detailed description of an organization’s contains two nouns (customer and invoices) and a verb (generate) that
operations, help to create and enforce actions within that organization’s associates the nouns. From this business rule, you could deduct that:
environment. Business rules must be rendered in writing and updated to reflect
any change in the organization’s operational environment. Customer and invoice are objects of interest for the environment and
should be represented by their respective entities.

3 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto
There is a “generate” relationship between customer and invoice.

To properly identify the type of relationship, you should consider that

relationships are bidirectional; that is, they go both ways. For example, the
business rule “a customer may generate many invoices” is complemented by the
business rule “an invoice is generated by only one customer.” In that case, the
relationship is .

As a general rule, to properly identify the relationship type, you should ask two
questions:

How many instances of B are related to one instance of A?

How many instances of A are related to one instance of B?

For example, you can assess the relationship between student and class by
asking two questions:

In how many classes can one student enroll?

Answer:

How many students can enroll in one class?

Answer: The Hierarchical Model

Therefore, the relationship between student and class is . The hierarchical model was developed in the 1960s to manage large amounts
of data for complex manufacturing projects such as the Apollo rocket that landed
on the moon in 1969. Its basic logical structure is represented by an upside-down
The Evolution Of Data Models tree. The hierarchical structure contains levels, or segments. A segment is the
equivalent of a file system’s record type. Within the hierarchy, the top layer (the
root) is perceived as the parent of the segment directly beneath it. For example,
The quest for better data management has led to several different models that in Figure 2.1, the root segment is the parent of the Level 1 segments, which, in
attempt to resolve the file system’s critical shortcomings. Table 2.1 traces the turn, are the parents of the Level 2 segments, etc. The segments below other
evolution of the major data models. segments are the children of the segment above. In short, the hierarchical model
depicts a set of one-to-many (1:M) relationships between a parent and its
children segments. (Each parent can have many children, but each child has only
one parent.)

4 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

The Network Model

The Relational Model
The network model was created to represent complex data relationships more
effectively than the hierarchical model, to improve database performance, and to The relational model was introduced in 1970 by E. F. Codd (of IBM) in his
impose a database standard. landmark paper “A Relational Model of Data for Large Shared Databanks”
(Communications of the ACM, June 1970, pp. 377−387). The relational model
In the network model, the user perceives the network database as a collection of represented a major breakthrough for both users and designers.
records in 1:M relationships. However, unlike the hierarchical model, the network
model allows a record to have more than one parent. In network database The relational model foundation is a mathematical concept known as a relation.
terminology, a relationship is called a set. Each set is composed of at least two To avoid the complexity of abstract mathematical theory, you can think of a
record types: an owner record and a member record. A set represents a 1:M relation (sometimes called a table) as a matrix composed of intersecting rows
relationship between the owner and the member. An example of such a and columns. Each row in a relation is called a tuple. Each column represents an
relationship is depicted in Figure 2.2. attribute. The relational model also describes a precise set of data manipulation
constructs based on advanced mathematical concepts.

The relational data model is implemented through a very sophisticated relational

database management system (RDBMS). The RDBMS performs the same
basic functions provided by the hierarchical and network DBMS systems, in

5 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto
addition to a host of other functions that make the relational data model easier to The Entity Relationship Model
understand and implement. Arguably the most important advantage of the
RDBMS is its ability to hide the complexities of the relational model from the The conceptual simplicity of
user. The RDBMS manages all of the physical details, while the user sees the relational database technology
relational database as a collection of tables in which data are stored. The user triggered the demand for
can manipulate and query the data in a way that seems intuitive and logical. RDBMSs. In turn, the rapidly
increasing requirements for
transaction and information
created the need for more
complex database
implementation structures, thus
creating the need for more
effective database design tools.

Relational model was a vast

improvement over the
hierarchical and network models,
it still lacked the features that
would make it an effective
database design tool. Because it
is easier to examine structures
graphically than to describe them in text, database designers prefer to use a
graphical tool in which entities and their relationships are pictured. Thus, the
entity relationship (ER) model, or ERM, has become a widely accepted
standard for data modeling.
The relationship type (1:1, 1:M, or M:N) is often shown in a relational schema,
an example of which is shown in Figure 2.4. A relational diagram is a Peter Chen first introduced the ER data model in 1976; it was the graphical
representation of the relational database’s entities, the attributes within those representation of entities and their relationships in a database structure that
entities, and the relationships between those entities. quickly became popular because it complemented the relational data model
concepts. The relational data model and ERM combined to provide the foundation
Another reason for the relational data model’s rise to dominance is its powerful for tightly structured database design. ER models are normally represented in an
and flexible query language. For most relational database software, the query entity relationship diagram (ERD), which uses graphical representations to
language is Structured Query Language (SQL), which allows the user to specify model database components.
what must be done without specifying how it must be done. The RDBMS uses
SQL to translate user queries into instructions for retrieving the requested data.
SQL makes it possible to retrieve data with far less effort than any other
database or file environment.

6 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto
The ER model is based on the following components:

• Entity. Earlier in this chapter, an entity was defined as anything about The right side of Figure 2.5 illustrates the Crow’s Foot notation. The name
which data are to be collected and stored. An entity is represented in the “Crow’s Foot” is derived from the three-pronged symbol used to represent the
ERD by a rectangle, also known as an entity box. “many” side of the relationship. As you examine the basic Crow’s Foot ERD in
Figure 2.5, note that the connectivities are represented by symbols. For example,
the “1” is represented by a short line segment and the “M” is represented by the
• Relationships. Relationships describe associations among data. Most three-pronged “crow’s foot.” In this example the relationship name is written
relationships describe associations between two entities. When the basic above the relationship line. In Figure 2.5, entities and relationships are shown in
data model components were introduced, three types of relationships a horizontal format, but they also may be oriented vertically. The entity location
among data were illustrated: one-to-many (1:M), many-to-many (M:N), and the order in which the entities are presented are immaterial; just remember
and one-to-one (1:1). The ER model uses the term connectivity to label to read a 1:M relationship from the “1” side to the “M” side.
the relationship types.

Figure 2.5 shows the different types of relationships using two ER notations: the
original Chen notation and the more current Crow’s Foot notation.

7 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

8 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

The Object-Oriented (OO) Model invoice references one or more lines, and each line represents an item purchased
by a customer. Figure 2.6 illustrates the object representation for this simple
Increasingly complex real-world problems demonstrated a need for a data model invoicing problem, as well as the equivalent UML class diagram and ER model.
that more closely represented the real world. In the object-oriented data The object representation is a simple way to visualize a single object occurrence.
model (OODM), both data and their relationships are contained in a single
structure known as an object. In turn, the OODM is the basis for the object- Figure 2.6, describes that:
oriented database management system (OODBMS).
• The object representation of the INVOICE includes all related objects
The OO data model is based on the following components: within the same object box. Note that the connectivities (1 and M)
indicate the relationship of the related objects to the INVOICE. For
• An object is an abstraction of a real-world entity. In general terms, an example, the 1 next to the CUSTOMER object indicates that each INVOICE
object may be considered equivalent to an ER model’s entity. is related to only one CUSTOMER. The M next to the LINE object indicates
that each INVOICE contains many LINEs.
• Attributes describe the properties of an object.
• The UML class diagram uses three separate object classes (CUSTOMER,
• Objects that share similar characteristics are grouped in classes. A class INVOICE, and LINE) and two relationships to represent this simple
is a collection of similar objects with shared structure (attributes) and invoicing problem. Note that the relationship connectivities are
behavior (methods). A class’s method represents a real-world action. represented by the 1..1, 0..* and 1..* symbols and that the relationships
are named in both ends to represent the different “roles” that the objects
• Classes are organized in a class hierarchy. The class hierarchy resembles play in the relationship.
an upside-down tree in which each class has only one parent.
• The ER model also uses three separate entities and two relationships to
represent this simple invoice problem.
• Inheritance is the ability of an object within the class hierarchy to inherit
the attributes and methods of the classes above it. For example, two
classes, CUSTOMER and EMPLOYEE, can be created as subclasses from
the class PERSON. In this case, CUSTOMER and EMPLOYEE will inherit all
attributes and methods from PERSON.

Object-oriented data models are typically depicted using Unified Modeling

Language (UML) class diagrams. Unified Modeling Language (UML) is a
language based on OO concepts that describes a set of diagrams and symbols
that can be used to graphically model a system. UML class diagrams are used
to represent data and their relationships within the larger UML object-oriented
systems modeling language.

To illustrate the main concepts of the object-oriented data model, let’s use a
simple invoicing problem. In this case, invoices are generated by customers, each

9 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

10 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

Degrees of Data Abstraction

A database designer starts with an abstract view of the overall data environment and adds details as the design comes closer to implementation. Using levels of
abstraction can also be very helpful in integrating multiple (and sometimes conflicting) views of data as seen at different levels of an organization.

In the early 1970s, the American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) defined a framework for data
modeling based on degrees of data abstraction. The ANSI/SPARC architecture (as it is often referred to) defines three levels of data abstraction: external, conceptual,
and internal.

The External Model

The external model is the end users’ view of the data

environment. The term end users refers to people who use
the application programs to manipulate the data and
generate information. End users usually operate in an
environment in which an application has a specific business
unit focus. Companies are generally divided into several
business units, such as sales, finance, and marketing.

11 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

The Conceptual Model

Having identified the external views, a conceptual model is used, graphically

represented by an ERD (as in Figure 2.10), to integrate all external views into a
single view. The conceptual model represents a global view of the entire
database as viewed by the entire organization. That is, the conceptual model
integrates all external views (entities, relationships, constraints, and processes)
into a single global view of the entire data in the enterprise. Also known as a
conceptual schema, it is the basis for the identification and high-level
description of the main data objects (avoiding any database model-specific
details).

The conceptual model yields some very important advantages.

• First, it provides a relatively easily understood bird’s-eye (macro level)

view of the data environment.

• Second, the conceptual model is independent of both software and

hardware.

Software independence means that the model does not depend on the DBMS
software used to implement the model. Hardware independence means that
the model does not depend on the hardware used in the implementation of the
model. Therefore, changes in either the hardware or the DBMS software will have
no effect on the database design at the conceptual level. Generally, the term
logical design is used to refer to the task of creating a conceptual data model
that could be implemented in any DBMS.

12 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

The Internal Model

Once a specific DBMS has been

selected, the internal model maps the
conceptual model to the DBMS. The
internal model is the representation
of the database as “seen” by the
DBMS. In other words, the internal
model requires the designer to match
the conceptual model’s characteristics
and constraints to those of the
selected implementation model. An
internal schema depicts a specific
representation of an internal model,
using the database constructs
supported by the chosen database.

13 |MODULE 2 (LECTURE): D a t a M o d e l s
 Information Management System (CC213 E)
Ms. Maricris R. Manaloto

The Physical Model

The physical model operates at the lowest level of abstraction, describing the
way data are saved on storage media such as disks or tapes. The physical
model requires the definition of both the physical storage devices and the
(physical) access methods required to reach the data within those storage
devices, making it both software- and hardware dependent. The storage
structures used are dependent on the software (the DBMS and the operating
system) and on the type of storage devices that the computer can handle. The
precision required in the physical model’s definition demands that database
designers who work at this level have a detailed knowledge of the hardware
and software used to implement the database design.

A summary of the levels of data abstraction is given in Table 2.4.

14 |MODULE 2 (LECTURE): D a t a M o d e l s