Modeling Business Scenarios with Entities and Relationships

Hello! In our last lesson, you mastered the technique of drawing an ERD to resolve a many-to-many relationship using a composite entity. We've now covered all the individual building blocks of a database model: entities, attributes, keys, and the different types of relationships.

Today, we'll shift our focus from assembling pre-defined components to a more realistic task: analyzing a real-world business scenario described in text and extracting these components ourselves. This is a crucial skill for your university project, as it's the very first step in designing a database from a client's requirements. By the end of this lesson, you will be able to extract entities, attributes, and relationships from a multi-paragraph business scenario.

1. A Systematic Approach to Business Requirements

When faced with a page of text describing a business, it can be hard to know where to start. A systematic approach helps turn this unstructured information into the structured components of a database model. Here’s a simple four-step process:

Identify Potential Entities: Read through the business description and look for key nouns. These often represent people (e.g., Student, Customer, Employee), places (e.g., Store, Warehouse), things (e.g., Product, Course), or events (e.g., Sale, Enrollment).
Identify Attributes: For each entity you've identified, look for the descriptive properties or characteristics mentioned in the text. If you have an entity called STUDENT, its attributes might be student_id, first_name, last_name, etc.
Identify Relationships: Look for verbs or phrases that connect your entities. For example, "a DOCTOR writes a PRESCRIPTION" or "a CUSTOMER places an ORDER". These verbs describe the relationships.
Determine Relationship Cardinality: Analyze the "business rules" in the text to understand the nature of each relationship. Look for phrases like "a customer can have many orders" or "an order belongs to only one customer." This helps you determine if the relationship is one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N).

Let's see this process in action.

2. Finding Entities and Relationships: The "Nouns and Verbs" Technique

A great way to start is by literally highlighting or listing the nouns in a business description. This video provides a simple, clear demonstration of this technique.

Entity Relationship Diagrams

This video introduces a simple business scenario and demonstrates how to extract potential entities and relationships from it.

Please watch the first four minutes of the video. First, the video introduces the business scenario about people and pets. Then, pay close attention to how it identifies entities by circling the nouns in the description and then refining that list. Finally, watch how it sketches out the relationships between these entities by thinking about how they are connected.

As you saw, the process involves:

Finding the nouns: Person, Pet, Name, Photo, Address, etc.
Refining the list: Owner is just a Person, so we remove the duplicate.
Identifying relationships: A Person owns a Pet. A Pet has a Name. A Photo depicts a Pet.

These business rules, found in the text, are the source of truth for our diagram. They dictate which entities are connected and how.

Business Rule Example 1: Translating Business Rules to ERD — This diagram shows how textual business rules (left) are directly translated into the visual components of an Entity-Relationship Diagram (right), defining the connections and cardinalities between entities like `PROFESSOR`, `STUDENT`, and `CLASS`.

3. Guided Practice: A Language School Database

Now, let's apply this systematic approach to a more detailed, multi-paragraph scenario. The following article describes the requirements for a database for a language school.

First, I want you to read the scenario and try this yourself.

Designing a Relational Database: The Language School Scenario

This section of the article by Craig Dickson lays out the business requirements for a language school database. Read it carefully to understand the school's operations.

Read the section titled "Defining the requirements". It begins with "The first thing to think about..." and contains a block-quoted text describing the "International Language School". As you read, on a piece of paper, try to: List the main entities (the nouns: people, things, concepts). List the relationships you can find between these entities (the verbs).

How did you do? Now, let's continue with the same article to see how the author breaks down this exact problem.

Designing a Relational Database: Extracting the Components

These sections walk through the process of identifying the entities, their attributes, and the relationships between them based on the scenario you just read.

Now, read the following three sections of the article to check your work and see a detailed breakdown: "Identifying the entities": Compare your list of entities with the author's. Note how the author simplifies "office locations" based on an assumption. "Which attributes do we want to store?": See how attributes are assigned to the Teacher, Client, and Course entities. Notice the author's reasoning for creating an arbitrary primary key (teacher_id) instead of using a name. "Mapping the relationships": This is the most important part. Observe how the business rules ("each course may have only one teacher," "a teacher may teach multiple courses") are used to define the 1-to-N relationships and establish the foreign keys.

This guided exercise shows the full process on a realistic problem: moving from a block of text to a well-defined set of entities, attributes, and relationships, ready to be drawn as an ERD.

4. Another Example: Breaking Down Rules

Sometimes, a business scenario is presented as a list of explicit rules. This can make the extraction process even more straightforward.

The document below tackles a scenario for a pharmaceutical chain. It's a great example of how to dissect a problem one rule at a time.

Solved Problems ERD: The Pharmaceutical Chain

This document presents a business scenario and then breaks it down rule by rule to identify the database components.

Read Problem #1. First, read the main description of the pharmaceutical chain. Then, pay close attention to how the solution analyzes each rule from (a) to (e) individually, identifying the entities and relationship for each one. For example, for rule (a), it identifies PHARMACEUTICAL COMPANY and DRUG as entities and a 1:M relationship between them.

This rule-by-rule approach is very effective for complex scenarios, ensuring you don't miss any requirements.

5. Your Turn: Challenge Scenario

You've seen the process demonstrated and have walked through it with guidance. Now, it's time to test your skills on a new scenario.

Read the business rules for "Pants-R-Us" from the same PDF document. As a self-assessment, try to identify the entities and the relationships between them as described in the rules.

Challenge: Pants-R-Us Scenario

This section presents a new, complex business scenario for an apparel company.

Find the problem statement for "Pants-R-Us". Focus on part (a), which contains rules (1) through (5). Based on these rules, list the entities you can find and the relationships that connect them. For example, rule (1) describes entities Style and SKU and the relationship between them. Don't worry about attributes or drawing the ERD for now. Just focus on identifying the core components from the text.

This exercise is very similar to what you'll need to do for your university project. Getting comfortable with this process of "translating" business language into a database model is the goal.

Conclusion

In this lesson, you've learned a systematic process for analyzing a business scenario to extract the core components of a database model.

Key Takeaways:

A Systematic Process is Key: Following a structured approach (identify entities -> attributes -> relationships) helps manage complexity.
Nouns and Verbs are Your Clues: Entities are often represented by nouns, and relationships by verbs that connect them.
Business Rules Define the Model: The specific rules in the text determine the relationships and their cardinalities (1:1, 1:M, M:N).

We briefly touched upon identifying cardinality today, but in our next lesson, we will dive much deeper. You'll learn how to precisely determine the cardinality and participation (mandatory vs. optional) of a relationship by interpreting the subtle language of business rules. This will be the final analytical skill you need before we start synthesizing everything into complete, detailed ERDs.

Recommended resources