Summary of DBA Activities
As you examine in the following figure, note that the DBA is the focal point for data/user interaction. The DBA defines and enforces the procedures and standards to be used by programmers and end users during their work with the DBMS. The DBA also verifies that programmer and end-user access meets the required quality and security standards.
The DBA activities portrayed in the above figure suggest the need for a diverse mix of skills. In large companies, such skills are likely to be distributed among several people who work within the DBA function. In small companies, the skills might be the domain of just one individual. The skills can be divided into two categories—managerial and technical.
Database is Corporate Asset
Role of Databases in An Organizations
DBA Evoluation
DA and DBA
The DBA’s Managerial Role:
The DBA must concentrate on the control and planning dimensions of database administration. Therefore, the DBA is responsible for:
• Coordinating, monitoring, and allocating database administration resources: people and data.
• Defining goals and formulating strategic plans for the database administration function.
DBA’s responsibilities are as follows:
I. End-User Support:
The DBA interacts with the end user by providing data and information support services to the organization’s departments. Because end users usually have dissimilar computer backgrounds, end-user support services include:
• Gathering user requirements:
The DBA must work within the end-user community to help gather the data required to identify and describe the end-users’ problems. The gathering of user requirements requires the DBA to develop a precise understanding of the users’ views and needs and to identify present and future information needs.
• Building end-user confidence:
Finding adequate solutions to end-users’ problems increases end-user trust and confidence in the DBA function. The DBA function is also to educate the end-user about the services provided and how those services enhance data stewardship and data security.
• Resolving conflicts and problems:
Finding solutions to end-users’ problems in one department might trigger conflicts with other departments. End users are typically concerned with their own specific data needs rather than with those of others, and they are not likely to consider how their data affect other departments within the organization. When data/information conflicts arise, the DBA function has the authority and responsibility to resolve them.
• Finding solutions to information needs:
The ability and authority to resolve data conflicts enables the DBA to develop solutions that will properly fit within the existing data management framework. The DBA’s primary objective is to provide solutions to address the end-users’ information needs.
• Ensuring quality and integrity of data and applications:
Once the right solution has been found, it must be properly implemented and used. Therefore, the DBA must work with both application programmers and end users to teach them the database standards and procedures required for data quality, access, and manipulation.
• Managing the training and support of DBMS users:
One of the most time-consuming DBA activities is teaching end users how to properly use the database. The DBA must ensure that all users accessing the database have a basic understanding of the functions and use of the DBMS software.
II. Policies, Procedures, and Standards
A prime component of a successful data administration strategy is the continuous enforcement of the policies, procedures, and standards for correct data creation, usage, distribution, and deletion within the database. The DBA must define, document, and communicate the policies, procedures, and standards before they can be enforced.
• Policies are general statements of direction or action that communicate and support DBA goals.
• Standards describe the minimum requirements of a given DBA activity; they are more detailed and specific than policies. In effect, standards are rules that are used to evaluate the quality of the activity. For example, standards define the structure of application programs and the naming conventions programmers must use.
• Procedures are written instructions that describe a series of steps to be followed during the performance of a given activity. Procedures must be developed within existing working conditions, and they must support and enhance that environment.
The DBA must define, communicate, and enforce procedures that cover areas such as:
• End-user database requirements gathering: What documentation is required? What forms must be used?
• Database design and modeling: What database design methodology is to be used (normalization or object-oriented methodology)? What tools are to be used (CASE tools, data dictionaries, UML or ER diagrams)?
• Documentation and naming conventions: What documentation must be used in the definition of all data elements, sets, and programs that access the database?
• Design, coding, and testing of database application programs:
• The DBA must define the standards for application program coding, documentation, and testing. The DBA standards and procedures are given to the application programmers, and the DBA must enforce those standards.
• Database software selection: The selection of the DBMS package and any other software related to the database must be properly managed.
• Database security and integrity: The DBA must define the policies governing security and integrity.
• Database security is especially crucial: Security standards must be clearly defined and strictly enforced.
• Database backup and recovery: Database backup and recovery procedures must include the information necessary to guarantee proper execution and management of the backups.
• Database maintenance and operation: The DBMS’s daily operations must be clearly documented. Operators must keep job logs, and they must write operator instructions and notes. Such notes are helpful in pinpointing the causes and solutions of problems. Operational procedures must also include precise information concerning backup and recovery procedures.
• End-user training: A full-featured training program must be established within the organization, and procedures governing the training must be clearly specified. The objective is to clearly indicate who does what, when, and how. Each end user must be aware of the type and extent of the available training methodology.
III. Data Security, Privacy, and Integrity
The security, privacy, and integrity of the data in the database are of great concern to DBAs who manage current DBMS installations. Technology has pointed the way to greater productivity through information management. Technology has also resulted in the distribution of data across multiple sites, thus making it more difficult to maintain data control, security, and integrity. The multiple-site data configuration has made it imperative that the DBA use the security and integrity mechanisms provided by the DBMS to enforce the database administration policies defined in the previous section. In addition, DBAs must team up with Internet security experts to build security mechanisms to safeguard data from possible attacks or unauthorized access.
IV. Data Backup and Recovery
When data are not readily available, companies face potentially ruinous losses. Therefore, data backup and recovery procedures are critical in all database installations. The DBA must also ensure that the data in the database can be fully recovered in case of physical data loss or loss of database integrity.
Data loss can be partial or total. A partial loss is caused by a physical loss of part of the database or when part of the database has lost integrity. A total loss might mean that the database continues to exist but its integrity is entirely lost or that the entire database is physically lost.
The management of database security, integrity, backup, and recovery is so critical that many DBA departments have created a position called the database security officer (DSO). In large organizations, the DSO’s activities are often classified as disaster management. The backup and recovery measures must include at least the following activities.
• Periodic data and applications backups:
Some DBMSs include tools to ensure backup and recovery of the data in the database. The DBA should use those tools to render the backup and recovery tasks automatic. Products such as IBM’s DB2 allow the creation of different backup types: full, incremental, and concurrent. A full backup, also known as a database dump, produces a complete copy of the entire database. An incremental backup produces a backup of all data since the last backup date; a concurrent backup takes place while the user is working on the database.
• Proper backup identification:
Backups must be clearly identified through detailed descriptions and date information, thus enabling the DBA to ensure that the correct backups are used to recover the database. The most common backup medium has traditionally been tape; the storage and labeling of tapes must be done diligently by the computer operators, and the DBA must keep track of tape currency and location.
• Convenient and safe backup storage:
There must be multiple backups of the same data, and each backup copy must be stored in a different location. The storage locations must include sites inside and outside the organization. The storage locations must be properly prepared and may include fire-safe and quakeproof vaults, as well as humidity and temperature controls. The DBA must establish a policy to respond to two questions: (1) Where are the backups to be stored? (2) How long are backups to be stored?
• Physical protection of both hardware and software:
Protection might include the use of closed installations with restricted access, as well as preparation of the computer sites to provide air conditioning, backup power, and fire protection. Physical protection also includes the provision of a backup computer and DBMS to be used in case of emergency.
• Personal access control to the software of a database installation:
Multilevel passwords and privileges and hardware and software challenge/response tokens can be used to properly identify authorized users of resources.
Insurance coverage for the data in the database:
The DBA or security officer must secure an insurance policy to provide financial protection in the event of a database failure.
V. Data Distribution and Use
Data are useful only when they reach the right users in a timely fashion. The DBA is responsible for ensuring that the data are distributed to the right people, at the right time, and in the right format. The DBA’s data distribution and use tasks can become very time-consuming, especially when the data delivery capacity is based on a typical applications programming environment, where users depend on programmers to deliver the programs to access the data in the database. Current data distribution philosophy makes it easy for authorized end users to access the database. One way to accomplish that task is to facilitate the use of a new generation of more sophisticated query tools.
The DBA’s Technical Role:
The DBA’s technical role requires a broad understanding of DBMS functions, configuration, programming languages, data modeling and design methodologies, and so on. For example, the DBA’s technical activities include the selection, installation, operation, maintenance, and upgrading of the DBMS and utility software, as well as the design, development, implementation, and maintenance of the application programs that interact with the database.
The technical aspects of the DBA’s job are rooted in the following areas of operation:
1. Evaluating, Selecting, and Installing the DBMS and Utilities
One of the DBA’s first and most important technical responsibilities is selecting the database management system, utility software, and supporting hardware to be used in the organization. Therefore, the DBA must develop and execute a plan for evaluating and selecting the DBMS, utilities, and hardware. That plan must be based primarily on the organization’s needs rather than on specific software and hardware features.
To match DBMS capability to the organization’s needs, the DBA would be wise to develop a checklist of desired DBMS features. That DBMS checklist should address at least these issues:
DBMS model: Are the company’s needs better served by a relational, object-oriented, or object/relational DBMS? If a data warehouse application is required, should a relational or multidimensional DBMS be used? Does the DBMS support star schemas?
• DBMS storage capacity: What maximum disk and database size is required? How many disk packages must be supported? How many tape units are needed? What are other storage needs?
• Application development support: Which programming languages are supported? What application development tools (database schema design, data dictionary, performance monitoring, and screen and menu painters) are available? Are end-user query tools provided? Does the DBMS provide Web front-end access?
• Security and integrity: Does the DBMS support referential and entity integrity rules, access rights, and so on? Does the DBMS support the use of audit trails to spot errors and security violations? Can the audit trail size be modified?
• Backup and recovery: Does the DBMS provide some automated backup and recovery tools? Does the DBMS support tape, optical disc, or network-based backups? Does the DBMS automatically back up the transaction logs?
• Concurrency control: Does the DBMS support multiple users? What levels of isolation (table, page, row) does the DBMS offer? How much manual coding is needed in the application programs?
• Performance: How many transactions per second does the DBMS support? Are additional transaction processors needed?
• Database administration tools: Does the DBMS offer some type of DBA management interface? What type of information does the DBA interface provide? Does the DBMS provide alerts to the DBA when errors or security violations occur?
• Interoperability and data distribution: Can the DBMS work with other DBMS types in the same environment? What coexistence or interoperability level is achieved? Does the DBMS support READ and WRITE operations to and from other DBMS packages? Does the DBMS support a client/server architecture?
• Portability and standards: Can the DBMS run on different operating systems and platforms? Can the DBMS run on mainframes, midrange computers, and personal computers? Can the DBMS applications run without modification on all platforms? What national and industry standards does the DBMS follow?
• Hardware: What hardware does the DBMS require?
• Data dictionary: Does the DBMS have a data dictionary? If so, what information is kept in it? Does the DBMS interface with any data dictionary tool? Does the DBMS support any CASE tools?
• Vendor training and support: Does the vendor offer in-house training? What type and level of support does the vendor provide? Is the DBMS documentation easy to read and helpful? What is the vendor’s upgrade policy?
• Available third-party tools: What additional tools are offered by third-party vendors (query tools, data dictionary, access management and control, and/or storage allocation management tools)?
• Cost: What costs are involved in the acquisition of the software and hardware? How many additional personnel are required, and what level of expertise is required of them? What are the recurring costs? What is the expected payback period?
2. Designing and Implementing Databases and Applications:
The DBA function also provides data-modeling and design services to end users. Such services are often coordinated with an application development group within the data-processing department. Therefore, one of the primary activities of a DBA is to determine and enforce standards and procedures to be used. Once the appropriate standards and procedures framework are in place, the DBA must ensure that the database-modeling and design activities are performed within the framework.
The DBA also works with applications programmers to ensure the quality and integrity of database design and transactions. Such support services include reviewing the database application design to ensure that transactions are:
• Correct: The transactions mirror real-world events.
• Efficient: The transactions do not overload the DBMS.
• Compliant: Complies with integrity rules and standards.
3. Testing and Evaluating Databases and Applications
The DBA must also provide testing and evaluation services for all of the database and end-user applications. Those services are the logical extension of the design, development, and implementation services. Clearly, testing procedures and standards must already be in place before any application program can be approved for use in the company.
The testing and evaluation of a database application cover all aspects of the system—from the simple collection and creation of data to its use and retirement. The evaluation process covers:
• Technical aspects of both the applications and the database. Backup and recovery, security and integrity, use of SQL, and application performance must be evaluated.
• Evaluation of the written documentation to ensure that the documentation and procedures are accurate and easy to follow.
• Observance of standards for naming, documenting, and coding.
• Data duplication conflicts with existing data.
• The enforcement of all data validation rules.
4. Operating the DBMS, Utilities, and Applications
DBMS operations can be divided into four main areas:
• System support: System support activities cover all tasks directly related to the day-to-day operations of the DBMS and its applications. These activities include filling out job logs, changing tape, and verifying the status of computer hardware, disk packages, and emergency power sources. System-related activities include periodic, occasional tasks such as running special programs and resource configurations for new and/or upgraded versions of database applications.
• Performance monitoring and tuning: Performance monitoring and tuning require much of the DBA’s attention and time. These activities are designed to ensure that the DBMS, utilities, and applications maintain satisfactory performance levels. To carry out the performance monitoring and tuning tasks, the DBA must:
Establish DBMS performance goals.
Monitor the DBMS to evaluate whether the performance objectives are being met.
Isolate the problem and find solutions (if performance objectives are not met).
Implement the selected performance solutions.
Query-optimization routines are usually integrated into the DBMS package, allowing few tuning options. Query optimization routines are oriented toward improving concurrent access to the database.
During DBMS performance tuning, the DBA must also consider available storage resources in terms of both primary and secondary memory. The allocation of storage resources is determined when the DBMS is configured. Storage configuration parameters can be used to determine:
• The number of databases that may be opened concurrently.
• The number of application programs or users supported concurrently.
• The amount of primary memory (buffer pool size) assigned to each database and each database process.
• The size and location of the log files.
• Backup and recovery: Backup and recovery activities are of primary concern during the DBMS operation. The DBA must establish a schedule for backing up database and log files at appropriate intervals. Backup frequency is dependent on the application type and on the relative importance of the data. All critical system components—the database, the database applications, and the transaction logs—must be backed up periodically. Database recovery after a media or systems failure requires application of the transaction log to the correct database copy. The DBA must plan, implement, test, and enforce a “bulletproof” backup and recovery procedure.
• Security auditing and monitoring: Security auditing and monitoring assumes the appropriate assignment of access rights and the proper use of access privileges by programmers and end users. The technical aspects of security auditing and monitoring involve creating users, assigning access rights, using SQL commands to grant and revoke access rights to users and database objects, and creating audit trails to discover security violations or attempted violations. The DBA must periodically generate an audit trail report to determine whether there have been actual or attempted security violations—and, if so, from what locations, and if possible, by whom.
5. Training and Supporting Users:
Training people to use the DBMS and its tools is included in the DBA’s technical activities. In addition, the DBA provides or secures technical training in the use of the DBMS and its utilities for the applications programmers. Applications programmer training covers the use of the DBMS tools as well as the procedures and standards required for database programming.
6. Maintaining the DBMS, Utilities, and Applications:
The maintenance activities of the DBA are an extension of the operational activities. Maintenance activities are dedicated to the preservation of the DBMS environment. Periodic DBMS maintenance includes management of the physical or secondary storage devices. One of the most common maintenance activities is reorganizing the physical location of data in the database. The reorganization of a database might be designed to allocate contiguous disk-page locations to the DBMS to increase performance.
Maintenance activities also include upgrading the DBMS and utility software. The upgrade might require the installation of a new version of the DBMS software or an Internet front-end tool. Or it might create an additional DBMS gateway to allow access to a host DBMS running on a different host computer.
