Home > Store > Data

larger cover

Add To My Wish List

Register your product to gain access to bonus material or receive a coupon.

Practical Issues in Database Management: A Reference for the Thinking Practitioner

Book

  • Your Price: $31.96
  • List Price: $39.95
  • Usually ships in 24 hours.
  • Description
  • Extras
  • Sample Content
  • Updates
  • Copyright 2000
  • Dimensions: 7-3/8x9-1/4
  • Pages: 288
  • Edition: 1st
  • Book
  • ISBN-10: 0-201-48555-9
  • ISBN-13: 978-0-201-48555-4

"The aim of this book is to provide a correct and up-to-date understanding of--and appreciation for--the practical aspects of crucial, yet little-understood core database issues. It identifies and clarifies certain fundamental concepts, principles, and techniques that persistently trouble users and vendors. It assesses the treatment of those issues in SQL (both the standard and commercial implementations) and gives specific guidance and practical advice on how to deal with them (and how not to). It covers, carefully and thoroughly, several particularly tricky and misunderstood topics--complex data types, missing information, data hierarchies, quota queries, and so forth--in a succinct and concise form for the busy database practitioner."

--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 don't 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 critical topics thoroughly, comparing the severe costs of mishandling them to the practical benefits of implementing the correct solutions. With an emphasis on both principles and practice, Practical Issues in Database Management employs real-world examples to provide an assessment of current technology--SQL and the DBMS products based on it--and, whenever possible, offers concrete recommendations and workarounds. With the insight provided by Practical Issues in Database Management, you will be in a far better position to evaluate specific products, exploit their capabilities, and avoid their deficiencies.

0201485559B04062001

Web Resources

Click below for Web Resources related to this title:
Author's Web Site

Table of Contents



Foreword.


Preface.


1. Careful What You Wish For: Data Types and Complexity.

The Issue.

Fundamentals.

“Simple” Types.

System-Defined Types.

User-Defined Types.

Data Type Support.

On Type “Atomicity.”

“Complex” Types.

Practical Implications.

Relational Domains versus Object Classes.

Database Design.

Relational Structure versus Object Manipulation.

DBMS Implementation.

“Domains.”

“Universal” DBMSs.

Conclusion and Recommendations.

Appendix 1A: Possible Representations for Image Types.

Appendix 1B: Graphics File Follies.

Appendix 1C: Biometric Tools Ready to Take Off.

Appendix 1D: Search Engine Failures.

Appendix 1E: “Complex” Types and Operators: An Internet Illustration.

Appendix 1F: Java and Database Synergy.



2. The Rule of Rules: Integrity.

The Issue.

Fundamentals.

Business Rules.

Integrity Constraints.

Domain Constraints.

Column Constraints.

Table Constraints.

Database Constraints.

Database Correctness.

Base versus Derived Constraints.

Integrity Enforcement.

Integrity Rules.

DBMS Support.

Practical Implications.

SQL and Integrity.

Domain Rules.

Column Rules.

Table and Database Rules.

Procedural Support.

Conclusion and Recommendations.

Appendix 2A: A Note on SQL's OVERLAPS Operator.



3. A Matter of Identity: Keys.

The Issue.

Fundamentals.

Simple versus Composite Keys.

Natural versus Surrogate Keys.

Candidate versus Primary Keys.

Foreign Keys.

Referential Integrity and Primary Keys.

DBMS Support.

Practical Implications.

SQL and Keys.

Conclusion and Recommendations.



4. Don't Get Duped by Dupes: Duplicate Rows.

The Issue.

Fundamentals.

Determining Entity Types.

“Hidden” Information.

A Relational Bonus.

Practical Implications.

SQL and Duplicates.

Duplicate Removal.

Countability.

Addressability.

Correctness and Interpretability of Results.

Essential Order and Performance Optimization.

Conclusion and Recommendations.

Appendix 4A: Duplicate Removal in SQL.

Appendix 4B: Language Redundancy and Duplicates.



5. The Key, the Whole Key, and Nothing but the Key: Normalization.

The Issue.

Fundamentals.

Repeating Groups.

First Normal Form.

Column Dependencies.

Functional Dependencies.

Second Normal Form.

Third Normal Form.

Multivalued Dependencies.

Fourth Normal Form.

Join Dependencies.

Fifth Normal Form.

Practical Implications.

SQL and Multivalued Columns.

“Denormalization” and Performance.

Conclusion and Recommendations.



6. Neither Distinct nor the Same: Entity Supertypes and Subtypes.

The Issue.

Fundamentals.

Entity Types, Attributes, and Relationships.

A Special Case.

DBMS Support.

Practical Implications.

Multikey References.

SQL Subtables and Supertables.

Conclusion and Recommendations.



7. Climbing Trees in SQL: Data Hierarchies.

The Issue.

Fundamentals.

Nodes and Links.

“Explode” Queries.

Recurring Nodes.

Practical Implications.

SQL and Trees.

Conclusion and Recommendations.



8. Not Worth Repeating: Redundancy.

The Issue.

Fundamentals.

Duplicate Rows.

Within-Table Duplicates.

Cross-Table Duplicates.

Entity Subtypes and Supertypes.

Column Dependencies.

Functional Dependencies.

Dependency on Part of the Key.

Indirect Dependency.

Multivalued Dependencies.

Join Dependencies.

Derived Information.

Redundancy Control.

Denormalized Designs.

Derived Information.

Practical Implications.

SQL and Keyless Tables.

SQL and Cross-Table Duplicates.

SQL and “Denormalization.”

SQL and Derived Information.

Conclusion and Recommendations.



9. Will SQL Come to Order: Quota Queries.

The Issue.

Fundamentals.

Ambiguities.

The Declarative Solution.

Practical Implications.

SQL and Quota Queries.

Conclusion and Recommendations.



10. What You Don't Know Can Hurt You: Missing Information.

The Issue.

Fundamentals.

Meaningless Assertions.

Empty Assertions.

Missing Information as Metadata.

DBMS Support.

Many-Valued Logic.

Practical Implications.

SQL NULLs.

NULLs and 4VL.

NULLs and 3VL.

2VL and Metadata.

Conclusion and Recommendations.



Index. 0201485559T04062001

Preface

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 database...is 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

Errata

Click for the Errata related to this title.

Submit Errata

FREE

ONE MONTH ACCESS!

WITH PURCHASE


Get unlimited 30-day access to thousands of Books & Training Videos about technology, professional development and digital media If you continue your subscription after your 30-day trial, you can receive 30% off a monthly subscription to the Safari Library for up to 12 months.