You are previewing Data Management: Databases and Organizations, Fifth Edition.
O'Reilly logo
Data Management: Databases and Organizations, Fifth Edition

Book Description

Wanted: Expert Data Modeling and SQL Skills... Inquire Within.

Data modeling and SQL--these are the data management skills that are in demand in today's job market. That's why Richard Watson's Fifth Edition of Data Management: Databases and Organizations offers in-depth, fully integrated coverage of data modeling and SQL, and a broad managerial perspective.

Updated with the latest developments in the field, the Fifth Edition will help you design and create relational databases, formulate complex SQL queries, understand OLAP, use SQL with Java, learn how to use XML, and prepare yourself for the real world of data management.

New Features of the Fifth Edition:

  • A new chapter on embedded SQL in Java and JDBC

  • A section on multidimensional expressions (MDX)

  • New material on content management systems (CMS) and wiki technology

  • Greater focus on MySQL

  • Increased coverage of mandatory and optional elements in data modeling

Table of Contents

  1. Copyright
  2. Preface
    1. Supplements
    2. New in the fifth edition
    3. Acknowledgments
  3. 1. The Managerial Perspective
    1. 1. Managing Data
      1. 1.1. Learning objectives
        1. 1.1.1. Learning objectives
        2. 1.1.2. Introduction
      2. 1.2. Individual data management
      3. 1.3. Organizational data management
        1. 1.3.1. The information systems cycle
        2. 1.3.2. Attributes of data
        3. 1.3.3. Shareable
        4. 1.3.4. Transportable
        5. 1.3.5. Secure
        6. 1.3.6. Accurate
        7. 1.3.7. Timely
        8. 1.3.8. Relevant
      4. 1.4. Components of organizational memory
        1. 1.4.1. People
        2. 1.4.2. Tables
        3. 1.4.3. Documents
        4. 1.4.4. Multimedia
          1. 1.4.4.1. Images
          2. 1.4.4.2. Graphics
          3. 1.4.4.3. Audio
          4. 1.4.4.4. Video
        5. 1.4.5. Models
        6. 1.4.6. Knowledge
        7. 1.4.7. Decisions
        8. 1.4.8. Specialized memories
        9. 1.4.9. External memories
      5. 1.5. Problems with data management systems
        1. 1.5.1. Redundancy
        2. 1.5.2. Lack of data control
        3. 1.5.3. Poor interface
        4. 1.5.4. Delays
        5. 1.5.5. Lack of reality
        6. 1.5.6. Lack of data integration
      6. 1.6. A brief history of data management systems
      7. 1.7. Data, information, and knowledge
      8. 1.8. The challenge
        1. 1.8.1. Summary
        2. 1.8.2. Key terms and concepts
        3. 1.8.3. References and additional readings
        4. 1.8.4. Exercises
    2. 2. Information
      1. 2.1.
        1. 2.1.1. Learning objectives
        2. 2.1.2. Introduction
      2. 2.2. A historical perspective
      3. 2.3. A brief history of information systems5
      4. 2.4. Information characteristics
        1. 2.4.1. Information hardness
        2. 2.4.2. Information richness
        3. 2.4.3. Information classes
      5. 2.5. Information and organizational change
        1. 2.5.1. Goal-setting information
          1. 2.5.1.1. Planning
          2. 2.5.1.2. Benchmarking
        2. 2.5.2. Gap information
          1. 2.5.2.1. Problem identification
          2. 2.5.2.2. Scorekeeping
      6. 2.6. Change information
        1. 2.6.1.
          1. 2.6.1.1. Problem solution
        2. 2.6.2. Information as a means of change
          1. 2.6.2.1. Marketing
          2. 2.6.2.2. Customer Service
          3. 2.6.2.3. Empowerment
      7. 2.7. Information and managerial work
        1. 2.7.1. Managers' information requirements
        2. 2.7.2. Managers' needs for information vary accordingly with responsibilities
        3. 2.7.3. Information satisficing
      8. 2.8. Information delivery systems
        1. 2.8.1.
          1. 2.8.1.1. Verbal exchange
          2. 2.8.1.2. Voice mail
          3. 2.8.1.3. Electronic mail
          4. 2.8.1.4. Written report
          5. 2.8.1.5. Meetings
          6. 2.8.1.6. Groupware
          7. 2.8.1.7. Management information system
          8. 2.8.1.8. Web
          9. 2.8.1.9. Image processing system
          10. 2.8.1.10. Computer-aided design
          11. 2.8.1.11. Geographic information system
          12. 2.8.1.12. Decision support system
          13. 2.8.1.13. Expert system
        2. 2.8.2. Information integration
      9. 2.9. Knowledge
        1. 2.9.1. Summary
        2. 2.9.2. Key terms and concepts
        3. 2.9.3. References and additional readings
        4. 2.9.4. Exercises
        5. 2.9.5. Case questions
  4. 2. Data Modeling and SQL
    1. 3. The Single Entity
      1. 3.1.
        1. 3.1.1. Learning objectives
      2. 3.2. The relational model
      3. 3.3. Getting started
      4. 3.4. Modeling a single-entity database
      5. 3.5. Creating a single-table database
        1. 3.5.1. Defining a table
        2. 3.5.2. Inserting rows into a table
        3. 3.5.3. Displaying an entire table
        4. 3.5.4. Project—choosing columns
        5. 3.5.5. Restrict—choosing rows
        6. 3.5.6. Combining project and restrict—choosing rows and columns
        7. 3.5.7. More about WHERE
        8. 3.5.8. The power of the primary key
        9. 3.5.9. The IN crowd
        10. 3.5.10. The NOT IN crowd
        11. 3.5.11. Ordering columns
        12. 3.5.12. Ordering rows
        13. 3.5.13. Numeric versus character sorting
        14. 3.5.14. Derived data
        15. 3.5.15. Aggregate functions
          1. 3.5.15.1. COUNT
          2. 3.5.15.2. AVG—averaging
          3. 3.5.15.3. SUM, MIN, and MAX
        16. 3.5.16. Subqueries
        17. 3.5.17. LIKE—pattern matching
        18. 3.5.18. DISTINCT—eliminating duplicate rows
        19. 3.5.19. DELETE
        20. 3.5.20. UPDATE
        21. 3.5.21. Debriefing
        22. 3.5.22. Summary
        23. 3.5.23. Key terms and concepts
        24. 3.5.24. Exercises
        25. 3.5.25. CD library case
    2. 4. The One-to-Many Relationship
      1. 4.1.
        1. 4.1.1. Learning objectives
      2. 4.2. Relationships
        1. 4.2.1. Why did we create an additional entity?
          1. 4.2.1.1. Insert anomalies
          2. 4.2.1.2. Delete anomalies
          3. 4.2.1.3. Update anomalies
      3. 4.3. Creating a database with a 1:m relationship
      4. 4.4. Querying a two-table database
        1. 4.4.1. Join
        2. 4.4.2. Control break reporting
        3. 4.4.3. GROUP BY—reporting by groups
        4. 4.4.4. HAVING—the WHERE clause of groups
        5. 4.4.5. Subqueries
          1. 4.4.5.1. Correlated subquery
        6. 4.4.6. Views—virtual tables
        7. 4.4.7. Summary
        8. 4.4.8. Key terms and concepts
        9. 4.4.9. Exercises
        10. 4.4.10. CD library case
    3. 5. The Many-to-Many Relationship
      1. 5.1.
        1. 5.1.1. Learning objectives
      2. 5.2. The many-to-many relationship
        1. 5.2.1. Why did we create a third entity?
      3. 5.3. Creating a relational database with an m:m relationship
      4. 5.4. Querying an m:m relationship
        1. 5.4.1. A three-table join
        2. 5.4.2. EXISTS—does a value exist
        3. 5.4.3. NOT EXISTS—select a value if it does not exist
        4. 5.4.4. Divide (and be conquered)
        5. 5.4.5. Beyond the great divide
        6. 5.4.6. Set operations
        7. 5.4.7. Summary
        8. 5.4.8. Key terms and concepts
        9. 5.4.9. Exercises
        10. 5.4.10. CD library case
    4. 6. One-to-One and Recursive Relationships
      1. 6.1.
        1. 6.1.1. Learning objectives
      2. 6.2. Modeling a one-to-one relationship
      3. 6.3. Mapping a one-to-one relationship
      4. 6.4. Mapping a one-to-many recursive relationship
      5. 6.5. Querying a one-to-one relationship
      6. 6.6. Querying a recursive relationship
      7. 6.7. Modeling a one-to-one recursive relationship
      8. 6.8. Mapping a one-to-one recursive relationship
      9. 6.9. Querying a one-to-one recursive relationship
      10. 6.10. Modeling a many-to-many recursive relationship
      11. 6.11. Mapping a many-to-many recursive relationship
      12. 6.12. Querying a many-to-many recursive relationship
        1. 6.12.1. Summary
        2. 6.12.2. Key terms and concepts
        3. 6.12.3. Exercises
        4. 6.12.4. CD Library case
    5. 7. Data Modeling
      1. 7.1.
        1. 7.1.1. Learning objectives
      2. 7.2. Modeling
      3. 7.3. Data modeling
      4. 7.4. The building blocks
        1. 7.4.1. Entity
        2. 7.4.2. Attribute
        3. 7.4.3. Relationship
        4. 7.4.4. Identifier
      5. 7.5. Data model quality
        1. 7.5.1. A well-formed data model
        2. 7.5.2. A high-fidelity image
      6. 7.6. Quality improvement
        1. 7.6.1. Detail and context
        2. 7.6.2. A lesson in pure geography
        3. 7.6.3. Family matters
        4. 7.6.4. When's a book not a book?
        5. 7.6.5. A history lesson
        6. 7.6.6. A ménage à trois for entities
        7. 7.6.7. Project management—planning and doing
        8. 7.6.8. Cardinality
        9. 7.6.9. Modality
        10. 7.6.10. Summary
        11. 7.6.11. Entity types
          1. 7.6.11.1. Independent entity
          2. 7.6.11.2. Weak or dependent entity
          3. 7.6.11.3. Associative entity
          4. 7.6.11.4. Aggregate entity
          5. 7.6.11.5. Subordinate entity
        12. 7.6.12. Generalization and aggregation
          1. 7.6.12.1. Generalization
        13. 7.6.13. Aggregation
        14. 7.6.14. Data modeling hints
        15. 7.6.15. The rise and fall of a data model
        16. 7.6.16. Identifier
        17. 7.6.17. Position and order
        18. 7.6.18. Attributes and consistency
        19. 7.6.19. Names and addresses
        20. 7.6.20. Single-instance entities
        21. 7.6.21. Picking words
        22. 7.6.22. Synonyms
        23. 7.6.23. Homonyms
        24. 7.6.24. Exception hunting
        25. 7.6.25. Relationship labeling
        26. 7.6.26. Keeping the data model in shape
        27. 7.6.27. Used entities
      7. 7.7. Meaningful identifiers
        1. 7.7.1. Advantages of meaningful identifiers
          1. 7.7.1.1. Recognizable and rememberable
          2. 7.7.1.2. Administrative simplicity
        2. 7.7.2. Disadvantages of meaningful identifiers
        3. 7.7.3. Identifier exhaustion
        4. 7.7.4. Reality changes
        5. 7.7.5. A meaningful identifier can lose its meaningfulness
        6. 7.7.6. The solution—nonmeaningful identifiers
      8. 7.8. The seven habits of highly effective data modelers
        1. 7.8.1. Immerse
        2. 7.8.2. Challenge
        3. 7.8.3. Generalize
        4. 7.8.4. Test
        5. 7.8.5. Limit
        6. 7.8.6. Integrate
        7. 7.8.7. Complete
        8. 7.8.8. Summary
        9. 7.8.9. Key terms and concepts
        10. 7.8.10. References and additional readings
        11. 7.8.11. Exercises
          1. 7.8.11.1. Short answers
          2. 7.8.11.2. Data modeling
    6. Reference 1: Basic Structures
      1. 7.9. One entity
        1. 7.9.1. No relationships
        2. 7.9.2. A 1:1 recursive relationship
        3. 7.9.3. A recursive 1:m relationship
        4. 7.9.4. A recursive m:m relationship
      2. 7.10. Two entities
        1. 7.10.1. No relationship
        2. 7.10.2. A 1:1 relationship
        3. 7.10.3. A 1:m relationship
        4. 7.10.4. An m:m relationship
      3. 7.11. Another entity's identifier as part of the identifier
        1. 7.11.1. A weak or dependent entity
        2. 7.11.2. An associative entity
        3. 7.11.3. A tree structure
        4. 7.11.4. Another approach to a tree structure
        5. 7.11.5. Exercises
    7. 8. Normalization and Other Data Modeling Methods
      1. 8.1.
        1. 8.1.1. Learning objectives
        2. 8.1.2. Introduction
      2. 8.2. Normalization
        1. 8.2.1. Functional dependency
          1. 8.2.1.1. One-to-one attribute relationship
          2. 8.2.1.2. One-to-many attribute relationship
          3. 8.2.1.3. Many-to-many attribute relationship
        2. 8.2.2. Normal forms
        3. 8.2.3. First normal form
        4. 8.2.4. Second normal form
        5. 8.2.5. Third normal form
        6. 8.2.6. Boyce-Codd normal form
        7. 8.2.7. Fourth normal form
        8. 8.2.8. Fifth normal form
        9. 8.2.9. Domain-key normal form
        10. 8.2.10. Conclusion
      3. 8.3. Other data modeling methods
        1. 8.3.1. The E-R model
        2. 8.3.2. IDEF1X
        3. 8.3.3. Representing relationships
        4. 8.3.4. Conclusion
        5. 8.3.5. Summary
        6. 8.3.6. Key terms and concepts
        7. 8.3.7. References and additional readings
        8. 8.3.8. Exercises
          1. 8.3.8.1. Short answers
          2. 8.3.8.2. Normalization and modeling
    8. 9. The Relational Model and Relational Algebra
      1. 9.1.
        1. 9.1.1. Learning objectives
        2. 9.1.2. Background
      2. 9.2. Data structures
        1. 9.2.1. Domain
        2. 9.2.2. Relations
        3. 9.2.3. Relational database
        4. 9.2.4. Primary key
        5. 9.2.5. Candidate key
        6. 9.2.6. Alternate key
        7. 9.2.7. Foreign key
      3. 9.3. Integrity rules
      4. 9.4. Manipulation languages
        1. 9.4.1. Relational algebra
        2. 9.4.2. Restrict
        3. 9.4.3. Project
        4. 9.4.4. Product
        5. 9.4.5. Union
        6. 9.4.6. Intersect
        7. 9.4.7. Difference
        8. 9.4.8. Join
        9. 9.4.9. Divide
        10. 9.4.10. Querying with relational algebra
      5. 9.5. A primitive set of relational operators
      6. 9.6. A fully relational database
        1. 9.6.1. The information rule
        2. 9.6.2. The guaranteed access rule
        3. 9.6.3. Systematic treatment of null values
        4. 9.6.4. Active online catalog of the relational model
        5. 9.6.5. The comprehensive data sublanguage rule
        6. 9.6.6. The view updating rule
        7. 9.6.7. High-level insert, update, and delete
        8. 9.6.8. Physical data independence
        9. 9.6.9. Logical data independence
        10. 9.6.10. Integrity independence
        11. 9.6.11. Distribution independence
        12. 9.6.12. The nonsubversion rule
        13. 9.6.13. Summary
        14. 9.6.14. Key terms and concepts
        15. 9.6.15. References and additional readings
        16. 9.6.16. Exercises
    9. 10. SQL
      1. 10.1.
        1. 10.1.1. Learning objectives
        2. 10.1.2. Introduction
      2. 10.2. Data definition
        1. 10.2.1. Keys
        2. 10.2.2. Indexes
        3. 10.2.3. Notation
        4. 10.2.4. Creating a table
          1. 10.2.4.1. Column definition
        5. 10.2.5. Constraints
          1. 10.2.5.1. Primary key constraint
          2. 10.2.5.2. Foreign key constraint
          3. 10.2.5.3. Unique constraint
          4. 10.2.5.4. Check constraint
        6. 10.2.6. Data types
        7. 10.2.7. BOOLEAN
        8. 10.2.8. SMALLINT and INTEGER
        9. 10.2.9. FLOAT
        10. 10.2.10. DECIMAL
        11. 10.2.11. CHAR and VARCHAR
          1. 10.2.11.1. Times and dates
          2. 10.2.11.2. BLOB (binary large object)
          3. 10.2.11.3. CLOB (character large object)
          4. 10.2.11.4. Special registers
        12. 10.2.12. Scalar functions
        13. 10.2.13. Altering a table
        14. 10.2.14. Dropping a table
        15. 10.2.15. Creating a view
        16. 10.2.16. Dropping a view
        17. 10.2.17. Creating an index
        18. 10.2.18. Dropping an index
      3. 10.3. Data manipulation
        1. 10.3.1. Qualifying column names
        2. 10.3.2. Temporary names
        3. 10.3.3. SELECT
          1. 10.3.3.1. Product
            1. 10.3.3.1.1. Join
            2. 10.3.3.1.2. Outer join
          2. 10.3.3.2. Simple subquery
          3. 10.3.3.3. Correlated subquery
          4. 10.3.3.4. Aggregate functions
        4. 10.3.4. GROUP BY and HAVING
        5. 10.3.5. LIKE
        6. 10.3.6. INSERT
          1. 10.3.6.1. Inserting a single record
          2. 10.3.6.2. Inserting multiple records using a query
        7. 10.3.7. UPDATE
          1. 10.3.7.1. Updating a single row
          2. 10.3.7.2. Updating multiple rows
          3. 10.3.7.3. Updating all rows
          4. 10.3.7.4. Updating with a subquery
          5. 10.3.7.5. DELETE
          6. 10.3.7.6. Delete a single record
          7. 10.3.7.7. Delete multiple records
          8. 10.3.7.8. Delete all records
          9. 10.3.7.9. Delete with a subquery
      4. 10.4. SQL routines
        1. 10.4.1. SQL function
        2. 10.4.2. SQL procedure
      5. 10.5. Triggers
      6. 10.6. Nulls—much ado about missing information
      7. 10.7. Security
        1. 10.7.1. GRANT
          1. 10.7.1.1. The WITH GRANT OPTION clause
        2. 10.7.2. REVOKE
          1. 10.7.2.1. Cascading revoke
      8. 10.8. The catalog
      9. 10.9. Natural language processing
      10. 10.10. Connectivity and ODBC
        1. 10.10.1. Open database connectivity (ODBC)
      11. 10.11. Embedded SQL
      12. 10.12. The future of SQL
        1. 10.12.1. SQL-99
          1. 10.12.1.1. User-defined types
        2. 10.12.2. SQLJ
        3. 10.12.3. Summary
        4. 10.12.4. Key terms and concepts
        5. 10.12.5. References and additional readings
        6. 10.12.6. Exercises
    10. Reference 2: SQL Playbook
      1. 10.13. The power of SQL
        1. 10.13.1. 1. A slow full toss
        2. 10.13.2. 2. Skinning a cat
          1. 10.13.2.1. 2.1 Join (545 units)
          2. 10.13.2.2. 2.2 In (8)
          3. 10.13.2.3. 2.3 Correlated subquery (7,780)
          4. 10.13.2.4. 2.4 Exists (8,080)
        3. 10.13.3. 3. Another full toss
        4. 10.13.4. 4. Subtracting from all
        5. 10.13.5. 5. Dividing
        6. 10.13.6. 6. At least some number
        7. 10.13.7. 7. A friendly IN for an SQL traveler
        8. 10.13.8. 8. Joining a table with itself
        9. 10.13.9. 9. A combination of subtract from all and a self-join
        10. 10.13.10. 10. Self-join with GROUP BY
        11. 10.13.11. 11. A self-join with two matching conditions
        12. 10.13.12. 12. Averaging with GROUP BY
        13. 10.13.13. 13. Inner query GROUP BY and HAVING
        14. 10.13.14. 14. An IN with GROUP BY and COUNT
        15. 10.13.15. 15. A self-join with some conditions
        16. 10.13.16. 16. Making comparisons
        17. 10.13.17. 17. An IN with GROUP BY and SUM
        18. 10.13.18. 18. A double divide!
        19. 10.13.19. 19. A slam dunk
        20. 10.13.20. 20. A 6-inch putt for a birdie
        21. 10.13.21. 21. Making the count
        22. 10.13.22. 22. Minus and divide
        23. 10.13.23. 23. Division with copies
        24. 10.13.24. 24. A difficult pairing
        25. 10.13.25. 25. Two divides and an intersection
        26. 10.13.26. 26. A divide with a matching condition
        27. 10.13.27. 27. Restricted divide
        28. 10.13.28. 28. A NOT IN variation on divide
        29. 10.13.29. 29. All and only
        30. 10.13.30. 30. Divide with an extra condition
        31. 10.13.31. 31. At least some COUNT
        32. 10.13.32. 32. Double divide with a restriction
        33. 10.13.33. 33. Triple divide with an intersection
        34. 10.13.34. 34. An easy one COUNT
        35. 10.13.35. 35. The only one
        36. 10.13.36. 36. At least some number
        37. 10.13.37. 37. A three-table join
        38. 10.13.38. 38. Using NOT IN like NOT EXISTS
        39. 10.13.39. 39. Minus after GROUP BY
        40. 10.13.40. 40. Something to all
        41. 10.13.41. 41. Intersection (AND)
        42. 10.13.42. 42. Union (OR)
        43. 10.13.43. 43. Intersection/union
        44. 10.13.44. 44. Averaging with a condition
        45. 10.13.45. 45. Averaging with grouping
        46. 10.13.46. 46. Average with a join, condition, and grouping
        47. 10.13.47. 47. Averaging with multiple joins
        48. 10.13.48. 48. Complex counting
        49. 10.13.49. 49. Summing with joins and conditions
        50. 10.13.50. 50. Summing with joins, conditions, and grouping
        51. 10.13.51. 51. Advanced summing
        52. 10.13.52. 52. Comparing to the average with a join
        53. 10.13.53. 53. Comparing to the average with a product
        54. 10.13.54. 54. Averaging with multiple grouping
        55. 10.13.55. 55. More than the average with grouping
        56. 10.13.56. 56. The simplest average
        57. 10.13.57. 57. Difference from the average
        58. 10.13.58. 58. Averaging with multiple joins, multiple grouping, and a condition
        59. 10.13.59. 59. Detailed averaging
        60. 10.13.60. 60. Counting pairs
        61. 10.13.61. 61. No Booleans
        62. 10.13.62. Summary
        63. 10.13.63. Key terms and concepts
          1. 10.13.63.1. Exercises
        64. 10.13.64. Data for tables
        65. 10.13.65. QSALE
        66. 10.13.66. QSPL
        67. 10.13.67. QITEM
        68. 10.13.68. QDEPT
        69. 10.13.69. QDEL
        70. 10.13.70. QEMP
  5. 3. Database Architectures and Implementations
    1. 11. Data Structure and Storage
      1. 11.1.
        1. 11.1.1. Learning objectives
        2. 11.1.2. Introduction
      2. 11.2. Data structures
        1. 11.2.1. Database access
        2. 11.2.2. The disk manager
        3. 11.2.3. The file manager
        4. 11.2.4. Techniques for reducing head movement
          1. 11.2.4.1. Cylinders
          2. 11.2.4.2. Clustering
        5. 11.2.5. Techniques for reducing disk accesses
        6. 11.2.6. Indexing
          1. 11.2.6.1. Multiple indexes
          2. 11.2.6.2. Sparse indexes
        7. 11.2.7. B-trees
        8. 11.2.8. Hashing
        9. 11.2.9. Linked lists
        10. 11.2.10. Bitmap index
        11. 11.2.11. Join index
      3. 11.3. Data coding standards
        1. 11.3.1. ASCII
        2. 11.3.2. Unicode
      4. 11.4. Data storage devices
        1. 11.4.1. Magnetic technology
          1. 11.4.1.1. Fixed magnetic disk
          2. 11.4.1.2. RAID
          3. 11.4.1.3. Removable magnetic disk
          4. 11.4.1.4. Magnetic tape
          5. 11.4.1.5. Magnetic tape cartridges
          6. 11.4.1.6. Mass storage
        2. 11.4.2. Solid-state memory
        3. 11.4.3. Optical technology
          1. 11.4.3.1. CD-ROM
          2. 11.4.3.2. Magneto-optical disk
          3. 11.4.3.3. Digital versatile disc
          4. 11.4.3.4. The future
        4. 11.4.4. Storage-area networks
        5. 11.4.5. Long-term storage
      5. 11.5. Data compression
        1. 11.5.1. Lossless compression
        2. 11.5.2. Lossy compression
      6. 11.6. Comparative analysis
        1. 11.6.1.
          1. 11.6.1.1. Legend
        2. 11.6.2. Conclusion
        3. 11.6.3. Summary
        4. 11.6.4. Key terms and concepts
        5. 11.6.5. Exercises
    2. 12. Data Processing Architectures
      1. 12.1.
        1. 12.1.1. Learning Objectives
      2. 12.2. Introduction
        1. 12.2.1. Remote job entry
        2. 12.2.2. Personal database
        3. 12.2.3. Host/terminal
        4. 12.2.4. LAN-based architectures
        5. 12.2.5. File server
        6. 12.2.6. DBMS server
      3. 12.3. Client/server fundamentals
      4. 12.4. Client/server—the second generation
        1. 12.4.1. Two-tier versus three-tier
      5. 12.5. Distributed database
        1. 12.5.1. A hybrid, distributed architecture
        2. 12.5.2. Transparency
        3. 12.5.3. No reliance on a central site
        4. 12.5.4. Local autonomy
        5. 12.5.5. Continuous operation
        6. 12.5.6. Distributed query processing
        7. 12.5.7. Distributed transaction processing
        8. 12.5.8. Fragmentation independence
        9. 12.5.9. Replication independence
        10. 12.5.10. Hardware independence
        11. 12.5.11. Operating system independence
        12. 12.5.12. Network independence
        13. 12.5.13. DBMS independence
        14. 12.5.14. Conclusion—paradise postponed
      6. 12.6. Distributed data access
        1. 12.6.1. Remote request
        2. 12.6.2. Remote transaction
        3. 12.6.3. Distributed transaction
        4. 12.6.4. Distributed request
      7. 12.7. Distributed database design
        1. 12.7.1. Horizontal fragmentation
          1. 12.7.1.1. Vertical fragmentation
          2. 12.7.1.2. Hybrid fragmentation
          3. 12.7.1.3. Replication
          4. 12.7.1.4. Conclusion
          5. 12.7.1.5. Summary
          6. 12.7.1.6. Key terms and concepts
          7. 12.7.1.7. References and additional readings
          8. 12.7.1.8. Exercises
    3. 13. Object-Oriented Data Management
      1. 13.1.
        1. 13.1.1. Learning objectives
        2. 13.1.2. Introduction
      2. 13.2. UML
      3. 13.3. Historical development
      4. 13.4. Key OO concepts
        1. 13.4.1. Data abstraction
        2. 13.4.2. Object instances and classes
        3. 13.4.3. Encapsulation
        4. 13.4.4. Message passing
        5. 13.4.5. Generalization/specialization hierarchies
        6. 13.4.6. Inheritance
        7. 13.4.7. Reuse
      5. 13.5. Why OO?
        1. 13.5.1. Productivity
        2. 13.5.2. Maintenance
          1. 13.5.2.1. Summary
      6. 13.6. Objects and information system modeling
        1. 13.6.1. Models and abstraction levels
        2. 13.6.2. The client-developer mind-frame gap
        3. 13.6.3. Data and procedures
        4. 13.6.4. Statics and dynamics
        5. 13.6.5. Scenarios
        6. 13.6.6. Static OO modeling
          1. 13.6.6.1. Finding objects and classes
        7. 13.6.7. Class and object layer
        8. 13.6.8. Structure layer
          1. 13.6.8.1. Generalization
          2. 13.6.8.2. Multiple inheritance
          3. 13.6.8.3. Aggregation
        9. 13.6.9. Attribute layer
          1. 13.6.9.1. Attributes
          2. 13.6.9.2. Association relationships
        10. 13.6.10. Service layer
      7. 13.7. The OO and relational paradigms
      8. 13.8. Mapping objects to a relational database
        1. 13.8.1. Mapping attributes to columns
        2. 13.8.2. Generalization
          1. 13.8.2.1. Vertical mapping
          2. 13.8.2.2. Horizontal mapping
          3. 13.8.2.3. Filtered mapping
        3. 13.8.3. Association and aggregation
      9. 13.9. Persistent objects
      10. 13.10. Object-oriented database management systems
        1. 13.10.1. ODBMS standards
      11. 13.11. Directions
        1. 13.11.1. Hybrid technology
          1. 13.11.1.1. The future of ODBMS
        2. 13.11.2. Summary
        3. 13.11.3. Key terms and concepts
        4. 13.11.4. References and additional readings
        5. 13.11.5. Exercises
    4. 14. Spatial and Temporal Data Management
      1. 14.1.
        1. 14.1.1. Learning objectives
      2. 14.2. Introduction
      3. 14.3. Managing spatial data
        1. 14.3.1. R-tree
      4. 14.4. Managing temporal data
        1. 14.4.1. Times remembered
          1. 14.4.1.1. Interval
          2. 14.4.1.2. Modeling temporal data
        2. 14.4.2. Summary
        3. 14.4.3. Key terms and concepts
        4. 14.4.4. References and additional readings
        5. 14.4.5. Exercises
  6. 4. Organizational Memory Technologies
    1. 15. Organizational Intelligence Technologies
      1. 15.1.
        1. 15.1.1. Learning objectives
        2. 15.1.2. Introduction
        3. 15.1.3. An organizational intelligence system
      2. 15.2. The data warehouse
        1. 15.2.1. Creating and maintaining the data warehouse
          1. 15.2.1.1. Extraction
          2. 15.2.1.2. Transformation
          3. 15.2.1.3. Cleaning
          4. 15.2.1.4. Loading
          5. 15.2.1.5. Scheduling
          6. 15.2.1.6. Metadata
        2. 15.2.2. Data warehouse technology
          1. 15.2.2.1. Warehouse architectures
          2. 15.2.2.2. Server and DBMS selection
            1. 15.2.2.2.1. Server options
            2. 15.2.2.2.2. DBMS options
            3. 15.2.2.2.3. Hardware and software
      3. 15.3. Exploiting data stores
        1. 15.3.1. Verification and discovery
      4. 15.4. OLAP
        1. 15.4.1. MDDB
          1. 15.4.1.1.
            1. 15.4.1.1.1. ROLAP
            2. 15.4.1.1.2. Rotation, drill-down, and drill-through
        2. 15.4.2. The hypercube
          1. 15.4.2.1. The link between RDBMS and MDDB
        3. 15.4.3. Designing a multidimensional database
      5. 15.5. Multidimensional expressions (MDX)
        1. 15.5.1. Mondrian
        2. 15.5.2. JPivot
      6. 15.6. Data mining
        1. 15.6.1. Data mining uses
        2. 15.6.2. Data mining functions
          1. 15.6.2.1. Associations
          2. 15.6.2.2. Sequential patterns
          3. 15.6.2.3. Classifying
          4. 15.6.2.4. Clustering
          5. 15.6.2.5. Prediction
        3. 15.6.3. Data mining technologies
          1. 15.6.3.1. Decision trees
          2. 15.6.3.2. Genetic algorithms
          3. 15.6.3.3. K-nearest-neighbor method
          4. 15.6.3.4. Neural networks
          5. 15.6.3.5. Data visualization
        4. 15.6.4. SQL-99 and OLAP
          1. 15.6.4.1. Grouping sets
          2. 15.6.4.2. Rollup
          3. 15.6.4.3. Cube
        5. 15.6.5. Conclusion
        6. 15.6.6. Summary
        7. 15.6.7. Key terms and concepts
        8. 15.6.8. References
        9. 15.6.9. Exercises
    2. 16. The Web and Data Management
      1. 16.1.
        1. 16.1.1. Learning objectives
        2. 16.1.2. Introduction
      2. 16.2. Information presentation
        1. 16.2.1. Organization models
        2. 16.2.2. Information design
        3. 16.2.3. Navigation aids
        4. 16.2.4. The design process
          1. 16.2.4.1. Consistency
          2. 16.2.4.2. A performance, not a system
      3. 16.3. Web site management
        1. 16.3.1. Creating and maintaining HTML files
        2. 16.3.2. Managing a Web site
          1. 16.3.2.1. Visual site management
          2. 16.3.2.2. Automatic link changes
          3. 16.3.2.3. Link checker
          4. 16.3.2.4. Web site publishing
          5. 16.3.2.5. Data management guidelines
            1. 16.3.2.5.1. All images in one file
            2. 16.3.2.5.2. Consistent naming
      4. 16.4. Content management systems
        1. 16.4.1. Plone
          1. 16.4.1.1. Membership
          2. 16.4.1.2. Workflow
            1. 16.4.1.2.1. Internationalization and localization
            2. 16.4.1.2.2. Standards compliance
        2. 16.4.2. Summary
      5. 16.5. Wiki
        1. 16.5.1. Resource Description Framework
      6. 16.6. Web browser-to-DBMS server connectivity
        1. 16.6.1. Options
        2. 16.6.2. CGI
        3. 16.6.3. ASP
          1. 16.6.3.1. Java servlets and JSP
        4. 16.6.4. PHP
        5. 16.6.5. Conclusion
        6. 16.6.6. Summary
        7. 16.6.7. Key terms and concepts
        8. 16.6.8. References
        9. 16.6.9. Exercises
    3. 17. SQL and Java
      1. 17.1.
        1. 17.1.1. Learning objectives
        2. 17.1.2. Introduction
      2. 17.2. JDBC
        1. 17.2.1. The JDBC core
      3. 17.3. Using SQL within Java
        1. 17.3.1. Connect to the database
          1. 17.3.1.1. Loading the driver
          2. 17.3.1.2. Connecting to the DBMS
        2. 17.3.2. Create an SQL statement
        3. 17.3.3. Execute a SELECT
        4. 17.3.4. Report a SELECT
        5. 17.3.5. Inserting a row
        6. 17.3.6. Release the Statement object
        7. 17.3.7. Release the Connection object
        8. 17.3.8. Map collection case
          1. 17.3.8.1. Data entry
          2. 17.3.8.2. Data validation
          3. 17.3.8.3. Transaction processing
            1. 17.3.8.3.1. Autocommit
            2. 17.3.8.3.2. Commit
            3. 17.3.8.3.3. Rollback
            4. 17.3.8.3.4. Inserting a new map
            5. 17.3.8.3.5. Inserting the countries on a map
            6. 17.3.8.3.6. Completing the transaction
            7. 17.3.8.3.7. Putting it all together
          4. 17.3.8.4. The complete application
        9. 17.3.9. Conclusion
        10. 17.3.10. Summary
        11. 17.3.11. Key terms and concepts
        12. 17.3.12. References
        13. 17.3.13. Exercises
    4. 18. XML: Managing Data Exchange
      1. 18.1.
        1. 18.1.1. Learning objectives
        2. 18.1.2. Introduction
      2. 18.2. EDI
      3. 18.3. SGML
      4. 18.4. XML
        1. 18.4.1. XML language design
        2. 18.4.2. XML schema
          1. 18.4.2.1. DOM
        3. 18.4.3. CD library case
          1. 18.4.3.1. XSL
        4. 18.4.4. Converting XML
        5. 18.4.5. XML and databases
          1. 18.4.5.1. RDBMS
            1. 18.4.5.1.1. DB2XML
          2. 18.4.5.2. ODBMS
          3. 18.4.5.3. XML database
      5. 18.5. Conclusion
        1. 18.5.1. Summary
        2. 18.5.2. Key terms and concepts
        3. 18.5.3. References
        4. 18.5.4. Exercises
        5. 18.5.5. Case: A conference support system
  7. 5. Managing Organizational Memory
    1. 19. Data Integrity
      1. 19.1.
        1. 19.1.1. Learning objectives
        2. 19.1.2. Introduction
      2. 19.2. Transaction management
        1. 19.2.1. Concurrent update control
          1. 19.2.1.1. Lost update
          2. 19.2.1.2. The solution: locking
        2. 19.2.2. Transaction failure and recovery
      3. 19.3. Protecting existence
        1. 19.3.1. Backup and recovery
          1. 19.3.1.1. Storage-medium destruction
          2. 19.3.1.2. Abnormal termination of an update transaction
          3. 19.3.1.3. Incorrect data discovered
        2. 19.3.2. Recovery strategies
          1. 19.3.2.1. Switching to a duplicate database
          2. 19.3.2.2. Backward recovery or rollback
          3. 19.3.2.3. Forward recovery or roll forward
          4. 19.3.2.4. Reprocessing transactions
        3. 19.3.3. Use of recovery procedures
      4. 19.4. Maintaining data quality
        1. 19.4.1. Dimensions
        2. 19.4.2. DBMS and data quality
          1. 19.4.2.1. Update authorization
          2. 19.4.2.2. Data validation using integrity constraints
      5. 19.5. Ensuring confidentiality
        1. 19.5.1. General model of data security
        2. 19.5.2. Data access control
          1. 19.5.2.1. Implementing authorization rules
        3. 19.5.3. Encryption
        4. 19.5.4. Monitoring activity
        5. 19.5.5. Summary
        6. 19.5.6. Key terms and concepts
        7. 19.5.7. References and additional readings
        8. 19.5.8. Exercises
    2. 20. Data Administration
      1. 20.1.
        1. 20.1.1. Learning objectives
      2. 20.2. Introduction
        1. 20.2.1. Why manage data?
      3. 20.3. Management of the database environment
        1. 20.3.1. Databases
        2. 20.3.2. Interface
        3. 20.3.3. Data dictionary
        4. 20.3.4. External databases
      4. 20.4. Data administration
        1. 20.4.1. Data administration functions and roles
        2. 20.4.2. Database levels
        3. 20.4.3. System-level data administration functions
          1. 20.4.3.1. Planning
          2. 20.4.3.2. Developing data standards and policies
          3. 20.4.3.3. Defining XML data schemas
          4. 20.4.3.4. Maintaining data integrity
          5. 20.4.3.5. Resolving data conflict
          6. 20.4.3.6. Managing the DBMS
          7. 20.4.3.7. Establishing and maintaining the data dictionary
          8. 20.4.3.8. Selecting hardware and software
          9. 20.4.3.9. Benchmarking
          10. 20.4.3.10. TPC-C
          11. 20.4.3.11. TPC-H
          12. 20.4.3.12. TPC-R
          13. 20.4.3.13. TPC-W
          14. 20.4.3.14. Managing external databases
          15. 20.4.3.15. Internal marketing
        4. 20.4.4. Project-level data administration
          1. 20.4.4.1. Systems development life cycle (SDLC)
          2. 20.4.4.2. Database development roles
          3. 20.4.4.3. Database development life cycle (DDLC)
          4. 20.4.4.4. Database project planning
          5. 20.4.4.5. Requirements definition
          6. 20.4.4.6. Database design
          7. 20.4.4.7. Database testing
          8. 20.4.4.8. Database implementation
          9. 20.4.4.9. Database use
          10. 20.4.4.10. Database evolution
        5. 20.4.5. Data administration interfaces
          1. 20.4.5.1. Management
          2. 20.4.5.2. Clients
          3. 20.4.5.3. Development staff
          4. 20.4.5.4. Computer operations
        6. 20.4.6. Communication
        7. 20.4.7. Data administration tools
          1. 20.4.7.1. Data dictionary/directory system (DD/DS)
      5. 20.5. Database management systems (DBMSs)
        1. 20.5.1. Performance monitoring tools
        2. 20.5.2. CASE tools
      6. 20.6. Groupware
      7. 20.7. Data integration
      8. 20.8. Organizing data administration
        1. 20.8.1. Initiating data administration
        2. 20.8.2. Selecting data administration staff
        3. 20.8.3. Locating data administration in the organization
        4. 20.8.4. Summary
        5. 20.8.5. Key terms and concepts
        6. 20.8.6. References and additional readings
        7. 20.8.7. Exercises
    3. 21. U-Commerce and Data Management
      1. 21.1.
        1. 21.1.1. Learning objectives
      2. 21.2. Introduction
      3. 21.3. U-commerce
        1. 21.3.1. Ubiquitous
        2. 21.3.2. Universal
        3. 21.3.3. Unique
        4. 21.3.4. Unison
      4. 21.4. A conceptual framework for u-commerce
        1. 21.4.1. The hyper-real (ultraconscious, unique)
        2. 21.4.2. The posthuman (ultraconscious, ubiquitous)
        3. 21.4.3. The matrix (unconscious, ubiquitous)
        4. 21.4.4. The node (unconscious, unique)
      5. 21.5. Implications
        1. 21.5.1. Summary
        2. 21.5.2. Key terms and concepts
        3. 21.5.3. References and additional readings
        4. 21.5.4. Exercises
    4. Photo Credits