Chapter 14. Outline. Database Support for Decision Making. Data and Database Administration

McGraw-Hill/Irwin Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.

Chapter 14

Data and Database Administration

14-2

Outline

Organizational context

Tools of database administration
Processes for database specialists
Overview of processing environments

Database Support for Decision

Making

(operational) Individual operational_databases Summarized, integrated

operational databases External data sources and summarized, tactical databases Management Hierarchy

14-4

Decision Making Examples

Top Identify new markets and products; plan growth; reallocate resources across divisions

Economic and technology forecasts; news summaries; industry reports; medium term performance reports M iddle Choose suppliers;

forecast sales, inventory, and cash; revise staffing levels; prepare budgets

Historical trends; supplier performance; critical path analysis; short term and m edium term plans

Lower Schedule employees; correct order delays; find production bottlenecks; monitor resource usage

Problem reports; exception reports; employee schedules; daily production results; inventory levels

14-5

Information Life Cycle

Acquisition Storage Protection Processing Formatting Dissemination Usage

Knowledge Management

14-7

Database Specialists

Data administrator

Middle or upper management
Broad view of information resources

Database administrator (DBA)

Support role

Emphasis on individual databases and DBMSs

14-8

Responsibilities of Specialists

Data administrator

Develops enterprise data model
Establishes inter database standards
Negotiates contractual terms

Database administrator

Performs database development tasks
Consults on application development
Evaluates DBMS capabilities and features

Database Administration Tools

Security
Integrity

Management of stored procedures and triggers

14-10

Database Access Control

Database security system Users Data dictionary Authorization rules Authentication, access requests DBA 14-11

Discretionary Access Control

Assign access rights or privileges to users
Specify ability to read, write, and delete

specified parts of a database
Use views for fine level of control
Use groups to reduce the number of

authorization rules

SQL Statements for Security I

CREATE ROLE ISFaculty

CREATE ROLE ISAdministrator WITH ADMIN CURRENT_ROLE

14-13

SQL Statements for Security II

GRANT SELECT ON ISStudentGPA

TO ISFaculty, ISAdvisor, ISAdministrator

GRANT UPDATE ON ISStudentGPA.StdGPA TO ISAdministrator

REVOKE SELECT ON ISStudentGPA FROM ISFaculty RESTRICT

GRANT ISAdministrator TO Smith WITH GRANT OPTION;

14-14

Common SQL Privileges

Privilege Explanation

SELECT Query the object; cannot be specified for individual columns

UPDATE Modify the value; can be specified for individual columns

INSERT Add a new row; can be specified for individual columns

DELETE Delete a row; cannot be specified for individual columns

TRIGGER Create a trigger on the specified table REFERENCES Reference columns of the given table in

integrity constraints EXECUTE Execute the stored procedure

Oracle Security Statements

CREATE USER statement
Predefined roles

CONNECT
RESOURCE
DBA

14-16

Access Security Tools

14-17

Mandatory Access Control

Less flexible security approach for highly sensitive and static databases

Assign classification levels to database objects

Assign clearance levels to users

Access granted if a user's clearance level provides access to the classification level of a database object

Encryption

Encoding data to obscure its meaning
Plaintext

Ciphertext
Encryption key

14-19

SQL Domains

Limited ability to define new domains
CREATE DOMAIN statement

CREATE DOMAIN StudentClass AS CHAR(2) CHECK(VALUE IN ('FR','SO','JR','SR') )

Distinct type

CREATE DISTINCT TYPE USD AS DECIMAL(10,2);

14-20

SQL Assertions

Supports complex constraints

Constraint specified through a SELECT statement

Enforcement can be inefficient

Stored procedures and form events are alternatives

Assertion Example

CREATE ASSERTION FullTimeEnrollment CHECK (NOT EXISTS

( SELECT Enrollment.RegNo FROM Registration, Offering,

Enrollment, Course

WHERE Offering.OfferNo =Enrollment.OfferNo AND Offering.CourseNo = Course.CourseNo AND Offering.RegNo = Registration.RegNo AND RegStatus = 'F'

GROUP BY Enrollment.RegNo HAVING SUM(CrsUnits) >= 9 ) )

14-22

CHECK Constraints

Use when a constraint involves columns of the same table

Part of CREATE TABLE statement
Easy to write

Efficient to enforce

14-23

CHECK Constraints Example

CREATE TABLE Student (…

CONSTRAINT ValidGPA CHECK ( StdGPA BETWEEN 0 AND 4 ), CONSTRAINT MajorDeclared CHECK

( StdClass IN ('FR','SO') OR StdMajor IS NOT NULL ) )

Coding Practice Concerns

Documentation
Parameter usage

14-25

Management of Dependencies

Referenced tables, views, and procedures
Access plans for SQL statements

DBMS support incomplete

Obsolete statistics

Remotely stored procedures

No automatic recompilation after deletion

14-26

Managing Trigger Complexity

Coding guidelines to minimize interaction
Trigger analysis tools

Additional testing for interacting triggers

Metadata

Define the source, use, value, and meaning of data

Stored in a data dictionary

DBMS data dictionary to track objects managed by the DBMS

Information resource dictionary to track objects relating to information systems

14-28

Catalog Tables

Most DBMSs provide a large collection
Definition Schema and Information

Schema in SQL:2003

Modify using data definition and control statements

Use SELECT statement to retrieve from catalog tables

Integrity of catalog tables is crucial

14-29

Sample Oracle Catalog Tables

Table Name Contents

USER_CATALOG Contains basic data about each table and view defined by a user.

USER_OBJECTS Contains data about each object (functions, procedures, indexes, triggers, assertions, etc.) defined by a user. This table contains the time created and the last time changed for each object.

USER_TABLES Contains extended data about each table such as space allocation and statistical summaries.

USER_TAB_COLUMNS Contains basic and extended data for each column such as the column name, the table reference, the data type, and a statistical summary.

USER_VIEWS Contains the SQL statement defining each view.

Information Resource Dictionary

CASE tool 1

IRDS

CASE tool 2 CASE tool n

Metadata import

Metadata export

14-31

Processes for Database

Specialists

Data planning

DBMS selection and evaluation

14-32

Goals of Data Planning

Evaluate current information systems with respect to the goals and objectives of the organization

Determine the scope and the timing of developing new information systems and utilizing of new information technology
Identify opportunities to apply information

technology for competitive advantage

Planning Models

14-34

Level of Detail in Models

Data Subject model (initial level), entity model (detailed level)

Process Functional areas and business processes (initial level), activity model (detailed level) Organization Role definitions and role relationships

Data-process interaction

Matrix and diagrams showing data requirements of processes Process-organization

interaction

Matrix and diagrams showing role responsibilities

Data-organization Matrix and diagrams showing usage of data by roles

14-35

DBMS Selection

Detailed process

Requires knowledge of organization goals and DBMS features

Systematic approach is important
High switching cost if wrong choice

Selection Process Phases

Score candidate systems Analyze requirements Determine weights

14-37

Analytic Hierarchy Process

Multi-criteria decision making tool
Supports systematic assignment of

weights and scores to candidate DBMSs
Uses pairwise comparisons

14-38

Rating Values for Comparisons

Requirement i is absolutely more important than requirement j. 9

Requirement i is very significantly more important than requirement j. 7

Requirement i is significantly more important than requirement j. 5

Requirement i is slightly more important than requirement j. 3

Requirements i and j are equally important.

1

Meaning Ranking Value of A_ij

Analytic Hierarchy Process Details

Assign importance weights to pairwise combinations of requirement groups and requirement categories

Combine and normalize importance weights
Score candidate DBMSs for each requirement
Combine and normalize scores

14-40

Final Selection Factors

Benchmarks and trial usage
Contractual terms

Vendor expectations

14-41

Benchmarking

Workload to evaluate the performance of a system or product

A good benchmark should be relevant, portable, scalable, and understandable.
Standard, domain-specific benchmarks by

TPC

TCP Benchmarks

Reasonable estimates about a DBMS in a specific hardware/software environment
Total system performance and cost

measures

14-43

Current TCP Benchmarks

TPC-C: order entry benchmark
TPC-App: business to business

transactions

TPC-H: decision support ad hoc queries
TPC-W: Ecommerce benchmark

14-44

Managing Database Environments

Data warehouse processing
Distributed processing
Object data management

Responsibilities of Database

Specialists

Application development

Database infrastructure and architectures
Performance monitoring

Enterprise data model development
Contingency planning

14-46

Summary

Two roles for managing information resources

Tools for security, integrity, rule

processing, stored procedures, and data dictionary manipulation

Processes for data planning and DBMS selection