Tài liệu A0004-Advanced-SQL-Guid-MoreBook.vn docx

ORACLE

Developed By:

Jignesh Dhol

Index

Contents

Ch :1 I n tro d u c tio n to RDBMS

Ch :2 S QL, S QL * Plu s

Ch :3 Ma n a g in g Ta b le s a n d Da ta

Ch :4 Oth e r ORACLE d a ta b a s e o b je c ts

Ch :5 Tra n s a c tio n a n d De c is io n Co n tro l La n g u a g e

Ch :6 I n tro d u c tio n to PL/ S QL

Ch :7 Ad v a n c e d PL/ S QL

Ch :8 Ora c le Da ta b a s e S tru c tu re

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

0101. Contents

Ch:1 Introduction to RDBMS

To p :1 W h a t is Da ta b a s e Ma n a g e m e n t S y s te m ?

To p :2 Da ta b a s e Mo d e ls

Hie ra rc h ic a l Mo d e l - N e tw o rk Mo d e l - Re la tio n a l Mo d e l

To p :3 W h a t is Re la tio n a l Da ta b a s e Ma n a g e m e n t S y s te m ?

To p :4 Diffe re n c e b e tw e e n DBMS / RDBMS

To p :5 E - R d ia g ra m

To p :6 Ty p e s o f Re la tio n s h ip

On e to On e - On e to Ma n y - Ma n y to Ma n y

To p :7 N o rm a liz a tio n

To p :8 Co d d '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 I n tro d u c tio n to S QL

To p :2 S QL Co m m a n d s a n d Da ta ty p e s

To p :3 Ex p re s s io n , Co n d itio n s a n d Op e ra to rs

To p :4 S ELECT s ta te m e n t

To p :5 S p e c ia l Op e ra to r

To p :6 Jo in , S u b q u 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 tin g a n d Alte rin g ta b le s ( I n c lu d in g Co n s tra in ts )

To p :2 Da ta De fin itio n La n g u a g e

To p :3 Da ta Ma n ip u la tio n Co m m a n d s like

I n s e rt, Up d a te , De le te a n d Alte r

To p :4 Fu n c tio n s

Ag g re g a te , Da te - Tim e , Arith m e tic , Ch a ra c te r,

Co n v e rs io n , Mis c e lla n e o u s

To p :5 S QL * Plu s

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 S e q u e n c e

To p :3 S y n o n y m s

To p :4 I n d e x

To p :5 Da ta b a s e Lin ks

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 W h a t is tra n s a c tio n ?

To p :2 S ta rtin g a n d En d in g o f Tra n s a c tio n

To p :3 Co m m it, Ro llb a c k, S a v e Po in t

To p :4 Gra n t, Re v o ke

To p :5 Ro le , Cre a tin g Us e rs , Ch a n g 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 S QL v / s PL/ S QL

To p :2 PL/ S QL Blo c k S tru c tu re

To p :3 La n g u a g e c o n s tru c t o f PL/ S QL

( Va ria b le s , Ba s ic Da ta ty p e s , Co m p o s ite Da ta ty p e s ,

Co n d itio n s lo o p in g e tc .)

To p :4 % TYPE a n d % ROW TYPE

To p :5 Us in g Cu rs o r ( I m p lic it, Ex p lic it)

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 n d Us in g Pro c e d u re

To p :2 Fu n c tio n s

To p :3 Pa c ka g e

To p :4 Trig g e r

To p :5 Cre a tin g Ob je c ts

To p :6 PL/ S QL Ta b le s

To p :7 N e s te a d Ta b le s

To p :8 Va rra y s

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 I n itia liz a tio n Pa ra m e te r

To p :2 Co n tro l File s , Re d o Lo g s file s

To p :3 Pro c e s s e s

To p :4 Ta b le s p a c e ( Cre a te , Alte r, Dro p )

To p :5 Ora c le Blo c ks

To p :6 I m p o rt, Ex p o rt

To p :7 S QL * Lo a d e r

To p :8 I n s ta n c e Arc h ite c tu re

1 . Da ta b a s e Pro c e s s e s

2 . Me m o ry S tru c tu re

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

0102. What is DBMS

TOP:1 W h a t is Da ta b a s e Ma n a g e m e n t S y s 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 S h a rin 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 c tio 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 Da ta b a s e S y s te m Be n e fic ia 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 ta b a s e Mo d e 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 ra rc h ic a l Mo d e 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 p re s e n te d a s h ie ra rc h ic a l 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]