Welcome to Lesson 2 of Module 9 on Data Modeling Problems and completion of an ERD. I'm going to start with a reflective question about the importance of revisions in problem statements. Why should you learn to adapt an ERD to revisions in a problem statement? Lesson 2 continues the coverage of data modeling problems begun in Lesson 1. The problems covered in this lesson involve analysis of more complex narrative problems. The major objective of this lesson is to work more difficult problems to gain confidence with important data modeling skills. You should be able to analyze more complex narrative problem statements and apply design transformations. In working problems, you should focus on consistency with problem statements and learning from mistakes. In Problem 1, the database supports the placement office of a leading graduate school of business. The primary purpose of the placement database is to schedule interviews and facilitate searches by students and companies. The narrative contains details about data for students, companies, interviewers, interviews, and positions. The problem is too detailed to read in entirety, so I will review only major points in the solution. This ERD shows the solution for Problem 1. I will begin with the simple parts and then move to the more difficult parts of the solution. As a problem-solving strategy, I suggest that you start with easy parts to gain confidence and familiarity and then move to the more difficult parts. The student, interview, interviewer, company, and position entity types follow directly from the narrative. The narrative statement clearly indicates attributes and a primary key for each entity type. The many side of the one-to-many relationships attends, conducts, and worksfor follows from the narrative with students attending multiple interviews, interviewers conducting multiple interviews, and companies having a list of interviewers. The one side of the one-to-many relationships follows from interview having interviewer and a student as well as an interviewer working for one company. The minimum cardinalities are not specified in the narrative, so additional requirements collection is necessary. Note that there's no entity type for the placement office. The placement office is the client for the database. If the problem indicated that the database should support multiple placement offices, a placement entity type may be useful. The difficult part of a narrative involves the relationship between company and positions. The problem narrative indicates that multiple companies may interview for the same position, and the company may interview for a list of positions. So a many-many relationship is necessary. The most difficult part of the narrative indicates that a company lists the cities in which positions are available. The solution ERD uses an associative entity type comppos, for company position, in which the primary key is a combination of compid, posid, city, and state. City by itself may not be unique, so state is necessary. To incorporate companies with international employment opportunities, country may also be necessary. If a location entity type is used, a more complex solution is possible. An associative entity type with three one-to-many identifying relationships among location, position, and company would work. location of the tag would have asked us for city, state and possibly country. More requirements collection is needed to determine a most appropriate solution. As a default, the comppos entity type in a solution ERD is preferred because it is simpler. The ERD revision indicates changes requirements, a common occurrence in database development projects. New requirements add details for interview blocks and a bidding process to allocate interviews to students. New requirements also substitute company descriptions of positions instead of usage of standardized position lists. This ERD shows the revised solution for Problem 1. In the revised solution, bid and interviewblock are new entity types, and the position entity type has been eliminated. The position entity type has been eliminated because the problem indicates that a standardized position list is not maintained. A transformation to contract the entity type structure has been made so that comppos has only one identifying relationship. The problem definition indicates a need for the bid in interviewblock entity types. For interview blocks, the attributes must be discerned from the problem statement. To represent blocks of time, the interviewblock entity type has attributes for the start time, end time, and duration for each interview. The primary key, intblockno, is not specified in the problem statement, so additional requirements collection is needed. The narrative provides incomplete details about some new relationships. The narrative indicates that a student can submit many bids and a bid contains one student. However, the minimum cardinality specifications are omitted. For interview blocks, the narrative indicates that companies request interview blocks before related interviews are added. A bid is made for a slot, an interviewblock with a company. The minimum cardinalities in the made-for requests and part of relationships are not specified in the problem statement. The problem statement needs another revision about the bidding process. The current design does not store details about connecting winning bids to interviews. More requirements collection is necessary. The graded problems are similar to the ungraded exercise problems. The graded problems have some basic problems similar to the problems covered in Lesson 1. The graded problems also include a more complex narrative with the revision similar to the complexity of the problem covered in this lesson. Let's wrap up Lesson 2 about more complex data modeling problems. The lesson covered the highlights of a more complex narrative problem containing the revision. You can see the complete details in the website document. The major objectives of this lesson are to extend your data modeling skills and gain confidence on more complex problem statements. An answer to the opening question, revision and problem statements are common on most database development projects. On a large database development project, hundreds of revisions may be necessary to clarify requirements. On some complex parts of problem statements, multiple revisions may be made. A good data modeler expects to deal with revisions to clarify incomplete, inconsistent, and irrelevant problem details. The problem in this lesson demonstrated some complexities in the revision details. Real data modeling situations would have even more complexities.
