Re: Bidirectional Binary Self-Joins

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 Received on Fri Mar 30 2007 - 16:44:05 CEST