Re: Pizza Example

From: Anthony W. Youngman <wol_at_thewolery.demon.co.uk>
Date: Sat, 17 Apr 2004 22:57:23 +0100
Message-ID: <2hyqTpBDhagAFw$P_at_thewolery.demon.co.uk>


In message <1YRcc.51786$3b7.838_at_newssvr16.news.prodigy.com>, Eric Kaun <ekaun_at_yahoo.com> writes
>"Anthony W. Youngman" <wol_at_thewolery.demon.co.uk> wrote in message
>news:+N$oICIHz0cAFw8o_at_thewolery.demon.co.uk...
>> In message <DIidncafbowjK-_dRVn-uA_at_comcast.com>, Laconic2
>> <laconic2_at_comcast.net> writes
>> >First, does the relational data model scatter information across
>multiple
>> >relations?
>>
>> What do you mean? If you have a repeating attribute, the relational
>> model demands that you spread data about a single object across multiple
>> tables.
>
>The argument hinges on the word "about". MV and OO folks would claim that
>any reference to X is "about" X and therefore should be collected together
>with everything else "about" X. Relational folks realize that's silly, since
>these represent different assertions, "about" X ALONG WITH OTHER THINGS.

Why, if I'm storing an attribute of entity X, is that attribute also about other things? I'm describing X, not something else.
>
>> >Second, if so, does the database fail to understand what the relational
>> >model has done?
>>
>> Let's assume we have several types of object. All have repeating
>> attributes. And all are related, some by a many-2-many relation. Can a
>> relational database group the tables according to the object they
>> describe?
>
>The question is too vague to address - why won't foreign keys suffice in
>this?

The point I was making, is that if you have a one-2-many relationship, you can put a foreign key in the object table on the "many" side. So the relationship gets allocated to that object by default - not always a good idea, but it usually helps matters when trying to understand. As soon as you have a many-2-many relationship, you need a third table, consisting solely of foreign keys. Multiplying tables increases complexity and slows down the system.

With a Pick-style database, you would still arbitrarily associate the foreign keys with one object, but the designer would (hopefully) pick the best object type to store the keys with. A relational DBA would hopefully cluster the relationship table with one of the objects, but most likely wouldn't, and the database wouldn't have a clue as to whether it should, and if so which to do it with.
>
>> >And here's a third question: can a relational model be reconstructed
>from
>> >the database schema? If not, why not?
>> >
>> Why not? If you're talking about a Pick schema, then the only reason you
>> can't get a relational model is if the Pick designer didn't do his job
>> properly.
>>
>> As a Pick database designer, I would have one FILE (our equivalent of
>> "table") per real-world object type. The data in this file *is*
>> *normalised*. It's just that it's NFNF (non first normal form).
>
>Then it's not normalized. Please, at least use a different term rather than
>co-opting one already defined. Wait - you used an S in normaliSed. My
>mistake. :-)

So you're saying the only possible normalisation is First Normal? That Second Normal, Third Normal etc don't exist?
>
>> So. Imagine you've defined a view, in your relational database, that
>> joins all tables representing an object. You then "list" (sorry I don't
>> know the relational term) one object in your view. In your
>> two-dimensional view, imagine that all duplicated values just "don't
>> exist".
>
>What duplicated values? Presumably the "list" (a report) would include a
>comma-separated representation of the multiple values or some such.

Let's say I "list" a pizza. It's a join of base and topping. As I understand relational, the resulting view would be

Neapolitan Mozarella
Neapolitan Ham
Neapolitan Mushroom

"Neapolitan" only exists once in the database, but it exists three times in my view. That brings up another problem - isn't the application supposed not to care whether it's dealing with views or tables? But as soon as you have a join like this, the resulting view is not normalised because it has repeating data! So your application MUST have some understanding of the underlying db structure because otherwise it can try and do something stupid (yes, the db will stop it succeeding, but it won't stop it trying!)
>
>> You now have the equivalent of a Pick RECORD (a bit like your
>> row). We don't duplicate a simple attribute because it doesn't make
>> sense to do so
>
>In what sense does the relational model duplicate an attribute?

See above - in the view (not the database itself) the value is presented to the overlying layer multiple times.
>
>> why list it repeatedly when it only exists once per object?
>
>There are multiple values which may be associated with X, but in what sense
>is it "listed repeatedly"? Again, I don't see the "duplication."
>

Cheers,
Wol

-- 
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
HEX wondered how much he should tell the Wizards. He felt it would not be a
good idea to burden them with too much input. Hex always thought of his reports
as Lies-to-People.
The Science of Discworld : (c) Terry Pratchett 1999
Received on Sat Apr 17 2004 - 23:57:23 CEST

Original text of this message