1

I'm trying to map this relationship described here:

"Teams play games. A team plays up to 40 games a season. Each game is played by 2 teams. You must keep track of the score for each team in a game. Teams may never play a game, but if a game is scheduled then it must have 2 teams play in it."

The entities here are Team and Game. Both have an ID associated with the entity i.e teamID and gameID. Common sense tells me that Game should be a weak entity because without team there can be no game. However if game has its own unique ID so does this mean it's not a weak entity?

Also how would I create relational table for the entity Game? What would the foreign key in Game be if there is exactly 2 teams that play in a game? Would I just have a teamOneID* and a teamTwoID* in the table?

Deik
  • 134
  • 9

1 Answers1

0

Weak entity sets aren't determined by being dependent on another entity set for existence. Weak entity sets are entity sets that can't be identified by their own attributes, and depend on a relationship with a regular entity set for its identity. If Game has its own surrogate identifier, it's a regular entity set.

The concept of depending on a relationship for existence is called total participation. See my answer to is optionality (mandatory, optional) and participation (total, partial) are same? for more info.

As for naming your foreign key columns, I've seen a number of designs use homeTeamID and awayTeamID, although if both teams play away from their home field, the assignment of roles are arbitrary. Symmetrical relationships can cause difficulties in database designs. Without a rule to break symmetry, your queries would have to check both fields for any given teamID, which can complicate queries and be inefficient. One approach I've used before is to store the lower value in teamOneID and the higher value in teamTwoID.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • 1
    Suppose an entity type, W, has two alternative identifiers (call them keys A and B). Key A consists of attributes that depend on a relationship with another entity. Key B consists of attributes that are not dependent on another entity. Therefore W *can* be identified by its own attributes (B). As far as I'm aware W is termed "weak" if and only if A is deemed to be the "primary" identifier. If B is deemed to be primary then W is not weak. In these circumstances I think the weak-ness distinction is arbitrary and is orthogonal to whether W is identifiable by its own attributes or not. – nvogel Nov 21 '17 at 12:45
  • 1
    @sqlvogel The ER model is mostly only concerned with primary keys, not alternate keys. Besides a couple of sentences about choosing a primary key, everything else in `The Entity-Relationship Model: Toward a Unified View of Data` is focused on primary keys only. From a relational point of view the distinction between weak and regular entity sets looks arbitrary, but for that matter, so do the distinctions between entity sets and value sets, or between attributes and relationships. – reaanb Nov 21 '17 at 16:22