Re: Bidirectional Binary Self-Joins

On Mar 30, 7:40 am, "David Cressey" <cresse...@xxxxxxxxxxx> wrote:
"JOG" <j...@xxxxxxxxxxxxx> wrote in message

news:1175256984.344299.77790@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





On Mar 30, 3:41 am, "Kevin Kirkpatrick" <kvnkrkpt...@xxxxxxxxx> 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

.

Relevant Pages

  • Re: The next 20 games
    ... Suffice it to say that the Red Sox have a significantly easier ... it's important for the Sox to have about a 7 game lead heading into ... some reason, tends to play them tough), and 3 against the Red Sox. ... The RS were constructed to beat NYY, ...
    (alt.sports.baseball.bos-redsox)
  • Re: Bidirectional Binary Self-Joins
    ... Game ... suffice to say, that way leads madness.... ... because it means I could normalize this proposition to: ...
    (comp.databases.theory)
  • Re: Bidirectional Binary Self-Joins
    ... Game ... suffice to say, that way leads madness.... ... to have attributes team1 and team2, ... repeating group, cleverly disguised. ...
    (comp.databases.theory)
  • Re: Bidirectional Binary Self-Joins
    ... Game ... suffice to say, that way leads madness.... ... to have attributes team1 and team2, ...
    (comp.databases.theory)
  • Re: Closure
    ... Closure is both a vivid meditation on sickness and the mental states it produces *and* an inventive 2d platform puzzle. ... It's all about illumination. ... If a surface isn't lit, then for the purposes of the game it doesn't exist; the puzzles are thus created from the strategic arrangement of orbs of light around the environment, each glowing a finite radius. ... What's remarkable is that Closure manages to sustain its 'gameness' whilst illustrating as much as any title yet the potential that the form has to express something more interesting. ...
    (uk.people.support.depression)