REVIEW - THEORY QUESTIONS Flashcards
What is the purpose of the SAVEPOINT statement?
The SAVEPOINT statement allows you to set a point within a transaction to which you can later roll back, making it possible to undo part of a transaction without affecting the rest.
What are the ACID Properties?
Atomicity, Consistency, Isolation and Durability.
What is atomicity?
Ensures that all parts of a transaction are completed successfully, or none are. If any part fails, the entire transaction is rolled back.
What is consistency?
Guarantees that a transaction brings the database from one valid state to another, preserving database integrity.
What is isolation?
Ensures that transactions are executed independently without interference, preventing issues like dirty reads or lost updates.
What is durability?
Once a transaction is committed, its results are permanent and survive system failures.
What are the Isolation Levels in PL/SQL?
Read Uncommitted, Read Committed, Repeatable Read, Serializable.
What is Read Uncommitted?
Allows dirty reads, where transactions can see uncommitted changes from others.
What is Read Committed?
Prevents dirty reads by only allowing transactions to see committed changes.
What is Repeatable Read?
Ensures that if a transaction reads a record, it will see the same record throughout the transaction, but doesn’t prevent phantom reads.
What is Serializable?
Highest isolation level, ensuring transactions are executed as if they were serially executed, preventing dirty reads, non-repeatable reads, and phantom reads.
What is the difference between role and user privilege?
A role is a collection of privileges that can be assigned to users, simplifying the management of permissions. User privileges are individual permissions granted directly to a user.
How do you end a transaction?
A transaction is explicitly ended using the COMMIT statement (to save changes) or the ROLLBACK statement (to undo changes).
Why are roles so important for a database’s security?
Roles group multiple privileges, making it easier to manage user permissions. Assigning roles instead of individual privileges reduces administrative overhead and enhances security by ensuring users have only the necessary access.
What is the difference between object and system privileges?
Object Privileges: Specific to database objects, such as SELECT, INSERT, or DELETE on a table.
System Privileges: Broader permissions that affect the entire database, like CREATE TABLE or ALTER DATABASE.
What does consistency do in ACID?
This property ensures that a transaction moves the database from one consistent state to another, preserving data integrity by enforcing all constraints.
Why is isolation so important in ACID?
Isolation levels like Serializable ensure that transactions are executed without interference, preventing one transaction from overwriting the changes made by another.
What’s a scenario without atomicity?
In a banking application, if a transaction to transfer funds fails after debiting one account but before crediting another, the system could become inconsistent, with funds lost or duplicated.
How is durability implemented?
Durability is implemented through techniques like write-ahead logging, where changes are recorded in a log before being committed to the database. This ensures that, in case of a crash, the database can recover committed transactions by reapplying the changes from the log.
