You are previewing Database Systems.
O'Reilly logo
Database Systems

Book Description

Database Systems: A Pragmatic Approach provides a comprehensive, yet concise introduction to database systems. It discusses the database as an essential component of a software system, as well as a valuable, mission critical corporate resource. The book is based on lecture notes that have been tested and proven over several years, with outstanding results. It also exemplifies mastery of the technique of combining and balancing theory with practice, to give students their best chance at success. Upholding his aim for brevity, comprehensive coverage, and relevance, author Elvis C. Foster's practical and methodical discussion style gets straight to the salient issues, and avoids unnecessary fluff as well as an overkill of theoretical calculations.

The book discusses concepts, principles, design, implementation, and management issues of databases. Each chapter is organized systematically into brief, reader-friendly sections, with itemization of the important points to be remembered. It adopts a methodical and pragmatic approach to solving database systems problems. Diagrams and illustrations also sum up the salient points to enhance learning. Additionally, the book includes a number of Foster's original methodologies that add clarity and creativity to the database modeling and design experience while making a novel contribution to the discipline. Everything combines to make Database Systems: A Pragmatic Approach an excellent textbook for students, and an excellent resource on theory for the practitioner.

Table of Contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. About the Authors
  8. Preface
  9. Acknowledgement
  10. Part A: Preliminary Topics
    1. Chapter 1: Introduction to Database Systems
      1. 1.1 Definitions and Rationale
      2. 1.2 Objectives of a Database System
        1. Clarification on Data Independence
      3. 1.3 Advantages of a Database System
      4. 1.4 Approaches to Database Design
      5. 1.5 Desirable Features of a DBS
      6. 1.6 Database Development Life Cycle
      7. 1.7 Summary and Concluding Remarks
      8. 1.8 Review Questions
      9. 1.9 References and/or Recommended Readings
    2. Chapter 2: The Database System Environment
      1. 2.1 Levels of Architecture
        1. 2.1.1 External Level
        2. 2.1.2 Conceptual Level
        3. 2.1.3 Internal Level
      2. 2.2 Inter-level Mappings
      3. 2.3 The Database Administrator
      4. 2.4 The Database Management System
      5. 2.5 Components of DBMS Suite
        1. 2.5.1 The DBMS Engine
        2. 2.5.2 Definition Tools Subsystem
        3. 2.5.3 The User Interface Subsystem
        4. 2.5.4 Application Development Subsystem
        5. 2.5.5 Data Administration Subsystem
        6. 2.5.6 Data Dictionary Subsystem
        7. 2.5.7 Data Communications Manager
        8. 2.5.8 Utilities Subsystem
      6. 2.6 The Front-end and Back-end Perspectives
      7. 2.7 Database System Architecture
      8. 2.8 Summary and Concluding Remarks
      9. 2.9 Review Questions
      10. 2.10 References and/or Recommended Readings
  11. Part B: The Relational Database Model
    1. Chapter 3: The Relational Model
      1. 3.1 Basic Concepts
      2. 3.2 Domains
        1. Significance of Domains
      3. 3.3 Relations
        1. 3.3.1 Properties of a Relation
        2. 3.3.2 Kinds of Relations
      4. 3.4 Relational Database System
        1. Steps in Building a Relational Database System
      5. 3.5 Identifying, Representing, and Implementing Relationships
        1. 3.5.1 Identifying Relationships
        2. 3.5.2 Representing Relationships
        3. 3.5.3 Multiplicity of Relationships
        4. 3.5.4 Implementing Relationships
      6. 3.6 The Relation-Attributes List and Relationship List
      7. 3.7 Non-Relational Approaches
      8. 3.8 Summary and Concluding Remarks
      9. 3.9 Review Questions
      10. 3.10 References and/or Recommended Readings
    2. Chapter 4: Integrity Rules and Normalization
      1. 4.1 Fundamental Integrity Rules
      2. 4.2 Foreign Key Concept
        1. Deletion of Referenced Tuples
      3. 4.3 Rationale for Normalization
      4. 4.4 Functional Dependence and Non-loss Decomposition
        1. 4.4.1 Functional Dependence
        2. 4.4.2 Non-loss Decomposition
      5. 4.5 The First Normal Form
        1. Problems with Relations in 1NF Only
      6. 4.6 The Second Normal Form
        1. Problems with Relations in 2NF Only
      7. 4.7 The Third Normal Form
        1. Problems with Relations in 3NF Only
      8. 4.8 The Boyce-Codd Normal Form
      9. 4.9 The Fourth Normal Form
        1. 4.9.1 Multi-valued Dependency
        2. 4.9.2 Fagin’s Theorem
      10. 4.10 The Fifth Normal Form
        1. 4.10.1 Definition of Join Dependency
        2. 4.10.2 Fagin’s Theorem
      11. 4.11 Other Normal Forms
        1. 4.11.1 The Domain-Key Normal Form
        2. 4.11.2 The Sixth Normal Form
      12. 4.12 Summary and Concluding Remarks
      13. 4.13 Review Questions
      14. 4.14 References and/or Recommended Readings
    3. Chapter 5: Database Modeling and Design
      1. 5.1 Database Model and Database Design
        1. 5.1.1 Database Model
        2. 5.1.2 Database Design
      2. 5.2 The E-R Model Revisited
      3. 5.3 Database Design via the E-R Model
      4. 5.4 The Extended Relational Model
        1. 5.4.1 Entity Classifications
        2. 5.4.2 Surrogates
        3. 5.4.3 E-Relations and P-Relations
        4. 5.4.4 Integrity Rules
      5. 5.5 Database Design via the XR Model
        1. 5.5.1 Determining the Kernel Entities
        2. 5.5.2 Determining the Characteristic Entities
        3. 5.5.3 Determining the Designative Entities
        4. 5.5.4 Determining the Associations
        5. 5.5.5 Determining Entity Subtypes and Super-types
        6. 5.5.6 Determining Component Entities
        7. 5.5.7 Determining the Properties
      6. 5.6 The UML Model
      7. 5.7 Database Design via the UML Model
      8. 5.8 Innovation: The Object/Entity Specification Grid
      9. 5.9 Database Design via Normalization Theory
        1. 5.9.1 Example: Mountaineering Problem
        2. 5.9.2 Determining Candidate Keys and then Normalizing
      10. 5.10 Database Model and Design Tools
      11. 5.11 Summary and Concluding Remarks
      12. 5.12 Review Questions
      13. 5.13 References and/or Recommended Readings
    4. Chapter 6: Database User Interface Design
      1. 6.1 Introduction
      2. 6.2 Deciding on User Interface
      3. 6.3 Steps in User Interface Design
        1. 6.3.1 Menu or Graphical User Interface
        2. 6.3.2 Command-Based User Interface
      4. 6.4 User Interface Development and Implementation
      5. 6.5 Summary and Concluding Remarks
      6. 6.6 Review Questions
      7. 6.7 References and/or Recommend Readings
    5. Chapter 7: Relational Algebra
      1. 7.1 Introduction
      2. 7.2 Basic Operations of Relational Algebra
        1. 7.2.1 Primary and Secondary Operations
        2. 7.2.2 Codd’s Original Classification of Operations
        3. 7.2.3 Nested Operations
      3. 7.3 Syntax of Relational Algebra
        1. 7.3.1 Select Statement
        2. 7.3.2 Projection Statement
        3. 7.3.3 Natural Join Statement
        4. 7.3.4 Cartesian Product
        5. 7.3.5 Theta-Join
        6. 7.3.6 Union, Intersection, Difference Statements
        7. 7.3.7 Division Statement
      4. 7.4 Aliases, Renaming and the Relational Assignment
        1. 7.4.1 The Alias Operation
        2. 7.4.2 The Assignment Operation
        3. 7.4.3 The Rename Operation
      5. 7.5 Other Operators
      6. 7.6 Summary and Concluding Remarks
      7. 7.7 Review Questions
      8. 7.8 References and/or Recommended Readings
    6. Chapter 8: Relational Calculus
      1. 8.1 Introduction
      2. 8.2 Calculus Notations and Illustrations
      3. 8.3 Quantifiers, Free and Bound Variables
        1. 8.3.1 Well-Formed Formula
        2. 8.3.2 Free and Bound Variables
      4. 8.4 Substitution Rule and Standardization Rules
      5. 8.5 Query Optimization
      6. 8.6 Domain Oriented Relational Calculus
      7. 8.7 Summary and Concluding Remarks
      8. 8.8 Review Questions
      9. 8.9 References and/or Recommended Readings
    7. Chapter 9: Relational System — a Closer Look
      1. 9.1 The Relational Model Summarized
      2. 9.2 Ramifications of the Relational Model
        1. 9.2.1 Codd’s Early Benchmark
        2. 9.2.2 Revised Definition of a Relational System
        3. 9.2.3 Far Reaching Consequences
      3. 9.3 Summary and Concluding Remarks
      4. 9.4 Review Questions
      5. 9.5 References
  12. Part C: The Structured Query Language
    1. Chapter 10: Overview of SQL
      1. 10.1 Important Facts
        1. 10.1.1 Commonly Used DDL Statements
        2. 10.1.2 Commonly Used DML and DCL Statements
        3. 10.1.3 Syntax Convention
      2. 10.2 Advantages of SQL
      3. 10.3 Summary and Concluding Remarks
      4. 10.4 Review Questions
      5. 10.5 Recommended Readings
    2. Chapter 11: SQL Data Definition Statements
      1. 11.1 Overview of Oracle’s SQL Environment
      2. 11.2 Database Creation
      3. 11.3 Database Management
      4. 11.4 Tablespace Creation
      5. 11.5 Tablespace Management
      6. 11.6 Table Creation Statement
      7. 11.7 Dropping or Modifying a Table
      8. 11.8 Working with Indexes
      9. 11.9 Creating and Managing Sequences
      10. 11.10 Altering and Dropping Sequences
      11. 11.11 Creating and Managing Synonyms
      12. 11.12 Summary and Concluding Remarks
      13. 11.13 Review Questions
      14. 11.14 References and/or Recommended Readings
    3. Chapter 12: SQL Data Manipulation Statements
      1. 12.1 Insertion of Data
      2. 12.2 Update Operations
      3. 12.3 Deletion of Data
      4. 12.4 Commit and Rollback Operations
      5. 12.5 Basic Syntax for Queries
      6. 12.6 Simple Queries
      7. 12.7 Queries Involving Multiple Tables
        1. 12.7.1 The Traditional Method
        2. 12.7.2 The ANSI Method
      8. 12.8 Queries Involving the use of Functions
        1. 12.8.1 Row Functions
        2. 12.8.2 Date Functions
        3. 12.8.3 Data Conversion Functions
        4. 12.8.4 Programmer-Defined Functions
        5. 12.8.5 Aggregation Functions
      9. 12.9 Queries Using LIKE, BETWEEN and IN Operators
      10. 12.10 Nested Queries
      11. 12.11 Queries Involving Set Operators
      12. 12.12 Queries with Runtime Variables
      13. 12.13 Queries Involving SQL Plus Format Commands
      14. 12.14 Embedded SQL
      15. 12.15 Dynamic Queries
      16. 12.16 Summary and Concluding Remarks
      17. 12.17 Review Questions
      18. 12.18 References and/or Recommended Readings
    4. Chapter 13: SQL Views and System Security
      1. 13.1 Traditional Logical Views
        1. 13.1.1 View Creation
        2. 13.1.2 View Modification and Removal
        3. 13.1.3 Usefulness and Manipulation of Logical Views
      2. 13.2 System Security
        1. 13.2.1 Access to the System
        2. 13.2.2 Access to the System Resources
        3. 13.2.3 Access to the System Data
      3. 13.3 Materialized Views
        1. 13.3.1 Creating a Materialized View
        2. 13.3.2 Altering or Dropping a Materialized View
      4. 13.4 Summary and Concluding Remarks
      5. 13.5 Review Questions
      6. 13.6 References and/or Recommended Readings
    5. Chapter 14: The System Catalog
      1. 14.1 Introduction
      2. 14.2 Three Important Catalog Tables
        1. 14.2.1 The User_Tables View
        2. 14.2.2 The User_Tab_Columns View
        3. 14.2.3 The User_Indexes View
      3. 14.3 Other Important Catalog Tables
      4. 14.4 Querying the System Catalog
      5. 14.5 Updating the System Catalog
      6. 14.6 Summary and Concluding Remarks
      7. 14.7 Review Questions
      8. 14.8 References and/or Recommended Readings
    6. Chapter 15: Some Limitations of SQL
      1. 15.1 Programming Limitations
      2. 15.2 Limitations on Views
        1. 15.2.1 Restriction on use of the Order-By-Clause
        2. 15.2.2 Restriction on Data Manipulation for Views involving UNION, INTERSECT or JOIN
      3. 15.3 Foreign Key Constraint Specification
      4. 15.4 Superfluous Enforcement of Referential Integrity
      5. 15.5 Limitations on Calculated Columns
      6. 15.6 If-Then Limitation
      7. 15.7 Summary and Concluding Remarks
      8. 15.8 Review Questions
      9. 15.9 Recommended Readings
  13. Part D: Some Commonly Used DBMS Suites
    1. Chapter 16: Overview of Oracle
      1. 16.1 Introduction
      2. 16.2 Main Components of the Oracle Suite
        1. 16.2.1 Oracle Server
        2. 16.2.2 Oracle PL/SQL and SQL *Plus
        3. 16.2.3 Oracle Developer Suite
        4. 16.2.4 Oracle Enterprise Manager Database Control and SQL Developer
        5. 16.2.5 Oracle Enterprise Manager Grid Control
        6. 16.2.6 Oracle Database Configuration Assistant
        7. 16.2.7 Oracle Warehouse Builder
      3. 16.3 Shortcomings of Oracle
      4. 16.4 Summary and Concluding Remarks
      5. 16.5 Review Questions
      6. 16.6 References and/or Recommended Readings
    2. Chapter 17: Overview of DB2
      1. 17.1 Introduction
      2. 17.2 Main Components of the DB2 Suite
        1. 17.2.1 DB2 Universal Database Core
        2. 17.2.2 IBM InfoSphere Information Server
        3. 17.2.3 IBM Data Studio
        4. 17.2.4 IBM InfoSphere Warehouse
      3. 17.3 Shortcomings of DB2
      4. 17.4 Summary and Concluding Remarks
      5. 17.5 Review Questions
      6. 17.6 References and/or Recommended Readings
    3. Chapter 18: Overview of MS SQL Server
      1. 18.1 Introduction
        1. 18.1.1 Brief History
        2. 18.1.2 Operating Environment
        3. 18.1.3 MS SQL Server and the Client-Server Model
      2. 18.2 Main Features of MS SQL Server
      3. 18.3 Editions of MS SQL Server
      4. 18.4 Main Components of MS SQL Server Suite
        1. 18.4.1 Server Components
        2. 18.4.2 Management Tools
        3. 18.4.3 Client Connectivity
        4. 18.4.4 Development Tools
        5. 18.4.5 Code Samples
        6. 18.4.6 SQL Server Optional Components
      5. 18.5 MS SQL Server Default Databases
      6. 18.6 MS SQL Server Default Logins
      7. 18.7 Named versus Default Instances
      8. 18.8 Removing MS SQL Server
      9. 18.9 Shortcomings of MS SQL Server
      10. 18.10 Summary and Concluding Remarks
      11. 18.11 Review Questions
      12. 18.12 References and/or Recommended Readings
    4. Chapter 19: Overview of MySQL
      1. 19.1 Introduction to MySQL
      2. 19.2 Main Features of MySQL
      3. 19.3 Main Components of MySQL
      4. 19.4 Shortcomings of MySQL
        1. 19.4.1 Limitation on Joins and Views
        2. 19.4.2 Limitations on Sub-queries
        3. 19.4.3 Limitations on server-side Cursors
        4. 19.4.4 Other Limitations
      5. 19.5 Summary and Concluding Remarks
      6. 19.6 Review Questions
      7. 19.7 References and/or Recommended Readings
    5. Chapter 20: Overview of Delphi
      1. 20.1 Introduction
      2. 20.2 Major Components of the Delphi Suite
        1. 20.2.1 The Database Development Environment
        2. 20.2.2 Interactive Development Environment
        3. 20.2.3 Database Engine
        4. 20.2.4 Component Library for Cross Reference
        5. 20.2.5 Enterprise Core Object Subsystem
        6. 20.2.6 Documentation
      3. 20.3 Shortcomings of Delphi
      4. 20.4 Summary and Concluding Remarks
      5. 20.5 Review Questions
      6. 20.6 References and/or Recommended Readings
  14. Part E: Advanced Topics
    1. Chapter 21: Database Administration
      1. 21.1 Database Installation, Creation, and Configuration
      2. 21.2 Database Security
      3. 21.3 Database Management
      4. 21.4 Database Backup and Recovery
        1. 21.4.1 Oracle Backups: Basic Concept
        2. 21.4.2 Oracle Recovery: Basic Concept
        3. 21.4.3 Types of Failures
        4. 21.4.4 Database Backups
        5. 21.4.5 Basic Recovery Steps
        6. 21.4.6 Oracle’s Backup and Recovery Solutions
      5. 21.5 Database Tuning
        1. 21.5.1 Tuning Goals
        2. 21.5.2 Tuning Methodology
      6. 21.6 Database Removal
      7. 21.7 Summary and Concluding Remarks
      8. 21.8 Review Questions
      9. 21.9 References and/or Recommended Readings
    2. Chapter 22: Distributed Database Systems
      1. 22.1 Introduction
      2. 22.2 Advantages of Distributed Database Systems
      3. 22.3 Twelve Rules for Distributed Database Systems
      4. 22.4 Challenges to Distributed Database Systems
      5. 22.5 Database Gateways
      6. 22.6 The Future of Distributed Database Systems
        1. 22.6.1 Object Technology
        2. 22.6.2 Electronic Communication Systems
      7. 22.7 Summary and Concluding Remarks
      8. 22.8 Review Questions
      9. 22.9 References and/or Recommended Readings
    3. Chapter 23: Object Databases
      1. 23.1 Introduction
      2. 23.2 Overview of Object-Oriented Database Management Systems
      3. 23.3 Challenges for Object-Oriented Database Management Systems
      4. 23.4 Hybrid Approaches
        1. 23.4.1 Hybrid Approach A
        2. 23.4.2 Hybrid Approach B
      5. 23.5 Summary and Concluding Remarks
      6. 23.6 Review Questions
      7. 23.7 References and/or Recommended Readings
    4. Chapter 24: Data Warehousing
      1. 24.1 Introduction
      2. 24.2 Rationale for Data Warehousing
      3. 24.3 Characteristics of a Data Warehouse
        1. 24.3.1 Definitive Features
        2. 24.3.2 Nature of Data Stored
        3. 24.3.3 Processing Requirements
        4. 24.3.4 Twelve Rules That Govern a Data Warehousing
      4. 24.4 Data Warehouse Architecture
        1. 24.4.1 Basic Data Warehouse Architecture
        2. 24.4.2 Data Warehouse Architecture with a Staging Area
        3. 24.4.3 Data Warehouse Architecture with a Staging Area and Data Marts
      5. 24.5 Extraction, Transformation, and Loading
        1. 24.5.1 What Happens During the ETL Process
        2. 24.5.2 ETL Tools
        3. 24.5.3 Daily Operations and Expansion of the Data Warehouse
      6. 24.6 Summary and Concluding Remarks
      7. 24.7 Review Questions
      8. 24.8 References and/or Recommended Readings
    5. Chapter 25: Web-Accessible Databases
      1. 25.1 Introduction
      2. 25.2 Web-Accessible Database Architecture
      3. 25.3 Supporting Technologies
      4. 25.4 Implementation with Oracle
      5. 25.5 Implementation with DB2
      6. 25.6 Generic Implementation via a Front-end and a Back-end Tool
      7. 25.7 Summary and Concluding Remarks
      8. 25.8 Review Questions
      9. 25.9 References and/or Recommended Readings
  15. Part F: Final Preparations
    1. Chapter 26: Sample Exercises and Examination Questions
      1. 26.1 Introduction
      2. 26.2 Sample Assignment 1A
      3. 26.3 Sample Assignment 2B
      4. 26.4 Sample Assignment 3A
      5. 26.5 Sample Assignment 4A
      6. 26.6 Sample Assignment 5A
      7. 26.7 Sample Assignment 6A
      8. 26.8 Sample Assignment 7A
      9. 26.9 Sample Assignment 8A
      10. 26.10 Sample Interim Examination A
      11. 26.11 Sample Interim Examination B
      12. 26.12 Sample Final Examination A
      13. 26.13 Sample Final Examination B
      14. 26.14 Sample Final Examination C
  16. Part G: Appendices
    1. Appendix 1: Review of Trees
      1. A1.1 Introduction to Trees
      2. A1.2 Binary Trees
        1. A1.2.1 Overview of Binary Trees
        2. A1.2.2 Representation of Binary Trees
        3. A1.2.3 Application of Binary Trees
        4. A1.2.4 Operations on Binary Trees
        5. A1.2.5 Implementation of Binary Trees
        6. A1.2.6 Binary Tree Traversals
        7. A1.2.7 Using Binary Tree to Evaluate Expressions
      3. A1.3 Threaded Binary Trees
      4. A1.4 Binary Search Trees
      5. A1.5 Height-Balanced Trees
      6. A1.6 Heaps
        1. A1.6.1 Building the Heap
        2. A1.6.2 Processing the Heap (Heap Sort)
      7. A1.7 M-Way Search Trees and B-Trees
        1. A1.7.1 Definition of B-tree
        2. A1.7.2 Implementation of the B-tree
      8. A1.8 Summary and Concluding Remarks
      9. A1.9 References and/or Recommended Readings
    2. Appendix 2: Review of Hashing
      1. A2.1 Introduction
      2. A2.2 Hash Functions
        1. A2.2.1 Absolute Addressing
        2. A2.2.2 Direct Table Lookup
        3. A2.2.3 Division-Remainder
        4. A2.2.4 Mid-Square
        5. A2.2.5 Folding
        6. A2.2.6 Truncation
        7. A2.2.7 Treating Alphanumeric Key Values
      3. A2.3 Collision Resolution
        1. A2.3.1 Linear Probing
        2. A2.3.2 Synonym Chaining
        3. A2.3.3 Rehashing
      4. A2.4 Hashing in Java
      5. A2.5 Summary and Concluding Remarks
      6. A2.6 References and/or Recommended Readings
    3. Appendix 3: Review of Information Gathering Techniques
      1. A3.1 Rationale for Information Gathering
      2. A3.2 Interviewing
        1. Steps in Planning the Interview
        2. Basic Guidelines for Interviews
      3. A3.3 Questionnaires and Surveys
        1. Guidelines for Questionnaires
        2. Using Scales in Questionnaires
        3. Administering the Questionnaire
      4. A3.4 Sampling and Experimenting
        1. A3.4.1 Probability Sampling Techniques
        2. A3.4.2 Non-Probability sampling Techniques
        3. A3.4.3 Sample Calculations
      5. A3.5 Observation and Document Review
      6. A3.6 Prototyping
        1. Kinds of Prototypes
      7. A3.7 Brainstorming and Mathematical Proof
      8. A3.8 Object Identification
        1. A3.8.1 The Descriptive Narrative Approach
        2. A3.8.2 The Rule-of-Thumb Approach
      9. A3.9 Summary and Concluding Remarks
      10. A3.10 References and/or Recommended Readings
  17. Index