Database Systems: A Practical Approach to Design, Implementation, and Management, 6th edition

Part 1 Background

• Chapter 1 Introduction to Databases
  • 1.1 Introduction
  • 1.2 Traditional File-Based Systems
    • 1.2.1 File-Based Approach
    • 1.2.2 Limitations of the File-Based Approach
  • 1.3 Database Approach
    • 1.3.1 The Database
    • 1.3.2 The Database Management System (DBMS)
    • 1.3.3 (Database) Application Programs
    • 1.3.4 Components of the DBMS Environment
    • 1.3.5 Database Design: The Paradigm Shift
  • 1.4 Roles in the Database Environment
    • 1.4.1 Data and Database Administrators
    • 1.4.2 Database Designers
    • 1.4.3 Application Developers
    • 1.4.4 End-Users
  • 1.5 History of Database Management Systems
  • 1.6 Advantages and Disadvantages of DBMSs
  • Chapter Summary
  • Review Questions
  • Exercises
• Chapter 2 Database Environment
  • 2.1 The Three-Level ANSI-SPARC Architecture
    • 2.1.1 External Level
    • 2.1.2 Conceptual Level
    • 2.1.3 Internal Level
    • 2.1.4 Schemas, Mappings, and Instances
    • 2.1.5 Data Independence
  • 2.2 Database Languages
    • 2.2.1 The Data Definition Language (DDL)
    • 2.2.2 The Data Manipulation Language (DML)
    • 2.2.3 Fourth-Generation Languages (4GLs)
  • 2.3 Data Models and Conceptual Modeling
    • 2.3.1 Object-Based Data Models
    • 2.3.2 Record-Based Data Models
    • 2.3.3 Physical Data Models
    • 2.3.4 Conceptual Modeling
  • 2.4 Functions of a DBMS
  • Chapter Summary
  • Review Questions
  • Exercises
• Chapter 3 Database Architectures and the Web
  • 3.1 Multi-user DBMS Architectures
    • 3.1.1 Teleprocessing
    • 3.1.2 File-Server Architecture
    • 3.1.3 Traditional Two-Tier Client—Server Architecture
    • 3.1.4 Three-Tier Client—Server Architecture
    • 3.1.5 N-Tier Architectures
    • 3.1.6 Middleware
    • 3.1.7 Transaction Processing Monitors
  • 3.2 Web Services and Service-Oriented Architectures
    • 3.2.1 Web Services
    • 3.2.2 Service-Oriented Architectures (SOA)
  • 3.3 Distributed DBMSs
  • 3.4 Data Warehousing
  • 3.5 Cloud Computing
    • 3.5.1 Benefits and Risks of Cloud Computing
    • 3.5.2 Cloud-based database solutions
  • 3.6 Components of a DBMS
  • 3.7 Oracle Architecture
    • 3.7.1 Oracle’s Logical Database Structure
    • 3.7.2 Oracle’s Physical Database Structure
  • Chapter Summary
  • Review Questions
  • Exercises

  Part 2 The Relational Model and Languages

  • Chapter 4 The Relational Model
    • 4.1 Brief History of the Relational Model
    • 4.2 Terminology
      • 4.2.1 Relational Data Structure
      • 4.2.2 Mathematical Relations
      • 4.2.3 Database Relations
      • 4.2.4 Properties of Relations
      • 4.2.5 Relational Keys
      • 4.2.6 Representing Relational Database Schemas
    • 4.3 Integrity Constraints
      • 4.3.1 Nulls
      • 4.3.2 Entity Integrity
      • 4.3.3 Referential Integrity
      • 4.3.4 General Constraints
    • 4.4 Views
      • 4.4.1 Terminology
      • 4.4.2 Purpose of Views
      • 4.4.3 Updating Views
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 5 Relational Algebra and Relational Calculus
    • 5.1 The Relational Algebra
      • 5.1.1 Unary Operations
      • 5.1.2 Set Operations
      • 5.1.3 Join Operations
      • 5.1.4 Division Operation
      • 5.1.5 Aggregation and Grouping Operations
      • 5.1.6 Summary of the Relational Algebra Operations
    • 5.2 The Relational Calculus
      • 5.2.1 Tuple Relational Calculus
      • 5.2.2 Domain Relational Calculus
    • 5.3 Other Languages
  • • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 6 SQL: Data Manipulation
    • 6.1 Introduction to SQL
      • 6.1.1 Objectives of SQL
      • 6.1.2 History of SQL
      • 6.1.3 Importance of SQL
      • 6.1.4 Terminology
    • 6.2 Writing SQL Commands
    • 6.3 Data Manipulation
      • 6.3.1 Simple Queries
      • 6.3.2 Sorting Results (ORDER BY Clause)
      • 6.3.3 Using the SQL Aggregate Functions
      • 6.3.4 Grouping Results (GROUP BY Clause)
      • 6.3.5 Subqueries
      • 6.3.6 ANY and ALL
      • 6.3.7 Multi-table Queries
      • 6.3.8 EXISTS and NOT EXISTS
      • 6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT)
      • 6.3.10 Database Updates
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 7 SQL: Data Definition
    • 7.1 The ISO SQL Data Types
      • 7.1.1 SQL Identifiers
      • 7.1.2 SQL Scalar Data Types
    • 7.2 Integrity Enhancement Feature
      • 7.2.1 Required Data
      • 7.2.2 Domain Constraints
      • 7.2.3 Entity Integrity
      • 7.2.4 Referential Integrity
      • 7.2.5 General Constraints
    • 7.3 Data Definition
      • 7.3.1 Creating a Database
      • 7.3.2 Creating a Table (CREATE TABLE)
      • 7.3.3 Changing a Table Definition (ALTER TABLE)
      • 7.3.4 Removing a Table (DROP TABLE)
      • 7.3.5 Creating an Index (CREATE INDEX)
      • 7.3.6 Removing an Index (DROP INDEX)
    • 7.4 Views
      • 7.4.1 Creating a View (CREATE VIEW)
      • 7.4.2 Removing a View (DROP VIEW)
      • 7.4.3 View Resolution
      • 7.4.4 Restrictions on Views
      • 7.4.5 View Updatability
      • 7.4.6 WITH CHECK OPTION
      • 7.4.7 Advantages and Disadvantages of Views
      • 7.4.8 View Materialization
    • 7.5 Transactions
      • 7.5.1 Immediate and Deferred Integrity Constraints
    • 7.6 Discretionary Access Control
      • 7.6.1 Granting Privileges to Other Users (GRANT)
      • 7.6.2 Revoking Privileges from Users (REVOKE)
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 8 Advanced SQL
    • 8.1 The SQL Programming Language
      • 8.1.1 Declarations
      • 8.1.2 Assignments
      • 8.1.3 Control Statements
      • 8.1.4 Exceptions in PL/SQL
      • 8.1.5 Cursors in PL/SQL
    • 8.2 Subprograms, Stored Procedures, Functions, and Packages
    • 8.3 Triggers
    • 8.4 Recursion
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 9 Object-Relational DBMSs
    • 9.1 Advanced Database Applications
    • 9.2 Weaknesses of RDBMSs
    • 9.3 Storing Objects in a Relational Database
      • 9.3.1 Mapping Classes to Relations
      • 9.3.2 Accessing Objects in the Relational Database
    • 9.4 Introduction to Object-Relational Database Systems
    • 9.5 SQL:2011
      • 9.5.1 Row Types
      • 9.5.2 User-Defined Types
      • 9.5.3 Subtypes and Supertypes
      • 9.5.4 User-Defined Routines
      • 9.5.5 Polymorphism
      • 9.5.6 Reference Types and Object Identity
      • 9.5.7 Creating Tables
      • 9.5.8 Querying Data
      • 9.5.9 Collection Types
      • 9.5.10 Typed Views
      • 9.5.11 Persistent Stored Modules
      • 9.5.12 Triggers
      • 9.5.13 Large Objects
      • 9.5.14 Recursion
    • 9.6 Object-Oriented Extensions in Oracle
      • 9.6.1 User-Defined Data Types
      • 9.6.2 Manipulating Object Tables
      • 9.6.3 Object Views
      • 9.6.4 Privileges
    • Chapter Summary
    • Review Questions
    • Exercises

  Part 3 Database Analysis and Design

  • Chapter 10 Database System Development Lifecycle
    • 10.1 The Information Systems Lifecycle
    • 10.2 The Database System Development Lifecycle
    • 10.3 Database Planning
    • 10.4 System Definition
      • 10.4.1 User Views
    • 10.5 Requirements Collection and Analysis
      • 10.5.1 Centralized Approach
      • 10.5.2 View Integration Approach
    • 10.6 Database Design
      • 10.6.1 Approaches to Database Design
      • 10.6.2 Data Modeling
      • 10.6.3 Phases of Database Design
    • 10.7 DBMS Selection
      • 10.7.1 Selecting the DBMS
    • 10.8 Application Design
      • 10.8.1 Transaction Design
      • 10.8.2 User Interface Design Guidelines
    • 10.9 Prototyping
    • 10.10 Implementation
    • 10.11 Data Conversion and Loading
    • 10.12 Testing
    • 10.13 Operational Maintenance
    • 10.14 CASE Tools
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 11 Database Analysis and the DreamHome Case Study
    • 11.1 When Are Fact-Finding Techniques Used?
    • 11.2 What Facts Are Collected?
    • 11.3 Fact-Finding Techniques
      • 11.3.1 Examining Documentation
      • 11.3.2 Interviewing
      • 11.3.3 Observing the Enterprise in Operation
      • 11.3.4 Research
      • 11.3.5 Questionnaires
    • 11.4 Using Fact-Finding Techniques: A Worked -Example
      • 11.4.1 The DreamHome Case Study–An Overview of the Current System
      • 11.4.2 The DreamHome Case Study–Database Planning
      • 11.4.3 The DreamHome Case Study–System Definition
      • 11.4.4 The DreamHome Case Study–Requirements Collection and Analysis
      • 11.4.5 The DreamHome Case Study–Database Design
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 12 Entity—Relationship Modeling
    • 12.1 Entity Types
    • 12.2 Relationship Types
      • 12.2.1 Degree of Relationship Type
      • 12.2.2 Recursive Relationship
    • 12.3 Attributes
      • 12.3.1 Simple and Composite Attributes
      • 12.3.2 Single-valued and Multi-valued Attributes
      • 12.3.3 Derived Attributes
      • 12.3.4 Keys
    • 12.4 Strong and Weak Entity Types
    • 12.5 Attributes on Relationships
    • 12.6 Structural Constraints
      • 12.6.1 One-to-One (1:1) Relationships
      • 12.6.2 One-to-Many (1:*) Relationships
      • 12.6.3 Many-to-Many (*:*) Relationships
      • 12.6.4 Multiplicity for Complex Relationships
      • 12.6.5 Cardinality and Participation Constraints
    • 12.7 Problems with ER Models
      • 12.7.1 Fan Traps
      • 12.7.2 Chasm Traps
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 13 Enhanced Entity—Relationship Modeling
    • 13.1 Specialization/Generalization
      • 13.1.1 Superclasses and Subclasses
      • 13.1.2 Superclass/Subclass Relationships
      • 13.1.3 Attribute Inheritance
      • 13.1.4 Specialization Process
      • 13.1.5 Generalization Process
      • 13.1.6 Constraints on Specialization/Generalization
      • 13.1.7 Worked Example of using Specialization/ Generalization to Model the Branch View of the DreamHome Case Study
    • 13.2 Aggregation
    • 13.3 Composition
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 14 Normalization
    • 14.1 The Purpose of Normalization
    • 14.2 How Normalization Supports Database Design
    • 14.3 Data Redundancy and Update Anomalies
      • 14.3.1 Insertion Anomalies
      • 14.3.2 Deletion Anomalies
      • 14.3.3 Modification Anomalies
    • 14.4 Functional Dependencies
      • 14.4.1 Characteristics of Functional Dependencies
      • 14.4.2 Identifying Functional Dependencies
      • 14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies
    • 14.5 The Process of Normalization
    • 14.6 First Normal Form (1NF)
    • 14.7 Second Normal Form (2NF)
    • 14.8 Third Normal Form (3NF)
    • 14.9 General Definitions of 2NF and 3NF
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 15 Advanced Normalization
    • 15.1 More on Functional Dependencies
      • 15.1.1 Inference Rules for Functional Dependencies
      • 15.1.2 Minimal Sets of Functional Dependencies
    • 15.2 Boyce—Codd Normal Form (BCNF)
      • 15.2.1 Definition of BCNF
    • 15.3 Review of Normalization Up to BCNF
    • 15.4 Fourth Normal Form (4NF)
      • 15.4.1 Multi-Valued Dependency
      • 15.4.2 Definition of Fourth Normal Form
    • 15.5 Fifth Normal Form (5NF)
      • 15.5.1 Lossless-Join Dependency
      • 15.5.2 Definition of Fifth Normal Form
    • Chapter Summary
    • Review Questions
    • Exercises

  Part 4 Methodology

  • Chapter 16 Methodology–Conceptual Database Design
    • 16.1 Introduction to the Database Design Methodology
      • 16.1.1 What Is a Design Methodology?
      • 16.1.2 Conceptual, Logical, and Physical Database Design
      • 16.1.3 Critical Success Factors in Database Design
    • 16.2 Overview of the Database Design Methodology
    • 16.3 Conceptual Database Design Methodology
    • Step 1: Build Conceptual Data Model
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 17 Methodology–Logical Database Design for the Relational Model
    • 17.1 Logical Database Design Methodology for the Relational Model
    • Step 2: Build Logical Data Model
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 18 Methodology–Physical Database Design for Relational Databases
    • 18.1 Comparison of Logical and Physical Database Design
    • 18.2 Overview of the Physical Database Design Methodology
    • 18.3 The Physical Database Design Methodology for Relational Databases
    • Step 3: Translate Logical Data Model for Target DBMS
    • Step 4: Design File Organizations and Indexes
    • Step 5: Design User Views
    • Step 6: Design Security Mechanisms
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 19 Methodology–Monitoring and Tuning the Operational System
    • 19.1 Denormalizing and Introducing Controlled Redundancy
    • Step 7: Consider the Introduction of Controlled Redundancy
    • 19.2 Monitoring the System to Improve Performance
    • Step 8: Monitor and Tune the Operational System
    • Chapter Summary
    • Review Questions
    • Exercises

  Part 5 Selected Database Issues

  • Chapter 20 Security and Administration
    • 20.1 Database Security
      • 20.1.1 Threats
    • 20.2 Countermeasures–Computer-Based Controls
      • 20.2.1 Authorization
      • 20.2.2 Access Controls
      • 20.2.3 Views
      • 20.2.4 Backup and Recovery
      • 20.2.5 Integrity
      • 20.2.6 Encryption
      • 20.2.7 RAID (Redundant Array of Independent Disks)
    • 20.3 Security in Microsoft Office Access DBMS
    • 20.4 Security in Oracle DBMS
    • 20.5 DBMSs and Web Security
      • 20.5.1 Proxy Servers
      • 20.5.2 Firewalls
      • 20.5.3 Message Digest Algorithms and Digital Signatures
      • 20.5.4 Digital Certificates
      • 20.5.5 Kerberos
      • 20.5.6 Secure Sockets Layer and Secure HTTP
      • 20.5.7 Secure Electronic Transactions and Secure Transaction Technology
      • 20.5.8 Java Security
      • 20.5.9 ActiveX Security
    • 20.6 Data Administration and Database Administration
      • 20.6.1 Data Administration
      • 20.6.2 Database Administration
      • 20.6.3 Comparison of Data and Database Administration
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 21 Professional, Legal, and Ethical Issues in Data Management
    • 21.1 Defining Legal and Ethical Issues in IT
      • 21.1.1 Defining Ethics in the Context of IT
      • 21.1.2 The Difference Between Ethical and Legal Behavior
      • 21.1.3 Ethical Behavior in IT
    • 21.2 Legislation and Its Impact on the IT Function
      • 21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS)
      • 21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO
      • 21.2.3 The Health Insurance Portability and Accountability Act
      • 21.2.4 The European Union (EU) Directive on Data Protection of 1995
      • 21.2.5 The United Kingdom’s Data Protection Act of 1998
      • 21.2.6 Access to Information Laws
      • 21.2.7 International Banking–Basel II Accords
    • 21.3 Establishing a Culture of Legal and Ethical Data Stewardship
      • 21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior
      • 21.3.2 Professional Organizations and Codes of Ethics
      • 21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome
    • 21.4 Intellectual Property
      • 21.4.1 Patent
      • 21.4.2 Copyright
      • 21.4.3 Trademark
      • 21.4.4 Intellectual Property Rights Issues for Software
      • 21.4.5 Intellectual Property Rights Issues for Data
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 22 Transaction Management
    • 22.1 Transaction Support
      • 22.1.1 Properties of Transactions
      • 22.1.2 Database Architecture
    • 22.2 Concurrency Control
      • 22.2.1 The Need for Concurrency Control
      • 22.2.2 Serializability and Recoverability
      • 22.2.3 Locking Methods
      • 22.2.4 Deadlock
      • 22.2.5 Timestamping Methods
      • 22.2.6 Multiversion Timestamp Ordering
      • 22.2.7 Optimistic Techniques
      • 22.2.8 Granularity of Data Items
    • 22.3 Database Recovery
      • 22.3.1 The Need for Recovery
      • 22.3.2 Transactions and Recovery
      • 22.3.3 Recovery Facilities
      • 22.3.4 Recovery Techniques
      • 22.3.5 Recovery in a Distributed DBMS
    • 22.4 Advanced Transaction Models
      • 22.4.1 Nested Transaction Model
      • 22.4.2 Sagas
      • 22.4.3 Multilevel Transaction Model
      • 22.4.4 Dynamic Restructuring
      • 22.4.5 Workflow Models
    • 22.5 Concurrency Control and Recovery in Oracle
      • 22.5.1 Oracle’s Isolation Levels
      • 22.5.2 Multiversion Read Consistency
      • 22.5.3 Deadlock Detection
      • 22.5.4 Backup and Recovery
    • Chapter Summary
    • Review Questions
    • Exercises
  • Chapter 23 Query Processing
    • 23.1 Overview of Query Processing
    • 23.2 Query Decomposition
    • 23.3 Heuristical Approach to Query Optimization
      • 23.3.1 Transformation Rules for the Relational Algebra Operations
      • 23.3.2 Heuristical Processing Strategies
    • 23.4 Cost Estimation for the Relational Algebra Operations
      • 23.4.1 Database Statistics
      • 23.4.2 Selection Operation (S = sp(R))
      • 23.4.3 Join Operation (T = (R 1F S))
      • 23.4.4 Projection Operation (S = pA1, A2, . . . , A m(R))
      • 23.4.5 The Relational Algebra Set Operations (T = R ¿ S, T = R > S, T = R — S)
    • 23.5 Enumeration of Alternative Execution Strategies
      • 23.5.1 Pipelining
      • 23.5.2 Linear Trees
      • 23.5.3 Physical Operators and Execution Strategies
      • 23.5.4 Reducing the Search Space
      • 23.5.5 Enumerating Left-Deep Trees
      • 23.5.6 Semantic Query Optimization
      • 23.5.7 Alternative Approaches to Query Optimization
      • 23.5.8 Distributed Query Optimization
    • 23.6 Query Processing and Optimization
      • 23.6.1 New Index Types
    • 23.7 Query Optimization in Oracle
      • 23.7.1 Rule-Based and Cost-Based Optimization
      • 23.7.2 Histograms
      • 23.7.3 Viewing the Execution Plan
    • Chapter Summary
    • Review Questions
    • Exercises

  Part 6 Distributed DBMSs and Replication

  • Chapter 24 Distributed DBMSs–Concepts and Design
    • 24.1 Introduction
      • 24.1.1 Concepts
      • 24.1.2 Advantages and Disadvantages of DDBMSs
      • 24.1.3 Homogeneous and Heterogeneous DDBMSs
    • 24.2 Overview of Networking
    • 24.3 Functions and Architectures of a DDBMS
      • 24.3.1 Functions of a DDBMS
      • 24.3.2 Reference Architecture for a DDBMS
      • 24.3.3 Reference Architecture for a Federated MDBS
      • 24.3.4 Component Architecture for a DDBMS
    • 24.4 Distributed Relational Database Design
      • 24.4.1 Data Allocation
      • 24.4.2 Fragmentation