Databases A Beginner Guide 1st Edition by Andy Oppel 0071608478 9780071608473

PART I Database Concepts

1 Database Fundamentals

Properties of a Database

The Database Management System

Layers of Data Abstraction

Physical Data Independence

Logical Data Independence

Prevalent Database Models

Flat Files

The Hierarchical Model

The Network Model

The Relational Model

The Object-Oriented Model

The Object-Relational Model

A Brief History of Databases

Why Focus on Relational?

2 Exploring Relational Database Components

Conceptual Database Design Components

Entities

Attributes

Relationships

Business Rules

Try This 2-1: Exploring the Northwind Database

Logical/Physical Database Design Components

Tables

Columns and Data Types

Constraints

Integrity Constraints

Views

3 Forms-based Database Queries

QBE: The Roots of Forms-based Queries

Getting Started in Microsoft Access

The Microsoft Access Relationships Panel

The Microsoft Access Table Design View

Creating Queries in Microsoft Access

Try This 3-1: List All Customers

Try This 3-2: Choose Columns to Display

Try This 3-3: Sorting Results

Try This 3-4: Advanced Sorting

Try This 3-5: Choosing Rows to Display

Try This 3-6: Compound Row Selection

Try This 3-7: Using Not Equal

Try This 3-8: Joining Tables

Try This 3-9: Limiting Join Results

Try This 3-10: Outer Joins

Try This 3-11: Microsoft Access SQL

Try This 3-12: Multiple Joins and Calculated Columns

Try This 3-13: Aggregate Functions

Try This 3-14: Self-Joins

4 Introduction to SQL

A Brief History of SQL

Getting Started with Oracle SQL

Try This 4-1: Unlock the HR Account and Log in as HR

Where’s the Data?

Finding Database Objects Using Catalog Views

Viewing Database Objects Using the Object Browser

Try This 4-2 Using the Application Express Object Browser

Data Query Language (DQL): The SELECT Statement

Listing All Rows and Columns

Limiting Columns to Display

Sorting Results

Choosing Rows to Display

Joining Tables

Aggregate Functions

Data Manipulation Language (DML)

Transaction Support (COMMIT and ROLLBACK)

The INSERT Statement

The UPDATE Statement

The DELETE Statement

Data Definition Language (DDL) Statements

The CREATE TABLE Statement

The ALTER TABLE Statement

The CREATE VIEW Statement

The CREATE INDEX Statement

The DROP Statement

Data Control Language (DCL) Statements

The GRANT Statement

The REVOKE Statement

PART II Database Development

5 The Database Life Cycle

The Traditional Life Cycle

Planning

Requirements Gathering

Conceptual Design

Logical Design

Physical Design

Construction

Implementation and Rollout

Ongoing Support

Nontraditional Life Cycles

Prototyping

Rapid Application Development

The Project Triangle

Try This 5-1: Project Database Management Tasks

6 Database Design Using Normalization

The Need for Normalization

Insert Anomaly

Delete Anomaly

Update Anomaly

Applying the Normalization Process

Choosing a Primary Key

First Normal Form: Eliminating Repeating Data

Second Normal Form: Eliminating Partial Dependencies

Third Normal Form: Eliminating Transitive Dependencies

Beyond Third Normal Form

Denormalization

Try This 6-1: UTLA Academic Tracking

Practice Problems

Try This 6-2: Computer Books Company

7 Data and Process Modeling

Entity Relationship Modeling

ERD Formats

Super Types and Subtypes

Guidelines for Drawing ERDs

Process Models

The Flowchart

The Function Hierarchy Diagram

The Swim Lane Diagram

The Data Flow Diagram

Relating Entities and Processes

Try This 7-1: Draw an ERD in Information Engineering (IE) Format

8 Physical Database Design

Designing Tables

Implementing Super Types and Subtypes

Naming Conventions

Integrating Business Rules and Data Integrity

NOT NULL Constraints

Primary Key Constraints

Referential (Foreign Key) Constraints

Unique Constraints

Check Constraints

Data Types, Precision, and Scale

Triggers

Designing Views

Adding Indexes for Performance

Try This 8-1: Mapping a Logical Model to a Physical Database Design

PART III Database Implementation

9 Connecting Databases to the Outside World

Deployment Models

Centralized Model

Distributed Model

Client/Server Model

Connecting Databases to the Web

Introduction to the Internet and the Web

Components of the Web “Technology Stack”

Invoking Transactions from Web Pages

Connecting Databases to Applications

Connecting Databases via ODBC

Connecting Databases via OLE DB

Connecting Databases to Java Applications

Try This 9-1: Exploring the World Wide Web

10 Database Security

Why Is Security Necessary?

Database Server Security

Physical Security

Network Security

System-Level Security

Database Client and Application Security

Login Credentials

Data Encryption

Other Client Considerations

Database Access Security

Database Security Architectures

Schema Owner Accounts

System Privileges

Object Privileges

Roles

Views

Security Monitoring and Auditing

Try This 10-1: Database Object Privileges

11 Deploying Databases

Cursor Processing

Transaction Management

What Is a Transaction?

DBMS Support for Transactions

Try This 11-1: SQL Transaction Support

Locking and Transaction Deadlock

Performance Tuning

Tuning Database Queries

Tuning DML Statements

Change Control

12 Databases for Online Analytical Processing

Data Warehouses

OLTP Systems Compared with Data Warehouse Systems

Data Warehouse Architecture

Data Marts

Data Mining

Try This 12-1: Design Star Schema Fact and Dimension Tables

13 Integrating XML Documents and Objects into Databases

Learn the Basics of XML

Learn About SQL/XML

The XML Data Type

SQL/XML Functions

SQL/XML Mapping Rule

Try This 13-1: Using SQL/XML Functions

Object-Oriented Applications

Object-Oriented Programming

Object-Oriented Languages

Object Persistence

Object-Relational Databases

PART IV Appendices

A Answers to Self Tests

Chapter 1: Database Fundamentals

Chapter 2: Exploring Relational Database Components

Chapter 3: Forms-based Database Queries

Chapter 4: Introduction to SQL

Chapter 5: The Database Life Cycle

Chapter 6: Database Design Using Normalization

Chapter 7: Data and Process Modeling

Chapter 8: Physical Database Design

Chapter 9: Connecting Databases to the Outside World

Chapter 10: Database Security

Chapter 11: Deploying Databases

Chapter 12: Databases for Online Analytical Processing

Chapter 13: Integrating XML Documents and Objects into Databases

B Solutions to the Try This Exercises

Try This 5-1 Solution: Project Database Management Tasks

Try This 6-1 Solution: UTLA Academic Tracking

Try This 6-2 Solution: Computer Books Company

Try This 7-1 Solution: Draw an ERD in Information Engineering (IE) Format

Try This 8-1 Solution: Mapping a Logical Model to a Physical Database Design

Try This 10-1 Solution: Database Object Privileges

Try This 11-1 Solution: SQL Transaction Support

Try This 12-1 Solution: Design Star Schema Fact and Dimension Tables

Try This 13-1 Solution: Using SQL/XML Functions

Index