Chapter 6 - Database Administration

Question 1

What are the Database Administration-Related Terms?

Answer 1

Data administration - a function that applies to an entire org concerning data privacy and security.
Database administration - More technical function that is specific to a particular database
Database administrator - Person in charge of a database and facilitates the development of use of it

Question 2

Figure 6.2

Answer 2

The database processing environment

Question 3

What is Concurrency Control?

Answer 3

It ensures that one user’s work does not inappropriately influence another user’s work. The difficulty is balancing strict and lenient levels

Question 4

How do users work in a Database?

Answer 4

Users submit work in the form of transactions, also known as logical units of work (LUWs)

Question 5

What is an Atomic Transaction?

Answer 5

One where a series of actions are taken on a database such that all of them are performed successfully or none of them are performed at all. Nothing is partially processed

Question 6

What are the Typical Problems that may occur when many users process work in a database At the Same Time?

Answer 6

Lost Update (Concurrent Problem) - If one user’s update overwrites another’s
Inconsistent Read - One user reads data that have been processed by only a portion of another user’s transaction

Question 7

Figure 6.5

Answer 7

Example of the Lost Update Problem

Question 8

What is Resource Locking?

Answer 8

It prevents concurrent processing problems by disallowing sharing by locking data that are retrieved for update

Question 9

What are the Four (4) Types of Resource Locks?

Answer 9

Explicit Locks: Lock resources by placing ‘commands’ explicitly
Implicit Locks: A DBMS places locks because it needs to control resources
Exclusive Locks: Lock resources from any type of access
Shared Locks: Locks an item from being changed but not from being read

Question 10

What does Serializable mean in regards to Transactions?

Answer 10

Concurrent transactions are processed in ‘an arbitrary serial fashion’ so that the database is maintained in a logically consistent state.

Question 11

What is Two-Phased Locking?

Answer 11

A method to achieve serializable transactions. It lets locks be obtained and released as they are needed.

Question 12

What are the Two Phases of Two-Phased Locking?

Answer 12

Growing Phase - When the transaction continues to request additional locks
Shrinking Phase - When the transaction begins to release the locks

Question 13

What is a Deadlock?

Answer 13

AKA Deadly Embrace; When two transactions indefinitely wait on each other to release locks on resources.

Question 14

What is the Difference between Optimistic and Pessimistic Locking?

Answer 14

Optimistic locking assumes that no conflict will occur thus no errors will occur.

Pessimistic locking assumes that conflict will occur, thus locks are issued.

Question 15

What is the SQL Transaction Control Language (TLC)? (Note: SQL syntax varies between DBMS products)

Answer 15

SQL BEGIN TRANSACTION statement
SQL COMMIT TRANSACTION statement
SQL ROLLBACK TRANSACTION statement

Question 16

What does it mean when a Transaction is ACID?

Answer 16

Atomic
Consistent
Isolation
Durable

Question 17

What is an Atomic Transaction?

Answer 17

It is a transaction in which all of the database actions occur, or none of them do

Question 18

What is a Consistent Transaction?

Answer 18

A consistent transaction means that no other transactions are permitted on the records until the current transaction finishes.

Question 19

What is Isolation as it regards to ACID Transactions?

Answer 19

Different transactions may be operating on the same data which can result in continuously changing data content. It is controlled by setting the “isolation levels” to prevent data read problems.

Question 20

What is a Durable Transaction?

Answer 20

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

Question 21

What are the Three (3) Types of Data Read Problems?

Answer 21

Dirty Read (DR) - Reads changed data that is uncommitted. If it’s rolled back, the transaction has incorrect data.
Nonrepeatable Read (NR) - Rereads changed data but finds committed changes in between
Phantom Read (PR) - Rereads data and finds new rows inserted

Question 22

Which Data Read Problems are possible on each Isolation Level?

Answer 22

Read Uncommitted - All 3 Possible
Read Committed - NR and PR possible; DR not possible
Repeatable Read - PR possible; DR and PR not possible
Serializable - None possible

Question 23

What is a Cursor?

Answer 23

A cursor is a pointer into a set of rows that is the result set from a SQL SELECT statement.

Question 24

What are the Cursor Types?

Answer 24

Either: Forward only (one way) or scrollable (both ways)
Static - Takes a snapshot and processes it
Dynamic - A fully featured cursor
Keyset - Combines some features of static and dynamic

Question 25

Database Security strives to Ensure what Two Things?

Answer 25

Authentication - You are who you you’re supposed to be. Password/login/etc.
Authorization - You have the permissions you’re supposed to have

Question 26

What is the Principle of Database Security Admin?

Answer 26

Permissions are given to groups/roles, not to individual users unless needed

Question 27

What are the Seven (1-4) DBMS Security Guidelines?

Answer 27

1. Run the DBMS behind a firewall
2. Apply the latest OS and DBMS service packs and fixes
3. Limit DBMS functionality to needed features
4. Protect the computer that runs the DBMS

Question 28

What are the Seven (5-7) DBMS Security Guidelines?

Answer 28

5. Manage Accounts and Passwords
6. Encrypt sensitive data transmitted across the network
7. Encrypt sensitive data stored in databases

Question 29

How is Application-Level Security applied?

Answer 29

When application security is executed on the web server computer, sensitive security data does not need to be transmitted over the network

Question 30

What is Recovery via Reprocessing? What are the downsides?

Answer 30

All activities since the backup was performed are redone. Takes a long time and might never catch up if the system is heavily scheduled

Question 31

What is Recovery via Rollback and Rollforward?

Answer 31

Log File - A mechanism to record database activities
Before Images - Copy of every DB record before it was changed (use this to undo or rewind or rollback transactions)
After Images - Copy of every DB record after it was changed (use this to redo or rollforward or fast-forward transactions)

Question 32

What are Additional DBA Responsibilities?

Answer 32

Ensure a system exists to gather and record user-reported error and other problems.

Create and manage a process for controlling the database configuration

Question 33

What do Service Level Agreements (SLAs) do?

Answer 33

It covers backups, application response time, and error reporting

Question 34

What is an Index? Why do you Need it? Why is it Useful? Advice?

Answer 34

It is a component of the physical database design. It can speed up a data access and improve a read performance.

Advice is do not create unnecessary indexes as they slow down database update performances.

Question 35

Which Fields are Prime Candidates to add indexes? What are the Disadvantages?

Answer 35

Frequently used fields. EMPLOYEE Name, SSN, dob, phone #.

Disadvantages are it uses storage space and the DBMS must update the index when the data gets updated.