Re: 3 value logic. Why is SQL so special?
Date: Sun, 17 Sep 2006 18:28:19 GMT
Message-ID: <7tgPg.21728$9u.255261_at_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 - 20:28:19 CEST