Re: Views for denomalizing

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?

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>

<table scope> ::= <global or local> TEMPORARY

<global or local> ::= GLOBAL | LOCAL

<table commit action> ::= PRESERVE | DELETE

<table element list> ::= <left paren> <table element> [ {
<comma> <table element> }... ] <right paren>

<table element> ::=

         <column definition>
     |    <table constraint definition>
     |    <like clause>
     |    <self-referencing column specification>
     |    <column options>

<self-referencing column specification> ::= REF IS
<self-referencing column name> <reference generation>

<reference generation> ::= SYSTEM GENERATED | USER GENERATED |
DERIVED
<self-referencing column name> ::= <column name>

<column options> ::= <column name> WITH OPTIONS <column option
list>

<column option list> ::= [ <scope clause> ] [ <default clause>
] [ <column constraint definition>... ]

<subtable clause> ::= UNDER <supertable clause>

<supertable clause> ::= <supertable name>

<supertable name> ::= <table name>

<like clause> ::= LIKE <table name> [ <like options> ]

<like options> ::= <identity option> | <column default option>

<identity option> ::= INCLUDING IDENTITY | EXCLUDING IDENTITY

<column default option> ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS

<as subquery clause> ::= [ <left paren> <column name list>
<right paren> ] AS <subquery> <with or without data>

<with or without data> ::= WITH NO DATA | WITH DATA

That's not very legible. There's a gzipped tar file at

http://home.earthlink.net/~jleffler/JLSS/SQL/sql-bnf.tgz

which contains both raw (text) BNF and HTML (heavily hyperlinked, and very large) versions of the grammars for SQL-92, SQL-99 and SQL-2003 (9075-2:2003, aka SQL/Foundation).

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).

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