Welcome to Lesson 2 of Module 7 on ERD Rules and Problem Solving. I'm going to start with an important question about the limits of diagram rules. What kind of errors about identification dependency cannot be detected with the diagram rules? Lesson 2 extends Lesson 1 with coverage of additional diagram rules. Novice data modelers often make errors in parts of an ERD covered by the diagram rules presented in Lesson 2. The objectives in this lesson involve the more difficult parts of an ERD. You want to apply the diagram rules to avoid notational errors and identification dependency. You should apply three diagram rules to check identification dependency in an ERD. An essential difference between an ERD and a relational database diagram is named relationships in an ERD versus foreign keys in a relational database diagram. You should eliminate redundant foreign keys in an ERD by applying the foreign key diagram rule. As a more reflective goal, you should be able to explain the limitations of diagram rules when analyzing an ERD for errors. Although these points were covered in Lesson 1 of Module 7, I want to reinforce them now. Diagram rules serve a purpose similar to syntax rules for a computer language. The absence of syntax errors does not mean that a computer program performs these tasks correctly. Likewise, the absence of notation errors does not mean that an ERD provides an adequate data representation for a business situation. The diagram rules do not ensure that you've considered multiple alternatives, correctly represented user requirements, and properly documented your design. Modules 8 and 9 cover these issues to enhance your data modeling skills. This lesson covers consistency rules that prevent conflicts along different parts of an ERD. Because these rules are more complex than the completeness rules, support by design tool is useful. You can use the ER Assistant to check consistency rule violations after creating the ERD using the ER Assistant. The class website contains a link to obtain the ER Assistant, available without cost. The first two consistency rules ensure correct usage relationships. Relationships can only be used to connect two entity types. In the case of self-referencing relationships, a relationship connects an entity type to itself such as a supervising relationship connecting a ploy to itself. A relationship cannot be used to connect another relationship in an entity type. ERD drawing tools prevent violations in the first two consistency rules. So no checking is normally needed if using a drawing tool such as the ER Assistant. Foreign keys are redundant in an ERD, replaced by named relationships. In a later module, you will learn about conversion rules that replace one-to-many relationships with foreign keys to transform an ERD into a table design. Confusion about foreign keys in an ERD is common, perhaps because relational database diagrams appear similar to ERDs. There is no simple, foolproof way to detect a redundant foreign key. You can use a simple method that often works. You should look for an attribute name matching the name of the primary key in the related parent entity type. In this ERD, FacNo in the offering entity type matches the primary key of FacNo in the faculty entity type. Offering the FacNo is redundant with the teaches relationship in this ERD. The ER Assistant uses a simple rule to check redundant foreign keys, so not all redundant foreign keys can be detected. A number of years ago the ER Assistant was developed to support the notation and diagram rules used in my database textbook. No other database design tool supports the diagram rules taught in this course. The ER Assistant uses this simple method to determine if an ERD contains a redundant foreign key. The ER Assistant checks the child entity type, that is the entity type on the many side of the relationship, for an attribute with the same name and data type as the primary key in the parent entity type, that is the entity type on the one side of the relationship. If the ER Assistant finds an attribute with the same name and data type, a violation is listed in the rules violation report. Let's use the ER Assistant to check for a redundant foreign key in this ERD. I have previously drawn the ERD using the ER Assistant. To check for errors, I use the check diagram button denoted by the check mark. The ER Assistant generates a short report indicating the fact number is redundant in the offering entity type. Identification dependency involving a weak entity type in identifying relationships provide more sources of errors than other parts of the crow's foot notation. In addition, each identifying relationship also requires a minimum and a maximum cardinality of one in the direction from the child, that is a weak entity type, to the parent entity type. Thus, three diagram rules are necessary to check correct structure of identification dependency, the weak entity type rule, the identifying relationship rule, and identification dependency cardinality rule. This ERD demonstrates violations of all three diagram rules for identification dependency. The faculty entity type violates the weak entity type rule. This is a weak entity type, but it is not involved in any identifying relationship. The has relationship violates the identifying relationship rule, as it does not connect a weak entity type. Both offering and course are regular entity types. The cardinality that registers relationship violates the identification dependency cardinality rule. This rule indicates that the minimum and maximum cardinality must both be one, meaning enrollment must be related to one student. This ERD corrects the rule violation shown in the previous ERD. Note that the appropriate resolution typically depends on business requirements. A violation of the weak entity type rule can be resolved by either adding one or more identifying relationships or by changing the weak entity type into a regular entity type. In this ERD, the problem is resolved by making faculty a regular entity type. The more common resolution is to add one or more identifying relationships. A violation of the identifying relationship rule can be resolved by adding a weak entity type or making the relationship non-identifying. In this ERD, the problem is resolved by making a has relationship non-identifying. A violation of the identification dependency cardinality rule can be resolved by changing the weak entity type's cardinality to one, one. Typically, the cardinality of the identifying relationship is reversed. In this ERD the cardinality of the registers relationship should be reversed. That is one, one near student and zero many near enrollment. As you have briefly seen in this lesson, the ER Assistant supports these diagram rules. The relationship consistency rules are supported by diagram construction. Other rules, including the identification dependency rules and the redundant foreign key rule, are supported by the check diagram feature of the ER Assistant. Let's wrap up Lesson 2 about extended diagram rules for ERDs. This lesson has articulated diagram rules for relationships, identification dependency, and redundant foreign keys. The relationship rules are typically enforced by ERD drawing tools, so you do not need to check them. The diagram rules for identification dependency in redundant foreign keys are common sources of errors for novice data modelers. The ER Assistant, a free ERD drawing tool, provides a convenient check diagram tool for checking these more complex rules. Now let's return to the opening question about the limits the diagram rules for detecting errors with identification dependency. The diagram rules provide important error checking on usage of weak entity types, identifying relationships, and relationship cardinalities. I have seen many novice data modelers make errors on these elements of identification dependency. However, diagram rules cannot indicate if identification dependency is appropriate to use in a given business situation. Identification dependency is a specialized concept, so it is not commonly used. Modules 8 and 9 will focus on the appropriateness of various data modeling concepts including identification dependency. After coverage of the notation of ERDs in Module 6, in diagram rules in Lessons 1 and 2 of Module 7, you are now ready for some problem-solving in Lessons 3 and 4. The problem solving is limited to creating ERDs from narrative problems that indicate elements in the ERD as well as checking an ERD for diagram rule violations. Modules 8 and 9 will cover more complex problem-solving using business requirements, not just narratives indicating the elements of an ERD.
