Cover image for Practical issues in database management : a reference for the thinking practitioner
Practical issues in database management : a reference for the thinking practitioner
Pascal, Fabian.
Personal Author:
Publication Information:
Boston, Mass. : Addison-Wesley, 2000.
Physical Description:
xxvii, 256 pages : illustrations ; 24 cm
Subject Term:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 P34855 2000 Adult Non-Fiction Central Closed Stacks

On Order



--C. J. Date Three decades ago relational technology put the database field on a sound, scientific foundation for the first time. But the database industry--vendors, users, experts, and the trade press--has essentially flouted its principles, focusing instead on a cookbook, product-specific approach, devoid of conceptual understanding. The consequences have been costly: DBMS products, databases, development tools, and applications dont always perform up to expectation or potential, and they can encourage the wrong questions and provide the wrong answers. Practical Issues in Database Management is an attempt to remedy this intractable and costly situation. Written for database designers, programmers, managers, and users, it addresses the core, commonly recurring issues and problems that practitioners--even the most experienced database professionals--seem to systematically misunderstand, namely: *Unstructured data and complex data types *Business rules and integrity enforcement *Keys *Duplicates *Normalization and denormalization *Entity subtypes and supertypes *Data hierarchies and recursive queries *Redundancy *Quota queries *Missing information Fabian Pascal examines these crit

Author Notes

Fabian Pascal is an independent industry analyst, consultant, author, and lecturer specializing in database management. He is the author of two previous books, Understanding Relational Databases and SQL and Relational Basics , and has contributed extensively to many industry publications.




It's official--client server is dead and the future is in the Net. Says who? Why Larry Ellison, that's who. "Client Servers were a tremendous mistake. And we are sorry that we sold it to you," the Oracle CEO said to a captive London audience last week. Instead of applications running on the desktop and data sitting on the server, everything will be Internet based. The only things running on the desktop will be a browser and a word processor. What people want, he said, is simple, inexpensive hardware that functions as a window on to the Net. The PC was ludicrously complex with stacks of manuals, helplines and IT support needed to make it function. Client server was supposed to alleviate this problem, but it was a step in the wrong direction. "We are paying through the nose to be ignorant," commented Ellison. --THE REGISTER (THEREGISTER.CO.UK) The computer industry--and its database sector in particular--resembles the fashion industry: It is driven by fads. And more often than not, vendors profit from the accelerated obsolescence on which fads are predicated. It's the users, however, not the vendors, who pay through the nose. The vendors, helped by the trade media, can profitably exploit ignorance and obscure serious product deficiencies and the questionable practices they induce by simply luring users to the next fad--the Internet being just the latest one. The Internet is as much a panacea for database management as the PC, SQL, client/server, object orientation, "universal" and multidimensional DBMS, data warehousing, and mining were before it. Java virtual machines, application servers, and browser-based development tools are in the application, not database, domain, and problems caused by an unsound database foundation cannot and should not be resolved at the application level. Moreover, ad hoc DBMS support for Web pages, Microsoft Word documents, spreadsheets, and the like-- also referred to as "complex data types" and "unstructured data"--adds serious complications and problems of its own (see Appendix 1D for an Internet example). Sound database technology should be a foundation for the Internet, not the other way around. But sadly, the database field is in disarray, if not in outright regression. Many, if not most, difficulties in database management are due to the persistent failure by both DBMS vendors and database users to educate themselves and rely on a sound, scientific foundation in their respective practices. The ad hoc, cookbook approach to database management that results is due in large part to the general business culture, and particularly to the way in which practitioners are introduced to the field. A large majority are self-taught and become DBAs, application developers, database consultants, and even DBMS designers via work with some specific DBMS software. Unexposed to database concepts, principles, and methods, practitioners are unaware of the field's fundamentals, or assume they know them already. But fundamentals are not product- or vendor-specific--and intentionally so: Their generality is precisely what makes them useful. Fundamentals are deemed "theory" and, therefore, not practical. Under industry pressures, even academic programs are becoming increasingly vocational in character, focusing on product training, rather than on database educa-tion. For example: From: RA Subject: Database Course We are very interested in additional Oracle instructors . . . From: CK Subject: Database Course Does it cover accessing a database via CGI? i.e., VB, Java, Perl, C++ access to SQL Server or Access DB? Yet even a cursory inspection of database practice reveals that most problems are simply due to lack of database education. Consider, for example, the following two representative comments, the first a question posed by a novice: I need to store 40 pieces of unrelated information. Is it better to create one table with one record and 40 fields, or create one table with 40 records and one field? The second is a consultant's assessment of a database supposedly constructed by experienced professionals Finished testing a COBOL program for a software company whose main product is a well-known government contract accounting system...Now the expletive deleted replete with repeating groups, redundant fields, etc. On top of all that, because it is one of the central files to the entire system, there are literally hundreds of rules and relationships, all of which must be enforced by the dozens of subprograms that access it. I found so many violations of so many of these rules in this new subprogram that I filled five single-spaced pages with comments and suggestions. And I probably missed the more obscure problems. Several such problems, perhaps. The first comment is indicative of how database work is frequently approached these days; the second shows the severe consequences that result. It should be obvious that these are database (not application) issues--and fundamental issues at that--for the following reasons: They underlie any and all database projects, regardless of nature and purpose. No amount of expertise in any DBMS product or platform is sufficient, in itself, to address them. The consequences of not addressing them are hardly theoretical and quite severe. An analogy can serve to drive the point home. Suppose you were to select a personal physician and there were two candidates: one educated in, among other things, anatomy, biology, and chemistry, and one trained in a "cookbook" approach to identify symptoms from a list and match treatments from another. Chances are you would opt with the majority for the educated, rather than the cookbook-trained physician, and for a very good reason: In the absence of knowledge and understanding of health fundamentals, serious problems can be expected. This is generally agreed on in every applied field except, it seems, database management. The two comments above are not exceptions, but representative of a common, persistent set of problems that keep recurring in database practice. Every chapter of this book starts with some such examples (ironically, a couple are from a review of this book's manuscript). Yet it is almost impossible to make most practitioners pay attention to anything other than product-specific "how-to" recipes, essentially the cookbook approach. Indeed, judging from want ads, the sole technical qualifications for practically all database positions are programming skills and experience with specific DBMS software and development tools on specific platforms (hardware and operating systems). Nothing else. For example: Title: Senior Database Architect Qualifications: Minimum of 3 years with Oracle on Solaris. Working knowledge of Tuxedo. Use of database design tools such as ER/Win. Perl and scripting. Familiarity with Oracle 8, Oracle Parallel Server, Sun Clusters, C. At least 3 years of relevant experience. Title: Database Analyst III Experience: Five to nine years developing applications using a major industry-standard relational database system (e.g., Oracle, Sybase, Ingres). Necessary Skills: Oracle DBMS Server and Oracle Application (Web) Server on Windows NT Server; Designer 2000; Developer 2000; Oracle Reports; Oracle Graphics; and PL/SQL. Also a plus: experience with UNIX, VMS, SQR, HTML, JAMA, or JavaScript. Is there any wonder then that practitioners, seasoned ones included, have neither a good idea of, nor interest in, database fundamentals? That most cannot offer a useful definition of a database? That DBMS products and databases are riddled with flaws and unnecessary complications, many of which go undetected? If users do not demand sound DBMS products, what incentives do DBMS vendors have to provide them? Correcting this state of affairs is not a trivial proposition. Because it is easier and more profitable to go with the flow, rather than uphill against it, the vast majority of trade publications, books, and education programs focus almost exclusively on product-specific training and ignore database education, exacerbating rather than solving the problem. On the other hand, the few books that do cover fundamentals have rather tenuous links, if any, to actual database practice, reinforcing the misconception that they are "not practical." Worse, as I have amply demonstrated in other writings, much of what is being written, said, or done about database management is irrelevant, misleading, or outright wrong. To help break the vicious cycle, this book takes a different approach. It identifies a set of common, recurring database--as distinct from application--issues that users and DBMS vendors (and products) seem to be particularly unclear on, have difficulties with, or fail to address correctly--specifically: "Unstructured" data and complex data types Business rules and integrity enforcement Keys Duplicates Normalization (and "denormalization") Entity subtypes and supertypes Data hierarchies and recursive queries Redundancy Quota queries Missing information A chapter is dedicated to each of these issues, consisting of A concise statement of the issue A succinct overview of the fundamentals underlying the issue A description of the correct and general way of addressing the issue A demonstration of the practical benefits of correct treatment and the costs of deviations from, or failure to implement it An assessment of whether and how well current technology--SQL and its commercial dialects (and in some cases, proposed alter-natives, e.g., object, or "universal" DBMSs)--address the issue Recommendations and, wherever possible, workarounds Organized in this consistent format, the chapters are intended to serve as stand-alone, compact, easy-to-read statements on the current state of knowledge on each issue--"all you need to know" references, so to speak, on subjects most essential to any involvement with databases. This book has several advantages over the usual fare. First, it is practical not because it ignores or pays lip service to fundamentals like most database books do, but because it demonstrates how impractical and costly ignoring the fundamentals is. Second, many examples are from actual database projects and all chapters include, where pertinent and possible, SQL or product-specific solutions and, when available, workarounds. In addition, each chapter starts with one or more real-world comments like those above, expressing some practical aspect of the issue actually encountered in practice. (The identity of the sources is kept anonymous because the purpose is not to single them out, but to demonstrate the scope of the problem.) A good way to read the book is to ponder these comments before reading each chapter and try to identify the problems, then revisit them after reading the chapter. Third, the material is intended to be reasonably accessible (though certainly not effortless mentally) to the nontechnical reader, yet useful to the experienced database professional as well. This is because the focus is on understanding core aspects of database management, rather than on offering product-specific implementation procedures to be followed on faith. This does not mean that product-specific details are not important, but rather that they are a necessary, but insuffi-cient basis for database practice. Sources for product details are in ample supply, but they cannot substitute for understanding database fundamentals--good sources for which are badly lacking. Fourth, this book is compact. Each chapter covers its issue as thoroughly and succinctly as possible in 15 pages or less. This was no easy feat given the profusion of material on the subject that is scattered throughout disparate sources (Chapter 10 on missing information has 20 references). As I demonstrated in previous writings (for example, Understanding Relational Databases , John Wiley, 1993), database issues are tightly interdependent. Thus keys (Chapter 3) are the mechanism for preventing duplicates (Chapter 4), which are one of several types of redundancy (Chapter 8), many of which can be prevented by normal-ized designs (Chapter 5). Together with keys, data types (Chapter 1) are components of database integrity (Chapter 2), whose enforcement is simplified via normalization (Chapter 5). Therefore, any separation into discrete subjects would be somewhat arbitrary and inhibit understand-ing. By referencing sources, heavily cross-referencing chapters, and repeating certain essentials in all chapters, the book provides a fifth advantage: It allows readers to focus on the main aspects of each issue by reading only one chapter. They can follow the pointers to related chapters or go to more in-depth sources when necessary. Sixth, because the content of this book is (intentionally) generic, apart from some illustrative examples, it will not become obsolete like product- specific books do. What is more, it is useful to all practitioners, regard-less of which DBMS and what kind of databases they work with, and it enables them to assess pros and cons of their specific circumstances based on general, sound, and objective criteria. This book can be used for familiarization with and understanding of practical database concepts and principles, as an accessible desk reference, or as a text for teaching purposes (indeed, it was written in part for a database course). On completion, the reader should be able to Understand central issues in database management and their practical implications Avoid costly misconceptions and fallacies prevalent in the industry Appreciate the correct general solutions to core problems Assess whether DBMS software offers such solutions Overcome, work around, or minimize the consequences if and when products do not offer such solutions One of the essentials reiterated throughout this book is the definition of a database. Chapter 4 quotes Hugh Darwen as follows: A database is a set of axioms. The response to a query is a theo-rem. The process of deriving the theorem from the axioms is a proof. The proof is made by manipulating symbols according to agreed mathematical rules. The proof that, is the query result is as sound and consistent as the rules are (emphasis mine). A DBMS, then, is a deductive logic system: It derives new facts from a set of asserted facts. The derived facts--query results--are true if and only if The initial assertions are true The derivation rules are (logically) sound and consistent Database professionals and users desire correct answers from their databases, but a vast majority of them are largely unaware that their practices and the DBMS software they employ fail to adhere to sound and consistent rules. The purpose of this book is to sensitize them to the costly consequences of this state of affairs and to help them minimize the costs as much as is possible.Fabian Pascal San Francisco, December 1999 0201485559P04062001 Excerpted from Practical Issues in Database Management: A Reference for the Thinking Practitioner by Fabian Pascal All rights reserved by the original copyright owners. Excerpts are provided for display purposes only and may not be reproduced, reprinted or distributed without the written permission of the publisher.

Table of Contents

Forewordp. xv
Prefacep. xix
Chapter 1 Careful What You Wish For: Data Types and Complexityp. 1
1.1 The Issuep. 1
1.2 Fundamentalsp. 2
1.2.1 "Simple" Typesp. 5
1.2.2 System-Defined Typesp. 5
1.2.3 User-Defined Typesp. 6
1.2.4 Data Type Supportp. 8
1.2.5 On Type "Atomicity"p. 8
1.2.6 "Complex" Typesp. 11
1.3 Practical Implicationsp. 13
1.3.1 Relational Domains versus Object Classesp. 13
1.3.2 Database Designp. 16 Relational Structure versus Object Manipulationp. 17
1.3.3 DBMS Implementationp. 20 SQL "Domains"p. 20 "Universal" DBMSsp. 22
1.4 Conclusion and Recommendationsp. 23
Appendix 1A Possible Representations for Image Typesp. 25
Appendix 1B Graphics File Folliesp. 29
Appendix 1C Biometric Tools Ready to Take Offp. 33
Appendix 1D Search Engine Failuresp. 35
Appendix 1E "Complex" Types and Operators: An Internet Illustrationp. 39
Appendix 1F Java and Database Synergy 41
Chapter 2 The Rule of Rules: Integrityp. 45
2.1 The Issuep. 45
2.2 Fundamentalsp. 46
2.2.1 Business Rulesp. 48
2.2.2 Integrity Constraintsp. 49 Domain Constraintsp. 49 Column Constraintsp. 50 Table Constraintsp. 50 Database Constraintsp. 52
2.2.3 Database Correctnessp. 52
2.2.4 Base versus Derived Constraintsp. 54
2.2.5 Integrity Enforcementp. 56 Integrity Rulesp. 57 DBMS Supportp. 59
2.3 Practical Implicationsp. 59
2.3.1 SQL and Integrityp. 59 Domain Rulesp. 60 Column Rulesp. 61 Table and Database Rulesp. 62
2.3.2 Procedural Supportp. 63
2.4 Conclusion and Recommendationsp. 65
Appendix 2A A Note on SQL's OVERLAPS Operatorp. 69
Chapter 3 A Matter of Identity: Keysp. 73
3.1 The Issuep. 74
3.2 Fundamentalsp. 74
3.2.1 Simple versus Composite Keysp. 77
3.2.2 Natural versus Surrogate Keysp. 78
3.2.3 Candidate versus Primary Keysp. 79
3.2.4 Foreign Keysp. 80 Referential Integrity and Primary Keysp. 81
3.2.5 DBMS Supportp. 84
3.3 Practical Implicationsp. 86
3.3.1 SQL and Keysp. 86
3.4 Conclusion and Recommendationsp. 89
Chapter 4 Don't Get Duped by Dupes: Duplicate Rowsp. 93
4.1 The Issuep. 94
4.2 Fundamentalsp. 94
4.2.1 Determining Entity Typesp. 95
4.2.2 "Hidden" Informationp. 96
4.2.3 A Relational Bonusp. 97
4.3 Practical Implicationsp. 99
4.3.1 SQL and Duplicatesp. 99 Duplicate Removalp. 99 Countabilityp. 100 Addressabilityp. 100 Correctness and Interpretability of Resultsp. 101 Essential Order and Performance Optimizationp. 102
4.4 Conclusion and Recommendationsp. 104
Appendix 4A Duplicate Removal in SQLp. 109
Appendix 4B Language Redundancy and Duplicatesp. 115
Chapter 5 The Key, the Whole Key, and Nothing but the Key: Normalizationp. 123
5.1 The Issuep. 123
5.2 Fundamentalsp. 124
5.2.1 Repeating Groupsp. 126
First Normal Formp. 129
5.2.2 Column Dependenciesp. 130 Functional Dependenciesp. 132
Second Normal Formp. 132
Third Normal Formp. 134 Multivalued Dependenciesp. 137
Fourth Normal Formp. 137 Join Dependenciesp. 139
Fifth Normal Formp. 140
5.3 Practical Implicationsp. 142
5.3.1 SQL and Multivalued Columnsp. 142
5.3.2 "Denormalization" and Performancep. 143
5.4 Conclusion and Recommendationsp. 146
Chapter 6 Neither Distinct nor the Same: Entity Supertypes and Subtypesp. 149
6.1 The Issuep. 149
6.2 Fundamentalsp. 150
6.2.1 Entity Types, Attributes, and Relationshipsp. 151
6.2.2 A Special Casep. 154
6.2.3 DBMS Supportp. 156
6.3 Practical Implicationsp. 159
6.3.1 Multikey Referencesp. 159
6.3.2 SQL Subtables and Supertablesp. 160
6.4 Conclusion and Recommendationsp. 163
Chapter 7 Climbing Trees in SQL: Data Hierarchiesp. 167
7.1 The Issuep. 167
7.2 Fundamentalsp. 168
7.2.1 Nodes and Linksp. 169
7.2.2 "Explode" Queriesp. 170
7.2.3 Recurring Nodesp. 171
7.3 Practical Implicationsp. 178
7.3.1 SQL and Treesp. 178
7.4 Conclusion and Recommendationsp. 182
Chapter 8 Not Worth Repeating: Redundancyp. 185
8.1 The Issuep. 185
8.2 Fundamentalsp. 186
8.2.1 Duplicate Rowsp. 187 Within-Table Duplicatesp. 187 Cross-Table Duplicatesp. 188
8.2.2 Entity Subtypes and Supertypesp. 190
8.2.3 Column Dependenciesp. 191 Functional Dependenciesp. 192
Dependency on Part of the Keyp. 192
Indirect Dependencyp. 194
8.2.4 Multivalued Dependenciesp. 195
8.2.5 Join Dependenciesp. 196
8.2.6 Derived Informationp. 197
8.2.7 Redundancy Controlp. 200 Denormalized Designsp. 200 Derived Informationp. 201
8.3 Practical Implicationsp. 202
8.3.1 SQL and Keyless Tablesp. 202
8.3.2 SQL and Cross-Table Duplicatesp. 202
8.3.3 SQL and "Denormalization"p. 203
8.3.4 SQL and Derived Informationp. 205
8.4 Conclusion and Recommendationsp. 207
Chapter 9 Will SQL Come to Order: Quota Queriesp. 211
9.1 The Issuep. 211
9.2 Fundamentalsp. 212
9.2.1 Ambiguitiesp. 213
9.2.2 The Declarative Solutionp. 215
9.3 Practical Implicationsp. 217
9.3.1 SQL and Quota Queriesp. 217
9.4 Conclusion and Recommendationsp. 223
Chapter 10 What You Don't Know Can Hurt You: Missing Informationp. 225
10.1 The Issuep. 226
10.2 Fundamentalsp. 227
10.2.1 Meaningless Assertionsp. 229
10.2.2 Empty Assertionsp. 230
10.2.3 Missing Information as Metadatap. 232
10.2.4 DBMS Supportp. 233
10.2.5 Many-Valued Logicp. 235
10.3 Practical Implicationsp. 238
10.3.1 SQL NULLsp. 238
10.3.2 User Optionsp. 239 NULLs and 4VLp. 240 NULLs and 3VLp. 241 2VL and Metadatap. 242
10.4 Conclusion and Recommendationsp. 244
Indexp. 249