What are the functions of the Database Administrator

What are the functions of the Database Administrator?

Before trying to understand the functions of the database administrator, it is necessary to first learn the three different functional levels needed to maintain a database. These levels are the data administration (DA), the database administration (DBA), and database steward.

What is a data administrator?

A data administration (also known as a database administration manager, data architect, or information center manager) is a high level function responsible for the overall management of data resources in an organization. In order to perform its duties, the DA must know a good deal of system analysis and programming.

These are the functions of a data administrator (not to be confused with database administrator functions):

1. Data policies, procedures, standards

2. Planning- development of organization's IT strategy, enterprise model, cost/benefit model, design of database environment, and administration plan.

3. Data conflict (ownership) resolution

4. Data analysis- Define and model data requirements, business rules, operational requirements, and maintain corporate data dictionary

5. Internal marketing of DA concepts

6. Managing the data repository

What is a database administrator?

Database administration is more of an operational or technical level function responsible for physical database design, security enforcement, and database performance. Tasks include maintaining the data dictionary, monitoring performance, and enforcing organizational standards and security.

What is a database steward?

A database steward is an administrative function responsible for managing data quality and assuring that organizational applications meet the enterprise goals. It is a connection between IT and business units. Data quality issues include security and disaster recovery, personnel controls, physical access controls, maintenance controls, and data protection and privacy. For example, in order to increase security the database steward can have control over who can gain access to the data base by assigning a specific privileges to users.

Now that you have an idea of the different responsibilities involved in maintaining a database, we can list and describe the functions of a database administrator.

What are the functions of a database administrator?

1. Selection of hardware and software

Keep up with current technological trends
Predict future changes
Emphasis on established off the shelf products

2. Managing data security and privacy

Protection of data against accidental or intentional loss, destruction, or misuse
Firewalls
Establishment of user privileges
Complicated by use of distributed systems such as internet access and client/ server technology.

How many major threats to database security can you think of?

1. Accidental loss due to human error or software/ hardware error.
2. Theft and fraud that could come from hackers or disgruntled employees.
3. Improper data access to personal or confidential data.
4. Loss of data integrity.
5. Loss of data availability through sabotage, a virus, or a worm.

3. Managing Data Integrity

Integrity controls protects data from unauthorized use
Data consistency
Maintaining data relationship
Domains- sets allowable values
Assertions- enforce database conditions

4. Data backup

We must assume that a database will eventually fail
Establishment procedures
- how often should the data be back-up?
- what data should be backed up more frequently?
- who is responsible for the back ups?
Back up facilities
- automatic dump- facility that produces backup copy of the entire database
- periodic backup- done on periodic basis such as nightly or weekly
- cold backup- database is shut down during backup
- hot backup- a selected portion of the database is shut down and backed up at a given time
- backups stored in a secure, off-site location

5. Database recovery

Application of proven strategies for reinstallation of database after crash
Recovery facilities include backup, journalizing, checkpoint, and recovery manager

If there are back up facilities, are there also journalizing, checkpoint, and recovery facilities?

Yes

Journalizing facilities include:
- audit trail of transactions and database updates
- transaction log which records essential data for each transaction processed against the database
- database change log shows images of updated data. The log stores a copy of the image before and after modification.
Checkpoint facilities:
- when the DBMS refuses to accept a new transaction, the system is in a quiet state
- database and transactions are synchronized
- allows the recovery manager to resume processing from a short period instead of repeating the entire day
Recovery and Restart Procedures
- switch- mirrored databases
- restore/rerun- reprocess transactions against the backup
- transaction integrity- commit or abort all transaction changes
- backward recovery (rollback)- apply before images
- forward recovery (roll forward)- apply after images (preferable to restore/rerun)

6. Tuning database performance

Set installation parameters/ upgrade DBMS
Monitor memory and CPU usage
Input/ output contention
- user striping
- distribution of heavily accessed files
Application tuning by modifying SQL code in applications

7. Improving query processing performance

Are there any shared administration functions?

Yes

These are share administration functions

1. Database design

DA is responsible for logical design
DBA is responsible for the external model design (subschemas), the physical design (construction), and for designing integrity controls

2. Database implementation

DBA
- establish security controls
- supervise database loading
- specify test procedures
- develop programming standards
- establish back up/ recovery procedures
Both
- specify access policies
- user training

3. Operations and maintenance

DBA
- monitor database performance
- tune and reorganize databases as needed
- enforce standards and procedures
Both
- support users

4. Growth and change

Both
- implement change-control procedures
- plan for growth and change
- evaluate new technologies

New functions

1. Data warehouse administration

New function due to the increase use of data warehousing
- (massively) integrated decision support databases from various sources
Emphasis on integration and coordination of data and metadata from multiple databases
Specific functions
- support decision-oriented applications
- manage data warehouse (exponential) growth
- establish service level agreements

Review Questions: Multiple Choice

1. A person who takes overall responsibility for data, metadata, and the policies about data use is the _______.

    A. Data administrator
    B. Database administrator
    C. Database steward
    D. Both A and B.

2. The _________ has a more hands-on, physical involvement with the database than the ____________.

    A. Data administrator; Database administrator
    B. Database administrator; Data administrator
    C. Database steward; Database administrator
    D. None of the above

3. Before- and after-images of records that have been modified by transactions are in a ____.

    A. database change log
    B. transaction log
    C. checkpoint
    D. journalizing facility

4. Which is NOT one of the basic facilities for backup and recovery of a database?

    A. Checkpoint facility
    B. Recovery Manager
    C. Biometric Device
    D. Journalizing facilities.

5. Which of the following is the goal of database security?

    A. To protect primarily against accidental or intentional loss of data
    B. To protect against misuse of data
    C. To protect against destruction of data
    D. All of the above

Answers:

1. A
2. B
3. A
4. C
5. D

Questions:

1. List the functions of a database administrator.

    1. Selection of hardware and software
    2. Managing data security and privacy
    3. Managing data integrity
    4. Data back up
    5. Database recovery
    6. Tuning database performance
    7. Improving query processing performance

2. What impact has the internet caused to the management of data security.

    As a result of the internet, managing data security effectively has become more difficult because access to data has become open through the internet and corporate intranets.

3. What are five major threats to data security?

    1. Accidental loss due to human error or software/ hardware error.
    2. Theft and fraud that could come from hackers or disgruntled employees.
    3. Improper data access to personal or confidential data.
    4. Loss of data integrity.
    5. Loss of data availability through sabotage, a virus, or a worm.

4. Explain the function of a recovery manager?

The recovery manager is a module of the DBMS which restores the database to a correct condition when a failure occurs and which resumes processing user requests.

5. What is the difference between backward (rollback) and forward (roll forward) recovery?

The rollback is the back out or undo of unwanted changes to the database. Before-images of the records that have been changed are applied to the database, and the database is returned to an earlier state. Used to reverse the changes made by transactions that have been aborted or terminated abnormally.

Roll forward is the technique that starts with an earlier copy of the database. After-images (the results of good transactions) are applied to the database, and the database is quickly moved forward to a later state.

References:

http://perjasa.org.my/mba/dba.html

http://myphlip1.pearsoncmg.com/phlip/mplistres1.cfm?vbookid=321

http://www.mysql.com/doc/en/MySQL_Database_Administration.html