ERD Verification Against Business Requirements

1. The Art of Verification: A Framework for Review

Verifying an ERD isn't a random process; it's a systematic check to ensure the diagram is a faithful model of the business reality. We can break this down into a structured review process that covers completeness, correctness, and adherence to design principles.

A good way to approach this is with a validation checklist. The following resource provides a practical framework for reviewing an ERD, which we will adopt for this lesson.

A Systematic Checklist for ERD Verification

Based on the reading and established database design principles, here is a checklist you can use to verify any ERD:

1. Completeness Check:

   • All Entities Included? Compare the nouns in the business description to the entities in the ERD. Has anything been missed?
   • All Attributes Included? Does each entity have the necessary attributes to store the required information? Are there any descriptive details from the scenario that haven't been captured?
   • All Relationships Included? Have all the interactions (verbs) between entities described in the rules been modeled as relationships?

2. Correctness Check:

   • Entities vs. Attributes: Is anything modeled as an entity when it should be a simple attribute (e.g., modeling 'City' as an entity when it's just a property of 'Customer')? Conversely, is something modeled as an attribute when it should be an entity (e.g., storing 'author_name' as a text field in a BOOK entity instead of creating a separate AUTHOR entity)?
   • Relationship Cardinality: Is the cardinality (1:1, 1:M, M:N) for each relationship correct according to the business rules? For example, if the rule says "a customer can have many orders," is the relationship a 1:M?
   • Relationship Participation: Is the participation (mandatory/optional) correct? For example, if an employee must belong to a department, is the participation from EMPLOYEE to DEPARTMENT shown as mandatory?
   • Key Placement: Does every entity have a primary key (PK)? For 1:M relationships, is the foreign key (FK) correctly placed on the "many" side?
   • M:N Relationships: Have all many-to-many relationships been properly resolved with a composite (or bridge) entity for implementation? A direct M:N link is a red flag in a logical or physical ERD.

2. Putting It to Practice: Verifying an ERD

Let's apply this checklist to a practical example. Below is a set of business rules and a corresponding ERD that a junior designer has created. Your task is to act as the senior designer and verify the diagram.

Business Scenario:

A company needs a database to track its sales operations.

• Some salespeople are managers who supervise other salespeople. A salesperson can have one manager.
• Each salesperson is an agent for many customers, but each customer is assigned to only one salesperson.
• A customer can place many orders.
• An order can contain many different products, and a single product can appear on many different orders.
• A product is assembled from many parts. A part can be used in the assembly of many different products.
• A supplier can supply many parts, and a part can be supplied by many different suppliers.
• An employee can be involved in assembling many products, and each product can be assembled by many employees.

Proposed ERD:

Now, examine the diagram below created from this scenario.

Take a few minutes and use our verification checklist to analyze this diagram. Does it fully and correctly model the business rules? Identify any errors or areas for improvement.

3. Understanding Common Modeling Errors

The mistakes found in the previous exercise are very common. It's crucial to learn to spot them. The resource below discusses several classic errors in ER modeling, including confusing entities with attributes and modeling relationships incorrectly.

4. Your Turn: Final Practice

Here is one final practice exercise. The image below contains the same business rules as before, but this time with a different, partially complete ERD drawn using Chen's notation.

Your task is to analyze this diagram against the business rules.

• Does this Chen-style ERD represent the M:N relationships more accurately at a conceptual level than the previous Crow's Foot diagram?
• What is missing from this diagram to make it a complete representation of the business rules? (Hint: look at the relationships and think about cardinality).

Take about 10 minutes to analyze it and formulate your answer.

Conclusion

In this lesson, you transitioned from being a creator of ERDs to a critical reviewer. You learned that verification is a crucial, systematic process that ensures a database design is a true and complete reflection of business requirements.

Key Takeaways:

• Verification is a systematic process: Use a checklist to check for completeness (all entities, attributes, relationships) and correctness (proper modeling of concepts, cardinality, and keys).
• Spotting common errors is key: Be on the lookout for improperly modeled entities/attributes and, most importantly, unresolved M:N relationships in logical/physical diagrams.
• A diagram's purpose matters: A conceptual diagram (like one in Chen's notation) might show M:N relationships directly, but a logical or physical diagram (often in Crow's Foot) must resolve them with composite entities.

You have now completed all the topics related to creating and verifying ERDs. The next series of lessons will take you "inside" the tables you've designed. We will begin with normalization, a formal process for organizing attributes within entities to minimize data redundancy and prevent common data management problems called anomalies. This will add another layer of quality and robustness to your database designs.