Re: Views for denomalizing

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:pQFMd.5658$Nn1.3745_at_newsread1.news.pas.earthlink.net...
> Dawn M. Wolthuis wrote:
>> "Anith Sen" <anith_at_bizdatasolutions.com> wrote:
>>>>>The only normal form that cannot be breached when creating SQL92 views
>>>>>is the same one required for base tables -- 1NF.
>>>
>>>Nope. SQL tables, *technically* cannot have a repeating group, since
>>>columns in a SQL table can have only scalar values.
>>
>> You didn't write "SQL92" so are you saying that this is true for SQL3 as
>> well? I was under the impression (from reading, rather than
>> experimenting) that SQL3 fixes this problem, permitting child tables, for
>> example.
>
> What did you have in mind for SQL3? SQL-1999 or SQL-2003?

I decided to blur it in the question but prior to 2003, I would have said SQL-99.
> Section 11.3 of ISO/IEC 9075-2:2003 <table definition> contains the BNF:
>
> <table definition> ::=
> CREATE [ <table scope> ] TABLE <table name> <table contents
> source>
> [ ON COMMIT <table commit action> ROWS ]
>
> <table contents source> ::=
> <table element list>
> | OF <path-resolved user-defined type name> [ <subtable clause> ]
> [ <table element list> ]
> | <as subquery clause>
<snip>
< <subtable clause> ::= UNDER <supertable clause>
>
> <supertable clause> ::= <supertable name>
>
> <supertable name> ::= <table name>
>
<snip>
> The mess above is cut'n'pasted from the sql-2003-2.bnf.html file (about 1
> MB - more disk space than is available in the web site).

Thanks for the link. The supertable/subtable is in SQL-99 as well, although without looking at it again, I had kept it in my brain a parent and child tables, while they opted for the OO terminology of super and subtables. That works for me. Of all of the institutions of higher ed in the world, I wonder what percentage are teaching this SQL-99 or SQL-2003 variation on SQL statements along with instructing students of examples where it is useful to nest relations. I suspect it is a small number. I also suspect that the implementations of this standard do not make it as easy to have super and subrelations as to put the data in what-used-to-be-called-1NF

Cheers! --dawn Received on Fri Feb 04 2005 - 17:48:53 CET