Chapter 3 Database Modeling Concepts

3.1 Data Modeling Basics

A DBMS is used to store information regarding an organization/institute etc. In order

to represent this information some kind of mapping or modeling is required so that
the things which are happing in real world can be simulated and stored in a
computer database.

A model is an abstraction process that hides extra details and represents only the
essential one. Data modeling is used for representing entities of interest and their
relationships in the database.

Over the period of time, number of data models have been developed, but as with
programming languages, there is no one perfect choice.

The purpose of all the models is the model or map the real world situation
in terms of entities and relationships which can be stored in the database.

Some popular data models are :

➢ Hierarchical Model
➢ Network Model
➢ Relational Model
• The hierarchical model: The data is sorted hierarchically, using a downward tree.
This model uses pointers to navigate between stored data. It was the first DBMS model.

The hierarchical model was developed by IBM in 1968.

The data is organize in a tree structure where the nodes represent the records and
the branches of the tree represent the fields.
Since the data is organized in a tree structure, the parent node has the links to its
child nodes.
If we want to search a record, we have to traverse the tree from the root through all
its parent nodes to reach the specific record. Thus, searching for a record is very
time consuming.
The hashing function is used to locate the root.
SYSTEM2000 is an example of hierarchical database.

• The network model: like the hierarchical model, this model uses pointers toward stored
data. However, it does not necessarily use a downward tree structure.

Record relationship in the network model is implemented by using pointers.

Record relationship implementation is very complex since pointers are used. It
supports many-to-many relationships and simplified searching of records since a
record has many access paths.
DBTG Codasyl was the first network database
 • The relational model (RDBMS, Relational database management system): The data
is stored in two-dimensional tables (rows and columns). The data is manipulated based on the
relational theory of mathematics.

The Relational Model, organizes data in the form of independent tables (consisting of
rows and columns) that are related to each other.
A table consists of a number of rows (records/tuples) and columns (attributes). Each
record contains values for the attributes.
The degree of the table denotes the number of columns.
A domain in the relational model is said to be atomic is it consists of indivisible units.
For example, name is not atomic since it can be divided into first name and last
name.
E. F. Codd laid down 12 rules (known as Codd's 12 rules) that outline the minimum
functionality of a RDBMS. A RDBMS must comply with at least 6 of the rules.

Before getting into details about any particular let us learn some terms which are common
to all models.

The analysis of data objects and their relationships to other data objects is known as Data
modeling. Data modeling is often the first step in database design and object-oriented
programming as the designers first create a conceptual model of how data items relate to
each other. Data modeling involves a progression from conceptual model to logical model to
physical schema.

Database Model : A real world situation can be modeled in a database and A

database can be modeled as:

• A collection of entities
• Set of relationships among such entities.
Entity: Entities are distinguished objects in the system. It is a "thing" or "object"
or a “concept” in a real world that is distinguishable from all other objects. Anything
about which we store information is called an entity. An entity is an object that
exists and is distinguishable from other objects.

For example: A Student, An employee, a bank account, a course, a product etc. all
are entities.

Attributes: Are the specific characteristics of an entity. Entities have attributes.

They are descriptive properties possessed by an entity. For eg. Name , age , city are
attributes of student. Similarly AccountNumer, Balance, Type are the attributes or
properties of a bank account.

Entity set: is a set of entities of the same type that share the same properties.

Example: set of all persons, companies, trees, holidays

Relationship : It defines a association among a given set of entity types.

For example a Student entity “registers” in a course entity. Here

register is an association or relation between student entity and course
entity.

Relationship Set : A collection of similar types of relationships.

For example: list of all registrations in different courses.

A relationship set is a set of relationships of the same type.

Formally it is a mathematical relation on (possibly non-distinct) sets.

If are entity sets, then a relationship set R is a subset of

where is a relationship.

For example, consider the two entity sets customer and account. We define the
relationship CustAcct to denote the association between customers and their accounts.
This is a binary relationship set.

Going back to our formal definition, the relationship set CustAcct is a subset of all the
possible customer and account pairings.

This is a binary relationship. Occasionally there are relationships involving more than
two entity sets.

The role of an entity is the function it plays in a relationship. For example, the
relationship works-for could be ordered pairs of employee entities. The first employee
takes the role of manager, and the second one will take the role of worker.

A relationship may also have descriptive attributes. For example, date (last date of
account access) could be an attribute of the CustAcct relationship set.

Relationship Types:

There are three types of relationships ;

• One-to -One
• One-to-many(or many -to-One)
• Many-to-many
 Consider the example of a university .For a particular DEPARTMENT (like the
department of social sciences) there can be only one DEPARTMENT HEAD .This is an
example of a one-to -one relationship.

A STUDENT can MAJOR in only one course, but many STUDENTS would have
registered for a given MAJOR course . This is an example of many-to-one relationship.

A STUDENT can take many COURSE and many STUDENTS can register for a given
COURSE. This is an example of many- to-many relationship.

The types of relationships between two entities are represented in E/R diagrams by
certain symbols. An entity may be associated with one, none, or many occurrences of
another entity.

3.2 Types of Database Models

Database Systems can be catagorised according to the data structures and operators
they present to the user. The oldest systems fall into hierarchical and network
systems. These are the pre-relational models.

3.2.1 Hierarchical Model

• In the Hierarchical Model, different records are inter-related through hierarchical

or tree-like structures. A parent record can have several children, but a child can
have only one parent. In the figure, there are two hierarchies shown - the first
storing the relations between CUSTOMER, ORDERS, CONTACTS and ORDER_PARTS
and the second showing the relation between PARTS, ORDER_PARTS and
SALES_HISTORY. The many-to-many relationship is implemented through the
ORDER_PARTS segment which occurs in both the hierarchies. In practice, only one
tree stores the ORDER_PARTS segment, while the other has a logical pointer to this
segment. IMS (Information Management System) of IBM is an example of a
Hierarchical DBMS.
The hierarchical data model organizes data in a tree structure. There is a hierarchy
of parent and child data segments. This structure implies that a record can have
repeating information, generally in the child data segments. Data in a series of
records, which have a set of field values attached to it. It collects all the instances of
a specific record together as a record type. These record types are the equivalent of
tables in the relational model, and with the individual records being the equivalent of
rows. To create links between these record types, the hierarchical model uses Parent
Child Relationships. These are a 1:N mapping between record types. This is done by
using trees, like set theory used in the relational model, "borrowed" from maths.
 For example, an organization might store information about an employee, such as
name, employee number, department, salary. The organization might also store
information about an employee's children, such as name and date of birth. The
employee and children data forms a hierarchy, where the employee data represents
the parent segment and the children data represents the child segment. If an
employee has three children, then there would be three child segments associated
with one employee segment. In a hierarchical database the parent-child relationship
is one to many. This restricts a child segment to having only one parent segment.
Hierarchical DBMSs were popular from the late 1960s, with the introduction of IBM's
Information Management System (IMS) DBMS, through the 1970s.

• A hierarchical schema consists of record types and PCR types:

- A record is a collection of field values.

- Records of the same type are grouped into record types.

- A PCR type (parent-child relationship type) is a 1:N relationship between

two record types.

• A hierarchical database schema consists of a number of hierarchical schemas.

3.2.2 Network Model

• In the Network Model, a parent can have several children and a child can also have
many parent records. Records are physically linked through linked-lists. IDMS from
Computer Associates International Inc. is an example of a Network DBMS.

Where the hierarchical model structures data as a tree of records, with each record
having one parent record and many children, the network model allows each record to
have multiple parent and child records, forming a lattice structure.

The chief argument in favour of the network model, in comparison to the hierarchic
model, was that it allowed a more natural modeling of relationships between entities.
Although the model was widely implemented and used, it failed to become dominant for
two main reasons. Firstly, IBM chose to stick to the hierarchical model with semi-
network extensions in their established products such as IMS and DL/I. Secondly, it was
eventually displaced by the relational model, which offered a higher-level, more
declarative interface.

The popularity of the network data model coincided with the popularity of the
hierarchical data model. Some data were more naturally modeled with more than
one parent per child. So, the network model permitted the modeling of many-to-
 many relationships in data. In 1971, the Conference on Data Systems Languages
(CODASYL) formally defined the network model. The basic data modeling construct
in the network model is the set construct. A set consists of an owner record type, a
set name, and a member record type. A member record type can have that role in
more than one set, hence the multiparent concept is supported. An owner record
type can also be a member or owner in another set. The data model is a simple
network, and link and intersection record types (called junction records by IDMS)
may exist, as well as sets between them . Thus, the complete network of
relationships is represented by several pairwise sets; in each set some (one) record
type is owner (at the tail of the network arrow) and one or more record types are
members (at the head of the relationship arrow). Usually, a set defines a 1:M
relationship, although 1:1 is permitted. The CODASYL network model is based on
mathematical set theory.

Some Well-known Network Databases

• TurboIMAGE
• IDMS
• RDM Embedded
• RDM Server

3.2.3 Relational Model

• In the Relational Model, unlike the Hierarchical and Network models, there are no
physical links. All data is maintained in the form of tables (relations) consisting of
rows and columns. Data in two tables is related through common columns and not
physical links or pointers. Operators are provided for operating on rows in tables.
Unlike the other two type of DBMS, there is no need to traverse pointers in the
Relational DBMS. This makes querying much more easier in a Relational DBMS than
in the the Hierarchical or Network DBMS. This, in fact, is a major reason for the
relational model to become more programmer friendly and much more dominant and
popular in both industrial and academic scenarios. Oracle, Sybase, DB2, Ingres,
Informix, MS-SQL Server are few of the popular Relational DBMSs.

“In Relational model both entities and relationships are represented with
the help of tables, only tables and nothing but tables.”
For example following are few RDBMS sample Tables/relations :

CUSTOMER Table

CUST. NO. CUSTOMER NAME ADDRESS CITY

15371 Nanubhai & Sons L. J. Road Mumbai
... ... ... ...
... ... ... ...
... ... ... ...

CONTACTS Table ORDERS Table

ORDER ORDER CUSTOMER
[Link]. CONTACT DESIGNATION
NO. DATE NO.
15371 Nanubhai Owner 3216 24-June-1997 15371
15371 Rajesh Munim Accountant ... ... ...
... ... ... ... ... ...
... ... ... ... ... ...

PARTS Table
PARTS NO. PARTS DESC PART PRICE
S3 Amkette 3.5" Floppies 400.00
... ... ...
... ... ...
... ... ...

ORDERS-PARTS Table
ORDER NO. PART NO. QUANTITY
 3216 C1 300
3216 S3 120
... ... ...
... ... ...

SALES-HISTORY Table

PART NO. REGION YEAR UNITS

S3 East 1996 2000
S3 North 1996 5500
S3 South 1996 12000
S3 West 1996 20000

(RDBMS - relational database management system) A database based on the relational

model developed by E.F. Codd. A relational database allows the definition of data structures,
storage and retrieval operations and integrity constraints. In such a database the data and
relations between them are organised in tables. A table is a collection of records and each
record in a table contains the same fields.

Properties of Relational Tables:

• Values Are Atomic

• Each Row is Unique
• Column Values Are of the Same Kind
• The Sequence of Columns is Insignificant
• The Sequence of Rows is Insignificant
• Each Column Has a Unique Name

Certain fields may be designated as keys, which means that searches for specific values of
that field will use indexing to speed them up. Where fields in two different tables take
values from the same set, a join operation can be performed to select related records in the
two tables by matching values in those fields. Often, but not always, the fields will have the
same name in both tables. For example, an "orders" table might contain (customer-ID,
product-code) pairs and a "products" table might contain (product-code, price) pairs so to
calculate a given customer's bill you would sum the prices of all products ordered by that
customer by joining on the product-code fields of the two tables. This can be extended to
joining multiple tables on multiple fields. Because these relationships are only specified at
retreival time, relational databases are classed as dynamic database management system.
The RELATIONAL database model is based on the Relational Algebra.

3.2.4 Comparison of 3 models

Firstly, a hierarchical data model is designed in a tree (or parent-child) structure and
allows only one-to-one or one-to-many relationships between entities. The model is
fast when it comes to getting information, but it is not a flexible structure.
Sometimes the role of the entity (parent or child) is not clear and unsuitable for a
hierarchical model.
Secondly, a network data model has a more flexible structure than the hierarchical
model and allows many-to-many relationships between the entities, but it easily
becomes complex and difficult to manage.
Thirdly, the relational data model is more flexible than the hierarchical model and
easier to manage than a network model. The relational data model is the most
widely used model today.

Hierarchical databases link records like an organization chart. A record type can be
owned by only one owner. In the following example, orders are owned by only one
customer. Hierarchical structures were widely used with early mainframe systems;
however, they are often restrictive in linking real-world structures.

In network databases, a record type can have multiple owners. In the example
 below, orders are owned by both customers and products, reflecting their natural
relationship in business.

Relational databases do not link records together physically, but the design of the
records must provide a common field, such as account number, to allow for
matching. Often, the fields used for matching are indexed in order to speed up the
process.

In the following example, customers, orders and products are linked by comparing
data fields and/or indexes when information from more than one record type is
needed. This method is more flexible for ad hoc inquiries. Many hierarchical and
network DBMSs also provide this capability.

Hierarchical Model
This model is referred to as a upside down Tree structure.
The elements of this model are known as nodes.
The uppermost node is called Root node. The later nodes are either Parent node or Child
node depending upon the situation. Parent- child nodes are inter-changeable.
The main restriction of this model is - Each child can have only One Parent. One parent can
have more than 1 child.
Network Model
In this model the restriction of child having 1 parent does not exist. Every child can have
more than 1 parent and every parent can have more than 1 child.
This model is very flexible. Network model usually consists of records and links.
In the network model any item can be related to any item.
In the network model, mapping between parents and children is similar to hierarchical
model, but mapping between parent-to-children is very complex.

Relational Model
Relational model represents a model that combines the simplicity of the hierarchical model
with the flexibility of the network model.
Relational model is constructed using the entities. An entity is defined as an item about
which information is stored in the data base. An entity can be Tangible or non-tangible. An
example of Tangible entity would be Employee. An example of Non-tangible entity would be
Customer account.

Entities are defined using attributes. An attribute is the property of the entity for which
information is stored.

These attributes are also known as columns. The group of column is known as row or a
tuple. Also, a row can be defined as an instant of an entity.
The entities are linked to each other using relationships. The relationship between entities
can be of different types. The main types of the relationship are:

One-To-One
--------------
In this type of relationship one entity is connected or linked to another entity. Eg.,
Manager<------------->Department
Every manager can manage only 1 department and every department can have only 1
manager.

One-To-Many
---------------
The relationship between 2 entities is of more than 1. Eg.
Employee<--------------->Department
Every employee can be in 1 department only but 1 department can have more than 1
employee.

Many-To-Many
----------------
At a given time each entity can be linked to another in many form. Eg.,
Employee<-------------->Project
An employee can be assigned to many projects and 1 project can have many employees.

In relational model the entities and their relationships are represented by 2 dimensional
array or table.

Every table represents an entity.

Every table consists of Rows and Columns.
Relationship between entities are represented by columns.
Each column represents an attribute of the entity.
The values in the columns are drawn from a domain or set of all possible values.
The columns of the entity that are used to link the entities are known as Keys.
There are 2 types of keys. Primary and Foreign.
Primary key is defined as the Entity identifier. It uniquely identifies the entity. Example: The
Social-Insurance-Number. The SIN identifies every person uniquely.
Foreign Key is defined as a Primary key of 1 entity that exists as an Attribute in another
entity.

Advantages of Relational Model.

1. Ease of Use.
2. Flexibility.
3. Data Independence.
4. Security.
5. Ease of implementation.
6. Data merging.
7. Data integrity.

Disadvantages.

1. Redundancy.
2. Performance.
3.2.5 Other Models (OODBMS):
The recent developments in the area have shown up in the form of certain object and
object/relational DBMS products. Examples of such systems are GemStone and Versant
ODBMS. Research has also proceeded on to a variety of other schemes including the multi-
dimensional approach and the logic-based approach.

Few Features of OODBMS are:

• When you integrate database capabilities with object programming language

capabilities, the result is an object-oriented database management system or
ODBMS.
• An ODBMS makes database objects appear as programming language objects
in one or more existing programming languages.
• An object-oriented database system must satisfy two criteria: it should be a
DBMS, and it should be an object-oriented system, i.e., to the extent
possible, it should be consistent with the current crop of object-oriented
programming languages. The first criterion translates into five features:
persistence, secondary storage management, concurrency, recovery and an
ad hoc query facility. The second one translates into eight features: complex
objects, object identity, encapsulation, types or classes, inheritance,
overriding combined with late binding, extensibility and computational
completeness.
• An object-oriented database management system (OODBMS), sometimes
shortened to ODBMS for object database management system), is a database
management system (DBMS) that supports the modelling and creation of data
as objects.
• This includes some kind of support for classes of objects and the inheritance
of class properties and methods by subclasses and their objects. There is
currently no widely agreed-upon standard for what constitutes an OODBMS,
and OODBMS products are considered to be still in their infancy.

Few Examples of OODBMS

▪ Gemstone
▪ IRIS
▪ ORION
▪ ONTOS
 Few Applications of OODBMS:

• Object databases based on persistent programming are used in application

areas such as engineering and spatial databases, telecommunications, and
scientific areas such as high energy physics and molecular biology.
• They have made little impact on mainstream commercial data processing,
though there is some usage in specialized areas of financial services.
• Another group of object databases focuses on embedded use in devices,
packaged software and real time systems. The reason to adapt is to create
new objects according to users choice

Exercise Questions - Chapter 3

Q1. What is data modeling ? why it is required?
Q2. List some popular data models.
Q3. What are the main components of database model?
Q4. Compare and contrast Hierarchical, Network and relational model with example.
Q5. What are object oriented DBMS (ODBMS) ? List their application areas.
Q6. What are entities, attributes, entity sets and relationship sets?
Q7. What is a relationship? What are various types of relationships?
Q8. What is hierarchical model? Explain.
Q9. Explain the features of Network model.
Q10. Describe the importance of relational model.