Easy Notes Of Database Management System unit-5 @Computer Diploma

5.1 Introduction to Database Administration

Database administration is the process of managing and maintaining a database management system (DBMS) to ensure data integrity, security, availability, and performance.

Types of Database Users

• End Users: Use the database to access information. They interact with applications, not directly with the database.
• Application Programmers: Develop applications that interact with the database.
• Database Administrators (DBAs): The central authority responsible for the entire database system. They manage security, performance, and integrity.
• System Analysts: Work with end users to define system requirements, which are then used by application programmers.

Create and Delete Users DBAs manage user accounts to control who can access the database.

• To create a new user: CREATE USER username IDENTIFIED BY password;
• To delete a user: DROP USER username;

Assign Privileges to Users Privileges are permissions to perform specific actions on the database.

• System privileges: Allow users to perform administrative tasks (e.g., CREATE TABLE).
• Object privileges: Allow users to perform actions on specific database objects (e.g., SELECT on a table).
• To grant privileges: GRANT privilege ON object_name TO user_name;
• To revoke privileges: REVOKE privilege ON object_name FROM user_name;

5.2 Transaction: Concept, Properties, and States

A transaction is a single logical unit of work in a database. It can consist of one or more database operations that are treated as a single, indivisible sequence. A classic example is a bank transfer, which involves a debit from one account and a credit to another.

Properties of Transaction (ACID) The ACID properties guarantee data integrity and reliability.

• Atomicity: The entire transaction is either fully completed or not at all. If any part of the transaction fails, the entire transaction is rolled back to its initial state.
• Consistency: The database state remains valid before and after the transaction. A transaction moves the database from one consistent state to another.
• Isolation: Concurrent transactions do not interfere with each other. Each transaction appears to run in isolation from others.
• Durability: Once a transaction is committed, its changes are permanent and survive any subsequent system failures (e.g., power outages).

States of a Transaction A transaction progresses through several states:

• Active: The initial state where the transaction is executing.
• Partially Committed: After the final operation has been executed, but before the changes are permanently recorded.
• Committed: All changes are permanently stored in the database.
• Failed: A transaction that has failed to complete its operations.
• Aborted: The transaction is rolled back and has no effect on the database.

5.3 Database Backup

Database backup is the process of creating a copy of the database at a specific point in time to protect against data loss.

Types of Failures and Causes

• Hardware failures: Disk crashes, power supply failure.
• Software failures: OS errors, bugs in the DBMS.
• Human errors: Accidental deletion of data, incorrect updates.
• Disasters: Fire, floods, or other natural calamities.

Types of Database Backups

• Physical Backup: A copy of the actual database files on the storage media. This is a bit-for-bit copy of the data.
• Logical Backup: A backup of the data itself, typically in a text or SQL script format. This involves exporting the data from the database into a file that can be used to recreate the database.

5.4 Data Recovery

Data recovery is the process of restoring a database to a correct and consistent state after a failure. It uses logs (transaction logs or redo logs) to track changes.

Recovery Concepts

• Undo: Reversing uncommitted changes of a failed transaction.
• Redo: Reapplying committed changes that were lost due to a system crash.

Recovery Techniques

• Rollback: The undo process. It is used to reverse a transaction to a consistent state by using the information in the transaction log. It is a critical component of atomicity.
• Roll Forward: The redo process. It re-applies committed transactions from a transaction log to a restored backup, bringing the database to a more current state. It is a key part of durability.

5.5 Overview of Advanced Database Concepts

• Data Warehouse: A large, central repository of data from many different sources. It is designed for reporting and data analysis, not for transactional processing.
• Data Lakes: A vast pool of raw data where data is stored in its native format. It’s used for big data analytics.
• Data Mining: The process of discovering patterns and insights from large datasets. It uses techniques like machine learning, statistics, and AI.
• Big Data: Refers to extremely large and complex datasets that cannot be managed by traditional database systems. It is often characterized by the “Three V’s”: Volume, Velocity, and Variety.
• MongoDB: A popular NoSQL database that stores data in flexible, document-like JSON format. It is highly scalable and well-suited for big data applications.
• DynamoDB: A fully managed NoSQL key-value and document database service offered by Amazon Web Services (AWS). It is designed for high performance and scalability.

Keywords of this Topic: CREATE USER, DROP USER, GRANT, REVOKE, ACID, Atomicity, Consistency, Isolation, Durability, Transaction, Commit, Rollback, Backup, Recovery, Redo, Undo, Data Warehouse, Data Lake, Data Mining, Big Data, MongoDB, DynamoDB.