Concepts of Database Management, Fifth Edition

1
Concepts of Database Management, Fifth Edition

• Chapter 8
• Database Administration

2
Objectives

• Discuss the need for database administration
  (DBA)
• Explain the DBAs responsibilities in formulating
  and enforcing database policies for access
  privileges, security, disaster planning, and
  archiving
• Discuss the DBAs administrative responsibilities
  for DBMS evaluation and selection, DBMS
  maintenance, data dictionary management, and
  training

3
Objectives

• Discuss the DBAs technical responsibilities for
  database design, testing, and performance tuning

4
Database Administration (DBA)

• Resource that supervises database and use of DBMS
• DBA functions are usually performed by a group
• May refer to one person also called a database
  administrator

5
Figure 8.1 DBA Responsibilities
6
Policy Formulation and Enforcement

• Access Privileges
• Users access the database only in ways in which
  they are entitled
• Security
• Access restrictions ensure database is secure
• Passwords, encryption, and views implement
  security
• Effective password protection critical

7
Figure 8.2 Access Privileges for Sam
8
Figure 8.3 Access Privileges for Paige
9
Figure 8.4 Access Privileges for Valerie
10
Figure 8.5 Attempted Security Violation by Brady
11
Figure 8.6 Attempted Security Violation by Paige
12
Figure 8.7 DBMS Supplies Security Features
13
Figure 8.8 Supplemented Security Features
14
Policy Formulation and Enforcement

• Disaster Planning
• Protection against physical damage
• Use DBMS backup and recovery tools with
  supplements where needed
• Disaster recovery plan
• Archiving
• Place to keep corporate data
• Mass-storage device used for archiving

15
Figure 8.9 Movement of Data to Archive
16
Other Database Administrative Functions

• DBMS Evaluation and Selection
• Data definition
• Data restructuring
• Nonprocedural languages
• Procedural languages
• Data dictionary
• Concurrent update
• Backup and recovery
• Security
• Integrity

17
Other Database Administrative Functions (Cont.)

• Replication and distributed databases
• Limitations
• Documentation and training
• Vendor support
• Performance
• Portability
• Cost
• Future plans
• Other considerations

18
Other Database Administrative Functions (Cont.)

• DBMS Maintenance
• Configuration changes
• Upgrades for new releases
• Problem resolution

19
Other Database Administrative Functions (Cont.)

• Data Dictionary Management
• Is essentially database catalog with wider range
  of information
• Updates the data dictionary
• Establishes naming conventions for tables,
  fields, etc.
• Creates data integrity rules
• Training
• How to use DBMS and database

20
Technical Functions

• Database Design
• Establishes sound methodology for database design
• Creates documentation standards
• Manages changes
• Testing
• DBA controls production system
• Grants privileges to programmers to modify and
  test software

21
Figure 8.11 Interaction Between Test and
Production Systems
22
Technical Functions (cont.)

• Performance Tuning
• DBA attempts to get best performance with funding
  constraints
• Create indexes, splitting tables, changing table
  design
• Denormalization - process that creates a table
  not in 3NF to optimize performance
• JOINs may be too costly to just scan data

23
Figure 8.12 Customer Table
24
Figure 8.13a Split Customer Table
25
Figure 8.13b Split Customer Table
26
Figure 8.14 Including Part Descriptions in
OrderLine Table
27
Summary

• Database administration (DBA) - person or group
  responsible for supervising database and use of
  database management system (DBMS)
• DBA formulates and enforces policies about
• Users who can access database
• Portions of database they may access
• In what manner they can access database
• DBA formulates and enforces policies about
  security

28
Summary

• DBA
• Creates and implements backup and recovery
  procedures
• Formulates and enforces policies that govern the
  management of an archive for data
• Leads effort to evaluate and select new DBMS
• Installs and maintains DBMS after it has been
  selected and procured
• Maintains data dictionary, establishes naming
  conventions for its contents, and provides
  information from it to others in the organization

29
Summary

• DBA
• Coordinates and provides database and DBMS
  training
• Verifies all information-level database designs,
  completes all physical-level database designs,
  and creates documentation standards
• Controls production system, which is accessible
  only to authorized users
• Tunes database design to improve performance