Transcript Data and database administration

DATA AND DATABASE ADMINISTRATION
CISB344 Database 2
LEARNING OUTCOMES

At the end of this chapter, you should be able
to:
Define terms related to data & database administration
 List several major functions of data/database
administration
 Describe the changing roles of the data and database
administrator in the current business environment

Data and Database Administration
CISB344 Database 2
TRADITIONAL ADMINISTRATION
DEFINITIONS


Data Administration: A high-level function that is
responsible for the overall management of data
resources in an organization, including maintaining
corporate-wide definitions and standards
Database Administration: A technical function that is
responsible for physical database design and for
dealing with technical issues such as security
enforcement, database performance, and backup and
recovery
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATA ADMINISTRATION
FUNCTIONS
1.
2.
3.
4.
5.
Data policies, procedures, standards
Planning
Data conflict (ownership) resolution
Managing the information repository
Internal marketing of DA concepts
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATA ADMINISTRATION
FUNCTIONS

Data policies, procedures, standards

Data Policies: statements that make explicit the goals of DA



Every user must have a valid password
Data procedure: written outlines of actions to be taken to
perform certain activity
Data Standard: explicit conventions and behaviors that are
to be followed and that can be used to help evaluate
database quality

Minimum characters for password: 8
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATA ADMINISTRATION
FUNCTIONS

Planning
 Provide
leadership in developing the organization’s
Information Architecture (IA)
 Requires both an understanding the needs of the
organization for data and information and the
ability to lead the development of an information
architecture
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATA ADMINISTRATION
FUNCTIONS

Data conflict (ownership) resolution
 Who
owns the data?
 DA: suitable person because DA is not associated
with a certain department
 Procedures must be established
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATA ADMINISTRATION
FUNCTIONS

Managing the information repository



Repositories contain metadata  describe an
organization’s data and data processing resources
Replacing Data Dictionaries
Serves as an essential source of information and
functionality for:
Users: who must understand data definitions, business rules and
relationships among data objects
 CASE tools used
 Applications that access and manipulate data
 DBMS: maintain the repository and update system privileges,
passwords, object definitions

Data and Database Administration
CISB344 Database 2
TRADITIONAL DATA ADMINISTRATION
FUNCTIONS

Internal marketing of DA concepts
 The
importance of procedures and policies
 Effective internal marketing  reduce resistance to
change and data ownership problems
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATABASE ADMINISTRATION
FUNCTIONS
1.
2.
3.
4.
5.
6.
7.
Analyzing and designing databases
Selection of DBMS and software tools
Installing/upgrading DBMS
Tuning database performance
Improving query processing performance
Managing data security, privacy, and integrity
Data backup and recovery
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATABASE ADMINISTRATION
FUNCTIONS

Analyzing and designing databases
 Prioritizing
application transactions by volume,
importance and complexity
 Specifications should be reviewed as quickly as the
transactions are developed

Selection of DBMS and software tools
 Establish
policies regarding the DBMS and related
system software that will be supported within the
organization
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATABASE ADMINISTRATION
FUNCTIONS

Installing/upgrading DBMS



All the correct versions of different modules are in place, all the proper
device drivers are present and the DBMS works correctly with any third
party software products
User accounts must be created and maintained.
Tuning database performance



Performance of a database (query and update processing time as well
as data storage utilization) needs to be monitored
Design of a database must be frequently changed to meet new
requirements
Database  rebuilt, reorganized, and re-indexed: recover wasted space
and to correct poor data allocation and fragmentation
Data and Database Administration
CISB344 Database 2
TRADITIONAL DATABASE ADMINISTRATION
FUNCTIONS

Improving query processing performance
 Queries
may need to be rewritten in a more
efficient form
 Index may need to be added or deleted
 Data may need to be relocated to different devices
Managing data security, privacy, and integrity
 Data backup and recovery

Data and Database Administration
CISB344 Database 2
FUNCTIONS OF DA & DBA
DATABASE PLANNING
Develop corporate database strategy/policies
Develop enterprise model (information architecture)
Develop cost/benefit models
Design database environment/select technologies
Develop and market data administration plan
DATABASE ANALYSIS
Define and model data requirements (conceptual)
Define and model business rules
Define operational requirements
Resolve requirements conflicts
Maintain corporate data dictionary/repository
Data and Database Administration
CISB344 Database 2
FUNCTIONS OF DA & DBA
DATABASE DESIGN
Perform logical database design
Design external model (sub schemas)
Design internal (physical) models
Design integrity controls
DATABASE IMPLEMENTATION
Specify database access policies
Establish security controls
Install DBMS
Supervise database loading
Specify test procedures
Develop application programming standards
Establish procedures for backup and recovery
Conduct user training
Data and Database Administration
CISB344 Database 2
FUNCTIONS OF DA & DBA
OPERATIONS AND MAINTENANCE
Backup and recover databases
Upgrade DBMS
Monitor database performance
Tune and reorganize databases
Resolve access conflict
Tune and rewrite queries
Enforce standards and procedures
Support users
GROWTH AND CHANGE
Implement change-control procedure
Plan growth and change
Evaluate new technology
Data and Database Administration
CISB344 Database 2
TRENDS IN DATABASE ADMINISTRATION

Trends and new skills needed:
 Increased
 Triggers,
used of procedural logic
stored procedures
 Proliferation
of e-business applications
 Increase use of Smart phones
Data and Database Administration
CISB344 Database 2
DATA WAREHOUSE ADMINISTRATION
New role, coming with the growth in data
warehouses
 Similar to DA/DBA roles
 Emphasis on integration and coordination of
metadata/data across many data sources
 Specific roles:

Support DSS applications
 Manage data warehouse growth
 Establish service level agreements regarding
data warehouses and data marts

Data and Database Administration
CISB344 Database 2
OPEN SOURCE DBMSS

DEFINITION:

Free DBMS source code software that provides the core
functionality of an SQL-compliant DBMS

An alternative to proprietary packages such as
Oracle, Microsoft SQL Server, or Microsoft Access

mySQL is an example of an open-source DBMS

Advantages:
Less expensive than proprietary packages
 Source code available, for modification
 Have different version (simple to complex, free to
some cost(additional features)
 No additional cost for copies or licensing

Data and Database Administration
CISB344 Database 2
OPEN SOURCE DBMSS
 Disadvantages:
Absence
of complete documentation
Ambiguous licensing concerns
Not as feature-rich as proprietary
DBMSs
Vendors may not have certification
programs
Data and Database Administration
CISB344 Database 2
OPEN SOURCE DBMSS
 Consider
these factors when choosing
an open source DBMS:
 Features
 Support
 Ease-of-use
 Stability
 Speed
 Training
 Licensing
Data and Database Administration
END OF CHAPTER
INFORMATION IN THIS SLIDES WERE TAKEN FROM MODERN
DATABASE MANAGEMENT SYSTEM, TENTH EDITION BY
JEFFREY A.HOFFER, V.RAMESH & HEIKKI TOPI.
CISB344 Database 2
INFORMATION ARCHITECTURE (IA)

Definition
 the
discipline of defining an information model or
concept and structuring information around this.
 Information architecture (IA) is the art of expressing
a model or concept of information used in activities
that require explicit details of complex systems
 a conceptual framework that defines the basic
structure, content and relationships of the
organizational databases that provide the data
needed to support the basic business processes of
an organization
Data and Database Administration
CISB344 Database 2
INFORMATION ARCHITECTURE (IA)
Taken from: http://www.shiftedpixels.com.au/S_Information-Architecture.php
Data and Database Administration
CISB344 Database 2
INFORMATION ARCHITECTURE (IA)
Taken from: http://ausweb.scu.edu.au/aw01/papers/refereed/sawyer/paper.html
Data and Database Administration
CISB344 Database 2
INFORMATION ARCHITECTURE (IA)
Taken from: http://businessfoundation.typepad.com/bf_blog/2008/11/world-class-informationarchitecture.html
Data and Database Administration
CISB344 Database 2
ROUTINES AND TRIGGERS

Routines
 Program
modules that execute on demand
 Functions–routines that return values and take
input parameters
 Procedures–routines that do not return values and
can take input or output parameters

Triggers
 Routines
that execute in response to a database
event (INSERT, UPDATE, or DELETE)
Data and Database Administration
CISB514 Advanced Database
ROUTINES AND TRIGGERS
Data and Database Administration
CISB344 Database 2
TRIGGERS
CREATE TRIGGER STANDARD_PRICE_UPDATE
AFTER UPDATE OF STANDARD_PRICE ON PRODUCT_T
FOR EACH ROW
INSERT INTO PRICE_UPDATES_T VALUES
(PRODUCT_DESCRIPTION, DATE, STANDARD_PRICE);
Data and Database Administration