Database Final, Database Systems Exam 4 Practice

A __________ statement erases saved data for exactly one savepoint

RELEASE SAVEPOINT

The concurrency control protocol in which transactions hold their exclusive locks until commit is called

Strict two-phase locking

Two transactions run in parallel to set an account's balance to different values

Isolated

8. Deadlock can occur in a serializable schedule

False

10. All of the following signal an end of a transaction EXCEPT

SAVEPOINT

11. Which two-phase locking technique results in the longest wait times for concurrent transactions?

Rigorous

12. A transaction increases all employee salaries by 10%. Due to a system failure, increases for only half of the employees are written to the database

Atomic

A transaction saves a row with a foreign key. The foreign key is not NULL and does not match any values of the corresponding primary key.

Consistent

Two transactions running in parallel reserve the same seat for different passengers

Isolated

A transaction withdraws $500 from account A and deposits $500 in account B. The withdrawal and deposit are written in the database, but due to a disk drive failure, the information is permanently lost.

Durable

15. One transaction can hold an exclusive lock while other transactions hold shared locks on the same row

False

16. B and C are ________ schedules (just solve for Z until Z commit)

conflicting

17. In recovery techniques ________ requires that the effects of a committed transaction be permanent, so they must survive the loss of main memory, loss of disk storage, and failures during data transfers to disk.

durability

19. For a schedule to be serializable it must

produce the same result as an actual serial execution

21. Checkpoints can be initialized either manually by the database administrator or automatically by the database

True

Assume existence of user Assistant Manager assistant. What privilege should you grant to assistant who needs to know only average department salaries? GRANT ____ ON DeptInfo TO assistant

SELECT

23. Each of the following is a well-known concurrency control problem EXCEPT

the redundant data problem

24. After a rollback, the database restarts a transaction:

Sometimes

25. A ___________ statement reverses all changes made by a transaction

ROLLBACK

26. A __________ statement temporarily saves data read and written by a transaction

SAVEPOINT

28. Several transactions can hold concurrent shared locks on the same row

True

29. For unknown reasons, an application program freezes. The data administrator forces termination and restarts the application

System failure

35. A database uses strict two-phasing locking. Transaction A takes an exclusive lock on X. Transaction B requests a shared lock on X. When is the shared lock granted?

After A commits or rolls back

37. A transaction with isolation level SERIALIZABLE can participate in a deadlock

True

39. A list of active transactions appear in a(n) ________ record

checkpoint

A bank stores checking account data in a database. The bank stores account owner data in a file on a different computer. A database transaction creates a new owner in the file and assigns the owner to a checking account in the database: The transaction is always isolated

False

A developer wishes to link to a database with a utility created in C. Which MySQL component does the developer use for the link?

API

42. Which MySQL layer interacts directly with database users and administrators?

Tools

43. Which MySQL component retains data blocks from storage in main memory for possible reuse?

Buffer manager

44. A document that sequentially writes all the database operations is known as a/an ______.

recovery log

A database admin uses a SHOW statement to retrieve information about objects in a database. This information is contained in a ____.

data dictionary

46. In the query processor layer, the query optimizer generates a/an ______.

execution plan

47. A recovery system should manage which three failure scenarios?

Transaction failure, System failure, and Storage media failure

48. In MySQL, which of the following SQL operations always commits immediately?

CREATE

49. A ___ is a part of the concurrency system that monitors, grants, and releases locks

Lock Manager

50. In an online bank application that transfers funds from a checking account to a savings account, the ___ property ensures that the changes made to each account will not be lost due to a computer failure.

durability

51. Which isolation level prevents dirty, non-repeatable, and phantom reads?

Serializable

52. When using the MySQL Workbench GUI, which icon will execute an SQL statement?

Lightning bolt

54. In a ____, all transactions come to a halt and remain at a standstill until one of the transactions is aborted.

deadlock

57. ____ allows all executed database instructions to be rolled back and to be restored in a prior transaction state.

SAVEPOINT

59. Which deadlock management technique automatically rolls back a transaction when a lock is not released in a fixed period of time?

Timeout

60. When using the MySQL Command-Line Client, which character ends a command?

; (semi-colon)

Phases in recovery using the ARIES method include all of the following EXCEPT

synthesis

Verifying the identity of a user is called

authentication

With a cold backup, recovery from storage media failure reads the log:

From the latest checkpoint

A ___ statement reverses all changes made by a transaction

ROLLBACK

two transactions run in parallel to set an account's balance to different values

isolated

A _________ statement erases saved data for exactly one savepoint

release savepoint

which of the following is NOT true of the two-phase locking protocol?

it eliminates the problem of deadlock

during the undo phase, the recovery system reads the log in reverse and stops at:

The start record for the last transaction in the active transaction list

CHECKPOINT statement syntax is specified in the SQL standard

False

A dirty block is a block that has been corruped and cannot be read

False

A database using strict two-phase locking Transaction A takes an exclusive lock on X transaction B requests a shared lock on X. When is the shared lock granted?

After A commits or rolls back

Database availability is:

Percentage of time that a databse is responsive to application programs

After a rollback, the database restarts a transaction:

Sometimes

The ______ schedule has the same result as schedule

Equivalent

When a transaction deletes a table row, an _________ record is written in the log

update

A _____ statement temporarily saves data read and written by a transaction

savepoint

Which two-phase locking technique results in the longest wait times for concurrent transactions?

Rigorous

All of the following are security tools EXCEPT

constraints

After a transaction commits, a transaction can be rolled back

NEVER

Each is a well known concurrency control problem EXCEPT

the redundant data problem

MySQL innoDB

False

how many sql statement in one transaction?

at least one

buffer manager retains _ from the _ to reduce data access time

data blocks, file system

which isolation level dirty reads

read uncommited

mySQL community edition create database full control mySQL

Root

A _ is a sequential order of database instructions for multiple transactions

Schedule

Username password is incorrect so web app cannot connnect to databse which failure scenario>

Transaction failure

Document sequentially write databse operations

recovery log

icon execute sql statement

lightning bolt

a database _ is a unit of work that is treated as a whole

transaction

link databse to utility in C

API

recovery technique that creates a nearly synchronized backup of the primary databse

Hot Backup

mysql layer interacts directly with databse users and administrators

Tools

SQL component retains data block from storage in main memory for reuse

buffer manager

Which options in schemas tab

list of available databases

incorrect mysql synthax mySQL workbench

Error code

In an online bank application that transfers funds from a checking account to a savings account, if a debit is made successfully from the checking account, the _____ property ensures that the corresponding credit is made to the savings account.

atomicity

monitor grant release locks

Lock manager

A(n) ________ record always appears in the log at the beginning of a transaction.

Transaction or Start

With a hot backup, as a transaction executes against the primary database

Log records are sent to the secondary database

A _________ statement erases saved data for zero, one, or many savepoints.

ROLLBACK TO

Match the schedule type to the example schedule

T1 T2

read X

Y = X +4

write Y

commit

read X

X = X / 8

write X

commit

Serial Schedule

Phases in recovery using the ARIES method include all of the following EXCEPT

synthesis

A __________ statement temporarily saves data read and written by a transaction

SAVEPOINT

After a rollback, the database restarts a transaction

Sometimes

Select the ACID property violated in the following example:

Two transactions run in parallel to set an accounts balance to different values.

Isolated

Deadlock can be spotted by looking for which of the following in the wait-for graph?

a cycle

A bank stores checking account data in a database. The bank stores account owner data in a file on a different computer. A database transaction creates a new owner in the file and assigns the owner to a checking account in the database.

The transaction is always isolated.

False

Select the failure scenario that best describes the example:

The database detects two deadlocked transactions. To break the deadlock, the database rolls back one of the transactions.

Transaction failure

A graphical depiction of the execution sequence of transactions is called a

Schedule

When two READ UNCOMMITTED transactions run concurrently, the result may vary.

True

Transactions A and B are both SERIALIZABLE, and A always starts before B. The results may vary.

False

How many SQL statements must be in one transaction?

At least one

T2 reads some accounts

T2 reads remaining accounts

T1 computes the largest account

T1 pays CEO 1% of largest account

T1 commits

T2 computes the average account size

T2 pays executives 1% of average account size

T3 deletes account A

T3 commits

T2 commits

Given the above set of transactions complete the following statement:

T3 executes ___________.

without a conflict

For unknown reasons, an application program freezes. The data administrator forces termination and restarts the application

System failure

After all updates have been reversed in a rollback, a(n) __________ record is written in the log.

Rollback or Transaction

Several transactions can hold concurrent shared locks on the same row.

True

Which two-phase locking technique has, in effect, just one phase?

Rigorous