O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Databases Illuminated, 3rd Edition

Book Description

Databases Illuminated, Third Edition Includes Navigate 2 Advantage Access combines database theory with a practical approach to database design and implementation. Strong pedagogical features, including accessible language, real-world examples, downloadable code, and engaging hands-on projects and lab exercises create a text with a unique combination of theory and student-oriented activities. Providing an integrated, modern approach to databases, Databases Illuminated, Third Edition is the essential text for students in this expanding field.

Table of Contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Dedication
  5. Table of Contents
  6. Preface
  7. Acknowledgments
  8. 1. Introductory Database Concepts
    1. Chapter Objectives
    2. 1.1 Uses of Databases
    3. 1.2 A Sample Database
    4. 1.3 The Integrated Database Environment
    5. 1.4 Roles in the Integrated Database Environment
    6. 1.5 Advantages of the Integrated Database Approach
    7. 1.6 Historical Developments in Information Systems
      1. 1.6.1 Big Data
    8. 1.7 Chapter Summary
    9. Exercises
  9. 2. Database Planning and Database Architecture
    1. Chapter Objectives
    2. 2.1 Data as a Resource
    3. 2.2 Characteristics of Data
      1. 2.2.1 Data and Information
      2. 2.2.2 Levels of Discussing Data
      3. 2.2.3 Data Sublanguages
    4. 2.3 Stages in Database Design
    5. 2.4 Design Tools
      1. 2.4.1 Data Dictionary
      2. 2.4.2 Project Management Software
      3. 2.4.3 Diagramming Tools
      4. 2.4.4 Case Packages
    6. 2.5 Database Administration
      1. 2.5.1 Planning and Design
      2. 2.5.2 Developing the Database
      3. 2.5.3 Database Management
    7. 2.6 The Three-Level Database Architecture
      1. 2.6.1 External Views
      2. 2.6.2 Logical and Conceptual Models
      3. 2.6.3 Internal Model
      4. 2.6.4 Data Independence
    8. 2.7 Overview of Data Models
      1. 2.7.1 The Entity-Relationship Model
      2. 2.7.2 Relational and Other Record-Based Models
      3. 2.7.3 Object-Oriented Model
      4. 2.7.4 Object-Relational Model
      5. 2.7.5 Large-Scale Data Models
    9. 2.8 Chapter Summary
    10. Exercises
  10. 3. The Entity-Relationship Model
    1. Chapter Objectives
    2. 3.1 Purpose of the E-R Model
    3. 3.2 Entities
    4. 3.3 Attributes
      1. 3.3.1 Domains
      2. 3.3.2 Null Values
      3. 3.3.3 Multivalued Attributes
      4. 3.3.4 Composite Attributes
      5. 3.3.5 Derived Attributes
    5. 3.4 Keys
      1. 3.4.1 Superkeys
      2. 3.4.2 Candidate Keys
      3. 3.4.3 Primary Keys
    6. 3.5 Relationships
      1. 3.5.1 Degree of Relationships
      2. 3.5.2 Attributes of Relationship Sets
      3. 3.5.3 Cardinality of a Binary Relationship
      4. 3.5.4 Showing Cardinalities on an E-R Diagram
      5. 3.5.5 Participation Constraints
      6. 3.5.6 Using (min,max) Notation for Cardinality and Participation
    7. 3.6 Roles
    8. 3.7 Existence Dependency and Weak Entities
    9. 3.8 Sample E-R Diagrams
    10. 3.9 Rationale for Extending the E-R Model
    11. 3.10 Generalization and Specialization
      1. 3.10.1 Specialization
      2. 3.10.2 Generalization
      3. 3.10.3 Generalization Constraints—Disjointness, Completeness, Definition Method
      4. 3.10.4 Multiple Hierarchies and Inheritance
    12. 3.11 Union
    13. 3.12 Sample EE-R Diagrams
    14. 3.13 Chapter Summary
    15. Exercises
  11. 4. The Relational Model
    1. Chapter Objectives
    2. 4.1 Advantages of the Relational Model
    3. 4.2 Relational Data Structures
      1. 4.2.1 Tables
      2. 4.2.2 Mathematical Relations
      3. 4.2.3 Database Relations and Tables
      4. 4.2.4 Properties of Relations
      5. 4.2.5 Degree and Cardinality
      6. 4.2.6 Relation Keys
    4. 4.3 Integrity Constraints: domain, key, foreign key, general constraints
    5. 4.4 Representing Relational Database Schemas
    6. 4.5 Relational Data Manipulation Languages
      1. 4.5.1 Categories of DMLs
      2. 4.5.2 Relational Algebra
    7. 4.6 Views
    8. 4.7 Mapping an E-R Model to a Relational Schema
    9. 4.8 Mapping an EE-R Model to a Relational Schema
      1. 4.8.1 Summary of E-R to Strictly Relational Mapping Concepts
      2. 4.8.2 Mapping EE-R Set Hierarchies to Relational Tables
      3. 4.8.3 Mapping Unions
      4. 4.8.4 EE-R to Relational Mapping Example
    10. 4.9 Chapter Summary
    11. Exercises
  12. 5. Relational Database Management Systems and SQL
    1. Chapter Objectives
    2. 5.1 Brief History of SQL in Relational Database Systems
    3. 5.2 Architecture of a Relational Database Management System
    4. 5.3 Defining the Database: SQL DDL
      1. 5.3.1 Create Database, Create Schema
      2. 5.3.2 Create Table
      3. 5.3.3 Create Index
      4. 5.3.4 Alter Table, Rename Table
      5. 5.3.5 Drop Statements
      6. 5.3.6 Additional SQL DDL Example
    5. 5.4 Manipulating the Database: SQL DML
      1. 5.4.1 Introduction to the SELECT Statement
      2. 5.4.2 Select Using Multiple Tables
      3. 5.4.3 Select with Other Operators
      4. 5.4.4 Operators for Updating: Update, Insert, Delete
      5. 5.4.5 Creating and Using Views
    6. 5.5 Active Databases
      1. 5.5.1 Enabling and Disabling Constraints
      2. 5.5.2 SQL Triggers
    7. 5.6 Using Commit and Rollback Statements
    8. 5.7 Temporal Databases and SQL
      1. 5.7.1 User Valid Time
      2. 5.7.2 Transaction Time
    9. 5.8 SQL Programming
      1. 5.8.1 SQL PSMs
      2. 5.8.2 Embedded SQL
      3. 5.8.3 Dynamic SQL
      4. 5.8.4 Application Programming Interfaces (APIs)
    10. 5.9 JDBC
      1. 5.9.1 Developing a JDBC Application
      2. 5.9.2 The Statement Object
      3. 5.9.3 The PreparedStatement Object
      4. 5.9.4 The CallableStatement Object
    11. 5.10 The System Catalog
    12. 5.11 Chapter Summary
    13. Exercises
  13. 6. Normalization and Denormalization
    1. Chapter Objectives
    2. 6.1 Objectives of Normalization
    3. 6.2 Insertion, Update, and Deletion Anomalies
    4. 6.3 Functional Dependency
    5. 6.4 Superkeys, Candidate Keys, and Primary Keys
    6. 6.5 Normalization Using Primary Keys
      1. 6.5.1 First Normal Form
      2. 6.5.2 Full Functional Dependency and Second Normal Form
      3. 6.5.3 Transitive Dependency and Third Normal Form
      4. 6.5.4 Boyce-Codd Normal Form
      5. 6.5.5 Comprehensive Example of Functional Dependencies
    7. 6.6 Properties of Relational Decompositions
      1. 6.6.1 Attribute Preservation
      2. 6.6.2 Dependency Preservation
      3. 6.6.3 Lossless Decomposition
      4. 6.6.4 Decomposition Algorithm for Boyce-Codd Normal Form with Lossless Join
    8. 6.7 The Normalization Process
      1. 6.7.1 Analysis
      2. 6.7.2 Synthesis
      3. 6.7.3 Normalization from an Entity-Relationship Diagram
    9. 6.8 When to Stop Normalizing
    10. 6.9 Non-normalized Databases
    11. 6.10 Chapter Summary
    12. Exercises
  14. 7. Object-Based Models
    1. Chapter Objectives
    2. 7.1 Rationale for the Object-Oriented Data Model
    3. 7.2 Object-Oriented Data Concepts
      1. 7.2.1 Objects and Literals
      2. 7.2.2 Classes
      3. 7.2.3 Class Hierarchies and Inheritance
      4. 7.2.4 Object Identity
    4. 7.3 Object-Oriented Data Modeling Using UML
    5. 7.4 The ODMG Model and ODL
      1. 7.4.1 Class Declarations
      2. 7.4.2 Extent
      3. 7.4.3 Attributes
      4. 7.4.4 Relationships
      5. 7.4.5 Methods
      6. 7.4.6 Classes and Inheritance
      7. 7.4.7 N-ary Relationships and M:N Relationships with Attributes
      8. 7.4.8 Keys
    6. 7.5 Object Query Language
    7. 7.6 Developing an OO Database
    8. 7.7 Developing an OO Database in Caché
    9. 7.8 Extending the Relational Model
      1. 7.8.1 Additional Fundamental Data Types
      2. 7.8.2 Collection Types
      3. 7.8.3 User-Defined Data Types (UDTs)
      4. 7.8.4 Reference Types
      5. 7.8.5 Type Hierarchies in Standard SQL
      6. 7.8.6 Type Hierarchies in Oracle
      7. 7.8.7 Nested Tables in Oracle
      8. 7.8.8 Oracle Object Views
    10. 7.9 Converting a UML Diagram to an Object-Relational Database Model
    11. 7.10 Converting an EE-R Diagram to an Object-Relational Database Model
    12. 7.11 Chapter Summary
    13. Exercises
  15. 8. Introduction to Database Security
    1. Chapter Objectives
    2. 8.1 Issues in Database Security
      1. 8.1.1 Accidental Security Threats
      2. 8.1.2 Deliberate Security Threats
    3. 8.2 Fundamentals of Access Control
      1. 8.2.1 Physical Security
      2. 8.2.2 Information System Access Control
    4. 8.3 Database Access Control
    5. 8.4 Using Views for Access Control
    6. 8.5 Security Logs and Audit Trails
    7. 8.6 Encryption
      1. 8.6.1 Symmetric Key Encryption
      2. 8.6.2 Public-Key Encryption
    8. 8.7 SQL Data Control Language
    9. 8.8 Security in Oracle
      1. 8.8.1 Security Features
      2. 8.8.2 Administrative Accounts
      3. 8.8.3 Security Tools
    10. 8.9 Statistical Database Security
    11. 8.10 SQL Injection
      1. 8.10.1 Examples of SQL Injection
      2. 8.10.2 Mitigation of SQL Injection
    12. 8.11 Database Security and the Internet
      1. 8.11.1 Proxy Servers
      2. 8.11.2 Firewalls
      3. 8.11.3 Digital Signatures
      4. 8.11.4 Certification Authorities
    13. 8.12 Chapter Summary
    14. Exercises
  16. 9. Transaction Management
    1. Chapter Objectives
    2. 9.1 ACID Properties of Transactions
    3. 9.2 Need for Concurrency Control
      1. 9.2.1 The Lost Update Problem
      2. 9.2.2 The Uncommitted Update Problem
      3. 9.2.3 The Problem of Inconsistent Analysis
    4. 9.3 Serializability
    5. 9.4 Locking
      1. 9.4.1 Deadlock
      2. 9.4.2 Two-Phase Locking Protocol
      3. 9.4.3 Levels of Locking
    6. 9.5 Timestamping
      1. 9.5.1 Basic Timestamping Protocol
      2. 9.5.2 Thomas’s Write Rule
      3. 9.5.3 Multiversion Timestamping
    7. 9.6 Optimistic Techniques
    8. 9.7 Need for Recovery
    9. 9.8 Recovery Techniques
      1. 9.8.1 Deferred Update Protocol
      2. 9.8.2 Checkpoints
      3. 9.8.3 Immediate Update Protocol
      4. 9.8.4 Shadow Paging
      5. 9.8.5 Overview of the ARIES Recovery Algorithm
    10. 9.9 Transaction Management in Oracle
    11. 9.10 Chapter Summary
    12. Exercises
  17. 10. Distributed Databases
    1. Chapter Objectives
    2. 10.1 Rationale for Distribution
    3. 10.2 Architectures for a Distributed System
      1. 10.2.1 Distributed Processing Using a Centralized Database
      2. 10.2.2 Client-Server Systems
      3. 10.2.3 Parallel Databases
      4. 10.2.4 Distributed Databases
    4. 10.3 Components of a Distributed Database System
    5. 10.4 Data Placement
    6. 10.5 Transparency
    7. 10.6 Transaction Control for Distributed Databases
      1. 10.6.1 Concurrency Control
      2. 10.6.2 Recovery
    8. 10.7 Distributed Query Processing
      1. 10.7.1 Steps in Distributed Query Processing
      2. 10.7.2 The Semijoin Operation
    9. 10.8 Chapter Summary
    10. Exercises
  18. 11. Data Warehouses and Data Mining
    1. Chapter Objectives
    2. 11.1 Origins of Data Warehousing and Data Mining
    3. 11.2 Operational Databases and Data Warehouses
    4. 11.3 Architecture of a Data Warehouse
    5. 11.4 Developing a Data Warehouse
      1. 11.4.1 Top-Down Method
      2. 11.4.2 Bottom-Up Method
      3. 11.4.3 Comparison of Methods
    6. 11.5 Data Models for Data Warehouses
      1. 11.5.1 Rolap Schemas
      2. 11.5.2 Optimization and Index Techniques for ROLAP
      3. 11.5.3 Molap Data Cubes and Hypercubes
    7. 11.6 Data Warehouse Queries and SQL
      1. 11.6.1 Aggregate Functions
      2. 11.6.2 Rank Functions
      3. 11.6.3 Cube and Rollup Functions
      4. 11.6.4 Analytic Functions
    8. 11.7 Views and View Materialization
    9. 11.8 Data Warehouse Vendors
    10. 11.9 Data Mining
    11. 11.10 Purpose of Data Mining
    12. 11.11 Types of Knowledge Discovered
    13. 11.12 Models and Methods Used
      1. 11.12.1 Data Mining Process Model
      2. 11.12.2 Regression
      3. 11.12.3 Decision Trees
      4. 11.12.4 Neural Networks
      5. 11.12.5 Clustering
      6. 11.12.6 Genetic Algorithms
    14. 11.13 Applications of Data Mining
      1. 11.13.1 Retailing
      2. 11.13.2 Banking and Finance
      3. 11.13.3 Manufacturing
      4. 11.13.4 Science and Medicine
      5. 11.13.5 Homeland Security
      6. 11.13.6 Search Engines
    15. 11.14 Data Mining Software Providers
    16. 11.15 Chapter Summary
    17. Exercises
  19. 12. Big Data and NoSQL
    1. Chapter Objectives
    2. 12.1 Introduction
    3. 12.2 Defining Big Data
    4. 12.3 Hadoop
      1. 12.3.1 Hadoop Distributed File System
      2. 12.3.2 MapReduce
      3. 12.3.3 Querying Data in Hadoop
      4. 12.3.4 Hive and HiveQL
      5. 12.3.5 Hadoop Software Products
    5. 12.4 NoSQL
      1. 12.4.1 Defining NoSQL Technology
      2. 12.4.2 The HBase Column-Oriented Datastore
      3. 12.4.3 NoSQL Software Products
    6. 12.5 NewSQL
    7. 12.6 Chapter Summary
    8. Exercises
  20. 13. Relational Query Optimization
    1. Chapter Objectives
    2. 13.1 Query Processing and Query Optimization
    3. 13.2 Algebraic Techniques for Query Transformation
      1. 13.2.1 The Query Tree
      2. 13.2.2 An SQL Query and Its Relational Algebra Translation
      3. 13.2.3 Performing Select Operations Early
      4. 13.2.4 Evaluating Conjunctive Conditions
      5. 13.2.5 Performing Project Early
      6. 13.2.6 Equivalence of Algebraic Operations
      7. 13.2.7 Heuristics for Query Optimization
    4. 13.3 Processing Techniques and Cost Estimation
      1. 13.3.1 Cost Factors
      2. 13.3.2 Cost of Processing Selects
      3. 13.3.3 Processing Joins
      4. 13.3.4 Processing Other Operations
    5. 13.4 Pipelining
    6. 13.5 Query Optimization in Oracle
    7. 13.6 Chapter Summary
    8. Exercises
  21. 14. Databases and the Internet
    1. Chapter Objectives
    2. 14.1 Introduction
    3. 14.2 Fundamental Concepts of the Internet and the World Wide Web
      1. 14.2.1 Browsers, Links, URIs, and URLs
      2. 14.2.2 HTTP
      3. 14.2.3 HTML
    4. 14.3 Extensible Markup Language (XML)
      1. 14.3.1 Standalone XML Documents
      2. 14.3.2 DTDs
      3. 14.3.3 XML Schema
    5. 14.4 Tiered Architectures
      1. 14.4.1 Single-Tier Architecture
      2. 14.4.2 Two-Tier Architecture
      3. 14.4.3 Three-Tier Architecture
    6. 14.5 Web Programming with Oracle
      1. 14.5.1 PL/SQL Web Toolkit
      2. 14.5.2 PL/SQL Server Pages (PSP)
    7. 14.6 The Semi-Structured Data Model
      1. 14.6.1 Graphical Representation
      2. 14.6.2 XML Parsers
      3. 14.6.3 XML Data Manipulation
    8. 14.7 XML and Relational Databases
    9. 14.8 XML in Oracle
      1. 14.8.1 XML DB
      2. 14.8.2 Oracle XML Developer Kits
    10. 14.9 Chapter Summary
    11. Exercises
  22. 15. Social and Ethical Issues
    1. Chapter Objectives
    2. 15.1 Computerization and Society
      1. 15.1.1 Ethical Issues in Computing
      2. 15.1.2 Ethical Standards for Computing Professionals
    3. 15.2 Intellectual Property
      1. 15.2.1 Definition of Intellectual Property
      2. 15.2.2 Legal Protections for Intellectual Property
      3. 15.2.3 Intellectual Property Protection for Software
    4. 15.3 Databases and Privacy Issues
      1. 15.3.1 Privacy and Security
      2. 15.3.2 Privacy as a Human Right
    5. 15.4 Human Factors
      1. 15.4.1 Human Factors in Software Development
      2. 15.4.2 The Human-Database Interface
      3. 15.4.3 Usability Testing for Database Applications
    6. 15.5 Chapter Summary
    7. Exercises
  23. Index