Unit 5 - Practice Quiz

The IF...THEN...ELSE construct is a fundamental flow control statement that allows for the conditional execution of code. It checks a condition and runs a specific block of code if it's true, and an optional alternative block if it's false.

A user-defined function is a reusable block of code that performs a specific task and is required to return a value (either a single scalar value or a table). This distinguishes it from a stored procedure, which does not have to return a value.

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. They are stored on the database server and can improve performance and reduce network traffic.

A cursor is a control structure that enables traversal over the records in a database. It acts as a pointer to a specific row within a result set, allowing you to process each row individually.

A trigger is a database object that is automatically executed ('fired') when a specific event occurs on a table or view. They are often used to maintain data integrity or enforce business rules.

The EXCEPTION block is specifically designed to catch and handle runtime errors. When an error occurs in the executable section (BEGIN...END), control is transferred to the EXCEPTION block to run error-handling code.

A transaction is a sequence of operations performed as a single logical unit of work. The entire sequence must either complete successfully or be fully rolled back to maintain database consistency.

Atomicity ensures that a transaction is treated as an 'atomic' or indivisible unit. It either executes completely, or not at all. There is no partial execution.

A schedule (or history) is a sequence of operations from a set of concurrent transactions. It specifies the chronological order in which these operations are executed by the DBMS.

A serial schedule executes transactions sequentially, one at a time. This guarantees consistency but offers no concurrency. It is used as a benchmark to verify the correctness of concurrent schedules.

Concurrency control is the process of managing simultaneous access to the database by multiple users or transactions to ensure that the data remains consistent and the integrity of the data is maintained.

The ROLLBACK command is used to discard all changes made during the current transaction, restoring the database to the state it was in before the transaction began.

Durability guarantees that the results of a committed transaction are permanently stored in the system. The changes must persist even in the case of a system failure.

The COMMIT command signals the successful completion of a transaction and instructs the DBMS to make all of its modifications permanent.

By executing code directly on the server, stored procedures eliminate the need to send individual SQL statements over the network, which can significantly reduce traffic and improve performance.

Scalar functions (which return a single value) are very flexible and can be used in expressions within a SQL statement, such as in the list of columns to be selected or as part of a condition in the WHERE clause.

The FETCH statement is used to retrieve a specific row from the result set pointed to by the cursor. Typically, it is used in a loop to iterate through all the rows.

Triggers can be set to execute either immediately before the triggering event (like an INSERT) occurs or immediately after it has completed, allowing for different kinds of validation and logging.

Locking is a fundamental mechanism for concurrency control. A transaction acquires a lock on a data item before accessing it, which can prevent other transactions from performing conflicting operations.

The database log is a critical component for ensuring durability and recoverability. It records information about all transactions and their changes, allowing the system to undo failed transactions (ROLLBACK) or redo committed transactions after a crash.

This scenario requires row-level processing to compare the state of a column before and after the update. An AFTER UPDATE FOR EACH ROW trigger is ideal. Inside the trigger, the NEW and OLD pseudo-records allow for comparing the updated price value with its original value, ensuring the logging action only occurs when necessary.

To check for conflict serializability, we draw a precedence graph. The conflicting operations are:

1. R1(B) and W2(B) -> This is a read-write conflict, creating an edge T1 -> T2.
2. R2(A) and W1(A) -> This is a read-write conflict, creating an edge T2 -> T1.
   Since the precedence graph contains a cycle (T1 -> T2 -> T1), the schedule S is not conflict-serializable.

The Two-Phase Locking (2PL) protocol consists of a 'growing phase' and a 'shrinking phase'. During the growing phase, a transaction can acquire locks but cannot release any. Once the transaction releases its first lock, it enters the 'shrinking phase'. In this phase, it can only release locks and is not permitted to acquire any new ones. This rule ensures serializability.

Atomicity ensures that all operations within a transaction are completed as a single, indivisible unit. Either all operations succeed, or none of them do. In this case, by rolling back the partial work (the debit), the system upholds atomicity, preventing the database from being in an inconsistent state where money is lost.

Stored procedures are designed to execute a series of SQL statements, including DML (Data Manipulation Language) and DDL (Data Definition Language) operations. They excel at encapsulating complex business logic that modifies the database. Functions, especially those used within SELECT statements, are generally expected to be side-effect-free (not modify data) and return a single value or a table.

Cursors provide a mechanism to retrieve a result set from a query and process it one row at a time. This is necessary when complex, conditional logic needs to be applied to each individual row, which cannot be easily expressed in a single, set-based SQL statement. While a simple conditional update could be done with a WHERE clause, a cursor is chosen when the logic per row is more intricate.

In this schedule, T2 reads data item A which was written by T1 (W1(A); R2(A)).

• Recoverability: A schedule is recoverable if a transaction Tj that reads from Ti commits only after Ti commits. Here, T2 reads from T1, and T2 commits (C2) after T1 commits (C1). So, the schedule is recoverable.
• Cascadeless: A schedule is cascadeless if a transaction Tj is only allowed to read an item X after the transaction Ti that last wrote X has committed. Here, T2 reads A (R2(A)) before T1 commits (C1). This violates the condition for being cascadeless. If T1 were to abort, T2 would have to be rolled back, causing a cascade.

In many SQL procedural languages (like PL/SQL), a SELECT ... INTO statement that finds no rows raises a NO_DATA_FOUND exception. The code execution immediately jumps to the EXCEPTION block. The line v_count := v_count + 1; is never reached. The WHEN NO_DATA_FOUND handler is matched, which sets v_count to -1. The WHEN OTHERS block is not executed as a more specific handler was found.

Isolation is the property that ensures concurrent transactions do not interfere with each other's execution. It makes transactions appear as if they are running serially, one after another, even though they are executing concurrently. This prevents issues like dirty reads, non-repeatable reads, and phantom reads.

The loop iterates with counter from 1 to 5. The IF statement checks if the counter is even (counter MOD 2 = 0).

• When counter is 1, 3, 5 (odd), the condition is false.
• When counter is 2, result becomes 0 + 2 = 2.
• When counter is 4, result becomes 2 + 4 = 6.
  The loop terminates when counter becomes 6. The final value of result is 6.

User-defined functions are designed to encapsulate calculations and return values (either scalar or table-valued) that can be seamlessly integrated into SQL queries, such as in the SELECT list or WHERE clause. A stored procedure cannot be called in this manner. The primary advantage here is the function's ability to be used as part of a declarative SQL statement.

The basic timestamp-ordering protocol aims to maintain a serializable order equivalent to the timestamp order. T1 has an older timestamp than T2. When T1 tries to write Q, the protocol checks the read-timestamp of Q, which is TS(T2) = 25. Since TS(T1) < read_TS(Q) (i.e., 20 < 25), it means an older transaction (T1) is trying to change a value that has already been read by a younger transaction (T2). This violates the serial order, so T1's write is rejected, and T1 is aborted.

This condition describes conflict-serializability, which is a stricter condition than view-serializability. View-equivalence is defined by three conditions: (1) Initial Reads, (2) Reads-From relationships, and (3) Final Writes. While a conflict-serializable schedule is always view-serializable, a view-serializable schedule is not necessarily conflict-serializable (e.g., in cases involving 'blind writes').

Writing directly to stable storage (like a hard disk) for every log record is inefficient. The log buffer is an area in main memory where log records are temporarily collected. They are then written to the physical log on disk in batches, which is much more efficient. This process must still adhere to the write-ahead logging (WAL) protocol, ensuring logs are on stable storage before data is written.

A conflict occurs between two transactions if they access the same data item and at least one of the operations is a write. In this schedule, T2 writes to B (W2(B)) and T1 also writes to B (W1(B)). This is a classic write-write conflict. The term 'lost update' specifically refers to a scenario where one transaction's update is overwritten by another, which is a consequence of this unsynchronized write-write conflict.

A BEFORE UPDATE trigger executes before the actual data modification occurs. This allows it to inspect the proposed new value (NEW.quantity) and, if the validation fails (the value is negative), it can raise an error to prevent the UPDATE operation from ever happening. Using an AFTER UPDATE trigger would be less efficient as it would allow the invalid update to occur and then require another operation to undo it.

By definition, a cascadeless schedule is one where a transaction Tj is only allowed to read a data item X after the transaction Ti that last wrote to X has committed. This prevents Tj from reading 'dirty' (uncommitted) data from Ti. Therefore, if Ti aborts, Tj does not need to be rolled back, thus avoiding a cascading abort.

When breaking a deadlock, database systems use heuristics to choose a 'victim' transaction to abort. The goal is to minimize the cost of the rollback. Common factors include how long the transaction has been running, how much CPU/IO it has consumed, how many data items it has updated, and how many locks it holds. Aborting a young transaction that has done little work is typically the most efficient choice.

Without stored procedures, a client application might need to send multiple individual SQL commands to the server to complete a single business transaction. This creates significant network latency. By encapsulating these commands into a single stored procedure, the client only needs to make one network round-trip: a call to execute the procedure with its parameters. The server then executes all the SQL internally, drastically reducing network traffic.

The CASE statement is the standard SQL construct for handling if/then/else logic within a query. It allows you to define conditions and return a corresponding value when a condition is met. For example: SELECT employee_name, CASE WHEN years_of_service > 10 THEN 'Senior' WHEN years_of_service > 4 THEN 'Mid-level' ELSE 'Junior' END AS experience_level FROM employees;. Other constructs like loops and IF blocks are part of procedural extensions (like PL/SQL or T-SQL) and cannot be used directly within a standard SELECT statement in this way.

This scenario describes a valid cascading trigger. The Departments trigger fires once due to the initial update. This trigger's action is a DML statement that modifies multiple rows in the Employees table. The AFTER UPDATE trigger on Employees will then fire once for each row affected by that DML statement, correctly generating multiple audit log entries. This is a standard and supported behavior in most SQL databases. A "mutating table" error (common in Oracle) would typically occur if a row-level trigger on a table tried to query or modify the same table. Deadlock is possible in complex concurrent systems but is not the most direct or likely outcome of this specific trigger logic.

To check for conflict serializability, we build a precedence graph based on conflicting operations (R-W, W-R, W-W). The schedule contains the conflict , which implies an edge . It also contains the conflict , implying an edge . The resulting cycle means S is not conflict serializable.

To check for view serializability, we check if S is view-equivalent to any serial schedule. An equivalent schedule must satisfy three conditions: same initial reads, same updated reads, and same final writes.

1. Initial Read: reads the initial value of A.
2. Updated Reads: There are none.
3. Final Write: performs the final write on A.

Let's test the serial schedule .

• In , reads the initial A. (Matches S)
• There are no updated reads. (Matches S)
• In , performs the final write. (Matches S)
  Since S is view-equivalent to the serial schedule , it is view serializable. This is a classic example where a blind write () breaks conflict serializability but preserves view serializability.

In an MVCC system, REPEATABLE READ is typically implemented using snapshot isolation. When transaction begins, it is given a transaction ID which determines its view of the database—it can only see versions of data committed before its snapshot was taken. Because inserted and committed its new row after started, the new row is not part of 's snapshot. When re-executes its query, it reads from the exact same snapshot, so it will not see the new row. This effectively prevents phantom reads in this common MVCC implementation, even though the SQL standard for REPEATABLE READ allows them. The transaction does not fail or block; it simply operates on its consistent, isolated view of the data.

Let's analyze the properties in order of strictness:

• Strict: A schedule is strict if no data item is read or written by a transaction until the last transaction that wrote to it has committed or aborted. In S, writes to A and then commits. Only after commits does read A and later write to A. Both the read and the write by happen after has committed. Therefore, the schedule is strict.
• Cascadeless: A schedule is cascadeless if reads are only performed on data written by committed transactions (i.e., no dirty reads). Since the schedule is strict, it is by definition also cascadeless. reads A after commits, so no dirty read occurs.
• Recoverable: A schedule is recoverable if transactions commit only after all transactions from which they read have committed. Since the schedule is cascadeless, it is also recoverable.
  Because the schedule meets the conditions for being strict, it also meets the less-stringent requirements for being cascadeless and recoverable.

The security context clause EXECUTE AS CALLER (in SQL Server) or SQL SECURITY INVOKER (in standard SQL) is critical. This clause specifies that the procedure must execute using the privilege set of the user who calls it, not the user who defined or owns it. In this case, web_user is the caller. Although web_user has permission to execute the procedure itself, the statements inside the procedure are subject to web_user's permissions. Since web_user lacks UPDATE permission on the Invoices table, the UPDATE statement will fail with a permission-denied error at runtime. If the procedure had been defined with EXECUTE AS OWNER or SQL SECURITY DEFINER, the permissions of the definer would be used, and the operation would likely succeed.

KEYSET-driven cursors work by creating a static set of unique keys for all rows that match the initial query. This 'keyset' is fixed for the life of the cursor.

1. For Updates: When you fetch a row, the database uses the stored key to look up the latest data values in the base tables. Therefore, updates to non-key columns of rows within the keyset are visible to the cursor. The fetch will return the new salary.
2. For Deletes: When you attempt to fetch a row whose key is in the keyset but has been deleted from the base table, the database lookup fails. The system reports this failure, typically by setting a status variable (like @@FETCH_STATUS = -2 in T-SQL) to indicate that the row is missing.
   This behavior is a hybrid of STATIC (which wouldn't see the update) and DYNAMIC (which might also see new rows).

The key concept here is that a handled exception does not automatically abort the entire transaction. The EXCEPTION block within the inner BEGIN...END scope catches the NO_DATA_FOUND error.

1. The execution of the failing UPDATE is halted.
2. Control is transferred to the EXCEPTION block, which successfully inserts a message into error_log.
3. Since the exception is now considered 'handled', the program flow continues normally after the inner block's END; statement.
4. The UPDATE on the products table is executed.
5. The final COMMIT statement is reached, which makes all successful modifications within the transaction's scope permanent: the first accounts update, the error_log insert, and the products update.

This is a textbook deadlock scenario. We have a circular wait condition:

• holds a lock on A and is waiting for a lock on B.
• holds a lock on B and is waiting for a lock on A.
  This creates a cycle in the waits-for graph (), which is the definition of a deadlock. Neither transaction can proceed. Strict 2PL ensures serializability and recoverability if transactions complete, but it does not prevent deadlocks. Database systems that use locking must employ a deadlock management strategy. The most common is deadlock detection (periodically checking the waits-for graph for cycles) and resolution, which involves choosing a 'victim' transaction (e.g., based on age, log space used, or priority) to abort and roll back. This releases its locks, breaking the cycle and allowing the other transaction to proceed.

A network failure is a partition ('P'). The requirement that money is never created or destroyed is a strict consistency ('C') guarantee. To ensure this guarantee in the face of a partition, the system must sacrifice availability ('A'). For example, it might return an error or block the transfer operation until the partition is resolved, rather than proceeding and risking an inconsistent state. This makes it a CP (Consistency/Partition Tolerance) system. This strict transactional guarantee maps directly to the ACID properties of Atomicity (the transfer must happen completely or not at all) and Consistency (the database invariant that the total sum of money is preserved is never violated).

INSTEAD OF triggers replace the action on the view with their own code, but they do not bypass the integrity constraints (like foreign keys, checks, not null) of the underlying base tables. When the trigger code attempts to execute INSERT into the Employees table first, the database engine will immediately check the constraints on Employees. Since the new department_id does not yet exist in the Departments table, the foreign key constraint will be violated. This violation causes the INSERT statement to fail, which in turn halts the trigger and rolls back the entire operation.

For a database to build and trust an index on a computed column that uses a function, it needs a guarantee that the function will always return the same output for the same input, and that the function's underlying dependencies will not change. This guarantee is provided by creating the function with the SCHEMABINDING option. Schema binding locks the referenced objects (like the Inventory table) from being dropped or modified in ways that would affect the function's signature or behavior. Without SCHEMABINDING, the database cannot ensure the integrity of the persisted index values over time, and therefore prohibits the creation of the index. While non-determinism (option A) is a related concern, SCHEMABINDING is the direct and mandatory requirement for indexing functions that access user data.

The @@ROWCOUNT variable in T-SQL is volatile; it is reset by almost every statement, including control-of-flow statements in some cases. If the UPDATE in the TRY block fails, control jumps to the CATCH block. The INSERT into ErrorLog is executed. This INSERT statement will set @@ROWCOUNT to 1 (the number of rows inserted into the log). The code then continues to the IF @@ROWCOUNT = 0 check. Since @@ROWCOUNT is now 1, the condition is false, and the loop will not BREAK. It will continue to run, attempting the same failing UPDATE indefinitely, creating an infinite loop that fills the error log. The correct approach is to save the value of @@ROWCOUNT into a local variable immediately after the DML statement inside the TRY block.

• Conflict Serializability: We build a precedence graph. The first conflicts with and , which creates a dependency . Later, the operations of (specifically ) conflict with the second , which creates a dependency . The existence of the cycle means the schedule is not conflict serializable.
• View Serializability: We check if it is view-equivalent to any serial schedule ( or ).
  1. In S, reads the value of A written by the first operation of . Therefore, any equivalent serial schedule must have appear before .
  2. In S, the final write on data item A is performed by the second operation of . Therefore, any equivalent serial schedule must have as the last transaction.
     These two conditions are contradictory: must be before , but it must also be after . It is impossible to construct a serial schedule that is view-equivalent to S. Therefore, the schedule is not view serializable.

This schedule is unrecoverable. Here's why: writes to A. Then overwrites that value. Then aborts. When aborts, the recovery manager must roll back its changes by restoring the before-image of A (the value A had before wrote to it). However, has already overwritten A and subsequently committed. If the system restores the before-image for , it will wipe out the committed update from , thus violating the durability of . This makes a correct rollback of impossible without violating the commit of . This is a classic unrecoverable situation. Option D is incorrect because no transaction reads from the aborted transaction; the conflict is a Write-Write conflict.

We trace the operations and timestamps on data item A:

1. issues read(A): . The basic TO rule for read is check if . Since , the read is permitted. The system updates .
2. issues write(A): . The basic TO rule for write is check if and . Since and , the write is permitted. The system updates .
3. issues read(A): . The system checks if . The condition is false. This means , an 'older' transaction, is trying to read a data item that has already been overwritten by a 'younger' transaction (). To prevent producing a non-serializable schedule, the protocol requires that be aborted and rolled back.

The critical event that moves a transaction to the COMMITTED state is the successful writing of its 'commit' record to the stable log. This is the point of no return. The principle of write-ahead logging (WAL) ensures this log record is written before the commit is acknowledged. When the system restarts, the recovery process (like ARIES) will scan the log. It will find the COMMIT record for this transaction, classifying it as a 'winner'. During the REDO phase of recovery, the manager will replay the log records for this transaction, reapplying all its changes to the data on disk to ensure they are present. This guarantees the Durability property of ACID, even if the buffer cache was not flushed before the crash.

In SQL Server's default model, transactions can be nested, but they are not truly independent. The system maintains a transaction counter, @@TRANCOUNT. The first BEGIN TRANSACTION (in sp_A) starts the real transaction and increments @@TRANCOUNT from 0 to 1. The nested BEGIN TRANSACTION in sp_B simply increments it to 2. Conversely, the COMMIT TRANSACTION in sp_B only decrements @@TRANCOUNT from 2 back to 1. It does not perform a physical commit. The transaction is only physically committed to the database when the outermost COMMIT is executed (in sp_A), which decrements @@TRANCOUNT from 1 to 0. Therefore, all work done in sp_A and sp_B is part of a single atomic transaction.

Let's analyze the types of concurrency problems:

• Dirty Read: A transaction reads data written by another transaction that has not yet committed. only re-reads after has committed. No dirty read occurs.
• Non-Repeatable Read: A transaction re-reads the same set of rows and finds that some rows have been updated or deleted. This schedule does not modify or delete any existing rows that has already seen.
• Phantom Read: A transaction re-executes a query with a search condition (a WHERE clause) and finds that additional rows now satisfy the condition because another transaction has inserted them. This is exactly what happens here. 's first query reads a set of rows matching category='A'. inserts a new row that also matches this condition. When runs the exact same query again, a new 'phantom' row appears, changing the result of the aggregate function. This is the definition of a phantom read.

This is a major performance anti-pattern in many RDBMSs. The query optimizer treats a scalar UDF as a 'black box'. It cannot determine its cost or how it affects the data. This prevents the optimizer from using column statistics to estimate how many rows will be returned and, most importantly, it makes the WHERE clause predicate non-SARGable (not a Search ARGument). A non-SARGable predicate cannot be satisfied with an efficient index seek operation. Instead, the optimizer is forced to generate a plan that performs a full table (or index) scan, retrieving every single row and executing the function on each one to see if it matches. This row-by-row execution is vastly less efficient than a targeted index seek, leading to extremely poor performance on large tables.

Conflict serializability is a stricter, more conservative condition than view serializability. Every schedule that is conflict serializable is, by definition, also view serializable. This is because being conflict-equivalent to a serial schedule implies that all reads, writes, and their dependencies are ordered in the same way, which satisfies the conditions for view-equivalence. However, the reverse is not true. There are schedules that are view serializable but not conflict serializable. These schedules typically involve 'blind writes' (a write to a data item without first reading it), which can create a cycle in the conflict precedence graph (making it not conflict serializable) while still being equivalent to a serial schedule from the perspective of what each transaction reads and what the final state is. Therefore, the set of all conflict serializable schedules is a proper (i.e., non-equal) subset of the larger set of all view serializable schedules.