Re: Bidirectional Binary Self-Joins

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. Received on Fri Mar 30 2007 - 17:23:57 CEST