introduction of oracle
TRANSCRIPT
ICopyright © Oracle Corporation, 2001. All rights reserved.
Introduction
I-2 Copyright © Oracle Corporation, 2001. All rights reserved.
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-3 Copyright © Oracle Corporation, 2001. All rights reserved.
Oracle9i
Scalability
Reliability
Single development
modelCommonskill sets
One management
interface
Onevendor
I-4 Copyright © Oracle Corporation, 2001. All rights reserved.
Oracle9i
I-5 Copyright © Oracle Corporation, 2001. All rights reserved.
Oracle9i Application Server
Business IntelligenceBusiness intelligence
Transactional AppsTransactional Apps
PortalsPortals
APACHE
IntegrationIntegration
I-6 Copyright © Oracle Corporation, 2001. All rights reserved.
Oracle9i Database
MultimediaMultimedia
Object Relational DataObject Relational Data
MessagesMessages
Documents
XML
Documents
XML
I-7 Copyright © Oracle Corporation, 2001. All rights reserved.
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-8 Copyright © Oracle Corporation, 2001. All rights reserved.
Oracle Internet Platform
ClientsSy
stem
man
agem
ent
Network services
Databases Applicationservers
Developm
ent toolsInternet applications
Presentation andbusiness logic
Business logicand data
Any browser Any FTP clientAny mailclient
Java
SQLSQL
PL/SQLPL/SQL
I-9 Copyright © Oracle Corporation, 2001. All rights reserved.
System Development Life Cycle
Strategyand
analysisDesign
Buildand
documentTransition
Production
I-11 Copyright © Oracle Corporation, 2001. All rights reserved.
Data Storage on Different Media
Electronic spreadsheet
Filing cabinet
Database
I-12 Copyright © Oracle Corporation, 2001. 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-13 Copyright © Oracle Corporation, 2001. All rights reserved.
Definition of a Relational Database
A relational database is a collection of relations or two-dimensional tables.
OracleOracleserverserver
Table Name: EMPLOYEES Table Name: DEPARTMENTS
… …
I-14 Copyright © Oracle Corporation, 2001. All rights reserved.
Data Models
Model ofsystem
in client’smind
Entity model ofclient’s model
Table modelof entity model
Tables on disk
Oracleserver
I-15 Copyright © Oracle Corporation, 2001. All rights reserved.
• Create an entity relationship diagram from business specifications or narratives
• Scenario– “. . . Assign one or more employees to a
department . . .”– “. . . Some departments do not yet have assigned
employees . . .”
Entity Relationship Model
EMPLOYEEEMPLOYEE#* #* numbernumber** namenameoo job titlejob title
DEPARTMENTDEPARTMENT#* #* numbernumber** namenameoo locationlocation
assigned to
composed of
I-16 Copyright © Oracle Corporation, 2001. All rights reserved.
Entity Relationship Modeling Conventions
EntitySoft boxSingular, unique nameUppercaseSynonym in parentheses
AttributeSingular nameLowercaseMandatory marked with “*”Optional marked with “o”
Unique Identifier (UID)Primary marked with “#”Secondary marked with “(#)”
EMPLOYEEEMPLOYEE#* #* numbernumber** namenameoo job titlejob title
DEPARTMENTDEPARTMENT#* #* numbernumber** namenameoo locationlocation
assigned to
composed of
I-18 Copyright © Oracle Corporation, 2001. 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: EMPLOYEESTable Name: DEPARTMENTS
Primary key Primary keyForeign key
…
I-19 Copyright © Oracle Corporation, 2001. All rights reserved.
Relational Database Terminology
1
23 4
5
6
I-20 Copyright © Oracle Corporation, 2001. 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-21 Copyright © Oracle Corporation, 2001. All rights reserved.
Communicating with a RDBMS Using SQL
SELECT department_name FROM departments;
SQL statementis entered.
Oracleserver
Statement is sent to Oracle Server.
I-22 Copyright © Oracle Corporation, 2001. All rights reserved.
Relational Database Management System
User tables Data dictionary
Oracleserver
I-23 Copyright © Oracle Corporation, 2001. All rights reserved.
SQL StatementsSELECT INSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATE
COMMITROLLBACKSAVEPOINT
GRANTREVOKE
Data retrieval
Data manipulation language (DML)
Data definition language (DDL)
Transaction control
Data control language (DCL)
I-24 Copyright © Oracle Corporation, 2001. All rights reserved.
Tables Used in the CourseEMPLOYEES
DEPARTMENTS JOB_GRADES
I-25 Copyright © Oracle Corporation, 2001. All rights reserved.
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.
• With the Oracle Server, you can store and manage information by using the SQL language and PL/SQL engine.