Relational Database Design and Implementation, 4th Edition

Book description

Relational Database Design and Implementation: Clearly Explained, Fourth Edition, provides the conceptual and practical information necessary to develop a database design and management scheme that ensures data accuracy and user satisfaction while optimizing performance.

Database systems underlie the large majority of business information systems. Most of those in use today are based on the relational data model, a way of representing data and data relationships using only two-dimensional tables. This book covers relational database theory as well as providing a solid introduction to SQL, the international standard for the relational database data manipulation language.

The book begins by reviewing basic concepts of databases and database design, then turns to creating, populating, and retrieving data using SQL. Topics such as the relational data model, normalization, data entities, and Codd's Rules (and why they are important) are covered clearly and concisely. In addition, the book looks at the impact of big data on relational databases and the option of using NoSQL databases for that purpose.

  • Features updated and expanded coverage of SQL and new material on big data, cloud computing, and object-relational databases
  • Presents design approaches that ensure data accuracy and consistency and help boost performance
  • Includes three case studies, each illustrating a different database design challenge
  • Reviews the basic concepts of databases and database design, then turns to creating, populating, and retrieving data using SQL

Table of contents

  1. Cover
  2. Title page
  3. Table of Contents
  4. Copyright
  5. Preface to the Fourth Edition
  6. Acknowledgments
  7. Part I: Introduction
    1. Introduction
    2. Chapter 1: The Database Environment
      1. Abstract
      2. Defining a Database
      3. Systems that Use Databases
      4. Data “Ownership”
      5. Database Software: DBMSs
      6. Database Hardware Architecture
      7. Other Factors in the Database Environment
      8. Open Source Relational DBMSs
    3. Chapter 2: Systems Analysis and Database Requirements
      1. Abstract
      2. Dealing with Resistance to Change
      3. The Structured Design Life Cycle
      4. Conducting the Needs Assessment
      5. Assessing Feasibility
      6. Generating Alternatives
      7. Evaluating and Choosing an Alternative
      8. Creating Design Requirements
      9. Alternative Analysis Methods
  8. Part II: Relational database design theory
    1. Introduction
    2. Chapter 3: Why Good Design Matters
      1. Abstract
      2. Effects of Poor Database Design
      3. Unnecessary Duplicated Data and Data Consistency
      4. Data Insertion Problems
      5. Data Deletion Problems
      6. Meaningful Identifiers
      7. The Bottom Line
    3. Chapter 4: Entities and Relationships
      1. Abstract
      2. Entities and Their Attributes
      3. Domains
      4. Basic Data Relationships
      5. Documenting Relationships
      6. Dealing with Many-to-Many Relationships
      7. Relationships and Business Rules
      8. Data Modeling Versus Data Flow
      9. Schemas
    4. Chapter 5: The Relational Data Model
      1. Abstract
      2. Understanding Relations
      3. Primary Keys
      4. Representing Data Relationships
      5. Views
      6. The Data Dictionary
      7. A Bit of History
    5. Chapter 6: Relational Algebra
      1. Abstract
      2. The Relational Algebra and SQL Example Database: Rare Books
      3. The Sample Data
      4. Making Vertical Subsets: Project
      5. Making Horizontal Subsets: Restrict
      6. Choosing Columns and Rows: Restrict and Then Project
      7. Union
      8. Join
      9. Difference
      10. Intersect
    6. Chapter 7: Normalization
      1. Abstract
      2. Translating an ER Diagram into Relations
      3. Normal Forms
      4. First Normal Form
      5. Second Normal Form
      6. Third Normal Form
      7. Boyce–Codd Normal Form
      8. Fourth Normal Form
      9. Fifth Normal Form
      10. Sixth Normal Form
    7. Chapter 8: Database Design and Performance Tuning
      1. Abstract
      2. Indexing
      3. Clustering
      4. Partitioning
    8. Chapter 9: Codd’s Rules for Relational DBMSs
      1. Abstract
      2. Rule 0: The Foundation Rule
      3. Rule 1: The Information Rule
      4. Rule 2: The Guaranteed Access Rule
      5. Rule 3: Systematic Treatment of Null Values
      6. Rule 4: Dynamic Online Catalog Based on the Relational Model
      7. Rule 5: The Comprehensive Data Sublanguage Rule
      8. Rule 6: The View Updating Rule
      9. Rule 7: High-Level Insert, Update, Delete
      10. Rule 8: Physical Data Independence
      11. Rule 9: Logical Data Independence
      12. Rule 10: Integrity Independence
      13. Rule 11: Distribution Independence
      14. Rule 12: Nonsubversion Rule
  9. Part III: Relational database design practice
    1. Introduction
    2. Chapter 10: Introduction to SQL
      1. Abstract
      2. A Bit of SQL History
      3. Conformance Levels
      4. SQL Environments
      5. Elements of a SQL Statement
    3. Chapter 11: Using SQL to Implement a Relational Design
      1. Abstract
      2. Database Structure Hierarchy
      3. Schemas
      4. Domains
      5. Tables
      6. Modifying Database Elements
      7. Deleting Database Elements
    4. Chapter 12: Using CASE Tools for Database Design
      1. Abstract
      2. CASE Capabilities
      3. ER Diagram Reports
      4. Data Flow Diagrams
      5. The Data Dictionary
      6. Code Generation
      7. Sample Input and Output Designs
      8. The Drawing Environment
    5. Chapter 13: Database Design Case Study #1: Mighty-Mite Motors
      1. Abstract
      2. Corporate Overview
      3. Designing the Database
    6. Chapter 14: Database Design Case Study #2: East Coast Aquarium
      1. Abstract
      2. Organizational Overview
      3. The Volunteers Database
      4. The Animal Tracking Database
    7. Chapter 15: Database Design Case Study #3: SmartMart
      1. Abstract
      2. The Merchandising Environment
      3. Putting Together an ERD
      4. Creating the Tables
      5. Generating the SQL
  10. Part IV: Using interactive SQL to manipulate a relational database
    1. Introduction
    2. Chapter 16: Simple SQL Retrieval
      1. Abstract
      2. Revisiting the Sample Data
      3. Choosing Columns
      4. Ordering the Result Table
      5. Choosing Rows
      6. Nulls and Retrieval: Three-Valued Logic
    3. Chapter 17: Retrieving Data from More Than One Table
      1. Abstract
      2. SQL Syntax for Inner Joins
      3. Finding Multiple Rows in One Table: Joining a Table to Itself
      4. Outer Joins
      5. Table Constructors in Queries
      6. Avoiding Joins with Uncorrelated Subqueries
    4. Chapter 18: Advanced Retrieval Operations
      1. Abstract
      2. Union
      3. Negative Queries
      4. The EXISTS Operator
      5. The EXCEPT and INTERSECT Operators
      6. Performing Arithmetic
      7. String Manipulation
      8. Date and Time Manipulation
      9. CASE Expressions
    5. Chapter 19: Working With Groups of Rows
      1. Abstract
      2. Set Functions
      3. Changing Data Types: CAST
      4. Grouping Queries
      5. Windowing and Window Functions
    6. Chapter 20: Data Modification
      1. Abstract
      2. Inserting Rows
      3. Updating Data
      4. Deleting Rows
      5. Inserting, Updating, or Deleting on a Condition: MERGE
    7. Chapter 21: Creating Additional Structural Elements
      1. Abstract
      2. Views
      3. Temporary Tables
      4. Common Table Expressions (CTEs)
      5. Creating Indexes
  11. Part V: Database implementation issues
    1. Introduction
    2. Chapter 22: Concurrency Control
      1. Abstract
      2. The Multiuser Environment
      3. Problems with Concurrent Use
      4. Solution #1: Classic Locking
      5. Solution #2: Optimistic Concurrency Control (Optimistic Locking)
      6. Solution #3: Multiversion Concurrency Control (Timestamping)
      7. Transaction Isolation Levels
      8. Web Database Concurrency Control Issues
      9. Distributed Database Issues
    3. Chapter 23: Database Security
      1. Abstract
      2. Sources of External Security Threats
      3. Sources of Internal Threats
      4. External Remedies
      5. Internal Solutions
      6. Backup and Recovery
      7. The Bottom Line: How Much Security Do You Need?
    4. Chapter 24: Data Warehousing
      1. Abstract
      2. Scope and Purpose of a Data Warehouse
      3. Obtaining and Preparing the Data
      4. Data Modeling for the Data Warehouse
      5. Data Warehouse Appliances
    5. Chapter 25: Data Quality
      1. Abstract
      2. Why Data Quality Matters
      3. Recognizing and Handling Incomplete Data
      4. Recognizing and Handling Incorrect Data
      5. Recognizing and Handling Incomprehensible Data
      6. Recognizing and Handling Inconsistent Data
      7. Employees and Data Quality
  12. Part VI: Beyond the relational data model
    1. Introduction
    2. Chapter 26: XML Support
      1. Abstract
      2. XML Basics
      3. SQL/XML
      4. The XML Data Type
    3. Chapter 27: Object-Relational Databases
      1. Abstract
      2. Getting Started: Object-Orientation without Computing
      3. Basic OO Concepts
      4. Benefits of Object-Orientation
      5. Limitations of Pure Object-Oriented DBMSs
      6. The Object-Relational Data Model
      7. SQL Support for the OR Data Model
      8. An Additional Sample Database
      9. SQL Data Types for Object-Relational Support
      10. User-Defined Data Types and Typed Tables
      11. Methods
    4. Chapter 28: Relational Databases and “Big Data”: The Alternative of a NoSQL Solution
      1. Abstract
      2. Types of NoSQL Databases
      3. Other Differences Between NoSQL Databases and Relational Databases
      4. Benefits of NoSQL Databases
      5. Problems with NoSQL Databases
      6. Open Source NoSQL Products
  13. Part VII: Appendices
    1. Appendix A: Historical Antecedents
    2. Appendix B: SQL Programming
    3. Appendix C: SQL Syntax Summary
  14. Glossary
  15. Subject Index

Product information

  • Title: Relational Database Design and Implementation, 4th Edition
  • Author(s): Jan L. Harrington
  • Release date: April 2016
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780128499023