My last example is typical of a common practical situation. It’s loosely based on an example in Fabian Pascal’s book 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!).^[166] 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
            NOT EXISTS
              ( SELECT *
                FROM ( SELECT CUSTNO , SUM ( AMT ) AS TOTAL
                       FROM   PAYMENTS
                       GROUP  BY CUSTNO ) AS TEMP
                WHERE  NOT EXISTS
                     ( SELECT *
                       FROM   TOTALS
                       WHERE  TOTALS.CUSTNO = TEMP.CUSTNO ) ) ) ;

For further explanation of SUMMARIZE, see SQL and Relational Theory.

Now, derived data is clearly redundant—though note once again that there are no violations of either normalization or orthogonality here (in particular, relvars PAYMENTS and TOTALS are both in 6NF). I’ll analyze this example in more detail in the section immediately following.