173. Oracle Fundamentals
Rev 2.0
Oracle is a robust and powerful data storage engine that provides
services on all levels of an application infrastructure – combining
programmer-definable objects and tools, data manipulation tools that are
capable of retrieving data from a host of data sources and storing those data
in a database, and the capability to automate tasks and run them under
developer control.
As a developer in today’s challenging and constantly changing
environment, understanding how data is stored and retrieved is essential to a
successful design and implementation.
This three-day course is designed to provide experienced programmers
and database administrators an introduction to database programming, focusing
on database design working with Oracle as your primary data store. You will be exposed to database layout and
design, actual database programming with the PL/SQL language to create database
objects, and exposure to various database objects such as SQL Stored
Procedures, Functions, Views, Triggers, and much more. The course will work
with either Oracle 9i or 10g, including the free Oracle 10g Express Edition.
The students will take on the role of a database administrator,
learning about the different database objects and how to implement them. Students will gain an understanding of how a
database is logically put together in the conceptual design phase of a
project’s lifetime.
Finally, students will learn some advanced database programming
techniques dealing with the use of transactions and database locking. Students
will obtain real-life, "hands-on" experience by creating a database
and populating it with data during the labs. (This same database is used in our
ADO.NET course, so the two courses work very well in combination.)
LEARNING OBJECTIVES
·
Gain an understanding of conceptual database
design techniques
·
Acquire a working knowledge of database objects
and how to implement them using Oracle 9i or 10g.
Course Duration: 3 days
Prerequisites: The
student should have experience as a programmer or database administrator.
Knowledge of database fundamentals is desirable, but prior experience with
Oracle is not needed.
1.
Introduction to Oracle
What is Oracle?
What is the Oracle Enterprise Manager (OEM)?
Database objects and a high overview of their function
How data are stored in an Oracle9i database
2.
Relational Database Basics and Design
Conceptual Design Phase
OLAP vs. OLTP databases
Relational Databases
Normalization
Case Study: Deriving a relational database design from a scenario
3.
Programming SQL Statements
The Oracle SQL and PL/SQL Languages
Permissions and Database Roles
DDL Statements
DCL Statements
DML Statements
Join Statements
Aggregate Functions and Group By Clause
Case Study: Retrieving
information from a database
4.
Implementing Oracle Objects
Database
Tables
Constraints
Stored Procedures
Functions
Views
Triggers
Case Study: Implementing
Oracle Objects
5.
Indexes and Storage Types
Table Storage Types: Heap;
Index-Organized; Clustered; Partitioned)
Index Storage Types: B-Tree; Functional; IOT; Bitmap; Partitioned)
Case Study - Implementing Indexes on Tables
6.
Transactions and Locks within Oracle
Transaction Blocks
Implicit Transactions
Case Study: Implementing
Transactions
Locks
Case Study - Examining Locking Issues
7.
Oracle PL/SQL and User-Defined Objects
PL/SQL Constructs: Anonymous
Blocks; Procedures; Functions; Packages
PL/SQL Objects
Creating and Using Cursors
REF Cursors
8.
Oracle and XML Integration
XML and Oracle
Retrieving XML Documents
Writing XML
Appendix A. Acme Computer Case Study
Appendix B. Learning Resources
System Requirements
Course exercises require Oracle 9i or 10g (Express Edition OK). See the appropriate course Setup Guide for
details.
A good minimal hardware profile for this course would have a Pentium
500-MHz or equivalent CPU, 256 MB of RAM, and at least 4 GB of free disk space
for operating system, tools installation, and courseware.
