Scott/Jones Scott/Jones Scott/Jones Scott/Jones Scott/Jones Scott/Jones Scott/Jones Scott/Jones

Home | Textbook Catalog | Order Book | Syllabi

Save 12% over most
bookstore prices by ordering
directly from Scott/Jones!

 

Available Now!

Implementing Databases in Oracle 9i
by John Day
Ohio University
and Craig Van Slyke
University of Central Florida

ISBN 1-57676-082-0

 


 

Table of Contents

Part 1:  Oracle Databases
This first part covers the issues related to the design and creation of databases in the Oracle Environment using SQL and PL/SQL.

Chapter 1 : Introduction to the Oracle Environment
Chapter Objectives
Introduction
What Is Oracle?
Database Design

      The Relational Data Model
            Primary keys
            Foreign keys
            Entity and referential integrity
            Types of relationships
      Normal Forms
      Entity Relationship Diagrams
            Discovering the entities and the attributes of each entity
            Determining the relationships between the entities
            Establishing the cardinality of each relationship
            Resolving many-to-many relationships
            Indicating optional and mandatory relationships
      Normalization
            First normal form
            Second normal form
            Third normal form
      Case Study Database Designs
           
Design of the Cyber College database
            Design of the Virtual Tunes database
            Design of the Xtreme Athletic Events database
The Oracle Environment
      The Oracle Server
            SQL
            PL/SQL
            Database objects
            Oracle Developer
      SQL*Plus
           
Using SQL*Plus
            Editing SQL commands
            Editing the SQL*Plus Buffer with a text editor
            Working with external files
            Exiting SQL*Plus
iSQL*Plus
Summary and Key Terms
Review Questions
Exercises

Chapter 2 : Creating and Modifying Database Tables
Chapter Objectives
Introduction
The Create Table Statement

      Constraints
      Naming Database Objects
      Constraint Clauses
           
Concatenated primary keys
            Foreign keys
            Other constraints
Creating Tables
      Data Types
      Creating the Cyber College Table
            The student table
            The instructor table
            The course table
            The section table
            Common errors when creating foreign keys
            The enrollment table
      Viewing Table Information
            Listing tables in a database
            Listing information about constraints
      Modifying Tables
            The Alter Table statement
            Removing tables from the database
       Writing a Script to Create a Database

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 3 : Adding and Modifying Data
Chapter Objectives
Introduction

Adding Data: The Insert Statement
      The Syntax for the INSERT Statement
      INSERT Examples
            Inserting null values
            Inserting DATE-Format data

      Inserting Multiple Rows
      Integrity Constraints and the INSERT statement
            Violating a primary key constraint
            Violating a referential integrity constraint

      Common Errors
            Invalid column name errors
            Value size errors
            Invalid data type errors
            Mismatched column-value errors

Modifying Existing Data: The UPDATE Statement
      UPDATE Examples
            Using a WHERE condition with UPDATE
            Using multiple conditions with UPDATE
            Updating values of multiple columns

      Integrity Constraints and UPDATE
      Common UPDATE Errors
            Datatype mismatch errors
            Invalid column name errors
            Value size errors

Removing Data: The DELETE Statement
      Integrity Constraints and DELETE
Rollback and Commit
      Using the ROLLBACK Statement
      Using the COMMIT Statement

Summary and Key Terms
Virtual Tunes Database Case
      Inserting Initial Data
      Practice Problems

Review Questions
Exercises

Chapter 4 : Simple Queries
Chapter Objectives
Introduction

Basic Retrieval Operations
        The SQL SELECT Statement
        Retrieving All Columns and Rows in a Table
        Working with Columns
        SQL Built-in Functions
            Numeric scalar functions
            String scalar functions
            Conversion scalar functions
            Aggregate functions
            Using DISTINCT with COUNT
           
The GROUP BY clause
        Selecting Rows with WHERE and HAVING
            WHERE clauses
                        Logical operators
                        The NOT operator
                        Conditions involving character strings
                        The LIKE operator
                        Using functions in WHERE clauses

            HAVING clauses
            Using WHERE and HAVING together
           
Combining WHERE with an aggregate function
        Sorting the Record Set
 
           Sorting in descending order
            Sorting on numeric columns
           
Multi-column sorts
       Using Aliases

Creating Indexes in SQL
        Planning Indexes
        The CREATE INDEX Statement
        Removing an Index
        Listing Indexes

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 5: Advanced SQL Queries and Views
Chapter Objectives
Introduction

Complex Query Conditions
        Using Logical Operators: AND and OR
 
           The AND operator
 
           The OR operator
 
           AND/OR examples
        Complex Compound Where Conditions
        Using IN, NOT And BETWEEN
 
           The IN operator
 
           The NOT operator
 
           The BETWEEN operator
 
           Compound conditions with BETWEEN and IN
Joining Tables in Multi-Table Queries
        Two-Table Joins
 
           Planning a query
 
           Combining join conditions with other conditions
        Querying More Than Two Tables
 
           Adding additional conditions
 
           Joining five tables
        Steps for Creating a Query
        Using Table Aliases
        Types of Joins
 
           Inner and equi-joins
 
           Outer joins
       
Subqueries
 
           Correlated subqueries
Views
        Creating a View
        Renaming Columns in a View
        Using Aggregation Functions with Views
        Queries Combining Views and Tables
        Common CREATE VIEW Errors
 
           Errors involving renamed columns
Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 6: PL/SQL
Introduction
Module A
Module Objectives
Creating a PL/SQL Program Unit
        The PL/SQL Block Structure
        PL/SQL Variables
        PL/SQL Datatypes
        A Sample PL/SQL Program
 
           Declaring variables
 
           Assignment statements
 
           Expressions and symbols
        Entering and Executing PL/SQL Programs
        Dealing with Syntax Errors

Adding Built-in Functions and Exception Handling
        Built-in Functions
        Adding an Exception Section

Conditional Structures
        The IF-THEN Conditional Structure
        The IF-THEN-ELSE Conditional Structure
        The IF-THEN-ELSIF Conditional Structure

Stored Procedures
        Creating Stored Procedures
        Debugging a Stored Procedure
        Another Stored Procedure

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Module B

Module Objectives
Functions
        Creating Functions
        Using a Function

Triggers
        Creating a Trigger
 
           Handling the INSERT
 
           Handling the DELETE
 
           The exception section
Cursors and Iterative Structures
        Cursors
 
           Declaring an explicit cursor
 
           Opening an explicit cursor
 
           Accessing rows from an explicit cursor
 
           Releasing the storage area used by an explicit cursor
        Iterative Structures
 
           LOOP EXIT
 
           LOOP EXIT WHEN
 
           WHILE loops
 
           FOR IN loops
        Controlling a Cursor with a Loop
 
           Using %ROWCOUNT
 
           Using  %NOTFOUND
        Cursor For Loops

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Part II: Oracle Developer Client Tools
This part covers the various tools within Oracle Developer for creating client applications to access data stored in an Oracle Database.

Chapter 7: Oracle Developer Tools
Introduction
Module A
Module Objectives
Schema Builder

        Displaying Database Objects in Schema Builder
        Creating Tables in Schema Builder
        Creating Relationships in Schema Builder
        Creating Other Constraints
        Creating Indexes

Query Builder
        Creating a Single Table Query
        Executing a Query
        The Results Window
        Saving and Printing
        More Complex Queries
        A Multi-Table Query

Procedure Builder
        Creating a Program Unit in Procedure Builder
        Executing a Program Unit in Procedure Builder
        Dealing with Errors
        Working with PL/SQL Libraries
        Dealing with Runtime Errors in the Interpreter
 
           Using breakpoints to debug a procedure
 
           Stepping through a procedure
Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Module B

Module Objectives
Graphics Builder
Creating a Pie Chart

        Starting Graphics Builder
        Creating the Chart Query
        Chart Properties
 
           Chart templates
 
           The Data tab
 
           The Categories tab
 
           The Values tab
        Generating the Chart
        Formatting the Chart
        Saving the Chart

Creating a Column Chart

        The Chart Query
        The Chart Properties
        Formatting the Chart Axes
        Frame Properties for the Column Chart
        Additional Formatting

Creating a Line Chart
        Formatting the Line Chart
Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 8: Designing Interactive Forms
Chapter Objectives
Oracle Form Builder
The Object Navigator
Creating a Single Table Form

        Adding a Block
        Layout Design
        Saving and Running the Form
        Executing Queries
        Inserting, Updating, and Deleting Data

Creating a Tabular Form
        Creating the Data Block
        Designing the Layout
        Using a Tabular Form
        Altering the Form Layout
        Changing Object Properties

Creating a Master-Detail Form
        Creating the Master Block
        Creating the Detail Block
        Layout Design
        Using the Master Detail Form

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 9: Advanced Forms
Chapter Objectives
Introduction

Advanced Form Builder Features
        Creating the Section Maintenance Form
        Creating Radio Button Groups
        Creating a Trigger
 
           Compiling triggers
        Creating Alerts
        List of Values
        Creating a Button
        Creating a Sequence
        Using the Form

Creating a Multi-Canvas Form
        Control Blocks
        Creating the Student Block and Canvas
        Sign On Canvas Triggers
        Trapping Errors
        Student Canvas Triggers

Creating a Multi-Form Application
        The Enrollment Form
        Blocks for the Enrollment Form
        Triggers for the Enrollment Form
 
           The View Schedule button
 
           The Find Open Sections button
 
           The Add This Section button
 
           The Select Section to Drop button
 
           The Drop This Section button
        Trapping Errors on the Enrollment Form
        Testing the Enrollment Form

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 10: Report Builder
Introduction
Module A
Module Objectives
Types of Reports
            Tabular report
 
           Form-like report
        Grouped Reports
 
           Group Left report
 
           Group Above report
 
           Matrix report
 
           Matrix with Group report
Creating a Tabular Report
        The Report Builder
        Starting Report Builder
        The Report Wizard
           
Step 1: The Report Style screen
 
           Step 2: The Data screen 
 
           Step 3: The Fields screen
 
           Step 4: The Totals screen
 
           Step 5: The Labels screen
 
           Step 6: The Template screen
        The Report Editor
 
           Toolbars in the Report Editor
 
           Altering the report in the Report Editor
        Report Components
 
           Report frames
 
           The Layout model
  
          Frames in the Layout model
 
           Manipulating frames in the Layout model
 
           Confine and Flex mode
 
           The Data model
 
           Property palettes
        Saving and Printing a Report

Altering an Existing Report
        Using the Report Wizard in Re-Entrant Mode
        Viewing the Changes in the Live Previewer
        Altering a Layout Model
        Altering Margin Items

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Module B

Module Objectives
Creating a Group Left Report
        Creating a Group Left Report with the Report Wizard
        Components of a Group Left Report
 
           The Data model
 
           The Layout model in the Object Navigator
 
           The Layout model in the Report Editor
        Adding a Formula Column
 
           Adding the column in the Layout view
 
           Entering the column formula
 
           Adding the column to the layout
 
           Fine-tuning the report
Creating a Group Above Report
        Creating a Group Above Report in the Report Wizard
 
           The Report query
 
           The Remaining Report Wizard steps
        Modifying the Report Format
 
           Adding the first and last name to the group
 
           Removing the grand total
 
           Viewing the changes
 
           Fine-tuning the report in the Live Previewer
        Adding Formula and Summary Columns
 
           Adding the Formula column in the Data model
  
          Entering the formula
 
           Creating the Summary column
 
           Entering the GPA formula
 
           Displaying the Summary fields
 
           Formatting the GPA column
 
           Fine-tuning the report layout
Adding a Parameter Form to a Report
        Creating a User Parameter
        Creating the List of Values for the Parameter
        Creating the Parameter Form
        Modifying the Data Model

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 11: Integrating Developer Applications
Introduction
Module A

Module Objectives
Running a Report from a Form
        Determining the Parameter Name
        Adding a Button to the Form
        Saving and Running the Form

Embedding a Chart on a Form
        Creating the Course Sections Form
        Creating the Embedded Chart
        Formatting the Chart
        Running the Application

Embedding a Chart in a Report
Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises
Module B
Module Objectives
Graphics Builder
Organizing Applications with Project Builder
        The Project Builder Interface
        Object Navigator Nodes in Project Builder
 
           Projects node
 
           The Global Registry node
 
           The User Registry node
 
           The Connections node
        Creating a Project
        Module Dependencies
        Delivering a Project

Creating an Application Menu
        Creating a Menu
        Modifying the Menu
        Creating the Menu Item Code
        Creating a Form with a Menu

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Part III: Advanced Topics
This part covers web applications, database administration issues and advanced issues related to using Oracle databases.

Chapter 12: Web Applications
Introduction
Module A

Module Objectives
Using WebDB
Building and Browsing Database Objects
        Building Database Objects
 
           Creating a table in WebDB
 
           Creating a function in WebDB
 
           Creating an index in WebDB
 
           Creating a sequence in WebDB
        Browsing Database Objects
        Working with Tables

Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises
Module B
Module Objectives
Building User Interface Components
        Creating a WebDB Form
        Managing a WebDB Form
        Creating a WebDB Report
        Managing a WebDB Report
        Creating a WebDB Chart
        Managing a WebDB Chart

Advanced WebDB Features
        Creating a Shared Component
        Creating and Using Menus
        Creating and Using Dynamic Web Pages

Administering and Monitoring WebDB Components
Summary and Key Terms
Virtual Tunes Database Case
Review Questions
Exercises

Chapter 13: Database Administration
Chapter Objectives
Introduction

Oracle TableSpaces
Starting and Shutting Down a Database
Creating and Removing Users
Granting and Revoking Access
Import/Export

Chapter Summary
Review Questions
Exercises
Glossary

Chapter 14: Advanced Database Topics
Chapter Objectives
Introduction

Parallel and Distributed Databases
        Parallel Database Servers
        Distributed Database Systems
 
           Database names and links
 
           Interacting with remote database tables
 
           Location transparency
Object-Oriented Features
        Abstraction
        Object Types
        Creating an Object Type
 
           The Object specification
 
           The Type Body
        Creating Tables Using an Object
 
           Inserting rows
 
           Retrieving rows
 
           Updating and deleting rows
        Using Object Methods
        Nested Object Types

Data Warehousing
        Overview of Data Warehousing
        Data Warehouse Characteristics
        Creating Materialized Views
 
           The CREATE MATERIALIZED VIEW statement
Summary and Key Terms
Review Questions
Exercises

Implementing Databases in Oracle 9i
800 pages
ISBN 1-57676-082-0


Home | Textbook Catalog | Order Book | Syllabi
 

Scott/Jones Inc., El Granada CA
Website maintenance & updating by Jane Scott/Jones
Updated January 16, 2004.