The dba creates security policies and procedures.

Database Administration

*

Objectives

Discuss the need for database administration

Explain the DBA’s responsibilities in formulating and enforcing database policies for access privileges, security, disaster planning, and archiving

Discuss the DBA’s administrative responsibilities for DBMS evaluation and selection, DBMS maintenance, data dictionary management, and training

Discuss the DBA’s technical responsibilities for database design, testing, and performance tuning

*

*

Introduction

FIGURE 8-1: DBA responsibilities

*

*

Database Policy Formulation and Enforcement

DBA

Formulates database policies

Communicates policies to users

Enforces policies

Policies

Access privileges

Security

Disaster planning

Archiving

*

*

Access Privileges

DBA

Determines access privileges for all users

Enters appropriate authorization rules in DBMS

SQL GRANT statement

Access privilege policy

Documented by DBA

Approved by top-level management

Communicated by DBA to all users

*

*

Access Privileges (continued)

FIGURE 8-2: Permitted and denied access privileges for Sam

*

*

Access Privileges (continued)

FIGURE 8-4: Permitted and denied access privileges for Valerie

*

*

Security

Prevention of unauthorized access, intentional or accidental, to database

DBA

Creates security policies and procedures

Obtains management approval of policies and procedures

Distributes policies and procedures to authorized users

*

*

Security (continued)

DBMS’s security features

Encryption

Authentication

Authorizations

Views

Additional security programs may be created or purchased

Monitoring of database usage to detect security violations

*

*

Security (continued)

FIGURE 8-5: Attempted security violation by Brady, who’s not an authorized user

*

*

Security (continued)

FIGURE 8-6: Attempted security violation by Paige, who’s authorized to access some customer data but is not authorized to access customer balances

*

*

Disaster Planning

Damage from physical incidents

Software/hardware/electrical

Natural disasters

Disaster recovery plan: ongoing and emergency actions and procedures to ensure data availability if a disaster occurs

Hard drive failures

Redundant array of inexpensive/independent drives (RAID): database updates replicated to multiple hard drives

*

*

Disaster Planning (continued)

Electrical power loss

Uninterruptible power supply (UPS): power source and power generator

Duplicate backup systems

Hot site: completely equipped with duplicate hardware, software, and data

Can switch to hot site in minutes or hours

Warm site: duplicate hardware and software but not data

Takes longer to start processing

*

*

Archiving

Governmental laws and regulations, for example:

Sarbannes-Oxley Act

Patriot Act

HIPAA

Auditing and financial requirements

Data archive or archive: place where record of certain corporate data is kept

Stored on mass storage devices

Copies of archives and database backups must be stored off-site

*

*

Archiving (continued)

FIGURE 8-7: Movement of order 21617 from the database to the archive

*

*

Other Database Administration Functions

DBMS evaluation and selection

DBMS maintenance

Data dictionary management

Training

*

*

DBMS Evaluation and Selection

Data definition

Data restructuring

Nonprocedural languages

Procedural languages

Data dictionary

Concurrent update

Shared lock

*

*

DBMS Evaluation and Selection (continued)

Backup and recovery

Security

Integrity

Replication and distributed databases

Limitations

Local area network (LAN)

Documentation and training

Context-sensitive help

*

*

DBMS Evaluation and Selection (continued)

Vendor support

Performance

Portability

Intranet

Cost

Future plans

Other considerations

*

*

DBMS Maintenance

Installation of DBMS

Configuration changes

Upgrades for new releases

Problem resolution

Special one-time processing needs

*

*

Data Dictionary Management

Data dictionary is like database catalog, but with wider range of information

Establishes naming conventions for tables, fields, indexes, etc.

Creates data definitions for tables

Creates data integrity rules and user views

Updates data dictionary

Creates and distributes reports from data dictionary

*

*

Training

Training in using DBMS and accessing database

Training of technical staff responsible for developing and maintaining database applications

If training is provided by vendor of DBMS, DBA handles scheduling of training

*

*

Technical Functions

Database design

Testing

Performance tuning

*

*

Database Design

Establishes sound methodology for database design

Does physical-level design

Creates documentation standards

Reviews changes to requirements and manages modifications to database

*

*

Testing

Production system or live system: hardware, software, and database for users

DBA grants access to production system only to authorized users, except for:

Troubleshooting a problem

Addition of new or modified programs

Test system or sandbox: used by programmers to develop new programs and modify existing programs

*

*

Testing (continued)

FIGURE 8-9: DBA controls the interaction between the test and production systems

*

*

Performance Tuning

DBA attempts to get best performance within funding constraints

Creating and deleting indexes

Splitting tables

Changing table design

Denormalizing converts a table in third normal form to a table not in third normal form

Improved performance

*

*

Performance Tuning (continued)

FIGURE 8-10: Customer table for Premiere Products

*

*

Performance Tuning (continued)

FIGURE 8-11: Result of splitting the Customer table into two tables

*

*

Performance Tuning (continued)

FIGURE 8-11: Result of splitting the Customer table into two tables (continued)

*

*

Performance Tuning (continued)

FIGURE 8-12: Including part descriptions in the OrderLine table, which creates a first normal form table

*

*

Summary

Database administrator (DBA) is responsible for supervising the database and use of the DBMS

DBA formulates and enforces policies about which users can access database, portions they may access, and the manner in which they can access it

DBA formulates and enforces policies about security by using DBMS’s security features, special security programs, and monitoring database usage

DBA creates and implements backup and recovery procedures as part of a disaster recovery plan

*

*

Summary (continued)

DBA formulates and enforces policies that govern management of an archive for data

DBA leads evaluation and selection of new DBMS

DBA installs and maintains DBMS

DBA maintains data dictionary, establishes naming conventions for its content, and provides information from it to others

DBA provides database and DBMS training and coordinates and schedules training by outside vendors

*

*

Summary (continued)

DBA verifies all information-level database designs, completes all physical-level database designs, and creates documentation standards; also evaluates changes in requirements

DBA controls production system, which is accessible only to authorized users; other than under exceptional situations, programmers access a separate test system

DBA tunes database design to improve performance; includes creating and deleting indexes, splitting tables, and denormalizing tables

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com