Re: sql views for denomalizing

David Cressey wrote:
> "Marshall Spight" <marshall.spight_at_gmail.com> wrote in message
> news:1122753267.167221.178610_at_g47g2000cwa.googlegroups.com...
>
> > Some phrasings of 1NF say this, and some just say "no repeated groups."
> > I've pretty much reached the conclusion that 1NF doesn't really mean
> > anything and isn't grounded in any particular theory. With, say,
> > 2NF, 3NF, and BCNF, you can point to the specific redundancy, and
> > the specific update anomalies. Can anyone do that with 1NF? It just
> > doesn't seem to fit with the other normal forms.
>
>
> There are no update anomalies associated with NFNF data. The problem with
> NFNF data is an access anomaly,
> not an update anomaly.

This anomally only shows up if restricting the dbms to relational operators, right? Otherwise I haven't been aware of it in practice.

> The exact description of the access anomaly is
> rather involved, but the gist of it is
> captured by my comment over in another thread, concerning the difference
> between two columns named "Language2" and "Language3"
>
> What I was really asking, in that other thread, was whether those two
> columns were a repeating group, masquerading as 1NF data. I think,
> although I'm not sure, that your response identifying my response as
> addressing the semantics is correct.
>
> Interestingly, no one thought to ask me why I didn't include "Language1" in
> my discussion.

Do tell, why didn't you include "Language 1" in your discussion?

> Moving right along, the biggest problem with normalization "rules" is that
> they have been presented as prescriptions (or proscriptions) when they
> should really be treated as descriptive. This whole business of "thou
> shalt" or "thou shalt not" in design is a bunch of crap, in my book. The
> better treatment would be: "if you do x, then be prepared for y as a
> consequence." Or, alternatively, "if y is a problem that you really need
> to avoid, then avoid doing x".

agreed

>
> Moving along again, 1NF is the one place where I differ with Down's Law
> ("Everybody understands tables just fine.") There are a lot of people who
> don't understand the difference between tabulated data and crosstabulated
> data. And they think the difference is trivial. And they store
> crosstabulated data in an SQL table, and they wonder why using it is so
> damned difficult.

I can hear the experience in that claim and since I hadn't thought of it quite that way, I'll tuck that one away.

> 1NF is related to the difference between tabulation and
> crosstabulation.

I can see a correlation between NFNF data models and OLAP -- you can do OLAP queries directly against the data model, without building fact & dimension tables, for example. Is that what you mean or is there some other relationship?

> >
> > Doesn't dataphor?
> >
> I don't know jack about dataphor, but when I see the discussion, it always
> takes me back to Datatrieve, circa 1982.
>
> Interestingly Datatrieve could view data either in a way that I'll call
> hierarchical or in a way that I'll call "proto relational".

I did a little with Datatrieve on a PC in 1986 IIRC, but never saw the relational side. At the time I thought of it as a codasyl reporting tool ported to the pc. If you know what languages the datatrieve query language came from or went to, pass it along, else I'll have to google for it someday. I'm thinking that maybe you worked with a di-graph model (aka network) once too and saw how sweet it was ??

> And retrieving data using CROSS (a little like a join) and then turning
> and stuffing it into a hierarchical report was so-o-o-o-o easy that it was
> beneath the dignity of snobby "real programmers".

I recall poo-pooing the product, but not the query language -- my issue was the data that was going into never or rarely backed up PCs using datatrieve (it was used like an access database at the time).

> I never realized that it
> was a problem until I tried to do it without Datatrieve.

That is how I think of the multivalue query language (even though I have many pages of changes I would like to see made to it, since it has not changed much in the forty years (!) since it was specified).

Querying for all students currently taking a math course who are not math majors is non-trivial in SQL (due to the negation along with multiple 1 to many relationships, if I'm thinking clearly) and would be trivial in both datatrieve and pick:

list students with courseDept = "Math" and with every major <> "Math"

> So much for the "good old days". "well, something's lost, and something's
> gained, in living every day."
"I've looked at life that way" but it seems "I really don't know life at all"
cheers! --dawn Received on Sat Jul 30 2005 - 23:56:46 CEST