Transcript Database Administration - School of Computing Homepage

Database Administration
Chapter 16
Need for Databases
 Data is used by different people, in different
departments, for different reasons
 Interpretation and Presentation of data in useful
formats
 Distribution of data and information
– To the right people
– At the right time
 Data Preservation
 Control over Data Duplication and Use
Role of the Database
 Primary Role: To Support Managerial Decision
Making at All Levels of the Organization
– Top Level  Strategic Decisions
– Middle Management  Tactical Decisions
– Operational Management  Daily, Operational
Decisions
 DBMS must provide each level a separate view of
the data and support their specialized decision
making roles
 Provide a seamless flow of information throughout
the company
Top Level Management
 Provide information for strategic decision making, strategic
planning, policy formation, and goals definitions
 Provide access to data to identify growth opportunities
 Provide a framework for defining and enforcing
organizational policies
 Improve the likelihood of a positive return on investment for
the company
– Ways to reduce cost
– Increase Productivity
 Provide feedback on whether the company is achieving its
goals
Middle Management
 Provide necessary data for tactical decision
making and planning
 Monitor and control the use of company resources
– How efficiently are the resources allocated and used
(including data)
– What potential or current operational problems exist
– Evaluate the performance of the different departments
 Provide a framework for ensuring the security and
privacy of the data in the database
Operational Management
 Represent and support the company
operations
 Produce query results within set
performance levels
 Enhance short-term operational ability
– Timely information for Customer Support
– Support for application development and
computer operations
Special Database Considerations
 Having a database does not mean the data
will be use properly, efficiently, correctly
 DBMS is just a tool for managing data – it
must be used correctly  effective
management and use
 3 Main Processes for implementing a DBMS
– Technological  DBMS software and hardware
– Managerial  Administrative Functions
– Cultural  Corporate Resistance to Change
Evolution of the DBA
 Data Processing Department
 Information Systems Department
– Service Function to provide end users active data management
support
– Production Function to provide solutions to information needs
(application development)
 DBA – Database Administration
– Size and Role varies from company to company
– DBA’s function is very dynamic




Distributed Databases
Internet Databases
Object Oriented databases
Sophistication and Power of the DBMS packages
Common Functions of DBA
 Database Planning
– Standards, procedures, enforcement
 Requirements Gathering and Conceptual Design
 Logical Design
 Physical Design and Implementation
 Testing and Debugging
 Operations and Maintenance
 Training and Support
(Follows the requirements of the DBLC phases)
DBA’s Managerial Role
 Control and Planning Dimensions of
Database Administration
– Coordinating, Monitoring, and Allocating
database administration resources
 People
 Data
– Defining Goals and Formulating Strategic Plans
for the Database Administration function
DBA’s Responsibilities
DBA Activity
DBA Service
Planning
End-User Support
Organizing
Policies, Procedures,
Standards
Data Security, Privacy, Integrity
Testing
of
Monitoring
Data backup and Recovery
Delivering
Data Distribution and Use
End-User Support
 User Requirements Gathering
– Understanding of the users’ views and needs
– Present and Future information needs
 Conflict and Problem Resolution
– Solutions in one department may cause problems in
another
 Finding Solutions to Information Needs
 Ensure Quality and Integrity of Applications and
Data
 Build End-User Confidence
 Manage the Training and Support of DBMS users
Policies, Procedures, and Standards
 Policies: General Statements of Direction or
action that communicate and support DBA goals
 Procedures: Written Instructions that describe a
services of steps to be followed during the
performance of a given activity
 Standards: More detailed and specific than
policies, and describe the minimum requirements
of a DBA activity
– Rules that are used to evaluate the qualityof the activity
Areas of Policies and Procedures









End-User database requirements gathering
Database design and modeling
Documentation and Naming conventions
Design, coding, and testing of applications
Database software selection
Database security and integrity
Database backup and recovery
Database maintenance and operation
End-user training
Data Security, Privacy and Integrity

User Access Management
– Define each user to the database
 Operating System Level
 Database Level
– Assign Passwords
– Define User Groups
– Assign Access Privileges
 Read
 Write
 Delete
– Physical Access Control

View Definitions
– Protect and Control the Scope of the Data that is accessible to a user

DBMS utilities access control
– Limit the use of query and reporting tools

DBMS usage Monitoring
– Audit Logs
(More difficult in distributed databases)
Data Backup and Recovery
 Disaster Management
– Periodic Data and Application Backups
 Full
 Incremental
 Concurrent
–
–
–
–
Proper Backup Identification
Convenient and safe backup storage
Physical protection of hardware and software
Personal Access Control to the software of a database
installation
– Insurance Coverate for the data in the database
Data Backup and Recovery
 Recovery and Contingency plans
– Tested
– Evaluated
– Practiced
 Will Not Recover all components of an IS
– Establish priorities for the nature and extend of
the data recovery process
Data Distribution and Use
 Data is only useful when:
– Given to the Right User
– Right Time
– Right Format
 Programmers Deliver programs to access data
– Time consuming for DBA
 Data Distribution allows end users to access the database
–
–
–
–
Internet
Intranets
Queries, Web Front Ends
End Users may make improper use of database, data duplication,
etc.
DBA’s Technical Role
 DBMS and utilities, evaluation, selection and
installation
 Design and implementation of Database
 Testing and Evaluation
 Operation of DBMS, Utilities, and Applications
 Training and Supporting Users
 Maintenance of DBMS, Utilities, and Applicaitons
Evaluation, Selection, and
Installation
 Selection of Hardware and Software
 Must be based on the Organization’s Needs
 Search is for a solutions to a problem, not a
need for a software
 1st step is to determine companies NEEDS
DBMS Checklist















DBMS Model
Storage Capacity
Application Development Support
Security and Integrity
Backup and Recovery
Concurrency Control
Performance
DBA tools
Interoperability and Data Distribution
Portability
Hardware
Data Dictionary
Vendor Training and Support
Third Party Tools
Cost
Design and Implementation
 Determination and Enforcement of Standards and
Procedures
 Ensure the Design activities are performed within
the Standards and Procedures
 Ensure Transactions are:
– Correct
– Efficient
– Compliant with Integrity and Standards
 Physical Design
 Operational Procedures
Testing and Evaluation
 All Database and End User Applications
 Maintained Independently of Development and
Implementation
 Cover:
– Technical Aspects
 Backup, Recovery, Security, Integrity, SQL
– Evaluation of Documentation
– Observance of Standards
 Naming, Documentation, Coding
– Data Duplication Conflicts with existing data
– Enforcement of Data Validation rules
Operation of DBMS, Utilities, and
Applications
 System Support
– Day-to-day activity of the DBMS
 Performance monitoring and tuning
–
–
–
–
Performance Goals
Evaluate if performance objectives are being met
Isolate Problems and Find solutions
Implement solutions
 Backup and Recovery
 Security auditing and monitoring
– Appropriate access rights
– Proper use of access privileges by programmers and end users
Training and Supporting Users
 Technical Training in the use of DBMS and
Utilities for Applications Programmers
 Unscheduled on-demand technical support
 Interaction with DBMS vendors
Maintenance of DBMS
 Dedicated to the Preservation of the DBMS
environment
 Management of the Physical or Secondary
Storage devices
– Reorganizing the physical location of the data
 Upgrading the DBMS and Utility Software
 Exchange of data is dissimilar formats or
between database
Database Administration Tools
 Data Dictionary
– Store the Description of all objects that interact with the
database
 Integrated  limited to data of DBMS
 Standalone  Include data outside of DBMS
 Case Tools
– Computer Aided Software Engineering
– Automated Framework for the SDLC
 Front-End Tools
– Planning, Analysis, Design
 Back-End Tools
– Coding and Implementation
Data Dictionary
 Data Elements from all tables of all databases
– Names, Types, Format, Validation rules, When an
Element is Used and by whom







Tables defined in the database
Indexes defined for each table
Defined databases, and properties
End Users and Administrators
Programs that access the database
Access Authorizations for all users
Relationships among data elements
Case Tools




Reduction in development time and costs
Automation of the SDLC
Standardization of Systems Development Methodologies
Easier Maintenance of Application Systems Developed
with CASE Tools
 Components:
– Graphics
– Screen Painters and Report Generators
– Repository for Storing and Cross-Referencing the System Design
Data (Data Dictionary)
– Analysis Segment for Automated Check on System Consistency,
Syntax, Completeness
– Program Documentation Generator