Introduction
Copyright Oracle Corporation, 2001. All rights
Objectives
After completing this lesson, you should be able to do the following:
List the features of Oracle9i Discuss the theoretical and physical aspects of a
relational database
Describe the Oracle implementation of the
RDBMS and ORDBMS
I-2
Copyright Oracle Corporation, 2001. All rights
Oracle9i
Scalability
One vendor
Reliability Single development model
One management interface Common skill sets
I-3
Copyright Oracle Corporation, 2001. All rights
Oracle9i
I-4
Copyright Oracle Corporation, 2001. All rights
Oracle9i Application Server
Portals
A P A C H E
Transactional Apps
Business Intelligence intelligence
Integration
I-5
Copyright Oracle Corporation, 2001. All rights
Oracle9i Database
Object Relational Data
Documents
XML
Multimedia
Messages
I-6
Copyright Oracle Corporation, 2001. All rights
Relational and Object Relational Database Management System
Relational model and object relational model User-defined data types and objects Fully compatible with relational database Support of multimedia and large objects High-quality database server features
I-7
Copyright Oracle Corporation, 2001. All rights
Oracle Internet Platform
Clients
Any browser Any mail client Any FTP client
System management
Development tools
Internet applications
Business logic and data Presentation and business logic
SQL SQL
PL/SQL PL/SQL
Application Databases servers
Java Java
Network services
Copyright Oracle Corporation, 2001. All rights
I-8
System Development Life Cycle
Strategy and analysis
Design Build and document Transition Production
I-9
Copyright Oracle Corporation, 2001. All rights
System Development Life Cycle
Strategy and analysis
Design Build and document Transition Production
I-10
Copyright Oracle Corporation, 2001. All rights
Data Storage on Different Media
Database Electronic spreadsheet
I-11
Filing cabinet
Copyright Oracle Corporation, 2001. All rights
Relational Database Concept
Dr. E.F. Codd proposed the relational model for
database systems in 1970.
It is the basis for the relational database
management system (RDBMS).
The relational model consists of the following:
Collection of objects or relations Set of operators to act on the relations Data integrity for accuracy and consistency
I-12
Copyright Oracle Corporation, 2001. All rights
Definition of a Relational Database
A relational database is a collection of relations or two-dimensional tables.
Oracle server
Table Name: EMPLOYEES
Table Name: DEPARTMENTS
I-13
Copyright Oracle Corporation, 2001. All rights
Data Models
Model of system in clients mind
Entity model of clients model Table model of entity model
Oracle server
Tables on disk
I-14
Copyright Oracle Corporation, 2001. All rights
Entity Relationship Model
Create an entity relationship diagram from Create an entity relationship diagram from business specifications or narratives business specifications or narratives
EMPLOYEE #* number * name o job title
assigned to composed of
DEPARTMENT #* number * name o location
Scenario Scenario
. .. .. Assign one or more employees to a . Assign one or more employees to a
department .. .. . department . . .. .. Some departments do not yet have assigned . Some departments do not yet have assigned employees .. .. . employees .
I-15 Copyright Oracle Corporation, 2001. All rights
Entity Relationship Modeling Conventions
Entity Soft box Singular, unique name Uppercase Synonym in parentheses Attribute Singular name Lowercase Mandatory marked with * Optional marked with o
EMPLOYEE #* number * name o job title
assigned to composed of
DEPARTMENT #* number * name o location
Unique Identifier (UID) Primary marked with # Secondary marked with (#)
I-16 Copyright Oracle Corporation, 2001. All rights
Entity Relationship Modeling Conventions
Entity Soft box Singular, unique name Uppercase Synonym in parentheses Attribute Singular name Lowercase Mandatory marked with * Optional marked with o
EMPLOYEE #* number * name o job title
assigned to
DEPARTMENT #* number * name composed of o location
Unique Identifier (UID) Primary marked with # Secondary marked with (#)
I-17 Copyright Oracle Corporation, 2001. All rights
Relating Multiple Tables
Each row of data in a table is uniquely identified by a primary key (PK). You can logically relate data from multiple tables using foreign keys (FK).
Table Name: DEPARTMENTS Table Name: EMPLOYEES
Primary key
I-18
Foreign key
Primary key
Copyright Oracle Corporation, 2001. All rights
Relational Database Terminology
2 3 4 6 5
I-19
Copyright Oracle Corporation, 2001. All rights
Relational Database Properties
A relational database:
Can be accessed and modified by executing
pointers
structured query language (SQL) statements
Contains a collection of tables with no physical Uses a set of operators
I-20
Copyright Oracle Corporation, 2001. All rights
Communicating with a RDBMS Using SQL
SQL statement is entered.
SELECT department_name SELECT department_name FROM FROM departments; departments;
Statement is sent to Oracle Server.
Oracle server
I-21
Copyright Oracle Corporation, 2001. All rights
Relational Database Management System
Oracle server
User tables
Data dictionary
I-22
Copyright Oracle Corporation, 2001. All rights
SQL Statements
SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE
Data retrieval Data manipulation language (DML)
Data definition language (DDL)
Transaction control
Data control language (DCL)
I-23
Copyright Oracle Corporation, 2001. All rights
Tables Used in the Course
EMPLOYEES
DEPARTMENTS
I-24 Copyright Oracle Corporation, 2001. All rights
JOB_GRADES
Summary
The Oracle9i Server is the database for Internet
computing.
Oracle9i is based on the object relational database
management system.
Relational databases are composed of relations,
managed by relational operations, and governed by data integrity constraints. information by using the SQL language and PL/SQL engine.
With the Oracle Server, you can store and manage
I-25
Copyright Oracle Corporation, 2001. All rights
I-26
Copyright Oracle Corporation, 2001. All rights