Anybody for Automated Comparative Normal Form?

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Thu, 18 Nov 2004 22:21:56 -0500
Message-ID: <kf5072-ilf.ln1_at_pluto.downsfam.net>

It seems that automation is very important if you want to get anywhere close to domain key normal form.

On the dbdebunk site Chris Date fielded a question on domain key normal form, at this link:

http://www.dbdebunk.com/page/page/621935.htm

A choice quote is this by Date, in describing the original definition of DK/NF:

"Fagin defines a relvar R to be in DK/NF if and only if every constraint on
R is a logical consequence of what he (Fagin) calls the domain constraints and key constraints on R."

Perhaps we can rephrase this into the vernacular as saying all biz rules are specified completely by primary and foreign keys? Sounds exciting, but then Date says this:

"The sad fact is, not all relvars can be reduced to DK/NF; nor do we know
the answer to the question "Exactly when can a relvar be so reduced?" "

His example of a problem case is positively weird. He cites a case of a constraint placed onto the employees table that it can contain only ten rows. I'm hoping he was in a hurry and did not bother to think it through, because that's a straw man if I ever heard of one. Do you delete employees when they are terminated? It makes no sense.

But let's pretend the constraint was real, perhaps the CEO decided to sit in on a design meeting. If automation were considered to be as important as constraints, we could define some table X that contained an aggregate of the count of rows in the EMPLOYEES table. We could then place a constraint on Table X that EMPLOYEE_COUNT <= SysVar("EMPLOYEE_COUNT_MAX").[1]

This brings the problem back to what I have called "arbitrary constraints" in recent weeks, but which should probably be called "comparative constraints", because this is what they keep turning into. It should be possible to demonstrate that by using automation, one can reduce any arbitrary constraint to a comparison constraint.

I have found no serious treatment of automation in my Google searches, so I will dub a new ideal: Automated Comparative Normal Form. The databases that I build are in this normal form, which has these characteristics:

1. There are four constraints:
2. Unique Keys
3. Foreign Keys
4. Comparisons between columns, or a column to a sysvar
5. No automated columns can be supplied in an INSERT or UPDATE statement.
6. User entered-data is anything that can be supplied in an INSERT or UPDATE statement. (Ken is typically on the post-3NF path).
7. Various forms of automation are provided, all of which break normalization, but because of constraint d), we maintain correctness[2].

If anybody knows of similar work that I may be re-inventing needlessly, feel free to chime in. All comments that automation is a priori bad will go to /dev/null.

[1] We certainly would not put a constant into the constraint, any more

    than we would put a constant in code. A constant is a value,     it is data, and it belongs in a table. The imaginary SysVar("key")     function fetches the value of the limit.

[2] Many DBA's mistake the primary goal of normalization to be maximizing

    normalization. The goal of normalization is to ensure correctness.

-- 
Kenneth Downs
define class sigblock
{
        #REGION Generated Code
        #       DO NOT EDIT!  This code generated by
        #       the .net sigblock EDI code generator
        #END REGION
}

Received on Fri Nov 19 2004 - 04:21:56 CET