Appendix - Relvars and Database Constraints - Card Game Results Tracking System

 Appendix

Complete Logical Model

A) Relvar Definitions

POINTSYSTEM

Heading: {POINTSYSTEMNAME(STRING)}

Key: {POINTSYSTEMNAME}

Predicate: Point system named §pointsystemname§ can be used for ranking.

TOURNAMENT

Heading: {TOURNAMENTID(INT) TOURNAMENTNAME(NAME) TOURNAMENTSTARTDATE(DATE) TOURNAMENTENDDATE(DATE) POINTSSYTEMNAME(STRING)}

Key: {TOURNAMENTID} {TOURNAMENTNAME}

Predicate: Tournament idendified by §tournamentid§ is titled $tournamentname$, starts/started on §tournamentstartdate§ and ends/ended on §tournamentenddate§ and uses point system §pointsystemname§.

TOURNAMENTROUND

Heading: {TOURNAMENTID(INT) ROUNDID(INT) ROUNDEXPECTEDSTARTDATE(DATE) ROUNDEXPECTEDENDDATE(DATE)}

Key: {TOURNAMENTID, ROUNDID}

Predicate: Round number §roundid§ of tournament §tournamentid§ has expected start date of §roundexpectedstartdate§ and expected end date of §roundexpectedenddate§.

PLAYER

Heading: {PLAYERID(INT) PLAYERNAME(NAME)}

Key: {PLAYERID} {PLAYERNAME}

Predicate: Player idendified by §playerid§ is named §playername§.

MATCHDRAW

Heading: {TOURNAMENTID(INT) ROUNDID(INT) MATCHID(INT) PLAYERID(INT)}

Key: {TOURNAMENTID, ROUNDID, PLAYERID} {MATCHID, PLAYERID}

Predicate: Player §playerid§ is drawn to play match §matchid§ of round number §roundid§ of turnament §tournamentid§.

MATCHTABLE

Heading: {MATCHID (INT) TABLESTRENGTH(INT)}

Key: {MATCHID}

Predicate: Match §matchid§ is played on table with difficulty §tablestrength§.

MATCHSCORE

Heading: {MATCHID(INT) PLAYERID(INT) MATCHSCORE(INT)}

Key: {MATCHID, PLAYERID}

Predicate: Player §playerid§ scored §matchscore§ in match §matchid§.

B) Database Constraints Definitions

Count of players for each match (identified by Match ID) has to be either 3 or 4 (finds tuples which violate the rule).

restrict(summarizeby(matchdraw,(matchid),(noofplayers(count))), noofplayers < 3 or noofplayers > 4)

Match table has to exists for each match of a round or for none of them (finds tuples which violate the rule).

semiminus(

  project(

    join(

      semijoin(

        tournamentround

        ,join(

          project(

            matchdraw

            ,(matchid,roundid,tournamentid))

        ,matchtable))

      ,project(matchdraw,(matchid,roundid,tournamentid)))

  ,(matchid))

  ,matchtable)

The only allowed values for Match Scores are even integers (finds tuples which violate the rule).

not(mod(matchscore,2) = 0)

Sum (Match Score) for each match (identified by Match ID) has to be zero (finds tuples that violate the rule).

restrict(summarizeby(matchscore,(matchid),total(sum(matchscore))), total <> 0)

If a Match Score exists, it has to contain scores for all players who played the match. These players have to be the same as the players in the Match Draw for the same match (finds tuples that violate the rule).

semiminus(

  join(

    matchdraw

      ,semijoin(

        project(matchdraw,(matchid))

        ,project(matchscore,(matchid))))

  ,matchscore)

These rules enforce what's usually referred to as foreign keys.

Tournament round has to refer to an existing tournament.

Match Draw has to refer to an existing tournament round.

Match Draw has to refer to an existing player.
Match Score has to refer to an existing Match Draw.
Match Table has to refer to an existing Match Draw.

semiminus(tournamentround,project(tournament,(tournamentid)))

semiminus(matchdraw,project(tournamentround,(tournamentid, roundid)))

semiminus(matchdraw,project(player,(playerid)))

semiminus(matchscore,project(matchdraw,(matchid, playerid)))

semiminus(matchtable,project(matchdraw,(matchid)))

Logical Model Part IV - Card Game Results Tracking System

Previous

Logical Model - Part IV

Part IV of the logical model discusses design of the relvar match score and the enforcement of the remaining business rules.

The relvar has the same structure as the Match Score entity in the business model.

MATCHSCORE

Heading: {MATCHID(INT) PLAYERID(INT) MATCHSCORE(INT)}

Key: {MATCHID, PLAYERID}

Predicate: Player §playerid§ scored §matchscore§ in match §matchid§.

The last step is to design database constraints for the remaining business rules.

Business Rule 1

The rule: The only allowed values for Match Scores are even integers can be specified in two ways:

• By defining a new data type called EVENINT or
• Using a database constraint

Looking at the requirements and the design so far we can conclude that there is only one attribute in the model representing match score. This does not disqualify creation of a new data type but it limits the benefits we may expect of using the new data type. Database constraint it is:

mod(matchscore,2) = 0

Business Rule 2

The rule: Sum (Match Score) for each match (identified by Match ID) has to be zero, can be specified as the following RA expression (finds the matches that break the rule):

restrict(summarizeby(matchscore,(matchid),total(sum(matchscore))), total <> 0)

Business Rule 4

The rule states: If a Match Score exists, it has to contain scores for all players who played the match. These players have to be the same as the players in the Match Draw for the same match.

Let’s take the bottom-up approach in presenting this expression.

First, we need to find all matches with at least one score:

semijoin(project(matchdraw,(matchid)),project(matchscore,(matchid)); let’s call this expression S.

Then, we need all players in the draw for these matches:

join(matchdraw, S); let’s call this expression A.

The last step is to find any players in A that are not in S:

semiminus(A, S)

The final RA expression is:

semiminus(

  join(

    matchdraw

    ,semijoin(

      project(matchdraw,(matchid))

      ,project(matchscore,(matchid))))

  ,matchscore)

Foreign Keys

So far we haven’t specified any of the foreign keys defined in the business model. Since foreign keys are just a special case of database constraints, we’ll use RA expressions to specify them.

semiminus(tournamentround,project(tournament,(tournamentid)))

semiminus(matchdraw,project(tournamentround,(tournamentid, roundid)))

semiminus(matchdraw,project(player,(playerid)))

semiminus(matchscore,project(matchdraw,(matchid, playerid)))

semiminus(matchtable,project(matchdraw,(matchid)))

All that is left is to calculate the points according to the match scores.

Business Rule 7

The rule is: Match Points are calculated as per the explanations in the business requirements.

The proper design is to create a view that contains the calculation of points. The calculation would need the so-called quota queries.

More to come . . .

Logical Model Part III - Card Game Results Tracking System

Previous

Logical Model - Part III

Part III of the logical model discusses how some of the business rules stated in the requirements will be enforced.

Even before completing specifications for all relvars, we should try to define all database constraints for all business rules that we already can:

Business Rule 5

The rule: One player can only play one match in each round of any tournament, is enforced by the key {tournament id, round id, player id} of the relvar match draw.

Business Rule 3

The rule Count of players for each match (identified by Match ID) has to be either 3 or 4, can be specified as the following relational algebra (RA) expression (finds the matches that break the rule):

restrict(summarizeby(matchdraw,(matchid),(noofplayers(count))), noofplayers < 3 or noofplayers > 4)

Business Rule 6

The rule: Match table has to exists for each match of a round or for none of them, can be specified as the following RA expression (finds the matches that break the rule):

semiminus(

  project(

    join(

      semijoin(

        tournamentround

        ,join(

          project(

            matchdraw

            ,(matchid,roundid,tournamentid))

          ,matchtable))

      ,project(matchdraw,(matchid,roundid,tournamentid)))

    ,(matchid))

  ,matchtable)

Hm, that easy?! Here is the explanation.

We don’t need to be concerned about tournament rounds with no matches in match table. They fall into the second part of the rule (none of the matches has match table defined). So, the first step is to find the rounds with at least one match with match table.

Matches with match tables are:

join(project(matchdraw,(matchid,roundid,tournamentid)),matchtable); let’s call this expression T.

Rounds with at least one match with match table are:

semijoin(tournamentround , T);  let’s call this expression R.

The next step is finding all matches of the selected rounds:

join(R, matchdraw); let’s call this expression A.

Finally, find any matches for rounds with at least one match with match table that are not in match table:

semiminus(A, matchtable)

By substituting back we have:

semiminus(join(semijoin(tournamentround , T), matchdraw), matchtable)

Looks a lot like algebra we’re all familiar with, doesn't it?

Next

Logical Model Part II - Card Game Results Tracking System

Previous

Logical Model - Part II 

Part II of the logical model discusses design of the relvars Match Draw and Match Score.

Here are some of the designs we could use:

1. Since there can only be 3 or 4 players, and this fact cannot change due to the nature of the game, we can have the following attributes of Match Draw/Score: Match ID, Player 1 ID, Player 2 ID, Player 3 ID, where the key is Match ID.
2. Use relation-valued attribute for Match Draw/Score: Match ID, relation(heading (Player ID)). Again, the key is Match ID.
3. Use the same design used in the business model (Match Draw/Score (Match ID, Player ID) where both attributes are a part of the key.

The first one is a really bad design. The only good thing about it is that it enforces the rule that only 3 players can play a match. But there are all kinds of problems with it. What about matches with 4^th players? We’ll have to have another relvar for matches with 4 players (remember, relational model, no nulls). How are we going to summarize scores for one player? We’ll have to union three/four projections on players. The kindest word I can think of to qualify this design is clumsy.

The second design is good for enforcing the rule that match score has to be for the same players that are in the draw. This rule can be enforced by comparing the relation valued attributes of Match Draw and Match Score. However, we’ll have to un-group the attribute in Match Score, project out the score itself, group it back and only then compare. This design is asymmetric. It favors access by match and makes access by player more difficult.

It looks like the best option is to use the same design as in the business model. The first question that comes to mind is: can we combine Match Draw and Match Score in one relvar since they have the same key. The score is an attribute that applies to all matches, it’s just not available at the time the match is drawn. Here are arguments against it:

1. Matches are drawn before played so the score does not exist at that time (again, no nulls)
2. There is no suitable default for missing match scores; any even integer can be a real score so another attribute is required: flag indicating if the match has been played or not
3. All calculations of points would have to exclude matches that haven’t been played

The main argument for combining the two is that the rule that match score has to be for the same players that are in the draw is enforced by the (relvar) structure itself. However, if actual scores are not always separated from artificial scores, the points calculation can lead to incorrect results. In this case, a view (virtual relvar) is called for together with providing users with access to the view and ensuring they don’t have access to the base relvar.

Slowly but surely we’re getting into the territory where designer’s preference will have to decide.

1^st Important Design Decision: Match Draw and Match Score are going to be 2 relvars.

Implications: all business rules will have to be enforced by database constraints

What about Match? Do we need a relvar for it? Let’s assume that both Match and Match Draw relvars exist. The main problem with this design is enforcing the following rule: One player can only play one match in each round of any tournament. Attributes tournament id, round id and player id are in two different relvars (match and match draw). There is no key constraint that can be defined so we’ll have to rely on counting different matches per player per tournament round. Can anything be done about it? What if we remove Match relvar and move it’s attributes to the Match Draw. This design will allow us to enforce the above rule using a key constraint. The two keys of Match Draw would be: {tournamentid, roundid, playerid} and {matchid, playerid}. Is there a problem with it? On the first sight the answer is yes, the Match Draw relvar is not in the 2^nd normal form. Tournament and round are identified by match only. The purpose of normalization is to ensure that there are no update anomalies: updating the same thing multiple times and/or not being able to insert/delete one thing without the other. In our case, updating tournament and/or round of a match would have to be done as many times as there are players in the match. Will we ever update tournament id or round id? No, of course not. How about moving a match from one tournament round to another? There is no point in doing that. Match ID has only one purpose: to link player playing the match. Apart from that, it has no meaning. The only thing that can ever be updated in Match Draw is the set of players playing the match. The design does not exhibit any update anomalies when updating player id.

2^nd Important Design Decision: Match and Match Draw are combined in one relvar.

Implications: What if a user still tries to update one of the following attributes: tournament id, round id and/or match id. Updating match id will be prevented by a foreign key on match score. Updating tournament and/or round id of a match can still be done by updating all tuples of the match (update anomaly due to relvar violating 3^rd normal form).

There is one more attribute of match that has to be converted to the logical model: table strength. Recall that table strength is not used in all point systems. Since each tournament uses exactly one point system, table strengths are not used in all tournaments. If we define table strength as an attribute of the match draw we’ll have an attribute that's inapplicable to all matches. Even if we could find a suitable default value for table strength it would be a bad design to have this attribute in match draw relvar.

3^rd Important Design Decision: Use two relvars for match draws: Match Draw and Match Table

Implications: The rule stating: Match table has to exists for each match of a round or for none of them will have to be enforce using a database constraint.

Finally, we can present the two relvars containing data about match draws.

MATCHDRAW

Heading: {TOURNAMENTID(INT) ROUNDID(INT) MATCHID(INT) PLAYERID(INT)}

Key: {TOURNAMENTID, ROUNDID, PLAYERID} {MATCHID, PLAYERID}

Predicate: Player §playerid§ is drawn to play match §matchid§ of round number §roundid§ of turnament §tournamentid§.

MATCHTABLE

Heading: {MATCHID (INT) TABLESTRENGTH(INT)}

Key: {MATCHID}

Predicate: Match §matchid§ is played on table with difficulty §tablestrength§.

Next

Logical Model Part I - Card Game Results Tracking System

Logical Model - Part I

It is clear that entities:

• Point System
• Tournament
• Tournament Round
• Player

can be modelled the same way they are in the business model. In other words, the above entities map to a relvar each with the same attributes as in the business model. The relvar specifications are presented in the following format:

Relvar Name

Heading: {Set of attributes}

Key: {Set of Attributes}, …

Predicate: A statement that describes the intented meaning of the relvar.

{} - denotes a set

Assume that the data type NAME is available in the RDBMS of choice.

Another assumption is that tournament name can change and, because of that, it’s not suitable for a key.

  

The specifications of the mentioned relvars follows.

POINTSYSTEM

Heading: {POINTSYSTEMNAME(STRING)}

Key: {POINTSYSTEMNAME}

Predicate: Point system named §pointsystemname§ can be used for ranking.

TOURNAMENT

Heading: {TOURNAMENTID(INT) TOURNAMENTNAME(NAME) TOURNAMENTSTARTDATE(DATE) TOURNAMENTENDDATE(DATE) POINTSSYTEMNAME(STRING)}

Key: {TOURNAMENTID} {TOURNAMENTNAME}

Predicate: Tournament idendified by §tournamentid§ is titled $tournamentname$, starts/started on §tournamentstartdate§ and ends/ended on §tournamentenddate§ and uses point system §pointsystemname§.

TOURNAMENTROUND

Heading: {TOURNAMENTID(INT) ROUNDID(INT) ROUNDEXPECTEDSTARTDATE(DATE) ROUNDEXPECTEDENDDATE(DATE)}

Key: {TOURNAMENTID, ROUNDID}

Predicate: Round number §roundid§ of tournament §tournamentid§ has expected start date of §roundexpectedstartdate§ and expected end date of §roundexpectedenddate§.

PLAYER

Heading: {PLAYERID(INT) PLAYERNAME(NAME)}

Key: {PLAYERID} {PLAYERNAME}

Predicate: Player idendified by §playerid§ is named §playername§.

Obviously, the complex part is designing logical model for Match Draw and Match Score. It's presented in Part II of the Logical Model.

Next

Business Model - Card Game Results Tracking System

Business/Conceptual Model

Here is the business model presented using (expectedly) Entity-Relationship diagram.

No surprises in the business model.

Now, to the not-so-easy part, the business rules.

1. The only allowed values for Match Scores are even integers
2. Sum (Match Score) for each match (identified by Match ID) has to be zero
3. Count of players for each match (identified by Match ID) has to be either 3 or 4
4. If a Match Score exists, it has to contain scores for all players who played the match. These players have to be the same as the players in the Match Draw for the same match.
5. One player can only play one match in each round of any tournament
6. Match table has to exists for each match of a round or for none of them
7. Match Points are calculated as per the explanations in the business requirements (BRD).

Requirements - Card Game Results Tracking System

Problem

Design a database to record scores of a various competitions of a card game and to calculate points and player standings based on the scores.

Business Requirements

The smallest unit of competition is called match.

Each match play 3 or 4 players.

There could be multiple competitions happening at the same time. The competitions are called tournaments.

Tournaments are time limited events. A tournament usually lasts a year but it can be shorter or longer.

Tournaments are organized in rounds. For each round a schedule is prepared. The schedule contains:

• Time interval for playing the round.
• Matches to be played. For each match the set of players is defined.
• Each player can only play one match of each round of a tournament.

For some tournaments the rounds are defined at the beginning of the tournament, for some, the rounds are defined at the beginning of each round.

The system has to support entering schedules independently of the results. However, the results can only be provided for the matches in schedule.

At the end of the match the score (or result) is calculated for each player. Each player's score has to be even number. Sum of all players’ scores of a match has to be zero. The winner is the player with the greatest (positive) score.

Each player earns point based on his/her result in a match. Total of points in a tournament determines the player’s standing in the tournament.  The player with the most points wins.

In order to explain how points are assigned let's first talk about well-known example.

In football (soccer, for the minority), the score is: Tottenham - Arsenal 2:0 (no pun intended).

In one point system, the winner gets 3 points, the looser 0 and in case of draw, both teams get 1 point. The actual scores are kept to decide in case of point ties. In the example, Tottenham gets 3 points and Arsenal 0.

However, in 1970s and before, the winner used to get only 2 points. In the example above, Tottenham would’ve got 2 points and Arsenal 0. It was a different point system.

In both systems the actual scores (or goal differential) are used to break ties – cases when multiple teams have the same number of points.

In our case, different tournaments can have different point systems.  What is always the same is the way match score is presented: set of even numbers, one number per player, totaling zero.

Since the total match score has to be zero, the distribution of individual player scores can be:

1. all scored 0
2. some scored positive and some negative

Zero is treated as a positive score.

All point systems have the following characteristics:

• If two scores are equal then the corresponding points must be equal as well.
• If one score is greater than the other than the points for one score are greater than the points for the other
• We're now going to describe two point systems to be supported.

Point System 1

Matches are played by 3 players only (never 4).

The points assigned depend only on placement of players in a match.

The points are assigned using one of:

1. 4, 1, 0
2. 3, 2, 0

If the winner has the only positive score, the points are assigned as per 1.

If two players have positive scores, the points are assigned as per 2.

If two or three players have the same score they'll all get points for the next available place. So, if two or three players share the first place, they all get 3 points, if two players share the second or third place they both get 1 point.

To recap, the actual point for any match can only be:

4, 1, 0 (different scores, 2 negative)

4, 1, 1 (2 same negative scores)

3, 3, 3 (all scored 0)

3, 2, 0 (different scores, 2 positive)

3, 3, 0 (2 same positive scores)

Point System 2

Matches are played by 3 or 4 players.

The points assigned depend on:

1.   placement (rank) of players in a match
2.   strength of the table (opponents)
3.   actual score achieved

Let's first define the calculation of points for 2 and 3.

Each match is assigned a number, to be used as points, called table strength, representing the degree of difficulty of the opponents. This number is assigned when the round is defined (drawn) and does not depend on the result of the match. The table strength is the number between 1 and the number of matches in the round. 1 is assigned to the weakest table.

The points based on achieved score are calculated as the score divided by 10 and rounded to the nearest integer (0.5 rounds to 1). This number of points is limited to between  -50 and 50. So, a player can actually lose up to 50 points for a score <= -495 or win up to 50 points for a score >= 495.

If three players play a match, the points for placement are assigned using one of:

1. 40, 10, 0
2. 30, 20, 0

If the winner has the only positive score, the points are assigned as per 1.

If two players have positive scores, the points are assigned as per 2.

If two or more players have the same score they'll both get points equal to the average of points available for the places they won rounded to the nearest integer. So, if 3 players share the first place, they all get 17 points, if two players share the first place they'll get 25 points, etc.

To recap, the actual point for any match played by 3 players can only be:

40, 10,  0 (different scores, 2 negative)

40,  5,  5 (2 same negative scores)

17, 17, 17 (all scored 0)

30, 20,  0 (different scores, 2 positive)

25, 25,  0 (2 same positive scores)

If four players play a match, the points for placement are assigned as follows: 35, 25, 10, 0.

If two or more players have the same score they'll both get points equal to the average of points available for the places they won rounded to the nearest integer.

To recap, the actual point for any match played by 4 players can only be:

35, 25, 10,  0 (different scores)

18, 18, 18, 18 (all scored 0)

23, 23, 23,  0 (first 3 scored the same)

35, 12, 12, 12 (last 3 scored the same)

30, 30,  5,  5 (first 2 scored the same and last 2 scored the same)

30, 30, 10,  0 (first 2 scored the same and last 2 have different scores)

35, 18, 18,  0 (second and third scored the same and first and last have different scores)

35, 25,  5,  5 (last 2 scored the same and first two have different scores)