Modern Database Management (12th Edition)

Cover
Title Page
Copyright Page
Brief Contents
Contents
Preface
Part I The Context of Database Management
	An Overview of Part One
	Chapter 1 The Database Environment and Development Process
		Learning Objectives
		Data Matter!
		Introduction
		Basic Concepts and Definitions
			Data
			Data Versus Information
			Metadata
		Traditional File Processing Systems
			File Processing Systems at Pine Valley Furniture Company
			Disadvantages of File Processing Systems
				Program-Data Dependence
				Duplication of Data
				Limited Data Sharing
				Lengthy Development Times
				Excessive Program Maintenance
		The Database Approach
			Data Models
				Entities
				Relationships
			Relational Databases
			Database Management Systems
			Advantages of the Database Approach
				Program-Data Independence
				Planned Data Redundancy
				Improved Data Consistency
				Improved Data Sharing
				Increased Productivity of Application Development
				Enforcement of Standards
				Improved Data Quality
				Improved Data Accessibility And Responsiveness
				Reduced Program Maintenance
				Improved Decision Support
			Cautions About Database Benefits
			Costs and Risks of the Database Approach
				New, Specialized Personnel
				Installation and Management Cost and Complexity
				Conversion Costs
				Need for Explicit Backup and Recovery
				Organizational Conflict
			Components of the Database Environment
			The Database Development Process
				Systems Development Life Cycle
					Planning—Enterprise Modeling
					Planning—Conceptual Data Modeling
					Analysis—Conceptual Data Modeling
					Design—Logical Database Design
					Design—Physical Database Design and Definition
					Implementation—Database Implementation
					Maintenance—Database Maintenance
				Alternative Information Systems (IS) Development Approaches
				Three-Schema Architecture for Database Development
				Managing the People Involved in Database Development
		Evolution of Database Systems
			1960s
			1970s
			1980s
			1990s
			2000 and Beyond
		The Range of Database Applications
			Personal Databases
			Multitier Client/Server Databases
			Enterprise Applications
		Developing a Database Application for Pine Valley Furniture Company
			Database Evolution at Pine Valley Furniture Company
			Project Planning
			Analyzing Database Requirements
			Designing the Database
			Using the Database
			Administering the Database
			Future of Databases at Pine Valley
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
Part II Database Analysis
	An Overview of Part Two
	Chapter 2 Modeling Data in the Organization
		Learning Objectives
		Introduction
		The E-R Model: An Overview
			Sample E-R Diagram
			E-R Model Notation
		Modeling the Rules of the Organization
			Overview of Business Rules
				The Business Rules Paradigm
			Scope of Business Rules
				Good Business Rules
				Gathering Business Rules
			Data Names and Definitions
				Data Names
				Data Definitions
				Good Data Definitions
		Modeling Entities and Attributes
			Entities
				Entity Type Versus Entity Instance
				Entity Type Versus System Input, Output, or User
				Strong Versus Weak Entity Types
				Naming and Defining Entity Types
			Attributes
				Required Versus Optional Attributes
				Simple Versus Composite Attributes
				Single-Valued Versus Multivalued Attributes
				Stored Versus Derived Attributes
				Identifier Attribute
				Naming and Defining Attributes
		Modeling Relationships
			Basic Concepts and Definitions in Relationships
				Attributes on Relationships
				Associative Entities
			Degree of a Relationship
				Unary Relationship
				Binary Relationship
				Ternary Relationship
			Attributes or Entity?
			Cardinality Constraints
				Minimum Cardinality
				Maximum Cardinality
			Some Examples of Relationships and Their Cardinalities
				A Ternary Relationship
			Modeling Time-Dependent Data
			Modeling Multiple Relationships Between Entity Types
			Naming and Defining Relationships
		E-R Modeling Example: Pine Valley Furniture Company
		Database Processing at Pine Valley Furniture
			Showing Product Information
			Showing Product Line Information
			Showing Customer Order Status
			Showing Product Sales
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
	Chapter 3 The Enhanced E-R Model
		Learning Objectives
		Introduction
		Representing Supertypes and Subtypes
			Basic Concepts and Notation
				An Example of a Supertype/Subtype Relationship
				Attribute Inheritance
				When to Use Supertype/Subtype Relationships
			Representing Specialization and Generalization
				Generalization
				Specialization
				Combining Specialization and Generalization
		Specifying Constraints in Supertype/Subtype Relationships
			Specifying Completeness Constraints
				Total Specialization Rule
				Partial Specialization Rule
			Specifying Disjointness Constraints
				Disjoint Rule
				Overlap Rule
			Defining Subtype Discriminators
				Disjoint Subtypes
				Overlapping Subtypes
			Defining Supertype/subtype Hierarchies
				An Example of a Supertype/Subtype Hierarchy
				Summary of Supertype/Subtype Hierarchies
		EER Modeling Example: Pine Valley Furniture Company
		Entity Clustering
		Packaged Data Models
			A Revised Data Modeling Process with Packaged Data Models
			Packaged Data Model Examples
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
Part III Database Design
	An Overview of Part Three
	Chapter 4 Logical Database Design and the Relational Model
		Learning Objectives
		Introduction
		The Relational Data Model
			Basic Definitions
				Relational Data Structure
				Relational Keys
				Properties of Relations
				Removing Multivalued Attributes From Tables
			Sample Database
		Integrity Constraints
			Domain Constraints
			Entity Integrity
			Referential Integrity
			Creating Relational Tables
			Well-Structured Relations
		Transforming EER Diagrams into Relations
			Step 1: Map Regular Entities
				Composite Attributes
				Multivalued Attributes
			Step 2: Map Weak Entities
				When to Create a Surrogate Key
			Step 3: Map Binary Relationships
				Map Binary One-to-Many Relationships
				Map Binary Many-to-Many Relationships
				Map Binary One-to-One Relationships
			Step 4: Map Associative Entities
				Identifier Not Assigned
				Identifier Assigned
			Step 5: Map Unary Relationships
				Unary One-to-Many Relationships
				Unary Many-to-Many Relationships
			Step 6: Map Ternary (and n-ary) Relationships
			Step 7: Map Supertype/Subtype Relationships
			Summary of EER-to-Relational Transformations
		Introduction to Normalization
			Steps in Normalization
			Functional Dependencies and Keys
				Determinants
				Candidate Keys
		Normalization Example: Pine Valley Furniture Company
			Step 0: Represent the View in Tabular Form
			Step 1: Convert to First Normal Form
				Remove Repeating Groups
				Select the Primary Key
				Anomalies in 1NF
			Step 2: Convert to Second Normal Form
			Step 3: Convert to Third Normal Form
				Removing Transitive Dependencies
			Determinants and Normalization
			Step 4: Further Normalization
		Merging Relations
			An Example
			View Integration Problems
				Synonyms
				Homonyms
				Transitive Dependencies
				Supertype/Subtype Relationships
		A Final Step for Defining Relational Keys
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
	Chapter 5 Physical Database Design and Performance
		Learning Objectives
		Introduction
		The Physical Database Design Process
			Physical Database Design as a Basis for Regulatory Compliance
			Data Volume and Usage Analysis
		Designing Fields
			Choosing Data Types
				Coding Techniques
				Handling Missing Data
		Denormalizing and Partitioning Data
			Denormalization
				Opportunities for and Types of Denormalization
				Denormalize With Caution
			Partitioning
		Designing Physical Database Files
			File Organizations
				Heap File Organization
				Sequential File Organizations
				Indexed File Organizations
				Hashed File Organizations
			Clustering Files
			Designing Controls for Files
		Using and Selecting Indexes
			Creating a Unique Key Index
			Creating a Secondary (Nonunique) Key Index
			When to Use Indexes
		Designing a Database for Optimal Query Performance
			Parallel Query Processing
			Overriding Automatic Query Optimization
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
Part IV Implementation
	An Overview of Part Four
	Chapter 6 Introduction to SQL
		Learning Objectives
		Introduction
		Origins of the SQL Standard
		The SQL Environment
		Defining A Database in SQL
			Generating SQL Database Definitions
			Creating Tables
			Creating Data Integrity Controls
			Changing Table Definitions
			Removing Tables
		Inserting, Updating, and Deleting Data
			Batch Input
			Deleting Database Contents
			Updating Database Contents
		Internal Schema Definition in RDBMSs
			Creating Indexes
		Processing Single Tables
			Clauses of the SELECT Statement
			Using Expressions
			Using Functions
			Using Wildcards
			Using Comparison Operators
			Using Null Values
			Using Boolean Operators
			Using Ranges for Qualification
			Using Distinct Values
			Using IN and NOT IN with Lists
			Sorting Results: The ORDER BY Clause
			Categorizing Results: The GROUP BY Clause
			Qualifying Results By Categories: The HAVING Clause
			Using and Defining Views
				Materialized Views
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
	Chapter 7 Advanced SQL
		Learning Objectives
		Introduction
		Processing Multiple Tables
			Equi-join
			Natural Join
			Outer Join
			Sample Join Involving Four Tables
			Self-Join
			Subqueries
			Correlated Subqueries
			Using Derived Tables
			Combining Queries
			Conditional Expressions
			More Complicated SQL Queries
		Tips for Developing Queries
			Guidelines for Better Query Design
		Ensuring Transaction Integrity
		Data Dictionary Facilities
		Recent Enhancements and Extensions to SQL
			Analytical and OLAP Functions
			New Data Types
			New Temporal Features in SQL
			Other Enhancements
		Triggers and Routines
			Triggers
			Routines and other Programming Extensions
			Example Routine in Oracle’s PL/SQL
		Embedded SQL and Dynamic SQL
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Further Reading
		References
		Field Exercises
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
	Chapter 8 Database Application Development
		Learning Objectives
		Location, Location, Location!
		Introduction
		Client/Server Architectures
		Databases in a Two-Tier Architecture
			A VB.NET Example
			A Java Example
		Three-Tier Architectures
		Web Application Components
		Databases in Three-Tier Applications
			A JSP Web Application
			A PHP Example
			An ASP.NET Example
		Key Considerations in Three-Tier Applications
			Stored Procedures
			Transactions
			Database Connections
			Key Benefits of Three-Tier Applications
			Cloud Computing and Three-Tier Applications
		Extensible Markup Language (XML)
			Storing XML Documents
			Retrieving XML Documents
			Displaying XML Data
			XML and Web Services
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
		Case: Forondo Artist Management Excellence Inc.
	Chapter 9 Data Warehousing
		Learning Objectives
		Introduction
		Basic Concepts of Data Warehousing
			A Brief History of Data Warehousing
			The Need for Data Warehousing
				Need for a Company-Wide View
				Need to Separate Operational and Informational Systems
		Data Warehouse Architectures
			Independent Data Mart Data Warehousing Environment
			Dependent Data Mart and Operational Data Store Architecture: A Three-Level Approach
			Logical Data Mart and Real-Time Data Warehouse Architecture
			Three-Layer Data Architecture
				Role of the Enterprise Data Model
				Role of Metadata
		Some Characteristics of Data Warehouse Data
			Status Versus Event Data
			Transient Versus Periodic Data
			An Example of Transient and Periodic Data
				Transient Data
				Periodic Data
				Other Data Warehouse Changes
		The Derived Data Layer
			Characteristics of Derived Data
			The Star Schema
				Fact Tables and Dimension Tables
				Example Star Schema
				Surrogate Key
				Grain of the Fact Table
				Duration of the Database
				Size of the Fact Table
				Modeling Date and Time
			Variations of the Star Schema
				Multiple Fact Tables
				Factless Fact Tables
			Normalizing Dimension Tables
				Multivalued Dimensions
				Hierarchies
			Slowly Changing Dimensions
			Determining Dimensions and Facts
		The Future of Data Warehousing: Integration with Big Data and Analytics
			Speed of Processing
			Cost of Storing Data
			Dealing with Unstructured Data
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
Part V Advanced Database Topics
	An Overview of Part Five
	Chapter 10 Data Quality and Integration
		Learning Objectives
		Introduction
		Data Governance
		Managing Data Quality
			Characteristics of Quality Data
				External Data Sources
				Redundant Data Storage and Inconsistent Metadata
				Data Entry Problems
				Lack of Organizational Commitment
			Data Quality Improvement
				Get the Business Buy-In
				Conduct a Data Quality Audit
				Establish a Data Stewardship Program
				Improve Data Capture Processes
				Apply Modern Data Management Principles and Technology
				Apply TQM Principles and Practices
			Summary of Data Quality
		Master Data Management
		Data Integration: An Overview
			General Approaches to Data Integration
				Data Federation
				Data Propagation
		Data Integration for Data Warehousing: The Reconciled Data Layer
			Characteristics of Data After ETL
			The ETL Process
				Mapping and Metadata Management
				Extract
				Cleanse
				Load and Index
		Data Transformation
			Data Transformation Functions
				Record-Level Functions
				Field-Level Functions
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
	Chapter 11 Big Data and Analytics
		Learning Objectives
		Introduction
		Big Data
		NoSQL
			Classification of NoSQL Database Management Systems
				Key-Value Stores
				Document Stores
				Wide-Column Stores
				Graph-Oriented Databases
				NoSQL Examples
					Redis
					MongoDB
					Apache Cassandra
					Neo4j
				Impact of NoSQL on Database Professionals
				Hadoop
				Components of Hadoop
					The Hadoop Distributed File System (HDFS
					Mapreduce
					PIG
					HIVE
					HBASE
				Integrated Analytics and Data Science Platforms
					HP HAVEn
					Teradata Aster
					IBM Big Data Platform
				Putting It All Together: Integrated Data Architecture
		Analytics
			Types of Analytics
			Use of Descriptive Analytics
				SQL OLAP Querying
				Online Analytical Processing (OLAP) Tools
				Data Visualization
				Business Performance Management and Dashboards
			Use of Predictive Analytics
				Data Mining Tools
				Examples of Predictive Analytics
			Use of Prescriptive Analytics
			Data Management Infrastructure for Analytics
		Impact of Big Data and Analytics
			Applications of Big Data and Analytics
				Business
				E-Government and Politics
				Science and Technology
				Smart Health and Well-Being
				Security and Public Safety
			Implications of Big Data Analytics and Decision Making
				Personal Privacy vs. Collective Benefits
				Ownership and Access
				Quality and Reuse of Data and Algorithms
				Transparency and Validation
				Changing Nature of Work
				Demands for Workforce Capabilities and Education
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		References
		Further Reading
		Web Resources
	Chapter 12 Data and Database Administration
		Learning Objectives
		Introduction
		The Roles of Data and Database Administrators
			Traditional Data Administration
			Traditional Database Administration
			Trends in Database Administration
			Data Warehouse Administration
			Summary of Evolving Data Administration Roles
		The Open Source Movement and Database Management
		Managing Data Security
			Threats to Data Security
			Establishing Client/Server Security
				Server Security
				Network Security
			Application Security Issues in Three-Tier Client/Server Environments
				Data Privacy
		Database Software Data Security Features
			Views
			Integrity Controls
			Authorization Rules
			User-Defined Procedures
			Encryption
			Authentication Schemes
				Passwords
				Strong Authentication
		Sarbanes-Oxley (SOX) and Databases
			IT Change Management
			Logical Access to Data
				Personnel Controls
				Physical Access Controls
			IT Operations
		Database Backup and Recovery
			Basic Recovery Facilities
				Backup Facilities
				Journalizing Facilities
				Checkpoint Facility
				Recovery Manager
			Recovery and Restart Procedures
				Disk Mirroring
				Restore/Rerun
				Maintaining Transaction Integrity
				Backward Recovery
				Forward Recovery
			Types of Database Failure
				Aborted Transactions
				Incorrect Data
				System Failure
				Database Destruction
			Disaster Recovery
		Controlling Concurrent Access
			The Problem of Lost Updates
			Serializability
			Locking Mechanisms
				Locking Level
				Types of Locks
				Deadlock
				Managing Deadlock
			Versioning
		Data Dictionaries and Repositories
			Data Dictionary
			Repositories
		Overview of Tuning the Database for Performance
			Installation of the DBMS
			Memory and Storage Space Usage
			Input/Output (I/O) Contention
			CPU Usage
			Application Tuning
		Data Availability
			Costs of Downtime
			Measures to Ensure Availability
				Hardware Failures
				Loss or Corruption of Data
				Human Error
				Maintenance Downtime
				Network-Related Problems
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
Glossary of Acronyms
Glossary of Terms
Index
Online Chapters
	Chapter 13 Distributed Databases
		Learning Objectives
		Introduction
			Objectives and Trade-offs
		Options for Distributing a Database
			Data Replication
				Snapshot Replication
				Near-Real-Time Replication
				Pull Replication
				Database Integrity with Replication
				When to Use Replication
			Horizontal Partitioning
			Vertical Partitioning
			Combinations of Operations
			Selecting the Right Data Distribution Strategy
		Distributed DBMS
			Location Transparency
			Replication Transparency
			Failure Transparency
			Commit Protocol
			Concurrency Transparency
				Time-Stamping
			Query Optimization
			Evolution of Distributed DBMSs
				Remote Unit of Work
				Distributed Unit of Work
				Distributed Request
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
	Chapter 14 Object-Oriented Data Modeling
		Learning Objectives
		Introduction
		Unified Modeling Language
		Object-Oriented Data Modeling
			Representing Objects and Classes
			Types of Operations
			Representing Associations
			Representing Association Classes
			Representing Derived Attributes, Derived Associations, and Derived Roles
			Representing Generalization
			Interpreting Inheritance and Overriding
			Representing Multiple Inheritance
			Representing Aggregation
		Business Rules
		Object Modeling Example: Pine Valley Furniture Company
		Summary
		Key Terms
		Review Questions
		Problems and Exercises
		Field Exercises
		References
		Further Reading
		Web Resources
	Appendix A Data Modeling Tools and Notation
		Comparing E-R Modeling Conventions
			Visio Professional 2013 Notation
				Entities
				Relationships
			CA ERwin Data Modeler 9.5 Notation
				Entities
				Relationships
			SAP Sybase PowerDesigner 16.5 Notation
				Entities
				Relationships
			Oracle Designer Notation
				Entities
				Relationships
			Comparison of Tool Interfaces and E-R Diagrams
	Appendix B Advanced Normal Forms
		Boyce-Codd Normal Form
			Anomalies in Student Advisor
			Definition of Boyce-Codd Normal Form (BCNF)
			Converting a Relation to BCNF
		Fourth Normal Form
			Multivalued Dependencies
		Higher Normal Forms
		Key Terms
		References
		Web Resource
	Appendix C Data Structures
		Pointers
		Data Structure Building Blocks
		Linear Data Structures
			Stacks
			Queues
			Sorted Lists
			Multilists
		Hazards of Chain Structures
		Trees
			Balanced Trees
		Reference