Home History Design Papers New ideas Nulls OWA Links

### MISSING DATA

The closed world assumption (CWA), from Date (2005), p.168 states that:-

If tuple t could plausibly appear in Relvar R at some given time but doesn't, then the proposition p corresponding to t is assumed to be false at that time.

There are two ways of interpreting the following tuple:-

 Sample_ID SiO2 % 102 49.2
1. sample #102 contains 49.2% SiO2
2. sample #102 is reported to contain 49.2% SiO2

In other words, interpretation (1) is a statement about the real world (from his writings it seems that this is how Chris Date intends databases to be understood). Interpretation (2) in contrast is a statement about our knowledge of the real world.

Under interpretation (1) the omission of this tuple from a relation to which it would otherwise belong, according to the CWA would mean that the SiO2 value in sample 102 is not 49.2%. If there is no tuple at all in this relation for sample 102, it would mean that there is no valid value of SiO2 for sample 102. This is patently untrue since the sample MUST contain some level of SiO2 (even if zero). Hence if this relation and the database that contains it are considered to be models of the real world, the interpetation is inconsistent with the CWA - and therefore the CWA cannot apply.

Under interpretation (2) the omission of this tuple from a relation to which it would otherwise belong, according to the CWA would mean that the SiO2 value reported for sample 102 is not 49.2%. If there is no tuple at all in this relation for sample 102, it would mean that there is no reported value for SiO2 for sample 102 - a perfectly valid statement. However, the cost of this statement is that the type of attribute SiO2 is descriptive ("reported as 49.2") and not numeric ("49.2"). Hence numeric operators such as >, <, and perhaps even = cannot be used. The tuple would appear as:

 Sample_ID SiO2 % 102 reported as 49.2

It means that this attribute is a step removed from the underlying reality which Date requires to support his case for prohibition of nulls - and we are prevented from carrying out perfectly reasonable numerical queries and operations.

A possible way around the problem would be to allow the attribute to have an unconventional type "numeric OR text". Where there is a numeric value, the attribute will be numeric (just "49.2"), and where there is not, then it will contain text. Such a solution would produce relations that are similar to the 'PERS_INFO' relation of Darwen's "How to Handle Missing Data Without Using Nulls" , in which details of salary are held in a SAL_INFO attribute which can also contain descriptive text such as 'unsalaried' or 'unknown'.

This will allow numeric operators to work as normal on the numeric values. However, where an operand is non-numeric, the normal numeric operations are invalid and special rules would need to be created to define the results - and the results of queries cannot in general be restricted to 'true' or 'false'.

If the SiO2 attribute contains what is reported about the SiO2 content of sample 102 then there is no obvious reason to exclude semi-numeric data (such as "below 0.1% detection limit") or non-numeric data (such as "sample contaminated, submitting for re-analysis") or (Heaven forbid!) "missing" - even the word "null" if this is not a red rag to a bull. Any such data values (or non-values) might perfectly validly be transcribed from a laboratory report, where conventionally a "-" character is used to signify that an analysis value is missing (or alternatively some such code as "n/a" for "not analysed"). If the attribute is interpreted as "reported as ...", there is no a priori reason to discriminate against putting such codes into the database.

What we will have is of course not the SQL 'null' which is deprecated by Date, Darwen, and Pascal. However, it does not remove the basic problem - the reason why they feel that 'null' must be prohibited.

This basic problem is that if one allows 'null' or anything like it (such as text descriptors in a 'reported as...' attribute), a corollary is the need for a three-valued logic, which includes 'unknown' as a third truth value. The main justification by Date of his insistence on use of the closed world assumption and a strict true/false two-valued logic is that this represents the 'real world'.

This may indeed be the real world of warehouse management and parts ordering, but it is most certainly not the real world of observational science, where data items are quite routinely imprecise, incomplete, or missing. In this real world, the correct result of a query is not in general either 'true' or 'false' but can be 'unknown' or even 'estimated probability 0.317'.

In Date and Darwen (1998,...) the only solution offered to avoid the use of nulls is to provide for user-defined 'special-value' coding on such 'reported as...' attributes with types that are not simple character or numeric. However, apart from the practical implementation difficulties, and more seriously the threats to database integrity from possible non-standard coding by users, this leaves open the door to 3VL which Date otherwise wishes to keep firmly closed.

However, Codd (1990, p.203-4) had already pointed out a total of no less than six problems with such a special-value (he calls it default-value) approach - and these problems were not addressed by Date's suggestion.

The fact remains that working within the CWA and 2VL, although Date, Darwen, and Pascal have each proposed methods by which the 'null' representation of missing data can be avoided, none have suggested any way in which the 'missingness' of data can properly be manipulated. The basic reason for this is that when the required correct answer is "unknown", this simply cannot be produced by a two-valued logic which knows only "true" or "false".

Or to use an analogy from Peter Robson (personal communication) - the CWA would say that if you cannot prove the existence of God, then he cannot exist. Really? In other words, agnosticism is untenable.

A slide show presentation on the missing-data (and partially missing data) problemis available on SlideShare: http://www.slideshare.net/silicondale/incomplete-and-missing-data-in-geoscience-databases