Database Management System(313315)

Chapter-5
Database Administration
(10 Marks)
Prepared By
Ms. H C. Kunwar
G. H. Raisoni College of Engg & Management, Nagpur

RAISONI GROUP OF INSTITUTIONS

Introduction to Database Administration:

Database administration involves managing and maintaining a database to ensure its availability, integrity, and
performance. Database administrators (DBAs) are responsible for tasks such as installing, configuring,
upgrading, monitoring, and securing databases.
Types of Database Users:
Database Administrators (DBAs):
DBAs are responsible for the overall management of the database. They have the highest level of privileges and
can create users, assign privileges, back up the database, and perform other maintenance tasks.
Application Developers:
Developers write and maintain the software that interacts with the database. They often need access to tables,
views, and procedures to build and test applications.
End Users:
End users interact with the database through applications. They typically only have the necessary privileges to
access or modify data relevant to their role.
Database Analysts:
Analysts are responsible for extracting and analyzing data from the database. They often require read-only
access to a wide range of data to generate reports and conduct analyses.
Security Officers: These users are responsible for ensuring that the database is secure. They might have special
privileges to manage access controls, encryption, and auditing features but not necessarily full database
administration

RAISONI GROUP OF INSTITUTIONS

Introduction to Database Security

Creating and Deleting Users in a Database:

Creating Users: In many database systems, creating a user involves specifying a username and
password.
Syntax:
CREATE USER 'username' IDENTIFIED BY 'password';

Deleting Users: Deleting a user involves removing the user from the system. It's important to
ensure that the user no longer has any active processes or dependencies before deletion.
Syntax:
Drop USER username;

RAISONI GROUP OF INSTITUTIONS

Introduction to Database Security

What is Database Security :

Database security requirements arise
from the need to protect data:
first, from accidental loss and
corruption, &
second, from deliberate unauthorized
attempts to access or alter that data.

Database security involves protecting the

database from unauthorized access,
modification or destruction.

RAISONI GROUP OF INSTITUTIONS

Database Security Requirement

Confidentiality
A secure system ensures the confidentiality of data. This means that it allows
individuals to see only the data they are supposed to see. Confidentiality has
several aspects like privacy of communications, secure storage of sensitive data,
authenticated users and authorization of users.
Integrity
A secure system en sums that the data it contains is valid. Data integrate means
that data is protected from deletion and corruption, both while it resides within
the data-case, and while it is being transmitted over the network.
Availability
A secure system makes data available to authorized users, without delay. Denial
of service attacks are attempts to block authorized users’ ability to access and use
the system when needed.
Authorization
Authorization is the process through which system obtains information about the
authenticated user, including which database operations that user may perform
and which data objects that user may access.

RAISONI GROUP OF INSTITUTIONS

What is Transaction

• A transaction is a very small unit of a program and it

may contain several low level tasks.
• A transaction in a database system must maintain
Atomicity, Consistency, Isolation, and Durability
States of Transaction

Active – the initial state; the transaction

stays in this state while it is executing

Partially committed –after the final

statement has been executed.

Failed - after the discovery that normal

execution can no longer proceed.

Aborted – after the transaction has been

rolled back and the database restored to
its state prior to the start of the
transaction.
Two options after
it has been aborted: restart the transaction
- can be done only if no internal logical
error kill the transaction
Committed –after successful completion
RAISONI GROUP OF INSTITUTIONS
ACID Proerties of Transaction

RAISONI GROUP OF INSTITUTIONS

PL/SQL Data Types
Recovery Technique
What is Recovery : When recovering the database, it is must redo the effects of the
previous transactions. This is called Rolling Forward or simple Forward Recovery. Not all
but some active transaction that didn’t complete successfully needs to rollback, when the
disk drive crashed. Such kind of rollback is called Backward Recovery
Rollback Segments and Rolling Back (UNDO
operation)
Rollback segments record database actions that
should be undone during certain database operations.
In database recovery, rollback segments undo the
effects of uncommitted transactions previously
applied by the rolling forward phase. After the roll
forward, any changes that were not committed must
be undone. After redo log files have reapplied all
changes made to the database, then the
corresponding rollback segments are used. Rollback
segments are used to identify and undo transactions
that were never committed, yet were recorded in the
redo log and applied to the database during roll
forward. This process is called rolling back.
RAISONI GROUP OF INSTITUTIONS
Database Privileges
Database privileges:
When multiple users can access database objects, authorization can be controlled to these
objects with privileges. Every object has an owner. Privileges control if a user can modify an
object owned by another user.
Privileges are granted or revoked either by the instance administrator, a user with the
ADMIN privilege or, for privileges to a certain object, by the owner of the object.

1) System Privileges:
System privileges are privileges given to users to allow them to perform certain functions
that deal with managing the database and the server
e.g Create user, Create table, Drop table etc.

2) Object Privileges:
Object privileges are privileges given to users as rights and restrictions to change contents of
database object – where database
objects are things like tables, stored procedures, indexes, etc.
Ex. Select,insert,delete,update,execute,references etc

RAISONI GROUP OF INSTITUTIONS

Procedure of Assigning Privileges
Procedure for granting privileges:
• Grant: This command is used to give permission to user to do operations
on the other user’s object.
• Syntax: Grant<object privileges>on<objectname>to<username>[with
grant option] ;
• Example: Grant select, update on emp to user1;

Procedure for revoking privileges

Revoke: This command is used to withdraw the privileges that has been
granted to a user.
Syntax: Revoke <object privileges>on<object name>from <username> ;
Example: Revoke select, update on emp from user1;
Database Failure
Database Failure: A statement failure can be defined as the inability of the
database to execute an SQL statement

1. Transaction failure
The transaction failure occurs when it fails to execute or when it reaches a point
from where it can't go any further. If a few transaction or process is hurt, then this
is called as transaction failure.

2. System Crash
System failure can occur due to power failure or other hardware or software
failure. Example: Operating system error.
Fail-stop assumption: In the system crash, non-volatile storage is assumed not to be
corrupted.

3. Disk Failure
It occurs where hard-disk drives or storage drives used to fail frequently. It was a
common problem in the early days of technology evolution.
Disk failure occurs due to the formation of bad sectors, disk head crash, and
unreachability to the disk or any other failure, which destroy all or part of disk
storage.
Overview of Advanced database concepts
Data Warehouse : Data Warehouse is a centralized repository designed to store large volumes of structured
data from multiple sources. It is used for reporting, analysis, and decision-making. Data is usually historical
and organized for querying and generating business intelligence (BI) insights.
– Characteristics:
– Data in warehouses is typically structured and organized in relational tables.
– Data is collected from multiple transactional systems (ETL process: Extract, Transform, Load).
– Data is optimized for read-heavy queries (OLAP - Online Analytical Processing).
– It provides historical context by storing snapshots of data over time.

Data Lakes :A Data Lake is a vast storage repository that can hold large volumes of raw, unprocessed data
in its native format (structured, semi-structured, and unstructured). Unlike data warehouses, data lakes store
data in a more flexible manner, allowing for a broader range of analysis and use cases.
– Characteristics:
– Data is stored as-is without being processed or structured.
– Supports various data formats, such as text, images, videos, or logs.
– Provides flexibility for future use cases, allowing data scientists and engineers to apply various
tools and frameworks to analyze the data.
– Typically used with big data processing tools like Hadoop and Apache Spark.
Overview of Advanced database concepts
Data Mining :Data Mining is the process of analyzing large datasets to discover patterns, correlations, and trends
that can provide valuable insights. It involves using techniques from statistics, machine learning, and database
systems to uncover hidden knowledge in large volumes of data.
– Characteristics:
– Uses algorithms like clustering, classification, association, and regression.
– Focuses on finding previously unknown information in large datasets.
– Can be applied to both structured and unstructured data.

Big Data : Big Data refers to the large, complex datasets that cannot be easily managed, processed, or analyzed
using traditional database systems. It encompasses high volumes of data, high velocity and high variety (different
types and sources of data). These characteristics are often referred to as the 3 Vs: Volume, Velocity, and Variety.
• Characteristics:
• Large-scale data processing and storage.
• Data can be structured, semi-structured, or unstructured.
• Requires distributed computing technologies like Hadoop, Apache Spark, and NoSQL databases for storage and
processing.
Overview of Advanced database concepts
MongoDB : MongoDB is a NoSQL database known for its flexibility and scalability. Unlike traditional relational
databases, MongoDB stores data in document format, using JSON-like BSON (Binary JSON) documents. It is
widely used for handling unstructured or semi-structured data and supports horizontal scaling across multiple
servers.
Characteristics:
– Schema-less design, allowing for flexible data structures.
– Data is stored as collections of documents, making it easy to work with varied data formats.
– Designed for high performance and large-scale data processing.
– Supports horizontal scaling (sharding), which enables distributed storage across multiple servers.

DynamoDB : Amazon DynamoDB is a fully managed NoSQL database service provided by AWS (Amazon Web
Services). It is designed for fast, consistent performance with the ability to scale automatically to handle large
volumes of read and write traffic. DynamoDB is often used for applications requiring low-latency access to large
amounts of data.
Characteristics:
– Key-value and document-based data model.
– Serverless architecture, meaning it is fully managed with no need for capacity planning.
– Supports automatic scaling, backup, and restore.
– Optimized for low-latency performance, making it ideal for real-time applications.
THANK YOU