Transcript View File

Data and Database
Administration
Chapter 12
Modern Database Management
Outline





Introduction
Traditional Approaches to data & DB
administration
Effective data and DB administration functions
Data security
Data Recovery and Backup
Introduction

Data are corporate asset just like personnel,
physical & financial resources.

Effective data administration provides support
for managerial decision making at all levels in
the organization.

Ineffective data administration leads to poor
data utilization.
Data & Database Administration

Data Administration


includes overall management of data resources in
an organization, including maintaining corporatewide definitions and standards.
Database Administration

includes physical database design and dealing
with technical issues associated with databases,
such as security enforcement, database
performance, and backup & recovery.
Traditional Data Administration



The data administrator (DA) must be senior-level
manager.
He/she should have both technical and
managerial skills.
Traditional DA functions:




Database planning, analysis, design, implementation,
maintenance & protection.
Establishing procedures for improving database
performance.
Providing education, training and consulting support to
users.
Resolve disputes that my arise when data are
centralized & shared among users.
Traditional Database Administration



The database administrator (DBA) carries through
the standards and procedures established by the
data administrator.
DBA can sometimes take up the role of data
administrator too.
The DBA needs to have a variety job skills. He/she
should:



Have broad technical background including sound
understanding of current H/W architectures.
Have solid understanding of data processing.
Have understanding of database development cycle
Traditional Database Administration
(contd.)



Have strong design and modeling skills (at
conceptual, logical and physical levels).
Have managerial skills to deal with other info system
personnel as the database is analyzed, designed &
implemented.
Provide support for end users involved with database
design and use
Evolving Approaches to Data and
Database Administration

There are no universally accepted data and
database administration structures.

Organizations vary widely in their approaches.

As business practices change, the roles are also
changing. However, there is a core set of data and
database administration functions that must be met
in every organization.

These can be termed as effective data and
database administration functions.
Effective Data and Database Administration
Functions

Data policies, procedures and standards*




Data policies are statements that make explicit the goals of data
administration, e.g., “every user must have a password”
Data procedures are written outlines of actions to be taken in
order to perform a certain activity, e.g., backup & recovery
procedures.
Data standards are explicit conventions and behaviours that are
to be followed and that can be used to evaluate DB quality, e.g.,
naming conventions for DB objects
Planning

involves understanding of organization’s information needs and
contribution in development of information architecture.
* See slide notes for detail
Effective Data and Database Administration
Functions (contd.)

Data conflict resolution


Internal marketing


In a shared data environment, data & database administrators
resolve data conflicts and ownership issues.
Involves enforcing procedures and policies within the organization to
avoid data related problems.
Managing the information repository


Information repositories contain metadata and are replacing data
dictionaries in many organizations.
Info repositories are used throughout the DB system life cycle and
serve the following*: Users, Automated CASE Tools, Applications,
DBMS
* See slide notes for detail
Effective Data and Database Administration
Functions (contd.)

Selection of hardware and software



Installing and upgrading the DBMS



New hardware and software are being developed at a rapid pace.
The data and database administrators are expected to know about
hardware/software architectures to be able to administer both inhouse-developed and off-the-shelf application.
After selecting a DBMS, performance benchmarking must be
conducted before installation.
After installation, periodic update packages (released from vendors)
should be used for regular DBMS upgrades.
Tuning database performance

DBs are dynamic, the initial DB design may be required to change
with time to meet new requirements. Hence, databases must be
rebuilt, reorganized and reindexed to achieve required performance.
Effective Data and Database Administration
Functions (contd.)

Improving database query processing



Managing data security, privacy &integrity


Workload against a database expands over time with increase in data
and number of users.
DBA must tune DB for performance and improve query processing time.
The above must be managed by the database administrators.
Data backup and recovery

DBA must ensure that backup procedures are established and will allow
recovery of necessary data in case of losses through




application failure,
hardware failure,
physical or electrical disaster,
human error or malfeasance.
Evolving Approaches to Data
Administration

Many organizations now have blended the data administration
and DB administration roles together. They emphasize the
capability to build a database quickly, tuning it for max
performance and being able to restore it to production quickly
when problems develop.

Quinlan has suggested changes in data and DB administration
practices that can be made at each stage of database
development life cycle (pg 452,453)

The DBA role will continue to evolve. This role is expected to
become more specialized, e.g., distributed DB/network capacity
planning DBAs, server programming DBAs, data warehousing
DBAs, etc.
Data Security



It is the protection of the data against accidental or
intentional loss, destruction or misuse.
Due to distributed databases and data access
through internet & intranet, data security has
become difficult.
Threats to data security


Threats to data security may be direct threats to databases.
To ensure database security, all parts of the system must
be secure, including





the database,
the network,
the operating system,
the building in which the database physically resides and
the personnel who have system access.
Data Security (Contd.)

Threats to be addressed in a comprehensive
data security plan





Accidental losses, including human error, software and
hardware-caused breaches.
Theft and fraud
Loss of privacy or confidentiality
Loss of data integrity
Loss of availability
Data Security (Contd.)

A comprehensive data security plan will include


establishing administrative policies & procedures, physical
protection and data management software protections.
Security features of data management software are:

Views or sub schemas




they are created on one or more base tables
they produce dynamic result tables at the time of request
they restrict user view of database
Domains, assertions, checks and other integrity controls



they limit the values a field can hold,
they limit the actions that can be preformed on data
they are enforced by DBMS during DB querying & updating
Data Security (Contd.)

Authorization rules


User-defined procedures


They define additional constraints or limitations in using DB
Encryption Procedures


they identify users, restrict access to data and user actions
(e.g., insert, read, modify, delete) against DB objects
They encode/scramble data
Authentication Schemes

They positively identify a person accessing the DB, examples:




biometric devices that can detect personal characteristics
(fingerprints, voice prints, retina, signature)
authentication systems such as Kerberos
Establishing no repudiation by biometrics & messaging
Backup, journaling and check pointing capabilities

They facilitate recovery procedures
Database Recovery and Backup

Databases are often damaged or lost because of system
problems that may be caused by:









Human error,
Hardware failure,
Incorrect or invalid data,
Program errors,
Viruses,
Network failures,
Conflicting transactions or,
Natural disasters
Mechanisms for restoring a database quickly and
accurately after loss or damage are known as Database
recovery.
Basic Recovery Facilities

A DBMS should provide four basic facilities for
backup& recovery of DB

Backup facilities


Journalizing facilities


Maintain an audit trail of transactions and database changes
Checkpoint facilities


Provide periodic backup copies of portions of or entire
database
Allow DBMS to periodically suspend all processing and
synchronize its files and journals
Recovery Manager

Allow DBMS to restore the DB to a correct condition and
restart processing transactions