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 Information helps in decision

making making
 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.
 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.
 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.
Operations on databases

 To add new information

 To view or retrieve the stored information
 To modify or edit the existing
 To remove or delete the unwanted information
 Arranging the information in a desired order etc.
 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
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 is a layer of software, called DBMS.
 Data: Data acts as the bridge between the machine parts (h/w
and s/w) and the users which directly access it or access it
through some application programs.
Data may be of different types
• USERDATA – It consists of tables of data called relations where
columns are called fields or attributes and rows are called records
or tuples.
• METADATA – It describes the properties or characteristics of end
user data and context of the data. It provides the complete
picture of data in database.
• APPLICATION METADATA – It stores the structure and format of
queries, reports and other application components.
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:
 Naïve users
 Online users
 Application Programmers
 Sophisticated Users
 Data base Administrator ( DBA)
Different Types of Database
Users in DBMS
Application Programmers
 As its name shows, application programmers are the one who writes
application programs that uses the database. These application
programs are written in programming languages like COBOL or PL
(Programming Language 1), Java and fourth generation language.
These programs meet the user requirement and made according to
user requirements. Retrieving information, creating new information
and changing existing information is done by these application
programs.
 They interact with DBMS through DML (Data manipulation
language) calls. And all these functions are performed by generating
a request to the DBMS. If application programmers are not there
then there will be no creativity in the whole team of Database.
 End Users (through Interactive Query Language
 End users are those who access the database from the terminal end. They
use the developed applications and they don’t have any knowledge about
the design and working of database. These are the second class of users and
their main motto is just to get their task done. There are basically two types
of end users that are discussed below.
 Casual End User
 These users have great knowledge of query language. Casual users access
data by entering different queries from the terminal end. They do not write
programs but they can interact with the system by writing queries.
 Naïve End Users
 Any user who does not have any knowledge about database can be in this
category. There task is to just use the developed application and get the
desired results. For example: Clerical staff in any bank is a naïve user. They
don’t have any dbms knowledge but they still use the database and perform
their given task.
 Sophisticated Users - They are database developers, who write SQL
queries to select/insert/delete/update data. They do not use any
application or programs to request the database. They directly
interact with the database by means of query language like SQL.

 DBA (Database Administrator)

 DBA can be a single person or it can be a group of person. Database Administrator
is responsible for everything that is related to database. He makes the policies,
strategies and provides technical supports.

 System Analyst
 System analyst is responsible for the design, structure and properties of database.
All the requirements of the end users are handled by system analyst. Feasibility,
economic and technical aspects of DBMS is the main concern of system analyst. x
Components of Database
System
 Procedures: It refers to the instructions and rules that
govern the design and the use of the database. The
users of the system and the staff that manage the
database requires documented procedures on how
to use or run the system.
 Levels of Abstraction(view of

data)
Physical level: describes how a record (e.g., customer) is stored.
 Logical level: describes what data stored in database, and the
relationships among the data. DBA, who decides what information to
keep in the database, use the logical level of abstraction.
 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.
View of Data

An architecture for a database system

 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 and also describes the data structures and
access methods to be used by the database.
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.
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 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.

• Instance:- Data in the database at a

particular moment in time.
 Database Languages
Database languages are used to create and maintain database on computer.
 1. Data Definition Language(DDL): It is a language that allows user to define data and their
relationship to other types of data.
 CREATE
 ALTER
 DROP
 TRUNCATE
 RENAME

 DROP vs TRUNCATE
 Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.
 Truncate preserves the structure of the table for future use, unlike drop table where the table is
deleted with its full structure.
Database Languages

 2. 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
 INSERT
 SELECT
 UPDATE
 Database Languages
 3. Data Control Language(DCL): DCL statements control access to data and the database
 GRANT
 REVOKE
 COMMENT
 The GRANT command is used for conferring the authorization to the users whereas REVOKE
command is used for withdrawing the authorization. Select, insert, update and delete are some of the
privileges that are included in SQL standards.

 SYNTAX of GRANT Command:

 grant <privilege record>
on <relation title or view title>
to <user/role record>;
example to illustrate the Granting of privileges. We have two schemas for the tables Faculty and
Department and accounts A1 and A2.
 GRANT SELECT, INSERT, UPDATE ON FACULTY, DEPARTMENT TO A1, A2;
 In the above given example, the account A1 and A2 are allowed to perform the select, insert and
update operations on the employee and department table.
 SYNTAX of REVOKE Command:
 revoke <privilege list>
on <relation name or view name>
from <user/role list>;

 Let’s take the similar example to illustrate the Revoking of

privileges.
 REVOKE INSERT, UPDATE ON FACULTY,
DEPARTMENT FROM A1, A2;
 In the above given example, the A1 and A2 accounts are
withdrawn from their rights and are not permitted to perform
insert and update operations on the employee and department
table.
 4. Transaction Control Language(TCL): TCL statements manage
the change made by DML statements, and group DML statements into
transactions.
 COMMIT: COMMIT command to make changes permanent save to a
database during the current transaction.
 ROLLBACK: ROLLBACK command execute at the end of current
transaction and undo/undone any changes made since the begin
transaction.
 SAVEPOINT: SAVEPOINT command save the current point with the
unique name in the processing of a transaction.
 AUTOCOMMIT: Set AUTOCOMMIT ON to execute COMMIT
Statement automatically.
 SET TRANSACTION: PL/SQL SET TRANSACTION command set
the transaction properties such as read-write/read only access.
Structure and
Components of DBMS
Database
Architecture
Two-tier and three-tier
architecture
Three-tier architecture
Data Independence-Achievement
of Layered Architecture of DBMS

 A major objective of three level architecture is to provide Data

Independence. It is the property of the database which tries to
ensure that if we make any change in any level of schema of the
database, the schema above it would require minimal or need of
change.
 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.
Mcqs

1. In three-tier architecture, intermediate layer between database and

client servers is classified as
A. functional server
B. transaction server
C. application server
D. disk server
 2. Process of converting requests into results between three-schema
architecture internal, external and conceptual levels is called
A. mapping
B. pitching
C. transforming
D. dependence
3. Levels in which three schema architecture can be defined includes

A. internal schema

B. conceptual schema

C. external schema

D. all of above

4. Database management architecture in which there is middle level between database server and client server is classified as

A. three-tier architecture

B. two-tier architecture

C. single-tier architecture

D. three way DBMS module

5. Information stored in information repository can be accessed by

A. client servers

B. host computers

C. security managers

D. database administrators

6. In database management system, term which is used to represent real world concept or object is classified as

A. entity

B. attribute

C. relationship

D. abstraction
 7)A logical schema
A.is the entire database
B.is a standard way of organizing information into
accessible parts.
C.Describes how data is actually stored on disk.
D. All of the above

 8)An advantage of the database management approach

is
A.data is dependent on programs
B.data redundancy increases
C.data is integrated and can be accessed by multiple
programs
D. none of the above
 9) Grant and revoke are ……. statements.
A. DDL
B. TCL
C. DCL
D. DML
 10) DBMS helps achieve
A. Data independence
B. Centralized control of data
C. Neither A nor B
D. Both A and B
 11) ………. command can be used to modify a
column in a table
A. alter
B. update
C. set
D. create
