Re: Bidirectional Binary Self-Joins
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 30 Mar 2007 19:04:54 GMT
Message-ID: <qbdPh.17434$PV3.180071_at_ursa-nb00s0.nbnet.nb.ca>
>
> scored
>
>
> Away_score)
>
>
> for
>
>
> unsatisfactory.
>
>
> got no quick fix. Sorry. I am going to point out that a game can be
> identified by a date and a SET of teams, not a LIST of teams. Emphasis
> mine. So the old bugaboo about sets versus lists surfaces once again.
>
> Marshall's description, in terms of RVA's obscures the difference between a
> set and a list, because when you lay it out in text, a set looks like a
> list.
>
> Marshall, for your RVA solution, how would the query language handle the
> question,
> "Find all the games that Hope played"?
>
> Also, let's say you already had the game you stated in the database, and
> you tried to insert the following game:
>
> { date=12-Dec, teamscores={(team=Calvin, score=32), (team=Hope,
> score=59)}}
>
> Would the database engine detect that the game was already in the relation?
> If so, how would it do that? I realize my question is a physical one, not a
> logical one, but I'm asking anyway.
Date: Fri, 30 Mar 2007 19:04:54 GMT
Message-ID: <qbdPh.17434$PV3.180071_at_ursa-nb00s0.nbnet.nb.ca>
> "JOG" <jog_at_cs.nott.ac.uk> wrote in message
> news:1175268237.587602.313730_at_r56g2000hsd.googlegroups.com...
>
>>On Mar 30, 3:44 pm, "Marshall" <marshall.spi..._at_gmail.com> wrote: >> >>>On Mar 30, 6:40 am, "David Cressey" <cresse..._at_verizon.net> wrote: >>> >>> >>> >>> >>>>"JOG" <j..._at_cs.nott.ac.uk> wrote in message >>> >>>>>"There was a game on Date [Dec-12], where the Home team [Hope]
>
> scored
>
>>>>>[59] and the Away Team [Calvin] scored [32]" >>> >>>>>Then a schema of ( Date, Home_team, Home_score, Away_team,
>
> Away_score)
>
>>>>>is sufficient. However if there was no such distinction between home >>>>>and away teams (perhaps you are recording finals played at neutral >>>>>grounds), then your proposition is more like: >>> >>>>>"On Date [Dec-12], [Hope] played [Calvin], with [Hope] scoring [59] >>>>>and [Calvin] scoring [32]" >>> >>>>>The conjunction in that sentence sets alarm bells off in my head, >>>>>because it means I could normalize this proposition to: >>>>>"On Date [Dec-12], [Hope] played [Calvin]" >>>>>"On Date [Dec-12], [Hope] scored [59]" >>>>>"On Date [Dec-12], [Calvin] scored [32]" >>> >>>>>Then I realize I have two different types of propositions, and hence >>>>>two different types of relations. This corresponds to your design
>
> for
>
>>>>>1a, but it highlights the process of how I would have started to get >>>>>there. It also shows that you do not need some sort of artificial ID >>>>>key - the date and the team are enough to serve as identity. >>> >>>>>Now, what is bugging me however is that my first relation would have >>>>>to have attributes team1 and team2, which is entirely
>
> unsatisfactory.
>
>>>>>RM prohibits the repetition of attribute names, even when the
>>>>>attributes are playing identical, equal roles in the relationship. I
>>>>>could solve this by adding a surrogate, to represent the game as a
>>>>>whole, and then normalizing, but this seems worryingly artificial,
>>>>>given in RL a game is identified by the teams and the date. Any
>>>>>illumination from cdt is more than welcome.
>>>
>>>>This is bugging me, too. In particular, it seems to me that the
>>>>proposition:
>>>
>>>> "On Date [Dec-12], [Hope] played [Calvin]"
>>>
>>>>and the proposition:
>>>
>>>>"On Date [Dec-12], [Calvin] played [Hope]"
>>>
>>>>Imply each other. But the way one of them is stated doesn't make that
>>>>"obvious", at least not to me.
>>>
>>>It doesn't seem to be a severe problem; more of an annoyance really.
>>>
>>>Idle thought: if you had RVAs, you could do something like:
>>>
>>>{ date=12-Dec, teamscores={(team=Hope, score=59), (team=Calvin,
>>>score=32)}}
>>>
>>>Marshall
>>
>>I hate the idea of having to invent a surrogate key to identify a
>>game, just so that we can model it in RM (especially given it is
>>perfectly identifiable from the date and teams). Hence using RVA's
>>certainly seems preferable. But then we've added seemingly unnecessary
>>complexity to our queries compared to:
>>{ (date:12-dec, team: Hope, team:Calvin) }
>>{ (date:12-dec, team: Hope, score 59), (date:12-dec, team: Hope, score
>>32) }
>>Someone fix my thinking. quick.
>
> got no quick fix. Sorry. I am going to point out that a game can be
> identified by a date and a SET of teams, not a LIST of teams. Emphasis
> mine. So the old bugaboo about sets versus lists surfaces once again.
>
> Marshall's description, in terms of RVA's obscures the difference between a
> set and a list, because when you lay it out in text, a set looks like a
> list.
>
> Marshall, for your RVA solution, how would the query language handle the
> question,
> "Find all the games that Hope played"?
>
> Also, let's say you already had the game you stated in the database, and
> you tried to insert the following game:
>
> { date=12-Dec, teamscores={(team=Calvin, score=32), (team=Hope,
> score=59)}}
>
> Would the database engine detect that the game was already in the relation?
> If so, how would it do that? I realize my question is a physical one, not a
> logical one, but I'm asking anyway.
It would use the equality operation to detect the duplicate. What's more
disturbing is it would allow the following without complaint:
{ date=12-Dec, teamscores={(team=Calvin, score=31), (team=Hope, score=59) }}