Download - Introduccion Al Usp de Oracle Version 1995
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 1/30
Copyright Oracle Corporation, 1998. All rights reserved.
I
Introduction
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 2/30
I-2 Copyright Oracle Corporation, 1998. All rights reserved.
Objectives
After completing this lesson, you should beable to do the following:
• Discuss the theoretical and physical aspectsof a relational database
• Describe the Oracle implementation of theRDBMS and ORDBMS
• Describe how SQL and PL/SQL are used in
the Oracle product set
• Describe the use and benefits of PL/SQL
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 3/30
I-3 Copyright Oracle Corporation, 1998. All rights reserved.
System Development Life Cycle
Strategyand
AnalysisDesign
Buildand
Document
Transition
Production
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 4/30
I-5 Copyright Oracle Corporation, 1998. All rights reserved.
Data Storage on Different Media
Electronicspreadsheet
Filing cabinet
Database
SALGRADE
GRADE LOSAL HISAL--------- --------- ---------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
DEPT
DEPTNO DNAME LOC
--------- -------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 5/30
I-6 Copyright Oracle Corporation, 1998. All rights reserved.
Relational Database Concept
• Dr. E. F. Codd proposed the relational modelfor 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
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 6/30
I-7 Copyright Oracle Corporation, 1998. All rights reserved.
Relational Database Definition
A relational database is a collection ofrelations or two-dimensional tables.
Database
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Table Name: DEPT
EMPNO ENAME JOB
DEPTNO
7839 KING PRESIDENT
10
7698 BLAKE MANAGER 30
7782 CLARK MANAGER
10
7566 JONES MANAGER 20
Table Name: EMP
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 7/30
I-8 Copyright Oracle Corporation, 1998. All rights reserved.
Data Models
Model of
systemin client’s
mind
Entity model ofclient’s model
Table modelof entity model
Tables on disk
Server
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 8/30
I-9 Copyright Oracle Corporation, 1998. All rights reserved.
•
Create an entity relationship diagram frombusiness specifications or narratives
• Scenario
– “. . . Assign one or more employees to adepartment . . .”
– “. . . Some departments do not yet haveassigned employees . . .”
Entity Relationship Model
EMPLOYEE#* number* nameo job title
DEPARTMENT#* number* nameo location
assigned to
composed of
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 9/30
I-10 Copyright Oracle Corporation, 1998. All rights reserved.
Entity Relationship Modeling
ConventionsEntity Soft boxSingular, unique nameUppercaseSynonym in parentheses
Attribute Singular nameLowercaseMandatory marked with “*” Optional marked with “o”
Unique Identifier (UID) Primary marked with “#” Secondary marked with “(#)”
EMPLOYEE#* number* name
o job title
DEPARTMENT#* number* name
o location
assigned to
composed of
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 10/30
I-11 Copyright Oracle Corporation, 1998. All rights reserved.
Entity Relationship Modeling
ConventionsEntity Soft boxSingular, unique nameUppercaseSynonym in parentheses
Attribute Singular nameLowercaseMandatory marked with “*” Optional marked with “o”
Unique Identifier (UID) Primary marked with “#” Secondary marked with “(#)”
EMPLOYEE#* number* name
o job title
DEPARTMENT#* number* name
o location
assigned to
composed of
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 11/30
I-12 Copyright Oracle Corporation, 1998. All rights reserved.
Relational Database Terminology
1
2 3 4
5
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ------------ --------------------- -------- ---------------- ----------- -------------- -----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 207369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
6
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 12/30
I-13 Copyright Oracle Corporation, 1998. All rights reserved.
Relating Multiple Tables
• Each row of data in a table is uniquelyidentified by a primary key (PK).
Table Name: EMP Table Name: DEPT
Primary key Primary key
• You can logically relate data from
multiple tables using foreign keys (FK).
Foreign key
EMPNO ENAME JOB
DEPTNO
7839 KING PRESIDENT
10
7698 BLAKE MANAGER 30
7782 CLARK MANAGER
10
7566 JONES MANAGER 20
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 13/30
I-14 Copyright Oracle Corporation, 1998. All rights reserved.
Relational Database Properties
A relational database
• Can be accessed and modified byexecuting structured query language(SQL) statements
• Contains a collection of tables with nophysical pointers
• Uses a set of operators
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 14/30
I-15 Copyright Oracle Corporation, 1998. All rights reserved.
Communicating with a RDBMS
Using SQL
Database
SQL> SELECT loc
2 FROM dept;
SQL statementis entered Statement is sent to
database
LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON
Data is displayed
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 15/30
I-16 Copyright Oracle Corporation, 1998. All rights reserved.
Relational Database
Management System
User tables Datadictionary
Server
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 16/30
I-17 Copyright Oracle Corporation, 1998. All rights reserved.
Oracle8: Object Relational
Database Management System• User-defined data types and objects
• Fully relational compatible
• Support of multimedia and large objects
• High-quality database server features
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 17/30
I-18 Copyright Oracle Corporation, 1998. All rights reserved.
Defining an Object
An object• Is a person, place, or thing
• Knows things about itself and performs
actions• Has an identity
I am a clock. I knowmy time zone , and I
can display time.
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 18/30
I-19 Copyright Oracle Corporation, 1998. All rights reserved.
Using an Object Model
•
Objects model a problem to solve.• The model is stated in terms of the
interactions between objects.
• Object models closely resemble the realworld.
Customers Business
Buy
Sell
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 19/30
I-20 Copyright Oracle Corporation, 1998. All rights reserved.
Characteristics of Object systems
• Present information in object form
• Classify objects into object types
• Inherit attributes and code
• Hide data, code, and attributes
• Interact with other objects
• Recognize different objects without
analysis• Interpret the same command in
different ways
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 20/30
I-21 Copyright Oracle Corporation, 1998. All rights reserved.
Oracle Complete Solution
SQL PL/SQL
Oracle7/8
Applications
HRFinancialsManufacturing... Designer 2000
Developer 2000
Data tables
Datadictionary
Database
SQL* Plus
Discoverer
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 21/30
I-22 Copyright Oracle Corporation, 1998. All rights reserved.
SQL StatementsSELECT
INSERTUPDATEDELETE
CREATE
ALTERDROPRENAMETRUNCATE
COMMIT
ROLLBACKSAVEPOINT
GRANTREVOKE
Data retrieval
Data manipulation language (DML)
Data definition language (DDL)
Transaction control
Data control language (DCL)
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 22/30
I-23 Copyright Oracle Corporation, 1998. All rights reserved.
About PL/SQL
• PL/SQL is an extension to SQL withdesign features of programminglanguages.
• Data manipulation and querystatements of SQL are included withinprocedural units of code.
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 23/30
I-24 Copyright Oracle Corporation, 1998. All rights reserved.
PL/SQL Environment
PL/SQLblock
PL/SQL engine
Oracle Server
ProceduralStatementExecutor
PL/SQL
SQL
SQL Statement Executor
PL/SQLblock
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 24/30
I-25 Copyright Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
Integration
Application
Oracle ServerSharedlibrary
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 25/30
I-26 Copyright Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
Application Other DBMSs
Application Oracle withPL/SQL
SQL
SQL
SQL
SQL
SQLIF...THEN
SQLELSE
SQLEND IF;SQL
Improve Performance
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 26/30
I-27 Copyright Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
Modularize program development
Anonymousblock
Applicationtrigger
Storedprocedure/
function
Databasetrigger
Applicationprocedure/
function
Packagedprocedure
DECLARE
BEGIN
EXCEPTION
END;
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 27/30
I-28 Copyright Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
• It is portable.
• You can declare identifiers.
•
You can program with procedurallanguage control structures.
• It can handle errors.
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 28/30
I-29 Copyright Oracle Corporation, 1998. All rights reserved.
Benefits of PL/SQL
• It is portable.
• You can declare identifiers.
•
You can program with procedurallanguage control structures.
• It can handle errors.
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 29/30
I-30 Copyright Oracle Corporation, 1998. All rights reserved.
Tables Used in the Course
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--------- ---------- --------- --------- --------- --------- --------- ---------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 1500 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
EMP
DEPT
DEPTNO DNAME LOC
--------- -------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO40 OPERATIONS BOSTON
GRADE LOSAL HISAL
--------- --------- ---------
1 700 12002 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SALGRADE
8/4/2019 Introduccion Al Usp de Oracle Version 1995
http://slidepdf.com/reader/full/introduccion-al-usp-de-oracle-version-1995 30/30
I-31 Copyright Oracle Corporation, 1998. All rights reserved.
Summary
•
Relational databases are composed ofrelations, managed by relationaloperations, and governed by dataintegrity constraints.
• Oracle Server allows you to store andmanage information by using the SQLlanguage and PL/SQL engine.
•
PL/SQL is an extension to SQL withdesign features of programminglanguages.