Re: 3 value logic. Why is SQL so special?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 17 Sep 2006 18:28:19 GMT
Message-ID: <7tgPg.21728$9u.255261@ursa-nb00s0.nbnet.nb.ca>

Chris Lim wrote:

> Cimode wrote:
>

>>You should not be surprised.  SQL is not a relational language for a
>>long time.  SQL is by nature a redundant language.  Additionally, your
>>second example is a bad one two.  At the first place, there should not

>
> I also wanted to confirm something regarding the number of extra tables
> that would need to be created to avoid NULLs. Am I right in saying that
> if I have a Customer table which has a lot of optional attributes (that
> a customer may or may not have or supply (e.g. middle name, birth date,
> gender, number of children, ethnicity, etc)), then I would need a
> separate table for each of those attributes (e.g. CustomerMiddleName,
> CustomerBirthDate etc) to avoid NULLs? That seems like an awful lot of
> overhead to me.

Overhead in what sense? It just means you have a few extra names. Temporal data caused Date et al to propose a 6th normal form, and I think their arguments for independently varying attributes apply to optional data as well.

> And if I needed a query to identify customers with the same last name +
> middle name + birth_date + gender, what would the query look like for
> that?

That would depend entirely on the language syntax.

  If they were NULLable columns in the same table, it would be
> something like:
>
> SELECT last_name,
> ISNULL(middle_name, '') AS middle_name,
> ISNULL(birth_date, '1 Jan 2050') AS birth_date,
> ISNULL(gender, '') AS gender,
> COUNT(*) AS cnt
> FROM Customer
> GROUP BY last_name,
> ISNULL(middle_name, ''),
> ISNULL(birth_date, '1 Jan 2050'),
> ISNULL(gender, '')
> HAVING COUNT(*) > 1
>
> What would be the equivalent in the no NULLs database?

How about the following?

WITH INTEGRATE Customer(

   cust#

, last_name
, middle_name
, birth_date
, gender

) OPTIONAL (middle_name, birth_date, gender) AS T1
, SUMMARIZE T1 BY {ALL BUT cust#} ADD COUNT(cust#) AS cnt

   AS T2
, T2 WHERE cnt > 1;

In the above, I introduce an INTEGRATE operator as a short-cut to join a set of 6th normal form relations and an OPTIONAL operator as a short-cut to GROUP potentially missing attributes as relation valued attributes with cardinality at most 1.

I welcome any suggestions for better names or for pointers to where Date et al have specified different short-cuts for handling 6NF relations. Received on Sun Sep 17 2006 - 13:28:19 CDT