You are previewing Databases: A Beginner's Guide.
O'Reilly logo
Databases: A Beginner's Guide

Book Description

Essential Database Skills--Made Easy!

Learn standard database design and management techniques applicable to any type of database. Featuring clear examples using both Microsoft Access and Oracle, Databases: A Beginner's Guide begins by showing you how to use Structured Query Language (SQL) to create and access database objects. Then, you'll discover how to implement logical design using normalization, transform the logical design into a physical database, and handle data and process modeling. You'll also get details on database security, online analytical processing (OLAP), connecting databases to applications, and integrating XML and object content into databases.

Designed for Easy Learning

• Key Skills & Concepts--Chapter-opening lists of specific skills covered in the chapter

• Ask the Expert--Q&A sections filled with bonus information and helpful tips

• Try This--Hands-on exercises that show you how to apply your skills

• Notes--Extra information related to the topic being covered

• Self Tests--Chapter-ending quizzes to test your knowledge

Table of Contents

  1. Cover Page
  2. Databases: A Beginner's Guide
  3. Copyright Page
  4. Contents
  5. ACKNOWLEDGMENTS
  6. INTRODUCTION
  7. PART I Database Concepts
    1. 1 Database Fundamentals
      1. Properties of a Database
        1. The Database Management System
        2. Layers of Data Abstraction
        3. Physical Data Independence
        4. Logical Data Independence
      2. Prevalent Database Models
        1. Flat Files
        2. The Hierarchical Model
        3. The Network Model
        4. The Relational Model
        5. The Object-Oriented Model
        6. The Object-Relational Model
      3. A Brief History of Databases
      4. Why Focus on Relational?
    2. 2 Exploring Relational Database Components
      1. Conceptual Database Design Components
        1. Entities
        2. Attributes
        3. Relationships
        4. Business Rules
      2. Try This 2-1: Exploring the Northwind Database
      3. Logical/Physical Database Design Components
        1. Tables
        2. Columns and Data Types
        3. Constraints
        4. Integrity Constraints
        5. Views
    3. 3 Forms-based Database Queries
      1. QBE: The Roots of Forms-based Queries
      2. Getting Started in Microsoft Access
      3. The Microsoft Access Relationships Panel
      4. The Microsoft Access Table Design View
      5. Creating Queries in Microsoft Access
      6. Try This 3-1: List All Customers
      7. Try This 3-2: Choose Columns to Display
      8. Try This 3-3: Sorting Results
      9. Try This 3-4: Advanced Sorting
      10. Try This 3-5: Choosing Rows to Display
      11. Try This 3-6: Compound Row Selection
      12. Try This 3-7: Using Not Equal
      13. Try This 3-8: Joining Tables
      14. Try This 3-9: Limiting Join Results
      15. Try This 3-10: Outer Joins
      16. Try This 3-11: Microsoft Access SQL
      17. Try This 3-12: Multiple Joins and Calculated Columns
      18. Try This 3-13: Aggregate Functions
      19. Try This 3-14: Self-Joins
    4. 4 Introduction to SQL
      1. A Brief History of SQL
      2. Getting Started with Oracle SQL
      3. Try This 4-1: Unlock the HR Account and Log in as HR
      4. Where's the Data?
        1. Finding Database Objects Using Catalog Views
        2. Viewing Database Objects Using the Object Browser
      5. Try This 4-2 Using the Application Express Object Browser
      6. Data Query Language (DQL): The SELECT Statement
        1. Listing All Rows and Columns
        2. Limiting Columns to Display
        3. Sorting Results
        4. Choosing Rows to Display
        5. Joining Tables
        6. Aggregate Functions
      7. Data Manipulation Language (DML)
        1. Transaction Support (COMMIT and ROLLBACK)
        2. The INSERT Statement
        3. The UPDATE Statement
        4. The DELETE Statement
      8. Data Definition Language (DDL) Statements
        1. The CREATE TABLE Statement
        2. The ALTER TABLE Statement
        3. The CREATE VIEW Statement
        4. The CREATE INDEX Statement
        5. The DROP Statement
      9. Data Control Language (DCL) Statements
        1. The GRANT Statement
        2. The REVOKE Statement
  8. PART II Database Development
    1. 5 The Database Life Cycle
      1. The Traditional Life Cycle
        1. Planning
        2. Requirements Gathering
        3. Conceptual Design
        4. Logical Design
        5. Physical Design
        6. Construction
        7. Implementation and Rollout
        8. Ongoing Support
      2. Nontraditional Life Cycles
        1. Prototyping
        2. Rapid Application Development
      3. The Project Triangle
      4. Try This 5-1: Project Database Management Tasks
    2. 6 Database Design Using Normalization
      1. The Need for Normalization
        1. Insert Anomaly
        2. Delete Anomaly
        3. Update Anomaly
      2. Applying the Normalization Process
        1. Choosing a Primary Key
        2. First Normal Form: Eliminating Repeating Data
        3. Second Normal Form: Eliminating Partial Dependencies
        4. Third Normal Form: Eliminating Transitive Dependencies
        5. Beyond Third Normal Form
      3. Denormalization
      4. Try This 6-1: UTLA Academic Tracking
      5. Practice Problems
      6. Try This 6-2: Computer Books Company
    3. 7 Data and Process Modeling
      1. Entity Relationship Modeling
        1. ERD Formats
        2. Super Types and Subtypes
        3. Guidelines for Drawing ERDs
      2. Process Models
        1. The Flowchart
        2. The Function Hierarchy Diagram
        3. The Swim Lane Diagram
        4. The Data Flow Diagram
      3. Relating Entities and Processes
      4. Try This 7-1: Draw an ERD in Information Engineering (IE) Format
    4. 8 Physical Database Design
      1. Designing Tables
        1. Implementing Super Types and Subtypes
        2. Naming Conventions
      2. Integrating Business Rules and Data Integrity
        1. NOT NULL Constraints
        2. Primary Key Constraints
        3. Referential (Foreign Key) Constraints
        4. Unique Constraints
        5. Check Constraints
        6. Data Types, Precision, and Scale
        7. Triggers
      3. Designing Views
      4. Adding Indexes for Performance
      5. Try This 8-1: Mapping a Logical Model to a Physical Database Design
  9. PART III Database Implementation
    1. 9 Connecting Databases to the Outside World
      1. Deployment Models
        1. Centralized Model
        2. Distributed Model
        3. Client/Server Model
      2. Connecting Databases to the Web
        1. Introduction to the Internet and the Web
        2. Components of the Web "Technology Stack"
        3. Invoking Transactions from Web Pages
      3. Connecting Databases to Applications
        1. Connecting Databases via ODBC
        2. Connecting Databases via OLE DB
        3. Connecting Databases to Java Applications
      4. Try This 9-1: Exploring the World Wide Web
    2. 10 Database Security
      1. Why Is Security Necessary?
      2. Database Server Security
        1. Physical Security
        2. Network Security
        3. System-Level Security
      3. Database Client and Application Security
        1. Login Credentials
        2. Data Encryption
        3. Other Client Considerations
      4. Database Access Security
        1. Database Security Architectures
        2. Schema Owner Accounts
        3. System Privileges
        4. Object Privileges
        5. Roles
        6. Views
      5. Security Monitoring and Auditing
      6. Try This 10-1: Database Object Privileges
    3. 11 Deploying Databases
      1. Cursor Processing
      2. Transaction Management
        1. What Is a Transaction?
        2. DBMS Support for Transactions
      3. Try This 11-1: SQL Transaction Support
        1. Locking and Transaction Deadlock
      4. Performance Tuning
        1. Tuning Database Queries
        2. Tuning DML Statements
      5. Change Control
    4. 12 Databases for Online Analytical Processing
      1. Data Warehouses
        1. OLTP Systems Compared with Data Warehouse Systems
        2. Data Warehouse Architecture
      2. Data Marts
      3. Data Mining
      4. Try This 12-1: Design Star Schema Fact and Dimension Tables
    5. 13 Integrating XML Documents and Objects into Databases
      1. Learn the Basics of XML
      2. Learn About SQL/XML
        1. The XML Data Type
        2. SQL/XML Functions
        3. SQL/XML Mapping Rule
      3. Try This 13-1: Using SQL/XML Functions
      4. Object-Oriented Applications
        1. Object-Oriented Programming
        2. Object-Oriented Languages
        3. Object Persistence
      5. Object-Relational Databases
  10. PART IV Appendices
    1. A Answers to Self Tests
      1. Chapter 1: Database Fundamentals
      2. Chapter 2: Exploring Relational Database Components
      3. Chapter 3: Forms-based Database Queries
      4. Chapter 4: Introduction to SQL
      5. Chapter 5: The Database Life Cycle
      6. Chapter 6: Database Design Using Normalization
      7. Chapter 7: Data and Process Modeling
      8. Chapter 8: Physical Database Design
      9. Chapter 9: Connecting Databases to the Outside World
      10. Chapter 10: Database Security
      11. Chapter 11: Deploying Databases
      12. Chapter 12: Databases for Online Analytical Processing
      13. Chapter 13: Integrating XML Documents and Objects into Databases
    2. B Solutions to the Try This Exercises
      1. Try This 5-1 Solution: Project Database Management Tasks
      2. Try This 6-1 Solution: UTLA Academic Tracking
      3. Try This 6-2 Solution: Computer Books Company
      4. Try This 7-1 Solution: Draw an ERD in Information Engineering (IE) Format
      5. Try This 8-1 Solution: Mapping a Logical Model to a Physical Database Design
      6. Try This 10-1 Solution: Database Object Privileges
      7. Try This 11-1 Solution: SQL Transaction Support
      8. Try This 12-1 Solution: Design Star Schema Fact and Dimension Tables
      9. Try This 13-1 Solution: Using SQL/XML Functions
  11. Index