THE FINAL NULL IN THE COFFIN: A Relational Solution to Missing Data (PRACTICAL DATABASE FOUNDATIONS Book 3)

Note: This paper assumes familiarity with the concepts and terminology introduced in paper #1, Business Modeling For Database Design and #2.

The relational data model (RDM) is based on the two-valued logic (2VL) of the real world: every proposition about the real world is unequivocally true or false. But our knowledge of the real world is usually imperfect—some data are missing—which means that we don't always know whether certain propositions are true or not. This violates 2VL and database query results are no longer guaranteed to be provably logically correct with respect to the real world.
Missing data has possibly been the thorniest aspect of database management. Without a logically sound yet practical solution, data professionals and users are between a rock and a hard place. They must either (a) rely on SQL's arbitrary and flawed implementations of three-valued logic (3VL) based on NULL’s and risk results that are erroneous in ways hard to discern or easy to misinterpret, or (b) undertake in applications a prohibitively complex, error prone and unreliable burden that belongs in the DBMS.

This paper illustrates some of the drawbacks of the many-valued logic (nVL, n > 2) approach to missing data and SQL’s NULL scheme and proposes a solution within the 2VL/relational framework that:
· Guarantees data integrity and logically correct query results;
· Avoids the complications and problematics of nVL/NULL's;
· Requires no changes to the relational model;
· Is largely transparent to users;
· Keeps users better apprised of the existence and effects of missing data.

Note: The proposed solution requires research into its full implications for data manipulation and integrity enforcement, but we believe it is theoretically sound and implementable in a TRDBMS using technologies that, unlike SQL, support full physical data independence e.g. the TransRelational™ Implementation Model (TRIM).

Table of Contents

Introduction

1. “Inapplicable Data”: Nothing's Missing

2. Missing Data: Into the Unknown

3. SQL’s NULL: What Valued Logic?

4. Known Unknowns: Meta-data

5. A 2VL Relational Solution
5.1. The Practicality of Theory
5.2. 2VL vs. NULL’s in the Real World
5.3. Relation Proliferation
5.4. The TransRelational™ Implementation Model

Conclusion

Appendix A: Misconceptions Debunked

References