Database systems · Chapter 2 · Worked example
Two relations, one relationship, and every symbol on the line doing a job. This is the smallest relational diagram that still has something to teach, and the user sees both relations as plain tables.
One verb phrase, two sentences. Each end symbol describes the entity it touches, not the one across the line.
Each CUSTOMER is billed on zero or many INVOICEs.
Invoice end · cardinality (0,M) · optional participation
Each INVOICE is billed to exactly one CUSTOMER.
Customer end · cardinality (1,1) · mandatory participation
A customer who was registered this morning and has bought nothing yet still satisfies the diagram. An invoice with no customer does not. It cannot exist, and it cannot be split across two of them.
Look only at what touches the boxes. A bar at one end, a crow’s foot at the other.
That single fact is what makes this design implementable as it stands. The customer_id column moves into the invoice table as a foreign key, and the relationship is finished. No bridge entity is needed here, because none is ever needed for one to many.
Had both ends carried crow’s feet, the diagram would still be a correct description of the business, but it would not be something you could build in a relational database. That is the moment a bridge entity has to be introduced.
The minimums played no part in any of this. Changing the circle to a bar would make participation mandatory without changing the connectivity, the foreign key, or the table structure by one character.
Once the foreign key is in place, the drawing stops describing entities in the abstract and starts describing tables you could query.
A relational diagram is a visual representation of the tables in a relational database together with the primary and foreign keys that link them. That is exactly what is above: two named boxes, their columns listed, the keys marked, and one line joining invoice.customer_id back to customer.customer_id.
The two artifacts overlap heavily for a design this simple, which is why one drawing serves both purposes here. They are not the same thing in general. An entity relationship diagram can legitimately show a many to many relationship; a relational diagram cannot, because there is no such table structure to draw. The moment a design contains an M:N relationship, the ERD and the relational diagram stop looking alike until a bridge entity is added.
In the relational model a relation is defined mathematically as a set of tuples: unordered, with no duplicates. Nobody works that way. The RDBMS presents each relation to the user as a table of rows and columns, and that tabular perception is the whole point of Codd’s 1970 proposal: the user reasons about a simple grid while the software handles the physical storage underneath.
This is why the logical simplicity of the relational model was such a departure. The hierarchical and network models required the user to know the structure in order to navigate it. A table requires only that you know the column names.
| Relational model | What the user sees | File system |
|---|---|---|
| Relation | Table | File |
| Tuple | Row | Record |
| Attribute | Column | Field |
The middle column is the one that matters in practice, and it is the only one most people ever use out loud. The left column is what the terms mean formally, and it is worth knowing because it explains the behaviour: rows come back in no guaranteed order unless you ask for one, because a relation is a set and sets have no order.
Filled with data, the perception is immediate. These are the tables a user would see.
| customer_id | first_name | last_name | phone |
|---|---|---|---|
| 1001 | Marisol | Reyes | 209-555-0142 |
| 1002 | Dana | Whitfield | 209-555-0198 |
| 1003 | Amit | Chandra | 209-555-0177 |
| invoice_id | customer_id | invoice_date | invoice_total |
|---|---|---|---|
| 5001 | 1001 | 2026-03-04 | 899.00 |
| 5002 | 1003 | 2026-03-04 | 42.75 |
| 5003 | 1001 | 2026-03-11 | 129.50 |
| 5004 | 1003 | 2026-03-19 | 1099.00 |
Everything the diagram claimed is visible in the data. Customer 1001 appears on two invoices and customer 1003 on two, which is the crow’s foot. Customer 1002 appears on none, which is the circle. Every value in the customer_id column of INVOICE matches a row in CUSTOMER, which is the foreign key doing its work, and an attempt to insert an invoice for customer 1009 would simply be rejected.
The circle on the invoice end is a business decision, not a drafting detail.
A mandatory reading, where every customer must have at least one invoice, would mean you cannot register a customer until they have bought something. That is wrong for a phone store, where people open an account, port a number, or join a family plan before any money changes hands.
Drawn as mandatory, the model would force staff to invent a zero value invoice just to create a customer record. The circle prevents that.
This is the general shape of the work. The connectivity usually follows straightforwardly from how the business counts things. The participation almost always requires asking someone what actually happens on the shop floor, and getting it wrong produces a database that people fight rather than use.
What the rectangles would say if there were room to spell it out. Attributes on the diagram; columns once the user is looking at the table.
| Attribute | Type | Rule it enforces |
|---|---|---|
| customer_id PK | INT | Uniquely identifies one customer |
| first_name | VARCHAR(30) | Optional |
| last_name | VARCHAR(30) | Required |
| phone | VARCHAR(15) | Required, and no two customers may share one |
| Attribute | Type | Rule it enforces |
|---|---|---|
| invoice_id PK | INT | Uniquely identifies one invoice |
| customer_id FK | INT | Required. This is the (1,1) end of the relationship |
| invoice_date | DATE | Required |
| invoice_total | DECIMAL(9,2) | Required, and may not be negative |
The foreign key is the only attribute that exists because of the relationship rather than because of the entity. Every other column would be there even if the two tables never touched.