CH6

Question 1

Database Administration Functions

Answer 1

– Concurrency control
– Security
– Backup
– Recovery

Question 2

Concurrency Control

Answer 2

ensures that one user’s actions do not impact another user’s actions

Question 3

Concurrent Transaction

Answer 3

When two transactions are being processed against a database at the same time

Question 4

Interdependency

Answer 4

Changes required by one user may impact others

Question 5

Atomic Transactions

Answer 5

database operation typically involves several transactions.

transactions are atomic and are sometimes called logical units of work (LUW).

Question 6

Lost Update Problem

Answer 6

• If two or more users are attempting to update the same piece of data at the same time, it is possible that one update may overwrite the other update

Question 7

Concurrency Issues

Answer 7

• Dirty reads

* Phantom reads

Question 8

Dirty reads

Answer 8

The transaction reads a changed record that has not been committed to the database

Question 9

Phantom reads

Answer 9

The transaction re-reads a data set and finds that a new record has been added

Question 10

Implicit locks

Answer 10

issued automatically by the DBMS based on an activity.

Question 11

Explicit locks

Answer 11

issued by users requesting exclusive rights to the data.

Question 12

Serializable Transactions

Answer 12

When two or more transactions are processed concurrently, the results in the database should be logically consistent with the results that would have been achieved had the transactions been processed in an arbitrary serial fashion

Question 13

Two-Phased Locking

Answer 13

• One way to achieve serializable transactions is by using two-phased locking.
• Two-phased locking lets locks be obtained and released as they are needed

Question 14

Growing phase

Answer 14

when the transaction continues to request additional locks

Question 15

Shrinking phase

Answer 15

when the transaction begins to release the locks

Question 16

Deadlock

Answer 16

On occasions, two transactions may indefinitely wait on each another to release resources

Question 17

Optimistic Locking

Answer 17

Read transaction is processed, updates are issued

Question 18

Pessimistic Locking

Answer 18

Locks are issued, the transaction is processed and then the locks are freed

Question 19

Consistent Transactions

Answer 19

Consistent transactions are often referred to by the ACID. – Atomic – Consistent – Isolated – Durable

Question 20

ACID: Atomic

Answer 20

• An atomic transaction is one in which all of the database actions occur or none of them do.
• A transaction consists of a series of steps. Each step must be successful for the transaction to be saved.
• This ensures that the transaction completes everything it intended to do before saving the changes.

Question 21

ACID: Consistent

Answer 21

• No other transactions are permitted on the records until the current transaction finishes.
• This ensures that the transaction integrity has statement level consistency among all records.

Question 22

ACID: Isolation

Answer 22

• Within multiuser environments, different transactions may be operating on the same data.
• As such, the sequencing of uncommitted updates, rollbacks, and commits continuously change the data content.
• The 1992 ANSI SQL standard defines four isolation levels that specify which of the concurrency control problems are allowed to occur

Question 23

ACID: Durable

Answer 23

• A durable transaction is one in which all committed changes are permanent

Question 24

Cursors

Answer 24

• A cursor is a pointer into a set of rows that are the result set from an SQL SELECT statement.
• Cursors are usually defined using SELECT statements.

Question 25

Cursor Types

Answer 25

• Forward only or scrollable – Static cursor – Keyset cursor – Dynamic cursor

Question 26

Notes On Cursor Types

Answer 26

* Other DBMS products may define a different set of cursors. * In this case, the forward only cursor is considered a separate cursor type, and only a scrollable cursor may be static, keyset, or dynamic.

Question 27

Database Security

Answer 27

• Database Security strives to ensure that – Only authenticated users – Perform authorized activities

Question 28

Processing Rights and Responsibilities

Answer 28

* Processing rights define who is permitted to do what and when. * The individuals performing these activities have full responsibility for the implications of their actions. * Individuals are identified by a username and a password.

Question 29

Granting Permissions

Answer 29

* Database users are known as an individual and as a member of one or more roles. * Granting access and processing rights/privileges may be granted to an individual and/or a role. * Users possess the compilation of rights granted to the individual and all the roles for which they are members.

Question 30

Database Security Guidelines

Answer 30

* Run the DBMS behind a firewall. * Apply the latest operating system and DBMS service packs and patches. * Limit DBMS functionality to needed features. • Protect the computer that runs the DBMS. * Manage accounts and passwords.

Question 31

Database Backup and Recovery

Answer 31

``` • Common causes of database failures – Hardware failures – Programming bugs – Human errors/mistakes – Malicious actions • As these issues are impossible to completely avoid, recovery procedures are essential. ```

Question 32

Reprocessing

Answer 32

all activities since the backup was performed are redone. • This is a brute-force technique. • This procedure is costly in the effort involved in re-entering the data. • This procedure is risky in that human error is likely and in that paper record-keeping may not be accurate.

Question 33

Log File

Answer 33

The log file is then used for recovery via rollback or rollforward. – To undo a transaction the log must contain a copy of every database record before it was changed. • Such records are called before-images. • A transaction is undone by applying before-images of all its changes to the database. – To redo a transaction the log must contain a copy of every database record (or page) after it was changed. • These records are called after-images. • A transaction is redone by applying after-images of all its changes to the database.

Question 34

Rollback

Answer 34

– Log files save activities in sequence order. – It is possible to undo activities in reverse order that they were originally executed. – This is performed to correct/undo erroneous or malicious transaction(s) after a database is recovered from a full backup.