Database Development & Management

ORACLE DATABASE COURSE DESCRIPTION

Oracle Database & Oracle Tools
Oracle Database Administration
Oracle Developer

Summary

Oracle8/8i Database course at Infotech is designed to prepare participants for a rewarding career in database applications development and management. Students will be introduced to the various facets of database development, including logical database design, physical database design, and the use of Structured Query Language (SQL), a standard language for interfacing with a relational database.

During the course of the program participants are expected to solve more than 100 exercises depicting real-life scenarios in the different subject topics. Team participation and group discussions are encouraged. Lab assistance is provided to participants to solve the problems.

Audience:

Motivated individuals who want to learn Oracle and its tool set
Application developers
Designer/Developers
Project Leaders

Prerequisites:

Working knowledge of Windows Operating Systems (95/98/NT/2000/XP)
Basic keyboard skills

How many hours?

Class Room Hours 56 hours
Mandatory Hands-on Lab Exercises 56 hours
Mandatory Project 40 + hours

Modules: This course comprises of

Overview of Client/Server Technology
Definition of Database
Concepts of Relational Database
Relational Database Management Systems (RDBMS)
Oracle RDBMS
Structured Query Language (SQL)
Oracle SQL and SQL*Plus
Introduction to Oracle Tools
Introduction to Oracle Procedural Language (PL/SQL)

The major concentration of this course is on the application of SQL and SQL*Plus in database design and manipulation.

SQL and SQL*Plus

In the SQL and SQL*Plus module, participants will learn and broaden their Oracle Server skill set by learning about simple to complex SQL and SQL*Plus concepts. Class exercises will prepare participants to develop complex applications using the full functionality of SQL. In the process, they will learn how to retrieve and combine data from multiple queries and define reports with SQL*Plus.

Topics include:

Relational database concepts such as normalization
Creating and Maintaining Tables Using Simple Data Definition Language (DDL) Statements
Manipulating Data in a Table Using Simple Data Manipulation Language (DML) Statements
More DML - Applying Simple & Compound Conditions
More DML - Arithmetic, Character & Date Functions
More DML - Queries, Sub-Queries & Correlated Sub Queries
More DML - Tree structured queries and their application
SQL*PLUS Report Features
Advanced SQL such as use of SQL to generate SQL statements
Concepts of Data Modeling
Integrity Constraints
Oracle8/8i features such as user-defined Data Types and Object Relational concepts

Upon completion of this module, participants will understand complex SQL and SQL*Plus concepts including SQL generating SQL and integrity constraints. They will be able to use their knowledge to design complex applications using the full functionality of SQL.

Oracle Database Administration

Description:

This course is designed to give the Oracle database administrator (DBA) a firm foundation in basic administrative tasks and provide the necessary knowledge and skills to set up, maintain, and troubleshoot an Oracle7, Oracle8, or Oracle8i database. The student learns to use an administration tool to startup and shutdown a database, create a database, manage file and database storage, and manage users and their privileges. In addition, the student learns to organize the database and to move data into and between databases, under different environments. Hands-on practices help to reinforce key concepts, and students have an opportunity to troubleshoot real life issues when they are given examples of questions frequently asked of Oracle Worldwide Support. This class is preparation for the Oracle Database Administrator certification exam.

Audience:

MIS Managers
System Administrators
Applications Developer
Technical Support Professionals
Database Administrators

Required Prerequisites:

Knowledge of database development
Knowledge of relational database management concepts
Knowledge of Oracle SQL

Suggested Prerequisites:

Oracle PL/SQL
Stored procedures, functions and packages - Concepts
Basic operating system knowledge

Course Objectives:

Create an operational database
Manage Oracle database files
Manage tablespaces, segments, extents and blocks
Manage users, privileges and resources
Start up and shut down an Oracle instance and database
Use National Language Support Features

Course Topics:

Oracle Architectural Components

Describing the Oracle Architecture and Its Main Components
Listing the Structures Involved in Connecting a User to an Oracle Instance
Listing the Stages in Processing: Queries, Data Manipulation Language (DML) Statements, and COMMITs

Getting Started with the Oracle Server

Identifying the Features of the Universal Installer
Setting Up Operating System and Password File Authentication
Listing the Main Components of Oracle Enterprise Manager and Their Uses

Managing an Oracle Instance

Creating the Parameter File
Starting Up an Instance and Opening the Database
Closing a Database and Shutting Down the Instance
Getting and Setting Parameter Values
Managing Sessions
Monitoring the ALERT File and Trace Files

Creating a Database

Preparing the Operating System
Preparing the Parameter File
Creating a Database

Data Dictionary Views and Standard Packages

Constructing the Data Dictionary Views
Querying the Data Dictionary
Preparing the PL/SQL Environment Using the Administrative Scripts
Administering Stored Procedures and Packages
Listing the Types of Database Event Triggers

Maintaining the Control File

Explaining the Use of the Control File
Listing the Contents of the Control File
Multiplexing the Control File
Obtaining Control File Information

Maintaining Redo Log Files

Explaining the Use of Online Redo Log Files
Obtaining Log and Archive Information
Controlling Log Switches and Checkpoints
Multiplexing and Maintaining Online Redo Log Files
Planning Online Redo Log Files
Troubleshooting Common Redo Log File Problems
Analyzing Online and Archived Redo Logs

Managing Tablespaces and Data Files

Describing the Logical Structure of the Database
Distinguishing the Different Types of Temporary Segments
Creating Tablespaces
Changing the Size of Tablespaces
Allocating Space for Temporary Segments
Changing the Status of Tablespaces
Changing the Storage Settings of Tablespaces
Relocating Tablespaces

Storage Structure and Relationships

Describing the Logical Structure of the Database
Listing the Segment Types and Their Uses
Listing the Keywords that Control Block Space Usage
Obtaining Information About Storage Structures from the Data Dictionary
Listing the Criteria for Separating Segments

Managing Rollback Segments

Creating Rollback Segments Using Appropriate Storage Settings
Maintaining Rollback Segments
Planning the Number and Size of Rollback Segments
Obtaining Rollback Segment Information from the Data Dictionary
Troubleshooting Common Rollback Segment Problems

Managing Tables

Creating Tables Using Appropriate Storage Settings
Controlling the Space Used by Tables
Analyzing Tables to Check Integrity and Migration
Retrieving Information About Tables from the Data Dictionary
Converting Between Different Formats of ROWID

Managing Indexes

Listing the Different Types of Indexes and Their Uses
Creating B*-Tree and Bitmap Indexes
Reorganizing Indexes
Dropping Indexes
Getting Index Information from the Data Dictionary

Maintaining Data Integrity

Implementing Data Integrity
Maintaining Integrity Constraints
Obtaining Constraint Information from the Data Dictionary

Loading Data

Loading Data Using Direct-Load Insert
Loading Data into Oracle Tables Using SQL*Loader
Conventional
Direct Paths

Reorganizing Data

Reorganizing Data Using Export and Import
Moving Data Using Transportable Tablespaces

Managing Password Security and Resources

Managing Passwords Using Profiles
Administering Profiles
Controlling Use of Resources Using Profiles
Obtaining Information About Profiles, Password Management, and Resources

Managing Users

Creating New Database Users
Altering and Dropping Existing Database Users
Monitoring Information About Existing Users

Managing Privileges

Identifying System and Object Privileges
Granting and Revoking Privileges
Controlling Operating System or Password File Authentication
Identifying Auditing Capabilities

Managing Roles

Creating and Modifying Roles
Controlling Availability of Roles
Removing Roles
Using Predefined Roles
Displaying Role Information from the Data Dictionary

Using National Language Support

Choosing Character Set and National Language Character Set for a Database
Specifying the Language-Dependent Behavior Using Initialization Parameters, Environment Variables, and the ALTER SESSION Command
Using Different Types of NLS Parameters
Obtaining Information About NLS Usage

Oracle Developer

In this course, participants will learn how to build and test interactive applications and will work in a graphical user interface (GUI) environment. They will learn how to customize forms, reports, and graphics. Participants will also learn how to modify data access by creating event-related triggers.

Oracle Developer: Forms

Audience:

Application Developers
Technical Support Professionals
Designer/Developers
Database Administrators

Required Prerequisites:

Knowledge of database development
Knowledge of relational database management concepts
Knowledge of Oracle SQL

Course Objectives:

Enhance functionality to interact with GUI controls
Display form documents in multiple windows and use a variety of layout styles
Communicate with users
Supplement validation
Control navigation
Create form documents, including components for database interaction and GUI controls
Modify default transaction processing
Reuse objects and code
Link one form document to another

Course Topics:

Define Form Builder Concepts

Identify the main components in a form application and how they are related
Run a form application
Retrieve, display, modify, and store data using the forms operator interface

Work in the Developer/2000 Environment

Navigate the Developer/2000 interface
Use features such as Wizards and the Object Navigator
Work with data blocks

Create a Basic Form Document

Create a form consisting of blocks
Build a new block using an existing table specification
Save and run a form module
Build a master/detail form module
Identify characteristics of each form file storage type

Customize the Appearance of the Form

Control the position and size of objects in a layout
Add lines, geometric shapes, and color
Import images onto the layout
Create and modify text items
Support text items with List of Values (LOVs) and editors

Create Additional Input Items

Create check boxes, list items and radio groups
Create non-input items
Create display items, image items, sound items, calculated fields, and buttons

Modify Forms Using Triggers

Identify the different categories of triggers
Plan the type and scope of triggers included in a form
Create and modify triggers that can access the database

Manage Windows and Canvases

Define window and canvas-view terms
Display a form document in multiple windows
Create tabbed canvases

Create Reusable Objects and Code

Write flexible code
Manage consistency across applications
Create applications with multiple forms
Start another form module
Define multiple form functionality

Oracle Developer: Reports

Audience:

Database Administrators
Application Developers
Designer/Developers
Technical Support Professionals

Required Prerequisites:

Knowledge of database development
Knowledge of relational database management concepts
Knowledge of Oracle SQL
Developer/2000: Forms

Course Objectives:

Manage and deliver Developer/2000 applications
Create, modify and attach menu documents to the application
Ensure application security with Oracle Server roles
Redefine function key behavior
Code triggers that respond to mouse movement and mouse button actions
Manipulate windows
Create and manage multiple form applications
Manage transaction processing in multiple form applications
Choose appropriate data sources for data blocks
Include charts and reports in Developer/2000 applications
Exploit Oracle Server features in applications

Course Topics:

Manage Projects with Project Builder

Use Project Builder to organize and access files
Customize the Project Builder environment
Deliver a completed project using Project Builder

Design and Build Menu Modules

Enhance the default menu
Attach a menu to a form document
Create and design new

Use Program Function Keys

Control keys through triggers and commands
Understand the effect of function keys in a graphical user interface (GUI) environment
Cause the forms application to respond to mouse events
Apply timers
Manage events in applications based on timers

Create Advanced Multiple-Form Applications

Call several forms in both modal and modeless windows
Open and close multiple transactions across an application

Define Data Block Sources

Identify data sources available for queries and DML
Determine the appropriate data sources for data blocks
Create data blocks based on stored procedures and nested SELECT statements

Work with Record Groups

Use record groups to store data for lists and List of Values (LOVs)
Use record groups to pass data to other Developer/2000 components
Incorporate charts and reports into applications

Oracle Developer: Graphics

Audience:

Designer/Developers
Database Administrators
Technical Support Professionals
Application Developers

Required Prerequisites:

Knowledge of database development
Knowledge of relational database management concepts
Knowledge of Oracle SQL
Developer/2000: Forms
Developer/2000: Reports

Suggested Prerequisites:

Developer/2000 Rel 2: Design Effective GUI Applications
Developer/2000 Rel 2: Deploy Web-Based Applications

Course Objectives:

Create and embed a Graphics chart in a report
Enhance reports with PL/SQL trigger code
Maximize report performance using the Reports Server
Build reports to run on various platforms and in different environments, including character mode
Identify Microsoft Windows-specific features
Use report parameters and customize a runtime parameter form
Enhance a report by creating additional layout objects
Identify the main components in a report document and how they are related
Identify standard report design styles and run existing reports to various output destinations
Create and modify basic tabular reports
Create other report styles such as break reports, mailing labels, letters and matrix reports
Manage report templates

Course Topics:

Introduction to Developer/2000

The common features and benefits of Developer/2000
The Developer/2000 components
Navigating around the Developer/2000 interface
The course application

Report Builder Concepts

The main Reports executables
The main components of Report Builder
The main objects in a report

Designing and Running Reports

Designing Common Report Styles
Running a Report
Viewing Large Reports in the Runtime Previewer
Splitting the Previewer Vertically
Splitting the Previewer Horizontally
Choosing the Output Destination

Creating Reports Using the Report Wizard

Invoking the Report Builder and Report wizard
Describing the different methods of building the report query
Creating a simple tabular report
Modifying the style and content of a report
Summarizing report values

Modifying a Report in the Live Previewer

Describing the report Live Previewer
Modifying the display of the report data in the Live Previewer
Adding page numbering and current date to a report

Managing Report Templates

Modifying a predefined report template
Registering a customized template in the predefined template list

Using Report Parameters and Customizing a Parameter Form

Creating and referencing parameters to control report output
Customizing a parameter form layout to allow user entry of parameter values

Reports Storage Methods

Managing the storage of report definitions
Producing documentation of a report definition
Describing report file types and portability issues

Enhancing Reports Using the Data Model

Describing the Data Model objects and their relationship
Creating columns in the Data Model

Enhancing Reports Using the Layout Model

Describing the Layout Model objects and their relationship
Creating variable-length lines in the Layout Model
Creating buttons and explicit anchors in the Layout Model

Modifying Properties

Creating a link file to display the contents of a file
Modifying layout properties common to all objects
Modifying layout properties specific to one object

Embedding a Chart in a Report

Creating and displaying a simple Graphics chart in a report
Displaying an existing Graphics chart in a report
Modifying the chart data dynamically, using parameters

Creating Matrix Reports

Creating simple and break matrix reports, including summaries
Displaying zeros in cells with no value
Describing the matrix Data Model objects
Describing the matrix Layout Model objects

Coding PL/SQL Triggers in Reports

Describing the different types of triggers
Describing sample uses of the different types of triggers
Writing and referencing common code in a PL/SQL library

Using the Report Builder Built-In Package

Describing the contents of the Report Builder built-in package
Describing sample uses of procedures and functions from the package
Outputting messages at runtime
Creating and populating temporary tables within a report
Modifying visual attributes dynamically at runtime