Olympic Games ER Diagram Critique

 

            This ER diagram has no problem statement to go along with it, so I will have to guess as to what the designer was after. It appears that the database designer was out to model a database that would track a single Olympic games from the standpoint of athletes competing in various events. If I had access to the designer, I would ask if the following assumptions are correct. Athletes are members of teams and have coaches. By competing in the events the athletes eventually place in that event, with the top three receiving a bronze, silver, and gold medal. If this is indeed a short synopsis of the statement, then the given ER diagram falls pretty far from the mark. One problem with this diagram is that there are no labels on the relationships between the entities. Because of this, it is difficult to tell if a relationship is valid.  Another problem with this diagram is that there are several tables that should not be tables at all, rather they should have been included as attributes of a many-to-many relationship, placed in a new entity all together, or as the result of a query. Still another problem is that several attributes that are not foreign keys are repeated in some entities. An example of this is the fname and lname in both the athletet and gamerres tables. This will only lead to having update, delete, and modify anomalies.

The designer has several entities that should not be there at all, one example being the goldt, silvert, and bronzet. These should be attributes of a many-to-many relationship between the athlete and event table, with the relationship called “compete in”. Perhaps included in this relationship should be the attributes of the particitit and particitt tables. The ratet should not be there at all, but should be a query on the database that counts the number of gold, silver, and bronze medals that each country has won. I can’t make a judgement on what the gamesres table is suppose to capture and would eliminate it entirely as it seems redundant to the athletet table. I would probably also introduce a team entity and have both the athletes and coaches get the country attribute from there via a one-to-many relationship. These are just a few recommendations I would make, I’m sure that more changes would be made if I were to actually go and model the situation.