Relational Database

Management System
(RDBMS)
Conventional method of Data storage
• In the traditional approach, information is stored in flat
files which are maintained by the file system of OS.
• Application programs go through the file system to access
these flat files.
• End users use application programs to perform specific
tasks
DBMS handles all requests for access to the DB
Database
• Organized collection of interrelated data
• Data in the database
➢ Is Integrated:
Collection of several distinct files.
Files may have some duplicate data
but duplication is kept to minimum.
➢ Can be shared :
Individual piece of data in the DB can be shared
➢ Can be concurrently accessed:
Different users can access the same piece of data
at the same time.
 DBMS

• The end user can use either Application program or Standard

SQL to access the data

• The std. SQL interface is provided as an integral part the of

database system software to access the DB.

• The DBMS acts as a layer of abstraction over the file system

• Abstraction: A simplified representation of something complex.

File System interface vs. DBMS interface
 Master And Transaction Files
• Master file:
➢ stores relatively static data about an entity
➢ Changes rarely
➢ E.g. Custmer_Details file ( Bank DB)
➢ Changes when a new account is created or whenever the
existing details of customer changes
• Transaction File:
➢ stores relatively transient data about a particular data processing task
➢ Changes frequently as transactions( withdraw, deposit etc) happen
more periodically and in large number
➢ E.g. Custmer_Transaction file
Three level Architecture for DBMS
Three level Architecture for DBMS
• Most commercial DBs are based on 3 level architecture model
ANSI / SPARC ( American National Standards Institute / Standard
Planning And Requirement committee.
• Overall design of the DB is called Database schema
• Database schema does not change frequently
• Database systems support
✓ one internal schema
✓ one conceptual schema and
✓ several external schemas
Three level Architecture for DBMS
• External / View Level:
• Many users of DB systems are not concerned with all the info in DB. They
need to access only a portion of the DB. So system may provide many
views for the same DB.

• Several views of the DB are defined and the DB end users are able to see
these views.

• These views hide the conceptual level details

• Prevents users from accessing other parts of the DB and hence provides
security

• View: is a virtual table in the DB defined by a query

• Conceptual level:
• Describes about the data stored in the DB and the relationship among

these data.

• The Conceptual view is how the DBA(Database Administrator) views it.

DBA decides what info must be kept in the DB

• Table definitions : Attributes, datatype, width definition etc.

• Internal/ Physical level:
• Lowest level of abstraction and describes the data storage and
access methods
• Deals with how the data is actually stored

• How the records are stored as a block of consecutive storage

locations( words/ bytes)
• Conceptually what happens in the DBMS
• A user issues an access request for data( SQL)
• The DBMS receives the request and analyzes it
• The DBMS checks the external schema, external / conceptual
mapping, conceptual schema, conceptual / internal mapping, storage
structure definition
• The DBMS executes the required operations on the stored database
• End users:
• Deal only with the highest level of abstraction
• Details of the DB is not known
• Involved in updates to DB or queries on the DB
• Can range from simple viewers who pay attention to the logs or
market rates to sophisticated users such as business analysts.
• Application programmer:
• Responsible for writing DB application programs in some
Programming language( 3rd generation / 4th generation)
• Application programs access the DB by issuing appropriate requests
• Users and application programs need not know exactly where or how
the data is stored in order to access it.
DBAs ( Database Administrators)
• Definition of the conceptual schema
➢ Decides what info must be held in DB
➢ Identifies entities and info about these entities
➢ This is called logical database design
➢ Then create conceptual schema
• Definition of the internal schema
➢ Decides how the data is to be represented in DB
➢ This is called physical database design
➢ Create corresponding storage structure
➢ Define conceptual to internal mapping
 DBA
• Granting of authorization for data access:
➢Granting different types of authorizations( read, write, etc.) thereby
regulating which part of the DB various users can access
• Defining integrity constraints:
• The data values stored in the DB must satisfy certain consistency
constraints
➢look after DB usage and by whom it should be used. They create access
profiles for users and apply limitations to maintain isolation and force
security.
➢Controls the allocation of system resources/ grants/ revokes privileges to /
from users and ensures the consistency of the DB.
( system license, required tools, and other software and hardware related
maintenance)
• Database Designers:They identify and design the whole set of entities,
 Users of a DBMS

• DBA is a key person and takes care of most administrative

tasks
• Database designers, design the database elements.
• Application programmers, make use of the various database
elements and write programs to retrieve data from them.
1. Proper database design can reduce or eliminate data
redundancy and confusion.
2.Support for unforeseen (ad hoc) information requests are
better supported - better flexibility.
3. Data can be more effectively shared between users and/or
application programs.
4. Data can be stored for long term analysis (data warehousing).
Traditional Approach to Information Processing
Disadvantages:
• Data insecurity: Data maintained in flat files is easily accessible and
therefore not secure. e.g., A customer wants to know about his a/c
balance, but it is difficult to give the customer, access to only his data
in a file. ( i.e., Difficult to enforce security constraints)
• Data Redundancy: Often same info is duplicated in two or more files.
This leads to higher storage and access cost, it also leads to
inconsistency if changes are made to data in one file and not in other
file. ( Update anomalies)
• Data Isolation: All the related data is not available in one file. Data is
scattered, and files may be in different formats. So writing new
application program to retrieve appropriate data is difficult.
Traditional Approach to Information Processing
• Program / data dependence:
Application programs are data dependent. If new data field
is added then Application program also must be altered.
If field delimiter or record delimiter is changed , it
necessitates that application which depends on it also be
altered.
• Lack of Flexibility:
Retrieval of info only for predetermined requests for data, and
for unanticipated data, extensive programming is however
required that leads to delay.
For ex, Application program is available to generate a list of
customer names in a particular area of the city. However the
bank manager requires a list of customers who have an account
balance > 10000 and reside in a particular area of the city.
• Consider the banking system. A master file, Customer_Fixed_Deposit
file exists which has details about the customers fixed deposit
accounts. A customer’s fixed deposit record is described as follows:
• Cust_ID
• Cust_Last_Name
• Cust_Mid_Name
• Cust_First_Name
• Cust_Email
• Fixed_Deposit_No
• Amount_in_Dollars
• Rate_of_Interest_in_Percent
suppose Fixed_Deposit_Maturity_Date is added to the master file.
Database Management System (DBMS)
• Collection of interrelated files and a set of programs
• These programs allow users to access and modify the
files.
• Goal: To provide a convenient and efficient way to
store, retrieve and modify information
• Layer of abstraction between application programs and
file system.
Why DBMS?
• Data Independence: Application programs and queries are data
independent
• Effective data sharing: Allows sharing of data among different users.
Multiple users are able to access the DB concurrently without facing the
issues of inconsistent data
• Controls redundancy and inconsistency
• Enforces integrity constraints( business rules) by preventing the entry of
invalid info into the DB.
• Enables backup and recovery from System crashes
• Better security
• Better flexibility
• Multiple views − DBMS offers multiple views for different
users. A user who is in the Sales department will have a
different view of database than a person working in the
Production department. This feature enables the users to
have a concentrate view of the database according to their
requirements.
• Security − Features like multiple views offer security to some
extent where users are unable to access data of other users
and departments.
• Multiuser and Concurrent Access − DBMS supports multi-
user environment and allows them to access and manipulate
data in parallel. Though there are restrictions on transactions
when users attempt to handle the same data item, but users
are always unaware of them.
• Query Language − DBMS is equipped with query language, which
makes it more efficient to retrieve and manipulate data. A user can
apply as many and as different filtering options as required to retrieve
a set of data. Traditionally it was not possible where file-processing
system was used.
Types of databases: Centralized & Distributed
Centralized DB
Distributed DB