DBMS Basic

Concepts
Introduction To Databases :
Purpose Of Database Systems,
Components Of Dbms,
Applications Of Dbms,
Three Tier Dbms Architecture,
Data Independence,
Database Schema,
 Instance,
Data Modeling,
Entity Relationship Model,
 Relational Model
 Data: It is a collection of information.

 Database: It is a collection of interrelated data

 Database System: It is computerized system, whose overall purpose is to maintain

the information and to make that the information is available on demand.

 Database Management System (DBMS): It is a collection of programs that enables

user to create and maintain a database.

 DBMS
Database Management System
• Term Database requires understanding of data and information
•Data: It can be anything like name, place or number, etc. Data usually
refers to raw data, or unprocessed data.

•Information: It is organized or classified data so that it has some

meaningful values to the receiver.
– Information is the processed data on which decisions and actions
are based.
Difference between Data
and Information?
 Data Information
Data is raw facts and figures Information is a processed form of data

For example: 12 is data For example: When 12 is stored in row

column form as shown it is information.
Age 12

Data are atomic level pieces of Information is a collection of data

information

Data does not help in decision making Information helps in decision making
 DBMS File Management System

It does not support multi-user

Multi-user access
access

Design to fulfill the need for small It is only limited to smaller DBMS
and large businesses system.

Remove redundancy and Integrity Redundancy and Integrity issues

Expensive. But in the long term

It’s cheaper
Total Cost of Ownership is cheap

Easy to implement complicated No support for complicated

transactions transactions
 Users of DBMS
Component Name Task

The Application programmers write programs in

Application
various programming languages to interact with
Programmers
databases.

Database Admin is responsible for managing the

Database Administrators entire DBMS system. He/She is called Database
admin or DBA.

The end users are the people who interact with

the database management system. They conduct
End-Users
various operations on database like retrieving,
updating, deleting, etc.
 Database
• A database is a shared collection of logically related data

designed to meet the information needs of an organization

• The related information when placed is an organized form

makes a database.

• The organization of data/information is necessary because

unorganized information has no meaning.

Popular DBMS Software
Here, is the list of some popular DBMS
system:
•MySQL
•Microsoft Access
•Oracle
•PostgreSQL
•dBASE
•FoxPro
•SQLite
•IBM DB2
•LibreOffice Base
•MariaDB
•Microsoft SQL Server etc.
 Purpose of DBMS
An example
•University Database:
Data about students, faculty, courses, research-
laboratories, course registration/enrollment etc.
Reflects the state of affairs of the academic
aspects of the university.
Purpose: To keep an accurate track of the
academic activities of the university.
 Purpose of DBMS
Before DBMSs were introduced, organizations
usually stored information in file processing
system which has a number of disadvantages:
•Data redundancy and inconsistency
•Difficulty in accessing data
•Data isolation
•Integrity problems
•Atomicity problems
•Concurrent-access anomalies
•Security problems
 Database Management System
• DBMS A database management system is the
software system that allows users to define,
create and maintain a database and provides
controlled access to the data.
• Examples:-Flipkart,Amazon,IRCTC,Facebook
etc.
• A database management system (DBMS) is
basically a collection of programs that enables
users to store, modify, and extract
information from a database as per the
requirements.
 Common Operations performed on
databases
• To add new information(Insertion)
• To view or retrieve the stored
information(Retrievel)
• To modify or edit the existing(Updation)
• To remove or delete the unwanted
information(Deletion)
• Arranging the information in a desired order
etc.(Sort)
 Components of Database
• Five major components in database system
environment:
– Hardware
– Software
– Data
– Users
– Procedures
 Components of Database System
• Hardware: It is the actual computer system used for
keeping and accessing the database. DBMS hardware
consists of secondary storage devices like hard disks.
• Software: It is the actual DBMS. Between the physical
database itself and the users of system .
• All the requests from users for access to database are
handled by DBMS.
• This is the program which controls everything. The
DBMS software is more like a wrapper around the
physical database, which provides us with an easy-to-
use interface to store, access and update data.
 Components of Database System
• Users: There are number of users who can
access or retrieve data on demand using the
applications and the interfaces provided by
DBMS. The users can be:
• Data base Administrator(DBA) (having all access
permissions)
• Application Programmers
• End Users: These are divided into:
– Naïve End User (user of ATM)
– Sophisticated user
 Components of Database System
• Procedures: It refers to the instructions and rules that
govern the design and the use of the database.
• Example-Log on to the DBMS. Start and stop DBMS system.
Make backup copies of database. Handle h/w and s/w
failures.

• Data: It is a bridge between machine and user.

• The motive behind the creation of DBMS was to store and
utilize data.
• The database contains the operational data and the meta-
data.(data about data)
 Applications of DBMS
•Banking: all transactions
•Airlines: reservations, schedules
•Universities: registration, grades
•Sales: customers, products, purchases
•Online retailers: order tracking, customized
recommendations
•Manufacturing: production, inventory, orders,
supply chain
•Human resources: employee records, salaries, tax
deductions
Different database softwares

• SQL Server (Microsoft)

• Oracle
• MySQL
• DB2 (IBM)
Data models, Schemas, and Instances
• Data model:-A set of concepts to describe the
structure of a database, and certain constraints
that the database should obey.
• Schema:- The overall description/representation
of the database is called the Database Schema.
– A schema is defined as an outline or a plan that
describes the records and relationships existing at the
particular level.
– Student schema:-Name Roll No. Address
• Instance:- Data in the database at a particular
moment in time.
 Data abstraction
• A major purpose of database system is to
provide user with an abstract view of data.
• That is, system hides certain details of how
the data are stored and maintained.
• Example- Our e-mails
 Levels of Abstraction(view of data)
3-schema architecture
• Physical level: describes how a record is stored. DBA
works at this level. The data is actually present here.
• Logical level: describes what data stored in database,
and the relationships among the data. DB designers
works at this level.They only design or makes blueprint
of data.
• View level: describe only part of database. application
programs hide details of data types. Complexity
remain due to variety of information stored. Views can
also hide information (such as an employee’s salary)
for security purposes.
• Front-end designers who works on how data should
be visible to user.
View of Data (making data
independent)

An architecture for a database system

 Database Languages
Database languages are used to create and maintain database on
computer.
•Data Definition Language(DDL): It is a language that allows user to
define data and their relationship to other types of data. Deals with
schema/structure of table.
– CREATE-Create schema objects
– ALTER-Alter schema objects
– DROP-Delete schema objects
– TRUNCATE-Delete data from schema objects
– RENAME-Rename schema objects.
•Data Manipulation Language(DML):It provides a set of operations to
support the basic data manipulation operations on the data held in
databases. It allows user to insert, update, delete and retrieve data
from the database.
– DELETE-Removes rows from tables
– INSERT-Add new rows of data into table
– SELECT-Retrieve data from one or more tables
– UPDATE-Change column values in existing rows of a table.
 Database Languages
• Data Control Language(DCL): DCL statements control
access to data and the database
– GRANT-Grant/Give privileges
– REVOKE-Take away privileges
– COMMENT-Add a comment.
• Transaction Control Language(TCL): TCL statements
manage the change made by DML statements, and
group DML statements into transactions
– COMMIT-Make a transaction changes permanent
– ROLLBACK-Undo changes in a transaction.
– SAVEPOINT-Set point to which transaction can be rolled
back
– SET TRANSACTION-Establish properties for a transaction.
Components of DBMS
 Components of Database System
• Storage Manager
• Query Processor
 Storage Manager
• Is responsible for storing ,retrieving and
updating the data in the database
• It is a program module which provides the
interface between the low level data stored in
the database and application programs and
queries submitted to the system
 Components of Storage Manager
• Authorization and Integrity Manager(checks
the authority of users to access data)
• Transaction Manager(ensures consistency
state of data, concurrent executions proceed
without conflicting)
• File Manager(manages the space on disk
storage)
• Buffer Manager(responsible for fetching the
data from disk storage into main memory)
 Storage Manager Responsiblites
• Interaction With the File Manager
• Intergrity Constraints enforcement
• Security Enforcement
• Back up and Recovery
• Concurrency Control
 Query Processor and its Components
• Is responsible for the execution of the query

Components :
• DDL Interpreter
• DML Compiler
• Query Evaluation Engine
 Types of Database System
• On the Basis of Number of users
• On the basis of site Location
 On the basis of Number of users
• Single user DBMS
In single user system the database resides on
one computer and is only accessed by one
user at a time. This one user may design,
maintain and write database programs.
• Multi user DBMS
Data is shared and integrated.Example-
Account dept.and Library dept. may need
student addresses.
 On the Basis of Site Location
• Centralized System-Consists of single CPU
• Parallel System-Consists of multiple CPUs
• Distributed System-Physically distributed over
a computer network
• Client –Server system
Database Architecture
Two-tier and three-tier architecture
 Two-tier architecture
• It is client-server architecture
• Direct communication
• Run faster
• Two parts:
1) Client Application (Client Tier)
2) Database (Data Tier)
• On client application side the code is written for
saving the data in the SQL server database
Advantages:

•Easy to maintain and modification is bit easy

•Communication is faster

Disadvantages:

•Performance will be degrade upon increasing

the users
•Security
•Cost-ineffective
 Three-tier architecture
• Web based application
• Three layers:
1) Client layer
2) Business layer
3) Data layer
 1) Client Layer
• Also called as Presentation layer contains
UI part of our application
• This layer is used for the design purpose
where data is presented to the user or
input is taken from the user.
• For example designing registration form
which contains text box, label, button etc.
 2) Business Layer
• All business logic written like validation of
data, calculations, data insertion etc…
• This acts as an interface between Client
layer and Data Access Layer
• Make communication faster between
client and data layer.
 3) Data Layer
• Actual database is comes in the picture
• Contains methods to connect with
database and to perform insert, update,
delete, get data from database based on
our input data.
• Advantages of 3-tier architecture-
• High performance, Scalability, Improved
Security , Improve Data Integrity
Three-tier architecture
• External or View level: It is the users’ view of the
database. This level describes that part of the database
that is relevant to each user.
– For example, one user may view dates in the form (day,
month, year), while another may view dates as (year,
month, day).
• Conceptual or logical level: It is the community view of
the database. This level describes what data is stored
in the database and the relationships among the data.
• It represents:
– All entities, their attributes, and their relationships;
– The constraints on the data;
– Security and integrity information.
• Internal or storage level: It is the physical
representation of the database on the computer. This
level describes how the data is stored in the database.
 Data Independence-Achievement
of Layered Architecture of DBMS

• Two kinds of data independence:

– Logical data independence
– Physical data independence
 Data Independence
• Logical Data Independence: The capacity to
change the conceptual schema without having
to change the external schemas and their
application programs.

• Physical Data Independence: The capacity to

change the internal schema without having to
change the conceptual schema.
 Data Independence
• The processes of transforming requests and results
between the levels are called mappings.
• When a schema at a lower level is changed, only
the mappings between this schema and higher-
level schemas need to be changed in a DBMS that
fully supports data independence. The higher-level
schemas themselves are unchanged. Hence, the
application programs need not be changed since
they refer to the external schemas.
 Data Independence
• The processes of transforming requests and results
between the levels are called mappings.
• When a schema at a lower level is changed, only
the mappings between this schema and higher-
level schemas need to be changed in a DBMS that
fully supports data independence. The higher-level
schemas themselves are unchanged. Hence, the
application programs need not be changed since
they refer to the external schemas.
 Data models in DBMS
• A model is representation of reality,
’real world’ objects and events and their
associations
• Data models define how the logical
structure of a database is modeled.
• Data models define how data is
connected to each other and how they
are processed and stored inside the
system.
 Types of Data models
Flat Data Model :-Flat data model is the first and
foremost introduced model and in this all the
data used is kept in the same place. Since it was
used earlier this model was not so scientific.
Entity Relationship Data Model
Entity relationship model is based on the
notion of the real world entities and their
relationships. Two things:-
•Entity and their attributes
•Relationships among entities
 Relational Data Model
In this model the data can be stored in the
form of tables.
Each row in a relation contains unique value
and it is called as tuple, each column contains
value from same domain and it is called as
attribute.
 Network Data Model
Network model has the entities which are
organized in a graphical representation and
some entities in the graph can be accessed
through several paths.Allows 1:1,1:M,M:M,M:1
relationships..
 Hierarchical Data Model
Hierarchical model has one parent entity
with several children entity but at the
top we should have only one entity
called root.
 Object oriented Data Model
Object oriented data model is one of the developed
data model and this can hold the audio, video and
graphic files. These consist of data piece and the
methods which are the DBMS instructions.