Unit5 - Subjective Questions

Flow Control Statements in database programming (like PL/SQL or T-SQL) allow developers to control the execution flow of statements based on certain conditions or repeatedly execute a block of code.

Key statements include:

• IF-THEN-ELSE: Executes a block of code if a condition is true, otherwise executes another block.
  • Syntax: IF condition THEN ... ELSIF condition THEN ... ELSE ... END IF;
• CASE Statement: Evaluates a list of conditions and returns one of multiple possible result expressions.
  • Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result_default END;
• Loops: Used for iterative execution.
  • Simple LOOP: Executes repeatedly until an EXIT statement is encountered.
  • WHILE LOOP: Executes as long as a specified condition is true. Syntax: WHILE condition LOOP ... END LOOP;
  • FOR LOOP: Executes a block of code a specified number of times. Syntax: FOR i IN 1..10 LOOP ... END LOOP;
• CONTINUE / EXIT: CONTINUE skips the current iteration, while EXIT breaks out of the loop entirely.

Stored Procedure:
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. It is stored in the database data dictionary and can accept input parameters and return multiple values.

Advantages:

• Code Reusability and Maintainability: Logic is written once and called multiple times.
• Performance: Stored procedures are pre-compiled and cached by the database engine, reducing execution time.
• Security: Can grant execution permissions without giving direct read/write access to the underlying tables, preventing SQL injection.
• Reduced Network Traffic: Instead of sending hundreds of lines of SQL code, only the procedure call is sent over the network.

General Syntax:
sql
CREATE PROCEDURE procedure_name (
param1 datatype [IN|OUT|INOUT],
param2 datatype [IN|OUT|INOUT]
)
BEGIN
-- SQL statements
END;

Differences between Functions and Stored Procedures:

• Return Value:
  • Function: Must return exactly one value (or a table).
  • Stored Procedure: Can return zero, one, or multiple values (using OUT parameters).
• Usage in SQL:
  • Function: Can be used inside SQL queries (e.g., SELECT, WHERE, HAVING clauses).
  • Stored Procedure: Cannot be used in SQL statements; invoked using CALL or EXEC.
• DML Operations:
  • Function: Generally cannot perform DML operations (INSERT, UPDATE, DELETE) on database tables (except on table variables).
  • Stored Procedure: Can freely perform INSERT, UPDATE, and DELETE operations.
• Transaction Management:
  • Function: Cannot use transaction control statements like COMMIT or ROLLBACK.
  • Stored Procedure: Can manage transactions using COMMIT and ROLLBACK.
• Exception Handling:
  • Function: Typically relies on the calling procedure for robust error handling, though simple blocks are allowed.
  • Stored Procedure: Can implement robust TRY...CATCH blocks for exception handling.

Cursor:
A cursor is a temporary work area created in the system memory when a SQL statement is executed. It holds the data retrieved by the SQL statement and allows row-by-row processing of the result set.

Lifecycle of an Explicit Cursor:

1. DECLARE: The cursor is declared in the declaration section. It defines the SQL SELECT statement to be executed.
   • Syntax: CURSOR cursor_name IS select_statement;
2. OPEN: The database executes the query, binds variables, and populates the active set. The cursor pointer points to the first row.
   • Syntax: OPEN cursor_name;
3. FETCH: Retrieves the current row from the active set into variables and advances the cursor pointer to the next row. This is usually done inside a loop.
   • Syntax: FETCH cursor_name INTO variable_list;
4. CLOSE: Releases the memory area and the resources associated with the cursor once all rows are processed.
   • Syntax: CLOSE cursor_name;

Triggers:
A trigger is a special kind of stored procedure that automatically executes (or "fires") in response to specific events on a particular table or view in a database. Events are usually DML operations (INSERT, UPDATE, DELETE).

Differences:

• Row-Level Trigger:
  • Fires once for each row affected by the triggering DML statement.
  • If an UPDATE statement updates 50 rows, the row-level trigger will execute 50 times.
  • Identified by the FOR EACH ROW clause.
  • Can access the OLD and NEW pseudo-records to see the data before and after the change.
• Statement-Level Trigger:
  • Fires only once for the triggering DML statement, regardless of how many rows are affected.
  • If an UPDATE statement updates 50 rows, the statement-level trigger will execute exactly once.
  • It is the default type of trigger if FOR EACH ROW is omitted.
  • Cannot access OLD and NEW row values because it does not operate on individual rows.

Exception Handling:
In database programming, an exception is an error condition during program execution. Exception handling is the process of intercepting and responding to these errors to prevent the program from crashing and to provide meaningful error messages.

Exceptions are generally of two types:

1. System-defined exceptions: Automatically raised by the DBMS (e.g., ZERO_DIVIDE, NO_DATA_FOUND).
2. User-defined exceptions: Defined by the programmer and raised explicitly using the RAISE statement.

Structure (PL/SQL approach):
sql
BEGIN
-- normal execution block
SELECT column INTO variable FROM table WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;

Benefits:

• Isolates error-handling routines from business logic.
• Maintains the consistency of the database by allowing ROLLBACK within the exception block.
• Improves application reliability.

Database Transaction:
A transaction is a logical unit of work that contains one or more SQL statements. A transaction must either complete entirely (commit) or have no effect at all (rollback), ensuring data consistency.

Transaction State Diagram:
A transaction passes through several states during its lifetime:

1. Active: The initial state. The transaction stays in this state while it is executing.
2. Partially Committed: After the final statement has been executed, the transaction enters this state. However, the actual database on disk might not yet reflect the changes.
3. Committed: After successful execution and all changes are permanently saved to the database. The transaction has concluded successfully.
4. Failed: If a transaction cannot proceed with its normal execution due to a hardware/logical error or cancellation, it enters the failed state.
5. Aborted: After the transaction has failed, the database is rolled back to its state prior to the start of the transaction. Once rolled back, it is in the aborted state. From here, it can either be restarted or killed.
6. Terminated: The final state. A transaction reaches this state after it is either committed or aborted.

Transactions must possess the ACID properties to ensure database reliability and integrity:

• Atomicity (All-or-Nothing):
  A transaction is an indivisible unit. Either all of its operations are executed successfully, or none are. If a failure occurs mid-transaction, all previously executed steps are rolled back.
• Consistency:
  Execution of a transaction in isolation preserves the consistency of the database. The database must transition from one valid state to another, strictly adhering to all defined rules, constraints, and triggers.
• Isolation:
  Multiple transactions running concurrently should not interfere with each other. Each transaction must feel as though it is the only transaction executing in the system. The intermediate states of a transaction are invisible to other transactions.
• Durability:
  Once a transaction commits successfully, its changes to the database must be permanent and survive any subsequent system failures (e.g., power loss, crashes). This is typically achieved through transaction logs.

Schedule:
A schedule (or history) is a sequential order in which operations (like Read and Write) of multiple concurrent transactions are executed. It maintains the chronological order of operations from the respective transactions.

Serial vs Non-Serial Schedules:

• Serial Schedule:
  • Transactions are executed one after the other.
  • Transaction only starts after has completely finished (committed or aborted).
  • Advantage: It guarantees database consistency because there is no interference.
  • Disadvantage: Very poor system utilization and throughput, as transactions must wait in a queue.
• Non-Serial Schedule:
  • Operations of multiple transactions are interleaved.
  • might do a Read, then does a Write, then does a Write.
  • Advantage: High throughput and better resource utilization (CPU and I/O happen concurrently).
  • Disadvantage: Can lead to database inconsistency if interleaved incorrectly (e.g., lost updates, dirty reads).

Conflicting Operations:
Two operations belong to different transactions, access the same data item, and at least one of them is a Write operation.
The conflicts are: Read-Write, Write-Read, and Write-Write.

Conflict Serializability:
A schedule is conflict serializable if it can be transformed into a serial schedule by swapping non-conflicting operations. It implies the concurrent schedule is as consistent as a serial one.

Testing for Conflict Serializability (Precedence Graph):

1. Create a node for every transaction in the schedule ().
2. Draw a directed edge if an operation in conflicts with an operation in and the operation in executes before the operation in .
3. Check the graph for cycles.
   • If the graph contains a cycle, the schedule is not conflict serializable.
   • If the graph contains no cycles, the schedule is conflict serializable.
   • The equivalent serial schedule can be found using a topological sort of the graph.

View Serializability:
A schedule is view serializable if it is "view equivalent" to some serial schedule. Two schedules and are view equivalent if:

1. Initial Read: If reads the initial value of data in , it must also read the initial value in .
2. Updated Read: If reads a value of written by in , must also read the value written by in .
3. Final Write: The transaction that performs the final write operation on in must also perform the final write on in .

Differences from Conflict Serializability:

• Every conflict serializable schedule is view serializable, but the reverse is not always true.
• View serializability is a broader concept that accounts for "Blind Writes" (writing data without reading it first).
• If a schedule is view serializable but not conflict serializable, it must contain blind writes.
• Testing for conflict serializability is polynomial time , whereas testing for view serializability is NP-Complete.

Need for Concurrency Control:
Concurrency control ensures that multiple transactions can execute simultaneously without violating data consistency. Without it, interleaved execution can cause unpredictable behavior.

Problems arising without Concurrency Control:

1. Lost Update Problem (Write-Write Conflict):
   Occurs when two transactions read the same data and update it. The second update overwrites the first, causing the first update to be "lost".
2. Dirty Read Problem (Write-Read Conflict):
   Occurs when a transaction reads uncommitted data written by another transaction. If the first transaction rolls back, the second transaction has read data that technically never existed.
3. Unrepeatable Read (Read-Write Conflict):
   Occurs when a transaction reads the same data twice, but gets different values because another transaction updated the data in between the two reads.
4. Phantom Read Problem:
   Occurs when a transaction executes a query returning a set of rows, and a second transaction inserts or deletes rows satisfying that condition. The first transaction gets a different set of rows if it repeats the query.

Two-Phase Locking (2PL):
2PL is a concurrency control protocol that ensures conflict serializability by requiring transactions to acquire and release locks in two distinct phases.

Phases of 2PL:

1. Growing Phase (Lock Acquisition):
   • A transaction may obtain locks on data items.
   • It may not release any locks.
   • Once the transaction releases its first lock, it moves to the shrinking phase.
2. Shrinking Phase (Lock Release):
   • A transaction may release locks.
   • It may not obtain any new locks.

Rules/Mechanism:

• The point where the transaction acquires its last lock is called the Lock Point.
• 2PL guarantees conflict serializability.
• Drawback: Basic 2PL does not guarantee freedom from Deadlocks or Cascading Rollbacks. To solve cascading rollbacks, Strict 2PL is used, where all exclusive locks are held until commit/abort.

Timestamp-based Concurrency Control:
Instead of using locks to control concurrency, this method assigns a unique, monotonically increasing Timestamp (TS) to each transaction when it starts. The protocol ensures that any conflicting read/write operations are executed in timestamp order, ensuring serializability.

Timestamp Ordering Protocol:
Each data item has two timestamp values:

• W-timestamp(Q): Largest timestamp of any transaction that executed Write(Q) successfully.
• R-timestamp(Q): Largest timestamp of any transaction that executed Read(Q) successfully.

Rules:

1. Suppose Transaction issues Read(Q):
   • If : needs to read a value that was already overwritten. Read is rejected, rolls back.
   • If : Read is executed. is updated to .
2. Suppose Transaction issues Write(Q):
   • If : The value is producing was needed previously, and it was missing. Write is rejected, rolls back.
   • If : is trying to write an obsolete value. Write is rejected, rolls back.
   • Otherwise: Write is executed, is set to .

This ensures a conflict serializable schedule without deadlocks.

Recoverable Schedule:
A schedule is recoverable if no transaction commits until all the transactions it has read from have committed.
Example: If reads a value written by , then MUST commit before commits. This ensures that if fails and rolls back, can also be rolled back, preventing from committing dirty data.

Cascading Rollback:
A cascading rollback (or cascading abort) occurs when the failure of a single transaction causes several other dependent transactions to roll back.
Example: writes , reads and writes , reads . If fails, rolls back. Since read an uncommitted value from , must roll back. Consequently, must also roll back.
Drawback: This wastes CPU time and resources.

Cascadeless Schedules:
A schedule is cascadeless (avoids cascading rollbacks) if every transaction reads only those values that were written by committed transactions.

Characteristics & Desirability:

• If wants to read a value written by , is delayed until commits or aborts.
• Desirability: They are highly desirable because they prevent the domino effect of transaction failures (Cascading Rollbacks). If a transaction fails, only that transaction needs to be rolled back, saving substantial system resources and recovery time.
• Relationship: Every cascadeless schedule is strictly a recoverable schedule, but not all recoverable schedules are cascadeless.

Deadlock:
A deadlock occurs in a concurrent database system when two or more transactions are waiting indefinitely for locks held by each other.
Example: holds a lock on and requests a lock on . holds a lock on and requests a lock on . Neither can proceed.

Handling Deadlocks:

1. Deadlock Prevention:
   • Transactions declare all required locks before execution.
   • Using Timestamp-based schemes like Wait-Die or Wound-Wait.
     • Wait-Die: Older transaction waits for younger; younger transaction requesting lock held by older dies (rolls back).
     • Wound-Wait: Older transaction wounds (forces rollback) younger; younger waits for older.
2. Deadlock Detection and Recovery:
   • The DBMS maintains a Wait-For Graph. Nodes are transactions, edges represent waiting for a lock.
   • If a cycle is detected, a deadlock exists.
   • Recovery: The system aborts a "victim" transaction to break the cycle (usually the one that minimizes rollback cost).

BEFORE and AFTER Triggers are defined by the timing of their execution relative to the triggering event (INSERT, UPDATE, DELETE).

• BEFORE Triggers:
  • Timing: Execute prior to the actual DML operation being applied to the table.
  • Use Cases: Ideal for data validation, complex business rule enforcement, and setting/modifying the NEW column values before they are written to the disk.
  • Example: Checking if the salary being inserted is greater than zero. If not, raise an error to prevent the INSERT.
• AFTER Triggers:
  • Timing: Execute following the successful completion of the DML operation on the table.
  • Use Cases: Used for auditing, logging changes, or updating other tables (cascading actions) because they guarantee that the triggering action was successfully completed.
  • Restrictions: Cannot modify the NEW values since the row has already been saved to the database.

Implicit Cursors:

• Automatically created and managed by the DBMS (like Oracle) whenever a DML statement (INSERT, UPDATE, DELETE) or a single-row SELECT INTO statement is executed.
• The programmer has no control over them, though attributes like %FOUND, %NOTFOUND, %ROWCOUNT can be checked immediately after execution.
• Faster and less code for single-row queries.

Explicit Cursors:

• Declared and managed by the programmer using a SELECT statement that returns multiple rows.
• Follows a strict lifecycle: DECLARE, OPEN, FETCH, and CLOSE.
• Allows iterating through the result set row-by-row using loops.
• Essential when a query is expected to return more than one row, as implicit cursors will raise a TOO_MANY_ROWS exception in such cases.

Strict Schedule:
A schedule is strict if a transaction can neither read nor write a data item until the transaction that previously wrote has committed or aborted.

Difference between Cascadeless and Strict:

• Cascadeless: Focuses only on Reads. cannot read until commits. However, could still write (overwriting uncommitted data, a blind write).
• Strict: Focuses on both Reads and Writes. cannot read OR write until commits.

Advantage of Strict Schedules:
Strict schedules simplify the database recovery process. If an abort occurs, the recovery subsystem simply restores the before-image of the aborted transaction. There is no need to undo cascaded effects or deal with overwritten uncommitted writes.