Database management and design / Gary W. Hansen and James V. Hansen

Part One
DATABASES AND THEIR CONTEXT I
One
Database Systems and the Evolution of Database Technology
The Evolution of Database Technology
Case: International Product Distribution
Flle- Orlented Systems
Meeting the Need for Random Access Processing
Information as a Resource
Other Limitations off raditional File Systems
Database Systems
Hierarchical and Network Model Systems
Relational Database Systems
Current Directions—Client/Server Platforms
Database Systems: Hardware, Software, Data, People
* Hardware 23 * Software 24 ▼ Data 27 ▼ People
Relationship of the Four System Components
Summary
Review Questions
Two
Problems and Exercises
Projects and Professional Issues
Bonhomie Catering: An Introductory Database Application
The Bonhomie Catering Case
Background
Business Operations
Employee Services
Purchasing
Job Scheduling
Promotion
Financial Status
Database Design and Implementation
A Description of the Sales/Cash Receipts Cycle
From Entitles to Tables
Creating aTable
Data Model Diagram Revisited
Setting Validity Checks
Additional DBMS Features
Dap Entry Through Simple Forms
A Multi-Table Form;The Request Form
Obtaining Management Information from Our Database
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Three
Database Systems in the Organization
Data Sharing and Databases
Sharing Data Between Functional Units
Sharing Data Between Different Levels of Users
Sharing Data Between Different Locations
The Role of the Database
Strategic Database Planning
The Need for Database Planning
The Database Planning Project
The Database Development Life Cycle (DDLC)
Databases and Management Control
Four
Database Design
User Training
Database Security and Integrity
Database System Performance
Risks and Costs of Databases
Organizational Conflicts
Development Project Failure
System Failure
Overhead Costs
Need for Sophisticated Personnel
Separating Logical and Physical Data Representation
Three-Level Database Architecture
Use of the Three-Level Architecture In this Book
Database Development
Database Design and the Traditional SDLC
The Database Development Life Cycle (DDLC)
CaseiThe Zeus Corporation
Preliminary Planning
Feasibility Study
Requirements Definition .
Conceptual Design
Implementation
Evaluating and Enhancing the Databasse Schema
Building Skills in Database Developmen t
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Principles of Conceptual Database Design
Reality, Requirements Definition, and Conceptual Data Modeling
Reality and Models
Conceptual Data Models
Fundamentals
Objects
Specialization and Generalization
Five
Relationships
Cardinality
Attributes
Examples
Example I :The Bank Data Model
Example 2: Stratton's Fruit Orchards
«
Example 3:A Logic Problem Involving Mapping
Building Conceptual Data Models from Existing Reports
Case: Manv^aring Consulting Services
A Data Model for Purchases
A Data Model for Project Billing
Aggregation
Example 4: Premier Construction Company
Example 5: Stratton's Fruit Orchards Continued
Example 6: Manv/aring Consulting Services Continued
Modeling Conceptual Objects Versus Physical Objects
The Library Problem
Creating the Library Data Model
Tracking Manu^ctured Parts
Conceptual Objects for Manwaring Consulting Services
View IntegrationiAn Example
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
The Relational Data Model
The Relational Data Model and System Development
The Relational Data Model: Fundamental Concepts
Relations
Null Values
Keys
Foreign Keys
Integrity Constraints
The Normalization Process
First Normal Form
Functional Dependencies
Second Normal Form
Third Normal Form
Six
Fourth Normal Form
Other Normal Forms
Transforming a Conceptual Model to a Relational Model
Transforming Objea Sets and Attributes
Transforming Models Without External Keys
Transforming Specialization and Generalization Object Sets
Transforming Relationships
Transforming Aggregate Object Sets
Transforming Recursive Relationships
Transformation Examples: Manwaring Consulting Services
Comparison of Conceptual and Relational Data Modeling
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Relational Algebra and Calculus
A Reyolutionary Advance In Data Manipulation
Relational Algebra
Union
Intersection
Difference
Product
Select
Project*
Join
Divide
Assignment
An Additional Example
Relational Calculus
Target List and Qualifying Statement
The Existential Quantifier
The Universal Quantifier
Relative Difficulty of Relational Algebra and Relational Calculus
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Seven
Relational Implementation with SQL
Relational ImplementationsiAn Overviefw
Schema and Table Definition
Schema Definition
Data Types and Domains
DefiningTables
Data Manipulation
Simple Queries
Multipie-Table Queries
Subqueries
EXISTS and NOT EXISTS
Built-in Functions
GROUP BY and HAVING
Built-in Funaions with Subqueries
Relational Algebra Operations
Database Change Operations
Using SQL With Data Processing Languages
View Definition
Restriaions on View Queries and Updates
The Information Schema
Summary
Eight
Review Questions
Problems and Exercises
Projects and Professional Issues
Relational Implementation with
Graphical Query Languages
Introduction
Data Manipulation
Simple Queries
Multiple-Table Queries
Built-in Functions
GROUP BY
Database Change Operations
Paradox for Windows
Nine
Data Definition and Entry
Data Manipulation
Selecting Attributes and Rows
Conjunctive and Disjunctive Queries
Application Language
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Client/Server Database Systems
Ten
IntroductiomA Review of Client/Server Concepts
Defining Database Tables on Server Database Management Systems
Creating User-Defined Datatypes
Defining Individual Tables
Data Definition Language: A Summary and Final Example
Server Data Manipulation and Programming
Control-of-Flow Language
Stored Procedures
An Introduction toTriggens
Developing Client Applications
PowerBuilder's Approach
Using PowerBuilder
Building an Application
Creating DataWindows
Creating Windows
Some Final Comments
Summary
Review Questions
Problems and Exercises
Professional Issues
Physical Database Systems
Introduction
Physical Access of the Database
Physical Storage Media
Secondary Storage
Physical Storage Blocks
Disk Performance Factors
Access Motion Time
Head Activation Time
Rotational Delay
Data Transfer Rate
Data Transfer Time
Data Storage Formats on Disk
Track Formats
Record Formats
Input/Output Management
File Or^nization and Addressing Methods
Sequential File Organization
Indexed-Sequential File Organization
Direct File Organization
Static Hash Functions
Dynamic Hash Functions
Implementing Logical Relationships
Linked Lists
Inverted Lists
Balanced-Tree Index (B'^-Tree) ■}
Client/Server Implementation of Balanced-Tree Indexing
Mapping Logical Data Structures to Physical Data Structures
Mapping Relational Databases
Mapping Network Databases
Mapping-Hierarchical Databases
Secondary-Key Access
Query Optimization
Combining Selection and Join Operations
Combining Projection, Selection, and Join Operations
Summary
Review Questions ^
Exercises and Problems
Projects and Professional Issues
Part Four
MANAGING THE DATABASE ENVIRONMENT
Eleven
Database Administration and Control
Database Administration: An Overview
DBA Functions
Communicating with Users
Establishing Standards and Procedures
DBA Goals
Database Integrity
Integrity Constraints in SQL
Integrity Constraints in Query-by-Example
Transaction Processing
Concurrency Control
Database Security
Authentication
Authorization and Views
Encryption
Database Recovery
Sources of Failure
Recovery Procedures
Summary
Review Questions
Problems and Exercises
Professional Issues
Twelve
Distributed Database Systems
Why Distributed Databases?
A General DDS Model
Distributed Database System Design
Strategies and Objectives
Distribution of Nonfragmented Files
Distributed Query Processing
Semijoins
Data Integrity in Distributed Database Systems
Two-Phase Commit Protocol
Distributed Locking
Distributed Two-Phase Locking
TImestamping
Database Recovery
Client/Server Systems
General Concepts
Database Applications
SQL Server for Windows NT
Summary
Review Questions
Problems and Exercises
Professional Issues
Thirteen
DBMS Selection and implementation
Analyzing Management Information Needs
Determining Application Requirements
Maintaining Data Consistency
Response-Time Requirepnents
DBMS Functions and Capabilities
The Data Dictionary/Directory
Data Security and Integrity
Query, Data Manipulation, and Reporting Capabilities
Support of Specialized Programming Requirements
Physical Data Organization Options
Classifying DBMS Feature Requirements
Gathering Data on Feature Availability and Performance
Acquiring Data from Vendors
Benchmark Tests
Evaluation Models
Scoring Model
Data Envelopment Analysis
Implementation Issues
Database Administration
Database Testing
Preparing Users for Change
Loading the Database
Database Maintenance
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Part Five
ADVANCED TOPICS
Fourteen
Advanced Systems: Object-Oriented Database Systems and
Knowledge-Base Systems
An Evolution To>^rd Object-Oriented Database Systems
Attempts to Provide More Powerful Data Representation
The Contribution of Object-Oriented Programming (OOP)
Class Abstraction
Derived Classes and Simple Inheritance
Aggregation
An Extended Example Demonstrating Multiple Inheritance
Object-Oriented Database System Developments
GemStone
Vbase
Orion
PDM
IRIS
O2
A Generic Object Database Language
The Data Model of Lerner College
Formulating Queries with Textquery
The Basic Format of Query Solutions InTextQuery
Defining Relationship Directions
Navigating Over Attributes and Relationships
Assigning Names to Derived Siets
Set Comparison Qualifications Expressions
Boolean Connectives
Introduction to Knowledge-Base Systems
Knowledge and Databases
Knowledge Representation with Rules
Rule Formation
Rules in PROLOG
A Simple PROLOG Database Application
More PROLOG Fundamentals
The Structure of a PROLOG Application
Database Application
Datalog
Logic Data Language (LDL)
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Part Six
LEGACY DATABASE SYSTEMS
Fifteen
The Network Data Model
Historical Background
Basic Concepts and Definitions
Three-Level Structure
Records and Sets
The Network Model's Relationship To Conceptual
Modeling Semantics
Transforming Object Sets and One-Many Relationships
Transforming n-ary Relationships
Transforming Many-Many Relationships
The DBTG Data Definition Language (DDL)
From Data Model to Schema
From Schema to Subschema
The DBTG Data Manipulation Language (DML)
DBTG Retrieval arid Update Facilities
DBTG Set-Processing Facility
Set Operations
Set Membership Classification
IDMS/R—A DBTG DBMS
CODASYL DBTG Evaluation
Data Representation
Data Manipulation Language
Integrity Constraints
Implementation
Summary
Review Questions
Problems and Exercises
Projects and Professional Issues
Sixteen
The Hierarchical Data Model
Introduction
Basic Concepts and Definitions
Hierarchical Model's Relationship to Conceptual
Modeling Semantics
Transforming One-Many Relationships
Transforming Many-Many Relationships
The IMS Architecture
Defining the Physical Database—^The DBD
Defining the Logical Database—^The PSB
IMS Access Methods
HSAM
HISAM
HDAM
HIDAM
The IMS Data Manipulation Language
Program Work Area
DL/1: An Overview
Get Unique (GU)
Get Next (GN)
Get Next Within Parent (GNP)
Get Hold
Replace (REPL)
Delete (DLET)
Insert (INSRT)
Hierarchical Data Model Evaluation
Data Representation
Data Manipulation Language
Summary
Review Questions
Problems and ^erclses
Projects and Professional Issues