DBA Notes

Introduction to Database Administration

Database Administration means taking care of a database so that the
data stays safe, organized, and easy to use.
The person who manages the database is called a Database
Administrator (DBA).
Main Jobs of a DBA
1. Install and Set Up – Put the database software on the computer
and get it ready to use.
2. Manage Users – Add users and control who can see or change the
data.
3. Backup and Restore – Save copies of the data and bring it back if
something goes wrong.
4. Check Performance – Make sure the database works fast and
smoothly.
5. Keep Data Correct – Ensure the data is always correct and up to
date.
6. Update System – Install updates to keep the database safe and
modern.
Why Database Administration is Important
 Keeps data safe and secure
 Makes data easy to find and use
 Protects against data loss
  Helps the system run faster
 Ensures accurate and reliable data
Example
In a school, the DBA manages a database that stores student records.
They make sure the data is safe, correct, and can be opened by
teachers when needed.
Database Failure
A database failure happens when a database stops working properly or
loses data due to some problem.
It can cause the system to crash, lose information, or stop users from
accessing data.
Types of Database Failures
1. Transaction Failure
o Happens when a specific operation or transaction fails.
o Example: Power goes off while saving data.
2. System Failure
o The whole computer or database system stops working.
o Example: Operating system crash or hardware problem.
3. Media Failure
o When the storage device (like hard disk) is damaged.
o Example: Hard drive crash or file corruption.
4. Application Failure
o When the software using the database has an error.
 o Example: Bug in a program that updates the database.
5. Human Error
o When someone makes a mistake.
o Example: Deleting a table or file by accident.
How to Prevent Database Failures
 Take regular backups of data
 Use recovery techniques
 Keep the system updated
 Use security controls
 Train users to avoid mistakes
Example
If a company’s database crashes due to a power failure, the DBA can
restore the data from the backup to avoid loss.

Database Backup:
A database backup is a copy of the database that is made to protect
data from loss or damage.
If the main database fails or crashes, the backup can be used to restore
the lost data.
Types of Backups:
1. Full Backup – A complete copy of the whole database.
2. Incremental Backup – Copies only the data that changed since the
last backup.
3. Differential Backup – Copies all data changed since the last full
backup.
Importance of Backup:
 Protects data from loss
 Helps restore the database quickly
 Ensures business continuity
Example:
A school takes a daily backup of student records so data is not lost if the
system crashes.
Database Recovery
Database recovery means restoring the database to its correct state
after a failure or crash.
It uses backups and logs to fix errors and bring data back.
Types of Recovery:
1. Crash Recovery – Fixes the database after a sudden system crash.
2. Transaction Recovery – Rolls back or completes unfinished
transactions.
3. Media Recovery – Restores data lost due to hardware or storage
failure.
Importance of Recovery:
 Restores lost or damaged data
 Maintains data accuracy and integrity
 Reduces downtime after failure
Example:
If a company’s hard drive fails, the DBA uses a backup file to recover all
lost data.
Database Model
A database model is a structure or design that shows how data is
stored, organized, and connected inside a database system. It helps to
understand how data items are related to each other and how they can
be accessed. In other words, a database model provides a logical view
of data and defines the rules for storing, retrieving, and managing it.
Different types of database models are used depending on the needs of
the system or organization. Each model has its own way of representing
and managing data.
Types of Database Models
1. Hierarchical Model
The hierarchical model organizes data in a tree-like structure where
each record (called a node) has a parent–child relationship.
 Each parent can have many children, but each child has only one
parent.
 Data is linked through pointers, and to access data, you move
through the hierarchy step by step.
 This model is fast and simple when the data has a clear one-to-
many relationship.
 However, it is less flexible and difficult to modify if the structure
of data changes.
Example:
An organization database where the company is the parent,
departments are the children, and employees are sub-children.
2. Network Model
The network model is an improved version of the hierarchical model.
  In this model, a record can have multiple parent and child
relationships.
 It uses pointers and sets to show connections between data.
 It supports many-to-many relationships, making it more flexible
than the hierarchical model.
 However, it is still complex to design and understand.
Example:
In a school database, a student can enroll in many courses, and each
course can have many students — a perfect example of a network
model.
3. Relational Model
The relational model is the most widely used database model today.
 Data is stored in tables made up of rows (records) and columns
(fields).
 Each table represents one type of data (for example, “Students”
or “Courses”).
 Tables are connected using keys (primary key and foreign key).
 It uses Structured Query Language (SQL) to store, retrieve, and
manipulate data.
 The relational model is easy to understand, flexible, and supports
data integrity.
Example:
A student database with one table for student details and another for
course details. Both are connected by a student ID.
4. Object-Oriented Model
The object-oriented model stores data in the form of objects, just like
in object-oriented programming (e.g., Java, C++).
 Each object contains both data (attributes) and methods
(functions).
 It helps handle complex data like images, audio, and video more
effectively.
 This model is best for applications involving multimedia,
engineering, and scientific data.
 However, it is more complex to design and requires more storage.
Example:
In a multimedia database, a “Student” object can contain the student’s
photo, name, and functions like “view profile”.
5. Entity-Relationship (ER) Model
The ER model uses diagrams to visually represent the structure of a
database.
 It shows entities (real-world objects like Students, Teachers,
Courses) and relationships (how they are connected).
 Each entity has attributes (properties).
 The ER model is mainly used in the design phase of a database
before it is implemented.
Example:
In a school system, the ER diagram shows that a Student enrolls in a
Course and a Teacher teaches a Course.
Importance of Database Models
 They provide a clear framework for organizing data.
  Help in designing databases efficiently.
 Make it easier to understand data relationships.
 Improve data management and retrieval.
 Support consistency and data integrity.
Conclusion
Database models are the foundation of every database system. They
define how data is stored, related, and accessed. Among all models, the
relational model is the most commonly used today because it is simple,
flexible, and efficient. A good database model helps maintain
organized, reliable, and easy-to-manage data, which is essential for
every organization.
Query Processing
Query Processing is the method used by a database system to receive,
understand, and execute a user’s query to get the correct data from
the database.
A query is a request made by the user to retrieve or modify data
(usually written in SQL, such as SELECT * FROM students;).
The process of converting this query into an actual result is called query
processing.
The goal of query processing is to get the right data quickly and
efficiently while using as few computer resources (like time and
memory) as possible.
Steps in Query Processing
The process of query execution happens in several main steps:
1. Query Parsing
  In this step, the database system checks the syntax of the query
(whether it’s written correctly).
 The DBMS also verifies that all table names, column names, and
commands exist and are valid.
 If the query has any errors, it stops here and shows an error
message.
Example:
If you write SELEC * FROM students; instead of SELECT, the parser
detects a syntax error.
2. Query Optimization
 Once the query is valid, the system looks for the best and fastest
way to execute it.
 There can be many ways to get the same result, so the DBMS
chooses the most efficient execution plan.
 The optimizer uses information like table size, indexes, and
available paths to minimize processing time.
Example:
The optimizer may choose to use an index to find data faster instead of
scanning the whole table.
3. Query Execution Plan
 The database now creates an execution plan, which is a detailed
list of steps the system will follow to get the data.
 This plan includes how to read the tables, join data, and apply
filters or sorting.
Example:
If you request all students from class 10, the plan decides whether to
check every record or use an index.
4. Query Execution
 In this final step, the database runs the plan created earlier.
 It reads the required data from memory or disk, processes it, and
sends the final result to the user.
 This result could be a table of data, a report, or even a message
confirming data changes.
Example:
Running the query SELECT name FROM students WHERE class = 10; will
display all student names from class 10.
Components of Query Processing
1. Query Parser – Checks if the query is correct.
2. Query Optimizer – Finds the best way to execute it.
3. Query Executor – Actually runs the query and gives the result.
Importance of Query Processing
 Makes data retrieval fast and efficient
 Ensures accurate results
 Reduces system workload
 Improves performance of the database system
 Allows users to interact with large databases easily
Example
If a user wants to know all the students who scored above 80 marks,
they might write this SQL query:
SELECT name FROM students WHERE marks > 80;
The DBMS will:
1. Check the query for errors (Parsing)
2. Decide the best way to get results (Optimization)
3. Execute the plan and show the list of students (Execution)
Conclusion
Query processing is an important part of database management. It
helps convert user requests into actual actions performed by the
system to retrieve or update data. A well-optimized query processing
system makes the database faster, more reliable, and efficient, which
is essential for handling large amounts of data in organizations.
Query Processing:
Query Processing means how the database understands and answers
a user’s question (called a query).
When we type a query in SQL (like SELECT * FROM students;), the
database must read it, understand it, and then show the correct result.
So, query processing is the step-by-step process of taking a query,
checking it, finding the best way to run it, and showing the output.
Main Steps of Query Processing
1. Parsing (Understanding the Query)
 The database first checks if the query is written correctly.
 It looks for spelling or syntax mistakes.
 If something is wrong, it gives an error message.
Example:
If you type SELEC * FROM students; (missing the “T”), it shows an error
— because the command is wrong.
But if you type SELECT * FROM students;, it understands it correctly.
2. Optimization (Finding the Best Way)
 The database looks for the quickest and most efficient way to get
the result.
 It checks whether to use an index, sort, or scan through the table.
 The goal is to save time and memory.
Example:
If you search for one student by ID number, the database uses an index
to find that record quickly instead of checking every row.
3. Execution (Getting the Result)
 The database now runs the plan and fetches the data.
 It reads from the storage, processes it, and shows the final result
to the user.
Example:
When you type
SELECT name FROM students WHERE marks > 80;
The database checks each student’s marks and shows the names of
those who scored above 80.
Practical Example
Let’s say you work in a school office, and you need a list of students
who got more than 80 marks.
You type this query:
SELECT name, marks FROM students WHERE marks > 80;
Here’s what happens:
1. Parsing: The database checks that the SQL command is correct.
2. Optimization: It decides the best way to find those students
(maybe using an index).
3. Execution: It looks in the “students” table, finds the records, and
shows names and marks.
Why Query Processing is Important
 Helps get accurate results quickly
 Makes large databases run fast and smooth
 Reduces system workload
 Helps users interact easily with data
Conclusion
Query processing is like a teacher checking your question, planning
how to answer, and then giving the correct reply.
It makes sure the database understands what the user wants and gives
the result in the best and fastest way possible.
Transaction Processing:
Transaction Processing means handling a set of database operations
(tasks) that happen together as one complete unit of work.
A transaction is a small logical operation in a database — for example,
adding, updating, or deleting data.
All the steps in a transaction must be done completely, or not at all.
Example
Imagine you are transferring money from one bank account to
another:
1. Money is deducted from Account A.
2. Money is added to Account B.
Both actions must happen together — if one fails (like a system crash),
the whole transaction should undo the change.
This makes sure the database stays correct and balanced.
ACID Properties of Transactions
Transactions follow ACID properties to make sure they are reliable:
1. A – Atomicity
o All parts of a transaction must be done completely or not at
all.
o Example: If money is taken from one account but not added
to another, the system cancels the transaction.
2. C – Consistency
o The database must stay in a correct state before and after
the transaction.
o Example: After transferring money, the total amount in both
accounts remains the same.
3. I – Isolation
o Each transaction runs independently without affecting
others.
o Example: Two people transferring money at the same time
should not mix their data.
4. D – Durability
 o Once a transaction is complete, its result is saved
permanently, even if the system crashes.
o Example: Once money is transferred, it remains in the new
account even after a restart.
Types of Transaction Processing
1. Batch Processing
o Many transactions are collected and processed together at a
later time.
o Example: Payroll system processing all salaries at the end of
the month.
2. Real-Time (Online) Processing
o Transactions are processed immediately as they happen.
o Example: ATM withdrawal or online shopping payment.
Importance of Transaction Processing
 Keeps data accurate and safe
 Ensures reliability even if errors occur
 Prevents data loss or duplication
 Helps maintain business operations smoothly
 Makes databases secure and consistent
Conclusion
Transaction processing ensures that every action in a database is
handled completely, correctly, and safely.
It makes sure that if one part of a task fails, the whole process is
stopped or reversed — keeping the database reliable and error-free.
File Organization:
File Organization means the way data is stored and arranged in a file
or database so that it can be used easily and quickly.
It decides how records are placed, found, and updated on the storage
device (like a hard disk).
Good file organization helps in fast searching, easy updating, and
saving space.
Types of File Organization
1. Sequential File Organization
 In this type, records are stored one after another in a sequence.
 Usually, they are arranged in order of a key field (like roll number
or employee ID).
 To find a record, the system searches one by one until it finds the
right one.
 It is simple but slow for large files.
Example:
Student records stored by roll number: 101, 102, 103, 104, etc.
If you need record 104, the system reads from 101 to 104 in order.
2. Direct (or Hashed) File Organization
 In this type, a hashing algorithm is used to find the location of a
record directly.
 Each record has a key, and the system calculates a hash value
that points to where the record is stored.
 It is very fast for searching and updating.
Example:
A library system uses a book ID to quickly find a book’s details using a
hash formula.
3. Indexed File Organization
 In this method, an index is created — like an index in a book — to
quickly find records.
 The index stores key values and their addresses.
 It combines the benefits of both sequential and direct access.
 It is fast and efficient but takes extra space for the index.
Example:
A company stores employee data in order, but also keeps an index of
employee IDs for quick searching.
4. Clustered File Organization
 In this type, related records from different tables are stored
together.
 It improves the performance when multiple related records are
accessed frequently.
 Commonly used in databases where joins are frequent.
Example:
Customer and order details stored close together to speed up queries.
Importance of File Organization
 Makes data access faster
 Reduces storage space
 Simplifies searching and updating
  Helps in data security and management
 Improves database performance
Example (Practical View)
In a school database:
 Student records can be stored sequentially by roll number.
 The system can also create an index to find students by name
quickly.
 If hashing is used, the computer can find a student’s record
instantly using the student ID.
Conclusion
File organization is an important part of database management.
It defines how data is stored, found, and managed.
Choosing the right file organization makes data handling faster, easier,
and more efficient, especially for large databases.
Concurrency Control
Concurrency Control means managing the situation when two or more
users try to access or change the same data at the same time in a
database.
It helps make sure that the data stays accurate, correct, and consistent,
even when many people use the database together.
In short, concurrency control prevents problems that can happen when
multiple transactions run at once
Why Concurrency Control is Needed
In a multi-user database, many transactions can happen at the same
time.
Without proper control, this can cause errors or data loss.
Example:
Two students try to update their profile at the same time.
If one saves the data while the other is still editing, one update might
overwrite the other.
Concurrency control ensures both updates happen safely and correctly.
Problems Caused Without Concurrency Control
1. Lost Update Problem
o When two users update the same data, and one user’s
change is lost.
o Example: Two cashiers update the same account balance at
the same time.
2. Dirty Read Problem
o One transaction reads data that another transaction has not
yet saved (committed).
o Example: A report shows temporary or wrong data.
3. Unrepeatable Read Problem
o A transaction reads the same data twice and gets different
results.
o Example: While reading student marks twice, another user
changes the marks in between.
4. Phantom Read Problem
 o A transaction reads a set of rows, but another transaction
adds or deletes rows during that time.
o Example: While checking a list of students, another student
record is added.
Methods of Concurrency Control
1. Locking
 The most common method.
 When a transaction is using some data, it locks it so others can’t
change it until it’s done.
 After the transaction finishes, the lock is released.
Example:
When a user edits a student’s record, the system locks that record so
no one else can edit it at the same time.
Types of Locks:
 Shared Lock: Others can read but not write.
 Exclusive Lock: Only one transaction can read and write.
2. Timestamp Ordering
 Each transaction gets a timestamp (time number) when it starts.
 The database uses these timestamps to decide the order of
transactions.
 Older transactions get priority over newer ones.
3. Optimistic Concurrency Control
 It assumes that conflicts are rare.
  Transactions work without locking, but before saving, the system
checks for conflicts.
 If there’s a conflict, the transaction is rolled back (cancelled).
4. Two-Phase Locking (2PL)
 Transactions go through two phases:
1. Locking Phase: It gets all required locks.
2. Unlocking Phase: It releases all locks after completing the
task.
 Ensures serializability (correct transaction order).
Importance of Concurrency Control
 Keeps data correct and consistent
 Prevents data loss or corruption
 Ensures fair access for all users
 Improves database reliability
 Supports multi-user operations safely
Example (Simple Practical Example)
In an online shopping system, two users try to buy the last item at the
same time.
Without concurrency control, both might think they bought it —
causing confusion.
With concurrency control, the system allows only one transaction to
complete, keeping data accurate.
Conclusion
Concurrency control is a key part of database management that
ensures multiple users can work together safely.
It prevents errors and keeps data reliable and consistent, even when
many people access the same information at once.
Physical Database:
A Physical Database refers to the actual storage of data on a computer
system or storage device like a hard drive, SSD, or server.
It shows how data is physically saved, organized, and accessed by the
database management system (DBMS).
In simple words, the physical database is the real place where data is
kept in files, tables, and indexes — not just the logical design we see on
the screen.
Physical vs. Logical Database
 The logical database shows what data is stored (like tables, rows,
and columns).
 The physical database shows how and where that data is stored
on the storage device.
Example:
In a student database:
 The logical view shows a table with names, roll numbers, and
marks.
 The physical view shows how this table is stored as files and
blocks on the hard disk.
Components of a Physical Database
1. Data Files
 o These files store the actual data — like student records or
employee information.
o Example: students.dbf file may contain all student details.
2. Log Files
o Store records of all changes made to the database.
o Used for recovery if the system crashes.
3. Control Files
o Store information about the structure and status of the
database.
o Help the DBMS manage all data files properly.
4. Indexes
o Help the system find data quickly, just like an index in a
book.
5. Tablespaces or Storage Blocks
o The database divides storage into small parts to manage
data efficiently.
Functions of Physical Database Design
 Decide how data is stored (file type, format).
 Choose storage location for efficiency.
 Improve performance by reducing access time.
 Ensure data security and recovery.
 Manage space usage on the disk.
Importance of Physical Database
  Ensures fast access and performance
 Helps in data backup and recovery
 Reduces storage cost
 Improves data security
 Supports efficient database management
Example (Simple Practical Example)
In a bank database, customer details, account balances, and
transactions are stored as data files on the server.
When you check your balance, the DBMS reads that information from
the physical database stored on the disk.
Conclusion
The physical database is the real-world storage of data inside a
computer system.
It focuses on how data is physically saved and managed on disk to
ensure fast performance, safety, and reliability.
Without the physical database, the logical structure (tables, rows, and
columns) could not actually exist.