Re: Views for denomalizing

<lauri.pietarinen_at_atbusiness.com> wrote in message news:1107412487.933609.147500_at_c13g2000cwb.googlegroups.com...
> >
>> 4) The only normal form that cannot be breached when creating SQL92
> views is
>> the same one required for base tables -- 1NF. Even though the
> purpose of a
>> View is to provide the user a "view" of their data, if the user would
> like
>> to see lists as attributes (e.g. a list of e-mail addresses as an
> attribute
>> for a person), there is no provision for this in a SQL92 View.
>>
>
> Actually, SQL92 does NOT impose 1NF on us, because it does not force
> you to have a key on your table (in SQL-speak, that would be a primary
> key or unique constraint, usually enforced by unique indexes).

That's a good point. It does not address the aspect of 1NF I'm interested in -- that of nested lists, but for accuracy I need to say that it imposes 1NF on tables with candidate keys. Thanks.

> 1NF is just another way to say that all data must be perceived as
> relations,
> and relations, by definition do NOT have duplicate tuples (or rows, in
> SQL-speak).

Even Codd says (in 1970 paper) that, of course, relations by mathematical definition could include tuples as the value of a tuple. In fact, mathematicians often speak of relations in terms of pairs since a tuple of any dimension (a1, a2, a3, ..., an) maps to a pair (a1, (a2, a3, ..., an)). Relations, by definition, do not have restrictions on the domains of the attributes.

>
> 1NF is easy to enforce with keys. On the otherhand it would be
> very hard for the DB to enforce the other normal forms, if not
> impossible,
> without knowledge of the functional dependencies.

That explains why, historically, tools would enforce 1NF -- because they could do so easily. Not only is it easy to enforce, it makes it easier FOR THE DBMS VENDOR to implement the logic of the database itself if we restrict the user in a variety of ways. Also, I'll grant that if we give more rope to the user, they might be more likely to hang themselves, so we can help force them to design before they implement by having some restrictions.

I can fully understand tools taking this approach in the 70's & 80's, but when you see how software tools are bending over backwards to give flexibility to users today, restricting domains on attributes in this way is really not acceptable, at least to me.

>>
>> Question: I recall that SQL Server UDF's permit lists to be returned,
> while
>> the last time I asked, Oracle did not. Do SQL Server customers
> create views
>> that include these user defined functions (returning derived data
> values)
>> with lists of values? If so, how does SQL handle queries against
> such
>> views? If not, what prohibits this?
>
> What do you mean by "how does SQL handle queries against such views"?
> What would you want it to do? Could you give an example?
> regards,

For example, if a UDF named "Email" has a value that is the list of e-mail addresses for a person and another one named Phone had a list of phones, then a view could have tuples such as:

(PersonID, LastName, FirstName, Phone, CompanyName, Email) would lend itself to a SQL select * having output such as:

12345    Harley               George       (514) 555-6762      This Company 
Name     gharley_at_aol.com
                                                         (514) 555-1123 
george_harley_at_thiscompanyname.com
                                                                             
                                                 george_at_harleyfamily.org

22222    Vander Kooi     Mary          (711) 555-8785      Another Company 
mvkooi_at_yahoo.com
                                                         (711) 555-1191

etc.

Alternatively, it could do what view on three base tables with the above values -- Person, Phone, Email -- would do if you joined them and then did a select *. It could provide the user a cross-product of the three -- obviously not user friendly, but better for pouring into Excel (I didn't dare say "flat" ;-)

I did try this with a PostgreSQL child table a while back (so, not using derived data) but I only had one list attribute in my tuple. I did a SELECT * on it and did get the child table data along with the parent IIRC. I'm pretty sure that SQL3 provides for queries on views with list attributes, but haven't seen any implementation of this that I know to be standard in order to see just how it handles such queries.

Thanks. --dawn

> Lauri Pietarinen
Received on Thu Feb 03 2005 - 13:52:36 CET