For each of the following exercises design a physical data model. The data model should
include table names, column names, datatypes, an indication of whether each column
may be null or must be not null, relationships in crow's foot notation including minimum
and maximum cardinality, and all primary and foreign keys must be properly indicated.
All tables should be in third normal form. Make whatever assumptions you need to, but
document your assumptions.
1) You are working for a consulting company and want to create a very simple database.
This database will be used to track problems clients are having and the consultants who
are working on them. Each client can have many problems and many clients can be
facing the same problems. Each tine a client reports a problem you want to track the
client, what the problem is, who the consultant is who is working on it, the date it was
logged into the system, its status, and the date the problem is solved. Consultants can
work on many problems at any given time. For each type of problem there is one
consultant who is considered the expert on this type of problem. A consultant may be an
expert in many different types of problems.
2) Your daughter plays hockey in a community league. The other parents on your
daughter's team have asked you to put together a small database to track the team's
performance. You need to record who the coach and assistant coach are for each team,
their phone numbers, and the names and phone numbers of all players on each team. You
have to be able to record each game that is played including the date, the location, the
teams, and the score. You also need to track individual player statistics including how
many goals and assists each player gets in each game.
This job designs a physical data model.