INFORMATION MANAGEMENT

CC 105
At the end of module, you should be able to:
1. Explain and discuss the concepts of Database Management
System.
2. Design a logical database model by applying rules of
normalization on existing relations.
3. Design a logical database model by applying rules of
normalization on existing relations.
4. Develop a relational database system and write documentation
by discussing the research issues and approach used in
developing the project.
5. Analyze more advanced issues in relational database design and
management.
6. Investigate emerging technologies and recent trends in database
design.

Prepared by:

ROMEO N. LABAYNA
Subject Teacher

|FIRST SEMESTER | AY 2021-22

 FIRST SEMESTER | AY 2021-2022

Unit II: Database Analysis Design and

Techniques, and Logical Modelling
Information Management

Topic Learning Outcomes

At the end of this topic, you are expected to:

1. Familiarize themselves on data modelling and planning by

understanding the concepts of data design.
2. Analyze given scenario and was able to illustrate ER diagram
with relations to depict given scenario.
3. Analyze given data or documents to identify functional
dependencies of data from one another and be able to group
each data based on the identified needs of the possible
MODULE

system. The student must be able to apply normalization of

tables with identified keys.

Lesson Outline

1. Data Models
2. Database Planning and Administration
3. Entity Relationship Modelling
4. Relationship Modelling
5. Transforming ERD to Relations
6. Normalization and Functional dependency

IM CC 105 | Page 2 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

EXPLAIN Unit II: Database Analysis Design and

Techniques, and Logical Modelling
Information Management

Lesson 1: Data Models

What is a Data Model?

Database effectiveness and

efficiency is directly
associated with the database
structured. It is essential and
to establish a good and
proper design of a database
that shows the needs of the
MODULE

user. Data model is the

visual plan for building a database. It is an integrated collection of concepts
that can be used to describe the structure of a database and to manipulating
data relationships between data and constraints on the data in an
organization, and to represent data and to make data understandable.
Data Model is a graphical system used to capture the nature and
relationship among data.

Data Model Components

1. Structural Part – consisting of set of rules according to which the

database can be constructed. A collection of data structures which are
used to create databases representing the entities or objects modelled
by the database.

IM CC 105 | Page 3 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

2. Manipulative Part – defining the types of operation that are allowed on

the data. This includes the operations that are used for updating or
retrieving data from the database and for changing the structure of the
database. A collection of operators which can be applied to the data
Information Management

structures, to update and query the data contained in the database.

3. Set of integrity rules – ensures that the data is accurate. A collection of

rules governing the constraints placed on these data structures to
ensure structural integrity.

Data Abstraction

Database systems are made-up of complex data structures. To ease

the user interaction with database, the developers hide internal irrelevant
MODULE

details from users. This process of hiding irrelevant details from user is called
data abstraction.

Data Abstraction is a mechanism to hide complexity of database in

database system. It allows database system to provide abstract view of
database user which simplifies users’ interactions with the system. It hides
how the data are actually stored and maintain in a database.

Level of Abstraction
1. Physical Level /
Internal Schema – This is the
lowest level of data
abstraction. It describes how
data is actually stored in

IM CC 105 | Page 4 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

database. This level describes how many bytes of physical space are
needed and restrictions. The internal schema defines the physical
storage structure of the database. The internal schema is a very low-
level representation of the entire database.
Information Management

2. Logical Level/ Conceptual Schema – It describes what data are stored

in database and the relationship exist among data. This level
comprises of the information that is actually stored in the database in
the form of tables. It also stores the relationship among the data entities
in relatively simple structures. At this level, the information available to
the user at the view level is unknown.

3. View Level / External Schema –It is how users see the data. It can also
hide information for security purposes. This is the highest level of
abstraction. Only a part of the actual database is viewed by the users.
MODULE

This level exists to ease the accessibility of the database by an

individual user. Users view data in the form of rows and columns.
Tables and relations are used to store data. Multiple views of the same
database may exist. Users can just view the data and interact with the
database, storage and implementation details are hidden from them.

Data Model Basic Building Blocks

1. Entity – anything about which data are to be collected and stored.

Entities are real time objects that exist. It can be a person, place,
object, event, and concept. Entities are represented by a rectangle box
containing the entity name in it.
Example: Student Student

IM CC 105 | Page 5 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

2. Attribute – It is the set of characteristics representing an entity. It is

represented by an ellipse symbol with attribute name on it.
Example: A student has attributes like name, student number
and age.
Information Management

Name Student Number Age

3. Relationships – It describes the association between two entities. It is

represented using diamond symbol containing relationship name with
it.
Example: The relationship between two entities
Student and Class has many to many Enrolled

relationships. Student is enrolled to a Class.

MODULE

Relational Database - a database that represents data as a collection

of tables in which all data relationships are represented by common values
in related tables. It establishes the relationship between entities by means of
common fields included in a file, called relation.

The data model generally uses three kinds of relationships: one to

many, many to many, one to one.

One to one Relationship (1:1) – a

one-to-one relationship is a type
of cardinality that refers to the
relationship between two entities.
In relational database it is
established when one record in a

IM CC 105 | Page 6 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

table has only one matching record in other table. This type of relationship is
not common because most of information related in this way would be all in
one table.
Information Management

One to Many
Relationship (1:M) –
can establish when
one record from a
table is related to
many matching
records in other tables. A one-to-many relationship exists when one row in
table A may be linked with many rows in table B, but one row in table B is
linked to only one row in table A. It is important to note that a one-to-many
relationship is not a property of the data, but rather of the relationship itself.
MODULE

Many to Many
Relationship (M:M) –
association between
tables in which one
record in either table
can relate to many
records in the other
table. A pair of tables
bears a many-to-many
relationship when a
single record in the first
table can be related to
one or more records in
the second table and a single record in the second table can be related to

IM CC 105 | Page 7 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

one or more records in the first table. Many to many relationships are difficult
to represent. We need to decompose a many to many (M:M) relationship into
two one-to-many (1:M) relationships.
Information Management

4. Constraints - Constraints are conditions applied on the data. It provides

the data integrity. It is a restriction place on data. Constraint are
important because they help to ensure data integrity. Constraint are
normally expressed in the form of rules.
Example: A student can take a maximum of 2 books from the library is
applied as a constraint on the student database.

Types of Data Model

Conceptual model – A conceptual

MODULE

model is a representation of a system,

made of the composition of concepts
which are used to help people know,
understand, or simulate a subject the
model represents. This Data Model
defines “WHAT” the system contains.
This model is typically created by
Business stakeholders and Data Architects. The purpose is to organize,
scope and define business concepts and rules.

Conceptual Model is an organized view of database concepts and their relationships. The purpose of creating a
conceptual data model is to establish entities, their attributes, and relationships.

IM CC 105 | Page 8 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

Logical Model - is used to define the

structure of data elements and to set
relationships between them. The logical
Information Management

data model adds further information to the

conceptual data model elements. The
advantage of using a Logical data model
is to provide a foundation to form the base
for the Physical model.

A Logical model is a fully attributed conceptual model. The attributes are fully spelled out with no abbreviations. High-level data types
(string, number, date, etc.) are provided at this point but no details of the physical implementation are provided in the logical model.

Physical Data Model -

describes a database-
specific implementation of the
MODULE

data model. It offers database

abstraction and helps
generate the schema. This is
because of the richness of
meta-data offered by a
Physical Data Model.

The physical data model also helps in visualizing database structure by replicating database column keys, constraints,
indexes, triggers, and other RDBMS features.

Database Models
A database model shows the logical structure of a database, including
the relationships and constraints that determine how data can be stored and
accessed. Individual database models are designed based on the rules and

IM CC 105 | Page 9 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

concepts of whichever broader data model the designers adopt. Most data
models can be represented by an accompanying database diagram.

There are five (5) common types of database model that are useful for
Information Management

different types of data or information.

1. Hierarchical Model - This database model organizes data into a tree-

like-structure, with a single root, to which all the other data is linked.
The hierarchy starts from
the Root data, and expands
like a tree, adding child
nodes to the parent nodes.
In this model, a child node
will only have a single
parent node. This model efficiently describes many real-world
relationships like index of a book, recipes etc. In hierarchical model,
MODULE

data is organized into tree-like structure with one one-to-many

relationship between two different types of data, for example, one
department can have many courses, many professors and of course
many students.

2. Network Model – This is an extension

of the Hierarchical model. In this model
data is organized more like a graph,
and are allowed to have more than one
parent node. In this database model
data is more related as more
relationships are established in this

IM CC 105 | Page 10 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

database model. Also, as the data is more related, hence accessing

the data is also easier and fast. This database model was used to map
many-to-many data relationships. This was the most widely used
database model, before Relational Model was introduced.
Information Management

3. Entity-relationship Model - In
this database model,
relationships are created by
dividing object of interest into
entity and its characteristics into
attributes. Different entities are
related using relationships. E-R Models are defined to represent the
relationships into pictorial form to make it easier for different
stakeholders to understand. This model is good to design a database,
MODULE

which can then be turned into tables in relational model.

Components of ER Model

 Rectangle – represents entity sets.

 Ellipses – represents an attributes.
 Diamonds – represents relationship among entity.
 Lines – link attributes to entity sets and entity sets to a
relationship.

IM CC 105 | Page 11 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

4. Relational Model is
developed by E. F. Codd in 1970.
The various software systems
used to maintain relational
Information Management

databases are known as a

relational database management
system (RDBMS). In this model,
data is organized in rows and
column structure i.e., two-
dimensional tables and the relationship is maintained by storing a
common field. It consists of three major components. In relational
model, three key terms are heavily used such as relations, attributes,
and domains. A relation nothing but is a table with rows and columns.
The named columns of the relation are called as attributes, and finally
the domain is nothing but the set of values the attributes can take.
MODULE

5. Object Oriented Model an object database is a system in which

information is represented in the form of objects as used in object-
oriented programming. Object oriented databases are different from
relational databases which are table-oriented.

IM CC 105 | Page 12 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

Instances and Schema

The Schema and Instance are the essential terms related to

databases. The major difference between schema and instance lies within
Information Management

their definition where Schema is the formal description of the structure of

database whereas Instance is the set of information currently stored in a
database at a specific time.

A schema is the complete design of database it is also known as

intension. It is the collection of named objects. The names of tables, columns
of each table, data type, triggers, functions views packages and other objects
are included in the schema. The changes in a schema are not applied so
frequently, but occasionally changes need to be applied as the requirements
of application changes. The schema modification or alteration is known as
schema revolution.

The database system has various schemata separated according to

MODULE

the levels of abstraction such as physical, logical and external/subschema.

1. The physical schema is the lowest level of a schema which describes

how the data stored on the disk or the physical storage.
2. The logical schema is the intermediate level of a schema which
describes the structure of the database to the database designers. It
also specifies what relationship exists between the data.
3. The external schema or subschema is the highest level of a schema
which defines the views for the end users.

An instance is the information collected in a database at some specific

moment, and it is also known as state or extension. It is a snapshot where
the current state or occurrence of a database is framed at that moment. Each
time when the data is inserted or deleted from the database changes the

IM CC 105 | Page 13 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

state of the database that is the reason why an

instance of the database changes very often.

Key Differences between Schema and

Information Management

Instance

1. A schema is the design representation of a database whereas instance

is the snapshot of a database at a particular moment.
2. Instance changes very frequently, whenever data is removed or added
in the database. As against, the changes in schema occurs rarely.
3. For example, schema and instance can be easily perceived by analogy
to a program. At the time of writing a program in a programming
language, the variables of that program is declared at first, this is
analogous to the schema definition. Additionally, each variable in a
program must have some values associated at a particular time; this is
similar to an instance.
MODULE

IM CC 105 | Page 14 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

Lesson 2: Database Planning and Administration

Database Development Life Cycle

Information Management
MODULE

Database Development Life Cycle

1. Database Planning
 Planning how the stages of the lifecycle can be realized most
efficient and effectively.
 Identification of enterprise plans and goals with subsequent
determination of information system needs.
 Evaluation of the current IS to determine existing strengths and
weaknesses.
 Database planning also include the development of standards
that govern how data will be collected.
 First step in database planning is to clearly define the mission
statement.

IM CC 105 | Page 15 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

 MISSION statement – defines the major aims of the database

application.
 It helps to clarify the purpose of the database and provide a
Information Management

clearer path towards the creation of the database.

2. System Definition
 Describe the scope and the boundaries of the database
application and major user views.
 Identifying user views is an important aspect of developing a
database application because it helps to ensure that no major
users are forgotten when developing the requirements.

3. Requirements Collection and Analysis

 Process of collecting and analyzing information about the part of
the organization that is to be supported by the database
MODULE

application and using this information to identify the user’s

requirements of the new system.
 The requirements collection and analysis phase, the collection
and analysis of the information about the part of the enterprise to
be served by the database are completed. The results may
include example: the description of the data used or generated,
the details how the data is to be used or generated, and any
additional requirements for the new database system

Multiple user views approaches

 Centralized approach – requirements for each user view are
merged into a single set of requirements.
 View integration – requirements for each user view are used to
build a separate data model to represent that user view. The

IM CC 105 | Page 16 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

resulting data models are merged later in the dbase design

stage.

4. Database Design
Information Management

 Process of creating a design for a database that will support the

enterprise operations and objectives.
 Includes conceptual, logical and physical design of the database.
 Represent data and relationships between data required by all
major application areas and user groups.
 Provide data model that supports any transactions required on
the data.
 Specify a minimal design that is appropriately structured to
achieve stated performance requirements for the system (such
as response times).
MODULE

Phases of Database Design

 Conceptual Database Design – process of constructing a model
of the information used in an enterprise. Involves the creation of
conceptual data model. Creation of data model is based on the
information documented in the user’s requirements specification.
The model is tested and validated against the user’s
requirements.
 Logical Database Design – process of constructing a model of
the information used in an enterprise based on a specific data
model. Creation of the logical data model of the part of the
enterprise. The technique of normalization used to test the
correctness of a logical data model. Normalization ensures that
the relations derived from the data model do not display data
redundancy.

IM CC 105 | Page 17 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

 Physical Database Design – producing a description of the

implementation of the database on secondary storage. Deciding
how the database is to be implemented.
Information Management

5. DBMS Selection
 Selection of an appropriate DBMS to support the database
application.
 If no DBMS exists, part of the lifecycle is the selection of the
DBMS.

6. Application Design
 Design of user interface and application programs that use and
process the database.
 Database and application design are parallel activities.
 Includes two important activities: transaction design (e.g.,
MODULE

Queries); User interface design (e.g., Forms).

7. Prototyping
 Building a working model of the database applications, which
allows the designers or user to visualize and evaluate how the
final system will look and function.
Requirements prototyping – the prototype is being discarded.
Evolutionary prototyping – prototype is not discarded and
develop with further revisions.

8. Implementation
 Physical realization of the database and applications designs.
 Once a database has been developed, tested, and tweaked to
optimize performance, it is then made available for the client to

IM CC 105 | Page 18 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022

use for everyday practical applications. Ideally, things will run

smoothly, but as is the case with all things technical, errors and
maintenance are still needed, which brings us to the next stage
of the database development lifecycle.
Information Management

9. Data Conversion and Loading

 Transferring any existing data into the new database and
converting any existing applications to run on the new database.

10. Testing
 Process of executing the application programs with the intent of
finding errors.

11. Operational Maintenance

 The operational maintenance is the process of monitoring and
MODULE

maintaining the database system.

 Monitoring means that the performance of the system is
observed. If the performance of the system falls below an
acceptable level, tuning or reorganization of the database may
be required. Maintaining and upgrading the database system
means that, when new requirements arise, the new development
lifecycle will be done.

IM CC 105 | Page 19 Unit II | Database Analysis Design and Techniques, and Logical Modelling
 FIRST SEMESTER | AY 2021-2022
Information Management
MODULE

THE END OF UNIT II Part 1

For clarification and further questions, please leave a
comment below and wait for a response, or chat with your subject
teacher asking for this.

IM CC 105 | Page 20 Unit II | Database Analysis Design and Techniques, and Logical Modelling