Implementing Databases in Oracle 9i

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