Transcript Chapter 14 of Database Design, Application Development, and

Chapter 14
Data and Database Administration
McGraw-Hill/Irwin
Copyright © 2007 by The McGraw-Hill Companies, Inc. All rights reserved.
Outline




Organizational context
Tools of database administration
Processes for database specialists
Overview of processing environments
14-2
Database Support for Decision
Making
Management Hierarchy
Top
(strategic)
Middle
(tactical)
Lower
(operational)
External data sources and
summarized, tactical databases
Summarized, integrated
operational databases
Individual operational
databases
Operational databases
14-3
Decision Making Examples
Level
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
Problem reports;
exception reports;
employee schedules; daily
production results;
inventory levels
Schedule employees;
correct order delays;
find production
bottlenecks; monitor
resource usage
14-4
Information Life Cycle
Usage
Acquisition
Dissemination
Storage
Formatting
Protection
Processing
14-5
Knowledge Management
Technology
Human information
processing
Organization
dynamics
14-6
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-7
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
14-8
Database Administration Tools
 Security
 Integrity
 Management of stored procedures and
triggers
 Data dictionary access
14-9
Database Access Control
Authorization rules
DBA
Authentication,
access requests
Database security
system
Users
Data dictionary
14-10
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
14-11
SQL Statements for Security I
CREATE ROLE ISFaculty
CREATE ROLE ISAdministrator
WITH ADMIN CURRENT_ROLE
CREATE ROLE ISAdvisor
14-12
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-13
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
14-14
Oracle Security Statements
 CREATE USER statement
 Predefined roles
 CONNECT
 RESOURCE
 DBA
 System versus object privileges
14-15
Access Security Tools
14-16
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
14-17
Encryption




Encoding data to obscure its meaning
Plaintext
Ciphertext
Encryption key
14-18
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-19
SQL Assertions
 Supports complex constraints
 Constraint specified through a SELECT
statement
 Enforcement can be inefficient
 Stored procedures and form events are
alternatives
14-20
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-21
CHECK Constraints
 Use when a constraint involves columns of
the same table
 Part of CREATE TABLE statement
 Easy to write
 Efficient to enforce
14-22
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 ) )
14-23
Coding Practice Concerns
 Documentation
 Parameter usage
 Content of triggers and stored procedures
14-24
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-25
Managing Trigger Complexity
 Coding guidelines to minimize interaction
 Trigger analysis tools
 Additional testing for interacting triggers
14-26
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
development
14-27
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-28
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.
14-29
Information Resource Dictionary
CASE tool 1
Metadata
import
CASE tool 2
IRDS
CASE tool n
...
Metadata
export
DBMS
IRD
14-30
Processes for Database
Specialists
 Data planning
 DBMS selection and evaluation
14-31
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
14-32
Planning Models
Enterprise models
Business goals
and objectives
Data
Processes
Organization
Align information
systems with
business environment
14-33
Level of Detail in Models
Model
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
14-34
DBMS Selection
 Detailed process
 Requires knowledge of organization goals
and DBMS features
 Systematic approach is important
 High switching cost if wrong choice
14-35
Selection Process Phases
Analyze
requirements
Determine
weights
Score
candidate
systems
Ranked
candidates
14-36
Analytic Hierarchy Process
 Multi-criteria decision making tool
 Supports systematic assignment of
weights and scores to candidate DBMSs
 Uses pairwise comparisons
14-37
Rating Values for Comparisons
Ranking Value of Aij
1
3
5
7
9
Meaning
Requirements i and j are equally
important.
Requirement i is slightly more
important than requirement j.
Requirement i is significantly more
important than requirement j.
Requirement i is very significantly
more important than requirement j.
Requirement i is absolutely more
important than requirement j.
14-38
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
 Combine importance weights and DBMS scores
14-39
Final Selection Factors
 Benchmarks and trial usage
 Contractual terms
 Vendor expectations
14-40
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
14-41
TCP Benchmarks
 Reasonable estimates about a DBMS in a
specific hardware/software environment
 Total system performance and cost
measures
 Audits to ensure unbiased results
14-42
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-43
Managing Database Environments




Transaction processing
Data warehouse processing
Distributed processing
Object data management
14-44
Responsibilities of Database
Specialists





Application development
Database infrastructure and architectures
Performance monitoring
Enterprise data model development
Contingency planning
14-45
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
 Context for studying other Part 7 chapters
14-46