Transcript chapter08

Concepts of Database
Management, Fifth Edition
Chapter 8:
Database Administration
Objectives
 Discuss
the need for database administration
(DBA)
 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
Concepts of Database Management, 5th Edition
2
Objectives
 Discuss
the DBA’s technical responsibilities for
database design, testing, and performance
tuning
Concepts of Database Management, 5th Edition
3
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
Concepts of Database Management, 5th Edition
4
Figure 8.1:
DBA Responsibilities
Concepts of Database Management, 5th Edition
5
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
Concepts of Database Management, 5th Edition
6
Figure 8.2: Access Privileges for Sam
Concepts of Database Management, 5th Edition
7
Figure 8.3:
Access
Privileges for
Paige
Concepts of Database Management, 5th Edition
8
Figure 8.4:
Access
Privileges for
Valerie
Concepts of Database Management, 5th Edition
9
Figure 8.5: Attempted Security Violation by Brady
Concepts of Database Management, 5th Edition
10
Figure 8.6:
Attempted
Security
Violation by
Paige
Concepts of Database Management, 5th Edition
11
Figure 8.7: DBMS Supplies Security Features
Concepts of Database Management, 5th Edition
12
Figure 8.8:
Supplemented Security Features
Concepts of Database Management, 5th Edition
13
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
Concepts of Database Management, 5th Edition
14
Figure 8.9:
Movement of Data
to Archive
Concepts of Database Management, 5th Edition
15
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
Concepts of Database Management, 5th Edition
16
Other Database Administrative
Functions (Con’t.)

Replication and distributed databases

Limitations

Documentation and training

Vendor support

Performance

Portability

Cost

Future plans

Other considerations
Concepts of Database Management, 5th Edition
17
Other Database Administrative
Functions (Con’t.)
 DBMS
Maintenance

Configuration changes

Upgrades for new releases

Problem resolution
Concepts of Database Management, 5th Edition
18
Other Database Administrative
Functions (Con’t.)
 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
Concepts of Database Management, 5th Edition
19
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
Concepts of Database Management, 5th Edition
20
Figure 8.11:
Interaction
Between Test
and Production
Systems
Concepts of Database Management, 5th Edition
21
Technical Functions (con’t.)
 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
Concepts of Database Management, 5th Edition
22
Figure 8.12: Customer Table
Concepts of Database Management, 5th Edition
23
Figure 8.13a: Split Customer Table
Concepts of Database Management, 5th Edition
24
Figure 8.13b: Split Customer Table
Concepts of Database Management, 5th Edition
25
Figure 8.14:
Including Part Descriptions in OrderLine Table
Concepts of Database Management, 5th Edition
26
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
Concepts of Database Management, 5th Edition
27
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
Concepts of Database Management, 5th Edition
28
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
Concepts of Database Management, 5th Edition
29