O
RACLE


Developed By:
Jignesh Dhol
Index

Contents

Ch:1 Intro ductio n to RDBMS
Ch:2
SQL, S QL * Plus
Ch:3
Ma n a g ing Table s and Data
Ch:4
Oth e r ORACLE da ta ba s e o bje cts
Ch:5
Tra n s a ctio n an d De cis io n Co ntro l Lang uag e
Ch:6
Intro ductio n to PL/ SQL
Ch:7
Adva n ce d PL/ SQL
Ch:8
Oracle Da tabas e Structure


file:///D|/JigneshDhol/Oracle/Index.htm [6/26/02 11:58:05 AM]
0101. Contents

Ch:1 Introduction to RDBMS

To p:1 Wh a t is Da ta ba s e Mana g e m e n t Sys te m ?
To p:2
Data ba s e Mo de ls
Hie rarch ical Mo de l - Ne tw o rk Mo de l - Re la tio nal Mo de l
To p:3 Wh a t is Re la tio na l Da ta ba s e Mana g e m e n t Sys te m ?
To p:4
Diffe re nce be tw e e n DBMS / RDBMS
To p:5
E - R diag ram
To p:6
Ty pe s o f Re la tio ns hip
One to On e - One to Ma ny - Many to Many
To p:7 No rm a liz a tio n
To p:8
Co dd's Ru le s


file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch1\0101.%20Contents.htm [6/26/02 12:06:29 PM]
0201. Contents

Ch:2 SQL, SQL *Plus

To p:1 Intro ductio n to S QL
To p:2
SQL Co m m a n ds a n d Data type s
To p:3
Ex pre s s io n, Co nditio n s and Ope ra to rs
To p:4

SELECT s ta te m e nt
To p:5
Spe cial Ope rato r
To p:6
Jo in , Subqu e ry

file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch2\0201.%20Contents.htm [6/26/02 12:06:30 PM]
0301. Contents

Ch:3 Managing Tables and Data

To p:1 Cre a ting and Alte ring ta ble s ( Including Co ns traints )
To p:2
Data De fin itio n La ng uag e
To p:3
Data Manipu la tio n Co m m ands like
Ins e rt, Update , De le te a n d Alte r
To p:4 Fu n ctio ns
Ag g re g ate , Date - Tim e , Arith m e tic, Characte r,
Co nve rs io n, Mis ce lla n e o us
To p:5 SQL * Plus


file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch3\0301.%20Contents.htm [6/26/02 12:06:32 PM]
0401. Contents

Ch:4 Other ORACLE database objects

To p:1 Vie w
To p:2

Se que n ce
To p:3
Syno n y m s
To p:4
Inde x
To p:5
Data ba s e Links

file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch4\0401.%20Contents.htm [6/26/02 12:06:34 PM]
0501. Contents

Ch:5 TCL and DCL

To p:1 Wh a t is tra n s a ctio n ?
To p:2
Sta rting a nd Ending o f Tra ns actio n
To p:3
Co m m it, Ro llba ck, Sa v e Po int
To p:4
Grant, Re vo ke
To p:5
Ro le , Cre a tin g Us e rs , Ch a ng e Pa s s w o rd

file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch5\0501.%20Contents.htm [6/26/02 12:06:35 PM]
0601. Contents

Ch:6 Introduction to PL/SQL

To p:1 SQL v/ s PL/ SQL
To p:2

PL/ SQL Blo ck Structu re
To p:3
La n g u a g e co ns truct o f PL/ SQL
( Varia ble s , Bas ic Da ta type s , Co m po s ite Da ta type s ,
Co nditio ns lo o ping e tc.)
To p:4 % TYPE a nd % ROWTYPE
To p:5
Us in g Cu rs o r ( I m plicit, Ex plicit)

file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch6\0601.%20Contents.htm [6/26/02 12:06:37 PM]
file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch7\0701.%20Contents.htm

Ch:7 Advanced PL/SQL

To p:1 Cre a tin g a nd Us ing Pro ce dure
To p:2
Fu n ctio ns
To p:3
Pa ckag e
To p:4
Trig g e r
To p:5
Cre a ting Obje cts
To p:6
PL/ SQL Ta ble s
To p:7
Ne s te a d Ta ble s
To p:8
Va rra ys



file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch7\0701.%20Contents.htm [6/26/02 12:06:38 PM]
0801. Contents

Ch:8 Oracle Database Structure

To p:1 In itia liza tio n Pa ra m e te r
To p:2
Co ntro l File s , Re do Lo g s file s
To p:3
Pro ce s s e s
To p:4
Ta ble s pa ce ( Cre a te , Alte r, Dro p)
To p:5
Oracle Blo cks
To p:6
Im po rt, Ex po rt
To p:7
SQL * Lo a de r
To p:8
Ins ta nce Archite cture
1 . Da ta ba s e Pro ce s s e s
2 . Me m o ry Structu re


file:///D|/JigneshDhol/Oracle/Book%20Ora\Ch8\0801.%20Contents.htm [6/26/02 12:06:40 PM]
0102. What is DBMS

TOP:1 Wha t is Da taba s e Mana g e m e nt Sys te m ?


C. J. Dates define a database system, as a computer base record
keeping system whose overall purpose to record and maintain
information. In other word, database is a collection of related records
and a set of programs to access this data. Because it is an entire
system and enables ones to enter, store and manage data it is call
Database Management System.
Modern Database Management System comes in many different
classifications, and with many different capabilities, but in general
they try or accomplish three things.
Data consolidation refers to the combining or unifying of separate data
file into centralized structure, and storing data in a non-redundant
format. A redundant format is a structure is that stores the same data
item two or more location. For instance, as seen in the examples
above, if within a company an employee address is stored not only by
the HRD Department in the employee history file, but also the account
department in the payroll file, the employee own department in a
project file, etc. then you have non centralized structure carrying
redundant information. An integrated (non-redundant) system stores
the employee's address stores only one location.
Da ta Sh arin g
Data sharing refer to the ability of the system to aloe multiple user
concurrent access to the individual pieces of data in the database. You
can think of the database as a 'pool' of sharable information.
Da ta Pro te ctio n
file:///D|/JigneshDhol/Oracle/0102.%20What%20is%20DBMS.htm (1 of 3) [6/26/02 12:10:58 PM]
0102. What is DBMS
Data protection refers to the ability of a database management system
to maintain integrity of its data in the face of certain type of
processing adversity such as crashes, program failures, etc. if this
type of events occurs, the DBMS must have the ability to back out (or

undo) incomplete of erroneous changes to data stored in the
database.

Ho w is a Data bas e Sys te m Be n e ficia l?
• The amount of redundancy in the stored data can be reduced.
• No more inconsistencies.
• The store data can be shared.
• Standards can be set and followed.
• Data integrity is maintained.
• Security of data can be implemented.
• Data independence.

DBMS Us e rs
• The Database Designers
• The Database Administrator or DBA
• The Application Programmer
• The actual End-Users of the application
file:///D|/JigneshDhol/Oracle/0102.%20What%20is%20DBMS.htm (2 of 3) [6/26/02 12:10:58 PM]
0102. What is DBMS


file:///D|/JigneshDhol/Oracle/0102.%20What%20is%20DBMS.htm (3 of 3) [6/26/02 12:10:58 PM]
0103. Database Models

TOP:2
Da taba s e Mo de ls

Database Management Systems organize data in what is known as
data model. You and think of a data model as the infrastructure of the
data organization, in other word how the data is presented to the

user. There are three basic data models:
• The Hierarchical Model
• The Network Model
• The Relational Model

( i) Th e Hie rarch ical Mo de l
One of the earliest database management systems was based on the
Hierarchical Model. In a hierarchical data model the records have a
parent-child relationship. The application used was Production
planning for automobile manufacturing companies. The model of
database is shown in following figure. An automobile manufacture may
manufacture various model of car. Each car model was decomposed
into its assemblies (Engine, Body and Chassis). Each assembly is
further decomposed into sub-assemblies (valves, spark plugs&ldots;)
and so on. If manufacturer wanted to generate the Bill of Materials for
a particular model of an automobile the hierarchical data model would
be very suitable because the bill of materials for a product has
hierarchical structure. Each record represents a particular part and
since the records have a parent-child relation-ship each part is linked
to its sub-part. The hierarchical model of support multiple occurrences
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (1 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
of the same record type.
One of the most popular hierarchic database management system was
IBM's Information Management System (IMS) introduced in 1968. IMS
is still most widely used DBMS in IBM mainframes.
The Characteristics of DBMS are:
• Da ta is re pre s e n te d as hie ra rch ical tre e s .
The hierarchical database is characterized by parent-child
relationship between records. A record type, R1, is said to the

parent of record type, R@, if R1 is one level higher than R2 in
the hierarchic tree. The root of the hierarchy is the most
important record type and all records at different levels of the
hierarchy are dependent of the root. Each child record has
only one parent record. The parent record can have one or
more children record type.
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (2 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
• Re pre s e nts a s e t o f re la te d re co rds .
There can be one or more than one record occurrences for
given record type. When writes into database, one occurrence
of record of the record type is written. Similarly, whenever a
record is retrieved from the database, one occurrence if the
record type is retrieved.
• Hie ra rch y is e s tablis he d thro u g h po in te rs .
In the hierarchic database, the pointers link the records.
Pointers determine whether a particular record occurrence is
a parent of child record and path from parent to the child.
• Sim ple s tru cture
The database is simple hierarchical tree. The parent and child
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (3 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
records can be stored close to each other on the disk,
minimizing disk input and output. The hierarchic data model
is simpler than a network model.
• Hig h pe rfo rm a nce
The parent-child relationship is stored as a pointer from one
record to another; hence navigation through the database is
very fast resulting in high performance.
• Re la tio ns h ips be tw e e n re co rd type s a re pro -de fine d

The hierarchical DBMS is based on the hierarchic tree
structure in which the parent-child relationship is supported.
A record type, R!, is said to be the parent of record type, R2,
if R1 is one level higher than R2 in the hierarchical tree.
Records types at different level of the hierarchy are
dependent on the root, which is most important record type in
the hierarchy. Since the relationships are predefined,
flexibility is lost but a high performance compared to other
data models is achieved.
• Te dio us to re o rg a niz e .
It is tedious to reorganize the database because the hierarchy
has to be maintained. Each time a record type is inserted of
deleted, the pointer have to be manipulated to maintain the
parent-child relationship. The reorganization is static and
appropriate changes have to be made to the application
programs.
• Re a l life re qu ire m e n t a re m o re co m ple x
The hierarchic DBMS is based on a simple parent-child
relationship, but real life applications are more complex and
cannot be represented by a hierarchic structure. In an order-
processing database, a single order might participate in three
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (4 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
different parent-child relationships linking the order to the
customer who placed the order, the items ordered and the
sales person who took the order. This complex structure
cannot be represented in a hierarchical structure.

( ii) The Ne tw o rk Mo de l
To overcome the problem posed by the hierarchical data model, the

network model was developed. The network model modified the
hierarchical model by allowing multiple parent-child relationships. This
relation is known as set in network model was developed. The network
model together with the hierarchical data model was major a data
model for implementing numerous commercial DBMSs. The network
model structure and language construct were defined by the CODASYL
(Conference on Data Systems Language).
Th e ch ara cte ris tics o f a n e tw o rk DBMS a re :
• Data record types are represented as a network.
• A network is used when hierarchy is not established or when a
record participates is more than one relationship.
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (5 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
• Each sub-module can have one or more super-ordinate
modules. Since each multiple parent child relationship is
supported child record type could have one than more parent
record types.
• Represents a set of related records.
The sets that support multiple parent-child relationships and
the structure of the record have to be specified in advance.
• Complex structure
Since multiple parent-child relationship is supported,
database structure is very complicated. The network database
implements sets that support multiple parent-child
relationships. The sets have to be specified in advance. In the
tradeoff between flexibility and performance, a network model
is not very flexible to reorganize but has high performance
level.
• Difficult to reorganize
The network database is very difficult to reorganized because

insert and deleting a record would trace the pointers and
changing the appropriate links.
• Navigation done by the programmer
The programmer will have to write 3-GL programs specifying
the relationship and direction in which to navigate in the
database.
• 3-GL needed to program database
To access records the programmer has to navigate the
database record-by-record. Program will have to be written
specifying to which relationship to navigate and the direction.
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (6 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
• 3-GL inadequate for handling sets.
The records network model are processed one set at a time. 3-
GLs handle only one record at a time and hence are
inadequate for handling sets.
• Query facility not available
Network database management system do not have any
query facility and hence 3-GL programs will have to written
specifying the path and the relationship.

( iii) The Re la tio n al Mo de l
An IBM research scientist Dr. E. F. Codd, was unhappy with the way
the DBMSs available in those day handled large volumes of data. He
felt the need to apply the rules and decline of mathematics to help
address the problems associated with the earlier models as
Data integrity
Data redundancy
In June 1970, he presented his paper titled ' A Relational Model of
Data for Large shared Databanks'. This paper actually laid down 12

rules. Which a true RDBMS would have to satisfy.
The term 'Relation' is derived from the set theory of mathematics. The
basis characteristics of a relational model are discussed here.
First, in a relation model, data is stored in relations. 'What are
relations?' will be the next question that we will answer. Before that,
consider the following example. Given below are two different lists.
One is a list of countries and their capitals. The other lists countries
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (7 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
and the local currencies used by them.
You will notice that their two different lists shown here. However,
there is a column, which is the common to both lists. This the column,
which contains names of the country. Now if someone wants to know
the currency used in Rome, first one should find out the name of the
country. Next that country should be looked up the next list to find out
the currency.
It is possible to get this information because it is possible to establish
relation between the two lists through a common column called
"country".
In the relational model data is stored in relations, relation is a formal
term for the table. In the example above we have stored information
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (8 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
about countries as a table. A table in a database as a unique table
name that identifies its contents. Each table can be called an
intersection of row and columns. One of the most important properties
of a table is that the rows are unordered. A row cannot be identified
by its position in the table. Every table must have a column that
uniquely identifies by each row in the table. It is essential that no two
rows should contain identical information. This is prevented by the use

of primary key.
Now we will exam the relational model in detail.
Th e re la tio na l m o de l - the de tails
Let us consider any of the tables we have considered in the example
above less us take the currency table.
a new column called "codes" has been introduced as the primary key.
The table show above consist of the components listed below,
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (9 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
according to a relational model:
Do m a in
Domain is pool of values from where one or more attributes (columns)
can draw their actual values. For example, the values in the field
"country" are available from the name of all the countries in the world.
Hence, the domain name for this field is country.
tu pple
according to the relational model, every relation or table is made up of
many tuples. They are called records- a term that we are already
familiar with. They are the rows that a table is made up of. Given
below are some of the tuples that are part of the currency table.
CHN China Remnimbi (quan)
FRN France Francs
PRT Portugal Escudo
The number of tuples in a table is the cardinality of the tuple.
Attribu te s
The term "attributes" refers to characteristic. The characteristic of the
tuple is reflected by its attributes or field. This simply means that what
the column contains will be define by the attributes of that column.
The number of attributes is called the degree of that table. Look at
some of the attributes shown below.

Peso
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (10 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
Australia
New Zealand
Although are relational model prescribe these above terms they do not
appear in the daily usage. The terms "records" and "fields" are
commonly used.
Adva nta g e s o f a Re la tio na l Da taba s e Mo de l
Some of the salient advantages of a relational database model have
been listed below:
Built in inte g rity a t v ario u s le ve ls .
Allow data integrity to be incorporated at the field level to ensure data
accuracy; integrity at the table level to avoid duplication of records
and to detect records with missing primary key values;
At the relationship level to ensure that relationships between tables
are valid.
Lo g ica l an d Phys ica l da ta I nde pe nde n ce fro m da ta bas e
a pplicatio ns
Changes made in the logical design of the database or changes made
in the database software will adversely affect the implementation of
the database.
Da ta co n s is te n cy a nd a ccuracy
Due to the various levels, at which data integrity can be built in, data
is accurate and consistent.
Eas y da ta re trie v al a nd da ta s h aring
file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (11 of 12) [6/26/02 12:11:01 PM]
0103. Database Models
Data cab be easily extracted from one or more than one tables. Data
can also be easily shared users.


file:///D|/JigneshDhol/Oracle/0103.%20Database%20Models.htm (12 of 12) [6/26/02 12:11:01 PM]