LEARNING THE DATA

MODELING TECHNIQUES
WITH EXAMPLES
December 2, 2014

What is Data Modeling?

Data modeling is oftentimes the first step in programs that are object oriented
and are about database design. The main job of data modeling is to identify
data or any kind of information that is required by the system so it can store it,
maintain it or let others access it when needed.

Importance of Data Modeling in Business

Business analysts all over the world use a combination of different
techniques that include different type of diagrams, matrices, model data and
several text based descriptions. Each data modeling technique will be helping
you analyze and communicate several different information about the data
related necessities.
[Related: Professional business writing standards for effective
communication]
The Role of Data Modelers
The process that is used to analyze and define the data requirements that are
needed to support any kind of business processes inside the scope of
 representing the information systems in any organization. Hence, the process
of data modeling consists of several professional data modelers who work in a
close relation of the stakeholders of any business. That’s not it, the data
modelers are also supposed to work with anyone who is a user of that
information system.
4 Basic Data Modeling Techniques: Get the
Most From Your Data
Today, we will be discussing the four major type of data modeling
techniques. The data modeling techniques are listed below with further
explanations about what they are and how they work.
 Entity Relationship Diagram.
 Data Mapping Specification.
 Data Matrix.
 Data Flow Diagram.
First of all, we will be discussing Entity Relationship Diagram, check out the
detailed description below.

[Related: PEST analysis for strategic marketing and management

environment ]
Entity Relationship Diagram
The first point on your list is Entity Relationship Diagram which is often known
as (ERD).
What is an ERD or Entity Relationship Diagram
An ERD is responsible for modeling things such
as relationships, entities and attributes.
There are two types of ERDs, the simpler ones that we are talking about in
this point and the advanced ones that are used in Visual Model Sample Pack.
Explaining ERD with Example
Here is a very simple ERD diagram that explains everything you need to know
at the moment about ERD.

What is an Entity and Attributes in ERD

In the diagram above, the customer and the order are the entities, however,
the items that are listed inside the entities are the attributes of the entities.
What is a Relationship in an ERD

Furthermore, the line in the middle is representing the relationship between

the customer and the order.
Main Phases of Data Design and the Importance of ERD
in a Database
For those who are using or going to use Entity Relationship Diagram, be
aware that you can use it to framework all your data at more than one level of
particularity.

The level of particularity usually spans from the physical database that is

considered the lowest one, then comes the middle one which is also known
as logical data base, and on the last, we get business domain model that is
the highest level of database.
When to Use Entity Relationship Diagram?
Wondering when should you use Entity Relationship Diagram? It is the best
choice when you have more than one database table or concept  and you are
in need of analyzing the boundaries of all the concepts or the tables.

This becomes simple because by defining an attribute, you will easily figure
out the entity it belongs to. Similarly, once you define a relationship, it will be
easy to figure out why and how each entity is relating to all the other entities in
the model.
[Related: How to Use Microsoft Excel’s HLOOKUP function for effective
data analysis ]
Data Mapping Specification
Data Mapping Specification is a data modeling technique that is responsible
for showing how the collected information stored in two different databases
associate to each other.

Almost all of the times, these databases are related to two information
technology systems, at times, these systems are owned by your own
organization and some third-party vendor, or two different organizations who
are cooperating.

When to Use Data Mapping Specification?

For instance, if you work for a company and the company, including you,
creates a data mapping specification that allows the company to get the job
content from some of the company’s top tier clients who are not willing to
manually input all the details in your company’s employ hub.

This is where the data mapping specification comes in, every time
you connect two systems together through the data exchange or an import,
that will be the time when data mapping specification will be your best bet.
[Related: What are Access 2013 web apps? A look into new features and
tools ]
Data Matrix
The third on the list is data matrix.
Defining Data Matrix and It’s Role in Database Systems
For those of you who are not aware of what data matrix is; it is a way to
provide more elaborated insight of the data model. Data matrix is quite
tangible and can easily take several different forms according to the situation
you are working under.

The Basic Structure of a Data Matrix: How to Prepare Data Matrix

Worksheets
Ordinarily, a data matrix is caught in a spreadsheet format. Furthermore, the
data matrix comes with a list of attributes, the list of attributes is accompanied
with a brief information regarding each of the listed attributes. If you are
 looking for the type of additional information that accompanies that data
matrix, the list is given below.

 Notes.
 What data is optional or required.
 Type of data.
 Sample data.
 Values that are allowed.
When to Use the Data Matrix
A data matrix is any business analyst’s best choice when he or she is in need
of analyzing all the attributes in his/her data model. All of this information is
most of the times used in shaping up the physical databases. Data developers
or data architects will be the ones to utilize this information properly.

[Related: Six important steps for writing a successful finance analysis ]

Data Flow Diagram
A data flow diagram is the diagram that exemplifies how the information flows
through the system, into and of the system as well.

Importance of DFD or Data Flow Diagram

Data flow diagrams are easy to create and can be created using the simple
workflow diagram. Data flow diagram is never responsible for giving you
ample information about how data is maintained or created by the system but
if you are looking for information that tells you how data is flowing through
the system or a set of systems that are connected to each other, then you
are in the right place as the data flow diagram tells you about how data is
stored, processed, and how data is outputted.
When to Use Data Flow Diagram?
Normally, a data flow diagram is your best choice when your data needs to be
processed over and over again, data flow diagram helps to clear how and
when the process are in need of execution.

Once that’s done, every data store can be modeled. You can model the data
using techniques like data flow diagram, entity relationship diagram, data
matrix. Each of the process utilizes data mapping specification.
Selecting The Best Data Modeling
Technique
It is highly unlikely that all four data modeling techniques are used by a
business analyst to model your data. A seasoned business analyst will
analyze all four of the data modeling techniques and then pick out the best
one to model your data.

Often times, business analyst will ask the stakeholders a series of questions

that will help him clarify the type of data modeling technique he or she should
use to model the data at hand

LEARNING THE DATA

MODELING TECHNIQUES
WITH EXAMPLES
December 2, 2014

What is Data Modeling?

Data modeling is oftentimes the first step in programs that are object oriented
and are about database design. The main job of data modeling is to identify
data or any kind of information that is required by the system so it can store it,
maintain it or let others access it when needed.

Importance of Data Modeling in Business

Business analysts all over the world use a combination of different
techniques that include different type of diagrams, matrices, model data and
several text based descriptions. Each data modeling technique will be helping
you analyze and communicate several different information about the data
related necessities.
 The Role of Data Modelers
The process that is used to analyze and define the data requirements that are
needed to support any kind of business processes inside the scope of
representing the information systems in any organization. Hence, the process
of data modeling consists of several professional data modelers who work in a
close relation of the stakeholders of any business. That’s not it, the data
modelers are also supposed to work with anyone who is a user of that
information system.
4 Basic Data Modeling Techniques: Get the
Most From Your Data
Today, we will be discussing the four major type of data modeling
techniques. The data modeling techniques are listed below with further
explanations about what they are and how they work.
 Entity Relationship Diagram.
 Data Mapping Specification.
 Data Matrix.
 Data Flow Diagram.
First of all, we will be discussing Entity Relationship Diagram, check out the
detailed description below.

[Related: PEST analysis for strategic marketing and management

environment ]
Entity Relationship Diagram
The first point on your list is Entity Relationship Diagram which is often known
as (ERD).
What is an ERD or Entity Relationship Diagram
An ERD is responsible for modeling things such
as relationships, entities and attributes.
There are two types of ERDs, the simpler ones that we are talking about in
this point and the advanced ones that are used in Visual Model Sample Pack.
Explaining ERD with Example
Here is a very simple ERD diagram that explains everything you need to know
at the moment about ERD.

What is an Entity and Attributes in ERD

In the diagram above, the customer and the order are the entities, however,
the items that are listed inside the entities are the attributes of the entities.
What is a Relationship in an ERD

Furthermore, the line in the middle is representing the relationship between

the customer and the order.
Main Phases of Data Design and the Importance of ERD
in a Database
For those who are using or going to use Entity Relationship Diagram, be
aware that you can use it to framework all your data at more than one level of
particularity.

The level of particularity usually spans from the physical database that is

considered the lowest one, then comes the middle one which is also known
as logical data base, and on the last, we get business domain model that is
the highest level of database.
When to Use Entity Relationship Diagram?
Wondering when should you use Entity Relationship Diagram? It is the best
choice when you have more than one database table or concept  and you are
in need of analyzing the boundaries of all the concepts or the tables.

This becomes simple because by defining an attribute, you will easily figure
out the entity it belongs to. Similarly, once you define a relationship, it will be
easy to figure out why and how each entity is relating to all the other entities in
the model.
[Related: How to Use Microsoft Excel’s HLOOKUP function for effective
data analysis ]
Data Mapping Specification
Data Mapping Specification is a data modeling technique that is responsible
for showing how the collected information stored in two different databases
associate to each other.

Almost all of the times, these databases are related to two information
technology systems, at times, these systems are owned by your own
organization and some third-party vendor, or two different organizations who
are cooperating.

When to Use Data Mapping Specification?

For instance, if you work for a company and the company, including you,
creates a data mapping specification that allows the company to get the job
content from some of the company’s top tier clients who are not willing to
manually input all the details in your company’s employ hub.

This is where the data mapping specification comes in, every time
you connect two systems together through the data exchange or an import,
that will be the time when data mapping specification will be your best bet.
Data Matrix
The third on the list is data matrix.
Defining Data Matrix and It’s Role in Database Systems
For those of you who are not aware of what data matrix is; it is a way to
provide more elaborated insight of the data model. Data matrix is quite
tangible and can easily take several different forms according to the situation
you are working under.

The Basic Structure of a Data Matrix: How to Prepare Data Matrix

Worksheets
Ordinarily, a data matrix is caught in a spreadsheet format. Furthermore, the
data matrix comes with a list of attributes, the list of attributes is accompanied
with a brief information regarding each of the listed attributes. If you are
looking for the type of additional information that accompanies that data
matrix, the list is given below.
 Notes.
 What data is optional or required.
 Type of data.
 Sample data.
 Values that are allowed.
When to Use the Data Matrix
A data matrix is any business analyst’s best choice when he or she is in need
of analyzing all the attributes in his/her data model. All of this information is
most of the times used in shaping up the physical databases. Data developers
or data architects will be the ones to utilize this information properly.

[Related: Six important steps for writing a successful finance analysis ]

Data Flow Diagram
A data flow diagram is the diagram that exemplifies how the information flows
through the system, into and of the system as well.

Importance of DFD or Data Flow Diagram

Data flow diagrams are easy to create and can be created using the simple
workflow diagram. Data flow diagram is never responsible for giving you
ample information about how data is maintained or created by the system but
if you are looking for information that tells you how data is flowing through
the system or a set of systems that are connected to each other, then you
are in the right place as the data flow diagram tells you about how data is
stored, processed, and how data is outputted.

When to Use Data Flow Diagram?

Normally, a data flow diagram is your best choice when your data needs to be
processed over and over again, data flow diagram helps to clear how and
when the process are in need of execution.

Once that’s done, every data store can be modeled. You can model the data
using techniques like data flow diagram, entity relationship diagram, data
matrix. Each of the process utilizes data mapping specification.
 Selecting The Best Data Modeling
Technique
It is highly unlikely that all four data modeling techniques are used by a
business analyst to model your data. A seasoned business analyst will
analyze all four of the data modeling techniques and then pick out the best
one to model your data.

Often times, business analyst will ask the stakeholders a series of questions

that will help him clarify the type of data modeling technique he or she should
use to model the data at hand

Chapter 4 Types of Data Models

ADRIENNE WATT & NELSON ENG

High-level Conceptual Data Models
High-level conceptual data models provide concepts for presenting data in ways that
are close to the way people perceive data. A typical example is the entity relationship
model, which uses main concepts like entities, attributes and relationships. An entity
represents a real-world object such as an employee or a project. The entity has
attributes that represent properties such as an employee’s name, address and birthdate.
A relationship represents an association among entities; for example, an employee
works on many projects. A relationship exists between the employee and each project.

Record-based Logical Data Models
Record-based logical data models provide concepts users can understand but are not
too far from the way data is stored in the computer. Three well-known data models of
this type are relational data models, network data models and hierarchical data
models.

 The relational model represents data as relations, or tables. For example, in

the membership system at Science World, each membership has many members
(see Figure 2.2 in Chapter 2). The membership identifier, expiry date and address
 information are fields in the membership. The members are individuals such as
Mickey, Minnie, Mighty, Door, Tom, King, Man and Moose. Each record is said to be
an instance of the membership table.

 The network model represents data as record types. This model also

represents a limited type of one to many relationship called a set type, as shown in
Figure 4.1.

Figure 4.1. Network model diagram.

 The hierarchical model represents data as a hierarchical tree structure. Each

branch of the hierarchy represents a number of related records. Figure 4.2 shows
this schema in hierarchical model notation.

Figure 4.2. Hierarchical model diagram.

Key Terms
hierarchical model: represents data as a hierarchical tree structure

instance: a record within a table

network model: represents data as record types

relation: another term for table

relational model: represents data as relations or tables

 set type: a limited type of one to many relationship 
Exercises

1. What is a data model?

2. What is a high-level conceptual data model?
3. What is an entity? An attribute? A relationship?
4. List and briefly describe the common record-based logical data models.

Attribution
This chapter of Database Design is a derivative copy of Database System
Concepts by Nguyen Kim Anh licensed under Creative Commons Attribution License
3.0 license

The following material was written by Adrienne Watt:

1. Key Terms
2. Exercises

Chapter 5 Data Modelling

ADRIENNE WATT

Data modelling is the first step in the process of database design. This step is
sometimes considered to be a high-level and abstract design phase, also referred to as
conceptual design. The aim of this phase is to describe:

 The data contained in the database (e.g., entities: students, lecturers, courses,

subjects)
 The relationships between data items (e.g., students are supervised by lecturers;
lecturers teach courses)
 The constraints on data (e.g., student number has exactly eight digits; a subject
has four or six units of credit only)

In the second step, the data items, the relationships and the constraints are all
expressed using the concepts provided by the high-level data model. Because these
 concmepts do not include the implementation details, the result of the data modelling
process is a (semi) formal representation of the database structure. This result is quite
easy to understand so it is used as reference to make sure that all the user’s
requirements are met.

The third step is database design. During this step, we might have two sub-steps: one
called database logical design, which defines a database in a data model of a specific
DBMS, and another called database physical design, which defines the internal
database storage structure, file organization or indexing techniques. These two sub-
steps are database implementation and operations/user interfaces building steps.

In the database design phases, data are represented using a certain data model.
The data model is a collection of concepts or notations for describing data, data
relationships, data semantics and data constraints. Most data models also include a set
of basic operations for manipulating data in the database.

Degrees of Data Abstraction

In this section we will look at the database design process in terms of specificity. Just
as any design starts at a high level and proceeds to an ever-increasing level of detail,
so does database design. For example, when building a home, you start with how
many bedrooms and bathrooms the home will have, whether it will be on one level or
multiple levels, etc. The next step is to get an architect to design the home from a
more structured perspective. This level gets more detailed with respect to actual room
sizes, how the home will be wired, where the plumbing fixtures will be placed,
etc. The last step is to hire a contractor to build the home. That’s looking at the design
from a high level of abstraction to an increasing level of detail.

The database design is very much like that. It starts with users identifying the business
rules; then the database designers and analysts create the database design; and then the
database administrator implements the design using a DBMS.

The following subsections summarize the models in order of decreasing level

of abstraction.

External models
 Represent the user’s view of the database
 Contain multiple different external views
 Are closely related to the real world as perceived by each user
 Conceptual models
 Provide flexible data-structuring capabilities
 Present a “community view”: the logical structure of the entire database
 Contain data stored in the database
 Show relationships among data including:
o Constraints
o Semantic information (e.g., business rules)
o Security and integrity information
 Consider a database as a collection of entities (objects) of various kinds
 Are the basis for identification and high-level description of main data objects;
they avoid details
 Are database independent regardless of the database you will be using

Internal models
The three best-known models of this kind are the relational data model, the network
data model and the hierarchical data model. These internal models:

 Consider a database as a collection of fixed-size records

 Are closer to the physical level or file structure
 Are a representation of the database as seen by the DBMS.
 Require the designer to match the conceptual model’s characteristics and
constraints to those of the selected implementation model
 Involve mapping the entities in the conceptual model to the tables in the
relational model

Physical models
 Are the physical representation of the database
 Have the lowest level of abstractions
 Are how the data is stored; they deal with
o Run-time performance
o Storage utilization and compression
o File organization and access methods
o Data encryption
 Are the physical level – managed by the operating system (OS)
 Provide concepts that describe the details of how data are stored in the
computer’s memory

Data Abstraction Layer

In a pictorial view, you can see how the different models work together. Let’s look at
this from the highest level, the external model.

The external model is the end user’s view of the data. Typically a database is an
enterprise system that serves the needs of multiple departments. However, one
department is not interested in seeing other departments’ data (e.g., the human
resources (HR) department does not care to view the sales department’s data).
Therefore, one user view will differ from another.

The external model requires that the designer subdivide a set of requirements and
constraints into functional modules that can be examined within the framework of
their external models (e.g., human resources versus sales).

As a data designer, you need to understand all the data so that you can build an
enterprise-wide database. Based on the needs of various departments, the conceptual
model is the first model created.

At this stage, the conceptual model is independent of both software and hardware. It
does not depend on the DBMS software used to implement the model. It does not
depend on the hardware used in the implementation of the model. Changes in either
hardware or DBMS software have no effect on the database design at the conceptual
level.

Once a DBMS is selected, you can then implement it. This is the internal model. Here
you create all the tables, constraints, keys, rules, etc.  This is often referred to as
the logical design.

The physical model is simply the way the data is stored on disk. Each database vendor
has its own way of storing the data.
 Figure 5.1. Data abstraction layers.

Schemas
A schema is an overall description of a database, and it is usually represented by
the entity relationship diagram (ERD). There are many subschemas that represent
external models and thus display external views of the data. Below is a list of items to
consider during the design process of a database.

 External schemas: there are multiple

 Multiple subschemas: these display multiple external views of the data
 Conceptual schema: there is only one. This schema includes data items,
relationships and constraints, all represented in an ERD.
 Physical schema: there is only one

Logical and Physical Data Independence

Data independence refers to the immunity of user applications to changes made in the
definition and organization of data. Data abstractions expose only those items that are
important or pertinent to the user. Complexity is hidden from the database user.

Data independence and operation independence together form the feature of data

abstraction. There are two types of data independence: logical and physical.

Logical data independence

A logical schema is a conceptual design of the database done on paper or a
whiteboard, much like architectural drawings for a house. The ability to change the
logical schema, without changing the external schema or user view,  is called logical
data independence. For example, the addition or removal of new entities, attributes or
relationships to this conceptual schema should be possible without having to change
existing external schemas or rewrite existing application programs.  

In other words, changes to the logical schema (e.g., alterations to the structure of the
database like adding a column or other tables) should not affect the function of the
application (external views).

Physical data independence

Physical data independence refers to the immunity of the internal model to changes in
the physical model. The logical schema stays unchanged even though changes are
made to file organization or storage structures, storage devices or indexing strategy.

Physical data independence deals with hiding the details of the storage structure from
user applications. The applications should not be involved with these issues, since
there is no difference in the operation carried out against the data.