Re: Views for denomalizing

>> You didn't write "SQL92" so are you saying that this is true for SQL3 as
>> well?

Though I have seen a SQL3 draft, I am not much familiar with its "features"

>> Is there a way to specify column ordering in a SQL table?

Several ways. INSERT, ORDER BY, UNION...several clauses

>> And if there were a product that did so, what in having a column ordering
>> is in violation of 1NF? Relations, by definition in mathematics, are
>> ordered tuples, even if we have opted to name the positions (e.g.
>> LastName) rather than number them.

The header of a relation is a set of n named, typed attributes. What does a set tell you about ordering? If you are familiar with the notion of essentiality, you should know ordering of attributes and tuples are inessential. And it would obviate some level of physical data dependence as well.

>> Are you saying that any relation where there is a tuple having the value
>> of any attribute be NULL violates 1NF?

Yes.

>> Is there a particular definition of 1NF that you could pont me to?

A table is in 1NF given at least one candidate key is present, has no dependence on physical implementation, has its column values scalar with respect to the declared type and has no "hidden" columns, internal columns, pointers etc. Formally one can put the requirements for a SQL table as:

1. No duplicate rows or columns
2. No ordering of rows or columns
3. Each row-column intersection consists of one value of a given type
4. No irregular columns
5. No NULLs ( since it is not a value of any type as per #3 )

As suggested elsewhere, SQL can violate 1, 2 and 5 directly and explicitly. 4 is implemented in some DBMSs these days, 3 is frequently attempted by SQL users by simulating types that are not supported in SQL.

Additionally, relations by definition adhere to all the above properties and hence all relations are in 1NF. A table is supposedly a 2 dimensional representation ( with rows having columns ) of an N-dimensional relation. Thus a table can be said to be in 1NF if it directly and faithfully represents a relation.

A note on nested relations: NF^2 or Non-First Normal Form "relations" do not form a structural component in relational model though some theorists have distinguished them as "nested" being distinct from "flat" relations. Several researchers have proposed such structures, for instance: http://portal.acm.org/citation.cfm?id=49347 ( Roth, Korth & Silberschatz ). Another proposal is nested relations by Makinouchi back in 77, later with PNF -- which is a proposed normalization goal for such structures ). Another restrictive variation of such structures are "Verso relations" ( citeseer it )

Recently, C J Date proposed relation valued attributes which would not violate 1NF, by virtue of the presence of a generic relation type. He has elaborated on this in his recent writings which explain its logical existence and practical utility.

Simply put, as many SQL textbooks would have you believe, 1NF has nothing to do with arrays, lists or other structures. All it matters to a table in 1NF is its conformance to relational representation and the existence of types.

>> It sounds like not only is 1NF the only normal form that SQL-DBMS's
>> enforces,

That seems incorrect, SQL DBMSs enforce no normal forms at all.

>> So, if table-valued UDF's are permitted, that is exactly what I'm
>> referring to and I'll try to change my language to match yours.

It is not just terminology, they are conceptually different.

>> If you have one of these t-SQL table valued UDF's with a single column,
>> doesn't that align with your understanding of the term "list"?

Why should they align? As mentioned before, other than the superficial similarity of its 2-dimensional representation, a list and an unidimensional relation are remarkably distinct. Does sequence, index and ordered collection give you a hint?

>> So you would end up with cross-products of the t-SQL tables so the data
>> show in fully-populated (with redundant data) rows rather than the output
>> I gave in the response to lauri?

Nested SQL expressions do not always have to be PRODUCT, though a CROSS JOIN with table valued UDF in a view is allowed in t-SQL.

-- 
Anith