Re: Bidirectional Binary Self-Joins
Date: Fri, 30 Mar 2007 16:55:31 GMT
Message-ID: <7ibPh.15$pI1.0_at_trndny07>
"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?