Chapter 14Chapter 14Data and Database Administration

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Outline Outline

Organizational contextTools of database administrationProcesses for database specialistsOverview of processing environments

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Database Support for Decision Database Support for Decision MakingMaking

Top(strategic)

Middle(tactical)

Lower(operational)

Individual operationaldatabases

Summarized, integratedoperational databases

External data sources andsummarized, tactical databases

Operational databases

Management Hierarchy

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Decision Making ExamplesDecision Making ExamplesLevel Example Decisions Data Requirements

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

Economic and technology forecasts; news summaries; industry reports; medium term performance reports

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

Historical trends; supplier performance; critical path analysis; short term and medium 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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Information Life CycleInformation Life Cycle

Acquisition

Storage

Protection

Processing

Formatting

Dissemination

Usage

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Knowledge ManagementKnowledge Management

Technology

Human informationprocessing

Organizationdynamics

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Database SpecialistsDatabase Specialists

Data administrator– Middle or upper management– Broad view of information resources

Database administrator (DBA)– Support role– Emphasis on individual databases and DBMSs

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Responsibilities of SpecialistsResponsibilities 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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Database Administration ToolsDatabase Administration Tools

SecurityIntegrityManagement of stored procedures and

triggersData dictionary access

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Database Access ControlDatabase Access Control

Database securitysystem

Users

Data dictionary

Authorization rules

Authentication,access requests

DBA

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Discretionary Access ControlDiscretionary Access Control

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

specified parts of a databaseUse views for fine level of controlUse groups to reduce the number of

authorization rules

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

SQL Statements for Security ISQL Statements for Security I

CREATE ROLE ISFaculty

CREATE ROLE ISAdministrator WITH ADMIN CURRENT_ROLE

CREATE ROLE ISAdvisor

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

SQL Statements for Security IISQL 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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Common SQL PrivilegesCommon SQL PrivilegesPrivilege 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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Oracle Security StatementsOracle Security Statements

CREATE USER statementPredefined roles

– CONNECT– RESOURCE– DBA

System versus object privileges

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Access Security ToolsAccess Security Tools

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Mandatory Access ControlMandatory Access Control

Less flexible security approach for highly sensitive and static databases

Assign classification levels to database objects

Assign clearance levels to usersAccess granted if a user's clearance level

provides access to the classification level of a database object

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

EncryptionEncryption

Encoding data to obscure its meaningPlaintextCiphertextEncryption key

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

SQL DomainsSQL Domains

Limited ability to define new domainsCREATE 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);

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

SQL AssertionsSQL Assertions

Supports complex constraintsConstraint specified through a SELECT

statementEnforcement can be inefficientStored procedures and form events are

alternatives

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Assertion ExampleAssertion ExampleCREATE 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 ) )

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

CHECK ConstraintsCHECK Constraints

Use when a constraint involves columns of the same table

Part of CREATE TABLE statementEasy to writeEfficient to enforce

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

CHECK Constraints ExampleCHECK 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 ) )

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Coding Practice ConcernsCoding Practice Concerns

DocumentationParameter usageContent of triggers and stored procedures

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Management of DependenciesManagement of Dependencies

Referenced tables, views, and proceduresAccess plans for SQL statementsDBMS support incomplete

– Obsolete statistics– Remotely stored procedures– No automatic recompilation after deletion

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Managing Trigger ComplexityManaging Trigger Complexity

Coding guidelines to minimize interactionTrigger analysis toolsAdditional testing for interacting triggers

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

MetadataMetadata

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

Stored in a data dictionaryDBMS data dictionary to track objects

managed by the DBMSInformation resource dictionary to track

objects relating to information systems development

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Catalog TablesCatalog Tables

Most DBMSs provide a large collectionDefinition Schema and Information

Schema in SQL:1999Modify using data definition and control

statementsUse SELECT statement to retrieve from

catalog tablesIntegrity of catalog tables is crucial

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Sample Oracle Catalog TablesSample Oracle Catalog TablesTable 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.

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Information Resource DictionaryInformation Resource Dictionary

DBMS

IRD

CASE tool 1

IRDS

CASE tool 2 CASE tool n

Metadataimport

Metadataexport

...

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Processes for Database Processes for Database SpecialistsSpecialistsData planningDBMS selection and evaluation

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Goals of Data PlanningGoals 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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Planning ModelsPlanning Models

Data

Processes Organization

Align informationsystems withbusiness environment

Enterprise models

Business goalsand objectives

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Level of Detail in ModelsLevel of Detail in ModelsModel Levels of Detail

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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

DBMS SelectionDBMS Selection

Detailed processRequires knowledge of organization goals

and DBMS featuresSystematic approach is importantHigh switching cost if wrong choice

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Selection Process PhasesSelection Process Phases

Scorecandidatesystems

Analyzerequirements

Determineweights

Rankedcandidates

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Analytic Hierarchy ProcessAnalytic Hierarchy Process

Multi-criteria decision making toolSupports systematic assignment of weights

and scores to candidate DBMSsUses pairwise comparisons

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Rating Values for ComparisonsRating Values for Comparisons

Ranking Value of Aij Meaning

1 Requirements i and j are equally important.

3 Requirement i is slightly more important than requirement j.

5 Requirement i is significantly more important than requirement j.

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

9 Requirement i is absolutely more important than requirement j.

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Final Selection FactorsFinal Selection Factors

Benchmarks and trial usageContractual termsVendor expectations

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

BenchmarkingBenchmarking

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

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

TCP BenchmarksTCP Benchmarks

Reasonable estimates about a DBMS in a specific hardware/software environment

Total system performance and cost measures

Audits to ensure unbiased results

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Managing Database EnvironmentsManaging Database Environments

Transaction processingData warehouse processingDistributed processingObject data management

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

Responsibilities of Database Responsibilities of Database SpecialistsSpecialistsApplication developmentDatabase infrastructure and architecturesPerformance monitoringEnterprise data model developmentContingency planning

McGraw-Hill/Irwin © 2004 The McGraw-Hill Companies, Inc. All rights reserved.

SummarySummary

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

Context for studying other Part 4 chapters