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 cannot take any parameters
B. It is used exclusively for transaction management
C. It executes automatically on data modification
D. It must return a value

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

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

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

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

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

cursors Easy
A. DECLARE, OPEN, FETCH, CLOSE
B. OPEN, FETCH, DECLARE, CLOSE
C. FETCH, OPEN, CLOSE, DECLARE
D. DECLARE, FETCH, OPEN, 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. Sequence
C. Cursor
D. Index

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

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

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

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

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

Database Transaction Processing Easy
A. A logical unit of work that contains one or more SQL statements
B. A type of database user account
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. UPDATE
C. SAVEPOINT
D. ROLLBACK

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

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

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

desirable properties of transactions Easy
A. CRUD
B. BASE
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. Isolation
B. Atomicity
C. Durability
D. Consistency

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. Durability
B. Consistency
C. Isolation
D. Atomicity

15 In database transaction processing, what is a schedule?

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

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 only contains read operations
D. It cannot be rolled back under any circumstances

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

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

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

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

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. Atomicity
B. Starvation
C. Serializability
D. Deadlock

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

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

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. WHILE loop
B. REPEAT loop
C. LOOP statement with EXIT WHEN
D. FOR 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 accept input parameters.
B. Functions cannot return values.
C. Functions cannot contain SELECT statements.
D. Functions cannot perform DML operations (INSERT/UPDATE/DELETE) that modify database state.

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 parameters are read-only, while OUT parameters are write-only.
B. IN OUT requires a default value, while OUT does not.
C. There is no difference; they are interchangeable.
D. 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.

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 eliminate the need for index lookups.
B. Stored procedures reduce network traffic by executing the logic entirely on the database server.
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. DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE
B. OPEN, DECLARE, FETCH, DEALLOCATE, CLOSE
C. FETCH, DECLARE, OPEN, CLOSE, DEALLOCATE
D. DECLARE, FETCH, OPEN, 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. Forward-only cursor
B. Static cursor
C. Keyset-driven cursor
D. Dynamic 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. BEFORE UPDATE trigger on Employees
B. AFTER INSERT trigger on the audit table
C. INSTEAD OF DELETE trigger on Employees
D. AFTER DELETE 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. Materialized views
B. Base tables with primary keys
C. Temporary tables
D. Complex views with joins or aggregates

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 transaction is immediately committed.
C. The block ignores the exception and continues executing the next statement.
D. The database engine automatically drops the affected table.

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

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

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. Aborted
B. Active
C. Failed
D. Partially committed

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. Atomicity
B. Durability
C. Consistency
D. Isolation

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. Durability
C. Atomicity
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 log file but not the database.
C. Writes a value to a data item without previously reading it.
D. Attempts to write to a locked resource.

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 has identical in-degrees and out-degrees for all nodes.
B. The graph is completely disconnected.
C. The graph has at least one cycle.
D. The graph contains no cycles (is a directed acyclic graph).

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. Write-Read (WR) conflict
C. Read-Write (RW) conflict
D. Read-Read (RR) conflict

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

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

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

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

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. Deadlocks are completely eliminated.
B. No transaction ever rolls back.
C. A transaction only reads values written by already committed transactions.
D. When a transaction aborts, all other transactions automatically commit.

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

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 database prevents the compilation of the function because sequence values trigger a syntax error in deterministic contexts.
B. The function will automatically be converted to an autonomous transaction to isolate the non-deterministic state.
C. The database engine will automatically track state changes and invalidate the query cache dynamically, causing high CPU overhead.
D. 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.

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 autonomous transaction's changes remain committed because it operates in a separate, independent transaction context.
C. The autonomous transaction's changes are reverted only if the procedure used an OUT parameter to pass data back.
D. The database raises an exception and places the database in an inconsistent state.

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 procedure returns a pointer to the exception object.
B. The OUT parameter becomes uninitialized (resulting in undefined behavior).
C. The original value (or NULL) before the procedure call is returned.
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 row-level trigger attempts to query or modify the same table that fired the trigger, while the triggering statement is still processing.
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 cursor attempts to fetch data from a table that is currently locked in SHARE mode by another transaction.

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

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. The entire transaction, including the initial update on Table A, is rolled back.
C. Table B and Table C are rolled back, but Table A's update is committed.
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. A syntax error occurs because RAISE requires an explicit exception name.
B. 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.
C. The exception is masked, and the inner block successfully completes, allowing the outer block to continue normally.
D. The error log INSERT is immediately committed, and the outer block resumes execution.

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 bypass the rollback mechanism, whereas pre-defined exceptions force a transaction rollback.
C. Pre-defined exceptions can only be caught in the OTHERS handler, whereas user-defined exceptions require specific WHEN clauses.
D. User-defined exceptions are evaluated at compile time, whereas pre-defined exceptions are evaluated at runtime.

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. Only UNDO logging is required.
B. Neither UNDO nor REDO logging is required if Write-Ahead Logging (WAL) is disabled.
C. Both UNDO and REDO logging are 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. Failed
B. Partially Committed
C. Active
D. 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. Dirty Read
B. Phantom Read
C. Write Skew
D. Non-Repeatable 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. Recoverable schedule
C. Strict schedule
D. Cascadeless 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. Two transactions updating separate data items concurrently without locking.
B. The presence of cascading rollbacks.
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 testing for View Serializability has a computational complexity of , making it too slow.
C. Because the problem of checking whether a schedule is View Serializable is NP-Complete, making it computationally infeasible for the scheduler.
D. Because View Serializability does not guarantee strictness, which is required for recoverability.

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

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

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

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

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

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

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