Page 24 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 2: Database Relational Model

Anonymous Columns Notes

A column in an SQL table can be unnamed and thus unable to be referenced in expressions. The
relational model requires every attribute to be named and referenceable.

Duplicate Column Names

Two or more columns of the same SQL table can have the same name and therefore cannot be
referenced, on account of the obvious ambiguity. The relational model requires every attribute
to be referenceable.

Column Order Significance

The order of columns in an SQL table is defined and significant, one consequence being that
SQL’s implementations of Cartesian product and union are both non-commutative. The relational
model requires there to be no significance to any ordering of the attributes of a relation.

Views without CHECK OPTION

Updates to a view defined without CHECK OPTION can be accepted but the resulting update to
the database does not necessarily have the expressed effect on its target. For example, an invocation
of INSERT can be accepted but the inserted rows might not all appear in the view, or an invocation
of UPDATE can result in rows disappearing from the view. The relational model requires
updates to a view to have the same effect as if the view were a base relvar.

Columnless Tables Unrecognized

SQL requires every table to have at least one column, but there are two relations of degree zero
(of cardinality one and zero) and they are needed to represent extensions of predicates that
contain no free variables.

NULL

This special mark can appear instead of a value wherever a value can appear in SQL, in particular
in place of a column value in some row. The deviation from the relational model arises from the
fact that the implementation of this ad hoc concept in SQL involves the use of three-valued logic,
under which the comparison of NULL with itself does not yield true but instead yields the third
truth value, unknown; similarly the comparison NULL with something other than itself does
not yield false but instead yields unknown. It is because of this behaviour in comparisons that
NULL is described as a mark rather than a value. The relational model depends on the law of
excluded middle under which anything that is not true is false and anything that is not false is
true; it also requires every tuple in a relation body to have a value for every attribute of that
relation. This particular deviation is disputed by some if only because E.F. Codd himself
eventually advocated the use of special marks and a 4-valued logic, but this was based on his
observation that there are two distinct reasons why one might want to use a special mark in
place of a value, which led opponents of the use of such logics to discover more distinct reasons
and at least as many as 19 have been noted, which would require a 21-valued logic. SQL itself
uses NULL for several purposes other than to represent “value unknown”.

Example: The sum of the empty set is NULL, meaning zero, the average of the empty set
is NULL, meaning undefined, and NULL appearing in the result of a LEFT JOIN can mean “no
value because there is no matching row in the right-hand operand”.

LOVELY PROFESSIONAL UNIVERSITY 17