Welcome to Lesson 1 of Module 9 on data modeling problems in completion of an ERD. I'm going to start with a reflective question about the nature of diagram problems versus data modeling problems. How do data modeling problems in Module 9 differ from diagram problems in Modules 6 and 7? Lesson 1 focuses on the application of problem-solving skills covered in Module 8. The problems covered in this lesson provide practice with narrative problem analysis in design transformations. The major objective of this lesson is to work problems to gain confidence with important data modeling skills and continue using a drawing tool such as the ER Assistant. You should be able to analyze simple narrative problem statements and apply design transformations. In working problems, you should focus on consistency with problem statements and learning from mistakes. You should define an ERD for the following narrative. The database should track homes and owners. A home has a unique home identifier, a street address, a city, a state, a zip, and number of bedrooms, number of bathrooms, and square feet. A home is either owner-occupied or rented. An owner has a unique owner number, a unique U.S. Social Security number used for government-reporting requirements, a name, an optional spouse name, a profession, an optional spouse profession, and an optional spouse Social Security number. An owner can possess one or more homes. Each home has only one owner. This ERD shows the solution for Problem 1. Most of the solution follows directly from the problem narrative. The one-to-many relationship from owner to home is indicated by the specifications for an owner can posses one or more homes and a home has only one owner. Note that the minimum cardinalities are not indicated in the problem narrative. Without an indication in the problem narrative, you can inject your own judgment, but you should note that additional requirements collection is necessary for both minimum cardinalities. Here are some other points about the solution. The sentence, a home is either owner-occupied or rented, indicates that the ownoccupied attribute in the home entity type with a data type for two states, true or false. Social Security number should not be used as a primary key, because this is a government identifier. Lesson 2 of Module 8 discussed problems with using government identifiers as primary keys. In Problem 2, you should refine the ERD from Problem 1 by adding an agent entity type. Agents represent owners in the sale of a home. An agent can list many homes, but only one agent can list a home. An agent has a unique agent identifier, a name, an office identifier, and a phone number. When an owner agrees to list a home with an agent, a commission, that is percentage of the sales price, and a selling price are determined. This ERD shows the solution for Problem 2. Most of the solution follows directly from the problem narrative. The one-to-many relationship from agent to home is indicated by the specifications for an agent can list many homes, but only one agent can list a home. Note that the minimum cardinalities are not indicated in the problem narrative. Without an indication in the problem narrative, you can inject your own judgment, but you should note that additional requirements collection is necessary for both minimum cardinalities. The statement, agents represent owners in the sale of a home, is not directly represented in ERD. Agent representation is show indirectly through the lists and owns relationships. For an agent, the list relationship indicates the homes listed, and the owns relationship indicates the owner of each list at home. In Problem 3, an ERD from Problem 2, you should transform the attribute office identifier into an entity type. Data about an office include the phone number, manager name, and address. This ERD shows the solution for Problem 3. The solution involves the attribute expansion transformation presented in Lesson 3 of Module 8. In the solution ERD, officeid is transformed into an entity type, office, in a one-to-many relationship, worksat. Note that the minimum cardinalities are not indicated in the problem narrative. Without an indication in the problem narrative, you can inject your own judgment, but you should note that additional requirements collection is necessary for both minimum cardinalities. Let's wrap up Lesson 1 about data modeling problems. The lesson covered the highlights of three problems involving analysis of narrative problem statements, an application of design transformations. You can see the complete details in the website documents. The major objectives of working these problems are to practice your data modeling skills with a focus on consistency with a narrative problem. In answer to the opening question, these data modeling problems differ from the diagram problems in Modules 6 and 7. Diagram problems contain direct statements about ERD solution such as the entity types required. In contrast, data modeling problems involve business requirements without reference to the ERD components. The diagram problems emphasize the notation of the ERD solution, while the data modeling problems emphasize the translation of business requirements into an ERD. The problems in this lesson contain important omissions on minimal cardinalities that should be noted for additional requirements collection. Lesson 2 continues on the data modeling theme on more complex problem statements.
