Adbase H1 Midterms

Question 1

a collection of operations that form a single logical unit of work.

Answer 1

Transaction

Question 2

the equivalent of a single SQL statement in an application program or transaction

Answer 2

database request

Question 3

Satisfies the constraints specified in the schema

Answer 3

database consistent state

Question 4

A DBMS uses this to keep track of all the transactions that update the database.

Answer 4

Transaction log

Question 5

requires that all operations (SQL request) of a transaction should be completed.

Answer 5

Atomacity

Question 6

ensures that only valid data following all rules and constraints will be written in the database.

Answer 6

Consistency

Question 7

ensures that once transaction changes are done and committed, they cannot be undone or lost.

Answer 7

Durability

Question 8

The data used during the execution of a current transaction cannot be used by another transaction until the first one is completed.

Answer 8

Isolation

Question 9

if the transaction fails after step 3, and before step 6, the system should ensure that its updates are not reflected in the database, an inconsistency will result.

Answer 9

Atomicity requirement

Question 10

The sum of Aldous account and Brendon’s account is unchanged by the execution of the transaction.

Answer 10

Consistency requirement

Question 11

Transactions should be run sequentially. Therefore, no other transaction will happen on both accounts until the current transaction is completed.

Answer 11

Isolation requirement

Question 12

Once the user has been notified that the transaction has completed( the transfer of the P1000 has taken place), the updates to the database by the transaction must persist despite failures.

Answer 12

Durability requirement

Question 13

This marks the beginning of transaction execution.

Answer 13

BEGIN TRANSACTION

Question 14

this signals a successful end of the transaction so that any changes (updates) executed by the transaction can be safely committed to the database and will not be undone.

Answer 14

COMMIT

Question 15

This signals that the transaction has ended unsuccessfully so that any changes or effects that the transaction may have applied to the database must be undone.

Answer 15

ROLLBACK TRANSACTION

Question 16

is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

Answer 16

SAVE TRANSACTION

Question 17

returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Answer 17

@@TRANCOUNT

Question 18

in this state, a transaction stays in this state to perform READ and WRITE operations.

Answer 18

Active state

Question 19

where the final statement in queries has been executed

Answer 19

Partially committed state

Question 20

after all the operation has been completed

Answer 20

Committed state

Question 21

if one of the operations cannot be done or proceed

Answer 21

Failed State

Question 22

corresponds to the transaction leaving the system and if it is either committed or aborted

Answer 22

Terminated

Question 23

When several transactions execute simultaneously in the database, there will be a chance that the consistency and integrity of data may no longer be preserved. The system must control the interaction among the concurrent transactions, and this control is achieved through concurrency control techniques.

Answer 23

• Concurrency Control:

Question 24

occurs when two concurrent transactions, T1 and T2, are updating the same data element, and one of the updates is lost (overwritten by the other transaction).

Answer 24

• Lost Update

Question 25

occurs when two transactions, T1 and T2, are executed concurrently, and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data.

Answer 25

• Uncommitted data

Question 26

occur when a transaction accesses data before and after one or more other transactions finish working with the same data.

Answer 26

• Inconsistent retrievals

Question 27

guarantees exclusive use of data item to a current transaction.
o Transaction T2 does not have access to a data item that is currently being used by transaction T1.
o The lock is released (unlocked) when the transaction is completed.

Answer 27

lock

Question 28

o Locks the entire database
o prevents the use of any tables in the database to transaction T2 while transaction T1 is being executed
o Good for batch processes, but not suitable for multiuser DBMS as it would slow down the data access if thousands of transactions had to wait for the current transaction to be completed

Answer 28

database-level lock

Question 29

o The entire table is locked.
o Prevents access to any row by transaction T2 while transaction T1 is using the table.
o If a transaction requires access to several tables, each table may be locked.
o Two (2) transactions can access the same database as long as they access different tables.
o Transactions T1 and T2 cannot access the same table even when they try to use different rows.

Answer 29

table-level lock

Question 30

o Less restrictive
o Allows concurrent transactions to access different rows of the same table even when the rows are located on the same page
o Improves the availability of data but requires high usage of space because a lock exists for each row in a table of the database

Answer 30

row-level lock

Question 31

o Has only two states: locked (1) and unlocked (0).
o If an object such as a database, table, or row is locked by a transaction, no other transaction can use that object.
o As a rule, the transaction must unlock the object after its termination.
o These options are automatically managed by the DBMS.
o User does not require to manually lock or unlock data items.

Answer 31

binary lock

Question 32

o An exclusive lock exists when access is reserved specifically for the transaction that locked the object.
o A shared lock exists when a transaction wants to read data from the database, and no exclusive lock has held that data item.
o Using the shared/exclusive locking concept, a lock can have three states: unlocked, shared (read), and exclusive (write).

Answer 32

shared/exclusive lock

Question 33

occurs when two (2) transactions wait indefinitely for each other to unlock data.

Answer 33

deadlock

Question 34

• Transaction isolation levels are described by the type of “reads” that a transaction allows or not. The types of read operation are:

Answer 34

Transaction Isolation Level

Question 35

a transaction can read data that is not committed yet.

Answer 35

Dirty Read

Question 36

The original salary of Mary was ₱15,000. The treasurer changed Mary’s salary to ₱20,000 but did not commit the transaction.

Answer 36

Dirty Read

Question 37

Mary read her wages and found an increase in her wages by
₱5,000.

Answer 37

Dirty Read

Question 38

The treasurer found out that they had a mistake in the corresponding transaction. The transaction had been rolled back, and Mary’s salary is back to ₱15,000. The ₱20,000 salary is now considered dirty data.

Answer 38

Dirty Read

Question 39

a transaction reads a given row at time T1, and then it reads the same row at time T2, yielding different results. The original row may have been updated or deleted.

Answer 39

Non-repeatable read

Question 40

In Transaction 1, Mary read her own salary of ₱15,000, and the operation was not completed.

Answer 40

Non-repeatable read

Question 41

In Transaction 2, the Treasurer modified Mary’s salary to
₱20,000 and submitted the transaction.

Answer 41

Non-repeatable read

Question 42

In Transaction 1, when Mary read her salary again, her salary changed to ₱20,000.

Answer 42

Non-repeatable read

Question 43

a transaction executes a query at time T1, and then it runs the same query at time T2, yielding additional rows that satisfy the query.

Answer 43

Phantom Read

Question 44

Transaction 1, read all employees with a salary of ₱1000 and return 10 rows.

Answer 44

Phantom Read

Question 45

At this point, Transaction 2 inserts an employee record into the employee table with a salary of 1000

Answer 45

Phantom Read

Question 46

Transaction 1 read all employees with a salary of ₱1000 again
and return 11 rows.

Answer 46

Phantom Read

Question 47

o is the least restrictive isolation level
o it ignores locks placed by other transactions.
o Can read modified data values that have not yet been committed by other transactions; these are called “dirty” reads.

Answer 47

READ UNCOMMITTED

Question 48

o A more restrictive isolation level than READ COMMITTED
o Encompasses READ COMMITTED
o Ensures that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits
o Does not prevent other transactions from inserting new rows into the tables which have been using in the existing transaction, resulting in “phantom reads”

Answer 48

REPEATABLE READ

Question 49

o Ensures that the data that one transaction has read, will be prevented from being updated or deleted by any other transaction.

Answer 49

SERIALIZABLE isolation