Database Administration

databaseadministration.htm; updated 4/10/01

Database Administration

Introduction.

The topics covered in these notes provide an overview of different aspects of database administration.
You need to understand the function of the database administrator.
You will also study some basic concepts about database security, recovery, transaction control, and concurrency control.

Functions of Data Administration vs. Database Administration.

Some organizations define different functions for the Data Administrator than those of the Database Administrator.
Data Administration concerns the responsibility for serving as the custodian of the firm's data. The Data Administrator:

resolves disputes that arise because data are centralized, but shared among system users.
decides where data will be stored and managed.
maintains corporate wide data definitions and standards.
plans for database usage, analysis, design, implementation, maintenance, and protection.
has a high level of both managerial and technical skills.

Database Administration involves the actual hands-on, physical manage databases.
This is a very technical function that focuses on physical database design issues including security enforcement, system performance, and backup/recovery.
A Database Administrator (DBA) may administer several databases simultaneously.
A DBA should have:

a broad technical background of hardware and operating systems as well as database programming.
knowledge of logical and physical issues.
good managerial skills.
knowledge of the Database Systems Development Lifecycle.

Database Systems Development Lifecycle.

The figure shown below gives the lifecycle of the stages of Database Systems development.

The major functions of these various phases are summarized as follows:

1. Database planning. Support overall organizational business plan. Develop a data administration plan and design the database environment.
2. Database analysis. Identifying data entities used by the organization, relationships, and documenting them (ER diagram). Define business rules and operational requirements.
3. Database design. Includes logical and physical design. Convert ER diagram to relational tables. Design integrity controls.
4. Database implementation. First step is creation and initial load of the database - called populating the database. Resolve data inconsistencies. Establish security controls. Establish backup and recovery procedures. Train system users.
5. Operation and maintenance. Update database to keep it current - done by application programs. Must include auditing the database for tuning and reorganization.
6. Growth and change. Modify the database to reflect changes in the organization. Monitor database performance for efficiency and customer satisfaction.

DBMS Components.

The figure shown below provides a visual representation of the various components that tend to comprise a DBMS environment.

The DBMS Interface may come in a variety of flavors - a plain telnet session interface or a GUI application provided by the DBMS vendor such as Oracle's Developer and Designer products.
The DBMS has a number of components that are responsible for data integrity, security, performance management, and backup/recovery as well as components to allow a information systems specialist to build a database application.
The DBMS engine interfaces between the Operating System and the DBMS components to store/fetch data to/from the Database and the Repository.

Additional Data Base Implementation Concepts.

Loading Data Bases:

Requires significant resources.
Must be scheduled.
Data Base must be Audited.
Combines data from existing master files.
May require special application programs to accomplish loading.
Must reconcile inconsistencies which will exist.

Specifying Test Procedures:

Careful testing prior to going on-line.
DBA establishes testing policies.

Backup/Recovery: Established by the DBA.
Growth and Change:

Change in size of the database is detected by analyses of storage space utilization - may be automated.
Performance decreases.
Correction: Additional space allocation or reallocation - unload/reload.

Changes in Structure:

Detected by new application requests that be supported.
Correction: Alter the database structure.

Changes in Usage Patterns:

Detect by performance monitoring system by degraded performance.
Correction: Reorganization or alter methods.

Organizing the DBA function.

Selecting the DBA:

Job is more managerial than technical.
Does not have to be a computer expert, but does to have technical expertise.
Prefer someone from within the organization knows the enterprise and its politics.

Transaction Integrity.

When several Physical Transactions must process together, such as saving a Customer Order record to the Orders table and the related Orderline records to the Orderline table, we term this a Logical Transaction.
In this situation, either all of the physical transaction must process or none of them must process.
If one physical transaction fails, we rollback the entire logical transaction.
If all physical transactions succeed, we commit the changes the database.
In modern database programming languages, we often implement processing through the Active-X Data Object model - this is done in Visual Basic.
The start of a transaction is marked by some command such as BEGIN TRANSACTION or through use of a pre-defined method such as the Visual Basic BeginTrans method of the Connection object used to connect to a database.
If a transaction succeeds, the end of the transaction is marked through the use of some command such as END TRANSACTION or a pre-defined method such as the Visual Basic CommitTrans method.
If a transaction fails someplace within the boundaries of the transaction, then changes to the data source are rolled back (that is undone). Some command such as ROLLBACK or a pre-defined method such as the Visual Basic RollbackTrans method of the connection object is used to rollback the logical transaction.
If the application ends or the database connection falls out of scope before a transaction commits, all changes are automatically rolled back.
Usually if a database connection is closed with the Close method while a transaction is in progress, an error occurs.
Below is given an example of a logical transaction written in Visual Basic.

Private Sub ArchiveInvoices()
' Either Archive all invoices or none of them.
ON Error GoTo ErrorHandler ' Turn on Error Handling
' Create the connection objects.
    Dim strSQL As String
    Dim cnAP As Connection
    Dim cmdInvoices As Command
    Set cnAP = New Connection
    Set cmdInvoices = New Command
    cnAP.CursorLocation = adUseClient
' Begin the transaction.
cnAP.BeginTrans
    ' Place commands here to move records to an archive table
    ' and delete them from the Invoices table.
    cnAP.Open "Provider=SQLOLEDB;" & _
        "Server=myserver;" & _
        "Database=Accountspayable;" & _
        "User ID=me;Password=XYZ"
    cmdInvoices.ActiveConnection = cnAP
' Insert records into the Archive table from the Invoices table.
    strSQL = "INSERT INTO InvoiceArchive " & _
        "SELECT * FROM Invoices " & _
        "WHERE InvoiceTotal - " & _
        "PaymentTotal - CreditTotal = 0"
    cmdInvoices.CommandText = strSQL
    cmdInvoices.Execute
    ' Now delete records from the Invoices table.
    strSQL = "DELETE FROM Invoices WHERE " & _
        "InvoiceTotal - PaymentTotal - " & _
        "CreditTotal = 0"
    cmdInvoices.CommandText = strSQL
    cmdInvoices.Execute
    ' End the Transaction by committing it.
    cnAP.CommitTrans
    Exit Sub

Errorhandler:
' Display an error message and check to see if the edit mode is add
' for the recordset and if it is, then cancel the update to throw away
' the blank record. Rollback the transaction that failed.
    DisplayerrorMessage
    cnAP.RollbackTrans
    If rsInvoices.EditMode = adEditAdd Or _
            rsInvoices.EditMode = adEditDelete Then
      rsInvoices.CancelUpdate
    End If
End Sub

Concurrency Control.

Concurrent Access: Two or more users try to access the same data simultaneously.
Interleaved Transactions (Lost Updates): Work through the example shown below to understand what happened to the account balance.

Resource Locking: This involves locking data for one user if an update is planned until the transaction commits. In order to lock resources the DBMS needs to know two things:

1. Intent to Update on the part of the application program.
2. Transaction Boundaries - as defined by the logical transaction boundary commands discussed in the previous section.

Lock Levels: There are several levels for locking records. These include from the individual field up to the entire database.

The figure shown here eliminates the lost transaction update problem.

Note that User 2 must wait for User 1 to complete the transaction before acquiring access to the customer account with the intent to update.

Deadlock: This occurs when two different application programs acquire resources simultaneously that the other program needs. Both programs sit and wait on the additional needed resources.

Deadlock Prevention/Resolution: Use resource locking; Put one transaction on hold until resources needed are free.
Versioning: Each application program/user is restricted to a view of the database at the time that the transaction starts, then when a transaction modifies a record and the DBMS recreates a new record version instead of overwriting the old record. Next the DBMS manages the change by using rollback and restart of a transaction to mesh various transactions together when they affect the same record(s).

DATABASE RECOVERY.

There are four Basic Recovery Facilities. These include:

1. Backup. Whole Database duplication.
2. Journalizing. Maintains an audit train of transactions and database changes. A Journal approach usually includes:

Transaction Log: Record of every transaction that processes.
Data Base Change Log: Before and after images of records.

3. Checkpointing. During the time when a Checkpoint record is written, the DBMS refuses new transactions while those in progress are processed and the journal files are brought up to date and synchronized. The DBMS writes a checkpoint record to the transaction log (this record includes information about non-completed transactions), then writes main memory contents to disk.

An alternative approach: Complete all transactions in progress.
Checkpointing is usually performed automatically.

4. Recovery/Restart Facility. Used to restore DBMS if failure occurs.

Recovery from Failure:

1. Restore/Rerun. Reprocess a day's transactions against a backup copy of data base.

2. Rollbackand Rollforward. The figure shown here provides a diagram of the Rollback and Rollforward process.

Rollback: Use the database with changes and apply "Before Images" from the Database Change Log to recover to an earlier point in time.
Rollforward: Use the database without changes (earlier version) and apply "After Images" from the Database Change Log to recover to a later point in time.

Types of Failures:

1. Aborted Transaction. Rollback is used to recover to a point in time prior to the transaction. The transaction can be reapplied if necessary or if the intent was to abort the transaction, then the rollback resolves all issues.
2. Incorrect data. If incorrect data is entered into the system, several approaches can be used to correct the data errors including:

backward recovery if the error is found soon.
compensating transaction - an offsetting transaction, e.g. if a deposit was incorrectly made to a bank account, a debit against the account can be posted.
restart from checkpoint - rolls forward to the point of the error from the last good checkpoint that was taken.

3. System Failure. If the system fails, e.g., power outage, upon restarting the server, the DBMS recovery engine can use a rollforward from the last good Checkpoint to recovery to the point of failure.
4. Database Destruction. If the database is destroyed, e.g. a server catches fire or a disk drive crashes, then forward recovery from the last good backup can be used to restore to a new server.

End of Notes.