Database Administration

1
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 is usually a group
• May refer to one person also called a database
  administrator

5
DBA Responsibilities Figure 8.1
6
Policy Formulation and Implementation

• 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
Access Privileges for Sam Figure 8.2
8
Access Privileges for Paige Figure 8.3
9
Access Privileges for Valerie Figure 8.4
10
Attempted Security Violation by Brady Figure 8.5
11
Attempted Security Violation by Paige Figure 8.6
12
DBMS Supplies Security Features Figure 8.7
13
Supplemented Security Features Figure 8.8
14
Policy Formulation and Implementation (cont.)

• 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
Movement of Data to Archive Figure 8.9
16
Policy Formulation and Implementation

• DBMS Evaluation and Selection
• Preparation of checklist
• Evaluation of various DBMSs according to selected
  categories
• Comparison of results
• DBMS Maintenance
• Configuration changes
• Upgrades for new releases
• Problem resolution

17
Policy Formulation and Implementation (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

18
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

19
Interaction Between Test and Production
Systems Figure 8.11
20
Technical Functions (cont.)

• Performance Tuning
• DBA attempts to get best performance with funding
  constraints
• Creating indexes, splitting tables, changing
  table design
• Denormalization

21
Customer Table Figure 8.12
22
Split Customer Table Figure 8.13a
23
Split Customer Table Figure 8.13b
24
Including Part Descriptions in OrderLine Table
Figure 8.14