Re: from UNF to 1NF ... newbie question ....

Joske_at_home wrote:
>
> If I have repeating-groups (3 levels deep), how do I seperate them to go to
> the 1NF? I have doubts/questions about assigning the key's.
>
> According the rule:
>
> 1) duplicate the primary key and add a key of the repeating group.
>
> question:
> What is considerate the primary key for the 2e level repeating group?
> Is it the UNF-key + 2e level-key or is it the combined key (UNF-key +
> 1e level key + 2e level-key.)

The default is UNF-key + 1st level key + 2nd level key + ... et cetera, but you have to check if the resulting key is actually a candidate key (i.e., if there is not a strict subset that might also be a key). Usually it is, but if it isn't you have to take some subset that is a candidate key as the primary key.

> Practical problem:
>
> I have a (biljart) match played between 2 teams. (A team consist of 5
> players)
>
> A match is finished when the 5 players played a game against the oposite
> team-player. (5 games)
>
> I need to keep track of the individual scores AND the Team-scores
>
> UNF:
>
> Matchnr
> date
> every match (level1)
> teamname
> every game (level2)
> nr of attemps
> every player (level3)
> playername
> score

Hm. I would expect something more like this: (the stars indicate local key columns)

 { matchnr*,
   date,
   teams { teamname*, players { playername* } }    games { gamenr*, scores { playername*, score } }  }

So a match has

- a match number,
- a date,
- a set of teams (always 2) consisting of

• a team name
• a set of player names (always 5) (I assume the list of players per team may vary per match) - a set of games consisting of
• a game number (I assume that is what you call attempt)
• a set of scores (always 2) consisting of
  • a playername
  • the score of the player in this game

The tables then become: (PK columns are indicated with *'s)

(1st level)
  Match(match-nr*, date)

(2nd level, teams)
  MatchTeam(match-nr*, team-name*)

(3rd level, teams, players)
  MatchTeamPlayer(match-nr*, team-name*, player-name*)

This table subsumes the previous table, so that table can be omitted.

(2nd level, games)
  MatchGame(match-nr*, game-nr*)

(3rd level, games, scores)
  MatchGameScore(match-nr*, game-nr*, player-name*, score)

Also here this table subsumes the previous table. So the resulting tables are:

  Match(match-nr*, date)
  MatchTeamPlayer(match-nr*, team-name*, player-name*)   MatchGameScore(match-nr*, game-nr*, player-name*, score)

Kind regards,

-- 
  Jan Hidders