Back to Subject

Unit 5 - Practice Quiz

INT306 60 Questions
0 Correct 0 Wrong 60 Left
0/60

1 Which of the following is used to execute a block of statements conditionally in a database programming language?

flow control statements Easy
A. SELECT
B. IF...THEN
C. COMMIT
D. TRIGGER

2 In database programming, what is a primary characteristic of a function compared to a standard stored procedure?

functions Easy
A. It executes automatically on data modification
B. It cannot take any parameters
C. It must return a value
D. It is used exclusively for transaction management

3 What is a stored procedure in a Database Management System?

stored procedures Easy
A. A hardware component for storing data
B. A graphical user interface for querying
C. A precompiled collection of SQL statements saved in the database
D. A temporary table created during execution

4 Which database object is used to retrieve, examine, and process data row by row from a result set?

cursors Easy
A. Trigger
B. Index
C. Cursor
D. View

5 What is the correct logical sequence of operations when working with a cursor?

cursors Easy
A. DECLARE, FETCH, OPEN, CLOSE
B. DECLARE, OPEN, FETCH, CLOSE
C. FETCH, OPEN, CLOSE, DECLARE
D. OPEN, FETCH, DECLARE, CLOSE

6 Which database programming construct automatically executes in response to certain events on a particular table, such as INSERT, UPDATE, or DELETE?

triggers Easy
A. Trigger
B. Cursor
C. Sequence
D. Index

7 Which of the following are valid timings for a trigger to execute relative to the triggering event?

triggers Easy
A. START and END
B. BEFORE and AFTER
C. INSIDE and OUTSIDE
D. TOP and BOTTOM

8 What is the primary purpose of exception handling in database programming?

exception handling Easy
A. To speed up the execution of SQL queries
B. To manage and respond to runtime errors gracefully
C. To create backups of the database
D. To join multiple tables together

9 In a Database Management System, what is a transaction?

Database Transaction Processing Easy
A. A type of database user account
B. A logical unit of work that contains one or more SQL statements
C. A single row in a database table
D. A graphical tool for designing tables

10 Which SQL command is used to permanently save the changes made during a transaction?

transaction system concepts Easy
A. COMMIT
B. SAVEPOINT
C. ROLLBACK
D. UPDATE

11 Which SQL command is used to undo the changes made in the current transaction?

transaction system concepts Easy
A. COMMIT
B. REVOKE
C. DROP
D. ROLLBACK

12 The desirable properties of a database transaction are commonly referred to by which acronym?

desirable properties of transactions Easy
A. BASE
B. CRUD
C. ACID
D. SOLID

13 Which ACID property ensures that a transaction is treated as a single, indivisible unit (either all operations succeed or none do)?

desirable properties of transactions Easy
A. Consistency
B. Durability
C. Atomicity
D. Isolation

14 Which ACID property guarantees that once a transaction has been committed, it will remain in the system even in the event of a system failure?

desirable properties of transactions Easy
A. Atomicity
B. Durability
C. Consistency
D. Isolation

15 In database transaction processing, what is a schedule?

schedules Easy
A. A chronological execution sequence of operations from multiple transactions
B. A backup plan for database restoration
C. A timetable for running automated reports
D. A list of database users logging in

16 What does it mean for a schedule to be "serializable"?

serializability of schedules Easy
A. Its execution produces the same result as some strictly sequential execution of those transactions
B. It executes all transactions exactly at the same time
C. It cannot be rolled back under any circumstances
D. It only contains read operations

17 What is the main objective of concurrency control in a Database Management System?

concurrency control Easy
A. To allow multiple transactions to execute simultaneously without interfering with each other
B. To automatically write SQL queries for the user
C. To compress the database files to save disk space
D. To prevent any multiple transactions from executing at the same time

18 Which mechanism is most commonly used to implement concurrency control and prevent conflicting transactions?

concurrency control Easy
A. Views
B. Cursors
C. Locks
D. Triggers

19 A situation where two or more transactions are waiting for each other to release locks, causing them all to be stuck indefinitely, is called:

concurrency control Easy
A. Serializability
B. Deadlock
C. Atomicity
D. Starvation

20 In the context of transaction schedules, a recoverable schedule ensures that:

recoverability Easy
A. Deadlocks can never occur
B. A transaction commits only after all transactions whose changes it read have committed
C. No transaction ever needs to be rolled back
D. Every transaction executes one at a time

21 Which flow control statement is best suited to repeatedly execute a block of SQL statements as long as a specified condition evaluates to TRUE, evaluating the condition before each iteration?

flow control statements Medium
A. LOOP statement with EXIT WHEN
B. REPEAT loop
C. FOR loop
D. WHILE loop

22 In standard SQL, a User-Defined Function (UDF) is being created to calculate total salary. Which of the following is a key restriction of standard SQL scalar functions compared to stored procedures?

functions Medium
A. Functions cannot contain SELECT statements.
B. Functions cannot perform DML operations (INSERT/UPDATE/DELETE) that modify database state.
C. Functions cannot return values.
D. Functions cannot accept input parameters.

23 Consider a stored procedure that processes user orders. How does passing a parameter as IN OUT differ from passing it as OUT in most database programming languages like PL/SQL?

stored procedures Medium
A. IN OUT requires a default value, while OUT does not.
B. IN OUT parameters pass an initial value to the procedure and can return an updated value, while OUT parameters ignore any initial value and only return a value.
C. There is no difference; they are interchangeable.
D. IN OUT parameters are read-only, while OUT parameters are write-only.

24 What is a primary performance advantage of using stored procedures for complex business logic over executing multiple individual SQL queries from a client application?

stored procedures Medium
A. Stored procedures reduce network traffic by executing the logic entirely on the database server.
B. Stored procedures eliminate the need for index lookups.
C. Stored procedures do not consume CPU resources on the database server.
D. Stored procedures bypass the database concurrency control mechanisms.

25 When processing a result set using a cursor, what is the standard and correct sequence of operations?

cursors Medium
A. FETCH, DECLARE, OPEN, CLOSE, DEALLOCATE
B. OPEN, DECLARE, FETCH, DEALLOCATE, CLOSE
C. DECLARE, FETCH, OPEN, CLOSE, DEALLOCATE
D. DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE

26 An application uses a cursor to iterate through a large table. Which type of cursor is least sensitive to changes made by other concurrent transactions after the cursor is opened?

cursors Medium
A. Static cursor
B. Dynamic cursor
C. Keyset-driven cursor
D. Forward-only cursor

27 You need to ensure that an audit table is updated automatically whenever a row in the Employees table is deleted. Which type of trigger is most appropriate for this requirement?

triggers Medium
A. AFTER INSERT trigger on the audit table
B. INSTEAD OF DELETE trigger on Employees
C. AFTER DELETE trigger on Employees
D. BEFORE UPDATE trigger on Employees

28 An INSTEAD OF trigger is most commonly applied to which of the following database objects to make them updatable?

triggers Medium
A. Base tables with primary keys
B. Materialized views
C. Complex views with joins or aggregates
D. Temporary tables

29 In a database programming block (e.g., PL/SQL), what happens when an exception is raised but not explicitly caught by an exception handler in that specific block?

exception handling Medium
A. The exception propagates to the calling environment or enclosing block.
B. The database engine automatically drops the affected table.
C. The block ignores the exception and continues executing the next statement.
D. The transaction is immediately committed.

30 In transaction processing, what does a 'Commit Point' signify?

Database Transaction Processing Medium
A. The point where a transaction acquires its first lock.
B. The point at which a transaction begins execution.
C. The point where all operations of a transaction have successfully executed and their effects are permanently recorded.
D. The point where a transaction is forced to roll back due to a system failure.

31 Which transaction state indicates that a transaction has executed its final statement but has not yet committed its changes to the database?

transaction system concepts Medium
A. Partially committed
B. Failed
C. Aborted
D. Active

32 A bank transfer transaction deducts $100 from Account A and adds $100 to Account B. If the system crashes immediately after the deduction but before the addition, which ACID property ensures the deduction is undone upon recovery?

desirable properties of transactions Medium
A. Durability
B. Isolation
C. Atomicity
D. Consistency

33 Two concurrent transactions are updating the same row. Transaction 1's intermediate changes are hidden from Transaction 2 until Transaction 1 commits. Which ACID property is being demonstrated here?

desirable properties of transactions Medium
A. Isolation
B. Atomicity
C. Durability
D. Consistency

34 In a concurrent schedule, a 'blind write' occurs when a transaction:

schedules Medium
A. Reads a value and then writes it without modifying it.
B. Writes a value to a data item without previously reading it.
C. Attempts to write to a locked resource.
D. Writes a value to a log file but not the database.

35 A precedence graph (serialization graph) is drawn for a schedule of concurrent transactions. Which condition strictly indicates that the schedule is conflict serializable?

serializability of schedules Medium
A. The graph contains no cycles (is a directed acyclic graph).
B. The graph is completely disconnected.
C. The graph has identical in-degrees and out-degrees for all nodes.
D. The graph has at least one cycle.

36 Consider transactions and . If reads data item and then writes to data item , what type of conflict is this?

serializability of schedules Medium
A. Write-Write (WW) conflict
B. Read-Write (RW) conflict
C. Read-Read (RR) conflict
D. Write-Read (WR) conflict

37 Under the Basic Two-Phase Locking (2PL) protocol, which of the following actions is strictly prohibited?

concurrency control Medium
A. Acquiring a shared lock after a transaction starts.
B. Acquiring a new lock after releasing an existing lock.
C. Releasing all locks simultaneously at the end of the transaction.
D. Upgrading a shared lock to an exclusive lock during the growing phase.

38 Which of the following concurrency control anomalies occurs when a transaction reads a value written by an uncommitted transaction?

concurrency control Medium
A. Phantom read
B. Non-repeatable read
C. Dirty read
D. Lost update

39 A schedule is defined as 'recoverable' if, for every pair of transactions and where reads a value written by , which of the following holds true?

recoverability Medium
A. commits before commits.
B. commits before commits.
C. Both transactions commit simultaneously.
D. rolls back if attempts to commit.

40 What characterizes a 'cascadeless' schedule?

recoverability Medium
A. No transaction ever rolls back.
B. Deadlocks are completely eliminated.
C. When a transaction aborts, all other transactions automatically commit.
D. A transaction only reads values written by already committed transactions.

41 In a procedural SQL block, how does three-valued logic (TRUE, FALSE, UNKNOWN) affect the execution of an IF ... ELSIF ... ELSE construct when evaluating a condition that results in NULL?

flow control statements Hard
A. The NULL condition evaluates to UNKNOWN, bypassing the current IF/ELSIF block and proceeding to the next ELSIF or the ELSE block.
B. The execution halts, and the transaction is implicitly rolled back to the nearest savepoint.
C. The NULL condition evaluates to UNKNOWN, which is treated as TRUE by default, executing the block under the current condition.
D. The construct throws a runtime exception because NULL cannot be evaluated in boolean contexts.

42 When defining a User-Defined Function (UDF) in a database, what is the primary performance implication of declaring the function as DETERMINISTIC when it actually accesses sequence values or time-based data?

functions Hard
A. The query optimizer may cache the result of the function for a given input during a query, leading to incorrect, stale data being returned for subsequent rows.
B. The function will automatically be converted to an autonomous transaction to isolate the non-deterministic state.
C. The database prevents the compilation of the function because sequence values trigger a syntax error in deterministic contexts.
D. The database engine will automatically track state changes and invalidate the query cache dynamically, causing high CPU overhead.

43 Consider a stored procedure containing an autonomous transaction. If the main transaction that calls this procedure rolls back, what is the state of the data modified and committed by the autonomous transaction?

stored procedures Hard
A. The autonomous transaction's changes are rolled back along with the main transaction.
B. The database raises an exception and places the database in an inconsistent state.
C. The autonomous transaction's changes remain committed because it operates in a separate, independent transaction context.
D. The autonomous transaction's changes are reverted only if the procedure used an OUT parameter to pass data back.

44 In the context of stored procedures with OUT parameters, if an unhandled exception occurs within the procedure after an OUT parameter has been assigned a new value, what value is returned to the calling environment?

stored procedures Hard
A. The original value (or NULL) before the procedure call is returned.
B. The OUT parameter becomes uninitialized (resulting in undefined behavior).
C. The procedure returns a pointer to the exception object.
D. The newly assigned value is returned.

45 Which of the following scenarios describes a 'mutating table' exception when dealing with implicit cursors and triggers?

cursors Hard
A. A cursor attempts to fetch data from a table that is currently locked in SHARE mode by another transaction.
B. A statement-level trigger modifies a view that possesses an INSTEAD OF trigger.
C. A dynamic cursor fetches a row that was deleted by another transaction after the cursor was opened.
D. A row-level trigger attempts to query or modify the same table that fired the trigger, while the triggering statement is still processing.

46 When utilizing the WHERE CURRENT OF clause with an explicit cursor to update a row, what prerequisite must be met in the cursor declaration to guarantee concurrency control without phantom updates?

cursors Hard
A. The cursor must be declared as a DYNAMIC scrollable cursor.
B. The cursor must strictly select only the primary key column.
C. The cursor must be declared with ISOLATION LEVEL SERIALIZABLE.
D. The cursor must include a FOR UPDATE clause, which acquires row-level exclusive locks during the fetch phase.

47 In a complex database schema, an INSTEAD OF trigger is defined on a non-updatable view. If a transaction executes an INSERT statement on this view, how does the database handle the base table constraints?

triggers Hard
A. The database throws a compilation error because non-updatable views cannot evaluate constraints.
B. The base table constraints are evaluated only when the DML statements inside the INSTEAD OF trigger attempt to modify the base tables.
C. The base table constraints are ignored entirely because the view is abstract.
D. The base table constraints are evaluated before the INSTEAD OF trigger executes.

48 Consider a scenario with cascading triggers: Table A has an AFTER UPDATE trigger that updates Table B. Table B has an AFTER UPDATE trigger that updates Table C. If the trigger on Table B fails and raises an unhandled exception, what is the final state of the transaction?

triggers Hard
A. Table C is rolled back, but Table A and Table B updates remain.
B. Table B and Table C are rolled back, but Table A's update is committed.
C. The entire transaction, including the initial update on Table A, is rolled back.
D. Only Table B's update is rolled back.

49 In a nested block structure, the inner block catches a NO_DATA_FOUND exception, performs an INSERT into an error log table, and then executes a RAISE; statement without arguments. What is the consequence of this action?

exception handling Hard
A. The NO_DATA_FOUND exception is re-raised to the outer block, and if not caught there, the entire transaction (including the error log INSERT) is rolled back.
B. The error log INSERT is immediately committed, and the outer block resumes execution.
C. A syntax error occurs because RAISE requires an explicit exception name.
D. The exception is masked, and the inner block successfully completes, allowing the outer block to continue normally.

50 Which of the following statements correctly differentiates between user-defined exceptions and pre-defined exceptions during the compilation and execution phases?

exception handling Hard
A. Pre-defined exceptions are raised automatically by the database engine, whereas user-defined exceptions must be raised explicitly using the RAISE statement.
B. User-defined exceptions are evaluated at compile time, whereas pre-defined exceptions are evaluated at runtime.
C. User-defined exceptions bypass the rollback mechanism, whereas pre-defined exceptions force a transaction rollback.
D. Pre-defined exceptions can only be caught in the OTHERS handler, whereas user-defined exceptions require specific WHEN clauses.

51 A Database Management System implements a Steal / No-Force buffer management policy. Which types of logging are strictly required to ensure both Atomicity and Durability in the event of a system crash?

Database Transaction Processing Hard
A. Neither UNDO nor REDO logging is required if Write-Ahead Logging (WAL) is disabled.
B. Both UNDO and REDO logging are required.
C. Only UNDO logging is required.
D. Only REDO logging is required.

52 During transaction execution, a transaction reaches the state where the final statement has been executed, but the system has not yet recorded the commit on disk. What is the precise state of the transaction?

transaction system concepts Hard
A. Active
B. Failed
C. Committed
D. Partially Committed

53 Which of the following concurrency anomalies represents a violation of Isolation that can occur under Snapshot Isolation, where two transactions read overlapping data sets but disjointly update different items?

desirable properties of transactions Hard
A. Phantom Read
B. Non-Repeatable Read
C. Write Skew
D. Dirty Read

54 Given a schedule , it is observed that no transaction reads from or writes to a data item that has been written by an uncommitted transaction. Which of the following classifications strictly defines this schedule?

schedules Hard
A. Conflict Serializable schedule
B. Cascadeless schedule
C. Recoverable schedule
D. Strict schedule

55 Consider a schedule that is View Serializable but NOT Conflict Serializable. What property must be present in schedule ?

serializability of schedules Hard
A. The presence of cascading rollbacks.
B. Two transactions updating separate data items concurrently without locking.
C. At least one blind write (a transaction writing to a data item without reading it first).
D. A cycle in the precedence graph consisting solely of Read-Write conflicts.

56 Why is View Serializability rarely used as the standard correctness criterion in practical database concurrency control implementations?

serializability of schedules Hard
A. Because View Serializability cannot prevent dirty reads in a distributed database system.
B. Because View Serializability does not guarantee strictness, which is required for recoverability.
C. Because the problem of checking whether a schedule is View Serializable is NP-Complete, making it computationally infeasible for the scheduler.
D. Because testing for View Serializability has a computational complexity of , making it too slow.

57 Under Basic Timestamp Ordering (TO) concurrency control, what does the Thomas Write Rule optimize by ignoring certain write operations rather than aborting the transaction?

concurrency control Hard
A. It ignores read operations if an older transaction holds a write lock, preventing deadlocks.
B. It ignores a write if a younger transaction has already read the data item, allowing the older transaction to commit.
C. It skips validation phases in optimistic concurrency control for read-only transactions.
D. It ignores a write if a younger transaction has already written the data item, thus avoiding an unnecessary abort.

58 In Strict Two-Phase Locking (Strict 2PL), when does a transaction release its exclusive (write) locks?

concurrency control Hard
A. Immediately after the specific write operation is completed.
B. As soon as the shrinking phase begins.
C. When the lock manager detects a potential deadlock cycle.
D. Only after the transaction has successfully committed or aborted.

59 Schedule : . Which statement accurately categorizes this schedule concerning recoverability?

recoverability Hard
A. It is cascadeless, because reads only after writes it.
B. It is recoverable but not cascadeless, because reads the uncommitted value of from , but commits after commits.
C. It is strict, because commits before commits.
D. It is unrecoverable because overwrites the value of before commits.

60 Which of the following conditions definitively guarantees that a schedule will NOT suffer from cascading rollbacks (cascadelessness)?

recoverability Hard
A. Every transaction holds all its read and write locks until it commits.
B. Every transaction reads only those data items written by transactions that have already committed.
C. The schedule is proven to be equivalent to a serial schedule.
D. Transactions commit in the exact chronological order they started.
Previous MCQ
Unit 4
Next MCQ
Unit 6