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 . . .