Home > Store

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

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

  • Sorry, this book is no longer in print.
Not for Sale

Description

  • Copyright 2000
  • Dimensions: 7-3/8" x 9-1/4"
  • Pages: 288
  • Edition: 1st
  • Book
  • ISBN-10: 0-201-48555-9
  • ISBN-13: 978-0-201-48555-4

Databasics clearly explains the key concepts users and database professionals need to understand in order to build well-designed databases that answer business questions accurately and efficiently. Fabian Pascal, one of the industry's leading experts, identifies ten critical, recurring issues that both database users and vendors often fail to address appropriately. Pascal demonstrates why understanding these fundamentals is so important, providing detailed examples and solutions designed to help users escape the key pitfalls of database development. Among the topics covered: unstructured data and complex data types; business rules and enforcing data integrity; keys; duplicates; normalization; entity subtypes and supertypes; data hierarchies and recursive queries; redundancy; quota queries; and how to handle missing information. Along the way, Pascal offers no-holds-barred assessments of how well current SQL implementations and commercial products address each issue. Databasics, in short, is a complete guide to building databases right the first time, so they don't have to be rebuilt later. For all DBAs, developers, managers, and end-users that need to understand the best ways to design and implement database systems.

Extras

Web Resources

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

Sample Content

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

Updates

Errata

Click for the Errata related to this title.

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020