Database Management Systems

Dr. Chandan Kumar

Module I: Introduction to DBMS
Definition of DBMS, Data Independence, DBMS Architecture, Levels, Database
Administrator, File System Approach Vs DBMS Approach, Advantages of Using a DBMS,
Data Models, Schemas, and Instances.
 1. Introduction to Database Systems
2. File System
3. Advantages of DBMS
4. DBA, Designers and End users
5. Data Models
• Relational Model
Topics:
• Entity-Relationship Model
• Hierarchical Model
• Network Model
• Object-Oriented Model

6. Levels of Abstraction or Three-Schema architecture

7. DBMS architecture

8. Classification Of Database Management Systems

CSIT132 AIIT, Amity Ranchi

 Data

• A data mean known facts or raw facts that can be recorded

and that have implicit meaning.
Topic 1
• For example, consider the names, telephone, numbers, and
Introduction to
Database addresses of the people you know. You may have recorded
Systems this data in an indexed address book, you may have stored it
on a hard drive, using a personal computer and software such
as Microsoft Access, or Excel.

• Information: Processed data.

CSIT132 AIIT, Amity Ranchi

 Database

• Database is a large collection of related data

Topic 1 that can be stored generally describes
Introduction to
Database Systems activities of an organization.

CSIT132 AIIT, Amity Ranchi

 Database Properties
• It is used to store data of an organization.
• A database is designed and developed for a specific purpose.
Topic 1 • It has some source from which data is derived and it is
populated with that data.
Introduction to
• It can be of any size
Database Systems
• It allows multiple users to share and access database at the
same time.
Eg: - University database which includes students, faculty,
courses & class rooms information along with activities such
as enrolment and teaching courses.

CSIT132 AIIT, Amity Ranchi

 Database Management
System
• A database management system (DBMS) is a set of programs
Topic 1 (software) for defining, creating, manipulating and
maintaining a database.
Introduction to
Database Systems

CSIT132 AIIT, Amity Ranchi

 Database Management
System
• The DBMS is a general-purpose software package that
Topic 1 facilitates the processes of defining, constructing,
manipulating, and sharing databases among various users and
Introduction to
Database Systems applications.

CSIT132 AIIT, Amity Ranchi

 Actions of DBMS
• Defining a database involves specifying the data types,
Topic 1 structures, and constraints for the data to be stored in the
database.
Introduction to
Database Systems • Manipulating a database includes functions such as querying
the database to retrieve specific data, updating the database to
reflect changes, and generating reports from the data.

• Constructing the database is the process of storing the data

itself on some storage medium that is controlled by the
DBMS.

CSIT132 AIIT, Amity Ranchi

 Actions of DBMS
• Sharing a database allows multiple users and programs
Topic 1 to access the database concurrently

Introduction to
Database Systems

CSIT132 AIIT, Amity Ranchi

 Simplified database system
environment

Topic 1

Introduction to
Database Systems

CSIT132 AIIT, Amity Ranchi

 Database Applications
• Banking: all transactions
Topic 1 • Airlines: reservations, schedules
Introduction to • Sales: customers, products, purchases
Database Systems
• Online retailers: order tracking, customized
recommendations
• Manufacturing: production, inventory,
orders, supply chain

CSIT132 AIIT, Amity Ranchi

 File System
• Before the evolution of DBMS, File Systems are used to
Topic 2 store and manage the data.

File System • In File System data will be stored in individual files.

• A File is a collection of data.

• Files are typically designed to meet needs of a particular

department or user group.

CSIT132 AIIT, Amity Ranchi

 File processing processing
system
• In the file processing approach, each department
would "own" a collection of relevant data and software
Topic 2 applications to manipulate that data.

File System

CSIT132 AIIT, Amity Ranchi

 Drawbacks of File System
1. Data Redundancy and Inconsistency
Topic 2 2. Difficulty in accessing data

File System 3. Data isolation

4. Enforcing Integrity constraints

5. Atomicity problems

6. Difficulty in concurrency control

7. Security Problems

CSIT132 AIIT, Amity Ranchi

 Cont ...
Data Redundancy and Inconsistency:

• Data redundancy means appearing same data in different

places.
Topic 2
• In file system same information is stored at different places it
File System
causes data-inconsistency problems during updates.

Difficulty in accessing data:

• In order to retrieve data from files we need to write special

application program every time.

• This is not a convenient way because every time the

requirements may change need to write a new program to
carry out each new task

CSIT132 AIIT, Amity Ranchi

 Cont ...
• Data isolation: The data is scattered in different files
and with different formats. So it is difficult to write
Topic 2
application programs to retrieve the data.
File System
• Enforcing Integrity constraints: Data integrity means,
all the data has to obey some condition. In File System
integrity constraints are set at program level.

CSIT132 AIIT, Amity Ranchi

 Cont ...
Atomicity problems:

• Atomicity is a property of a transaction it states that either

Topic 2 all actions to be performed or none.

• In file processing system incomplete transactions cannot

roll back. Due to this data will be inconsistent.
File System
Difficulty in concurrency control:

• concurrency means same file is updated by different

application programs at the same time.

• In File Processing System it is not possible to handle

concurrency because of that we can get inconsistent data

CSIT132 AIIT, Amity Ranchi

 Cont ...
Security Problems:

Topic 2 • Since the information is scattered in different files and

does not have centralized access path, so it is not
File System possible to provide security to the data so that everyone
can access the data.

CSIT132 AIIT, Amity Ranchi

 Advantages of DBMS
1. Controlling Redundancy

2. Data Consistency

Topic 3 3. Data Security

4. Providing Storage Structures for Efficient Query

Processing
Advantages of 5. Providing Backup and Recovery
DBMS
6. Providing concurrency control

7. Enforcing Integrity constraints

CSIT132 AIIT, Amity Ranchi

 Controlling Redundancy
• In non-database systems (traditional computer file
processing), same data is stored in many places.

Topic 3 • Redundancy leads to several problems [Link] effort:

same data can be entered multiple times 2. Storage space is
wasted. 3. Data inconsistency.
Advantages of
DBMS • In DBMS, all the data of an organization is integrated into a
single database.

CSIT132 AIIT, Amity Ranchi

 Data Consistency:
• By controlling the data redundancy, the data consistency
is obtained.

Topic 3 • If a data item appears only once, any update to its value
has to be performed only once and the updated value
(new value of item) is immediately available to all users.
Advantages of
DBMS • If the DBMS has reduced redundancy to a minimum
level, the database system enforces consistency.

• It means that when a data item appears more than once

in the database and is updated, the DBMS automatically
updates each occurrence of a data item in the database.

CSIT132 AIIT, Amity Ranchi

 Data Security

• Data security is the protection of the database from

unauthorized users.
Topic 3
• Only the authorized persons are allowed to access the

Advantages of database.
DBMS
• Some of the users may be allowed to access only a part
of database i.e., the data that is related to them or related
to their department.

CSIT132 AIIT, Amity Ranchi

 Data Security

• Some users may be permitted only to retrieve data,

whereas others are allowed to retrieve as well as to
update data.
Topic 3
• The database access is controlled by the DBA.
• He creates the accounts of users and gives rights to
Advantages of access the database.
DBMS • Typically, users or group of users are given usernames
protected by passwords.
• Most of the DBMSs provide the security sub-system,
which the DBA uses to create accounts of users and to
specify account restrictions.

CSIT132 AIIT, Amity Ranchi

 Providing Storage Structures for Efficient Query
Processing
• Database systems must provide capabilities to execute
queries and updates efficiently

Topic 3 • Because the database is typically stored on disk, the

DBMS must provide specialized data structures to speed

Advantages of up disk search for the desired records.

DBMS
• Auxiliary files called indexes are used for this purpose.
Indexes are typically based on tree data structures or hash
data structures.

• DBMS supports file manager to manage the allocation of

disk space for the DBMS files.

CSIT132 AIIT, Amity Ranchi

 Providing Backup and Recovery

• Most of the DBMSs provide the 'backup and recovery'

sub-systems that automatically create the backup of data
and restore data if required.
• For example, if the computer system fails in the middle
Topic 3 (or end) of an update operation of the program, the
recovery sub-system is responsible for making sure that the
database is restored to the state it was in before the
Advantages of program started executing.
DBMS

CSIT132 AIIT, Amity Ranchi

 Providing concurrency control

• DBMS will support concurrency control tools for

permitting multiple users or application programs to access
Topic 3
the database concurrently, while preserving the consistency
of database.
Advantages of
DBMS

CSIT132 AIIT, Amity Ranchi

 Enforcing Integrity constraints
• Integrity constraints can be applied to database so that
correct data can be entered in to database.

Ex: 1. minimum balance of a account is 1000

Topic 3 2. Max marks of a subject is 100

• Most database applications have certain integrity

Advantages of constraints that must hold for the data.
DBMS
• A DBMS should provide capabilities for defining and
enforcing these constraints.

• The simplest type of integrity constraint involves

specifying a data type for each data item.

CSIT132 AIIT, Amity Ranchi

 Disadvantages of DBMS
• 1. Complexity :
• 2. Size :
Topic 3 • 3. Performance:
• 4. Higher impact of a failure:
• 5. Cost of DBMS:

CSIT132 AIIT, Amity Ranchi

 Actors of DBMS

Topic 4
DBA, Designers
and End users

CSIT132 AIIT, Amity Ranchi

 Database Designers
• Database designers are responsible for identifying the data
to be stored in the database and for choosing appropriate
Topic 4
structures to represent and store this data.
• These tasks are mostly undertaken before the database is
Database Designers actually implemented and populated with It is the
responsibility of database designers to communicate with
all prospective database users in order to understand their
requirements, and to come up with a design that meets
these requirements.

CSIT132 AIIT, Amity Ranchi

 Database Designers
• In many cases, the designers are on the staff of the DBA
and may be assigned other staff responsibilities after the
Topic 4
database design is completed.
• Database designers typically interact with each potential
Database Designers group of users and develop views of the database that
meet the data and processing requirements of these
groups.

CSIT132 AIIT, Amity Ranchi

Database Designers
• To perform the tasks effectively, Database Designers follow a
systematic design process and use various techniques, tools, and
models.
Steps a Database Designer Follows & Techniques Used:
Topic 4
1. Requirements Analysis: Meeting with stakeholders, users, and
domain experts to understand what data needs to be stored, how it will
be used, and any constraints. Techniques/Tools used during this
Database Designers process: Interviews, questionnaires, Use-case analysis, Business
process modeling, Document analysis.
2. Conceptual Design: Identify entities (objects), attributes (details),
and relationships (connections). Create a high-level data model
without worrying about how data is stored. Techniques/Tools used:
Entity-Relationship (ER) modelling, UML diagrams for class and data
modeling.
3. Logical Design: Translate the conceptual model into a logical model
suited to the type of database. (e.g., relational, NoSQL).

CSIT132 AIIT, Amity Ranchi

Database Designers
• Normalize the data to reduce redundancy and improve integrity.
Techniques/Tools used: Relational schema design, Normalization
(1NF to 5NF, BCNF), Tools: MySQL Workbench, Oracle Data Modeler,
etc.
Topic 4 4. Physical Design: Decide how the data will be stored physically in
terms of files, indexing, partitioning, etc., Optimize storage and access
performance. Techniques/Tools used: Index selection, denormalization
Database Designers (if needed for performance), Storage format decisions (e.g., row-based
vs column-based), Tools: DBMS internal tools (e.g., PostgreSQL
EXPLAIN, SQL Server Profiler).
5. Creating User Views: Design specific views (virtual tables) tailored
to different users or departments, Implement access controls to
ensure users only see what they need. Techniques/Tools: SQL Views,
Role-based access control (RBAC), GRANT/REVOKE statements.
6. Validation and Testing: Ensure the design meets all requirements
through testing with sample data and simulated queries.

CSIT132 AIIT, Amity Ranchi

Database Designers
Techniques/Tools used: Sample data population, Query performance
testing, Data integrity checks.
7. Documentation and Maintenance: Prepare detailed documentation
to help developers, DBAs, and future designers. Regularly update the
Topic 4 schema when requirements evolve. Tools used: Data dictionaries,
Schema Diagrams,

Database Designers

CSIT132 AIIT, Amity Ranchi

Database Administrator
A Database Administrator (DBA) is responsible for managing,
maintaining, securing, and optimizing a database system. The DBA
ensures that the database is always available, performs efficiently, and
remains secure and consistent with organizational policies.
Topic 4 Key Roles of a DBA:
1. Database Installation & Configuration: Install and configure DBMS
software
DBA 2. Database Design Support: Assist and Guide in logical & physical
design.
3. Performance Monitoring & Tuning: Monitor and optimize slow
queries or DB operations
4. Backup and Recovery: Create regular backups and restore when
needed
5. Security Management: Control access to data and monitor threats.
6. User Account Management: Create and manage user roles,
permissions

CSIT132 AIIT, Amity Ranchi

Database Administrator
Key Roles of a DBA:
7. Data Integrity & Consistency: Ensure data is accurate and reliable.
8. Replication and High Availability: Set up replicas and clustering for
Topic 4 reliability
9. Database Migration: Move data from one system to another
10. Capacity Planning: Plan for future growth in storage & performance
DBA

CSIT132 AIIT, Amity Ranchi

Application Programmer
An Application Programmer (also called a Database Application
Developer) is someone who writes software applications that interact
with a database. They do not manage the database directly like a
DBA but instead create user-facing programs (like web apps, mobile
Topic 4 apps, or desktop apps) that retrieve, insert, update, and delete data
stored in a database.
Application
Programmer Role of Application Programmer as a Database Actor:
1. Writing database-driven applications
2. Implementing business logic
3. Data access & manipulation
4. Ensuring secure and efficient access
5. Integrating DB with user interfaces
6. Testing and debugging database apps

CSIT132 AIIT, Amity Ranchi

 End Users
• End users are the people whose jobs require access to
the database for querying, updating, and generating
Topic 4
reports; the database primarily exists for their use.

• There are several categories of end users:

End Users
• Casual end users

• Naive or parametric end users

• Sophisticated end users

• Stand-alone users

CSIT132 AIIT, Amity Ranchi

 Data Models
• A data model is a collection of concepts that can be used to
describe the structure of a database.
• Structure of a database; mean the data types, relationships,
Topic 5 and constraints that should hold for the data. Most data
models also include a set of basic operations for specifying
Data Models retrievals and updates on the database.
Types of Data Models
• Hierarchical Model (Tree-like structure using parent-child
relationships)
• Network Model
• Relational Model
• Entity-Relationship Model (Mainly for conceptual design
before actual implementation).
• Object-Oriented Model (Less commonly supported by
traditional DBMS)

CSIT132 AIIT, Amity Ranchi

 Hierarchical Model
• A hierarchical data model is a data model which the data is
organized into a tree like structure.
• First commercial DBMS is based on this model.
Topic 5 • In Hierarchical model data is represented as records and the
records organized as collection of trees.
Types of Data • The relationships among the data are represented by links,
Models which can be viewed as pointers.

CSIT132 AIIT, Amity Ranchi

 Network Model
Network Model
• In Network model data is represented as records and the
Topic 5 records organized as collection of arbitrary graphs.
• The relationships among the data are represented by links,
Types of Data which can be viewed as pointers.
Models • In network model a record can have any number of parent
records.

CSIT132 AIIT, Amity Ranchi

 Relational Model
• Most commonly used model is the relational model.
• In this model data is organized as two-dimensional tables.
Topic 5 • Each table is called relation.
• The relational model uses a collection of tables to represent
Types of Data both data and the relationships among those data. Each table
has multiple columns, and each column has a unique name.
Models
• The relational model is implemented in database where a
relation is represented by a table, a tuple is represented by a
row, and an attribute is represented by a column of the table.

CSIT132 AIIT, Amity Ranchi

 Entity-Relationship Model
• Entity-Relationship model is based on the notion of real
world entities and relationship among them.
Topic 5 • ER Model is best used for the conceptual design of
database.
Types of Data • ER Model is based on:
Models • Entities and their attributes
• Relationships among entities

CSIT132 AIIT, Amity Ranchi

 Schemas, Instances, and Database State
• Database Schema refers to the overall structure of a database.
• The description of a database is called the database schema, which is
specified during database design and is not expected to change
Sub topic 5 frequently
Schema Diagram:

Schema Student

Course

Teacher

CSIT132 AIIT, Amity Ranchi

 Instance:
• The data in the database at a particular moment in time is
called instance or database state.
• The distinction between database schema and database
Sub-Topic 5 state is very important.
Database state:
• At any point in time, the database has a current state.
Instance

CSIT132 AIIT, Amity Ranchi

Topic 6 DBMS Architecture

• The DBMS architecture describes how data in the database is viewed by the users.
• It is not concerned with how the data is handled and processed by the DBMS.
• The database users are provided with an abstract view of the data by hiding certain
details of how data is physically stored.
• This enables the users to manipulate the data without worrying about where it is
located or how it is actually stored.
• In this architecture, the overall database description can be defined at three levels,
namely, internal, conceptual, and external levels and thus, named three-level DBMS
architecture.
• This architecture is proposed by ANSI/SPARC (American National Standards
Institute/Standards Planning and Requirements Committee) and hence, is also known
as ANSI/SPARC architecture.

CSIT132 AIIT, Amity Ranchi

 Levels of Abstraction or Three-Schema architecture

Topic 6

Three-Schema
architecture

3-Schema
Architecture

CSIT132 AIIT, Amity Ranchi

 Internal level or internal schema:
• The internal schema uses a physical data model and
describes the complete details of data storage and
access paths for the database.
Topic 6
• It tells us what data is stored in the database and how. At
least the following aspects are considered at this level:
Three-Schema
architecture • Storage allocation: B-trees, hashing etc.
• Access paths: specification of primary and secondary
keys, indexes and pointers and sequencing..

CSIT132 AIIT, Amity Ranchi

 Conceptual level or conceptual schema
• The conceptual level has a conceptual schema, which
describes the structure of the whole database for a
community of users.
Topic 6
• The conceptual schema hides the details of physical
storage structures and concentrates on describing
Three-Schema entities, data types, relationships, user operations, and
architecture constraints.
• Usually, a representational data model is used to
describe the conceptual schema when a database
system is implemented.

CSIT132 AIIT, Amity Ranchi

 External level or External View:
External level or External View:
• The external or view level includes a number of external
schemas or user views.
Topic 6
• The external level is the view that the individual user of the
database has.
Three-Schema
architecture • Each external schema describes the part of the database
that a particular user group is interested in and hides the rest
of the database from that user group.

CSIT132 AIIT, Amity Ranchi

 The main architecture:
Levels of Abstraction or Three-Schema advantage of three-schema
architecture is that it provides data
independence. Data independence is the
ability to change the schema at one level of
the database system without having to
change the schema at the other levels. The
database users are provided with an abstract
view of the data by hiding certain details of
how data are physically stored. This enables
the users to manipulate the data without
worrying about where they are located or
how they are actually stored.

Mapping: The process of transforming the

requests and results between various levels of
DBMS architecture is known as mapping.

CSIT132 AIIT, Amity Ranchi

 Data Independence
• Data independence can be defined as the capacity to change
the schema at one level of a database system without
having to change the schema at the next higher level.
Topic 6
• We can define two types of data independence:

Three-Schema 1. Logical data independence

architecture 2. Physical data independence

CSIT132 AIIT, Amity Ranchi

 Logical data independence:

• Logical data independence is the capacity to change the

conceptual schema without having to change external
Topic 6 schema or application programs.
• We may change the conceptual schema to expand the
Three-Schema
architecture database (by adding a record type or data item), to
change constraints, or to reduce the database (by
removing a record type or data item).
• It is harder to achieve than physical data independence.

CSIT132 AIIT, Amity Ranchi

 Physical data independence:

• Physical data independence is the capacity to change the

Topic 6 internal schema without having to change the conceptual
schema.
Three-Schema
architecture • Hence, the external schemas need not be changed as well.
• Changes to the internal schema may be needed because
some physical files had to be reorganized (storage details
like indexing, file organization, compression). for example,
by creating additional access structures-to improve the
performance of retrieval or update.

CSIT132 AIIT, Amity Ranchi

 Component modules of DBMS
CSIT132 AIIT, Amity Ranchi