Browse Prior Art Database

Handling the Nulls Generated by Extended Relational Operations

IP.com Disclosure Number: IPCOM000121551D
Original Publication Date: 1991-Sep-01
Included in the Prior Art Database: 2005-Apr-03
Document File: 9 page(s) / 417K

Publishing Venue

IBM

Related People

Larner, A: AUTHOR

Abstract

This disclosure pertains to two operational problems encountered since the introduction of Relational Data Base Management Systems (DBMSs). The first is the interpretation of NULL (missing or improper) values in stored relations, and the handling of them by the relational operations. The second, which this disclosure resolves, is the addition of more powerful operations, particularly Outer Joins and Outer Unions, but also including GROUPing, which is implemented in current data sublanguages, like SQL, but is without foundation in relational theory.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 14% of the total text.

Handling the Nulls Generated by Extended Relational Operations

      This disclosure pertains to two operational problems
encountered since the introduction of Relational Data Base Management
Systems (DBMSs).  The first is the interpretation of NULL (missing or
improper) values in stored relations, and the handling of them by the
relational operations.  The second, which this disclosure resolves,
is the addition of more powerful operations, particularly Outer Joins
and Outer Unions, but also including GROUPing, which is implemented
in current data sublanguages, like SQL, but is without foundation in
relational theory.

      The first of these problems is addressed in the preceding
article.  The solution described here pertains to the second problem
of interpretation and handling of the NULLs generated by the extended
(Outer) operations.  Outer Unions generate a sort of NULL quite
different to those present in stored relations, or generated by some
Outer Joins.  NULLs in stored data, can be construed by treating the
intersection of a column and a row of a table not as itself a value
but as a cell that can be empty (NULL) or contain a single value.
This requires a minor redefinition of the standard relational
operations.  The interpretation of NULLs in stored data (say, Inner
NULLs) can be left to the installations owning the data.  But the
interpretation of NULLs generated by extended relational operations
(say, Outer NULLs) must derive from the definitions of those
operations.
SUMMARY OF REDEFINITIONS AND INTERPRETATION
*    An Outer Union operation generates an object that is not itself
a table (a relation) but could be construed as a set of tables, here
termed a tabulation.  Thus relational theory extended to incorporate
this operation is no longer closed over tables, but is closed over
tabulations.  (Strictly, a tabulation is defined as a set containing
all the rows in a set of tables, and is thus in effect an equivalence
class of sets of tables or - choosing a canonical representation -
the member of lowest cardinality in that class.)
*    If two or more tables are unioned to generate a tabulation it
may be that such a tabulation contains one row that lacks a column
present in another.  Such a missing cell (column in a row), or Outer
NULL, is to be distinguished from the usual Inner (stored) NULL,
i.e., a present cell with a missing value.
*    If Outer Union is introduced, the current relational operations
are redefined (as described below) to handle rows with Outer NULLs.
*    The Outer Union operation may then replace the current (Inner)
Union operation;  Outer Joins may be defined rather than introduced
as primitive, and GROUPing may be formalized.

      In the following description, the Structured Query Language
(SQL) data sublanguage is used for illustration. The technique is
applicable to other relational languages, and in part both to
non-relational and to extended relational languages, if the...