DBMS

UNIT I
INTRODUCTION TO DBMS
History of Database
Data is a collection of facts and figures. The data collection was increasing
day to day and they needed to be stored in a device or a software which is
safer. Charles Bachman was the first person to develop the Integrated Data
Store (IDS) which was based on network data model for which he was
inaugurated with the Turing Award (The most prestigious award which is
equivalent to Nobel prize in the field of Computer Science.)Database
Management System (DBMS) is the collection of software programs that is
used to manage the database effectively and conveniently. DBMS interface
with application programs so that database can be shared by multiple users
or application programs.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 1

DBMS

Characteristics of database approach :

Some of the most important characteristics of the database approach to the
file processing approach are the following as follows.
Approach-1
1.Self-Describing Nature of a Database System :
One of the most fundamental characteristics of the database approach is
that the database system contains not only the database itself but also an
entire definition or description of the database structure and constraints also
known as metadata of the database.
This definition is stored within the DBMS catalog, which contains
information like the structure of every file, the sort and storage format of
every data item, and various constraints/rules on the information.
Approach-2 :
Isolation between Programs and Data, and Data Abstraction :
In a traditional file processing system, the structure of database knowledge
files is embedded within the application programs, so any changes to the
structure of a file may require changing all programs that access that file.
Against this, DBMS access programs don’t require such changes in most
cases, so independence is achieved between them.
The structure of knowledge files is stored within the DBMS catalog separately
from the programs that access them. We call this property program-data
independence.
The characteristic that allows program-data independence and program-
operation independence is known as data abstraction.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 2

DBMS
Approach-3 :
Support for Multiple Views of the Data :
A database sometimes has many users, each of whom may require a special
perspective or view of the database.
A view could also be a subset of the database, or it’s going to contain virtual
data that is derived from the database files but isn’t explicitly stored.
Some users might not get to remember whether the information they ask for
is stored or derived.
A multi-user DBMS whose users have a spread of distinct applications must
provide facilities for outlining multiple views. This provides many benefits for
large databases such as the Aadhaar database.

Approach-4 :
Sharing of knowledge and Multi-user Transaction Processing :
A multi-user DBMS, as its name implies, must allow multiple users to access
the database at an equivalent time or concurrently.
This is often essential if data for multiple applications is to be integrated and
maintained during a single database such as the latest feature of WhatsApp
integration with Facebook.
The DBMS must implement concurrency control in the software to make
sure that several users trying to update equivalent data do so in a controlled
manner in order that the results of the updates are correct.

DIFFERENT PEOPLE BEHIND DBMS

Database users are categorized based up on their interaction with the
database
There are two categories of people behind DBMS
a)Those who actually use ad control the database content , ad those who
design develop and maintain database application( called --- Actors on the
Scene)
b) Those who design and develop the DBMS software and related tools and
the computer system operators ( called ----- Workers Behind the Scene)
Actors on the Scene
Database Administrator (DBA) : Database Administrator (DBA) is a
person/team who defines the schema and also controls the 3 levels of
database. The DBA will then create a new account id and password for the
user if he/she need to access the database. DBA is also responsible for
providing security to the database and he allows only the authorized users
to access/modify the data base. DBA is responsible for the problems such
as security breaches and poor system response time.
DBA also monitors the recovery and backup and provide technical support.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 3

DBMS
The DBA has a DBA account in the DBMS which called a system or
superuser account.
DBA repairs damage caused due to hardware and/or software failures.
DBA is the one having privileges to perform DCL (Data Control Language)
operations such as GRANT and REVOKE, to allow/restrict a particular user
from accessing the database.
Naive / Parametric End Users : Parametric End Users are the
unsophisticated who don’t have any DBMS knowledge but they frequently
use the database applications in their daily life to get the desired results. For
examples, Railway’s ticket booking users are naive users. Clerks in any bank
is a naive user because they don’t have any DBMS knowledge but they still
use the database and perform their given task.
System Analyst :
System Analyst is a user who analyzes the requirements of parametric end
users. They check whether all the requirements of end users are satisfied.
Sophisticated Users : Sophisticated users can be engineers, scientists,
business analyst, who are familiar with the database. They can develop their
own database applications according to their requirement. They don’t write
the program code but they interact the database by writing SQL queries
directly through the query processor.
Database Designers : Data Base Designers are the users who design the
structure of database which includes tables, indexes, views, triggers, stored
procedures and constraints which are usually enforced before the database
is created or populated with data. He/she controls what data must be stored
and how the data items to be related. It is responsibility of Database
Designers to understand the requirements of different user groups and then
create a design which satisfies the need of all the user groups.
Application Programmers : Application Programmers also referred as
System Analysts or simply Software Engineers, are the back-end
programmers who writes the code for the application programs. They are the
computer professionals. These programs could be written in Programming
languages such as Visual Basic, Developer, C, FORTRAN, COBOL etc.
Application programmers design, debug, test, and maintain set of programs
called “canned transactions” for the Naive (parametric) users in order to
interact with database.
Casual Users / Temporary Users : Casual Users are the users who
occasionally use/access the database but each time when they access the
database they require the new information, for example, Middle or higher
level manager.
Specialized users : Specialized users are sophisticated users who write
specialized database application that does not fit into the traditional data-
processing framework. Among these applications are computer aided-design
systems, knowledge-base and expert systems etc.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 4

DBMS

Workers Behind the Scene

DBMS System designers and implementers are present who design and
implement the DBMS modules and interfaces as a software package.
Tool Developers include persons who design and implement software tools
facilitating database system design, performance monitoring , creation of
graphical user interfaces, prototyping etc.
Operators and maintenance personnel are the system administrator
personnel who are responsible for the actual running and maintenance of the
hardware and software environment for the database system.

A Database Management System (DBMS) is a software system that allows

users to store, manage, and manipulate data in a structured and organized
way.
Here are six advantages of using a DBMS:
1. Improved efficiency
Businesses use DBMS to manage large amounts of data efficiently and make
well-informed decisions more quickly.
2. Versatility
Systems for managing databases are highly flexible. Computers, tablets, and
even mobile devices can access them.
3. Organizing and categorizing data
DBM solutions provide the categorization and structuring of available data for
businesses and people. For instance, a university's database can preserve
student grades and GPA as well as admission details (such as name,
anticipated graduation year, and nationality)
4. Multi-Access
A DBM has multiple authorized users to access the same database from
different locations and for various purposes. For example, an HR
administrator can use it to review annual salary increases, while the
development team can use it to find temporary replacements for sick team
members.
5. Organizing the working environment
Use a database management system to organize your work environment
seamlessly. You can access, update, and process data easily using SQL.
Popular systems like Microsoft SQL Server and MySQL come with software
that enables third-party access to data.
6. Managing data in new and better ways
In addition to allowing authorized users to add new data to the database, a
strong database management system also updates the data that is currently
there and removes any outdated data.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 5

DBMS
When not to use a DBMS

1.Main inhibitors (costs) of using a DBMS

a) High initial investment and possible need for additional hardware.
b) Overhead for providing generality, security, concurrency control, recovery,
and integrity functions

When a DBMS may be unnecessary:

A). If the database and application are simple, well defined and not expected
to change.
b) If there are stringent real time requirement that may not one met because
of DBMS overhead.
c) If access to data by multiple users is not required

When no DBMS may suffice:

a) If the database system is not able to handle the complexity of data because
of modelling limitations
b) If the database users need special operations not supported by the DBMS.

A Data Model in Database Management System (DBMS) is the concept of

tools that are developed to summarize the description of the database. Data
Models provide us with a transparent picture of data which helps us in
creating an actual database. It shows us from the design of the data to its
proper implementation of data

Types of Relational Models

Conceptual Data Model
Representational Data Model
Physical Data Model
It is basically classified into 3 types:-

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 6

DBMS

1. Conceptual Data Model OR High Level

The conceptual data model describes the database at a very high level and
is useful to understand the needs or requirements of the database. It is this
model, that is used in the requirement-gathering process i.e. before the
Database Designers start making a particular database.

Entity-Relationship Model( ER Model): It is a high-level data model which

is used to define the data and the relationships between them. It is basically
a conceptual design of any database which is easy to design the view of data.
Components of ER Model:
Entity: An entity is referred to as a real-world object. It can be a name, place,
object, class, etc. These are represented by a rectangle in an ER Diagram.
Attributes: An attribute can be defined as the description of the entity.
These are represented by Eclipse in an ER Diagram. It can be Age, Roll
Number, or Marks for a Student.
Relationship: Relationships are used to define relations among different
entities. Diamonds and Rhombus are used to show Relationships.
Characteristics of a conceptual data model
Offers Organization-wide coverage of the business concepts.
This type of Data Models are designed and developed for a business
audience.
The conceptual model is developed independently of hardware specifications
like data storage capacity, location or software specifications like DBMS
vendor and technology. The focus is to represent data as a user will see it in
the “real world.”

Conceptual data models known as Domain models create a common

vocabulary for all stakeholders by establishing basic concepts and scope
2. Representational Data Model (Relational Model)
• In this model, data is organized in two-dimensional tables and the
relationship is maintained by storing a common field.
• This model was introduced by E.F Codd in 1970, and since then it has been
the most widely used database model
• The basic structure of data in the relational model is tables. All the
information related to a particular type is stored in rows of that table.
• Hence, tables are also known as relations in the relational model. • You can
design tables, normalize them to reduce data redundancy, and use Structured
Query language or SQL to access data from the tables.
• Some of the most popular databases are based on this database model. For
example, Oracle, MySQL, etc

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 7

DBMS

3. Physical Data Model

The physical Data Model is used to practically implement Relational Data
Model. Ultimately, all data in a database is stored physically on a secondary
storage device such as discs and tapes. This is stored in the form of files,
records, and certain other data structures. It has all the information on the
format in which the files are present and the structure of the databases, the
presence of external data structures, and their relation to each other

Instances
These refer to a collection of all the information and data stored at any given
moment.

Schema
It refers to an overall description that we get for any given database. In simpler
words, schema refers to the basic structure of how one needs to store data in
any database. There are basically two types of Schema: Physical Schema and
Logical Schema. : Design of a database is called the schema
Physical Schema – This schema describes the DB designed at a physical
level.
Logical Schema – This schema describes the DB designed at a logical level.
Example
Consider a table given below which has the Student (Schema) −

Std ID Name City

100 Lucky Hyderabad

101 Pinky Delhi

102 Bob Hyderabad

In the above table, rows are called instances.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 8

DBMS
Finally, we can say that the content of database at a point of time is called
instance or database state.
The three-schema architecture divides the database into three-level used to
create a separation between the physical database and the user application.
In simple terms, this architecture hides the details of physical storage from
the user.
The database administrator (DBA) responsible is to change the structure of
database storage without affecting the user’s view. It deals with the data, the
relationship between them and the different access methods implemented on
the database. The logical design of database is called a schema

This architecture contains three layers of database management system,

which are as follows −
External level
Conceptual level
Internal level

External/ View level

This is the highest level of database abstraction. It includes a number of
external schemas or user views. This level provides different views of the same
database for a specific user or a group of users. An external view provides a
powerful and flexible security mechanism by hiding the parts of the database
from a particular user.

Conceptual or Logical level

This level describes the structure of the whole database. It acts as a middle
layer between the physical storage and user view. It explains what data to be
stored in the database, what the data types are, and what relationship exists
among those data. There is only one conceptual schema per database.
This level describes the structure of the whole database. It acts as a middle
layer between the physical storage and user view. It explains what data to be
stored in the database, what the data types are, and what relationship exists
among those data. There is only one conceptual schema per database.

Internal or Physical level

This is the lowest level of database abstraction. It describes how the data is
stored in the database and provides the methods to access data from the
database. It allows viewing the physical representation of the database on the
computer system.

The interface between the conceptual and internal schema identifies how an
element in the conceptual schema is stored and how it may be accessed. It is

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 9

DBMS
one which is closest to physical storage. The internal schema not only defines
different stored record types, but also specifies what indices exist, how stored
fields are represented.

Data independence
Data independence is the ability to modify the scheme without affecting the
programs and the application to be rewritten. Data is separated from the
programs, so that the changes made to the data will not affect the program
execution and the application.

There are two levels of data independence based on three levels of abstraction.
These are as follows −
Physical Data Independence
Logical Data Independence
Physical Data Independence

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 10

DBMS
Physical Data Independence means changing the physical level without
affecting the logical level or conceptual level. Using this property, we can
change the storage device of the database without affecting the logical schema.
The changes in the physical level may include changes using the following −
A new storage device like magnetic tape, hard disk, etc.
A new data structure for storage.
A different data access method or using an alternative files organization
technique.
Changing the location of the database.

Logical Data Independence

Logical view of data is the user view of the data. It presents data in the form
that can be accessed by the end users.
Codd’s Rule of Logical Data Independence says that users should be able to
manipulate the Logical View of data without any information of its physical
storage. Software or the computer program is used to manipulate the logical
view of the data.

Database administrator is the one who decides what information is to be

kept in the database and how to use the logical level of abstraction.
The changes in the logical level may include −
Change the data definition.
Adding, deleting, or updating any new attribute, entity or relationship in the
database.

Database Languages
Database languages are used to read, store and update the data in the
database. Specific languages are used to perform various operations of the
database.
Types of Database Languages

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 11

DBMS

DDL(Data Definition Language)

Data Definition Language(DDL) is used for describing structures or patterns
and its relationship in a database. It is also used to define the database
schema, tables, index, Constraints, etc. It can also be used to store
information like the number of tables, names, columns, indexes, etc. The
commands only affect the database structure and not the data.
The commands used in DDL are:
Create: It is used to create a database or table.
Alter: It is used to make a change in the structure of a database.
Drop: It is used to completely delete a table from the database
Rename: It is used to rename a table.
Truncate: It is used to delete the entities inside the table while holding the
structure of the table.
Comment: It is used to comment on the data dictionary.

DML(Data Manipulation Language)

DML is used to manipulate the data present in the table or database. We can
easily perform operations such as store, modify, update, and delete on the
database.
The commands used in DML are:
Select: It shows the record of the specific table. Also, it can be used with a
WHERE clause to get the particular record.
Insert: It allows users to insert data into the database or tables.
Update: It is used to update or modify the existing data in database tables.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 12

DBMS
Delete: It is used to delete records from the database tables. Also, it can be
used with a WHERE clause to delete a particular row from the table.
Merge: It allows the insert and update(UPSERT) operations.

DCL(Data Control Language)

DCL works to deal with SQL commands that are used to permit a user to
access, modify and work on a database. it is used to access stored data. It
gives access, revokes access, and changes the permission to the owner of the
database as per the requirement.
The commands used in DCL are:
Grant: It is used to give access to security privileges to a specific database
user.
Revoke: It is used to revoke the access from the user that is being granted by
the grant command.

TCL(Transaction Control Language)

It can be grouped into a logical transaction and is used to run the changes
made by the DML command in the database.
Commit: Transaction on the database is saved using Commit.
Rollback: The database gets restored to the original since the last commit.
Interface
An interface is a program that allows users to input queries into a database
without writing the code in the query language. An interface can be used to
manipulate the database for adding, deleting, updating, or viewing the data.

Types of Interface are

Form−based Interface
A form is displayed to each user by the form−based interface. The user fills in
the details and submits the form to make a new entry into the database. It
can also be done when the user only fills in some details and the system will
help by retrieving the rest of the details from the database. The form−based
interface is built for the naive user(inexperienced user) which deals with a
limited number of operations. Many DBMS have specification language which
helps the programmer define such forms.
Example
Student entering his roll. no, branch in the form to get the grade card.

Menu−based User Interface

In this interface, the user was provided with a list of options (called a menu)
through which the user forms a request. The user doesn’t need to memorize
the command and syntax and the query is composed step by step by picking
options from a menu. Pull−down menu interfaces are mostly used in

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 13

DBMS
web−based user interfaces and are often used in browsing interfaces by which
the database content can be looked through.
Example
In a shopping website, categories are selected from the menu, brands are
selected from the menu of brands, and budget ranges are applied from the
menu of budget range.

GUI(Graphical User Interface)

Users are provided a schema of diagrammatic form by which query can be
specified through manipulating the diagram. GUI utilizes both menu and form
in several cases. Schema Diagram's specific parts are selected using devices
used by GUI.
Example
You liked a video on Instagram by tapping with your finger, and the color
changes to red. The visual graphic gets changed due to user action.

Natural Language Interface

A natural language interface contains its unique schema more like the
high−level conceptual schema. It also has a directory of important words. It
generates a query based on the interpretation of important words in the input
by the user and if the interpretation is successful, then it displays the result
to the user.
Example
A user googled the fastest car in India, and now the natural language interface
will look for the important words i. e. fastest, car, India, and show the result
accordingly.
Example
OK Google, Siri on Apple, and Alexa is used in the form of speech.

Interface for DBA

DBA staff are provided commands that can only be used by them only to create
an account, grant account authorization, and change a schema, and storage
structure reorganization.

THE DATABASE SYSTEM ENVIRONMENT

The DDL compiler processes schema definitions, specified in the DDL,and
stores the description of the schema in the DBMS Catalog..The catalog
includes information such as names and sizes of the sizes of the files, data
types of data of data items. Storage details of each file, mapping information
among schemas and constraints.

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 14

DBMS

DR.VIDYA PAWAR, VEDANTA DEGREE COLLEGE 15