Welcome to the fourth module of our course. In the last lesson, we established what a relationship is: the "verb" that describes how two "noun" entities are connected. We saw that a relationship like "a CUSTOMER places an ORDER" is the logical concept that is physically implemented using foreign keys.
Today, we will move from defining relationships to the practical skill of identifying them. Your goal for this lesson is to learn how to read a business scenario—like a project description or a client request—and systematically extract the relationships between the key concepts. This is a crucial step in translating a real-world problem into a structured database design.
1. The Noun-Verb Technique
A straightforward and effective method for identifying entities and relationships is to analyze the language used in a business description. Think of it as a grammatical exercise:
- Nouns often represent the people, places, things, or concepts you need to store data about. These are your candidate entities.
- Verbs often describe the actions or associations between these nouns. These are your candidate relationships.
For example, in the sentence "A Professor teaches a Class," we can immediately spot two potential entities (Professor, Class) and the relationship between them (teaches).
The image below gives a simple, visual example of breaking down a sentence to find entities and the relationships that connect them.
Let's see this technique applied to a more detailed business scenario.
Entity Relationship Diagram and Database Normalization Process
This blog post by Chosen Gambit walks through the design of a "Ticket System." We'll start by looking at how the author extracts nouns and verbs from the initial description.
First, read the Product Description for the "Ticket System" to understand the scenario. Then, move to the Interpreting the Description section and carefully examine Table 1, which lists the verbs and nouns extracted from the text. This table is a perfect illustration of the noun-verb identification technique.
As you can see, this simple method gives you a structured list of potential entities and the relationships that might connect them.
2. From Nouns to Entities: A Filtering Process
Of course, not every noun you identify will become an entity (a table). Some nouns are better suited as attributes (columns) of other entities. For example, Personal Information from the "Ticket System" description isn't an entity itself, but rather a collection of attributes belonging to the User entity.
So, how do you decide? The same resource provides a helpful set of questions to ask about each noun.
Entity Relationship Diagram and Database Normalization Process
Let's continue with the "Ticket System" example to see how the initial list of nouns is filtered.
Read the subsection Entity or Attribute. It provides six key questions to help you determine if a noun should be its own entity. Then, read the Processing Nouns section. Pay close attention to how the author applies this logic to decide which nouns from their list (like users, hardware, tickets) become entities and which ones (like web portal, personal information) do not.
This two-step process—identifying nouns and verbs, then filtering the nouns to find the true entities—forms the foundation for discovering relationships. Once you have your entities, you can use the verbs you found to define how they connect. For example: a User submits a Ticket, and an Employee is assigned to a Ticket.
3. A Worked Example: Library Circulation System
Let's apply this entire process to a new scenario. The following video provides a clear, practical walkthrough using a library book circulation system as a case study. It starts with a set of business rules and uses them to identify entities and their relationships.
Entity Relationship Diagram (ERD) Tutorial and EXAMPLE
This video from The Business Analysis Doctor demonstrates the process of building an ERD from business rules. We will focus on the part where entities and relationships are identified.
First, watch the segment from business rules to entities. The presenter lists several business rules and then uses the "noun technique" to extract five key entities: Reservations, Books, Borrowers, Checkout, and Renewal. Next, watch the segment explaining the relationships between these entities. The presenter systematically describes the connections based on the business rules, such as the relationship between a Book and a Reservation, or a Borrower and a Checkout. Notice how each relationship is justified by a specific rule.
This example reinforces the workflow:
- Analyze the requirements (business rules).
- Identify key nouns to determine your entities.
- Identify the verbs and actions that connect those entities to determine your relationships.
Conclusion
In this lesson, you've learned a practical technique for translating a real-world business scenario into the fundamental components of a database model. By analyzing the language of a description, you can systematically identify candidate entities and the relationships that link them.
Key Takeaways:
- A powerful technique for initial database design is to read a business description and identify the nouns (potential entities/attributes) and verbs (potential relationships).
- Not all nouns become entities. You must filter your list by asking questions to determine if a concept warrants its own table or if it's merely an attribute of another entity.
- Business rules are the primary source of truth for defining relationships. They dictate exactly how entities interact with one another.
We've now covered what relationships are and how to find them. In our next lesson, we will take the next logical step: classifying these relationships. You'll learn to distinguish between one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N) relationships, and how to determine the precise rules (cardinality) that govern them.