Re: Can we solve this -- NFNF and non-1NF at Loggerheads

<lauri.pietarinen_at_atbusiness.com> wrote in message news:1107803406.581986.297810_at_c13g2000cwb.googlegroups.com...
> Paul wrote:
>> lauri.pietarinen_at_atbusiness.com wrote:
>> > You are correct, except for perhaps the eliminating of NULLs (if
>> > you think that NULLs are a bad idea). Nulls would be represented
>> > by empty sets. Representing outer joins is more natural since rows
>> > with no corresponding "child rows" could be handled the same as the
>> > ones with child rows. Also, if you have a "multiway" outer join
>> > things get pretty tricky. Say if the person is associated with
>> > n emails and m phone numbers, you will get n x m rows with the
>> > person-columns repeated. These have to be eliminated in the
>> > application program.
>>
>> Could you explain what exactly you mean by "multiway join"? - Google
>> isn't being too helpful.
>>
>
> Sorry, that was just a term I invented on the fly!
>
> Take, for example the following tables:
>
> PERSON(person_id PK, person_nme);
>
> --person can have multiple email-addresses
> EMAIL(email_id (PK), person_id(FK), email_txt)
>
> --person can have multiple phonenumbers
> PHONE(phone_id (PK), person_id(FK), phone_type, phone_number)
>
> With the following values:
>
> PERSON:
> 1, TOM
> 2, DICK
> 3, HARRY
>
> EMAIL
> 1, 1, tom_at_acme.com
> 2, 1, tom_at_hotmal.com
> 3, 2, dick_at_mycomp.com
>
> PHONE
> 1, 1, MOBILE, 111-222 -tom
> 2, 1, OFFICE, 222-333 -tom
> 3, 1, FAX, 444-555 -tom
> 4, 3, MOBILE, 666-777 -harry
>
> The SQL to get all email-addresses and phone numbers
> would be something like this:
>
> select ...
> from PERSON P, EMAIL E, PHONE PH
> where P.person_id = E.person_id and
> P.person_id = PH.person_id
>
> The problems with this SQL is that
>
> 1) You would get 6 rows for TOM, 2
> for each phonenumber and 3 for each
> email address
>
> 2) For Dick and Harry you would not
> get any rows
>
> Using outer join you can correct problem 2)
>
> *A* solution for 1) would be to nest
> the emails and phone numbers, naturally
> only in the result, not in the schema!

This brings up a point I have wanted to be sure I'm clear about. If we focus on logical modeling of the data, ignoring (for this discussion) any conceptual or physical modeling, then we could work with a model that includes a relation with Tom, Dick and Harry in the tuples, with nested tuples in attributes that include their e-mail and phone numbers. We could work with this model when interfacing with the database (that software could be written to put in separate relations, one would think) and with anything else with which a modeler would interface (a person, for example).

Even if you believed the physical model required exploding out multiple base tables, the logical model need not. My discussion is entirely about the logical model, while others in the PICK world argue the advantages of using this with the physical model. (They know more about performance issues, disk reads, etc, than I.)

So, why do you write "only in the result" rather than "only in the logical model"? Why do you or the computer need the logical model to include multiple relations with this particular set of predicates? It seems to me that the DBMS software and you could both be successful with your work with a model that includes only one relation, rather than three, in this case. Your thoughts? Thanks. --dawn
<snip> Received on Mon Feb 07 2005 - 20:44:02 CET