CUIT201: Database

Systems
DBMS Functions

Lecture 3
Presentation layout
 Lecture Objectives
 Introduction
 DBMS definition and Functions
 Types of DBMS Structures
 Types of Databases
 Advantages and complexities of DBMS
 Types of Database structures
 Conclusion
 References
Lecture Objectives
At the end of the lecture the student should be
able to:
Define DBMS
Explain the functions of DBMS
Describe components of DBMS
Outline the advantages and limitations of

using DBMS
Classify different types of databases.
Definition of DBMS
 A DBMS is a collection of programs that
manages the database structure and controls
access to the data stored in the database.
 “A software system that enables users to

define, create, maintain and control access to

the database” (Connolly/Begg, )
 Provides users with the ability to store,

retrieve, and update the data that are in the

database.
DBMS
DBMS Function-Control
 Changing data structure
◦ Creating new data types
◦ Removing existing data types
 Manipulating data
◦ Inserting, deleting, updating
 Accessing data
◦ Security
◦ Integrity
DBMS Advantages
 Improved
◦ Shared update
◦ Data consistency
◦ Concurrency control
◦ Backup and recovery
◦ Data Security
◦ Data Integrity
◦ Data independence
◦ Standards
◦ scalability
Shared update

 Ensures accuracy when several users

are updating the database at the same
time.
 The DBMS helps create an

environment in which end users have

better access to more and better-
managed data.
Avoiding Data inconsistency

 Data inconsistency exists when different

versions of the same data appear in
different places. The probability of data
inconsistency is greatly reduced in a
properly designed database
◦ Prohibit shared update.
◦ Use batch processing
◦ Lock other users from accessing records that
are being updated by another.
Concurrency Control
 Concurrency control is a database
management systems (DBMS) concept that is
used to address conflicts with the
simultaneous accessing or altering of data
that can occur with a multi-user system.
 Concurrency control, when applied to a

DBMS, is meant to coordinate simultaneous

transactions while preserving data integrity.
 The Concurrency is about to control the

multi-user access of Database

Backup and Recovery
 Provides a mechanism for recovering the
database in the event that the database is
damaged in any way.
 The process of returning the database to a

correct state is called recovery.

 Periodically making a copy of the database is

called backup.
Data Security
 The more the users access the data, the
greater the risks of data security breaches.
This is the reason DBMS provides a framework
for better enforcement of data privacy and
security policies.
 The protection of the database against

unauthorized access.
◦ Passwords
◦ Encryption
◦ Views
Data Integrity
 data integrity” refers to the accuracy and
consistency of data stored in a database, data
warehouse, data mart or other construct.
 Integrity constraints, or conditions, fall into

four categories:
◦ Data type
◦ Legal values
◦ Format
◦ Key constraints
Data Independence
 Facilities that allow programs to be
independent of the structure of the database.
◦ Addition of a field
◦ Changing the length of a field
◦ Creating a new index
◦ Adding or changing a relationship
Better data integration
It is much easier to see how actions in one segment
of the company affect other segments.
Improved data access.

The DBMS makes it possible to produce quick

answers to ad hoc queries. From a database
perspective, a query is a specific request issued to
the DBMS for data manipulation.
Improved decision making

Better-managed data and improved data access

make it possible to generate better-quality
information, on which better decisions are based.
Data quality is a comprehensive approach to
promoting the accuracy, validity, and timeliness of
the data. While the DBMS does not guarantee data
quality, it provides a framework to facilitate data
quality initiatives.
Increased end-user productivity.
The availability of data, combined with the tools that
transform data into usable information, empowers
end users to make quick, informed decisions that
can make the difference between success and failure
in the global economy.
Disadvantage-Complexity
 The provision of the functionality that is
expected of a good DBMS makes the DBMS
an extremely complex piece of software.
Failure to understand the system can lead
to bad design decisions, which can have
serious consequences for an organization.
Disadvantages - Size
The complexity and breadth of functionality
makes the DBMS an extremely large piece of
software, occupying many megabytes of disk
space and requiring substantial amounts of
memory to run efficiently.
Disadvantages- Perfomance
 The DBMS file based system is written to be
more general, to cater for many applications
rather than just one. The effect is that some
applications may not run as fast as they
used to.
 Specialised software is normally
quicker
Disadvantages- Higher impact of a
failure
he centralization of resources increases the
vulnerability of the system. Since all users
and applications rely on the availabi1ity of
the DBMS, the failure of any component can
bring operations to a halt.
Disadvantages- Cost
 The cost of DBMS varies significantly, depending on the environment and
functionality provided. There is also the recurrent annual maintenance
cost.
◦ Software
◦ Hardware
◦ Training
 Additional Hardware costs:To achieve the required performance it may be
necessary to purchase a larger machine, perhaps even a machine
dedicated to running the DBMS. The procurement of additional hardware
results in further expenditure.
 Cost of Conversion: In some situations, the cost of DBMS and extra
hardware may be insignificant compared with the cost of converting
existing applications to run on the new DBMS and hardware. This cost is
one of the main reasons why some organizations feel tied to their current
systems and cannot switch to modern database technology.
Types of DBMS
 Main Commercial Systems
◦ Relational
 Others
◦ Network
◦ Hierarchical
◦ Object-oriented
◦ Object-relational
◦ Semantic
◦ Associative
◦ Free-text
DBMS Transaction
 A transaction can be defined as a group of tasks.
 A single task is the minimum processing unit which
cannot be divided further.
 E.g. Transferring money $200 between two accounts (X to
Y) involves simple transactions such as
 X Account
◦ Open(X)
◦ oldBalance = X.Balance
◦ newBalance = oldBalance – 200
◦ X.balance =newBalance
◦ Close(X)
 Y Account
◦ Open(Y)
◦ oldBalance = Y.balance
◦ newBalance = oldBalance + 200
◦ Y.balance =newBalance
◦ Close(Y)
DBMS ACID Properties of a
Transaction
 Atomic
◦ all of a transaction should be executed or none
 Consistent
◦ the database should be in a “consistent” state
after the transaction is completed
 Isolated
◦ Updates of concurrent transactions should be
concealed from each other, till committed
 Durable
◦ once a transaction commits, its updates
survive even if there is a subsequent system
crash
DBMS Components and Structure
 The DBMS software is consists of several
components.
 Each component has a specific function.
 Some of the functions of the DBMS are supported by
operating systems (OS) to provide basic services and
DBMS is built on top of it.
 The physical data and system catalog are stored on
a physical disk.
 Access to the disk is controlled primarily by OS,
which schedules disk input/output. Therefore, while
designing a DBMS its interface with the OS must be
taken into account.
DBMS Components
 data dictionary management,
 data storage management,
 data transformation and presentation,
 security management,
 multiuser access control,
 backup and recovery management,
 data integrity management,
 database access languages and application

programming interfaces
 database communication interfaces.
Data Dictionary Management
 Stores data that describes each database
(metadata).
 The DBMS stores definitions of the data

elements and their relationships (metadata) in

a data dictionary (System Catalogue).
 The DBMS uses the data dictionary to look up

the required data component structures and

relationships, thus relieving the programmer
from having to code such complex
relationships in each program.
Meta-data

 Data that describes other data

 data about data, through which the end-user data
are integrated and managed
 In databases metadata defines data elements and
attributes(Name data type, size, etc) and the set of
relationships that links the data found within the
database.
◦ conceptual, logical, physical schema
◦ mapping between schemata
◦ info for query optimization, security, authorization, etc...
◦ integrity constraints
Examples of metadata
 Web pages
◦ Descriptive text e.g blogspot
◦ Dates
◦ Keywords

 Describe metadata of data CUSTOMER?

Data Dictionary Management ctd
 Any changes made in a database structure
are automatically recorded in the data
dictionary, thereby freeing the programmer
from having to modify all of the programs
that access the changed structure. (program-
data independence)
 The DBMS provides data abstraction, and it

removes structural and data dependency

from the system.
Data Dictionary Summary
 conceptual, logical, physical schema
 mapping between schemata
 info for query optimization, security,
authorization, etc...
 integrity constraints
Data Storage Management
 The DBMS creates and manages the complex
structures required for data storage, thus relieving
the programmer from the difficult task of defining
and programming the physical data
characteristics.
 A modern DBMS provides storage not only for the
data, but also for related data entry forms or
screen definitions, report definitions, data
validation rules, procedural code, structures to
handle video and picture formats, and so on.
 Data storage management is also important for
database performance tuning.
Data Storage MGT
 Physical design
 Performance tuning
Data Transformation And Presentation
 The DBMS transforms entered data to
conform to required data structures.
 The DBMS relieves the programmer of the

chore of making a distinction between the

logical data format and the physical data
format.
 The DBMS formats the physically retrieved

data to make it conform to the user’s logical

expectations.
Security Management
 The DBMS creates a security system that enforces user
security and data privacy.
 Security rules determine which users can access the
database, which data items each user can access, and
which data operations (read, add, delete, or modify)
the user can perform.
 Security is especially important in multiuser database
systems. All database users may be authenticated to
the DBMS through a username and password or
through biometric authentication such as a fingerprint
scan. The DBMS uses this information to assign access
privileges to various database components such as
queries and reports.
Multiuser Access Control
 To provide data integrity and data
consistency, the DBMS uses complex
algorithms to ensure that multiple users can
access the database concurrently without
compromising the integrity of the database.
◦ Concurrency control algorithms
Backup And Recovery Management
 The DBMS provides backup and data recovery
to ensure data safety and integrity.
 Current DBMS systems provide special

utilities that allow the DBA to perform routine

and special backup and restore procedures.
 Recovery management deals with the

recovery of the database after a failure, such

as a bad sector in the disk or a power failure.
Such capability is critical to preserving the
database’s integrity.
Data Integrity Management
 The DBMS promotes and enforces integrity
rules, thus minimizing data redundancy and
maximizing data consistency.
 The data relationships stored in the data

dictionary are used to enforce data integrity.

 Ensuring data integrity is especially important

in transaction-oriented database systems.

Database Access Languages And
Application Programming Interfaces
 The DBMS provides data access through a query language.
 A query language is a nonprocedural language—one that
lets the user specify what must be done without having to
specify how it is to be done.
 Structured Query Language (SQL) is the de facto query
language and data access standard supported by the
majority of DBMS vendors.
 The DBMS provides application programming interfaces to
procedural languages such as COBOL, C, Java, Visual
Basic.NET, and C++.
 The DBMS provides administrative utilities used by the
DBA and the database designer to create, implement,
monitor, and maintain the database.
Database Communication Interfaces
 Current-generation DBMSs accept end-user requests
via multiple, different network environments. For
example, the DBMS might provide access to the
database via the Internet through the use of Web
browsers such as Mozilla Firefox or Microsoft Internet
Explorer. In this environment, communications can be
accomplished in several ways:
◦ End users can generate answers to queries by filling in
screen forms through their preferred Web browser.
◦ The DBMS can automatically publish predefined reports on a
Web site.
◦ The DBMS can connect to third-party systems to distribute
information via e-mail or other productivity applications.
Question ?
Discuss the different types of database
structures.
 Inverted lists systems
 Hierarchical systems
 Network systems
 Relational systems
Questions
 What is deadlock? What are its prevention and
avoidance methods?
 What is concurrency? If not controlled where

it can lead to? What are the methods to

control concurrency?
 Explain ACID properties of transaction.
 What are the components of data dictionary?.
Questions CTD
 What is meant by the term on-line transaction
processing (OLTP)?
 What is meant by the term on-line analytic processing
(OLAP)?
 Compare and contrast the approach to schema design
for OLTP and OLAP databases.
 Compare OLAP with the so-called NoSQL approach to
database design.
 Give an example of a set of requirements whose
solution would need to combine OLAP, OLTP and
NoSQL.
 Describe an architecture integrating these elements in
the system design.
Conclusion
 Whilst every business enterprise is now
implementing databases, these databases
have their complexities as they grow.
 Research areas of interest include

concurrency control in a distributed

environment, security when deployed on the
cloud and backup methods
References
Coronel, Carlos, Peter Rob. Database Systems, sixth ed.
Thomson Course Technology, 2004.
 Ambler, Scott. Introduction to Concurrency Control, 2006

http://www.agiledata.org/essays/concurrencyControl.htm
l
 Ambler, Scott. Introduction to Concurrency Control, 2006
http://www.alkissdesigners.kbo.co.ke
 Ricardo, Catherine. Databases Illuminated, second ed.
p386-387 Jones & Bartlett Learning, 2012.
 Kumar, V. Transaction Management Concurrency Control
Mechanisms, 2012 <
http://sce.umkc.edu/~kumarv/cs470/transaction/T-man
agement.pdf
>