Systems Building with Oracle: The Theory and Practice of Database Design

570 Pages·2004·4.097 MB·English

by  William Smith (auth.)

S Y S T E M S B U I L D I N G W I T H
O R A C L E
The Theory and Practice of Database Design
S Y S T E M S B U I L D I N G
W I T H O R A C L E
The Theory and Practice of Database Design
WilliamSmith
©WilliamSmith2004
Allrightsreserved.Noreproduction,copyortransmissionofthis
publicationmaybemadewithoutwrittenpermission.
Noparagraphofthispublicationmaybereproduced,copiedortransmitted
savewithwrittenpermissionorinaccordancewiththeprovisionsofthe
Copyright,DesignsandPatentsAct1988,orunderthetermsofanylicence
permittinglimitedcopyingissuedbytheCopyrightLicensingAgency,90
TottenhamCourtRoad,LondonW1T4LP.
Anypersonwhodoesanyunauthorisedactinrelationtothispublication
maybeliabletocriminalprosecutionandcivilclaimsfordamages.
Theauthorhasassertedhisrighttobeidentifiedastheauthorofthis
workinaccordancewiththeCopyright,DesignsandPatentsAct1988.
Firstpublished2004by
PALGRAVEMACMILLAN
Houndmills,Basingstoke,HampshireRG216XSand
175FifthAvenue,NewYork,N.Y.10010
Companiesandrepresentativesthroughouttheworld
PALGRAVEMACMILLANistheglobalacademicimprintofthePalgrave
MacmillandivisionofSt.Martin’sPressLLCandofPalgraveMacmillanLtd.
Macmillan®isaregisteredtrademarkintheUnitedStates,UnitedKingdom
andothercountries.PalgraveisaregisteredtrademarkintheEuropean
Unionandothercountries.
ISBN978-1-4039-0169-9
ISBN978-0-230-00094-0 (eBook)
DOI 10.1007/978-0-230-00094-0
Thisbookisprintedonpapersuitableforrecyclingand
madefromfullymanagedandsustainedforestsources.
Acataloguerecordforthisbookisavailable
fromtheBritishLibrary.
10 9 8 7 6 5 4 3 2 1
13 12 11 10 09 08 07 06 05 04
CONTENTS
I try to leave out the parts that people skip
ElmoreLeonard
PREFACE xi
PART 1 GETTING STARTED 1
1 A BRIEF HISTORY OF DATA 3
1.1 Normans and Lyons 3
1.2 Deus ex Machina 4
1.3 Files and Filing Systems 6
1.4 Database Managers 9
1.5 Hierarchical Databases 11
1.6 CODASYL Databases 14
1.7 Relational Databases 16
1.8 Objection Sustained 19
1.9 Object Databases 22
1.10 Object Relational Databases 26
1.11 Key Terms 29
1.12 Questions 29
1.13 Exercises 29
2 LIGA UTOPIA 31
2.1 The Utopian Football League 31
2.2 UML Specification 33
2.3 UFL UseCases 35
2.4 Class diagrams 38
v
vi CONTENTS
2.5 Key Terms 45
2.6 Questions 45
2.7 Exercise 45
PART 2 DATABASE SYSTEMS 47
3 DATA MODELLING 49
3.1 A Clear Picture of the Data 49
3.2 Relationships 53
3.3 Keys 56
3.4 Entity–Relationship Diagrams 62
3.5 The UFL Entity–Relationship Diagram 68
3.6 ERD Issues 69
3.7 Key Terms 71
3.8 Questions 71
3.9 Exercises 72
4 NORMALIZATION 73
4.1 Set Theory and the Entity–Relationship Model 73
4.2 First Normal Form 74
4.3 A Is for Anomaly, Axiom, and Armstrong 76
4.4 Second Normal Form 80
4.5 Third Normal Form 83
4.6 Boyce–Codd Normal Form 84
4.7 Key Terms 86
4.8 Exercises 87
5 THE ROOTS OF SQL 88
5.1 Calculus 89
5.2 Propositional Logic 89
5.3 Predicate Logic 91
5.4 Tuple Relational Calculus 91
5.5 Relational Algebra 94
5.6 Key Terms 98
6 SQL 99
6.1 Taking Stock 99
6.2 Data Definition Language 100
6.3 Create 101
6.4 Alter 106
6.5 Grant 107
6.6 Insert 109
6.7 Select 111
6.8 Formatting Output 114
6.9 Create with Select 118
CONTENTS vii
6.10 Insert with Select 119
6.11 Sequences 119
6.12 Update 121
6.13 Delete 122
6.14 Key Terms 123
6.15 Questions 124
6.16 Exercises 124
7 MORE SQL 125
7.1 Joins and Join Conditions 125
7.2 Query Optimization 133
7.3 Explain Plan 134
7.4 Key Terms 137
7.5 Questions 138
7.6 Exercises 138
8 DATA SECURITY 139
8.1 Transactions 139
8.2 Atomicity 140
8.3 Consistency 142
8.4 Isolation 144
8.5 Durability 151
8.6 Concurrency Control 151
8.7 Recovery 157
8.8 SQL92 Transactions 159
8.9 Oracle Concurrency and Recovery 163
8.10 Key Terms 165
8.11 Questions 166
8.12 Exercises 166
9 FURTHER SQL 167
9.1 Aggregate Functions 167
9.2 Having 172
9.3 SQL 3 (SQL:1999) 177
9.4 Views 180
9.5 Key Terms 187
9.6 Questions 187
9.7 Exercise 188
10 SERVER-SIDE PROGRAMMING 189
10.1 PL/SQL 191
10.2 Anonymous Blocks and Cursors 195
10.3 PL/SQL Exceptions 205
10.4 Stored Programs 210
10.5 Stored Procedures 212
10.6 PL/SQL Stored Functions 220
viii CONTENTS
10.7 PL/SQL Stored Packages 221
10.8 PL/SQL Triggers 228
10.9 Key Terms 236
10.10 Exercise 236
11 DATABASE ARCHITECTURE 237
11.1 Tables 238
11.2 Indexes 239
11.3 Metadata 242
11.4 Oracle Data 244
11.5 The Oracle Instance 245
11.6 The Oracle Server 246
11.7 Systems Configuration 249
11.8 Network Software 250
11.9 Key Terms 253
11.10 Exercises 254
PART 3 IMPLEMENTING THE SYSTEM 255
12 CLIENT–SERVER MODELS 257
12.1 Multi-Tasking, Multi-Threaded 257
12.2 REmote Procedure Call 259
12.3 Remote Method Invocation 262
12.4 Data Access Mechanisms 264
12.5 Open Database Connectivity – ODBC 266
12.6 Java Database Connectivity – JDBC 268
12.7 Summary 276
12.8 Key Terms 277
12.9 Exercises 278
13 FURTHER PL/SQL 279
13.1 Mutating Tables 279
13.2 PL/SQL Programming Structures 285
13.3 PL/SQL Collections 289
13.4 Advanced Cursors 294
13.5 Advanced PL/SQL Triggers 299
13.6 Key Terms 307
13.7 Exercise 307
14 IMPLEMENTING IN JAVA 308
14.1 The JBuilder Project 310
14.2 The Login User Form 320
14.3 The Players Form 326
14.4 THe newPlayer Dialog 329
14.5 The matchResults Form 338
CONTENTS ix
14.6 The matchResults Report 350
14.7 Exercises 357
PART 4 ADVANCED DATABASE ISSUES 359
15 UNDERLYING STRUCTURES 361
15.1 Indexes 361
15.2 Tables 364
15.3 Join Operations 369
15.4 Key Terms 373
15.5 Exercises 374
16 DECISION SUPPORT SYSTEMS 376
16.1 Online Analytical Processing 376
16.2 Data Warehouses 379
16.3 OLAP Aggregations 389
16.4 Data Mining 400
16.5 So What Exactly Is OLAP? 402
16.6 Key Terms 406
16.7 Exercises 407
17 DATABASE PERFORMANCE 408
17.1 Goals and Targets 408
17.2 Performance Tuning Methods 412
17.3 Applying the Tuning Method 417
17.4 Overview of Diagnostic Tools 418
17.5 Benchmarks and Benchmarking 420
17.6 Case Study 423
17.7 Key Terms 430
17.8 Exercises 430
18 WEB INTERFACES 431
18.1 Programmatic Interfaces 431
18.2 Two-Tier Architecture 432
18.3 Three-Tier Architecture 439
18.4 Remote Method Invocation 439
18.5 Java Servlets 447
18.6 Enterprise JavaBeans 455
18.7 JavaServer Pages 457
18.8 XML 461
18.9 Key Terms 470
18.10 Exercises 471
19 PARALLEL DATABASES 472
19.1 Reliability, Availability and Scalability 472
x CONTENTS
19.2 Parallel Databases 475
19.3 Parallel Architectures 477
19.4 Database Parallelism 482
19.5 Performance Issues 487
19.6 Key Terms 489
19.7 Exercises 490
20 DISTRIBUTED DATABASES 491
20.2 Distributed Data 497
20.3 Distributed Query Processing 505
20.4 Distributed Query Optimization 508
20.5 Distributed Transactions 509
20.6 Replication 513
20.7 Distributed Database Case Study 516
20.8 Key Terms 520
20.9 Exercises 522
21 TEXT AND DOCUMENT MANAGEMENT 523
21.1 Information Overload 523
21.2 How Long Is a String? 524
21.3 Oracle Text 529
21.4 Key Terms 535
21.5 Exercise 536
BIBLIOGRAPHY 537
APPENDIX 540
A.1 Installing Oracle 540
A.2 Using Oracle Enterprise Manager 541
A.3 Using SQL*Plus 545
A.4 Data Types, Classes and Exceptions 547
A.5 Performance Experiments 551
A.6 Entities and Relationships 553
A.7 PL/SQL Collection Methods 555
INDEX 556