Requirements Analysis

Finding Attributes and Relationships

Entities are the cast; attributes are what they know and relationships are how they are connected. This lesson shows how to extract both from a requirements text without encoding facts into strings, inventing domains, or turning every verb into a table.

Beginner

Learning objectives

  • Describe an attribute by name, domain, optionality and unit
  • Distinguish simple, composite, multivalued and derived attributes and decide which to model
  • Detect facts hidden inside coded strings and split them
  • Extract candidate relationships from verbs and from shared references
  • Name a relationship so that it reads correctly in both directions
  • Decide when an association must become an entity with its own identity

Prerequisites

  • Finding Candidate Entities
  • Analysing the Application Domain

Introduction

The previous lesson produced the cast: Book, Copy, Member, Loan, Branch, Author. That list answers one question — who is in the story — and leaves two others open.

What does each of them know? Those are the attributes. And how are they connected? Those are the relationships.

Both questions look easy and both hide the same trap. The trap is that a requirements text is written in natural language, and natural language compresses facts into convenient lumps. "Address" is one word and seven facts. "Member M-2024-0031" is one token and three facts. "Borrows" is one verb and, in a library, a whole entity with dates, states and fines.

This lesson is about unpacking those lumps deliberately, before they harden into a schema where the date of a loan can only be recovered by parsing a string.

The anatomy of an attribute

An attribute is not a column name. It is a claim about the domain, and a complete claim has four parts.

Attribute

An attribute is a property of an entity or of a relationship that describes all its instances in the same way and takes its values from a defined domain.

A complete attribute specification states:

  1. Name — what the property is called, in the domain's language.
  2. Domain — the set of admissible values, with type and constraints.
  3. Optionality — whether every instance must have a value, or may lack one.
  4. Unit or format — the measurement unit, currency, precision, or textual pattern.

Most beginner schemas specify only the first. That is why they fail on contact with real data: a weight with no unit is ambiguous between kilograms and pounds; a price with no currency is unusable the day the organisation sells abroad; a date with no precision cannot distinguish a birth date from a timestamp.

A fully specified attribute reads like a small contract:

loan_period_days — positive integer, 1 to 365, mandatory, default 30, unit: days. The maximum number of days a copy may be kept before the loan is overdue.

Compare with the version that usually appears in a draft: loan period. The second is a wish; the first is a specification.

The four kinds of attribute

The classification matters because each kind has a different modelling rule.

Kind Definition Example Modelling rule
Simple Atomic: no meaningful parts card_number, weight Model directly
Composite Has meaningful parts that are used separately address = street + city + postal code Model the parts, not the whole
Multivalued Several values for the same instance phone_number, email Model as a separate entity or a repeating structure, never as one field
Derived Computable from other data age, total_loans, available Compute on demand; store only with a documented reason

Composite attributes

The rule is simple and constantly broken: if a part is ever used on its own, the part is an attribute in its own right.

A library that stores address as a single string can print a mailing label. It cannot:

  • list members by city, because the city is inside a string;
  • validate that the postal code belongs to the city;
  • apply a different shipping cost per region;
  • correct "Milano" to "Milan" across 12,000 records without a text search.

The moment any of those requirements appears, the composite must be exploded into street, city, postal_code. And since requirements grow, the professional default is to explode it from the start — the cost is three columns, the cost of not doing it is a migration plus a data-cleaning project.

Multivalued attributes

A member with three phone numbers is the standard case. The tempting design is one field holding "3331234567; 0212345678". It fails for reasons that are worth memorising, because they recur for every list stuffed into a field:

  • you cannot index the third number;
  • you cannot enforce that a number is well formed;
  • you cannot count how many members have a mobile number;
  • deleting one number requires string surgery;
  • the separator eventually appears inside a value.

The conceptual model has two legitimate representations, and the choice between them is a modelling decision:

Representation When to use it What it looks like
Multivalued attribute The values are simple, have no attributes of their own, and are not shared Member.phone with multiplicity many
Separate entity The values have their own attributes, or are shared, or are reported on PhoneNumber with type, verified_on, linked to Member

phone_number is usually the first. But the moment the library wants "which number is the mobile, and when was it verified?", it becomes the second. This is the entity-versus-attribute test of the previous lesson, applied one level down.

Derived attributes

An attribute is derived when its value is a function of other data: age from birth_date, total_loans from the loan history, available from the active loans, days_overdue from the due date and today.

The rule: do not store derived attributes unless you can state why.

The reason is not storage cost. It is that a stored derived value is a second source of truth, and two sources of truth always diverge — the day someone updates the birth date through a path that forgets to recompute the age, the record becomes internally inconsistent and nothing in the schema prevents it.

There are legitimate reasons to store one anyway:

  • Performance, when recomputation is too slow for a required response time. Then it is a materialised value, and it needs a documented refresh rule.
  • History, when the derived value at a past moment is itself a fact. The price of an order line is computed from the catalogue price, but the price at the time of sale must be stored, because the catalogue price will change and the invoice must not.

That second reason is worth dwelling on, because it is not really a derived attribute at all — it is a snapshot, a fact about the past that happens to have been computed once. The distinction between "recomputable" and "frozen at a moment" is one of the most useful in database design.

Attributes with a hidden structure

Two patterns account for most of the "why is this schema unqueryable?" complaints.

Coded strings. A field like "M-2024-0031" looks like an identifier and is actually four facts: a type (M), a year (2024), a sequence (0031), and a formatting convention (the hyphens). Every one of them will eventually be needed: "how many memberships were created in 2024?", "what is the next sequence number?". The moment a query must call SUBSTRING, the encoding has become a schema defect.

The fix is not to remove the code — users like readable codes — but to store the parts and, if necessary, generate the code from them.

Overloaded fields. A field notes that holds "returned damaged, fine waived by the librarian, 12/03" is three facts, a decision and a date in one cell. It cannot be reported on, it cannot be audited, and it will be inconsistent within a month.

The test is the same in both cases: can a requirement be answered without parsing the value? If not, the value is a compressed record and must be decompressed.

Domains: the half that gets forgotten

An attribute without a domain is a column name with good intentions. Domains come in four practical shapes.

Shape Description Example What it prevents
Enumerated A closed list of values condition ∈ {new, good, worn, damaged} Free text spelling variants
Ranged A type plus bounds loan_period_days ∈ ℤ, 1–365 Zero-day and negative loans
Patterned A textual format ISBN-13, postal code, email Structurally impossible values
Reference The set of instances of another entity branch_id must exist in Branch Dangling references

The fourth is the one that becomes a foreign key later, and it is worth noticing now: a domain that is "the set of existing branches" is a statement that the attribute is a reference, and a reference is what turns two entities into a relationship. Attributes and relationships are two views of the same fact.

Optionality deserves its own moment, because it is where ambiguity hides.

Relationships: definition and degree

Relationship (association)

A relationship is an association between two or more entities that has meaning for the application domain. Each occurrence of a relationship links one instance of each participating entity.

Member borrows Copy is a relationship. One occurrence links Anna with copy 2 of The Name of the Rose. The relationship is a class, just like the entity: it describes the kind of link, not the single link.

The degree of a relationship is the number of entities it connects.

Degree Name Example Note
2 Binary Member — Copy (borrows) By far the most common; almost every real model is built from these
1 Unary / recursive Employee — Employee (supervises) The same entity plays two roles; needs role names
3 Ternary Supplier — Part — Project (supplies) Rare, and often misused as a shortcut for three binary links
n n-ary — Legitimate but exceptional; treat with suspicion

Ternary relationships are where many models go wrong, and the reason is subtle: a ternary is not the same as three binaries. Supplier supplies Part to Project records that this supplier supplied this part to this project. Three binary links record that the supplier supplies the part somewhere, the part is used by the project somewhere, and the supplier works with the project somewhere — which is compatible with a state of affairs where the part came from a different supplier entirely.

The practical guidance is: model ternaries when the fact genuinely has three participants and cannot be decomposed; otherwise decompose. Module 3 returns to this with the full rules.

A relationship has two endpoints. Each endpoint is a role, and the role name says what the entity is doing in that relationship.

For most binary relationships the role is obvious and no name is needed: in Member borrows Copy, the member is the borrower and the copy is the borrowed item. Nothing is gained by writing it.

Role names become mandatory in three situations:

Situation Example Why names are needed
Recursive relationship Employee supervises Employee Which side is the boss? supervisor / subordinate
Two relationships between the same entities Member reserves Copy and Member borrows Copy The two links must be told apart, and read correctly
Ambiguous direction Person is married to Person Symmetric: both roles are the same, which is itself information

The third case hides a design question. If the relationship is symmetric, storing it as two rows (A→B and B→A) duplicates the fact and invites inconsistency. One row plus a rule that reads it in both directions is correct — a decision to make explicitly, not by accident.

Naming a relationship so it reads

The naming test is mechanical: insert the entity names into the relationship name and check that the sentence is true in both directions.

Member [borrows] Copy → "a member borrows a copy" ✓ and "a copy is borrowed by a member" ✓. The name works.

Member [loan] Copy → "a member loan a copy" ✗. It is a noun, not a verb; it will be misread as an entity, and misreading it as an entity is exactly the error the next section is about.

Copy [belongs] Member → "a copy belongs a member" ✗. Wrong preposition; the name encodes a direction that the reading does not support.

Two rules follow: use a verb phrase in the active voice, and make the name read correctly when the entities are swapped. A relationship name that only works in one direction will be drawn backwards by half the people who read the diagram.

Extracting relationships from a text

There are three reliable signals. Use all three; each catches what the others miss.

Signal 1 — verbs. "A member borrows a copy", "a librarian records a loan", "a copy belongs to a branch". Underline every verb whose subject and object are both candidate entities. Verbs whose object is not an entity ("the system calculates the fine") do not produce relationships; they produce rules.

Signal 2 — shared references. "Each copy has a shelf location", "each loan is made at a branch". A reference from one entity to another is a relationship even when no verb appears.

Signal 3 — the "each ... one/many" phrasing. "Each member belongs to exactly one branch", "a book may have many copies". This phrasing is gold, because it states the cardinalities directly. Collect these sentences verbatim; they will be the raw material for the cardinality lesson.

Association or entity?

This is the decision that separates a workable model from a painful one, and it is the one students most often get wrong in both directions.

The rule of thumb "an association with attributes is still an association" is correct but incomplete, because it says nothing about which attributes.

The association-versus-entity test

Keep a link as an association when it is nothing more than the fact that two things are related.

Promote it to an entity when it acquires a life of its own, that is, when at least one of these holds:

  1. It has facts that describe the link over time — dates, states, quantities, amounts, outcomes.
  2. It participates in other relationships — the enrolment is graded by a teacher, the order line is shipped in a parcel.
  3. It is referred to by other things — a fine is charged on a loan, a review is written about an enrolment.
  4. It must be identified and tracked individually — "the loan number 4471".

The distinction between clauses 1 and the "attributes on an association" advice is the useful part, and it is a matter of degree rather than kind:

Case Example Verdict
A link with one descriptive attribute Member reserves Copy with reserved_on Association with an attribute — still a link
A link with a state machine Loan with borrowed, due, returned, overdue Entity — it has a lifecycle
A link with a quantity and a price Order — Product with quantity, unit_price Entity in practice (OrderLine), because it is priced, invoiced and shipped individually
A link that is graded Student — Course with grade Borderline: an association with an attribute is defensible; an Enrolment entity is better the moment the grade has a date, an examiner and an appeal
A link that is only a link Book — Author Association, no attributes

The pattern behind the table: the more the link is talked about in the requirements, the more it deserves to be an entity. A link nobody mentions beyond its existence is an association. A link that appears in sentences with dates, amounts, states and consequences is a thing.

Worked example: the library

Continuing from the previous lesson's thirteen entities, here are the attributes and the relationships.

Attributes of the principal entities

Entity Attributes Notes
Branch code (patterned, unique), name, address (composite: street, city, postal code), phone (multivalued), opening_hours (free text at this stage) Opening hours fail T3 and stay an attribute
Book isbn (patterned, unique), title, publication_year (ranged), language (enumerated), publisher (reference) Author is a relationship, not an attribute
Copy copy_number (ranged), acquisition_date, condition (enumerated), shelf (reference), state (enumerated) state is derived-able from loans — see below
Member card_number (patterned, unique), first_name, last_name, birth_date, email, phone (multivalued), address (composite) age is derived: excluded
Loan borrowed_on, due_on, returned_on (optional), renewals (ranged) days_overdue is derived: excluded
Reservation requested_on, queue_position (derived), state
Fine amount, currency, reason (enumerated), issued_on, paid_on (optional) amount is stored, not derived: the rate may change
Author name, birth_year (optional), nationality (optional)
Publisher name, city
Category name, description
Shelf code, room, capacity (ranged)
Librarian employee_code, name, hired_on

Candidate relationships

Relationship Entities Sentence Attributes Open questions
has_copy Book — Copy Each copy is of exactly one book; a book has one or more copies — Are copies ever reclassified to another title? (No, in practice)
written_by Book — Author Each book has one or more authors; an author writes zero or more books — Author order matters for citation — a position attribute, or an ordering rule
published_by Book — Publisher Each book is published by exactly one publisher — Self-published titles: optional, or a Publisher row meaning "none"?
classified_as Book — Category Each book is in exactly one category — One category or several? The text says "a category"
held_at Copy — Branch Each copy is held at exactly one branch — Transfers between branches: a Transfer event, or overwrite?
located_on Copy — Shelf Each copy is on zero or one shelf — A copy in transit has no shelf: optional
member_of Member — Branch Each member is registered at exactly one branch registered_on Can a member change branch? If yes, history is needed
borrows Member — Copy Each loan links one member and one copy borrowed_on, due_on, returned_on Promoted to the entity Loan: dates, state, fines
reserves Member — Book Each reservation links one member and one book requested_on, state Promoted to the entity Reservation: queue position, lifecycle
fined_for Fine — Loan Each fine refers to exactly one loan — A fine that covers several loans? Then it is many-to-many
recorded_by Loan — Librarian Each loan is recorded by exactly one librarian — Needed for audit; the requirement says "librarians record loans"
supervises Librarian — Librarian Each librarian may have one supervisor — Recursive; roles supervisor / subordinate

Four things in that table are worth noticing.

borrows and reserves became entities, the other nine stayed relationships. The test was clause 1 of the definition: they have facts that describe the link over time. has_copy has none — the link is the whole fact.

member_of kept registered_on as an association attribute. One date does not make a lifecycle. If the library later asks "how many members switched branch last year?", the attribute becomes a Membership entity with a period — a change of one line now, a redraw later.

supervises needs role names. Without supervisor / subordinate the diagram is unreadable: both ends say Librarian.

state on Copy is suspicious. It duplicates what the loans already say. Either it is derived (available if no active loan), or it records something loans cannot (lost, damaged, withdrawn). The right answer is usually: keep the states loans cannot express, derive the rest.

Common mistakes

Mistake What it looks like Why it hurts Fix
Composite attributes kept whole address as one string No query by city; no validation Explode into parts
Lists stuffed into a field phones = "333...; 021..." Unindexable, uncountable, unfixable Multivalued attribute or entity
Storing derived values age, available, total_loans Two sources of truth that diverge Compute, or store as a documented snapshot
Encoded identifiers code = "M-2024-0031" with all meaning inside Every report needs SUBSTRING Store the parts, generate the code
Attributes with no domain price, weight, date bare Units, currencies and precision become guesswork Name, domain, optionality, unit
Relationship named with a noun Member [loan] Copy Read as an entity; confused with the real Loan Verb phrase, active voice
Recursive relationship without roles Librarian — Librarian Which end is the boss? Role names on both ends
Link that should be an entity, kept as a link Student — Course with a grade crammed in Cannot hold the exam date, examiner, appeal Promote to Enrolment

Summary

  • An attribute is a claim about the domain with four parts: name, domain, optionality, unit. Three of the four are usually missing in a draft.
  • Four kinds, four rules: simple model directly; composite model the parts; multivalued never in one field; derived compute, unless the value must not change — then it is a snapshot and must be stored.
  • A coded string is a compressed record. If a requirement needs SUBSTRING, the schema is wrong.
  • Domains come as enumerated, ranged, patterned or reference. A reference domain is a relationship in disguise.
  • Optionality must say which case is meant: unknown, not applicable, or forbidden.
  • A relationship is a class of links between entities; its degree is the number of participants. Binary covers almost everything; ternaries are legitimate but rare and are not three binaries.
  • Role names are mandatory for recursive links and for two links between the same entities.
  • A relationship name must read correctly in both directions; use a verb phrase in the active voice.
  • A link stays an association unless it acquires a life of its own: facts over time, participation in other relationships, references from elsewhere, or individual tracking. Then it becomes an entity.

Verification questions

1. A hospital stores blood_pressure as "120/80". What is wrong, and what would you do?

Show the solution

It is a composite attribute compressed into a string: two measurements with a unit (mmHg) and a separator that happens to be a slash. Three concrete consequences:

  • You cannot query "patients with systolic above 140" without parsing the string, so the most basic clinical filter becomes a text operation.
  • You cannot validate the values, so "12O/8O" with letters is accepted.
  • You cannot compare readings across time, because the string carries no timestamp of its own — and a single value on the patient record loses the trend, which is the clinically meaningful fact.

The fix has two levels. First, split into systolic and diastolic, both ranged integers with a unit. Second — and this is the real insight — a blood pressure is measured at a moment, so it belongs to a Measurement (or Observation) event linked to the patient, not to the patient entity. The same reasoning that moved weight from Animal to Visit in the previous lesson applies here: a repeated measurement is an event, not an attribute.

2. Order — Product with attributes quantity and unit_price: association or entity? Justify.

Show the solution

In practice, an entity: OrderLine. The justification is not the presence of attributes — an association can carry attributes — but the four clauses of the test.

Facts over time: the unit price is a snapshot of the catalogue price at the moment of sale, and it must not change when the catalogue changes. That is a fact about the link, frozen.

Participation in other relationships: a line is shipped in a parcel, discounted by a promotion, returned individually. Each of those attaches to the line, not to the order.

Referenced from elsewhere: an invoice line, a credit note and a stock movement all refer to a specific line.

Individual tracking: the customer service conversation is about "line 3 of order 4471", not about the order as a whole.

Remove any three of those and the association with attributes would be defensible. With all four present, OrderLine is the honest model. Note that the price is also a snapshot in the sense of the derived-attribute discussion: recomputing it tomorrow would give a different answer, so it must be stored.

3. Why is Employee supervises Employee a relationship and not two entities?

Show the solution

Because both participants are instances of the same entity. The relationship does not create a new kind of thing; it creates a link between two employees, one of whom plays the role supervisor and the other subordinate.

Modelling it with two entities (Supervisor and Subordinate) would duplicate every attribute of Employee twice and would prevent a person from being both a supervisor of some people and a subordinate of another — a situation that is the norm in every organisation with more than one level.

The two things this relationship genuinely requires are role names (to make both ends readable) and a cardinality decision: can one employee have several supervisors? In a matrix organisation, yes — and that changes the relationship from 1:N to N:M, which is a fact the requirements must supply.

Exercise

Advanced

A scientific conference

Extract the attributes of each entity and the candidate relationships, then decide which links must become entities.

A conference has several tracks. Each track has a name, a short description and one or more chairs. Authors submit papers to exactly one track. A paper has a title, an abstract, a list of keywords, a submission date and a state (submitted, under review, accepted, rejected). Each paper has one or more authors; an author may submit several papers. Chairs assign each paper to two or three reviewers. A reviewer is a person with an email and an affiliation, and may review at most six papers. Each review has a score from 1 to 10, a confidence level, a text of comments and a submission date. When a paper has all its reviews, the chairs decide the outcome and may add a note for the authors. The conference wants to know, for each reviewer, the average score given, and for each track the acceptance rate.

Pay particular attention to: keywords, state, assignment of a paper to a reviewer, review, and the chairs' decision.

Commented solution

Entities and attributes

Entity Attributes Notes
Conference name, year, location, start_date, end_date If the system handles several editions, year is part of the identity
Track name, description Track names repeat across years: identity is Conference + name
Person email (patterned, unique), first_name, last_name, affiliation See the note on authors and reviewers below
Paper title, abstract, submitted_on, state (enumerated) keywords is multivalued
Keyword term Shared across papers → entity, not a multivalued attribute
Review score (ranged 1–10), confidence (enumerated), comments, submitted_on Entity: facts over time, referenced by the decision
Decision outcome (enumerated), decided_on, note_to_authors (optional) Entity, not an attribute of Paper
Assignment assigned_on Borderline — see below

Relationships

Relationship Entities Sentence Attributes Verdict
belongs_to Paper — Track Each paper is submitted to exactly one track — Association
chairs Track — Person Each track has one or more chairs — Association; role chair
writes Paper — Person Each paper has one or more authors; a person writes zero or more papers author_position Association with an attribute — order matters for citation
about Paper — Keyword Each paper has one or more keywords; a keyword may classify many papers — Association (N:M)
assigned_to Paper — Person Chairs assign each paper to two or three reviewers assigned_on Promoted to Assignment — it is referred to by the review and constrained by a maximum
produces Assignment — Review Each assignment produces zero or one review — Association, or merge into Assignment
decides Decision — Paper Each paper has at most one decision — Association, one-to-one
by Decision — Person The decision is made by one or more chairs — Association; role decider

The four decisions that carry the exercise

Authors and reviewers are the same entity. A person who submits a paper is very often a reviewer. Two entities (Author, Reviewer) would duplicate the person, and the same person would have two identities and two emails. Person plus two relationships, with the roles named in the relationship, is the correct model — and it is the recursive-role lesson applied at the level of two different links.

Review is an entity, not an association attribute. The tempting design is Paper — Reviewer with score and comments. It fails because the review is referenced by the decision, has its own submission date, can be late or missing, and is the subject of a reporting requirement ("average score per reviewer"). All four clauses of the test are satisfied.

Assignment is genuinely borderline, and that is the point. Paper — Reviewer with assigned_on would work if assignments had no consequences. But the requirement "may review at most six papers" is a constraint on the assignment as a fact, and a review refers to a specific assignment. Modelling it as Assignment makes both expressible; modelling it as a plain link makes the maximum a rule that no one can verify. Note that produces could be merged into Assignment — one assignment, at most one review — which is a legitimate simplification with a cost: an assignment without a review (a reviewer who never delivered) becomes indistinguishable from a missing row.

Decision is an entity, not Paper.state. The state on the paper says accepted or rejected. The decision adds who decided, when, and the note to the authors. Keeping only the state loses the audit trail, and the note has nowhere to live. Note the relationship between the two: state on Paper is derivable from the decision, so storing both is a redundancy that must be managed — either derive the state, or document that the state is a cache updated at decision time.

Two traps in the text

"A list of keywords" looks like a multivalued attribute. It is an entity, because keywords are shared across papers and the conference will want "papers per keyword" — a report that requires the keyword to exist on its own.

"At most six papers" is not an attribute and not a relationship. It is a business rule — a constraint on the cardinality of Assignment per reviewer — and it belongs in the requirements register, not in a column. It is also the kind of rule a DBMS cannot enforce declaratively in every case, which is exactly the distinction between conceptual constraints and implementable ones that Module 7 will draw.

Where to go next

Related lessons