Re: Views for denomalizing

"Anith Sen" <anith_at_bizdatasolutions.com> wrote in message news:VXjMd.5243$cl1.3811_at_newsread3.news.pas.earthlink.net...
>>> 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.

> However that does not mean that all are in 1NF. Duplicates,

Yes -- got that one. I have to indicate I'm referring to tables with candidate keys.

> column ordering,

Is there a way to specify column ordering in a SQL table? 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.

> NULLs

Are you saying that any relation where there is a tuple having the value of any attribute be NULL violates 1NF? Is there a particular definition of 1NF that you could pont me to? It sounds like not only is 1NF the only normal form that SQL-DBMS's enforces, it is only the aspect of 1NF that I really care about -- nested tables -- that is actually enforced! Obviously if any NULL value causes non-1NF structures, then a SQL-DBMS not only could have enforced that, but could have eliminated the entire 3 Valued Logic hogwash (not to show my hand, but ...) from the languages.

etc. all cause violation
> of 1NF. ( In practice, SQL user often try to fake/simulate list-like
> structures using existing types though )

Yes. As we know, when software isn't written to meet the needs of users, those users can be pretty clever in how they opt to use the software. It isn't surprising that SQL-DBMS users have fiddled around to make the products work for them.

>
> Your question regarding UDFs seems more apt for the product specific
> forum.

But my subsequent "blog" that the question is related to does not, thus my use of this forum -- 2 birds & all. Sometimes it helps when answering a question to understand the motivation for the question.

>>> I recall that SQL Server UDF's permit lists to be returned, ...
>
> That is incorrect. SQL Server UDFs are either scalar or table-valued.

I guess I'm using incorrect terms. I was using the term "list" for "table-valued" although the word list is typically used for relations of dimension 1. 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.

> And a table valued UDF is more or less similar to a parameterized view.
> Its return value is a table; in other words a table valued UDF returns a
> set of rows each with a set of columns each with a valid t-SQL datatype.

Yes, that is what I'm referring to.

> However, they may violate 1NF due to reasons mentioned before.
>
>>> Do SQL Server customers create views that include these user defined
>>> functions (returning derived data values) with lists of values?
>
> Not with "lists of values", but with a t-SQL table valued UDF, yes.

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"? I'm really curious about your understanding of such terms. Is your concern that by saying "list" I might be implying an ordered list or is there something else about the word "list" that does not align with your understanding of a dimension 1 table?

>
>>> If so, how does SQL handle queries against such views? If not, what
>>> prohibits this?
>
> It would behave the same way as any nested SQL expression would behave.

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? This is what I'm trying to understand without taking the time to experiment (not having any SQL DBMS's installed right now -- I know you could tell me to try it myself).

> However, not all t-SQL operations are allowed within a table valued UDF --
> the product manual should have the details on which constructs/expressions
> are allowed.

Thanks for the responses. Someday I'll pull down a copy of SQL Server again, but not high enough on the priority list right now. I just wanted to understand it for communicating my 1NF concerns in terms of today's database capabilities rather than simply reacting to current theory. Cheers! --dawn

> --
> Anith
>
Received on Thu Feb 03 2005 - 14:18:20 CET