Unit - V Database Administration

5.1 Introduction to database administration:- Types of

database users, Create and delete users, Assign privileges to
users
Introduction to Database Administration
De nition:

Database Administration (DBA) refers to the process of managing and controlling access to
the database to ensure security, availability, and ef cient performance.

A Database Administrator (DBA) is responsible for creating users, granting privileges,

maintaining data integrity, and managing database storage.

Types of Database Users

Database users can be classi ed based on their roles and access levels:

1. Database Administrator (DBA)

◦ Full control over the database.

◦ Responsibilities include creating/deleting users, assigning privileges, backing

up data, and performance tuning.

2. Application Developer

◦ Develops applications that interact with the database.

◦ May have privileges to create tables, views, stored procedures, and test
queries.

3. End User / General User

◦ Uses the database through applications or queries.

◦ Limited access, usually only to retrieve (SELECT) or modify (INSERT,

UPDATE, DELETE) data.

4. System User / Maintenance User

◦ Used by the system for running automated jobs or maintenance tasks.

◦ Privileges are typically restricted to speci c system functions.

2. Creating and Deleting Users

1
fi
fi
fi
fi
 Database administrators can create or remove users. Here’s how it is generally done in SQL
(using Oracle/MySQL/PostgreSQL syntax):

Creating a User

-- MySQL Example

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- PostgreSQL Example

CREATE USER username WITH PASSWORD 'password';

-- Oracle Example

CREATE USER username IDENTIFIED BY password;

• 'username' → name of the user.

• 'host' → location from which user can connect (MySQL speci c).

• 'password' → password for the user.

Deleting a User
-- MySQL

DROP USER 'username'@'host';

-- PostgreSQL

DROP USER username;

-- Oracle

DROP USER username CASCADE; -- CASCADE removes all objects owned by the
user

Assigning Privileges to Users

Privileges de ne what actions a user can perform in the database. They can be system
privileges or object privileges.

Common Privileges

• SELECT – Read data from tables

2
fi
fi
 • INSERT – Add new records

• UPDATE – Modify existing records

• DELETE – Remove records

• CREATE – Create tables, indexes, or views

• DROP – Delete tables, views, or indexes

Granting Privileges

-- MySQL Example

GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';

-- PostgreSQL Example

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO username;

-- Oracle Example

GRANT CREATE SESSION, CREATE TABLE TO username;

Revoking Privileges

-- MySQL Example
REVOKE INSERT ON database_name.* FROM 'username'@'host';

-- PostgreSQL Example
REVOKE SELECT, INSERT ON ALL TABLES IN SCHEMA public FROM username;

-- Oracle Example
REVOKE CREATE TABLE FROM username;

5.2 Transaction: Concept, Properties & States of

Transaction

1. Concept of a Transaction

A transaction in a database is a sequence of one or more operations (queries) performed

as a single logical unit of work. These operations can include reading, writing, or
updating data.

3
 • A transaction must be atomic, meaning all operations should succeed together or fail
together.

• Transactions ensure data consistency, integrity, and reliability, especially in multi-

user environments.

Examples:

• Transferring money from one bank account to another (debit from one, credit to
another).

• Placing an order in an online shopping system.

2. Properties of a Transaction (ACID)

Transactions in a database system must satisfy the ACID properties:

1. Atomicity (All or Nothing)

◦ Either all operations of a transaction are executed successfully, or none are

◦ executed.

◦ Example: If transferring $100 fails midway, no money is debited or credited.

2. Consistency
◦ Transaction must transform the database from one valid state to another valid
state.

◦ Example: Total money in bank accounts before and after a transfer should
remain the same.

3. Isolation
◦ Transactions must be executed independently of other concurrent transactions.

◦ Example: Two users updating the same account balance should not interfere
with each other.

4. Durability
◦ Once a transaction is committed, the changes are permanent, even if a system
crash occurs.

3. States of a Transaction

A transaction goes through several states during its execution:

1. Active

4
 ◦ The transaction has started and is currently performing operations.

◦ Not yet completed.

2. Partially Committed

◦ The nal operation of the transaction has been executed, but changes are not
yet permanently recorded.

3. Committed

◦ All operations of the transaction have been successfully executed and changes
are permanently recorded in the database.

4. Failed / Aborted

◦ Transaction cannot proceed due to some error (e.g., constraint violation,

system crash).

◦ The database is rolled back to the state before the transaction started.

5. Terminated

◦ The transaction has ended. It may be committed (successful) or aborted

(rolled back).

5.3 Database Backup: Types of Failures, Causes of Failure,

Database backup introduction, types of database backups:
Physical & Logical

Database Backup
Database backup is a critical aspect of database administration. It ensures that data can be
recovered in case of failures, protecting against data loss and maintaining business
continuity.

1. Types of Failures

Database systems may encounter different types of failures, which necessitate backups:

1. Transaction Failure
◦ Occurs when a transaction cannot complete successfully due to an error (e.g.,
constraint violation, invalid data).

◦ Only affects the transaction in progress; the database itself remains

operational.

System Failure

5
fi
 • Occurs due to hardware or software issues, such as system crashes, power outages,
or operating system failures.

• Active transactions may be lost; the database may need recovery.

Media Failure

• Occurs when physical storage devices (disks, tapes) fail.

• Can result in permanent loss of data unless backups exist.

Human Error

• Mistakes by database users or administrators, such as accidental deletion or

modi cation of data.

2. Causes of Database Failures

Common causes of failures include:

• Hardware malfunction (disk crash, memory failure)

• Software bugs or operating system errors

• Power outages or sudden system shutdowns

• Network failures in distributed databases

• Human mistakes (wrong queries, accidental deletions)

• Virus or malicious attacks

3. Database Backup: Introduction

A database backup is a copy of the database that can be used to restore the original

data after a failure. Backups are essential for:

• Data recovery after failures

• Archiving data for audit purposes

• Ensuring business continuity

Backups can be physical or logical, depending on how data is stored.

4. Types of Database Backups

A. Physical Backup

6
fi
 • Involves copying the physical les of the database (data les, control les, logs) as
they exist on the storage media.

• Useful for full database recovery in case of hardware failure.

• Types of physical backups:

1. Full Backup – Copies all database les.

2. Incremental Backup – Copies only data changed since the last backup.

3. Differential Backup – Copies all changes since the last full backup.

Example: Copying database les to an external disk or tape.

B. Logical Backup

• Involves copying the logical contents of the database (tables, views, stored
procedures).

• Usually done using database utilities like mysqldump (MySQL) or pg_dump

(PostgreSQL).

• Easier to read, transport, and restore selectively.

Example: Exporting a table’s data to SQL scripts or CSV les.

5.4 Data Recovery – Recovery concepts , recovery

techniques- roll forward ,Rollback

Data Recovery
Data recovery in databases refers to the process of restoring the database to a correct state
after a failure, ensuring data integrity and consistency. Recovery is essential to protect
against transaction failures, system crashes, and media failures.

1. Recovery Concepts

Key concepts in database recovery:

1. Failure Types Requiring Recovery

◦ Transaction failure – an individual transaction fails; only that transaction

needs to be undone.

◦ System failure – system crashes due to hardware or software errors; active

transactions need recovery.

7
fi
fi
fi
fi
fi
fi
 ◦ Media failure – storage device crashes; may require full database restore from
backup.

2. Recovery Goals

◦ Ensure Atomicity: incomplete transactions are rolled back.

◦ Ensure Consistency: database moves from one consistent state to another.

◦ Minimize data loss and downtime.

2. Recovery Procedures
◦ Use backup data and transaction logs (redo/undo logs).

◦ Maintain commit history of transactions to determine what to redo or undo.

2. Recovery Techniques

There are two main recovery techniques used in database systems: Rollback and Roll
Forward.

A. Rollback (Undo)

• Purpose: Undo the effects of a transaction that has failed or has been aborted.

• When Used:

◦ Transaction failure

◦ User-initiated abort

• How It Works:

◦ Database uses undo/rollback logs to reverse changes made by the failed

◦ transaction.

◦ Only changes made by the speci c transaction are undone; other committed
transactions remain intact.

Example:

• Transaction T1 tries to transfer $100 from account A to account B.

• If T1 fails midway, the rollback will undo any partial changes, restoring account
balances to their previous state.

B. Roll Forward (Redo)

8
fi
 • Purpose: Reapply changes of committed transactions to recover the database after a
system or media failure.

• When Used:

◦ System crash after some transactions committed but not yet permanently
written to the database.

• How It Works:

◦ Database uses redo logs to re-execute committed transactions after the last
backup.

◦ Ensures that all committed transactions are re ected in the database.

Example:

• Database crashes after T2 commits.

• Using roll forward, the system re-applies T2’s changes from the transaction log to
recover the committed state.

3. Recovery Process Flow

1. Identify the type of failure

2. Restore backup (full or partial)

3. Apply Roll Forward to redo committed transactions

4. Apply Rollback to undo uncommitted transactions.

5.5 Overview of Advanced database concepts:- Data

Warehouse ,Data lakes , Data mining, Big data ,Mongo DB
, DynamoDB,
Overview of Advanced Database Concepts
Modern database technologies go beyond traditional relational databases to handle large,
complex, and diverse datasets. This section introduces some key concepts and systems.

1. Data Warehouse

• A Data Warehouse (DW) is a centralized repository that stores integrated,

historical, and subject-oriented datafrom multiple sources.

• Primarily used for reporting, analysis, and business intelligence (BI) rather than
transaction processing.

• Features:

9
fl
 ◦ Optimized for query and analysis rather than updates.

◦ Stores historical data over long periods.

◦ Supports OLAP (Online Analytical Processing).

Example: Sales data warehouse storing years of sales from multiple stores for trend analysis.

2. Data Lake

• A Data Lake is a storage system that holds large volumes of raw, unstructured,
semi-structured, or structured data.

• Unlike data warehouses, data lakes do not enforce schema on write (schema-on-
read).

• Features:

◦ Handles big data ef ciently.

◦ Flexible storage for JSON, XML, videos, logs, sensor data, etc.

◦ Often built on cloud storage systems like AWS S3, Azure Data Lake.

Example: Collecting IoT sensor data, social media feeds, and server logs for later analysis.

3. Data Mining

• Data Mining is the process of discovering patterns, correlations, and insights from
large datasets using statistical and computational techniques.

• Applications:

◦ Predictive modeling (e.g., customer churn prediction)

◦ Market basket analysis (e.g., Amazon’s “Customers who bought this also

◦ bought…”)

◦ Fraud detection

• Techniques include clustering, classi cation, regression, and association rules.

4. Big Data

• Big Data refers to datasets that are too large, fast, or complex for traditional
databases to handle.

• Characterized by 5 V’s:

10
fi
fi
 1. Volume – Huge amounts of data

2. Velocity – Rapid data generation and processing

3. Variety – Structured, semi-structured, unstructured data

4. Veracity – Data quality and reliability

5. Value – Insights derived from data

• Requires distributed storage and processing systems like Hadoop, Spark, or

NoSQL databases.

5. NoSQL Databases

NoSQL databases are non-relational databases designed for scalability and exibility,
often used in big data and real-time applications.

A. MongoDB

• Document-oriented NoSQL database

• Stores data in JSON-like documents (BSON).

• Features:

◦ Schema-less ( exible)

◦ High scalability

◦ Supports indexing, aggregation, and replication

• Use Cases: Content management, real-time analytics, IoT applications.

B. DynamoDB

• Key-Value and Document NoSQL database by AWS

• Fully managed, highly available, and scalable

• Features:

◦ Fast performance for read/write operations

◦ Serverless architecture

◦ Automatic replication across regions

◦ Use Cases: E-commerce platforms, gaming leaderboards, session management

11
fl
fl
12