database transactions

Question 1

database transaction

Answer 1

any (possibly multi-step) action that reads from and/or writes to a database

Question 2

a database transaction may consist of

Answer 2

a single SQL statement or a collection of related SQL statements

Question 3

successful transaction

Answer 3

all of the SQL statements are completed successfully

Question 4

consistent database state

Answer 4

all data integrity constraints are satisfied

Question 5

successful transaction changes the database from

Answer 5

one consistent state to another

Question 6

improper or incomplete transactions can have a

Answer 6

devastating effect on database integrity

Question 7

if a DBMS supports transaction management, it will

Answer 7

roll back an inconsistent database to a previous consistent state

Question 8

an inconsistent database is the result of

Answer 8

an unsuccessful transaction

Question 9

properties of a transaction

Answer 9

atomicity
consistency
isolation
durability

Question 10

atomicity

Answer 10

the “all or nothing” property
- all transaction operations must be completed
- transaction is treated as a single unit of work

Question 11

consistency

Answer 11

when a transaction is completed, the database must be in a consistent state

Question 12

isolation

Answer 12

data used during the execution of a transaction cannot be used by a second transaction until the first one is completed

Question 13

durability

Answer 13

once transaction changes are committed (applied to database), they cannot be undone or lost due to a subsequent failure

Question 14

if a transaction involves reading from a table,

Answer 14

no changes are made to the database, and it remains in a consistent state

Question 15

if a transaction requires updating two tables, the database is in ____ only if _____

Answer 15

a consistent state, both database requests are successfully completed

Question 16

ANSI SQL standards for managing transactions:

Answer 16

COMMIT;
ROLLBACK;
BEGIN TRANSACTION also used by some DBMSs

Question 17

COMMIT;

Answer 17

permanently records all changes in the database, automatically ends transaction

Question 18

ROLLBACK;

Answer 18

aborts all uncommitted changes, database is rolled back to previous state

Question 19

t/f: a rolled back transaction can typically be restarted later

Answer 19

true

Question 20

a DBMS maintains a transaction log to

Answer 20

support recovery to a consistent state

Question 21

transaction log stores

Answer 21

• a record for the beginning of the transaction
• a record for each transaction component
• a record for the end of the transaction (COMMIT)

Question 22

the transaction log record for a transaction component includes

Answer 22

• type of operation
• tables/attributes affected
• before and after values for attributes affected

Question 23

transaction log is typically implemented as

Answer 23

one or more files that are stored separately from the database itself

Question 24

t/f: a transaction log is automatically updated when the DBMS executes transactions

Answer 24

true

Question 25

transaction log tradeoffs

Answer 25

increases DBMS processing overhead, but provides ability to restore a corrupted databased

Question 26

MS Access only supports multi-step transactions when

Answer 26

using code to access DBMS - the SQL window within Access only allows one SQL statement to be run at a time

Question 27

you can implement multi-step transactions in MS Access when

Answer 27

- connecting from other office applications via VBA - connecting from a .NET application - connecting from a web-based application, etc