23ACS12-Data Base Management Systems

Program & Semester: [Link] & IVSEM

AcademicYear:2025 - 26

UNIT I
Module 1: INTRODUCTION
• Pre-requisites: Data Structures

• Elementary set theory, concepts of relations and

functions, propositional logic data structures (trees, graphs,
dictionaries) & File Concepts.
• Course Educational
Objective:
• Introduce database management systems and to give a
good formal foundation on the relational model of data
and usage of Relational Algebra
• Introduce the concepts of basic SQL as a
universal Database language
• Demonstrate the principles behind systematic database
design approaches by covering conceptual design,
logical design through normalization
• Provide an overview of physical design of a database
system, by discussing Database indexing techniques
and storage techniques
Course Outcomes: After successful completion of the course
the students are able to
• CO1: Understand the foundation of database
management system and various data models.
• (Understand-L2)
• CO2: Identify relational model concepts, implement
various constraints, perform SQL queries and DML
operations. (Understand-L2)
• CO3: Apply SQL queries, functions, and work with
nested queries, grouping, joins, views, and set operations.
(Apply- L3)
• CO4: Apply various normalization techniques for
efficient data handling. (Apply-L3)
• CO5: Understand Transaction management, recovery
& indexing techniques. (Understand-L2)
 UNIT-I

Database system, Characteristics (Database Vs File System),

Database Users, Advantages of Database systems, Database
applications. Brief introduction of different Data Models;
Concepts of Schema, Instance and data independence; Three
tier schema architecture for data independence; Database
system structure, environment, Centralized and Client Server
architecture for the database.
An overview of
Database
Management System

•What is data?
•Data is the known facts or figures that
can be recorded.
•It can also be defined as it is the
representation of facts ,concepts or
instruction in a formal manner, which is
suitable for understanding and
processing.
•Data can be represented in alphabets(A-Z,
a-z),in digits(0-9) and using special
characters(+,-.#,$, etc) e.g: 25, “ajit” etc.
•Raw Data or Unprocessed data
An overview of Database Management System

What is Information Information Eg: “The

Information is the can be age of Ravi
? processed defined as is 25”
data on the
which organized
decisions and
and actions classified
are based. data to
provide
meaningful
v s.
alue

What is File is a
File? collection
of related
data stored
in
secondary
memory.
An overview of Database
Management System

• What is Database?
• Collection of related data that
represents some real world
entity.
• Example: Marks of 10000 Students

• What is Database Management

System?

• A Database Management
System (DBMS) is software designed
to store, retrieve, define,
and manage data in a database.
• Database + Database Management
System=Database System
 An overview of Database Management
System
A database-management system (DBMS) is a collection of
interrelated data and a set of programs to access those data. The
collection of data, usually referred to as the database.
(or)
The DBMS is a general-purpose software system that facilitates the
processes of defining, constructing, manipulating, and sharing
databases among various users and applications.

Database has the following implicit properties:

⚫ A database represents some aspect of the real world, sometimes called
the
miniworld or the universe of discourse (UoD).
⚫ A database is a logically coherent collection of data with some
inherent Meaning.
⚫ A database is designed, built, and populated with data for a
specific purpose. It has an intended group of users and
some preconceived applications in which these users are
interested.
Database System Applications
 Banking
 Airlines
 Universities
 Credit card transactions
 Finance
 Sales
 Manufacturing
 Human resources
Evolution of Database Management
Systems
 File Management System
 Hierarchical database System
 Network Database System
 Relational Database System

Classification of Database Management

System Based on the data model
 Hierarchical database
 Network database
 Relational Database
 Object oriented database
 Object related database

Based on the users

 Single user
 Multiple users
 Based on the sites over which network is
distributed
 Centralized database system
 Parallel network database system
 Distributed database system

Database Systems versus File Systems

⚫ One way to keep the information on a computer is to
store it in operating system files. File-processing
system is supported by a conventional operating
system.
⚫ The system stores permanent records in various files, and it
needs different application programs to extract records from,
and add records to, the appropriate files.
⚫ A file management system is an abstraction to store,
retrieve, and management and update a set of files. A File
Management System keep track on the files and also manage
them
Drawbacks of using file systems
Data redundancy and inconsistency
Multiple file formats, duplication of information in different
files
Difficulty in accessing data
Need to write a new program to carry out each new task
Data isolation
multiple files and formats
Integrity problems
⚫ Integrity constraints (e.g. account balance > 0)
become part of program code.
⚫ Hard to add new constraints or change existing ones
Atomicity of updates
⚫ Failures may leave database in an inconsistent state
with partial updates carried out
E.g. transfer of funds from one account to another should
either complete or not happen at all
Concurrent access by multiple users
⚫ Concurrent accessed needed for performance
⚫ Uncontrolled concurrent accesses can lead to
inconsistencies
E.g. two people reading a balance and updating it at the
same time
Security problems
Advantages of DBMS:

• Data Independence
• Efficient data access
• Data Integrity and Security.
• Data Administration.
• Concurrent access and Crash recovery.
• Reduced application development time.
 Data Models
A database model shows the logical structure of a database,
including the relationships and constraints that determine how
data can be stored and accessed. Individual database models
are designed based on the rules and concepts of
whichever broader data model the
designers adopt. Most data models be represented by an
can accompanying database
diagram.
We have different data models
⚫ The Entity-Relationship
Model
⚫ Relational Model
⚫ Object oriented data Model
⚫ Object Relational Model
⚫ Network data Model
⚫ Hierarchical data model

The Entity-Relationship(ER) Model:

The entity-relationship (E-R) data model is based on a
perception of a real world that consists of a collection of basic
objects, called entities, and of relationships among these
objects.
⚫ An entity is a “thing” or “object” in the real world that
is distinguishable from other objects.
⚫ Entities are described in a database by a set of attributes.
A unique identifiable attribute must be assigned to each
entity.
⚫ The set of all entities of the similar type are termed as Entity set.
⚫ A relationship is an association among several entities.
Set of all relationship of the similar type are termed as
relationship set.
⚫ The overall logical structure (schema) of a database can be
expressed graphically by an E-R diagram, which is built up
from the following components:
• Rectangles: which represent entity sets
• Ellipses: which represent attributes
• Diamonds: which represent relationships among entity
sets
• Lines: which link attributes to entity sets and
entity sets to relationships
 Relational Model
⚫ The relational model uses a collection of tables to represent
both data and the relationships among those data. Each table
has multiple columns, and each column corresponds to an
attribute which has a unique name.
⚫ Together, the attributes in a relation are called a domain.
⚫ A particular attribute or combination of attributes is chosen as
a primary key that can be referred to in other tables, when it’s
called a foreign key.
⚫ Each row, also called a tuple, includes data about a specific
instance of the entity. Relational databases are typically
written in Structured Query Language (SQL). The model was
introduced by E.F. Codd in 1970.
 Object oriented model
This model defines a database as a collection of objects, or
reusable software elements, with associated features and
methods. There are several kinds of object-oriented databases:
A multimedia database incorporates media, such as images,
that could not be stored in a relational database.
A hypertext database allows any object to link to any other
object. It’s useful for organizing lots of disparate data, but it’s
not ideal for numerical analysis.
The object-oriented database model is the best known post-
relational database model, since it incorporates tables, but isn’t
limited to tables.
Object-relational model
This hybrid database model combines the simplicity of the
relational model with some of the advanced functionality of the
object-oriented database model. In essence, it allows designers
to incorporate objects into the familiar table structure.
 Three Schema Architecture
A database system is a collection of interrelated files and a set of
programs
that allow users to access and modify these files. It provides users
with an abstract view of the data. That is, the system hides certain
details of how the data are stored and maintained.
Developers hide the complexity from users through levels of
several abstraction, to simplify users’ interactions with
the system.
The data in the DBMS is described at three levels of abstraction
Physical Level (Internal Schema)

 Logical Level (Conceptual Schema)
 View Level (External Schema)
Physical Level
The lowest level of abstraction describes how the data are
stored. The physical level has an internal schema which
describes the physical storage structure of the database.
Logical Level
⚫ Logical level describes what data are stored in the database,
and what relationships exist among those data.
⚫ Logical level has a conceptual schema, which describes the
structure of the whole database for a community of users.
The conceptual schema hides the details of physical storage
structures and concentrates on describing entities, data types,
relationships, user operations, and constraints.
View level
⚫ This is the highest level in data abstraction.
⚫ This level includes number of external schemas or user views.
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.
 Database Schema and Instances
The description of a database is called the database schema,
which is specified during database design and is not expected
to change frequently.
 ⚫ A schema diagram displays only some aspects of a schema,
such as the names of record types and data items, and some
types of constraints.
⚫ The data in the database at a particular moment in time is
called a database state or Instance.
⚫ Database schema defines the variable declarations in tables
that belong to a particular database, the values of these
variables at a particular moment of time is called data
instance.
⚫ The distinction between database schema and database state
is very important
⚫ The DBMS stores the descriptions of the schema constructs
and constraints—also called the meta-data
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.
⚫ Metadata itself follows a layered architecture, so that when
we change data at one layer, it does not affect the data at
another level. This data is independent but mapped to each
other.
⚫ There are two types of Data Independence
 Logical Data Independence
 Physical Data Independence
 Logical data independence is the capacity to change the
conceptual schema without having to change external schemas
or application programs.

Physical data independence is the capacity to change the

internal schema without having to change the conceptual
schema. Hence, the external schemas need not be changed as
well.

Database Languages
Database languages are used for read, update and store data in a
database. There are several such languages that can be used for
this purpose; one of them is SQL (Structured Query Language).
A database system provides a data definition language to
specify the database schema and a to express database queries
and updates. data manipulation language.
There are three categories of DBMS Languages. Those are
1)Data Definition Language (DDL)
2)Data Manipulation Language (DML)
3)Data Control Language (DCL)
 Data Definition Language (DDL)
DDL or Data Definition Language actually consists of the SQL
commands that can be used to define the database schema. It
simply deals with descriptions of the database schema and is used
to create and modify the structure of database objects in the
database.

CREATE – is used to create the database or its objects (like table,

index, function, views, store procedure and triggers).
DROP – is used to delete objects from the database.
ALTER-is used to alter the structure of the database.
COMMENT –is used to add comments to the data
dictionary. RENAME –is used to rename an object
existing in the database
 Data Manipulation Language (DML)
The SQL commands that deals with the manipulation of data
present in the database belong to DML or Data Manipulation
Language and this includes most of the SQL statements.
Examples of DML:
INSERT – is used to insert data into a table.
UPDATE – is used to update existing data within a table.
DELETE– is used to delete records from a database table.
TRUNCATE–is used to remove all records from a table, including all
spaces allocated for the records are removed.
Data Control Language (DCL)
DCL includes commands such as GRANT and REVOKE which
mainly deal with the rights, permissions and other controls of the
database system.

Examples of DCL commands:

GRANT-gives user’s access privileges to the database.

REVOKE-withdraw user’s access privileges given by using the
GRANT command.
 Database System Structure
A database system is partitioned into modules that deal with each of the
responsibilities of the overall system. The functional components of a
database system can be broadly divided into the storage manager and the
query processor components.

Storage manager
⚫ A storage manager is a program module that provides the interface
between the low- level data stored in the database and the application
programs and queries submitted to the system.
⚫ Thus, the storage manager is responsible for storing, retrieving,
and updating data in the database. The storage manager
components include:

Authorization and integrity manager: which tests for the satisfaction of

integrity constraints and checks the authority of users to access data.
Transaction manager: which ensures that thedatabase
remains in a
consistent (correct) state despite system failures, and
that concurrent transaction executions proceed without
conflicting.
File manager: which manages the allocation of space on disk storage
and the

data structures used to represent information stored on disk.

Buffer manager: which is responsible for fetching data from disk
storage into main memory, and deciding what
data to cache in main memory. The buffer
manager is a critical part of the database system,
since it enables the database to handle data sizes
that are much larger than the size of main
memory.
The storage manager implements several data structures as
part of the physical system implementation:
• Data files: which store the database itself.
• Data dictionary: which stores metadata about the
structure of the database, in particular the
schema of the database.
• Indices: which provide fast access to data items that hold
particular values.
The Query Processor
The query processor components include
• DML compiler, which translates DML statements in a query
language into an evaluation plan consisting of low-level
instructions that the query evaluation engine understands. A
query can usually be translated into any of a number of
alternative evaluation plans that all give the same result. The
DML compiler also performs query optimization, that is, it
picks the lowest cost evaluation plan from among the
alternatives.
• DDL interpreter, which interprets DDL statements and
records the definitions in the data dictionary.
• Query evaluation engine, which executes low-level
instructions generated by the DML compiler