Requirements Analysis

Case Study: The Library Requirements Package

The nine lessons of this module applied end to end to one domain. From the interview transcript to a requirements package with glossary, functional and non-functional requirements, rule register, entity list, attributes, associations and open questions — the artefact the rest of the course will keep building on.

Intermediate

Learning objectives

  • Produce the seven artefacts of a complete requirements package
  • Trace every entity, attribute and cardinality back to a requirement or a rule
  • Distinguish the model that the requirements justify from the parts that are assumptions
  • Recognise which elements will change in later modules and why
  • Apply the whole Module 2 process to a domain you have never seen

Prerequisites

  • Ambiguity and Incomplete Requirements
  • Business Rules and Constraints
  • Glossary of Terms

Introduction

Every lesson in this module used the same domain in fragments: a paragraph about loans here, a sentence about reservations there, a rule about fines when the point was about rules. This lesson puts the fragments back together.

The result is not a diagram — that is Module 3 — and it is not a design. It is the requirements package: the set of documents that a conceptual design can be built from, defended, and revised against. It is the deliverable that says here is what we understand, here is where it came from, and here is what we do not know.

Seven artefacts make up the package:

# Artefact Answers Produced in
1 Source inventory Who and what did we learn from? Requirements Gathering
2 Glossary What do the words mean? Glossary of Terms
3 Functional requirements What must the system do? Functional and Non-Functional Requirements
4 Non-functional requirements How well must it do it? Functional and Non-Functional Requirements
5 Rule register What must always be true? Business Rules and Constraints
6 Model draft: entities, attributes, associations What is in the domain and how is it connected? Candidate Entities; Attributes and Relationships
7 Assumption and open-question register What did we decide without knowing? Ambiguity and Incomplete Requirements

A package missing artefact 5 is a schema with no laws. A package missing artefact 7 is a schema whose decisions have no history. A package missing artefact 1 cannot be audited at all, because nobody can tell whether the library's actual rules were ever asked about.

1. The source material

The case begins with what the analyst was actually given. Three documents and three conversations.

Document 1 — the brief. One page, written by the library director:

The library serves 4,200 members across three branches. It holds roughly 38,000 copies of about 21,000 titles. Members borrow copies for a period set by their membership category, currently 30 days for adults and 45 for students. Loans may be renewed if nobody else is waiting. The catalogue is searchable by title, author and subject. The library also runs a reservations service and charges fines for late returns. Staff at each branch register loans and returns; the head office produces monthly statistics.

Document 2 — the current spreadsheet. One sheet per branch, columns: date, member name, member number, book title, author, copy code, due date, returned, fine. Two of the three sheets have a notes column that the third lacks. The member number is sometimes written with a leading zero and sometimes without.

Document 3 — the lending policy. A two-page internal regulation: membership categories and durations, renewal limits, fine rates, what happens to lost copies, who may waive a fine.

Conversations. The director (40 minutes, decisions and statistics), the head librarian at the main branch (90 minutes, daily operations and exceptions), and the catalogue manager (60 minutes, titles, authors, acquisitions).

The spreadsheet is the artefact that produces the most information per minute, and the reason is visible in the columns. It has no author table — authors are written as text next to titles. It has no copy-level distinction — book title and copy code are adjacent columns with no explanation of their relationship. It has a notes column on two sheets only, which means something is recorded there that the third branch does not record. And the member number is stored inconsistently, which means it is being typed rather than looked up, which means it is not yet a real identifier.

Every one of those observations becomes a question.

2. The glossary

Nine lessons ago the glossary was introduced as a document with nine fields per term. Here is the library's glossary, in its delivered form, trimmed to the fields that matter for the design.

Term Definition Not to be confused with Owner
Title An abstract published work, identified by ISBN when one exists A copy — the physical object Catalogue manager
Copy One physical volume on a shelf, identified by a barcode unique across all branches A title Catalogue manager
Author A person credited with a title; a person may author many titles and a title may have many authors A staff member Catalogue manager
Member A person registered to borrow, identified by a membership number A visitor, a staff member Head librarian
Membership category Adult, student, or concession; determines the loan period and the maximum number of open loans A member's status (active, suspended) Director
Loan The fact that a copy is out to a member, with a due date and a return date A reservation Head librarian
Renewal An extension of an existing loan's due date A new loan Head librarian
Reservation A member's claim on a title that has no available copy, with a queue position and an expiry A loan Head librarian
Fine An amount owed by a member for a late return, a lost copy, or damage A payment Director
Branch A physical library location with staff and stock The head office Director
Staff member An employee who registers loans, returns and waivers A member Director
Available The state of a copy that is on the shelf and not reserved for another member "Exists in the catalogue" Head librarian

Three entries in that table do real work.

Title versus Copy is the distinction the spreadsheet did not make, and it is the single most consequential decision in the whole model. Without it, the library cannot record that it owns three copies of a book, cannot lend one while another is on the shelf, and cannot say which copy a fine refers to.

Loan versus Reservation are two different things that both look like "a member and a book". They have different attributes (due date versus expiry), different rules (fines versus queue order) and different relationships (a loan refers to a copy, a reservation to a title). Merging them produces a table half of whose columns are null.

Available is the term that reveals a derivation. The head librarian said "available" meaning "on the shelf and not reserved", which is computed from the copy's loans and reservations. It is not a stored fact, and the glossary entry records that.

3. Functional requirements

Twelve requirements, each traceable to a source. The identifiers are stable and will be referenced by the rule register and by every later module.

ID Requirement Source
FR-01 Register a member with a unique membership number, a name, contact details and a category Director; head librarian
FR-02 Record a loan of a specific copy to a specific member, with a due date computed from the member's category Policy; head librarian
FR-03 Record the return of a copy and close the loan Policy; head librarian
FR-04 Renew a loan, up to the limit set by policy, provided no other member is waiting Policy
FR-05 Place a reservation on a title that has no available copy, and maintain the queue Head librarian
FR-06 Notify a member when a reserved title becomes available, and expire the reservation after 72 hours Head librarian
FR-07 Compute and record a fine for a late return, a lost copy or damage Policy; director
FR-08 Record the payment of a fine, and record a waiver with the staff member who granted it and the reason Policy; director
FR-09 Search the catalogue by title, author and subject Director
FR-10 Maintain the catalogue: titles, copies, authors, subjects, acquisitions and withdrawals Catalogue manager
FR-11 Suspend a member's borrowing rights and record why Policy
FR-12 Produce monthly statistics per branch: loans, returns, reservations, fines, active members Director

Note what is not in the list: nothing about acquisitions budgets, nothing about inter-library loans, nothing about a public web catalogue. Those came up in conversation and were explicitly deferred. Recording the deferral matters as much as recording the requirement, because a later reader will otherwise assume they were forgotten.

4. Non-functional requirements

The constraints that shape the design without being features.

ID Requirement Consequence for the schema
NFR-01 4,200 members, 38,000 copies, 21,000 titles, about 900 loans per day Small data; no partitioning, no sharding, no denormalisation for scale
NFR-02 Loan registration must complete in under one second at the desk Indexes on the lookup paths: barcode, membership number
NFR-03 Loan history must be retained for five years Loans are never deleted; no purge job
NFR-04 Monthly statistics per branch, produced on demand Aggregates computed from loans, not stored counters
NFR-05 Italian and English interfaces; catalogue data in Italian Schema stores one value per field; interface language is not a schema concern
NFR-06 Three branches today, possibly five within two years Branch is an entity, never a column or an enumeration
NFR-07 One member record per person across all branches Membership number is global, not per branch

NFR-06 and NFR-07 both look trivial and both decide structure. A branch column on the member would make NFR-07 impossible; a branch_code enumeration would make NFR-06 a migration.

5. The rule register

The full register has eighteen entries. Here are all of them, in the eight-field form, condensed to the fields that carry information.

ID Statement Family Enforcement
BR-001 Every copy belongs to exactly one title Existence Model: minimum 1, maximum 1
BR-002 Every loan refers to exactly one member and one copy Existence Model: minima 1; DBMS NOT NULL + FK
BR-003 A copy may not be on two open loans at the same time Cardinality / prohibition DBMS: partial unique index on open loans per copy
BR-004 A member may hold at most 5 open loans (3 for students over 45-day loans) Cardinality Application
BR-005 A loan may be renewed at most twice, and not if another member is waiting Cardinality Application + CHECK (renewals BETWEEN 0 AND 2)
BR-006 A copy is identified by a barcode unique across all branches Identity Model: identifier; DBMS UNIQUE
BR-007 A title is identified by ISBN when present, otherwise by an internal catalogue number Identity Model: alternative identifiers
BR-008 The loan period is 30 days for adults and 45 for students Domain DBMS CHECK (loan_period_days BETWEEN 1 AND 365) on the category
BR-009 The return date is never before the loan date Domain (cross-attribute) DBMS table-level CHECK
BR-010 A loan is overdue when today is past the due date and it has not been returned Derivation Derived, not stored
BR-011 The fine is 0.15 per overdue day, capped at the copy's replacement value Derivation Application computes; stored as a snapshot with the rate used
BR-012 A fine may be waived only by a staff member, recording who and why Transition / authorisation Application only
BR-013 A reservation may not be placed on a title that has an available copy Prohibition Application
BR-014 A reservation expires 72 hours after it becomes collectable Transition Application; stored as expires_at
BR-015 Queue position is first-come, first-served, unless staff override Ordering Application; derived from reserved_at
BR-016 A member whose fines exceed 10.00 is suspended until they are paid Derivation + transition Application; stored as a status
BR-017 A copy may be withdrawn from the catalogue, but its loan history is kept Transition Model: status attribute; never deleted
BR-018 A title may have many authors; an author may write many titles Cardinality Model: N:M association

Reading the enforcement column gives the number that matters: seven rules are enforced by the model, four by the DBMS, and seven by the application. A schema generated from this domain enforces about a third of the library's actual rules — the same ratio as the gym in the previous lesson, and for the same reasons.

BR-015 is worth a second look. "First-come, first-served, unless staff override" is a rule whose second half makes the first half unenforceable in a derived form: a queue position computed from reserved_at cannot express a manual override, so if overrides happen, the position must be a stored attribute maintained by the application. That is a decision to confirm, not to assume — it is assumption A-4 below.

6. The model draft: entities

The candidate list after the noun-phrase pass and the four tests. Twenty-three candidates, eight survivors.

Candidate Verdict Reason
Title Entity Independent identity (ISBN), own attributes, referred to alone in the catalogue
Copy Entity Own identity (barcode), own attributes (condition, acquisition date), the thing actually lent
Author Entity Shared across titles; the report "titles per author" requires it to stand alone
Subject Entity Shared across titles; FR-09 searches by it
Member Entity Own identity, own attributes, own lifecycle
Membership category Entity Referenced by both member and loan period; will grow
Loan Entity Has dates, status and its own rules; referred to by fines and by statistics
Reservation Entity Has its own dates, queue position and lifecycle
Fine Entity Has an amount, a reason, a payment date and an audit trail
Branch Entity NFR-06 requires it to be data, not a column
Staff member Entity Referenced by waivers (BR-012); must be identifiable
Address Composite attribute of member Never referenced independently; no separate lifecycle
Telephone Multivalued attribute Kept as a separate table at implementation; not an entity in the conceptual model
Name Composite attribute First and last name; never referenced alone
Fine rate Attribute of a policy, not an entity One value, changed rarely; a column on the category
Due date Derived attribute of loan Computed from loan date and category period
Member status Attribute of member Enumerated: active, suspended
Copy condition Attribute of copy Enumerated: good, damaged, lost, withdrawn
Age Derived attribute of member Computed from birth date; not stored
Total fines Derived attribute of member Computed; stored only as a suspension flag per BR-016
Notes Not modelled Free text with no structure; if it carries decisions, each becomes an attribute
Acquisition Entity (deferred) Would record purchase price and supplier; out of scope for this phase
Inter-library loan Not modelled (deferred) Explicitly deferred in the interview

Two verdicts deserve attention because they go against intuition.

Subject survives as an entity although nothing in the brief asks for it beyond "searchable by subject". It survives because it is shared: a subject classifies many titles, and a subject written as free text cannot support the report "how many titles per subject", which the catalogue manager asked for unprompted.

Telephone does not survive as an entity. It is multivalued — a member may have a home and a mobile number — and at implementation it will need a table. But in the conceptual model it is an attribute with a cardinality, not an entity, because it has no identity, no attributes of its own and no relationships. The distinction matters: conflating the two produces conceptual models cluttered with tables that have no conceptual justification.

7. The model draft: attributes

Entity Attributes Identifier
Title isbn (optional, unique), catalogue_no (unique), title, subtitle, publication_year, publisher, language, subject isbn or catalogue_no
Copy barcode (unique), acquisition_date, replacement_value, condition, status barcode
Author author_id, first_name, last_name, birth_year (optional) author_id
Subject subject_code, name, description subject_code
Member membership_no (unique), first_name, last_name, birth_date, email (optional), phone (multivalued), address (composite: street, city, postcode), joined_on, status membership_no
Membership category category_code, name, loan_period_days, max_open_loans, max_renewals category_code
Loan loan_id, loan_date, due_date (derived), return_date (optional), renewals, status loan_id
Reservation reservation_id, reserved_at, available_at (optional), expires_at (optional), queue_position, status reservation_id
Fine fine_id, amount, reason, issued_on, paid_on (optional), waived_by (optional), waiver_reason (optional), rate_applied fine_id
Branch branch_code, name, address (composite), phone, opened_on branch_code
Staff member staff_no, first_name, last_name, role, branch staff_no

Four decisions in that table are worth naming.

due_date is derived but the attribute is real. It is computed at loan creation from loan_date and the category's period, and it must be stored, because the category's period can change and a loan's due date must not change with it. This is the derivation-with-snapshot pattern from the previous lesson, and it is the reason due_date appears in the table despite being "derived".

rate_applied on Fine exists for the same reason. The fine rate will change; a historical fine must not. Storing the rate used makes the amount reconstructible.

Title.isbn is optional and catalogue_no is not. BR-007 says a title is identified by its ISBN when present. Pre-1970 books and donations frequently have none, so the model needs an alternative identifier that always exists. Two identifiers, one optional, is the honest model — and it means the primary key cannot simply be the ISBN.

phone is marked multivalued and address composite. Both are conceptual notations that will become tables or columns later. Recording the notation now preserves the information; deciding the implementation now would be premature.

8. The model draft: associations

Association Entities Minimum and maximum Attributes Notes
belongs_to Copy — Title Each copy: exactly 1 title. Each title: 1 or more copies — BR-001
writes Title — Author Each title: 1 or more authors. Each author: 0 or more titles author_order Position matters for citation
classified_as Title — Subject Each title: 1 or more subjects. Each subject: 0 or more titles — Supports FR-09
held_at Copy — Branch Each copy: exactly 1 branch. Each branch: 0 or more copies — A copy is at one branch at a time
is_a Member — Membership category Each member: exactly 1 category. Each category: 0 or more members — BR-008
borrows Loan — Member Each loan: exactly 1 member. Each member: 0 or more loans — BR-002, BR-004
lends Loan — Copy Each loan: exactly 1 copy. Each copy: 0 or more loans over time — BR-003 constrains open loans only
reserves Reservation — Member Each reservation: exactly 1 member. Each member: 0 or more reservations —
for_title Reservation — Title Each reservation: exactly 1 title. Each title: 0 or more reservations — Per assumption A-2, not per copy
charged_to Fine — Member Each fine: exactly 1 member. Each member: 0 or more fines —
concerns Fine — Loan Each fine: 0 or 1 loan. Each loan: 0 or more fines — Optional: a fine for damage has no loan
issued_by Fine — Staff member Each waived fine: exactly 1 staff member. Each staff member: 0 or more waivers — Only when waived, hence optional
works_at Staff member — Branch Each staff member: exactly 1 branch. Each branch: 1 or more staff —

Five of those rows need justification, and the justification is the point of the table.

writes has an attribute, author_order. The requirement is citation, and citation is ordered. An association attribute is exactly the right construct.

lends is one-to-many over time, not a prohibition. A copy is lent many times across its life; BR-003 forbids two open loans at once, which the conceptual model cannot express. The model records the many-to-many-over-time shape and the rule register records the prohibition.

concerns is optional on the loan side. A late return produces a fine that refers to a loan; a damaged or lost copy produces a fine that does not, or that refers to the loan only loosely. Forcing the association to be mandatory would make the second case unrecordable.

issued_by exists only when waived. The rule is "a waiver must record who and why". A fine that was paid, not waived, has no staff member. The association is optional, and the reason it exists at all is BR-012 — a rule that the model does not enforce but that requires the model to provide somewhere to record the fact.

works_at is a maximum of 1 on the staff side. The director confirmed that staff do not move between branches within a day. Had that been a many-to-many, the association would need a join table and the whole "which branch registered this loan" question would change shape.

9. The assumption and open-question register

Six assumptions, each with the six fields from the previous lesson. This is the artefact that most teams skip and the one that saves the most time.

ID Question Chosen Why Cost if wrong Confirm with
A-1 Does a member's category change the loan period of loans already open? No — the period in force at loan creation applies A loan's due date is a commitment made to the member Low: a rule change in the application; no schema change Director
A-2 Does a reservation hold a specific copy or a title? A title Members do not care which copy; the queue is per title Moderate: add a nullable copy_id, backfill, change the queue rule Head librarian
A-3 Is a renewal a new loan or a modification? A modification, incrementing renewals The director described "extending"; statistics count loans, not periods Low–moderate: billing and history views change; existing data would need merging Director
A-4 Do staff ever override the reservation queue order? No — position is derived from reserved_at Nobody described an override; BR-015's second half came from the policy document, not from practice Moderate: add queue_position as a stored attribute and renumber on every cancellation Head librarian
A-5 Are fines charged per day or per started week? Per day, at 0.15 The policy document says "per day" Low: one expression in the application; historical fines unaffected because the rate is snapshotted Director
A-6 Can one person be both a member and a staff member? Yes, as two records They are different roles with different identifiers and different rules High: if it must be one person, Member and Staff become roles of a Person entity and every association changes Director

A-6 is the assumption to be least comfortable with, and the register says so by marking its cost as high. It is also the one that a five-minute question would settle — which is exactly why the confirm with column exists and why the register is reviewed before the design is frozen.

Open questions carried forward

Four questions could not be answered in the time available and are not yet assumptions:

  1. Does the library charge for reservations that are never collected?
  2. Are withdrawn copies deleted or retained with a status? (BR-017 leans towards retained, but the director was unsure.)
  3. Does the monthly statistics report count loans by the branch where they were registered, or the branch that holds the copy?
  4. When a member is deleted, what happens to their loan history? (NFR-03 says five years, but a member's own request to be forgotten may conflict.)

None of these blocks the conceptual design. All of them will block implementation, and recording them now means the questions are asked once rather than rediscovered.

10. What the package is worth

Read the seven artefacts together and something becomes visible that no single artefact shows: the proportion of the model that is justified by evidence versus assumption.

Count the entities: eight. Of those, six appear directly in a document or a conversation. Subject appears because the catalogue manager asked for a report. Membership category appears because the policy sets different periods for different members. Both are justified.

Count the associations: thirteen. Eleven are stated or implied; concerns and issued_by exist because of rules (BR-011, BR-012) rather than because anyone described them. That is a legitimate reason — a rule that needs somewhere to record a fact requires an association to record it in.

Count the assumptions: six, one of them high-cost.

That ratio — mostly justified, a few rule-driven, a few assumed — is what a healthy requirements package looks like. A package with no assumptions is not complete; it is a package where the assumptions were not noticed.

Summary

  • The requirements package has seven artefacts: source inventory, glossary, functional requirements, non-functional requirements, rule register, model draft, assumption register. Each answers a question the others cannot.
  • Source artefacts — the existing spreadsheet, the paper form, the old export — yield more per minute than interviews, because they record what people do rather than what they believe they do.
  • The glossary decides the distinctions the design depends on. In this domain the decisive pair is title versus copy, followed by loan versus reservation.
  • Functional requirements say what the system does; non-functional requirements shape the schema without being features. NFR-06 ("possibly five branches") decides that branch is an entity; NFR-07 decides that membership numbers are global.
  • The rule register exposes the enforcement gap: of eighteen rules, seven are enforced by the model, four by the DBMS, seven by the application.
  • The model draft is traceable: every entity, attribute and association can be pointed back at a requirement, a rule or a named assumption.
  • Entities that are shared and reported on survive even when no requirement mentions them directly. Attributes that are multivalued or composite are not entities in the conceptual model, whatever tables they become later.
  • Derived attributes are stored when the derivation depends on a value that can change — due_date and rate_applied are the two examples here.
  • The assumption register is the artefact that most teams skip. An assumption with a stated cost and a named confirmer is a managed risk; one without is a latent defect.
  • A healthy package is mostly justified, partly rule-driven, and has a small number of recorded assumptions. A package with none has simply not looked.

Verification questions

1. Why is due_date listed as an attribute of Loan when the lesson on attributes calls it a derived attribute that should not be stored?

Show the solution

Because the derivation depends on a value that can change, and the two cases are governed by different questions.

due_date is computed at loan creation as loan_date + category.loan_period_days. The category's period is not a constant — BR-008 sets 30 days for adults and 45 for students, and the policy document implies these can be revised. If due_date were recomputed on every read, then the day the library changes the student period from 45 to 40 days, every open student loan silently becomes overdue four days earlier, and members are charged fines for a rule that changed after they borrowed.

Storing due_date makes the commitment made at loan time durable. It is a snapshot, exactly like rate_applied on Fine.

Contrast with BR-010, a loan is overdue when today is past the due date and it has not been returned. That is derived and not stored, because it depends on today: any stored value would be wrong tomorrow unless something updated it nightly, and a nightly job that fails leaves the library with wrong overdue data and no way to tell.

The rule is not "derived means don't store". It is: store a derived value when its inputs can change under it; compute it when its inputs cannot.

2. The package says Subject is an entity and Telephone is not, even though both are multivalued and both will need a table at implementation. Explain the distinction.

Show the solution

The distinction is between the conceptual model and the implementation, and it is decided by whether the thing has an identity of its own.

Subject has an identity (subject_code), its own attributes (name, description), and its own relationships (it classifies titles). It is shared — many titles have the same subject — and it is reported on: the catalogue manager asked how many titles fall under each subject, which is impossible if the subject is free text on the title.

Telephone has no identity. Two phone numbers are not distinguished by anything except the number itself; there is nothing to say about one of them that is not just the number; and no relationship points at a phone number. It is an attribute of Member that happens to admit more than one value.

At implementation, both become tables: subject as an entity table, member_phone as a table representing a multivalued attribute. But the conceptual model should say Member has a multivalued attribute phone, not that Member is associated with a Phone entity, because the second statement asserts something false about the domain — that a phone number is a thing the library cares about independently.

Keeping the two levels distinct prevents a conceptual diagram cluttered with tables that have no conceptual reason to exist, which is one of the most common defects in student models.

3. Assumption A-6 says a person might be both a member and a staff member, and marks the cost as high. Why is that cost so much higher than the others?

Show the solution

Because it changes the shape of the model rather than the content of one element.

If members and staff are separate entities, the model has Member with membership_no and Staff with staff_no, and thirteen associations attached to the appropriate one. The change is local: adding or removing an association, adding a column.

If a person can be both, then Member and Staff are roles of one Person. That means:

  • A new Person entity with the shared attributes (name, birth date, contacts).
  • Member and Staff become either subtypes of Person or roles attached to it — a generalisation, which Module 4 will then have to restructure.
  • Every association that currently points at Member or Staff must be re-examined: does it point at the person or at the role? A loan points at the member role; a waiver points at the staff role; but a name change updates the person.
  • The identifier strategy changes: membership_no is no longer the identity of the person, only of the membership.

That is a different diagram, not an edited one, and it would be discovered at Module 3 or later, after the conceptual design has been reviewed.

The general lesson: the cost of an assumption is proportional to how far up the model it operates. An assumption about a domain or an optionality is cheap. An assumption about whether two entities are one entity is expensive, because everything downstream inherits the answer.

Exercise

Advanced

A requirements package for a dental clinic

A dental clinic with three surgeries is replacing its paper appointment book. Produce the full seven-artefact package.

The clinic has four dentists and two hygienists. Patients register once and are seen by whichever clinician is available, though most prefer to stay with the same dentist. Each appointment is booked into a surgery for a fixed 30-minute slot, and the clinic runs 09:00–18:00 Monday to Friday. Treatments are recorded against the appointment: a check-up, a filling, a root canal, a cleaning, and so on; each treatment has a standard fee, and some appointments involve more than one treatment. The clinic bills patients monthly, and a patient with an unpaid bill older than 60 days is not given new appointments until it is settled. Dentists may request X-rays; each X-ray belongs to one patient and is stored as a digital file with a date and the clinician who requested it. Some patients are referred to specialists outside the clinic; the referral records the specialist, the reason and the date. The clinic must keep clinical records for ten years. Two of the four dentists work part-time and are only present on certain days.

Produce:

  1. The glossary, with at least ten terms and the distinctions that matter.
  2. Eight to twelve functional requirements, numbered and traced to a phrase in the text.
  3. Five to seven non-functional requirements, each with its consequence for the schema.
  4. A rule register with at least twelve entries, classified and with the enforcement layer.
  5. The entity list with a verdict for every candidate, including the ones rejected.
  6. Attributes for each surviving entity, with identifiers.
  7. The associations with minimum and maximum cardinalities.
  8. The assumption register — at least four assumptions, one of which must be high-cost, with the reason for the cost.
Commented solution

1. Glossary (abridged)

Term Definition Not to be confused with
Patient A person registered for treatment, with a clinical record A customer of a single visit
Clinician A dentist or hygienist who performs treatments A staff member — receptionists are staff but not clinicians
Dentist / Hygienist The two clinician types; they differ in the treatments they may perform —
Appointment A booked 30-minute slot with a clinician in a surgery A treatment — one appointment may contain several
Treatment A clinical procedure with a standard fee, recorded against an appointment An appointment
Treatment type The catalogue entry: check-up, filling, root canal… A performed treatment
Surgery A physical room, bookable in slots The clinic
Bill A monthly invoice covering the treatments performed in that month A payment
X-ray A digital image belonging to one patient, requested by a clinician A treatment
Referral An outgoing referral to an external specialist An appointment with an external clinician
Availability The set of slots in which a clinician is present and free The clinic's opening hours

The decisive distinctions are appointment versus treatment (one appointment contains several treatments, and fees attach to treatments), treatment versus treatment type (the catalogue entry versus the performed procedure, with the fee snapshot taken from the type), and clinician versus staff member (only clinicians have appointments).

2. Functional requirements

ID Requirement Source phrase
FR-01 Register a patient with a unique identifier, personal and contact data "Patients register once"
FR-02 Record each clinician's type (dentist or hygienist) and their weekly availability "four dentists and two hygienists"; "only present on certain days"
FR-03 Book an appointment for a patient with a clinician in a surgery in a 30-minute slot "booked into a surgery for a fixed 30-minute slot"
FR-04 Record one or more treatments against an appointment, each with its fee "some appointments involve more than one treatment"
FR-05 Maintain the treatment-type catalogue with standard fees "each treatment has a standard fee"
FR-06 Produce a monthly bill per patient covering that month's treatments "bills patients monthly"
FR-07 Record payments and show the outstanding balance per patient "unpaid bill older than 60 days"
FR-08 Record X-rays with a date, the patient and the requesting clinician "each X-ray belongs to one patient"
FR-09 Record referrals with the specialist, the reason and the date "the referral records the specialist, the reason and the date"
FR-10 Record a patient's preference for a particular dentist "most prefer to stay with the same dentist"
FR-11 Prevent new appointments for patients with bills unpaid over 60 days "is not given new appointments until it is settled"
FR-12 Retain clinical records for ten years "must keep clinical records for ten years"

3. Non-functional requirements

ID Requirement Consequence
NFR-01 ~2,000 active patients, ~120 appointments per week Small data; no scale-driven denormalisation
NFR-02 The receptionist must find a patient and a free slot in under two seconds Indexes on patient name, on clinician, on slot start
NFR-03 Clinical records retained ten years, including X-ray files No deletion; X-ray files stored outside the database with a path in it
NFR-04 Four clinicians today; the clinic may add two more surgeries Clinician and surgery are entities, never enumerations
NFR-05 Billing monthly per patient Treatment rows carry the fee at the time, not a pointer to the catalogue
NFR-06 X-ray images are large Files on disk or object storage; the database stores metadata and a reference

NFR-03 and NFR-06 together decide that X-ray files are not in the database, while X-ray records are — a decision that belongs in the package because it shapes the schema.

4. Rule register (abridged to the twelve most important)

ID Statement Family Enforcement
BR-001 Every appointment has exactly one patient, one clinician and one surgery Existence Model: minima 1; DBMS NOT NULL + FK
BR-002 A clinician cannot be in two appointments in the same slot Prohibition DBMS: unique on (clinician_id, slot_start)
BR-003 A surgery cannot host two appointments in the same slot Prohibition DBMS: unique on (surgery_id, slot_start)
BR-004 Appointments are 30 minutes and fall between 09:00 and 18:00 on weekdays Domain DBMS CHECK on slot start time and day
BR-005 Every treatment is recorded against exactly one appointment Existence Model: minimum 1
BR-006 A treatment's fee is the standard fee of its type at the time of the treatment Derivation + time Snapshot: store the fee on the treatment
BR-007 A clinician may only perform treatments their type permits Transition / authorisation Application only
BR-008 A bill covers exactly one patient and one calendar month Domain DBMS unique on (patient_id, year_month)
BR-009 A patient with a bill unpaid over 60 days cannot book Derivation + transition Application; computed on booking
BR-010 An X-ray belongs to exactly one patient and records one requesting clinician Existence Model: minima 1
BR-011 Clinical records are never deleted before ten years Transition Application and policy — no schema mechanism; retention job refuses
BR-012 A patient may have at most one preferred dentist Cardinality (bound 1) Model: maximum 1 on the preference association

BR-006 is the one that most often goes wrong in practice: storing a pointer to the treatment type and reading the fee from the catalogue means that a fee increase silently rewrites every historical bill.

5. Entity list

Candidate Verdict Reason
Patient Entity Own identity, own record, referenced by appointments, bills, X-rays
Clinician Entity Own identity, availability, appointments
Clinician type Entity Dentist/hygienist; determines permitted treatments (BR-007)
Appointment Entity The central event; has a slot, a surgery, participants
Treatment Entity Has its own fee snapshot and is the unit of billing
Treatment type Entity Shared catalogue with fees; reported on
Surgery Entity NFR-04: may grow; bookable resource
Bill Entity Monthly, per patient, referenced by payments
Payment Entity Own date, amount, method
X-ray Entity Own date, file reference, requesting clinician
Referral Entity Own specialist, reason, date
Clinician availability Entity Weekly pattern per clinician; needed for booking
Preferred dentist Association attribute or a 1:1 association BR-012 bounds it at one
Patient address Composite attribute No independent lifecycle
Patient phone Multivalued attribute No identity
Age Derived attribute From birth date
Outstanding balance Derived attribute From bills and payments; not stored
Clinic Not modelled One clinic; a constant, not data
Receptionist Rejected for this phase Not referenced by any requirement; staff management is out of scope

The two rejections matter. Clinic is a constant because there is exactly one and nothing refers to it — the moment a second clinic appears it becomes an entity, which is why NFR-04 was written for clinicians and surgeries but not for the clinic itself. Receptionist is rejected because no requirement references it; recording staff is a different system. Both rejections are recorded so that a later reader knows they were considered.

6. Attributes

Entity Attributes Identifier
Patient patient_no, first_name, last_name, birth_date, email (optional), phone (multivalued), address (composite), registered_on, preferred_clinician_id (optional) patient_no
Clinician clinician_no, first_name, last_name, clinician_type, active_from, active_to (optional) clinician_no
Clinician type type_code, name, description type_code
Clinician availability availability_id, day_of_week, start_time, end_time availability_id
Surgery surgery_code, name, floor surgery_code
Appointment appointment_id, slot_start, duration_minutes, status, notes appointment_id
Treatment treatment_id, performed_on, fee_charged, notes treatment_id
Treatment type type_code, name, standard_fee, duration_minutes type_code
Bill bill_id, year_month, issued_on, total_amount, status bill_id
Payment payment_id, paid_on, amount, method payment_id
X-ray xray_id, taken_on, file_reference, body_area xray_id
Referral referral_id, referred_on, specialist_name, specialist_practice, reason referral_id

fee_charged on Treatment and total_amount on Bill are both snapshots, for the reason in BR-006. duration_minutes appears on both Appointment and Treatment type and they are not redundant: the first is what was booked, the second is the standard duration of the procedure.

7. Associations

Association Entities Min and max Attributes
has_appointment Appointment — Patient Each appointment: exactly 1 patient. Each patient: 0 or more appointments —
with_clinician Appointment — Clinician Each appointment: exactly 1 clinician. Each clinician: 0 or more appointments —
in_surgery Appointment — Surgery Each appointment: exactly 1 surgery. Each surgery: 0 or more appointments —
includes Appointment — Treatment Each treatment: exactly 1 appointment. Each appointment: 1 or more treatments —
of_type Treatment — Treatment type Each treatment: exactly 1 type. Each type: 0 or more treatments —
billed_in Treatment — Bill Each treatment: 0 or 1 bill. Each bill: 1 or more treatments —
for_patient Bill — Patient Each bill: exactly 1 patient. Each patient: 0 or more bills —
settled_by Bill — Payment Each payment: exactly 1 bill. Each bill: 0 or more payments —
belongs_to X-ray — Patient Each X-ray: exactly 1 patient. Each patient: 0 or more X-rays —
requested_by X-ray — Clinician Each X-ray: exactly 1 clinician. Each clinician: 0 or more X-rays —
refers Referral — Patient Each referral: exactly 1 patient. Each patient: 0 or more referrals —
available Clinician availability — Clinician Each availability row: exactly 1 clinician. Each clinician: 1 or more rows —
prefers Patient — Clinician Each patient: 0 or 1 preferred clinician. Each clinician: 0 or more preferring patients —

billed_in deserves a note: a treatment is billed once, so the association is optional on the treatment side (a treatment performed on the 30th is billed in that month, but the bill may not exist yet). Making it mandatory would mean the treatment cannot be recorded before the bill — which inverts the real process.

8. Assumption register

ID Question Chosen Why Cost if wrong Confirm with
A-1 Is a treatment always part of exactly one appointment? Yes The text says treatments are "recorded against the appointment" Low: add a nullable appointment reference for walk-in procedures Practice manager
A-2 Can a bill contain treatments from more than one month? No — one bill per patient per month The text says "bills patients monthly" Moderate: the bill would need a period rather than a month, and the unique constraint changes Practice manager
A-3 Does the fee snapshot include discounts for children or concessions? No — the standard fee is charged No discount was mentioned Low: a discount attribute on the treatment; historical bills unaffected because the fee is already snapshotted Practice manager
A-4 Is a referral always to an external specialist, never to another clinician in the clinic? External only The text says "specialists outside the clinic" High: internal referrals would make Referral point at either a Clinician or an external person, requiring either two associations or a generalisation — a different shape of model Clinical lead
A-5 Does a hygienist's availability follow the same weekly pattern every week? Yes "only present on certain days" implies a fixed pattern Moderate: a per-date availability table instead of a per-weekday one Practice manager
A-6 Can two clinicians share a surgery in the same slot? No One surgery, one appointment per slot is the obvious reading Low: drop the unique constraint on (surgery_id, slot_start) Practice manager

A-4 is the high-cost one, and for the same structural reason as A-6 in the library case: it decides whether Referral has one target or two, which decides whether the model needs a generalisation or a role — and a generalisation is exactly the construct Module 4 will have to remove. Getting it wrong is not a column change; it is a different diagram.

What the exercise is testing

Not whether you produced the same twelve rules. Whether every artefact can be traced: each requirement to a phrase, each rule to a requirement or a policy, each entity to a requirement or a rule, each assumption to a question you could not answer.

A package where every element has a source is a package that can be defended in a review. One where the sources are missing is a package that will be redesigned by whoever inherits it.

Where to go next

Related lessons