Suit # 3 2nd Floor Azam Tower Arbab Road Chowk Jamrud Road Peshawar www.linorasystems.
com
FAROOQ NAWAZ Senior Oracle Applications DBA, 11i, R12 Oracle 11g,10g,GRID,Apps Server,Data Guard, RAC DBA 0300-5847462
I-1
Copyright 2004, Oracle. All rights reserved.
Introduction
Copyright 2004, Oracle. All rights reserved.
Lesson Objectives
After completing this lesson, you should be able to do the following: List the features of Oracle10g Discuss the theoretical and physical aspects of a relational database Describe the Oracle implementation of the RDBMS and ORDBMS Understand the goals of the course
I-3
Copyright 2004, Oracle. All rights reserved.
Goals of the Course
After completing this course, you should be able to do the following: Identify the major structural components of Oracle Database 10g Retrieve row and column data from tables with the SELECT statement
I-4
Create reports of sorted and restricted data Employ SQL functions to generate and retrieve customized data Run data manipulation language (DML) statements to update data in Oracle Database 10g Obtain metadata by querying the dictionary views
Copyright 2004, Oracle. All rights reserved.
Oracle10g
Scalability
One vendor
Reliability
Unified management
Single development model
Common skill sets
I-5
Copyright 2004, Oracle. All rights reserved.
Oracle10g
I-6
Copyright 2004, Oracle. All rights reserved.
Oracle Database 10g
Object relational data
Documents
Multimedia
Messages
I-7
Copyright 2004, Oracle. All rights reserved.
Oracle Application Server 10g
Portals
Transactional applications
Business intelligence
Integration
Application development framework
I-8
Application server
Copyright 2004, Oracle. All rights reserved.
Oracle Enterprise Manager 10g Grid Control
Software provisioning Application service level monitoring
I-9
Copyright 2004, Oracle. All rights reserved.
Relational and Object Relational Database Management Systems
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-10
Copyright 2004, Oracle. All rights reserved.
Oracle Internet Platform
Clients
Any browser
Any Any mail client FTP client
Development tools
System management
Internet applications Business logic Presentation and and data business logic Databases Application servers
SQL PL/SQL
Java
Network services
I-11
Copyright 2004, Oracle. All rights reserved.
System Development Life Cycle
Strategy and analysis Design Build and document Transition Production
I-12
Copyright 2004, Oracle. All rights reserved.
System Development Life Cycle
Strategy and analysis Design Build and document Transition Production
I-13
Copyright 2004, Oracle. All rights reserved.
Data Storage on Different Media
Electronic spreadsheet
I-14
Database Filing cabinet
Copyright 2004, Oracle. All rights reserved.
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-15
Copyright 2004, Oracle. All rights reserved.
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-16
Copyright 2004, Oracle. All rights reserved.
Data Models
Model of system in clients mind
Entity model of clients model Table model of entity model Oracle server
Tables on disk
I-17 Copyright 2004, Oracle. All rights reserved.
Entity Relationship Model
Create an entity relationship diagram from business specifications or narratives:
EMPLOYEE #* number * name o job title assigned to DEPARTMENT #* number * name composed of o location
Scenario
. . . Assign one or more employees to a department . . . . . . Some departments do not yet have assigned employees . . .
I-18
Copyright 2004, Oracle. All rights reserved.
I-19
Copyright 2004, Oracle. All rights reserved.
Entity Relationship Modeling Conventions
Entity
Singular, unique name Uppercase Soft box 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-20 Copyright 2004, Oracle. All rights reserved.
Entity Relationship Modeling Conventions
Entity
Singular, unique name Uppercase Soft box 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-21 Copyright 2004, Oracle. All rights reserved.
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-22
Foreign key Primary key
Copyright 2004, Oracle. All rights reserved.
I-23
Copyright 2004, Oracle. All rights reserved.
Relational Database Terminology
2 3 4
5 6
I-24
Copyright 2004, Oracle. All rights reserved.
I-25
Copyright 2004, Oracle. All rights reserved.
Relational Database Properties
A relational database: Can be accessed and modified by executing structured query language (SQL) statements Contains a collection of tables with no physical pointers Uses a set of operators
I-26
Copyright 2004, Oracle. All rights reserved.
Communicating with an RDBMS Using SQL
SQL statement is entered.
SELECT department_name FROM departments;
Statement is sent to Oracle server.
Oracle server
I-27
Copyright 2004, Oracle. All rights reserved.
Oracles Relational Database Management System
Oracle server
User tables
Data dictionary
I-28
Copyright 2004, Oracle. All rights reserved.
SQL Statements
SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMENT COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data manipulation language (DML)
Data definition language (DDL)
Transaction control
Data control language (DCL)
I-29
Copyright 2004, Oracle. All rights reserved.
Tables Used in the Course
EMPLOYEES
DEPARTMENTS
I-30 Copyright 2004, Oracle. All rights reserved.
JOB_GRADES
Summary
Oracle Database 10g is the database for grid computing. The database 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. With the Oracle server, you can store and manage information by using the SQL language and PL/SQL engine.
I-31
Copyright 2004, Oracle. All rights reserved.
