Re: Bidirectional Binary Self-Joins

On Mar 30, 7:40 am, "David Cressey" <cresse..._at_verizon.net> wrote:
> "JOG" <j..._at_cs.nott.ac.uk> wrote in message
>
> news:1175256984.344299.77790_at_l77g2000hsb.googlegroups.com...
>
>
>
>
>
> > On Mar 30, 3:41 am, "Kevin Kirkpatrick" <kvnkrkpt..._at_gmail.com> wrote:
> > >[snip]
> > > Finally, JOG - I toyed around with the notion of:
> > > Game {Date, Team, Score, Team, Score}
> > > suffice to say, that way leads madness....
>
> > I would never propose such a schema, so I hope you are not suggesting
> > I did - with no connection between Score and Team other than attribute
> > order (which is totally against the principles of RM), such a
> > structure is useless. , I always find it easiest to go back to the
> > propositions you want to record. If every proposition was of the
> > nature:
>
> > "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.
>
> The second thing that bothers me is this: if you had two columns, namely
> team1 and team2, and you wanted to make a list of all the games hope played
> in, you would have to lookup in two columns. This is alarmingly like the
> reason first advanced to me, way back in about 1983, for conforming to 1NF.
> If you have to look in two places to find one fact, there's somethnig
> wrong.
>
> In fact, it almost seems as though the two columns "team1" and "team2" are a
> repeating group, cleverly disguised.- Hide quoted text -
>
> - Show quoted text -

Not sure if this link is particularly illuminating, but a similar problem (using soccer instead of basketball) is used by Darwen as an example in this exchange on POOD:
http://www.dbdebunk.com/page/page/3010532.htm

TroyK Received on Fri Mar 30 2007 - 21:07:02 CEST