Chapter 15 Database Administration

1
Chapter 15Database Administration
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
2
Data as a Corporate Asset

• Data were seldom viewed as a company asset for
  two reasons
• There were simply too many data to be processed
  manually
• Internal and external business operations moved
  at a much slower pace than they do today, so
  there was relatively little need for quick
  reactions triggered by fast-flowing information.
• The computer liberated both managers and data.
  Data are now better understood as a resource.
• An organization is subject to a
  data-information-decision cycle. (Figure 15.1)

3
The Data-Information-Decision Cycle
Figure 15.1
4
The Need for and Roleof Databases in an
Organization

• Used properly, the DBMS facilitates
• Interpretation and presentation of data in useful
  formats, by transforming raw data into
  information.
• Distribution of data and information to the right
  people at the right time.
• Data preservation and data usage monitoring for
  adequate periods of time.
• Control over data duplication and use, both
  internally and externally.
• The databases predominant role is to support
  managerial decision making at all levels in the
  organization.

5
The Need for and Roleof Databases in an
Organization

• DB Support at Different Management Levels
• Top Management Level
• Provide the information necessary for strategic
  decision making, strategic planning, policy
  formulation, and goals definition.
• Provide access to external and internal data to
  identify growth opportunities and to chart the
  direction of such growth.
• Provide a framework for defining and enforcing
  organizational policies.
• Improve the likelihood of a positive return on
  investment for the company by searching for new
  ways to reduce costs and/or by boosting
  productivity.
• Provide feedback to monitor whether the company
  is achieving its goals.

6
The Need for and Roleof Databases in an
Organization

• Middle Management
• Deliver the data necessary for tactical decisions
  and planning.
• Monitor and control the allocation and use of
  company resources
• How efficiently are resources allocated and used?
• What potential or current operational problems
  exist?
• Evaluate the performance of the different
  departments.
• Provide a framework for enforcing and ensuring
  the security and privacy of the data in the
  database.

7
The Need for and Roleof Databases in an
Organization

• Operational Management
• Represent and support the company operations as
  closely as possible.
• Produce query results within specified
  performance levels.
• Enhance the companys short-term operational
  ability by providing
• Timely information for customer support
• Support for application development and computer
  operations.

8
Introduction of a DatabaseSpecial Considerations

• The introduction of a DBMS represents a big
  change and challenge and is likely to have a
  profound impact throughout the organization.
• The main issue of the DBMS should be the
  organizations needs rather than the DBMSs
  technical virtues.
• Three important aspects
• Technological DBMS hardware and software
• Managerial Administrative functions
• Cultural Corporate resistance to change

9
The Evolution of the Database Administration
Function

• Electronic Data Processing (EDP) Department
• Data process (DP) department
• Its task was to pool all computer resources to
  support all departments at the operational level
  with a minimal duplication of data and effort.
• The DP department was in charge of coordinating
  and maintaining all operational applications.

10
The Evolution of the Database Administration
Function

• Information Systems (IS) Department
• IS responsibilities were broadened to include
• A service function to provide end users with
  active data management service.
• A production function to provide end users with
  specific solutions for their information needs.
• The database administration function was created
  to handle increasingly complex data management
  tasks.
• The person responsible for the control of the
  centralized and shared database was the database
  administrator (DBA).

11
The IS Department Internal Organization
Figure 15.2
12
The Placement Of The DBA Function
Figure 15.3
13
The Evolution of the Database Administration
Function

• Factors Affecting the Placement of DBA
• Company management style
• Size and complexity of the company operations
• Geographic distribution of the company
  facilities
• Changes in DBMS Technologies Affecting
  Organizational Styles
• The development of distributed databases
• The growing use of Internet-ready and
  object-oriented databases and the growing number
  of data warehousing applications
• The increasing sophistication and power of
  microcomputer-based DBMS.

14
The Evolution of the Database Administration
Function

• DBA Activities
• Database planning
• Database requirements-gathering and conceptual
  design
• Database logical design and transaction design
• Database physical design and implementation
• Database testing and debugging
• Database operations and maintenance, including
  installation, conversion, and migration
• Database training and support

15
A DBA Functional Organization
Figure 15.4
16
The Evolution of the Database Administration
Function

• The general coordinator of all DBAs is known as
  the systems administrator (SYSADM). (Figure 15.5)
• Some large corporations make a distinction
  between a DBA and the data administrator (DA).
• The DA, a.k.a., the information resource manager
  (IRM), usually reports directly report to top
  management and is given a higher degree of
  responsibility and authority than the DBA.
• The DA is responsible for controlling the overall
  corporate data resource, both computerized and
  non-computerized.

17
Multiple Database Administrators In An
Organization
Figure 15.5
18
Contrasting DA And DBA Activities And
Characteristics
Table 15.1
19
A Summary Of DBA Activities
Figure 15.6
20
Desired DBA Skills
Table 15.2
21
Human Component

• The DBAs Managerial Role
• Coordinating, monitoring, and allocating DB
  administration resources people and data.
• Defining goals and formulating strategic plans
  for the DB administration function.

Table 15.3 DBA Activities And Services
22
Human Component

• End User Support
• User requirements-gathering
• Conflict and problem resolution
• Finding solutions to information needs
• Ensuring quality and integrity of application and
  data
• Building end user confidence
• Managing the training and support of DBMS users
• Policies, Procedures, and Standards
• Policies are general statements of direction or
  action that communicate and support the DBA
  goals.
• Standards are more detailed and specific than
  policies and describe the minimum requirement of
  a given DBA activity.
• Procedures are written instructions that describe
  a series of steps to be followed during the
  performance of a given activity.

23
Human Component

• Data Security, Privacy, and Integrity
• Authorization management defines procedures for
  the following functions
• User access management
• View definition
• DBMS utilities access control
• DBMS usage monitoring
• Security breaches can yield a database whose
  integrity is either preserved or corrupted
• Preserved Action is required to avoid the
  repetition of similar security problems, but data
  recovery may not be necessary.
• Corrupted Action is required to avoid the
  repetition of similar security problems, and the
  database must be recovered to a consistent state.

24
Human Component

• Data Backup and Recovery
• The database security officer (DSO) is
  responsible for the assurance of the database
  security and integrity.
• Disaster management includes all planning,
  organizing, and testing of database contingency
  plans and recover procedures.
• 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 Distribution and Use

25
Human Component

• The DBAs Technical Role
• 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

26
Human Component
DBMS and Utilities Evaluation, Selection, and
Installation

• DBMS model
• DBMS storage capacity
• Application development support
• Security and integrity
• Backup and recovery
• Concurrency control
• Performance
• Database administration tools

• Interoperability and data distribution
• Portability and standards
• Hardware
• Data Dictionary
• Vendor training and support
• Available third-party tools
• Cost

27
Human Component

• Design and Implementation of Databases and
  Applications
• Data modeling and design services at conceptual,
  logical, and physical levels
• Review of the design to ensure that transactions
  are
• Correct The transactions mirror real-world
  events.
• Efficient The transactions do not overload the
  DBMS.
• Compliant with integrity and standards.
• Database storage and access plans
• Database operational procedures

28
Human Component

• Testing and Evaluation of Databases and
  Applications
• Technical aspects of both the applications and
  the database
• Evaluation of the written documentation
• Observance of standards for naming, documenting,
  and coding
• Data duplication conflicts with existing data
• The enforcement of all data validation rules
• Operation of the DBMS, Utilities, and
  Applications
• System Support
• Performance monitoring and tuning
• Backup and recovery
• Security auditing and monitoring

29
Human Component

• Training and Supporting Users
• Training users and application programmers to use
  the DBMS and its tools
• Unscheduled, on-demand technical support for end
  user and programmers
• External sources (e.g., vendors) for support
• Maintenance of the DBMS, Utilities, and
  Applications
• Management of the physical storage devices
• Upgrading the DBMS and utility software
• Migration and conversion services for
  incompatible data and different DBMS

30
Database Administration Tools

• The Data Dictionary
• Different types of data dictionaries
• An integrated data dictionary is included with
  the DBMS, while a stand-alone data dictionary
  comes from a third-party vendor.
• An active data dictionary is automatically
  updated by the DBMS, while a passive data
  dictionary requires a batch process to create and
  update the dictionary.

31
Database Administration Tools

• The Data Dictionary
• The data dictionary typically stores descriptions
  of all
• Data elements defined in all tables of all
  databases
• Tables defined in all databases
• Indexes defined for each database table
• Defined databases
• End users and administrators of the database
• Programs that access the database
• Access authorizations for all users of all
  databases
• Relationships among the data elements

32
Database Administration Tools

• Data Dictionary Usage Examples
• Example 1List the names and creation dates of
  all tables created by the user JONESVI in the
  current database.
• SELECT NAME, CTIMEFROM SYSTABLESWHERE CREATOR
  JONESVI
• Example 2List the names of the columns for all
  tables created by JONESVI in the current
  database.
• SELECT NAMEFROM SYSCOLUMNSWHERE CREATOR
  JONESVI

33
Database Administration Tools

• Example 3List the names of all tables for which
  the user JONESVI has DELETE authorization.
• SELECT NAMEFROM SYSTABAUTHWHERE GRANTEE
  JONESVI AND DELETEAUTH Y
• Example 4List the names all users who have some
  type of authority over the INVENTRY table.
• SELECT DISTINCT GRANTEEFROM SYSTABAUTHWHERE
  TTNAME INVENTRY

34
Database Administration Tools

• Example 5List the user and table names for all
  users who can alter the database structure for
  any table in the database.
• SELECT GRANTEE, TTNAMEFROM SYSTABAUTHWHERE
  ALTERAUTH YORDER BY GRANTEE, TTNAME

35
Database Administration Tools

• CASE Tools
• CASE -- Computer-Aided Software Engineering
• It provides an automated framework for the
  Systems Development Life Cycle.
• Front-end CASE tools provide support for the
  planning, analysis, and design phases.
• Back-end CASE tools provide support for the
  coding and implementation phases.
• It is based on the use of structured
  methodologies and powerful graphical interfaces.

36
Database Administration Tools

• Benefits of CASE tools
• A reduction in development time and costs
• The automation of the SDLC
• Standardization of the systems development
  methodologies
• Easier maintenance of application systems
  developed with CASE tools.
• Improve communications among the DBA,
  applications designers, and the end users.

37
Database Administration Tools

• Commercial CASE Tools
• Excelerator from Merant, Inc. provides five
  components
• Graphics designed to produce structured diagrams
  as data flow diagrams and E-R diagrams.
• Screen painters and report generators to produce
  the information systems input/output formats.
• An integrated repository for storing and
  cross-referencing the system design data.
• An analysis segment to provide a fully automated
  check of system consistency, syntax, and
  completeness.
• A program document generator.

38
Figure 15.7 An Example of a CASE Tools Use
Excelerator
39
Database Administration Tools

• Commercial CASE Tools
• PLATINUM ERwin by PLATINUM Technology
• It produces fully documented E-R diagrams that
  can be displayed at different abstraction levels.
• It is able to produce detailed relational
  designs.
• Major relational DBMS vendors, such as ORACLE,
  provide fully integrated CASE tools for their own
  DBMS software as well as for RDBMSs supplied by
  other vendors.

40
Developing a Data Administration Strategy

• Information Engineering allows us to translate
  the strategic goals of the company into the data
  and applications.
• IE focuses on the description of the corporate
  data instead of the processes.
• The output of the IE process is an Information
  Systems Architecture (ISA) that serves as the
  basis for planning, development, and control of
  future information systems. (Figure 15.8)

41
Forces Affecting The Development Of The ISA
Figure 15.8
42
Developing a Data Administration Strategy

• Critical Success Factors for IS Strategy
• Management commitment
• Thorough company situation analysis
• End user involvement
• Defined standards
• Training
• A small pilot project

43
Database Administration Using Oracle

• Detailed look at how a DBA might handle the
  following technical tasks in a specific DBMS -
  Oracle Workgroup 2000 for Windows NT
• Creation and expansion of the database storage
  structures.
• Management of database objects such as tables,
  indexes, triggers, procedures, and so on.
• Management of the end user database environment,
  including the type and extent of database access.
• Customization of database initialization
  parameters.

44
Database Administration Using Oracle

• Oracle Database Administration Tools
• Security Manager
• Schema Manager
• Storage Manager
• SQL Worksheet
• Instance Manager

Figure 15.9 The Oracle Administrator Toolbar
45
Database Administration Using Oracle

• The Default Login

Figure 15.10 Oracle Login Information
46
Database Administration Using Oracle

• Ensuring an Automatic RDBMS Start

Figure 15.11 Verifying That The Oracle Services
Are Running
47
Database Administration Using Oracle

• Using the Storage Manager to Create Tablespaces
  and Datafiles
• A database is logically composed of one or more
  tablespaces. A tablespace is a logical storage
  space. Tablespaces are primarily used to
  logically group related data.
• The tablespace data are physically stored in one
  or more datafiles. A datafile physically stores
  the databases data.

48
Database Administration Using Oracle

• When the DBA creates a database, Oracle
  automatically creates the tablespaces and
  datafiles shown in Figure 15.12
• The SYSTEM tablespace is used to store the data
  dictionary data.
• The USER_DATA tablespace is used to store the
  table and to index data created by the end users.
• The TEMPORARY_DATA tablespace is used to store
  the temporary tables and indexes created during
  the execution of SQL statements.
• The ROLLBACK_DATA tablespace is used to store
  database transaction recovery information.

49
Database Administration Using Oracle

• Using the Storage Manager, the DBA can
• Create additional tablespaces to organize the
  data in the database.
• Create additional tablespaces to organize the
  different subsystems that may exist within the
  database.
• Expand the tablespace storage capacity by
  creating additional datafiles.

50
The Oracle Storage Manager
Figure 15.12
51
Figure 15.13 Creating a New Tablespace
52
The Oracle Schema Manager
Figure 15.14
53
Figure 15.15 The Oracle Security Manager
54
Figure 15.16 The Create User Dialog Box
55
(No Transcript)
56
Creating A New Database With The Oracle Instance
Manager
Figure 15.18
57
New Instance Screen
Figure 15.19
58
Advanced Parameters Screen
Figure 15.20