Welcome to Lesson 1 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 with primary keys cannot be detected by the diagram rules? Module 7 refines your background on ERD notation from Module 6. Module 6 provided a context for database development and covered basic ERD symbols and relationship variations. Module 7 emphasizes precise usage of ERD notation and basic problem-solving skills. The objectives in this lesson involve a focus on rules for complete ERDs. You should be able to apply the completeness rules to avoid obvious omissions such as a missing primary key. As a more reflective goal, you should be able to explain the limitations of diagram rules when analyzing an ERD for errors. Diagram rules serve a purpose similar to syntax rules for computer language. The absence of syntax errors does not mean that a computer program performs its 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 have now ensured that you have 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 the completeness rules that prevent omission of required elements in an ERD. Despite the simplicity of the completeness rules, some students still submit ERDs and assignments that violate completeness rules. The first three completeness rules for primary keys, object names, and cardinalities are mandatory. A finished ERD should not have omissions that violate these rules. The entity participation rule is optional but usually necessary in most parts of even large ERDs. If violated, a warning should be raised. An entity type not participating in at least one relationship should be carefully examined. Without participating in a relationship, the entity type cannot be easily combined with other entity types in queries. An ERD can be inspected to identify completeness rule violations. Note that you do not need to understand the meaning of an ERD to identify diagram rule violations. This ERD has the following violations of completeness rules. The student entity type does not have a primary key, as no attribute is underlined. The relationship between offering and faculty is missing. The supervising relationship is missing the minimum cardinality. If you look carefully, you'll see that the minimum cardinality is missing near the crow's foot symbol. The course entity type does not participate in any relationships. Although the summation may be allowed in some rare situations, a connection is required for the university database. Correcting errors in diagram rules may require an understanding of an ERD's meaning. Typically, business requirements already collected indicate the corrections, so it is just a matter of reviewing the requirements again. For example, business requirements would probably indicate that STD number is the primary key of student. For regular or nonidentifying entity types, identifying a missing primary key is easy. Few students omit primary key specifications for regular entity types. For weak entity types, identifying missing primary keys can involve two entity types, a parent and a child, in a one-to-many relationship. For a weak entity type, with only one one-to-many identifying relationship, the primary key consists of a borrowed primary key from the parent entity type and a local key from the weak entity type. The primary key rule is violated if a local key is missing. In this ERD, the room entity type is missing its primary key. Room borrows from building for part of this primary key because of the identifying contains relationship. The primary key of room is a combination of BldgId from building and another attribute from room. The specification of local key in room is missing, violating the primary key rule. Note that the primary key rule is violated because there's only a single identifying relationship. For associative entity types with more than one identifying relationship, a local key is not necessary as a combination of borrowed primary keys can identify entities in the associative entity type. The naming consistency rules are simple extensions to the completeness rules. Entity type names must be unique within an entire ERD. Attribute names must be unique with an entity type. This rule allows name qualification to make attribute names complete. The fully qualified name of an attribute is the entity type name followed by the attribute name. The attribute name rule is the same in relational databases in which column names are qualified by their table name if a query statement contains an ambiguous column name. The name consistency rules do not require unique relationship names, because participating entities provide a context relationship names. However, it is good practice to use unique relationship names as much as possible to make relationships easy to distinguish. Now, let's wrap up Lesson 1 about completeness rules for ERDs. This lesson has articulated completeness rules for primary keys, object names, cardinalities, and entity type participation in relationships. You should not have difficulty to inspect an ERD for violations of these rules. Typically, business requirements contain corrections because of missing specifications or necessary requirement documents. Now, in answer to the opening question about the limits of the primary key rule, diagram rules provide simple checks for completeness and consistency. Diagram rules serve a purpose similar to syntax rules for computer language. The absence of syntax errors does not mean that a computer program performs its task correctly. Likewise, the absence of notational errors does not mean that an ERD provides an adequate data representation. For primary keys, the completeness rule does not mean that the primary key chosen satisfies business requirements. For example, an ERD may use a government identifier for a primary key although government identifiers may not be reliable for privacy and reliability reasons. Individuals may acquire duplicate government identifiers and others may share the same government identifier due to fraud. Choosing the most appropriate attribute to be the primary key is a design issue, not a design notational issue. Modules 8 and 9 cover design issues for developing business data models, including primary key selection.
