Verbalizing an ER Diagram

A simple verbalization grammar

1. Define each entity: Explain what one instance represents, for example, “A customer is a person or organization that buys CDs.”
2. State its identifier: Explain what distinguishes one instance from another. In a logical model, identify the selected primary key and any other candidate keys.
3. Read each relationship in both directions: Start with one instance, use a meaningful verb, and state both the minimum and maximum number of related instances.
4. Add constraints: Record allowed values, uniqueness rules, lifecycle conditions, and attributes belonging to the relationship.

Replace “relate to” with a business verb such as places, contains, or supplies. “Can have many” alone does not state whether zero is allowed. For Crow’s Foot notation, read the symbols at the opposite end: the markers beside ORDER count orders for one customer. Then reverse the direction and read the markers beside CUSTOMER.

Worked example - Stories on CD (relational shorthand)

The following logical model uses the course’s Stories on CD scenario. It is a compact schema summary, not executable SQL. PK means primary key, FK means foreign key, and CPK/FK marks a column that belongs to a composite primary key and is also a foreign key.

Customers (CustID PK, CustLast, CustFirst, CustStreet, CustCity, CustState, CustZip, CustPhone)
Orders    (OrderNo PK, CustID FK, OrderDate)

LineItems (OrderNo CPK/FK, CDNo CPK/FK, Quantity, SellingPrice, LineCost, Shipped)

CDs          (CDNo PK, CDTitle, DistID FK, RetailPrice, AgeGroup, Description)
Distributors (DistID PK, DistName, DistStreet, DistCity, DistState, DistZip, DistPhone)

Business rules assumed for this example: every order belongs to one customer; every CD has one distributor; and a submitted order contains at least one line item. A customer, CD, or distributor may exist before participating in a sale. The key labels above do not, by themselves, specify all these participation rules.

Now verbalize the same model

• Entities and identifiers
  • A Customer represents a buyer and is identified by CustID.
  • An Order records a customer purchase and is identified by OrderNo.
  • A CD represents a catalog item and is identified by CDNo.
  • A Distributor represents a supplier of catalog items and is identified by DistID.
  • A LineItem records the quantity and selling price of a CD in an order. It is identified by the composite key (OrderNo, CDNo).
• Relationships (read both directions)
  • Each Order must belong to exactly one Customer; each Customer may have zero or many Orders.
  • Each LineItem must reference exactly one Order; each submitted Order must contain one or more LineItems. If the system also stores draft orders, specify separately whether drafts may temporarily contain zero line items.
  • Each LineItem must reference exactly one CD; each CD may appear in zero or many LineItems.
  • Each CD must have exactly one Distributor; each Distributor may supply zero or many CDs.
• Constraints and domains
  • CustPhone is a phone-number domain (format validated per locale).
  • Quantity is a positive whole number. RetailPrice and SellingPrice are non-negative amounts in the example’s agreed currency. SellingPrice records the unit price for that sale and must not change merely because the catalog price changes. Define LineCost = Quantity × SellingPrice as the line amount before any separately modeled taxes or discounts; derive it or enforce consistency if it is stored.
  • For a simple all-or-nothing shipment rule, Shipped can be a yes/no indicator. If a line can be shipped in parts, this field alone is insufficient: record shipment quantities and events in an extended model.

What the key and participation statements imply

The composite key (OrderNo, CDNo) permits each CD only once per order; Quantity records multiple copies. If users need separate lines for the same CD, such as different prices or delivery arrangements, consider a line number unique within an order: (OrderNo, LineNo). Keep CDNo as a foreign key. Agree on the business rule before changing the identifier.

A non-null foreign key from an order to a customer enforces the order’s mandatory customer reference. It does not require a customer to place an order. Likewise, a line-item foreign key prevents an invalid order reference but does not ensure that every order has a line item. The “submitted order has at least one line” rule needs separate enforcement when an order is submitted, with transactions and concurrency handled appropriately.

The one-distributor-per-CD rule is an assumption for this example, not a universal rule. If a CD can be sourced from several distributors, model that many-to-many relationship through an associative entity and verbalize the revised rules.

How to produce verbalizations quickly

1. List entities and keys first (one line each).
2. For every relationship, write two sentences using the four minimum/maximum patterns above.
3. Add only the constraints that change behavior (domains, uniqueness, state rules).
4. Keep statements brief and group them by business area. Retain necessary exceptions rather than imposing an arbitrary page limit.

Relational notation vs. verbalization - when to use each

• Relational notation is compact and great for modelers (naming, keys, and joins).
• Verbalization lets stakeholders review rules without reading symbols.
• Use both: place notation in an appendix; read verbalizations in review meetings.

Naming fields (be consistent)

• Use descriptive names and a consistent convention. This course retains CustID and DistID; alternatives such as customer_id and distributor_id are also clear. Table-name prefixes are a convention, not a relational requirement.
• Use CamelCase or snake_case consistently; avoid spaces in identifiers.
• Avoid non-obvious abbreviations. If you abbreviate, keep it intuitive (CustLast, CustFirst).
• Document genuine candidate keys even when using a surrogate primary key. In the SQL design, enforce their uniqueness and required values with suitable constraints; do not assume every business attribute is unique.

Test the statements with examples

• A customer has no orders: valid under the stated zero-or-more rule.
• An order has no customer: invalid under the mandatory customer rule.
• An order is submitted with no line items: invalid under the submission rule; decide separately whether an empty draft is permitted.
• The same CD appears twice in one order: invalid with the stated composite key. Combine quantities or revise the model if separate lines are required.

Read these cases to users during the review described in Lesson 7. If an expected case contradicts a statement, revise the rule and diagram together, and record who approved the decision.

Quality checks before sign-off

• Every entity has a purpose sentence and an identifier.
• Every relationship has two clear sentences (both directions) with min/max.
• All mandatory/optional rules in text match the ERD symbols.
• Constraints that affect behavior are stated and testable.

The next lesson concludes the module.