Transcript Week 11

11
Database Administration
MIS 304 Winter 2006
11
Review of Last Class
• Transactions are used in the databases and their
applications to manage data.
• Managing Database transactions is a function of
the DBMS through the Scheduler and Transaction
Manager.
• Concurrency and locking technology manage
multiple users sharing the same data.
2
11
Class Goals
• To be able to understand and articulate the issues
that affect the management of Data and
Databases.
• To understand the Physical management of data
and databases.
• Understand the job of the Data Base
Administrator
3
11
Two Sides of the Coin
• Physical and logical database.
• The human side of the equation.
– The DBA.
– The Users of the database.
4
11
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.
• Every organization is subject to a data-informationdecision cycle.
5
The Data-Information-Decision Cycle
11
6
The Need for and Role
of Databases in an Organization
11
• 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 database’s predominant role is to support
managerial decision making at all levels in the
organization.
7
11
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.
8
The Need for and Role
of Databases in an Organization
11
• 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.
9
The Need for and Role
of Databases in an Organization
11
• Operational Management
– Represent and support the company operations
as closely as possible.
– Produce query results within specified
performance levels.
– Enhance the company’s short-term operational
ability by providing
• Timely information for customer support
• Support for application development and
computer operations.
10
11
Database use levels
• Personal
• Workgroup
• Organizational
More users typically means greater database
complexity
11
11
Personal databases
• Notebook computers
• Personal digital assistants (PDAs)
• Personal information managers (PIMs)
– Outlook
– Notes
– Act
• On today’s computer hardware you can get
away with a lot.
12
11
Workgroup and organizational
databases
• Shared by many people
• Greater complexity
• Require more planning and co-ordination than
personal databases
13
11
System level data administration
•
•
•
•
•
•
•
•
•
•
Planning
Development of data standards and policies
Data integrity
Data conflict resolution
Managing the DBMS
Establishing and maintaining the Data Dictionary
Selection of hardware and software
Benchmarking
Managing external databases
Internal marketing
14
11
Introduction of a Database:
Special 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 organization’s
needs rather than the DBMS’s technical virtues.
• Three important aspects:
– Technological: DBMS hardware and software
– Managerial: Administrative functions
– Cultural: Corporate resistance to change
15
11
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.
16
11
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).
17
The IS Department Internal Organization
11
18
The Evolution of the Database
Administration Function
11
• 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 microcomputerbased DBMS.
19
The Evolution of the Database
Administration Function
11
• 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
20
11
Organizing the data administration
function
• Creation of the function
• Selecting staff and assigning roles
• Locating the function
21
11
Data administration reporting to the CIO
Chief Executive
Officer
Chief
Information
Officer
Application
Development
Group
Computer
Operations
Group
Data
Administ rat ion
Manager
Data
administration
staff
22
11
Data administration reporting to Support Services
Chief Executive
Officer
Chief
Information
Officer
Application
Development
Group
Computer
Operations
Group
Manager
Support
Services Group
Data
administration
st aff
23
11
Matrix structure for data administration
Chief Executive
Officer
Chief
Information
Officer
Manager
Project Group 1
Data
Administration
Staff
Manager
Project Group 2
Data
Administration
Manager
Data
Administration
Staff
24
11
Data administration as a staff function
Chief Executive
Officer
Chief
Information
Officer
Application
Development
Group
Computer
Operations
Group
Data
Administrat ion
Advisory
Committee
Manager
Support
Services Group
25
11
Decentralized data administration
Chief
Executive
Officer
Departmental/
Divisional
Head
Chief
Information
Officer
Departmental/
Divisional
Head
Data
Administratio
n Staff
Data
Administratio
n manager
Data
Administratio
n Staff
26
The Evolution of the Database
Administration Function
11
• The general coordinator of all DBAs is known as the
systems administrator (SYSADM).
• 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.
27
Contrasting DA And DBA Activities And
Characteristics
• Data Administrator
– Strategic
– Sets long term goals
– Sets policies
– Broad scope
– Managerial orientation
11
• Database Administrator
– Operational
– Executes plans
– Enforces Policies
– Narrow scope
– Technical orientation
You can further contrast this with the role
of the Chief Information Officer (CIO)
28
A Summary Of DBA Activities
11
29
11
Desired DBA Skills
• Managerial
– Business skills
– Analytical skills
– Coordination skills
– Negotiation and
Conflict resolution
skills
– Communications skills
• Technical
– Broad knowledge of
computer systems
– Programming
knowledge
– Modeling
• Database
• Systems
– Operational skills
• Specific database
systems
30
11
The DBA’s Managerial Role
• Coordinating, monitoring, and allocating DB administration
resources: people and data.
• Defining goals and formulating strategic plans for the DB
administration function.
31
11
End User Support
• 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
32
11
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.
33
11
Data Security, Privacy, & Integrity
• Authorization management defines
procedures for the following functions:
–
–
–
–
User access management
View definition
DBMS utilities access control
DBMS usage monitoring
34
11
Security – Consistent State
• 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.
35
11
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
36
11
The DBA’s Technical Role
• DBMS and utilities evaluation, selection, and
installation
• Design and implementation of databases and
applications
• Testing and evaluation of databases and applications
37
11
The DBA’s Technical Role cont.
• Operation of the DBMS, utilities, and applications
• Training and supporting users
• Maintenance of the DBMS, utilities, and
applications
38
11
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
39
11
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
40
11
Testing and Evaluation of Databases
and Applications
– 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
41
11
Operation of the DBMS, Utilities, and
Applications
• System Support
• Performance monitoring and tuning
• Backup and recovery
• Security auditing and monitoring
42
11
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
43
11
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
44
11
Data management problems
•
•
•
•
•
•
•
Redundancy
Inconsistent representations
Multiple definitions of data items
Essential data missing
Inaccurate or incomplete data
Uncaptured data
Data that cannot be located
45
11
Selection of hardware and software
• How many users will simultaneously access the
database?
• Will the database need to be geographically
distributed?
• What is the maximum size of the database?
• How many transactions per second can the DBMS
handle?
• What kind of support for on-line transaction
processing is available?
• What are the initial and ongoing costs of using the
product?
• What is the extent of training required, will it be
provided, and what are the associated costs?
46
11
Benchmarking
• TPC-C
– Benchmarking of TPS
• TPC-W
– Benchmarking of Web sites
• TPC-D
– Benchmarking of DSS (Obsolete)
http://www.tpc.org
47
11
Data Structures
• The goal is to minimize disk accesses
• Disks are relatively slow compared to main
memory
– Writing a letter compared to a telephone call
• Disks are a bottleneck
• Appropriate data structures can reduce disk
accesses
48
11
Database access
Record
returned
Page
returned
File
Manager
DBMS
Record
request
Page
read
Disk
Manager
Page
request
Read
page
command
49
11
Disks
• Data stored on tracks on a surface
• A disk drive can have multiple surfaces
• Rotational delay
– Waiting for the physical storage location of
the data to appear under the read/write head
– Around 5 msec for a magnetic disk
– Set by the manufacturer
• Access arm delay
– Moving the read/write head to the track on
which the storage location can be found.
– Around 10 msec for a magnetic disk
50
11
Minimizing data access times
• Rotational delay is fixed by the manufacturer
• Access arm delay can be reduced by storing
files on
– The same track
– The same track on each surface
• A cylinder
51
11
Clustering
• Records that are often retrieved together
should be stored together
• Intra-file clustering
– Records within the one file
• A sequential file
• Inter-file clustering
– Records in different files
• A nation and its stocks
52
11
Disk manager
• Manages physical I/O
• Sees the disk as a collection of pages
DISK
PAGE
*diskid
*pageid
• Has a directory of each page on a disk
• Retrieves, replaces, and manages free pages
53
11
File manager
• Manages the storage of files
• Sees the disk as a collection of stored files
DISK
FILE
RECORD
*diskid
*fileid
*recordid
• Each file has a unique identifier
• Each record within a file has a unique record
identifier
54
11
File manager's tasks
•
•
•
•
•
•
Create a file
Delete a file
Retrieve a record from a file
Update a record in a file
Add a new record to a file
Delete a record from a file
55
11
Magnetic technology
• Up to 50% of IS hardware budgets are spent
on magnetic storage
• A $50 billion market
• The major form of data storage
• A mature and widely used technology
• Strong magnetic fields can erase data
• Magnetization decays with time
56
11
Fixed disks
•
•
•
•
•
Sealed, permanently mounted
Highly reliable
Access times of 4-10 msec
Transfer rates as high as 15 Mbytes per second
Capacities of Gbytes to Tbytes
57
11
RAID
• Redundant arrays of inexpensive or
independent drives
• Exploits economies of scale of disk
manufacturing for the personal computer
market
• Can also give greater security
• Increases a systems fault tolerance
• Not a replacement for regular backup
58
11
Mirroring
Data
Parity
59
Disk Mirroring
11
• Write
– Identical copies of a file are written to each drive in an array
• Read
– Alternate pages are read simultaneously from each drive
– Pages put together in memory
– Access time is reduced by approximately the number of disks in the
array
• Read error
– Read required page from another drive
• Tradeoffs
– Reduced access time
– Greater security
– More disk space
60
11
Parity
• A simple but effective way of insuring data
integrity
• 1 Byte = 8 Bits (0 or 1)
• 00000001 is Odd
• 00000011 1+1 is Even
• 00000111 1+1+1 is Odd
• 10000001 is Even
61
11
Striping
Data
Parity
62
11
Striping
• Three drive model
– Write
• Half of file to first drive
• Half of file to second drive
• Parity bit to third drive
– Read
• Portions from each drive are put together in memory
– Read error
• Lost bits are reconstructed from third drive’s parity data
– Tradeoffs
• Increased data security
• Less storage capacity than mirroring
• Not as fast as mirroring
63
11
RAID levels
• All levels, except 0, have common features
• The operating system sees a set of physical
drives as one logical drive
• Data are distributed across physical drives
• Parity is used for data recovery
64
RAID levels
11
• Level 0 (striping)
– Data spread across multiple drives
– No data recovery when a drive fails
• Level 1
– Mirroring
– Critical non-stop applications
• Level 3
– Striping
• Level 5
– A variation of striping
– Parity data is spread across drives
– Less capacity than level 1
– Higher I/O rates than level 3
65
11
RAID 5
66
11
Magnetic technology
•
•
•
•
•
Removable magnetic disk
Floppy disk
Magnetic tape
Magnetic tape cartridge
Mass storage
67
11
Solid State
• Arrays of memory chips
• 10 times faster than magnetic storage
• $20 per Mbyte
– Magnetic disk is about 6 cents per Mbyte
• Stock trading and video-streaming
applications
68
11
Optical technology
•
•
•
•
•
•
•
A more recent development
Use a laser for reading and writing data
High storage densities
Low cost
Direct access
Long storage life
Not susceptible to head crashes
69
11
Optical technology
CD-ROM
write once–
read many
WORM
write once–
ready many
Optical storage
Magneto-optical
write many–
read many
DVD
multiple formats
70
11
CD-ROM
• CD can store data as well as sound
• Economies of scale because of common
components for CD players and CD-ROM drives
• ROM - read only memory
• Capacity of 650 M bytes
• Relatively slow device
– 100 ms access time
• Growing consumer and commercial markets
• Ideal for storing legal documents
71
11
WORM
• Write-once read-many
• Popular for storing images
• High capacity
– As much as 10 G bytes
• Relatively slow
– 100 - 200 ms access time
• Juke-boxes for high volumes of data
• Not as secure as CD-ROM
72
11
Magneto-optical disk
• High capacity read-write medium
• 3.5" disk can store up to 256 M bytes
• Not as fast as fixed disk
– 10 msec access time
• Compact
• Reliable
• Suitable for data transfer, backup, and
archival purposes
73
Digital Versatile Disc (DVD)
11
• The same physical size as a CD-ROM but up to 28 times the
capacity (i.e., 17 Gbytes)
• DVD drives are likely to have transfer rates of around 2.76 M
bytes/sec and access times of 150 msec.
• DVD-ROM drive will play both audio CD's and CD-ROM's.
• Read-only versions
– DVD-Video (movies)
– DVD-ROM (software)
– DVD-Audio (songs)
• DVD-R
– Recordable (write once, read many)
• DVD-RAM
– Erasable (write many, read many)
74
11
Storage life
Magnetic tape
Half-inch reel-to-reel
Half-inch tape cartridge
VHS tape
Quarter-inch tape
Optical disk
CD-ROM (read only)
CD R (recordable) Microfilm
Medium-term film
Archival quality (silver)
Paper
Newspaper
High quality
Permanent
1
10
100
Storage life in years of high quality brands
500
75
11
The future
• Toshiba has developed technology that hold
1,000 times more data than a DVD (5
Tbytes)
• This technology is not likely to be
introduced for another 10 years
76
11
Merit of data storage devices
Device
Access speedVolume
Volatility
Cost per
megabyte
Reliability
Legal
standing
Solid state
***
*
***
*
**
*
Fixed disk
***
***
***
**
**
*
RAID
***
***
***
**
***
*
Removable disk
**
**
***
**
**
*
Floppy
*
*
***
*
*
*
Tape
*
**
*
***
**
*
Cartridge
**
***
*
***
**
*
Mass storage
**
***
*
***
**
*
CD-ROM
*
***
*
***
***
***
WORM
*
***
*
***
***
**
Magneto-optical **
***
**
***
***
*
DVD-ROM
*
***
*
***
***
***
DVD-R
*
***
*
***
***
**
DVD-RAM
*
***
**
***
***
*
77
11
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.
78
The Data Dictionary
11
• 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
79
11
Database Administration Tools
Data Dictionary Usage Examples:
– Example 1
List the names and creation dates of all tables created by the
user JONESVI in the current database.
SELECT NAME, CTIME
FROM SYSTABLES
WHERE CREATOR = ‘JONESVI’;
– Example 2
List the names of the columns for all tables created by
JONESVI in the current database.
SELECT NAME
FROM SYSCOLUMNS
WHERE CREATOR = ‘JONESVI’;
80
11
Database Administration Tools
– Example 3
List the names of all tables for which the user JONESVI has
DELETE authorization.
SELECT NAME
FROM SYSTABAUTH
WHERE GRANTEE = ‘JONESVI’;
AND DELETEAUTH = ‘Y’;
– Example 4
List the names all users who have some type of authority
over the INVENTRY table.
SELECT DISTINCT GRANTEE
FROM SYSTABAUTH
WHERE TTNAME = ‘INVENTRY’;
81
11
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.
82
11
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.
83
11
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 system’s 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.
84
11
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.
85
11
Database Administration Tools
• Lots of new options:
– Rational (IBM)
– Microsoft Visio morphing to “Systems
Definition Model” in Visual Studio Team
System
86
Developing a Data
Administration Strategy
11
• 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)
87
Forces Affecting The
Development Of The ISA
11
88
Developing a Data
Administration Strategy
11
• Critical Success Factors for IS Strategy
– Management commitment
– Thorough company situation analysis
– End user involvement
– Defined standards
– Training
– A small pilot project
89