Table Of Contentj\tfacmillan Computer Science Series
A Abdellatif, J. Le Bihan, M. Limame, Oracle-A User's Guide
Ian 0. Angell, High-resolution Computer Graphics Using C
Ian 0. Angell and Gareth Griffith, High-resolution Computer Graphics Using Pascal
C. Bamford and P. Curran, Data Structures, Files and Databases, second edition
P. Beynon-Davies, Database Systems
P. Beynon-Davies, Inf01mation Systems Development, second edition
Linda E.M. BrackenbUI)', Design ofV LSI Systems -A Practical Introduction
Alan Bradley, Peripherals for Computer Systems
P.C. Capon and P.J. Jinks, Compiler Engineering Using Pascal
B.S. Chalk, Computer Organisation and Architecture
Eric Davalo and Patrick Natm, Neural Networks
Joyce Duncan, Lesley Rackley and Alexandria Walker, SSADM in Practice -A Version 4
Text
D. England et al.,A Sun User's Guide, second edition
Jean Ettinger, Programming in C++
J.S. Florentin, Microprogrammed Systems Design
Michel Gauthier, Ada -A Profossional Course
M.G. Hartley, M. Healey and P.G. Depledge, Mini and Microcomputer Systems
M.J. King and J.P. Pardoe, Program Design Using JSP-A Practical Introduction,
second edition
Bernard Leguy, Ada -A Programmer's Introduction
M. Leonard, Database Design Theory
David Lightfoot, Fonnal Specification Using Z
A.M. Lister and RD. Eager, Fundamentals ofO perating Systems, fifth edition
Tom Manns and Michael Coleman, Software Quality Assurance, second edition
G.P. McKeown and V.J. Rayward-Smith, Mathematical Foundations for Computing
B.A. E. Meekings, T.P. Kudrycki and M.D. Soren, A book on C, third edition
R.J. Mitchell, C++ Object-oriented Programming
R.J. Mitchell, Microcomputer Systems Using the STE Bus
R.J. Mitchell, Modula-2 Applied
J.P. Pardoe and M.J. King, Object Oriented Programming Using C++
Pham Thu Quang and C. Chartier-Kastler, !v!ERISE in Practice
Ian Pratt, Artificial Intelligence
F.D. Rolland, Programming with VDM
Z.M. Sikora, Oracle Database Principles
S.Skidmore, Introducting Systems Analysis, second edition
S.Skidmore, Introducing Systems Design, second edition
A. G. Sutcliffe, Human-Computer Inteiface Design, second edition
C.J. Theaker and G.R. Brookes, Concepts ofO perating Systems
M. Thorin, Real-time Transaction Processing
D.J. Tudor and I.J. Tudor, Systems Analysis and Design-A Comparison ofS tructured
Methods
A.J. Tyrrell, Eiffel Object-Oriented Programming
Other titles
Ian 0. Angell and Dimitrios Tsoubelis, Advanced Graphics on VGA and XGA Cards
Using Borland C++
N. Frude, A Guide to SPSSIPC+, second edition
Peter Grossman, Discrete Mathematics for Computing
H. Harper and A Meadows, GNVQ Advanced Infonnation Technology
Percy Mett, Introduction to Computing
P.D. Picton, Introduction to Neural Networks
Tony Royce, COBOL-An Introduction
Tony Royce, Structured COBOL -An Introduction
Tony Royce, C Programming
Oracle Database Principles
Z.M. Sikora BA MSc
lndependentConsuhant
© Z.M. Sikora 1997
All rights reserved. No reproduction, copy or transmission of
this publication may be made without written permission.
No paragraph ofthis publication maybe reproduced, copied or
transmitted save with written permission or in accordance with
the provisions of the Copyright, Designs and Patents Act 1988,
or under the terms of any licence permitting limited copying
issued by the Copyright Licensing Agency, 90 Tottenham Court
Road, London WlP 9HE.
Any person who does any unauthorised act in relation to this
publication may be liable to criminal prosecution and civil
claims for damages.
The author has asserted his rights to be identified
as the author of this work in accordance with the
Copyright, Designs and Patents Act 1988.
First published 1997 by
MACNITLLANPRESSLTD
Houndmills, Basingstoke, Hampshire RG21 6XS
and London
Companies and representatives
throughout the world
ISBN 978-0-333-72327-2 ISBN 978-1-349-14693-2 ( eBook)
DOI 10.1007/978-1-349-14693-2
A catalogue record for this book is available
from the British Library.
This book is printed on paper suitable for recycling and
made from fully managed and sustained forest sources.
10 9 8 7 6 5 4 3 2 1
06 05 04 03 02 01 00 99 98 97
Contents
Preface ix
1 Basic Concepts 1
1.1 Introduction 1
1.2 Definition of a Database Management System 1
1.3 Levels of Abstraction in a DBMS 3
1.4 Data Independence 4
1.5 Concurrency Issues 5
1.6 Integrity 8
1.7 Security 9
1.8 Recovery 9
1.9 A Brief History of Relational DBMS 11
2 The Hierarchical Model (IMS) 14
2.1 Conceptual Level 14
2.2 View Level 15
2.3 Physical Level 16
2.4 Logical Databases 19
2.5 Secondary Indexes 21
2.6 Summary 21
3 The Network Model (CODASYL) 22
3.1 The Codasyl Schema 26
3.2 The Codasyl Subschema 28
3.3 The Codasyl Storage Schema 28
3.4 The Codasyl Data Manipulation Language 31
3.5 Summary 31
4 The Relational Model 32
4.1 Introduction 32
4.2 Relations 32
4.3 Relational Algebra 35
4.4 Relational Integrity Rules 41
4.5 Codd's Twelve Rules 42
5 Entity Relationship Modelling 46
5.1 Introduction 46
5.2 Entities, Relationships and Attributes 47
5.3 SpecifYing Relationships 47
5.4 Modelling Master End Optionality 49
5.5 Exclusive Relationships 51
v
vi Contents
5.6 Many-Many Relationships 52
5.7 Involuted Relationships 53
5.8 Entity Sub-Types 54
5.9 Ternary Relationships 55
6 Normalisation 56
6.1 Introduction 56
6.2 The Tlrree Normal Forms Outlined 56
6.3 Recognise and Split Decomposition Method 58
6.4 More Formal Definitions 63
6.5 Boyce Codd Normal Form 65
6.6 Fourth Normal Form 67
6.7 Fifth Normal Form 70
6.8 Summary 72
7 Implementing a Design 74
7.1 Introduction 74
7.2 An E-R Representation of 3NF Relations 74
7.3 Relational Representation ofE-R models 79
8 SQL*Plus 85
8.1 Creating Tables and Adding Data 86
8.2 NULLS 88
8.3 Querying Single Tables 89
8.4 Grouping Retrievals 91
8.5 Modifying and Deleting Data 93
8.6 Retrievals Using JOIN 94
8.7 SQL *Plus Editor 96
8.8 SQL *Plus Report Formatting Conunands 98
8.9 Directing Query Results to a File or Printer 102
8.10 Using Variables in SQL *Plus 102
8.11 More Complex Queries 104
8.12 SQL Functions 110
8.13 UNION, INTERSECT, MINUS 116
8.14 Commit, Rollback and Savepoint 117
8.15 DESCRIBE Conunand 117
8.16 HOST Conunand 118
8.17 ROWID 118
8.18 Summary 118
9 PUSQL 120
9.1 Introduction 120
9.2 Basic Features 120
9.3 Cursors 126
Contents vii
9.4 Exception Handling 132
9.5 Stored Procedures, Functions and Packages 137
9.6 Database Triggers 140
9.7 User Defined Records 143
9.8 PUSQL Tables 145
9.9 Dynamic PUSQL 147
9.10 Summary 148
10 Security and Integrity 149
10.1 Introduction 149
10.2 Creating an Oracle User 149
10.3 System Privileges 151
10.4 Object Privileges 151
10.5 Synonyms 152
10.6 Roles 153
10.7 Views 155
10.8 Integrity Constraints 162
11 Indexes and Storage Structure 167
11.1 Introduction 167
11.2 Indexes 167
11.3 When Oracle Uses Indexes 170
11.4 The SQL Tuning Tools 175
11.5 Cost Based Optimiser 181
11.6 Clusters 184
11.7 Hashing 186
11.8 Oracle Storage Structure 189
12 Distributed Databases 192
12.1 Introduction 192
12.2 Objectives 193
12.3 Fragmentation 194
12.4 The OSI Model and SQL *Net 195
12.5 Distributed Databases with Oracle 197
13 SQL*Fonns 200
13.1 Introduction 200
13.2 Creating a Default Form 200
13.3 Running and Querying a Form 204
13.4 Customising a Form 207
13.5 Field Attributes and Validation 213
13.6 Block Characteristics 216
13.7 SQL *Forms Storage Formats 218
13.8 Pop-up Pages 218
viii Contents
13.9 Triggers 221
13.10 Key Triggers 224
13 .11 Packaged Procedures 225
13.12 Form Level Procedures 227
13.13 Calling Other Forms 229
13.14 Navigation Triggers 230
13.15 Transaction Triggers 230
13.16 User Exits 231
14 SQL*Reportwriter 232
14.1 Introduction 232
14.2 Building a Simple Report 232
14.3 Using Parameters in SQL*Reportwriter 247
15 Future Developments 250
15.1 Introduction 250
15.2 Object Oriented Programming Concepts 250
15.3 Object Oriented Databases 259
15.4 The Object-Relational Model 260
15.5 Oracle and the Internet 265
References and Further Reading 268
Index 271
Preface
The aim of this book is to provide a single text which covers the major components of
the Oracle database management system (DBMS) and the underlying principles. The
book is a tutorial, not a reference, and is aimed at students who are following a course
in database systems using Oracle software. It is also suitable for computing pro
fessionals who wish to have an understanding of the concepts underlying Oracle.
The first four chapters provide both a foundation and a background to Oracle
technology. Chapter 1 describes the objectives that any DBMS should satisfy.
Chapters 2 and 3 cover the hierarchical and network models which were widely used
as DBMSs before the adoption of relational DBMSs in the 1980s. These two chapters
can easily be skipped if the reader so wishes. Chapter 4 describes the relational model
on which Oracle is based: the model was proposed a decade before any commercial
product became available.
The next three chapters, 5, 6 and 7 deal with relational database design. We have
not described a database design methodology. Many such methodologies exist;
depending on the context some are more relevant than others. Many methodologies
assume a project structure or a way of working which may not exist back at the
workplace. Consequently the approach has been to describe design techniques which
are common to most methodologies.
The remaining chapters are Oracle specific. Chapter 8 covers SQL *Plus which is
Oracle's superset of the standard SQL retrieval language. Chapter 9 covers PL/SQL
which is Oracle's procedural language extension to SQL. PL/SQL is the language
used in Stored Procedures and Database Triggers and these topics are also covered in
this chapter. Chapters 10, 11 and 12 cover topics on the Oracle server architecture.
There are many end user and application development tools, both Oracle and non
Oracle, that can access an Oracle database. Two Oracle application development
tools, SQL *Forms version 3 and SQL *Reportwriter are described in chapters 13 and
14 respectively. Although these are not the latest versions, they have been included as
they are widely used both in universities and in industry. Coverage of the current
version of these tools, the GUI based Designer/2000, would require a separate text.
At the time of writing Oracle version 8 is around the corner. This will be an
Object-Relational DBMS based on SQL3. To prepare the reader for this, the final
chapter includes object oriented programming concepts as well as the Object
Relational model based on SQL3.
The book is based on Oracle version 7.2; throughout the text version 7 features
have been contrasted with those of version 6.
In the first place I would like to thank Birkbeck College for providing a
stimulating introduction to the subject. The author would like to thank the reviewers
for many suggestions which have been incorporated in the final text. I would also like
to thank Malcolm Stewart at Macmillan for supporting this work.
ix
