Assignment:
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.
Provide complete and step by step solution for the question and show calculations and use formulas.