Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half fu

Here is another interesing "thought experiment". You have a table that store
s
information about people. Let's say there are three columns
CREATE TABLE People (
PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
PersonName varchar(512) NOT NULL,
Address varchar(512) NOT NULL
)
An entry may look like
PersonId PersonName Address
----
--
101 Mark Williams 1519 15th Street, Albany, NY 12183
One could arguably way that storing the address in this way violates 1NF, so
you redesign your table.
CREATE TABLE People (
PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
PersonName varchar(512) NOT NULL,
StreetAddress varchar(512) NOT NULL,
City varchar(512) NOT NULL,
State char(2) NOT NULL,
ZipCode int NOT NULL
)
All fine, but now someone comes along and says your table isn't in BCNF (or
5NF) because there's a non trivial functional dependency (ZipCode depends on
City / State).
Relational theory does not expressly prohibit multi-valued attributes, like
XML, so the table I started with could have been (at least in SQL 2005)
CREATE TABLE People (
PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
PersonName varchar(512) NOT NULL,
Address xml
)
And now the table is (somewhat magically) back to BCNF, just by changing the
datatype that the Addrress was expressed in. To me, that means that it was
always in BCNF, even though you used commas to express the address.
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"05ponyGT" wrote:

> Lately there seems to be quite a few posts regarding the
> concatenating of column data in sql.Faq2559,written by a very
> knowledgable MVP,is often cited as a reference for this
> problem's solution.This problem is interesting as it has
> spurred debate on a number of issues including client vs. server,
> static vs. dynamic sql and the nature of the sql solutions.
> Perhaps there is even more of interest here between the code.
> For example, the author starts out with the following:
>
> Color
> --
> red
> orange
> blue
> green
> And return a resultset like this:
> Colors
> --
> by stating:
> Has anyone wondered exactly what 'isn't exactly relational' means?
> I assume I'm safe in assuming he is referring to the resultset.
> Which part is relational and which part is not relational?(Putting
> aside the idea whether it even makes sense to refer to a resultset
> as being 'relational').Is it the comma's that cause the problem?
> What about :
> 1,100,100
> which could be money?Has anyone said this is not exactly relational?
> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
> Or does the fuzzy part of relational come from the fact that the
> result came from a single column?This is interesting since 2V logic,
> whether a result is relational or quasi-relational,is predicated on
> 'where' the result came from.So we don't know if '1,100,100' is
> relational or almost relational unless we know 'exactly' where it
> came from.What if we don't know where it came from?Is the result
> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
> that a 'pure' sql solution to concatenation 'would never destroy 1NF
> by building a list structure..'.Sounds real good but it is Bizarre!
> Perhaps he's suggesting a new type of check constraint.The 99.9%
> ivory soap constraint:)
> Anyone on what this phrase means?
> Here's another one.The author states:
> 'SQL Server 2005 adds some interesting options for this kind of
> non-relational
> query.'
> What is a 'non-relational' query?Is it a query that produces a result
> that isn't exactly relational?Is it a query that some dislike?You can
> see where this is going:)
> (Note that another MVP referred to the xml solution as a 'set based
> solution'.
> but that is a subject for another thread:)
> Anyone on what a non-relational query is?
> It's obviously easier to code this stuff than it is to talk about it.
> Which would explain why sql remains such a mystery to so many.Be that
> as it may, to try to get some clarity in all the haze is much more
> interesting,challenging and fun than coding it.You just gotta love
> the glass:)
> (The author of the faq is to be recognized for his great contributions
> and assistance to the server community.His words have impact and the
> bar is set high in such a case).
> musings from
> www.rac4sql.net
>
>
>Using xml to prove a relational point!It could only happen in the IT
industry:)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:48FAE3B3-9E61-452E-9E6B-614E07AF5299@.microsoft.com...
> Here is another interesing "thought experiment". You have a table that
> stores
> information about people. Let's say there are three columns
> CREATE TABLE People (
> PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
> PersonName varchar(512) NOT NULL,
> Address varchar(512) NOT NULL
> )
> An entry may look like
> PersonId PersonName Address
> ----
--
> 101 Mark Williams 1519 15th Street, Albany, NY 12183
> One could arguably way that storing the address in this way violates 1NF,
> so
> you redesign your table.
> CREATE TABLE People (
> PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
> PersonName varchar(512) NOT NULL,
> StreetAddress varchar(512) NOT NULL,
> City varchar(512) NOT NULL,
> State char(2) NOT NULL,
> ZipCode int NOT NULL
> )
> All fine, but now someone comes along and says your table isn't in BCNF
> (or
> 5NF) because there's a non trivial functional dependency (ZipCode depends
> on
> City / State).
> Relational theory does not expressly prohibit multi-valued attributes,
> like
> XML, so the table I started with could have been (at least in SQL 2005)
> CREATE TABLE People (
> PersonID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
> PersonName varchar(512) NOT NULL,
> Address xml
> )
> And now the table is (somewhat magically) back to BCNF, just by changing
> the
> datatype that the Addrress was expressed in. To me, that means that it was
> always in BCNF, even though you used commas to express the address.
> --
> If you posted to this forum through TechNet, and you found my answers
> helpful, please mark them as answers.
>
> "05ponyGT" wrote:
>|||>> All fine, but now someone comes along and says your table isn't in BCNF
Dependencies are business specific. If the specific business model does not
explicitly establish a transitive dependency as above, you don't have to
consider it.
Relational theory does prohibit multi-valued attributes, but in this case
since the SQL 2005 supports an XML type, Address values is just as scalar as
CHAR, INT or DATETIME values.
Anith