Question

(From the book Database Systems 10th editions by Coronel pg.142-143)
The local city youth league needs a database system to help track children who sign up to play soccer. Data need to be kept on each team and the children who will beplaying on each team and their parents. Also, data need to be kept on the coaches for each team. Draw the data model described below:
Entities required: Team, Player, Coach, and Parent
Attributes required:
- Team:
Team ID number, Team name, and Team colors
- Player:
Player ID number, Player first name, and Player last name
- Coach:
Coach ID number, Coach first name, Coach last name, and Coach home phone number
- Parent:
Parent ID number, Parent last name, Parent first name, Home phone number, and Home address (street, city, state, and zip code)

The following relationships must be defined:
- Team is related to Player
- Team is related to Coach
- Player is related to Parent


Connectivities and participants are also defined as follows:
- A Team may or may not have a Player
- A Player must have a Team
- A Team may have many Players
- A Player has only one Team
- A Team may or may not have a Coach
- A Coach must have a Team
- A Team may have many Coaches
- A Coach has only one Team
- A Player must have a Parent
- A Parent must have a Player
- A Player may have many Parents
- A Parent may have many Players

Answer 1

Crow’s Foot Notation ERD:

In this notation entities are represented in boxes and relationships are represented using line. There are many different shapes are used at the end of the line which represent the cardinality of the relationship.

Cardinality:

It refers to the uniqueness of the data value contained in the particular column of table. When cardinality is lower, then there will be more duplicate elements in the column.

• Cardinality (0, N) represents zero or many relationships.

• Cardinality (1, N) represents one or many relationships.

• Cardinality (1, 1) represents one and only one relationship.

• Cardinality (0, 1) represents zero and one relationship.

According to the given specifications the data model can be drawn as follow:

ERD with foreign keys:

Explanation:

In above diagram, there is an curious fact about the Team_Colors attribute that if teams have more than one color as it is implied “colors” in plural form.

Consider three cases:

• if Team_Colors is a single-valued attribute,

• create more than one attributes within the TEAM entity, or

• create a separate COLOR table.

• The attribute Team_Colors can be left with one attribute as user is not concerned with dealing with the more than one color individually.

• For example, user is not interested in knowing that how many teams have the same color Black, then in this case the above diagram can be implemented.

• In second case, if there are some possibilities that in future users may become interested for assigning the different colors for a given team seperately, then it must be modified the above diagram to fullfill this need.

• If it is confirmed that all teams have the exactly two team colors for now and forever, then modify the design by adding additional attributes in the TEAM entity.

ERD with two Team_Color attribute:

• The above model is a kind of limited solution because it will work only if teams have exactly two team colors but problem arises if some teams have more than two colors.

• In this case, it will have to modify the data model of the database by designing with a separate table named COLOR to handle the multi-valued Colors OF Team attribute.

ERD with separate table COLOR: