Re: Bidirectional Binary Self-Joins

On Mar 31, 3:37 pm, "Marshall" <marshall.spi...@xxxxxxxxx> wrote:
On Mar 31, 2:08 am, mAsterdam <mAster...@xxxxxxxxxxx> wrote:



Marshall wrote:
Bob Badour wrote:
It would use the equality operation to detect the duplicate. What's more
disturbing is it would allow the following without complaint:

{ date=12-Dec, teamscores={(team=Calvin, score=31), (team=Hope, score=59) }}

Whether it complained or not would depend on the keys and other
constraints.
In another subthread I proposed the following key:

(date, π_team(teamscores))

... allowing (using Bob's tag-notation):

{ date=12-Dec, teamscores={(team=Calvin, score=31),
(team=Hope, score=59),
(team=Glory, score=71)
}

}

D'oh!

I believe it was earlier stated that each team could play
only once per day, thus both (12-Dec, Hope) and
(12-Dec, Calvin) must be unique. This is sufficiently
annoying with RVAs that I am now inclined to go back
to the non-RVA model.

(date, team1, team1score, team2, team2score)
unique(date, team1)
unique(date, team2)
check(team1 < team2)

The third constraint's "<" can be any order relation,
and canonicalizes the tuple type.

This is a good tip - I use this sort of constraint a lot, especially
in self-joins to guarantee uniqueness.


Queries like "what games did Hope play in" and
"what teams scored over 40 points" go back to
being trivial.

Unfortunately we are left with that same issue - the choice of team1
and team2 is arbitrary, and as David or Paul pointed out, it means I
have to do some jiggery pokery on my queries to consider both these
different attributes simultaneously, even though they are playing the
same roles. This obviously isn't going to kill us, but it doesn't seem
elegant, so it niggles.

I would actually add a surrogate identifier for the game if I had to
build this db. I'd have a schema for (game_id, team) and two rows
representing all the data content for a fixture, because I can regain
all that info in single proposition simply with a self-join (or just
using a view), but it also allows me to join with other tables without
specifying 'team1' or 'team2'. Of course, then I've invented an
identifier that the original content didn't have solely to achieve my
modeling, which again....well, its not a problem as such, its just one
of those Columbo moments, where the little things niggle.


Any my new response to anyone who doesn't like
the above will be "get over it." :-)

Maybe I need an old raincoat and a cigar. "Just onnne more thing
surrr....". Jim.


Marshall


.

Relevant Pages

  • Re: About wrong winner predicted in the model
    ... India played 20 games with Japan and 5 games with all other countries. ... notices that Team1 is India and predicts, most likely, Japan is the winner ... In each record the most important columns are team1 and team2 for ... and winner that tell us who win this match. ...
    (microsoft.public.sqlserver.datamining)
  • Re: About wrong winner predicted in the model
    ... India played 20 games with Japan and 5 games with all other countries. ... notices that Team1 is India and predicts, most likely, Japan is the winner ... In each record the most important columns are team1 and team2 for ... and winner that tell us who win this match. ...
    (microsoft.public.sqlserver.datamining)
  • About wrong winner predicted in the model
    ... Another problem is how I can tell the computer some columns are for team1, and some other columns are for team2, or just let the computer do the model process. ... Tong ...
    (microsoft.public.sqlserver.datamining)