Database 9
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
*
*