Re: OODB

"Adrian Veith" <adrian_at_veith-system.de> wrote in message news:as7gds$mvf$02$1_at_news.t-online.com...
> "Scotty" <invallid_at_invalid.spam> schrieb im Newsbeitrag
> news:noaotu066nbjo304t5qbvrr74gfencntcv_at_4ax.com...
> > Do they exist or are they a figment of a college lecturers
> > imagination?
>
> They do exist!
>
> But, do REAL relational databases exist ?
>
> Problem:
>
> Consider the following families: Family Miller has kids John and Steve,
> Family Jones has kid Tom and Family Smith has no kids.
>
> So you can design these two relations:
>
> FAMILY (ID, NAME)
> 1 Miller
> 2 Jones
> 3 Smith
>
> and KID(ID, NAME)
> 1 John
> 1 Steve
> 2 Tom
>
> How do you get all the families with their kids ?
>
> Solution a) the natural join:
>
> select * from family f, kids k where f.id=k.id
>
> gives you the result:
>
> 1 Miller 1 John
> 1 Miller 1 Steve
> 2 Jones 2 Tom
>
> how can we interpret the result:
>
> - Family Miller has 2 kids, because there are 2 rows with name Miller ?
> - Family Jones has 1 kid, because there is 1 rows with name Jones ?
> - Family Smith has no kids, because there is no row with name Smith ?
>
> so far so good. But then, the following statements may also be true:
>
> - Family Bush has no kids, because there is no row with name Bush ?
> - or family Bush does not exist ?
> - Family Smith is not a family, because they don't have kids ?
>
> Solution b) ok, ok try a non relational workaround - the outer join:
> select * from family f left outer join kids k on f.id=k.id
>
> 1 Miller 1 John
> 1 Miller 1 Steve
> 2 Jones 2 Tom
> 3 Smith <NULL> <NULL>
>
> At the first sight, the result looks slightly better:
>
> - Family Miller has 2 kids, because there are 2 rows with name Miller
> - Family Jones has 1 kid, because there is 1 rows with name Jones
> - Family Smith exists.
>
> but:
>
> - Has Family Smith 1 kid, because there is 1 row with name Smith ?
> - Is the statement 3 == <NULL> true or not ?
> - Is the result a true relation ?
>
> Solution c) ?
>
> --
> It looks like, that a "relational" database needs a non relational
> operation, which, in return, gives you a invalid relation !
> And as a result we have to live with these <NULL>s
>
>
> Problem:
>
> What is: "Relational Database" left outer join "Relational Algebra" ?
>
> a) Relational <NULL>
> b) <NULL> Database
> c) both a) and b)
> d) ?
>
> Welcome to the real world ;-)
>
> cheers,
>
> Adrian.

Duh! Why are you counting rows returned from a query rather than using COUNT(Kid.ID) with GROUP BY Family.ID? Well, not you, but presumably a programatic loop summing by Family.ID.

You need two queries to answer two different questions - i.e. get both the names of kids in each family and the number of kids in each family.

(Note: I will ignore the PK problem in Kids where Family.name is "Foreman":

   http://www.biggeorge.com/familyman/familyman.htm :-) Received on Sat Nov 30 2002 - 15:38:16 CET