Re: Normalisation

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 05 Jul 2005 05:31:58 GMT
Message-ID: <iXoye.13119$jX6.2816@newsread2.news.pas.earthlink.net>

> Paul wrote:
> 

>> Jan Hidders wrote:
>>
>>>> Are values in domains not atomic by definition, irrespective of any
>>>> internal structure they may have? (Structure invisible to the
>>>> relational
>>>> operators, that is).
>>>
>>>
>>> Yes, but that depends a bit on what you take as "the set of relational
>>> operators". If that includes nesting and unnesting then you make the
>>> structure of nested relations visible, and you should therefore consider
>>> them non-atomic.
>>
>>
>> OK I see. I've never understood the appeal of "nesting" or "unnesting"
>> relations. It doesn't seem to add anything to the relational model and
>> only serves to complicate things. Does anyone have a concrete example of
>> the usefulness of this? Any I've seen in the past seem to be trival to
>> implement in a standard relational model.

Sorry about one extra level of quote...

The only place where I've seen any plausible use for nested relations was in C J Date's work where he discussed using a relation-valued attribute (RVA) as the primary key (PK) of a relation describing functional dependencies (FD) in a relation schema. The PK is the set of attributes in the determinant (left-hand side, LHS) of the FD. The RHS would also be a relation-valued attribute. (If the RHS is a simple attribute (containing the names of attributes in the described relation schema), then the RVA is not the PK - the combination of the RVA plus the singleton attribute is the PK.)

You can model this without any RVA by assigning a name to the FD, then describing the determinant set in one relation and the determined set in another. In SQL (simplified):

CREATE TABLE FD(NAME CHAR(18) NOT NULL PRIMARY KEY); CREATE TABLE LHS
(

     FD_NAME CHAR(18) NOT NULL REFERENCES FD,
     ATT_NAME CHAR(!8) NOT NULL REFERENCES <column-names>,
     PRIMARY KEY(FD_NAME, ATT_NAME)

);

CREATE TABLE RHS
(

     FD_NAME CHAR(18) NOT NULL REFERENCES FD,
     ATT_NAME CHAR(!8) NOT NULL REFERENCES <column-names>,
     PRIMARY KEY(FD_NAME, ATT_NAME)

);

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

Received on Tue Jul 05 2005 - 00:31:58 CDT