Database Management Systems

UNIT-I
What is a DBMS? Compare file systems vs. database systems
What is a File system?
A file system is a technique of arranging the files in a storage devices like a hard disk, pen
drive, DVD, etc.
It helps you to organizes the data and allows easy retrieval of files when they are required. A
file system enables you to handle the way of reading and writing data to the storage medium.
It is directly installed into the computer with the Operating systems such as Windows and
Linux.4
What is DBMS?
Database Management System (DBMS) is a software for storing and retrieving user’s data
while considering appropriate security measures. It consists of a group of programs that
manipulate the database. The DBMS accepts the request for data from an application and
instructs the DBMS engine to provide the specific data. In large systems, a DBMS helps users
and other third-party software to store and retrieve data.
Introduction to Database Management System
As the name suggests, the database management system consists of two parts. They are:
1. Database and 2. Management System
What is a Database?
To find out what database is, we have to start from data, which is the basic building block of
any DBMS.
Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1, ABC, 19 etc).
Record: Collection of related data items, e.g. in the above example the three data items had
no meaning. But if we organize them in the following way, then they collectively represent
meaningful information
File Systems vs. Database Systems
Aspect File System Database System (DBMS)
Data Storage Data stored in separate files Data stored in tables inside
a database
Redundancy High redundancy Reduced redundancy using
normalization
Data Access Requires custom programs Accessed using SQL
Security Basic file-level permissions Advanced access control

1
 Data Integrity Hard to maintain Enforced through
constraints
Backup & Recovery Manual Automatic support
Concurrency Limited Supports multiple users

Write about the advantages of the DBMS and its applications.

Advantages of DBMS system
DBMS offers a variety of techniques to store & retrieve data
Uniform administration procedures for data6
Application programmers never exposed to details of data representation and Storage.
A DBMS uses various powerful functions to store and retrieve data efficiently.
Offers Data Integrity and Security.
The DBMS implies integrity constraints to get a high level of protection against prohibited
access to data.
Reduced Application Development Time Consume lesser space.
Reduction of redundancy.
Data independence.

Database Management System (DBMS) and Its Applications:

A Database management system is a computerized record-keeping system. It is a repository or
a container for collection of computerized data files. The overall purpose of DBMS is to allow
he users to define, store, retrieve and update the information contained in the database on
demand. Information can be anything that is of significance to an individual or organization.

Databases touch all aspects of our lives. Some of the major areas of application are as
follows:
1. Banking
2. Airlines
3. Universities
4. Manufacturing and selling
5. Human resources

2
Explain about various database languages?
Database Languages - Detailed Explanation
A database language is a specialized language used to define, manipulate, and control data in a
database. In relational databases such as MySQL, Oracle, PostgreSQL, and SQL Server, the
most common standard is SQL (Structured Query Language), which is composed of different
sub-languages. These are generally classified into four main categories: Data Definition
Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and
Transaction Control Language (TCL).
1. Data Definition Language (DDL)
Purpose: Defines the structure of the database — tables, schemas, indexes, and constraints.
Key Characteristics:
- Deals with schema creation and modification.
- Changes are automatically committed and cannot be rolled back in most DBMSs.
Common Commands:
CREATE – Creates a new database object.
ALTER – Modifies an existing object.
DROP – Deletes an object from the database.
TRUNCATE – Removes all data from a table but keeps its structure.
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT );
ALTER TABLE Students ADD Email VARCHAR(100);
DROP TABLE Students;
TRUNCATE TABLE Students;

2. Data Manipulation Language (DML)

Purpose: Manages the data stored inside database objects.
Key Characteristics:
- Works on the actual records (rows) in tables.

3
- Changes can usually be rolled back before commit.
Common Commands:
INSERT – Adds new records.
SELECT – Retrieves data from the database.
UPDATE – Modifies existing records.
DELETE – Removes records from a table.
Example:

INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'John Doe', 20);
SELECT Name, Age FROM Students WHERE Age > 18;
UPDATE Students SET Age = 21 WHERE StudentID = 1;
DELETE FROM Students WHERE StudentID = 1;

3. Data Control Language (DCL)

Purpose: Controls access and permissions for database users.
Key Characteristics:
- Concerned with security and authorization.
- Usually handled by database administrators (DBAs).
Common Commands:
GRANT – Gives specific privileges to a user.
REVOKE – Removes privileges from a user.
Example:
GRANT SELECT, INSERT ON Students TO user1;
REVOKE INSERT ON Students FROM user1;

4. Transaction Control Language (TCL)

Purpose: Manages transactions — a group of SQL statements executed as a single unit.
Key Characteristics:
- Ensures data integrity using ACID properties (Atomicity, Consistency, Isolation, Durability).
- Works closely with DML commands.

4
Common Commands:
COMMIT – Saves all changes made in the current transaction.
ROLLBACK – Undoes changes in the current transaction.
SAVEPOINT – Sets a point within a transaction to roll back to later.
SET TRANSACTION – Defines characteristics for a transaction.

Example:
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
SET TRANSACTION READ ONLY;

Explain Data base system structure with neat diagram.

System Structure is a software that allows access to data stored in a database
and provides an easy and effective method of –
Defining the information.
Storing the information.
Manipulating the information.
Protecting the information from system crashes or data theft.
Differentiating access permissions for different users.
Please be note that the Structure of Database Management System is also referred to as Overall
System Structure or Database Architecture but it is different from the tier architecture of
Database.
The database system is divided into three components: Query Processor, Storage
Manager, and Disk
Storage.
These are explained as following below.

5
1. Query Processor :
It interprets the requests (queries) received from end user via an application program into
instructions. It also executes the user request which is received from the DML compiler.
Query Processor contains the following components –
DML Compiler –
It processes the DML statements into low level instruction (machine language), so that they
can be executed.
DDL Interpreter –
It processes the DDL statements into a set of table containing meta data (data about data).
Embedded DML Pre-compiler –
It processes DML statements embedded in an application program into procedural calls.
Query Optimizer –
It executes the instruction generated by DML Compiler.
2. Storage Manager :
Storage Manager is a program that provides an interface between the data stored in the database
and the queries received. It is also known as Database Control System. It maintains the

6
consistency and integrity of the database by applying the constraints and executes the DCL
statements. It is responsible for updating, storing, deleting, and retrieving data in the database.
It contains the following components –
Authorization Manager –
It ensures role-based access control, i.e,. checks whether the particular person is privileged to
perform the requested operation or not.
Integrity Manager –
It checks the integrity constraints when the database is modified.
Transaction Manager –
It controls concurrent access by performing the operations in a scheduled way that it receives
the transaction. Thus, it ensures that the database remains in the consistent state before and
after the execution of a transaction.
File Manager –
It manages the file space and the data structure used to represent information in the database.
Buffer Manager –
It is responsible for cache memory and the transfer of data between the secondary storage and
main memory.
3. Disk Storage: It contains the following components –
Data Files –
It stores the data.
Data Dictionary –
It contains the information about the structure of any database object. It is the repository of
information that governs the metadata.
Indices –
It provides faster retrieval of data item.

Explain 3-levels of data abstractions.

Levels of Abstraction in a DBMS
Data Abstraction is a process of hiding unwanted or irrelevant details from the end user. It
provides a different view and helps in achieving data independence which is used to enhance
the security of data. The database systems consist of complicated data structures and relations.
For users to access the data easily, these complications are kept hidden, and only the relevant
part of the database is made accessible to the users through data abstraction.12

7
Levels of abstraction for DBMS
Database systems include complex data-structures. In terms of retrieval of data, reduce
complexity in terms of usability of users and in order to make the system efficient, developers
use levels of abstraction that hide irrelevant details from the users. Levels of abstraction
simplify database design.
Mainly there are three levels of abstraction for DBMS
1. Physical or Internal Level
2. Logical or Conceptual Level
3. View or External Level
DBMS Schemas: Internal, Conceptual, External
Database systems comprise of complex data structures. Thus, to make the system efficient for
retrieval of data and reduce the complexity of the users, developers use the method of Data
Abstraction.
There are mainly three levels of data abstraction:
1. Internal Level: Actual PHYSICAL storage structure and access paths.
2. Conceptual or Logical Level: Structure and constraints for the entire database
3. External or View level: Describes various user views

Internal Level/Schema

8
The internal schema defines the physical storage structure of the database. The internal schema
is a very low-level representation of the entire database. It contains multiple occurrences of
multiple types of internal record. In the ANSI term, it is also called “stored record’.
Conceptual Schema/Level
The conceptual schema describes the Database structure of the whole database for the
community of users. This schema hides information about the physical storage structures and
focuses on describing data types, entities, relationships, etc.
This logical level comes between the user level and physical storage view. However, there is
only single conceptual view of a single database.
External Schema/Level
An external schema describes the part of the database which specific user is interested in. It
hides the unrelated details of the database from the user. There may be “n” number of external
views for each database. Each external view is defined using an external schema, which
consists of definitions of various types of external record of that specific view. An external
view is just the content of the database as it is seen by some specific particular user. For
example, a user from the sales department will see only sales related data.

Explain the Roles and responsibilities of Database administrator.

Roles & Responsibilities of a Database Administrator (DBA)
A Database Administrator (DBA) is responsible for the installation, configuration, operation,
maintenance, and security of databases.
Their main goal is to ensure availability, performance, and integrity of data.

1. Database Installation & Configuration

• Installing the DBMS software (e.g., Oracle, MySQL, PostgreSQL).
• Setting up database instances and configuring parameters like memory allocation,
storage location, and logging.
• Applying patches and updates to keep the database secure and efficient.

2. Database Design & Implementation

• Working with developers and analysts to design a logical data model and physical
storage structure.
• Creating tables, indexes, relationships, and constraints to maintain data integrity.
• Choosing appropriate data types and normalization levels.

3. Security Management
• Creating user accounts and assigning appropriate privileges.

9
 • Implementing role-based access control to restrict unauthorized access.
• Using encryption to protect sensitive data.
• Monitoring for suspicious activity and responding to security breaches.

4. Performance Monitoring & Tuning

• Monitoring database performance using tools like EXPLAIN PLAN or performance
dashboards.
• Identifying slow queries and optimizing them.
• Adjusting database parameters like cache size, indexing strategies, and memory
settings.
• Balancing workloads to prevent bottlenecks.

5. Backup & Recovery

• Designing a backup policy (full, incremental, differential).
• Ensuring backups are stored securely and tested regularly.
• Planning a disaster recovery strategy for emergencies (server crash, corruption).
• Using point-in-time recovery to restore data to a specific moment.

6. Data Integrity & Consistency

• Defining constraints (primary keys, foreign keys, check constraints).
• Using triggers and stored procedures to enforce rules.
• Regularly checking for corrupted data and fixing anomalies.

7. Database Upgrades & Migration

• Upgrading to newer database versions without data loss.
• Migrating databases to new hardware, cloud platforms, or different DBMS software.
• Ensuring minimal downtime during migration.

8. Troubleshooting & Problem Resolution

• Responding to database errors, crashes, or connectivity issues.
• Resolving deadlocks and concurrency conflicts.
• Working with application developers to debug SQL errors.

9. Capacity Planning
• Estimating future storage and performance needs.
• Scaling the database infrastructure to handle increased load.
• Avoiding resource shortages that could affect operations.

10. Documentation & Policies

10
 • Maintaining documentation of database configurations, backup procedures, and
recovery steps.
• Creating policies for data retention, archiving, and compliance.
Ensuring database operations follow legal and regulatory standards (e.g., GDPR, HIPAA).

Explain the following terms.

i. Schema
ii. Instance of the database
iii. Data independence
Schema
• Definition: The schema is the overall design or blueprint of a database.
• It describes:
o The tables and their columns
o The relationships between tables
o Data types, constraints, and indexes
• The schema does not store actual data — it defines how the data will be organized.
Example:
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Age INT );

Instance of the Database

• Definition: An instance refers to the actual data stored in the database at a particular
moment in time.
• While the schema is fixed (until altered), the instance changes frequently as data is
inserted, updated, or deleted.
• Example:
If the Students table has these records at this moment:

RollNo Name Age

1 Raj 20

2 Meera 21

Data Independence in DBMS

11
o Data independence can be explained using the three-schema architecture.
o Data independence refers characteristic of being able to modify the schema at one level of
the database system without altering the schema at the next higher level.
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

o Physical data independence can be defined as the capacity to change the internal schema
without having to change the conceptual schema.
o If we do any changes in the storage size of the database system server, then the Conceptual
structure of the database will not be affected.
o Physical data independence is used to separate conceptual levels from the internal levels.
o Physical data independence occurs at the logical interface level.

Logical Data Independence

o Logical data independence refers characteristic of being able to change the conceptual
schema without having to change the external schema.

12
o Logical data independence is used to separate the external level from the conceptual view.
o If we do any changes in the conceptual view of the data, then the user view of the data would
not be affected.
o Logical data independence occurs at the user interface level.

Write about different data models.

Data Models
A data model is a collection of high-level data description constructs that hide many low-level
storage details. A DBMS allows a user to define the data to be stored in terms of a data model.
The data models can be classified into different categories:
1. Hierarchical Model
2. Network Model
3. Entity-Relationship Model
4. Relational Model
5. Object-Based Data Model
1. Hierarchical Model

Hierarchical Model was the first DBMS model. This model organises the data in the
hierarchical tree structure.
The hierarchy starts from the root which has root data and then it expands in the form of a tree
adding child node to the parent node. This model easily represents some of the real-world
relationships like food recipes, sitemap of a website etc.
Depicts a set of one-to-many (1:M) relationships

13
Disadvantages of the hierarchical model:
easy to design at the same time it is quite complex to implement.
This model also lacks flexibility as the changes in the new tables or segments often yield
very complex system management tasks. Here, a deletion of one segment can lead to the
involuntary deletion of all segments under it.
2. 2.Network Model9

This model is an extension of the hierarchical model, the only difference is that a record can
have more than one parent. It replaces the hierarchical tree with a graph.
The network model was created to represent complex data relationships more effectively when
compared to hierarchical models, to improve database performance and standards.
Depicts both one-to-many (1:M) and many-to-many (M:N) relationships.
Disadvantages of the Network Model:
Database contains a complex array of pointers.
System complexity limits efficiency.
Structural changes require changes in all application programs.
Navigation systems yield complex implementation and management.
Keep heavy pressure on programmers due to the complex structure.
Any change like updating, deletion, insertion is very complex.

3.Entity-Relationship Model

14
An ER model is the logical representation of data as objects and relationships among them.
These objects are known as entities, and relationship is an association among these entities.
Entity-Relationship Model Components
ER diagram basically having three components:
1. Entities − It is a real-world thing which can be a person, place, or even a concept. For
Example: Department, Admin, Courses, Teachers, Students, Building, etc are some of the
entities of a School Management System.
2. Attributes − An entity which contains a real-world property called an attribute. For
Example: The entity employee has the property like employee id, salary, age, etc.
3. Relationship − Relationship tells how two attributes are related. For Example: Employee
works for a department.
An entity has a real-world property called attribute and these attributes are defined by a set of
values called domain.

Advantages of Entity-Relationship Model

The ER model is easy to build.
This model is widely used by database designers for communicating their ideas.
This model can easily convert to any other model like network model, hierarchical model
etc.
It is integrated with the dominant relational model.
Disadvantages of Entity-Relationship Model
There is no industry standard for developing an ER model.
Information might be lost or hidden in the ER model.
There is no Data Manipulation
There is limited relationship representation.

3. Relational Model

15
The relational model uses a collection of tables to represent both data and the relationships.
Tables are also known as relations. Each table has multiple columns represent as attributes,
Attributes are the properties which define a relation. Each row of the table represents as Tuple,
Tuple is one piece of information.
Terminologies used in Relation Model
Tables: relations are saved in the table format. A table has two properties rows and columns
Attribute: columns represent as attributes
Tuple: A Row represent as Tuple
Relation Schema: A relation schema represents the name of the relation with its attributes.
Degree: The total number of attributes which in the relation is called the degree of the
relation.
Cardinality: Total number of rows present in the Table.
Column: The column represents the set of values for a specific attribute.
Relation instance: The set of tuples of a relation at a particular instance of time is called as
relation instance.

5. Object-Based Data Model11

16
The complex real world problems are represented as objects with different attributes. In Object
Oriented Data Model, data and their relationships are contained in a single structure which is
referred as object. All objects have multiple relationships between them. Basically, it is
combination of Object Oriented programming and Relational Database Model.
Components of Object Oriented Data Model
1. Objects: An object is an abstraction of a real world entity or we can say it is an instance of
class.
Object encapsulates data and code into a single unit which provide data abstraction by hiding
the implementation details from the user.
2. Attribute: An attribute describes the properties of object.
3. Methods: Method represents the behavior of an object, it represents the real-world action
4. Class: A class is a collection of similar objects with shared structure i.e. attributes and
behavior.
5. Inheritance: new classes are created from the existing classes
Advantages of Object-Based Data Model
Reduced Maintenance
Real-World Modeling
Improved Reliability and Flexibility
High Code Reusability
Disadvantages of Object-Based Data Model
It is a complex navigational system.
Slow development of standards.
High system overheads.

17
 Slow transactions.

What is two tier and three tier database architecture? Explain.

DBMS Architecture
DBMS architecture is focused on the development, design, maintenance, and
implementation of computer programs that are used to store and organize information.
A database architect helps in the development and implementation of software to meet user
requirements.
Database architecture uses languages to design a particular type of software for business
organizations.

Types of DBMS Architecture

1-Tier Architecture DBMS architecture

1. In this, there is only one server and multiple clients are there.
2. Only the user interface will be there.
3. In this, we will use the file server rather than using the database server.
4. These are mainly used where there are less frequent changes in the data and there are no
multiple users accessing the data.

18
5. Basically, a one-tier architecture helps in keeping all of the elements of an application,
including the interface, middleware, and back-end data, in only one place.
For example – Let us say we want to fetch the records of employees from the database and the
database is available on the computer system, so the request to fetch employee details will be
made by the computer and data will be fetched from the database. This type of system is
referred to as a local database system.17

2-Tier Architecture DBMS architecture

1. It is a client-server architecture. Here two layers will be there the client tier and the database
tier.
2. There is direct communication, therefore, faster than 1 tier architecture.
3. In this, we use a database server.
4. In this, the database system and DBMS application are present at the server machine and the
client machine respectively. The two devices are connected with each other via a reliable
network.
5. All the clients will be communicating with the database server which is present in the
organization.
6. Whenever the client machine makes a request to access the database located at the server
using a query language like SQL, the server performs the request on the database and returns
the result to the client.
The application connection interface like JDBC and ODBC are generally used for the
interconnection between server and client.
There is no intermediate layer between the client and the server.

3-Tier Architecture DBMS architecture

19
1. In this, there will be three layers: the client tier, the business logic tier, and the database tier.
2. In this, the client application does not directly communicate with the database systems
present. But they communicate with server applications and the server application
communicates internally with the database system.
3. This architecture separates ties depending on the complexity of users and how users use the
data present in the database.
4. It is a completely web-based application.

20