week 7 lecture part 2 introduction to database administration samuel s. connsamuel s. conn, asst...

Week 7 Lecture Part 2

Introduction to Database Administration

Samuel S. Conn, Asst Professor

2

In this lecture, you will: Learn that data are a valuable business asset that

requires careful management Learn why and how the database plays a critical role

in an organization Learn that the introduction of a DBMS has important

technological, managerial, and cultural organizational consequences

Learn what the database administrator's managerial and technical roles are

Be introduced to several database administration tools

Examine various database administration strategies See how various database administration technical

tasks are performed with Oracle

3

Data as a Corporate Asset

Raw material used to produce information

Valuable resource Information

Timely and accurate data can trigger beneficial actions

Basis for knowledge Used in decision making

4

Data-Information-Decision Cycle

Figure 16.1

5

Databases in an Organization

DBMS facilitates Interpretation and presentation of data Distribution of data and information Data preservation and monitoring Control over data duplication and use

Supports managerial decision making Top Middle Operational

6

Introduction of Database

Big organizational change and challenge

Process with three primary aspects Technological Managerial Cultural

Must focus on organizational needs

7

Roots in decentralized file systems Electronic Data Processing (EDP) or Data

Processing (DP) Department Information systems (IS) department

Evolution of Database Administration

Figure 16.2

8

Database administration created to handle complex data management tasks

Database administrator (DBA) responsible for control of database

Factors affecting DBA placement in organization Management style Size and complexity of operations Geographic distribution of facilities

General coordinator of all DBAs is systems administrator (SYSADM)

Data administrator (DA)

Evolution of Database Administration (Con’t.)

9

Placement of DBA Function

Figure 16.3

10

DBA Functional Organization

Figure 16.4

11

DA Versus DBA

Table 16.1

12

Summary of DBA Activities

Figure 16.6

13

DBA Skills

Table 16.2

14

DBA Activities and Services

Table 16.3

15

DBA Service: End-User Support

User requirements gathering Conflict and problem resolution Finding solutions to information

needs Ensuring quality and integrity of

applications and data Building end-user confidence Managing the training and support of

DBMS users

16

Policies General statements of direction or action Communicate and support DBA goals

Standards More detailed and specific than policies Describe minimum requirement of DBA activity

Procedures Written instructions that describe steps for

activity Support and enhance work environment

DBA Service: Policies, Procedures, & Standards

17

Authorization management User access management View definition DBMS utilities access control DBMS usage monitoring

Security breaches Preserved integrity

• Data recovery may not be necessary Corrupted integrity

• Database must be recovered

DBA Service: Security, Privacy, & Integrity

18

Database security officer (DSO) Disaster management

Periodic data and applications backups Proper backup identification Convenient and safe backup storage Physical protection Personal access control Insurance coverage for the data in the database

Data recovery and contingency plans must be tested and evaluated

Backup and recovery may not cover all aspects of IS

DBA Service: Data Backup and Recovery

19

Data useful in right hands at right time

Current philosophy makes authorized data access easier

Sophisticated query tools Web front ends Data democracy Cautions due to improper use of

data

DBA Service: Data Distribution and Use

20

DBMS and utilities evaluation, selection, and installation

Design and implementation of databases and applications

Testing and evaluation of databases and applications

Operation of the DBMS, utilities, and applications

Training and supporting users Maintenance of the DBMS, utilities, and

applications

DBA’s Technical Role

21

Integrated data dictionary Included with the DBMS

Stand-alone data dictionary From third-party vendor

Active data dictionary Automatically updated by the DBMS

Passive data dictionary Created and updated by a batch

process

DB Administrative Tools: Data Dictionary

22

Data elements in all tables Tables defined in all databases Indexes defined for each database table Defined database specifics End users and administrators of

database Programs that access database Access authorizations for all users Relationships among data elements

DB Administrative Tools: Data Dictionary (Con’t.)

23

CASE is Computer-Aided Software Engineering

Provides automated framework for Systems Development Life Cycle

Classifications Front-end CASE tools support planning,

analysis, and design Back-end CASE tools support coding

and implementation

DB Administrative Tools: CASE

24

Reduction in development time and costs

Automation of the SDLC Standardization of the systems

development methodologies Easier maintenance of application

systems developed with CASE tools Improves communications among the

DBA, applications designers, and end users

DB Administrative Tools: CASE (Con’t.)

25

Information Engineering (IE) translates strategic goals of the company into data and applications

Focuses on description of corporate data instead of the processes

Output is Information Systems Architecture (ISA) Serves as the basis for planning,

development, and control of future IS

Data Administration Strategy: Information Engineering

26

Forces Affecting Development of ISA

Figure 16.8

27

Management commitment Thorough company situation

analysis End-user involvement Defined standards Training Small pilot project

Critical Success Factors for IS Strategy

28

Database Administration Using Oracle

Security Manager Schema Manager Storage Manager SQL Worksheet Instance Manager