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
.
