Databases Reference

In-Depth Information

EXAMPLE 11

This one is just a placemarker. In our topic
Temporal Data and the Relational Model
(Morgan Kaufmann, 2003),

Hugh Darwen, Nikos Lorentzos, and I show that several further kinds of redundancy can arise in connection with

temporal data, and we propose a number of new design principles and techniques for dealing with them. In

particular, that topic is the source of the new normal form 6NF (discussed in Chapter 13), though it's important to

understand that 6NF has an extended definition (and is “even more relevant”) in the temporal context.

EXAMPLE 12

My last example is typical of a common practical situation. It's loosely based on an example in Fabian Pascal's

topic
Practical Issues in Database Management: A Reference for the Thinking Practitioner
(Addison-Wesley,

2000). We're given two relvars that look like this (and I assume until further notice that they're base relvars

specifically):

PAYMENTS { CUSTNO , DATE , AMOUNT }

KEY { CUSTNO , DATE }

FOREIGN KEY { CUSTNO } REFERENCES TOTALS

TOTALS { CUSTNO , TOTAL }

KEY { CUSTNO }

Attribute TOTAL in relvar TOTALS is an example of what's often called
derived data
, since its value for

any given customer is derived by summing all of the payments for the customer in question. In fact, the following

equality dependency holds:

CONSTRAINT C12 TOTALS = SUMMARIZE PAYMENTS BY { CUSTNO } :

{ TOTAL := SUM ( AMOUNT ) } ;

Note:
SUMMARIZE is
Tutorial D
's analog of SQL's SELECT with a GROUP BY (speaking
very
loosely!).
18
In

case you feel more comfortable with SQL than
Tutorial D
, let me also give an SQL version of the foregoing

constraint:

CREATE ASSERTION C12 CHECK

( NOT EXISTS

( SELECT *

FROM TOTALS

WHERE NOT EXISTS

( SELECT *

FROM ( SELECT CUSTNO , SUM ( AMT ) AS TOTAL

FROM PAYMENTS

GROUP BY CUSTNO ) AS TEMP

WHERE TOTALS.CUSTNO = TEMP.CUSTNO ) )

AND

18
Actually SUMMARIZE is likely to be dropped from the next version of
Tutorial D
, because expressions involving SUMMARIZE can always

be formulated more “respectably” in terms of the relational EXTEND operator and what are called
image relations
. For example, the

SUMMARIZE expression in the case at hand could be replaced by the following: EXTEND PAYMENTS{CUSTNO}:{TOTAL :=

SUM(‼PAYMENTS,AMOUNT). For more information regarding EXTEND in general and image relations in particular, see
SQL and

Relational Theory
.