Implementing Databases in Oracle 9i

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