DBMS

DATABASE MANAGEMENT SYSTEM

(Concept, Components of DBMS,
Characteristics of well designed DBMS, File
System(Introduction & Problems), Types of
DBMS, Hierarchical Models, Network Models,
Relational Model, Introduction to ERD)

What is a Database?
A database is any organized collection of data.

Some

examples of databases you

encounter in your daily life are:
a telephone book
T.V. Guide
airline reservation system
motor vehicle registration records
papers in your filing cabinet
files on your computer hard drive.

may

Database Management
Database management is the process of

storing data efficiently on the computer,

keeping it updated, manipulating and
processing it and making the information
readily accessible (Skeete, 2004).
A database management system is a
collection of programs that enable you to
store, modify, and retrieve information from
a database.

Data
Data is nothing but a random, unorganized
collection of indications or measurements of
certain qualities or attributes relating to an
entity,
recorded
either
in
alphabetical,
numerical, alphanumerical, voice, image, text
or any other form.

Examples
person names
telephone numbers
salaries
addresses

Data Processing
Data

Processing

Informatio
n

Information, according to Davis and Olson, is

data that has been processed into a form
that is meaningful to the recipient and is of
real or perceived value in current or
prospective actions or decisions.

Data from an event/transaction has to be

recorded
Data has to be checked/validated for
Verifying
correctness
Data has to be placed in specific
Classifying
categories
Arranging/Sortin Data has to be placed in a particular
g
order/sequence
Data elements have to be
Summarizing
combined/aggregated
Arithmetical/Logical
Calculating
calculations/computations have to be
carried out
Data has to be placed in some specific
Storing
media
Specific data elements have to be
Retrieving
searched for and
accessed
Data has to be duplicated from one
Reproducing

Data Processing
Capturing

Data vs. Information: What is the

difference?

What is information?

What is data?
Data
can
be
defined in many
ways. Information
science
defines
data
as
unprocessed
information.

Information is data that

have been organized

and communicated in a
coherent
and
meaningful manner.
Data is converted into
information,
and
information is converted
into knowledge.
Knowledge; information
evaluated and organized
so that it can be used
purposefully.

Characteristics of Information
It
It
It
It

improves representation of an entity;

updates the level of knowledge;
reduces the uncertainty;
aids in Decision Making; etc.

Why do we need a
Database?
Keep records of our:
Clients
Staff
Volunteers

To keep a record of

activities
and
interventions;
Keep sales records;
Develop reports;
Perform research; etc.

Purpose of Database
Systems
In the early days, database applications were built

directly on top of file systems

Drawbacks of using file systems to store data:
Data redundancy and inconsistency
Multiple file formats, duplication of information in different files
Difficulty in accessing data

Need to write a new program to carry out each new task

Data isolation multiple files and formats

Integrity problems

Integrity constraints (e.g. account balance > 0) become buried

in program code rather than being stated explicitly
Hard to add new constraints or change existing ones

Purpose of Database Systems (Cont.)

Drawbacks of using file systems (cont.)
Atomicity of updates
Failures may leave database in an inconsistent state with
partial updates carried out
Example: Transfer of funds from one account to another
should either complete or not happen at all

Concurrent access by multiple users

Concurrent accessed needed for performance
Uncontrolled concurrent accesses can lead to inconsistencies
Example: Two people reading a balance and updating it at the
same time
Security problems

Hard to provide user access to some, but not all, data

Database systems offer solutions to all the above

problems

What is the ultimate purpose of a

Database Management System?

Is to transform
Data

Information

Knowledge

Action

DBMS-Concept
Collection of interrelated data
Set of programs to access the data
DBMS contains information about

a particular enterprise
DBMS provides an environment
that is both convenient and
efficient to use.
Database Applications:
Banking: all transactions
Airlines: reservations, schedules

Sales:

customers,

products,

purchases
Manufacturing:
production,
inventory, orders, supply chain
Human
resources:
employee
records, salaries, tax deductions
Databases touch all aspects of our
lives

DBMS-Definition
DBMS is basically a software which performs the

functions of defining,
controlling the database.

creating,

revising

and

DBMS is a specially designed software to create and

maintain a database and enable individual business

applications to extract the data they need without
having to create separate files in their computer
programs.

DBMS-illustration

Components of DBMS
Transaction Management
Ensuring Atomicity
Concurrency Control
Recovery Management
Security Management
Language Interface
Storage Management
Database Catalog Management

Transaction Management
A transaction is a sequence of database operations that

represents a logical unit of work and that accesses a

database and transforms it from one state to another.
A transaction can update, Delete or modify a single or a
set of records.
When the DBMS does a commit ,the changes made by
the transaction made permanent. If the changes are not to
be made permanent , the transaction can be rolled back
and the database will remain in its original state.

Ensuring Atomicity
DBMS ensures atomicity (all-or-nothing property)

even if system crashes in the middle of a

transaction.
Keep a log (history) of all actions carried out by
the DBMS while executing a set of transactions:
Before

a change is made to the database, the

corresponding log entry is forced to a safe location.
After a crash, the effects of partially executed
transactions are undone using the log. (If log entry
wasnt saved before the crash, corresponding
change was not applied to database!)

All log related activities are handled transparently

by the DBMS.

Concurrency Control
Concurrency Control is the database management activity

of coordinating the actions of database manipulation

process that operates concurrently that access shared data
and can potentially interface with one another.
The goal of an ideal concurrency management
mechanism is to allow concurrency while maintaining
the consistency of the share data.

Concurrency Control
Concurrent execution of user programs is

essential for good DBMS performance.

Because

disk accesses are frequent, and relatively

slow, it is important to keep the CPU humming by
working on several user programs concurrently.

Interleaving actions of different user programs

can lead to inconsistency: e.g., check is cleared

while account balance is being computed.
DBMS ensures such problems dont arise: users
can pretend they are using a single-user system.

Scheduling Concurrent Transactions

DBMS ensures that execution of {T1, ... , Tn} is

equivalent to some serial execution T1 ... Tn.

Before

reading/writing an object, a transaction

requests a lock on the object, and waits till the
DBMS gives it the lock. All locks are released at
the end of the transaction. (locking protocol.)
E.g: If an action of Ti (say, writing X) affects Tj
(which perhaps reads X), one of them, say Ti, will
obtain the lock on X first and Tj is forced to wait
until Ti completes; this effectively orders the
transactions.

Recovery Management
Ensures that aborted or failed transaction

do not create an adverse effect on the

database or the other transactions.
It also ensures that the database is returned
to a consistent state after a transaction fails
or abort.
Recovery is related to concurrency---more the
concurrency the more is the chance that an
aborted transaction can affect many other
transactions.

Security Management
Refers to the protection of data against unauthorized

access---only authorized users are given access to the data

in the database.
The level of access for each user and the type of operation
the user can perform on data will depend on the access
privileges of the users.

Language Interface
Provides support languages used for the definition and

manipulation of data in the database.

Storage Management
The DBMS provides a mechanism for management of

permanent storage of data.

The internal schema defines how the data should be
stored by the storage management mechanism and the
storage manager interfaces with the operating system to
access the physical storage.

Data Catalog
Management
Data Catalog or Data Dictionary is a system database

that contains description of data in the database.

It contains information about data, relationships,
constraints and the entire schema that organize these
features into a unified database.
The data catalog can be queried to get information
about the structure of the database.

Characteristics of Well Designed

DBMS
The DBMS helps in reducing the complexity

in the systems environment due to the central

control/management
of
data,
access,
utilization and security.
As same data elements are not repeated in all
the files, DBMS helps reduce/eliminate data
redundancy and inconsistency and promotes
data
integrity
throughout
the
system/organization.
DBMS helps increase access and availability
of/ to information.

The DBMS provides for central control of data

creation and definition, thereby reducing/

eliminating data confusion.
DBMS helps in bringing about substantial
reduction in the costs related with program
development and maintenance.
DBMS helps separate logical view and
physical arrangement, thereby, reducing
program-data dependence.
DBMS, particularly the RDBMS, permits adhoc queries, thereby ensuring flexibility of
information system.

Structure of a DBMS
A typical DBMS has a

layered architecture.
This is one of several
possible architectures;
each system has its
own variations.

These layers
must consider
concurrency
control and
recovery

Query Optimization
and Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management

DB

Database Languages
Data Definition Language (DDL)
define relation, schemes and constraints.

Data Manipulation Language (DML)

insert, update, delete, retrieve.
Procedural - tell the system what and how to get it
non-procedural (declarative) tell the system what, not how

Data Control Language (DCL)

security,
accessibility
Example - The SQL or Structured Query Language, is, presently, the most

prominent and popular database language used.

Sophisticated languages for managing database systems are called fourthgeneration languages, or 4GLs for short.

DBMS Approach

File System approach

Database

DBMS approach

Data Definition Language (DDL)

Specification notation for defining the database schema
Example: create table account (
account_number char(10),
branch_name
char(10),
balance
integer)
DDL compiler generates a set of tables stored in a data

dictionary
Data dictionary contains metadata (i.e., data about data)
Database schema
Data storage and definition language
Specifies the storage structure and access methods used
Integrity constraints

Domain constraints
Referential integrity (e.g. branch_name must correspond to a valid
branch in the branch table)

Authorization

Data Manipulation Language (DML)

Language for accessing and manipulating the

data organized by the appropriate data model

DML also known as query language

Two classes of languages

Procedural user specifies what data is
required and how to get those data
Declarative (nonprocedural) user specifies
what data is required without specifying how to
get those data
SQL is the most widely used query language

An Example
The Wine Cellar Database:
Cellar:

Bin
Wine
Comments
2
Chardonnay
1
85
3
Chardonnay
6 Chardonnay
Thanksgiving
12
Jo. Riesling
83
Late Harvest
16
Jo. Riesling
83
Very dry
43
Cab. Sauvignon
50
Pinot Noir
85
Harvest
51
Pinot Noir
86

Retrieval:

Producer

Year Bottle Ready

Buena Vista

Louis Martini 81
5
Chappellet
82
Buena Vista
Sattui

SELECT Wine, Bin, Producer

FROM
Cellar
WHERE Ready = 85;

Result:

Bin
2
6
50

84
85

82

82

Robt. Mondavi 77 12
Mirassou
77
3

Ch. St. Jean

78
DML (Data Manipulation
Language)
:

Wine
Chardonnay
Chardonnay
Pinot Noir

83

Producer
Buena Vista
Chappellet
Mirassou

87
2

An Example

(cont.)

Deletion:

DML: DELETE FROM Cellar

WHERE Ready < 86;

Result:

Bin
Wine
Comments
43
51

Cab. Sauvignon
Pinot Noir

Producer

Year Bottle Ready

Robt. Mondavi
77
12
Ch. St. Jean
78
2

87
86

Insertion:

DML: INSERT INTO Cellar

VALUES (53, 'Pinot Noir', 'Franciscan', 79, 1, 86,
'for Joan');
Bin
Wine
Producer
Year Bottle Ready

Result:

Comments
43
51
53

Cab. Sauvignon
Pinot Noir
Pinot Noir
1

Robt. Mondavi
77
12
Ch. St. Jean
78
2
Franciscan
86
for Joan

79

87
86

1-37

An Example

(cont.)

Update

DML:

UPDATE Cellar

SET Bottles = 4
WHERE Bin = 51;

Result:
Bin
Wine
Ready Comments

43
Cab. Sauvignon
12
87
51
Pinot Noir
4
86
53
Pinot Noir
79
1

Producer

Year

Robt. Mondavi

Bottle

77

Ch. St. Jean

86

78

Franciscan
for Joan

1-38

Data Types

File Based System

The typical file-processing system is supported by a

conventional operating system.

Permanent records are stored in various files, and
different application programs are written to extract
records from, and to add records to, the appropriate files.
Before the advent of DBMSs, organizations typically
stored information using such systems.

Advantages of DBMS over FileProcessing System

Data redundancy and inconsistency
Difficulty in accessing data
Data isolation
Integrity problems
Atomicity Problems (transaction failure-

rolled back)
Concurrent-Access Anomalies
Security Problems

Disadvantages of Using a
DBMS
Confidentiality, Privacy and Security

(centralized information)
Data Quality (multi users-damage of data)
Data Integrity (concurrency-accuracy)
Enterprise Vulnerability (centralizedunauthorized access of data)
The Cost of Using a DBMS (installation
cost)
Experts (specialised personnel)

Levels of Abstraction
Physical level: describes how a record (e.g.,

customer) is stored.
Logical level: describes data stored in database,
and the relationships among the data.
type customer = record
customer_id : string;
customer_name : string;
customer_street : string;
customer_city : string;
end;

View level: application programs hide details of

data types. Views can also hide information (such

as an employees salary) for security purposes.