Copyright © 2020 by the Pamantasan ng Lungsod ng Valenzuela

All rights reserved. No part of this module may be reproduced, repurposed, distributed, or transmitted in
any form or by any means including photocopying, reprinting, or other electronic or mechanical
methods without the prior written permission of PLV and the individual developers of instructional
materials (IMs) except in the case of brief quotations embodied in critical and creative reviews and
certain other noncommercial uses permitted by the Copyright Law. For permission request, address your
written correspondence whether printed or electronic to the Chair of the University Committee on
Instructional Materials Development and Evaluation at the address below:

Pamantasan ng Lungsod ng Valenzuela

Tongco St., Maysan, Valenzuela City
College: Department: Course Course Title:
Engineering and Information Information Code: Fundamentals of Database
Technology Technology IM 101 Systems
Faculty: Chairperson:
Rommel P. Apostol, MIT PATRICK LUIS M. FRANCISCO, MIT

Understanding the Flow of Data in Everyday Transactions,

A module in IM 101: Fundamentals of Database Systems
 Foreword
This module aims to help students understand, familiarize, and adopt the use of fundamental data
processes and operation to utilize them in developing more efficient and secure information systems.
This module contains lessons that introduce them to the core concept of database systems and
management and answers the essential questions presented in each part of the module. The learning
outcomes from each part of the module will help the students understand the essential questions given
and at a certain point in the discussions, an evaluation will be done through the use of different
activities.
At the end of this module, the student will be able to understand the basic concepts and use of
database systems and be able to use tools and software in manipulating them.
 Table of Contents
Unit Six
Essential Questions …………………………………………………………………………….. 1
Intended Learning Outcomes …………………………………………………………………... 1
Assesment Task
Diagnostic ………………………………………………………………………………. 1
Formative ………………………………………………………………………………. 18
Summative ……………………………………………………………………………... 18
Lessons Input …………………………………………………………………………………… 1
References ……………………………………………………………………………………… 19
 1

Unit Six – Transaction Management, Data Model and Entity-Relationship Diagram

This unit explains the importance of transactions in a database management system. It will also cover
the use of the data model and how it is important in creating an entity-relationship diagram.

 Essential Question
How do we define a database transaction?
How do we define a data model?
How do the elements of a data model work?
When and why did the different data models appear?
What are the business rules?

 Intended Learning Outcomes

The definition of a data model and the elements that make up a data model
The evolution and history of data models
Business rules creation and the process of creating Chen and Crow’s Foot diagrams

 Diagnostic Assessment Task

At the start of the lesson the instructor will provide the following activities to gauge the students
understanding of the lesson beforehand :
1. The instructor will ask the students to write a list of at least 2 words each to which they think is
related to the terms data model, relationship between objects, and business rules

 Lessons Input
Transaction Management
When several users access (and possibly modify) a database concurrently, the DBMS must order
their requests carefully to avoid conflicts.
A transaction is any one execution of a user program in a DBMS. (Executing the same program
several times will generate several transactions.) This is the basic unit of change as seen by the
DBMS: Partial transactions are not allowed, and the effect of a group of transactions is equivalent to
some serial execution of all transactions.
 2

Data Modeling
Data model – is a relatively simple representation, usually graphical, of more complex real-world
data structures. In general terms, a model is an abstraction of a more complex real-world object or
event. A model’s main function is to help you understand the complexities of the real-world
environment. Within the database environment, a data model represents data structures and their
characteristics, relations, constraints, transformations, and other constructs to support a specific
problem domain.
An implementation-ready data model should contain at least the following components:
• A description of the data structure that will store the end-user data.
• A set of enforceable rules to guarantee the integrity of the data.
• A data manipulation methodology to support real-world data transformations.

Data Model Basic Building Blocks

The basic building blocks of all data models are entities, attributes, relationships, and constraints.
Entity – anything (a person, a place, a thing, or an event)about which data are to be collected and
stored. It represents a particular type of object in the real world, each entity occurrence is unique and
distinct. For example, a CUSTOMER entity would have many distinguishable customer occurrences,
such as John Smith, Pedro Dinamita, etc. Entities may be physical objects, such as customers or
products, but entities may also be abstractions, such as flight routes or musical concerts.
Attribute – a characteristic of an entity. For example, a CUSTOMER entity would be described by
attributes such as customer last name, customer first name, customer phone, customer address, and
customer credit limit. Attributes are the equivalent of fields in file systems.
Relationship – describes an association among entities. For example, a relationship exists between
customers and agents that can be described as follows: an agent can serve many customers, and each
customer may be served by one agent. Data models use three types of relationships: one-to-many,
many-to-many, and one-to-one. Database designers usually use the shorthand notations 1:M or 1..*,
M:N or *..*, and 1:1 or 1..1, respectively. (Although the M:N notation is a standard label for the
many-to-many relationship, the label M:M may also be used.)

Examples of relationship
One-to-many (1:M or 1..*) relationship. A painter paints many different paintings, but each one of
them is painted by only one painter. Thus, the painter is related to the paintings. Therefore, this
relationship “PAINTER paints PAINTING” is signified as 1:M.
 3

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.
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 employee. In turn, each store manager, who is an
employee, manages only a single store. Therefore, the relationship “EMPLOYEE manages STORE”
is labeled 1:1.
Constraint is a restriction placed on the data. They’re important because they help to ensure data
integrity and are normally expressed in the form of rules. For example:
 An employee’s salary must have values that are between 6,000 and 350,000.

 A student’s GWA must be between 1.00 and 2.00.

Business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle
within an organization. In a sense, business rules are misnamed: they apply to any organization,
large or small—a business, a government unit, a religious group, or a research laboratory. Properly
written business rules are used to define entities, attributes, relationships, and constraints.
The process of identifying and documenting business rules is essential to database design for several
reasons:
 They help to standardize the company’s view of data.

 They can be a communication tool between users and designers.

 They allow the designer to understand the nature, role, and scope of the data.

 They allow the designer to understand business processes.

 They allow the designer to develop appropriate relationship participation rules and
constraints and to create an accurate data model.

Example

Consider a company that sells books online.

Business rules define the discounts which you can apply in each individual case and also, what
the minimum threshold is to offer free shipping to customers, for example.

A business rule will also establish that you can only send a purchase after recording that
particular customer’s payment.

In another case, consider a SaaS (Software as a Service) that works from recurring sales.

The company has been experiencing problems with Defaulter clients.

 4

What do you do if you don’t receive a payment within the agreed upon time limit?

The company that makes SaaS available can decide to cut the software supply based on a
deadline established through a business rule.

That is, it may decide to cancel the subscription of a client who fails to make payment for two
consecutive months, for example.

You should realize that executing these types of processes is fundamental.

It’s not enough to just know how to analyze and define the established rules, if you can’t put
them into practice.

Imagine in the above situations how these processes would occur if all of these guidelines didn’t
exist or depended on only manual tasks.

Have you thought about having to decide case by case what discount to give a retailer or whether
to cancel the accounts of delinquent clients?

Certainly, your business would lose productivity and mistakes could happen.

Figure 20. Evolution of Major Data Model

Hierarchical and Network Models
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 tree. The hierarchical structure contains levels or segments.
A segment is the equivalent of a file system’s record type. Within the hierarchy, a higher layer is
 5

perceived as the parent of the segment directly beneath it, which is called the child. 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.)
In the Hierarchical Model, a hierarchical relation is formed by a collection of relations and forms a tree-
like structure.
The relationship can be defined in the form of a parent-child type.
One of the first and most popular Hierarchical Model is the Information Management System (IMS),
developed by IBM.

Example
The hierarchy shows an Employee can be an Intern, on Contract or Full- Time. Sub-levels show that
Full-Time Employee can be hired as a Writer, Senior Writer or Editor:

Figure 21. Hierarchical Model

Advantages

 The design of the hierarchical model is simple.

 Provides Data Integrity since it is based on a parent/ child relationship
 Data sharing is feasible since the data is stored in a single database.
 Even for large volumes of data, this model works perfectly.

Disadvantages

 Implementation is complex.
 This model has to deal with anomalies like Insert, Update, and Delete.
 6

 Maintenance is difficult since changes done in the database may want you to do changes in the
entire database structure.

The network model was created to represent complex data relationships more effectively than the
hierarchical model, to improve database performance, and to impose a database standard. In the network
model, the user perceives the network database as a collection of records in 1:M relationships. However,
unlike the hierarchical model, the network model allows a record to have more than one parent. Some
concepts defined during this time were:
 The schema, which is the conceptual organization of the entire database as viewed by the
database administrator.

 The subschema, which defines the portion of the database “seen” by the application programs
that produce the desired information from the data contained within the database.

 Data management language (DML), which defines the environment in which data can be
managed and to work with the data in the database.

 Data definition language (DDL), which enables the database administrator to define the schema
components.

The Hierarchical Model creates hierarchical tree with parent/ child relationship, whereas the Network
Model has graph and links.
The relationship can be defined in the form of links and it handles many-to-many relations. This itself
states that a record can have more than one parent.

Figure 21. Network Model

Advantages

 Easy to design the Network Model

 The model can handle one-one, one-to-many, many-to-many relationships.
 7

 It isolates the program from other details.

 Based on standards and conventions.
Disadvantages

 Pointers bring complexity since the records are based on pointers and graphs.
 Changes in the database isn’t easy that makes it hard to achieve structural independence.

The relational model was introduced in 1970 by E. F. Codd (of IBM) in his landmark paper “A
Relational Model of Data for Large Shared Databanks”.The relational model represented a breakthrough
for both users and designers. To use an analogy, the relational model produced an “automatic
transmission” database to replace the “standard transmission” databases that preceded it.
The relational model foundation is a mathematical concept known as a relation. To avoid the complexity
of abstract mathematical theory, you can think of a relation (sometimes called a table) as a matrix
composed of intersecting rows and columns. Each row in a relation is called a tuple. Each column
represents an 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 addition to a host of other functions that make the relational data model
easier to understand and implement.
Arguably the most important advantage of the RDBMS is its ability to hide the complexities of the
relational model from the user. The RDBMS manages all of the physical details, while the user sees the
relational database as a collection of tables in which data are stored.
Relational diagram is a representation of the relational database’s entities, the attributes within those
entities, and the relationships between those entities.

End-User’s perspective SQL-based relational database application

 End-user interface – allows the end-user to interact with the data (by auto-generating SQL
code).

 A collection of tables stored in the database – In a relational database, all data are perceived to
be stored in tables. The tables simply “present” the data to the end-user in a way that is easy to
understand. Each table is independent. Rows in different tables are related by common values in
common attributes.

 SQL engine –executes all queries or data requests. Keep in mind that the SQL engine is part of
the DBMS software.
 8

A relational model groups data into one or more tables. These tables are related to each other using
common records.
The data is represented in the form of rows and columns i.e. tables:

Figure 22. Relational Model

Example
Let us see an example of two relations <Employee> and <Department> linked to each other,
with DepartmentID, which is Foreign Key of <Employee> table and Primary key
of <Department> table.

Figure 22. Relational Model example

Advantages

 The Relational Model does not have any issues that we saw in the previous two models i.e.
update, insert and delete anomalies have nothing to do in this model.
 Changes in the database do not require you to affect the complete database.
 Implementation of a Relational Model is easy.
 To maintain a Relational Model is not a tiresome task.
Disadvantages
 9

 Database inefficiencies hide and arise when the model has large volumes of data.
 The overheads of using a relational data model come with the cost of using powerful hardware
and devices.

NoSQL
Around 2008, triggered by Facebook’s open-source versions of Hive and Cassandra, the NoSQL
counter-revolution started. This space gets all of the attention today.
2008 was indeed a turning point. This can also be seen in the report of the very influential summit of
database researchers, which have met in 1989, 1990, 1995, 1996, 1998, 2003, 2008, and 2013. In 2008,
big data was the number one factor for a “sense of change”
So, where do we go now? How to balance the “what” and the “how” in light of NoSQL and all of the
new technologies?
Well, the modern development platforms use schema-free or semi-structured approaches (also under the
umbrella of NoSQL). “Model as you go” is a common theme, while data modelers and data governors
are seen as relics from the past. Surveys (e.g. Insights into Modeling NoSQL, A Dataversity Report
2015, by Dr. Vladimir Bacvanski and Charles Roe) confirm this. Modeling for NoSQL is very often
performed by the developer on the fly.

Entity-Relationship Model
A graphical tool in which entities and their relationships are pictured. First introduced in 1976.ER
models are normally presented in an entity-relationship diagram (ERD), which uses graphical
representations to model database components.

ER Model Components
Entity – anything about which data are to be collected and stored. Represented in the ERD by a
rectangle,also known as an entity box. The name of the entity, a noun, is written in the center of the
rectangle and is generally written in capital letters and is written in the singular form: PAINTER rather
than PAINTERS, and EMPLOYEE rather than EMPLOYEES. Each row in the relational table is known
as an entity instance or entity occurrence in the ER model. A collection of like entities is known as an
entity set.
Relationships. describe associations among data. Most relationships describe associations between two
entities. When the basic data model components were introduced, three types of relationships among
data were illustrated: one-to-many(1:M), many-to-many(M:N), and one-to-one(1:1).
 10

ER Notations
Chen notation – based on Peter Chen’s landmark paper. In this notation, the connections are written
next to each entity box. Relationships are represented by a diamond connected to the related entities
through a relationship line. The relationship name is written inside the diamond.
Crow’s foot notation – derived from the three-pronged symbol used to represent the “many” side of the
relationship. The connections are represented by symbols. For example, the“1”is represented by a short
line segment, and the “M” is represented by the three-pronged “crow’s foot.”
The crow's foot notation was invented by Gordon Everest, who originally used the term "inverted arrow"
but now just calls it a "fork". For cardinality, a fork or crow's foot intuitively indicates "many", by its
many "toes".

Figure 23. Chen and Crow’s Foot diagram

Crow’s foot symbol for zero or none is O. So is zero-to-many and is zero-to-one .

Components used in the creation of an ERD

 11

Entity – A person, place or thing about which we want to collect and store multiple instances of data. It
has a name, which is a noun, and attributes that describe the data we are interested in storing. It also has
an identifier, which uniquely identifies one instance of an entity. The attribute which acts as the
identifier is marked with an asterisk.

This is an entity named DESK. We want to store information about several desks. The
information we want to collect and store are the desk id, colour of the desk, and desk size. These are the
attributes of the entity. Desk_id is the identifier. Given the id of a desk, we would be able to seek out
one specific desk, as no two would have the same id number.

Relationship – Illustrates an association between two entities. It has a name which is a verb. It also has
cardinality and modality.

This is a relationship named “is assigned”. It is shown by a line which normally connects two
entities together. No cardinality or modality has been assigned yet.

Cardinality and Modality are the indicators of the business rules around a relationship. Cardinality
refers to the maximum number of times an instance in one entity can be associated with instances in the
related entity. Modality refers to the minimum number of times an instance in one entity can be
associated with an instance in the related entity.
Cardinality can be 1 or Many and the symbol is placed on the outside ends of the relationship line,
closest to the entity, Modality can be 1 or 0 and the symbol is placed on the inside, next to the
cardinality symbol. For a cardinality of 1 a straight line is drawn. For a cardinality of Many a foot with
three toes is drawn. For a modality of 1 a straight line is drawn. For a modality of 0 a circle is drawn.

cardinality is many, modality is zero

cardinality is many, modality is 1

 12

cardinality is 1, modality is 1

cardinality is 1, modality is zero

Cardinality and modality are indicated at both ends of the relationship line. Once this has
been done, the relationships are read as being 1 to 1 (1:1), 1 to many (1:M), or many to many
(M:M).

A student fills a seat. This is a 1:1 relationship. 1 student can fill a maximum of 1 seat. 1 seat can be
filled by a maximum of 1 student. Each side of the relationship has a cardinality of 1. The modality on
each side is also 1. A student must fill at least 1 seat, and 1 seat must be filled by at least one student.
Although it may seem possible to have an empty seat, in which case the modality would be 0, the
business rules we have defined determine that for the purposes of our database – we do not want to be
able to store information about empty seats.

An instructor teaches a course. This is a 1:M relationship. One instructor can teach many courses, but
one course can only be taught by one instructor. The modality is one on both ends of the relationship.
According to the business rules we have defined – an instructor must teach at least 1 course, and a
course must be taught by 1 instructor.
 13

A student takes a course. This is a M:M relationship. One student can take many courses, and one course
can be taken by many students. For the information we want to store, the business rules indicate that a
student must take at least one course, and a course must be taken by at least one student. The modality is
1 on both sides.

A professor teaches a section of a course. This is a 1:M relationship. One professor can teach many
sections of a course, and 1 section of a course can only be taught by 1 instructor. On the modality side of
things – a professor does not need to teach a section at all, so the modality is zero on that side of the
relationshp. On the other hand, a section must be taught by 1 instructor.
Typically, ERDs are much more complex than this, involving quite a number of entities and
relationships. If we join all of the above relationships together and add a few attributes, a small
collection of data might be depicted in the following way using Crow’s Foot Notation:

Figure 23. Crow’s Foot diagram of a Student, Course and Instructor relationship
Consider the following business rules for a patient appointment system:

A doctor can be scheduled for many appointments, but may not have any schedule at all. Each
appointment is scheduled with exactly 1 doctor. A patient can schedule 1 or more appointments. One
appointment is scheduled with exactly 1 patient. An appointment must generate exactly 1 bill, a bill is
 14

generated by only 1 appointment. One payment is applied to exactly 1 bill, and 1 bill can be paid off
over time by several payments. A bill can be outstanding, having nothing yet paid on it at all. One
patient can make many payments, but a single payment is made by only 1 patient. Some patients are
insured by an insurance company. If they are insured, they can only carry insurance with one company.
An insurance company can have many patients carry their policies. For patients that carry insurance, the
insurance company will make payments, each single payment is made by exactly 1 insurance company.

Given the above information, the following ERD can be drawn:

Figure 24. Crow’s Foot diagram of a Patient, Doctor and Bill relationship

Intersection Entities

Intersection entities are used in the resolution of a many to many relationship. This resolution is done in
order to store additional information which doesn’t fit into the attribute list of either entity in the M:M
relationship. For instance, in the instructor-course example given earlier, there is a M:M relationship
 15

between Course and Section. A course can have many sections, and a section can have many courses. If
we want to know how many students are registered in a particular class, we cannot store the information
on either entity. We could store the total number of students registered in all sections of a particular
course, in the course entity, ie: 300 students are registered in CS270, all sections included. We could
store the total number of students registered in all courses with a particular section number, in the
section entity, ie: 25,000 students are registered in a section numbered 002. If we want more practical
information, such as how many students are registered in section 002 of CS270, we need another entity
in which to store the information. So, we create an intersection entity, and place it between its related
entities with the additional attributes. The M:M relationship becomes two 1:M relationships. See
resolution below.

becomes the diagram below, in order to accommodate the needed information, CLASS table is used as
INTERSECTION entity :
 16

Figure 25. Crow’s Foot diagram of a Student, Course, Class and Instructor relationship
This can also be related to the concept of database normalization which we will tackle on the latter units.

Independent Entities

Independent entities are those which can exist without another entity. Student, seat, instructor etc. are
examples of independent entities.

Dependent Entities

Dependent entities rely upon the existence of other entities. Some of their attributes are taken from the
identifiers in the entities upon which they depend. Class is an example of a dependent entity. It relies
upon course and section and gets two of its attributes from their identifiers.

To convert the previous Crow’s foot diagram to Chen notation, we just have to change the lines
representing the cardinality and modality. Example :
 17

Figure 26. Chen Notation diagram of a Student, Course and Instructor relationship
Notice that for 1:1 relationship we only denoted this as 1, there is no need for it to be repeated.
 18

Figure 27. Chen Notation diagram of a Patient, Doctor and Bill relationship

Figure 28. Chen Notation diagram of a Student, Course, Class and Instructor relationship
 19

 Formative Assessment Task

1. After the lecture, students will write down one or more real-world applications of business
rule, chen and crow’s foot diagram.

*** End of Lesson Input ***

 Summative Assessment Task

1. The instructor will provide a written examination with the topics discussed in Unit Four to
Six
2. The instructor will give sample transactions to students that they will convert into business
rules, data models, and entity-relationship diagrams.
 20

References

Garcia-Molina, H.,Ullman, J.,Widom, J. (2008). Database Systems: The Complete Book (2nd ed., pp 15-
55). Pearson

ER diagram tutorial in DBMS (with example). (n.d.). Meet Guru99 - Free Training Tutorials & Video
for IT Courses. https://www.guru99.com/er-diagram-tutorial-dbms.html