Database Administration MSBTE III Sem

Unit 5

 

Summer 2013

Q1. State types of database User. (2 Marks)

Ans. The main types of database users are:

1. Database Administrators (DBAs) - Manage and maintain the database system.
2. Application Programmers - Develop applications that interact with the database.
3. End Users - Use the database through applications (e.g., casual, naive, or sophisticated users).
4. Database Designers - Design the structure and schema of the database.

 

Q2. State the concept of database recovery. (4 Marks)

Ans. Database recovery refers to the process of restoring a database to a consistent and operational state after a failure or error. Failures can occur due to hardware malfunctions, software bugs, power outages, or human errors, which may lead to data loss or corruption.

Key Points:

1.      Purpose:

• To ensure data integrity and consistency.
• To minimize data loss and downtime.
• To restore operations quickly.

2.      Types of Failures:

• Transaction Failure: Issues during a specific transaction (e.g., deadlock, invalid operations).
• System Failure: Crashes affecting the database but not the disk storage (e.g., power outages).
• Media Failure: Disk corruption or hardware damage.

3.      Recovery Techniques:

• Backup and Restore: Using backups to restore the database.
• Transaction Logs: Rolling forward committed transactions and rolling back incomplete ones.
• Checkpointing: Periodically saving the database state to reduce recovery time.

4.      ACID Compliance: Database recovery ensures Atomicity and Durability from ACID properties by guaranteeing that either all operations in a transaction are completed or none are, and that changes from committed transactions persist.

Q3. With neat sketch describe Transaction states diagram. (4 

Marks)

Ans. Transaction States Diagram

A transaction in a database management system (DBMS) goes through various states during its lifecycle. Here's a diagram illustrating these states and their transitions:

Explanation of States:

1. Active State:
• Initial state of a transaction.
• Transaction is being executed.
• If successful, it moves to the Partially Committed state.
• If an error occurs, it transitions to the Failed state.
2. Partially Committed State:
• Transaction has completed its execution.
• Changes are made to the database but not yet permanently stored.
• If the commit operation is successful, it moves to the Committed state.
• If an error occurs during the commit process, it transitions to the Failed state.
3. Committed State:
• Transaction is successfully completed.
• Changes are permanently stored in the database.
• Transaction is terminated.
4. Failed State:
• Transaction encounters an error and cannot be completed.
• The system rolls back the transaction, undoing all changes made by the transaction.
• Transaction is terminated.
5. Aborted State:
• Transaction is intentionally terminated before completion.
• The system rolls back the transaction, undoing all changes made by the transaction.
• Transaction is terminated.

Transition Rules:

• A transaction can only move from the Active state to the Partially Committed state if all operations are executed successfully.
• A transaction can only move from the Partially Committed state to the Committed state if the commit operation is successful.
• A transaction can move to the Failed state from either the Active or Partially Committed state if an error occurs.
• A transaction can move to the Aborted state from the Active state if it is explicitly terminated by the system or the user.

 

Q4. Write SQL commands for Create user RAM Grant create, 

select, privileges to RAM () Remove update privilege from user 

RAM - (4 Marks)

Ans.

CREATE USER RAR IDENTIFIED BY password:

-- Create a new user named RAM with the specified privileges

CREATE USER RAM WITH PASSWORD 'your_password';

GRANT CREATE SESSION TO RAM

 -- Grant CREATE and SELECT privileges to RAM

GRANT CREATE, SELECT ON YOUR_DATABASE_NAME TO RAM;

GRANT CREATE TABLE TO RAM: GRANT SELECT ANY

TABLE TO RAM

GRANT INSERT ANY TABLE TO RAM GRANT UPDATE ANY 

TABLE TO RAM

GRANT DELETE ANY TABLE TO RAM

-- Revoke the UPDATE privilege from RAM

REVOKE UPDATE ON YOUR_DATABASE_NAME FROM RAM;

REVOKE UPDATE ANY TABLE FRON RAM

Replace the following:

• YOUR_DATABASE_NAME: Replace this with the actual name of your database.
• your_password: Replace this with a strong password for the user RAM.

Important Note:

• Password Security: Always use strong, complex passwords to protect your database and user accounts.
• Privilege Management: Be cautious when granting privileges. Grant only the necessary privileges to users to minimize the risk of unauthorized access and data manipulation.
• Regular Review: Periodically review and update user privileges to ensure they remain appropriate and secure.
• Consult Your DBMS Documentation: Refer to your specific database management system's documentation for exact syntax and additional options related to user management and privilege control.

Winter 2022

Q5. Explain ACID properties of transaction. (4 Marks)

Ans. The ACID properties ensure the reliability, consistency, and integrity of database transactions, especially in multi-user and failure-prone environments. They are:

1. Atomicity:
• Ensures a transaction is an indivisible unit. Either all its operations are performed, or none are.
• Example: In a fund transfer, money is either debited and credited completely, or no changes occur if an error happens.

2. Consistency:
• Guarantees that the database transitions from one valid state to another.
• Example: A transaction must maintain the integrity rules, such as ensuring the total balance remains consistent in a banking system.

3. Isolation:
• Ensures that concurrent transactions do not interfere with each other.
• Example: Two users updating the same account won't see each other's changes until the first transaction is completed.

4. Durability:
• Ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure.
• Example: After a successful purchase, the order details remain saved despite a server crash.

These properties collectively maintain database reliability and prevent data corruption.

Q6. Explain Database Recovery techniques in detail. (4 Marks)

Ans. Database Recovery Techniques 

Database recovery techniques restore a database to a consistent state after a failure. These techniques ensure minimal data loss and maintain database integrity.

1. Backup and Restore

• Description: Regular backups of the database are taken and used to restore the database in case of failure.
• Process:
• Restore the most recent backup.
• Apply transaction logs (if available) to recover the most recent state.
• Use Case: Disk failures or accidental deletion of data. 

2. Checkpointing

• Description: The database periodically saves a snapshot of its current state (called a checkpoint).
• Process:
• During recovery, transactions before the last checkpoint are ignored.
• Transactions after the checkpoint are replayed using logs.
• Use Case: Reduces recovery time by avoiding unnecessary redo operations.

3. Log-Based Recovery

• Description: Uses transaction logs to recover the database. Logs record every change made by transactions.
• Types:
• Redo Logging: Reapplies committed changes.
• Undo Logging: Reverts uncommitted changes.
• Use Case: System crashes or transaction failures.

4. Shadow Paging

• Description: Maintains two copies of the database – the current page table and a shadow page table.
• Process:
• Changes are made to the current page table.
• The shadow page table remains unchanged.
• If a failure occurs, the shadow page table is used for recovery.
• Use Case: Ensures quick recovery without log replay.

5. ARIES (Algorithm for Recovery and Isolation Exploiting Semantics)

• Description: A sophisticated recovery algorithm that supports undo and redo operations using logs and checkpoints.
• Process:
• Performs analysis, redo, and undo phases during recovery.
• Use Case: Modern databases with high performance requirements.
  
  

Summer 2023

Q7. Explain properties of transaction. (2 Marks)

Ans. The ACID properties of transactions ensure reliability and data integrity in SQL databases:

1. Atomicity: A transaction is treated as a single unit. All operations must complete successfully, or none are applied.
• Example: In a bank transfer, money is debited and credited fully or not at all.
2. Consistency: Ensures the database transitions from one valid state to another, maintaining rules and constraints.
• Example: Inventory and sales data remain consistent after a purchase.
3. Isolation: Ensures transactions execute independently without interference from others.
• Example: Two users updating the same record do not see each other’s changes until committed.
4. Durability: Guarantees that committed changes are permanent, even in case of system failure.
• Example: A ticket booking remains valid after a system crash.

Q8. Explain the most common types of database failures for (4 Marks)

Ans. Database failures can occur due to various reasons, and understanding the types of failures is crucial for implementing effective recovery techniques. The most common types of database failures are:

1. Transaction Failures

• Description: Occurs when a transaction cannot be completed successfully due to errors like invalid operations, constraint violations, or logical errors in SQL statements.
• Example: A transaction fails if a bank transfer attempts to withdraw more money than the available balance.

2. System Failures

• Description: Happens when the database system or server crashes due to hardware or software malfunctions (e.g., power outages, memory leaks, or deadlocks). The database might be partially updated when the failure occurs.
• Example: A server crash while processing multiple transactions can cause data inconsistencies or loss.

3. Media Failures

• Description: Refers to hardware-related failures that affect the storage devices (e.g., hard disk crashes, data corruption on storage media). These failures cause the database to become inaccessible or the data to become corrupted.
• Example: A disk failure leading to the loss of critical database files.

4. Concurrency Failures

• Description: Occurs when concurrent transactions interfere with each other, leading to issues like dirty reads, non-repeatable reads, or lost updates. This usually happens when isolation levels are not properly maintained.
• Example: Two users trying to update the same record simultaneously, causing data corruption or inconsistent results.

Winter 2023 

Q9. Define failure. Enlist types of failure. (2 Marks)

Ans. Database Failure

A database failure occurs when the database system or its components fail to operate as expected, leading to data inconsistency, inaccessibility, or corruption.

Types of Database Failures:

1. Transaction Failure: Occurs when a transaction cannot complete due to errors such as invalid operations or constraint violations.
2. System Failure: Happens when the database system crashes due to hardware or software malfunctions.
3. Media Failure: Refers to the failure of storage devices (e.g., disk crashes) that result in data loss or corruption.
4. Concurrency Failure: Occurs when concurrent transactions interfere with each other, leading to issues like data corruption or inconsistent results.

Q10. Describe ACID properties of transaction. (2 Marks)

Ans. The ACID properties ensure the integrity and reliability of transactions in a database:

1. Atomicity: Ensures that a transaction is fully completed or not executed at all. If a transaction fails, all changes are rolled back.
• Example: If money is transferred between two accounts, either both the debit and credit happen, or neither does.
2. Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining integrity constraints (e.g., primary keys, foreign keys).
• Example: A transaction that violates business rules (e.g., negative balances) will not complete.
3. Isolation: Ensures that concurrent transactions do not interfere with each other and that intermediate transaction states are not visible to others.
• Example: Two users updating the same record will not see each other’s changes until one transaction is complete.
4. Durability: Guarantees that once a transaction is committed, its changes are permanent, even in the event of a system crash.
• Example: After a successful purchase, the changes are saved permanently, even if the system crashes immediately after.

Q11. Describe database privileges. Write down the command for granting and revoking privileges on database object to users.

Ans. Database privileges are permissions that determine what operations a user or role can perform on database objects. They control access to the resources within the database, such as tables,  views, procedures, etc. Privileges can be granted or revoked by database administrators or users with appropriate privileges.

Types of Database Privileges:

1. System Privileges: These are high-level privileges that allow users to perform administrative tasks, such as creating and managing database objects or users.
• Examples: CREATE USER, DROP USER, CREATE DATABASE, ALTER USER, GRANT, REVOKE.
2. Object Privileges: These privileges control access to specific database objects (tables, views, procedures, etc.) and define what operations can be performed on those objects.
• Common Object Privileges:
• SELECT: Allows reading data from a table or view.
• INSERT: Allows inserting new data into a table.
• UPDATE: Allows modifying existing data in a table.
• DELETE: Allows deleting data from a table.
• EXECUTE: Allows executing stored procedures or functions.
• ALTER: Allows modifying the structure of a table or other database object (e.g., adding/removing columns).
• DROP: Allows deleting a table, view, or other database objects.
• REFERENCES: Allows creating foreign key constraints referencing a table.

Granting and Revoking Privileges

The GRANT and REVOKE statements are used to assign and remove privileges for database objects.

1. Granting Privileges

The GRANT command is used to assign specific privileges on database objects to a user or role. It can also grant the ability to further grant the privileges to other users using the WITH GRANT OPTION.

Syntax:

sql

Example: Granting SELECT and INSERT privileges on the employees table to the user john:

sql

Example with WITH GRANT OPTION: Allowing john to further grant SELECT and INSERT privileges to others:

sql

2. Revoking Privileges

The REVOKE command is used to remove privileges from a user. This command can be used to revoke all privileges or specific ones.

Syntax:

sql

Example: Revoking the INSERT privilege from the user john on the employees table:

sql

Example to Revoke All Privileges: Revoking all privileges on the employees table from john:

sql

Key Points:

• GRANT: Assigns privileges to a user, allowing them to perform specific actions on database objects.
• REVOKE: Removes previously granted privileges from a user, restricting their ability to perform certain actions on database objects.
• Privileges can be granted at both the object level (e.g., tables, views) and system level (e.g., creating users, altering database schema).

These privilege management commands ensure secure and controlled access to the database.

Summer 2024 

Q12. List system privileges and object privileges

Ans. System Privileges and Object Privileges (2 Marks)

System Privileges: These control administrative tasks and high-level operations in the database.

1. CREATE USER
2. DROP USER
3. ALTER USER
4. CREATE TABLE
5. DROP TABLE
6. GRANT
7. REVOKE

Object Privileges: These control access to specific database objects like tables, views, and procedures.

1. SELECT
2. INSERT
3. UPDATE
4. DELETE
5. EXECUTE
6. ALTER
7. DROP 

Q13. Write SQL command for following:

(1) Create user

(ii) Grant privileges to user

(iii) Remove privileges from user

Ans

(i) Create user

CREATE USER <username> IDENTIFIED BY <password>,

OR

CREATE USER RAJ IDENTIFIED BY RAJ123;

(ii) Grant privileges to user.

GRANT <privilege list> ON <relation name or view name>

TO<user list>

OR

(assuming table Employee for granting permissions to user 'RAJ' for select, insert, update and delete privilege).

GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE TO RAJ

(iii) Remove privileges from user.

REVOKE <privilege list> ON <relation name or view name >

FROM <user list>;

OR (assuming table Employee for revoking permissions to user 'RAJ)

REVOKE SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE FROM RAJ;