Requirements Analysis in the Database Life Cycle
Requirements Analysis is the first and most critical stage of the Database Life Cycle (DBLC). Before a single table is designed or a line of SQL is written, a database designer must understand what data the organization needs to store, how that data will be used, and what rules govern its management. This stage involves gathering detailed information from stakeholders, documenting business objects and their relationships, and producing a set of requirements that will guide every subsequent design decision. It requires multiple iterations to capture all necessary details, as initial discussions often reveal only part of the picture. The goal is to create a comprehensive foundation for designing a database that genuinely meets the organization's needs rather than one that has to be rebuilt when those needs become clearer.
This module examines Requirements Analysis in depth, using a consistent case study — Stories on CD, Inc. — to ground each concept in a realistic business context. Each lesson in the module addresses one or more of the activities that together constitute a complete Requirements Analysis. This introductory lesson establishes the scope of those activities and explains why getting this stage right determines the quality of everything that follows.
Learning Objectives for Lesson 1:
- Explain the purpose of Requirements Analysis.
- Identify business objects and describe their characteristics.
- Explain the importance of business rules.
- Explain the purpose of interviewing data users.
- Explain the purpose of a data flow diagram.
- List reasons for creating user views.
- Describe the documents produced during Requirements Analysis.
Where Requirements Analysis Sits in the DBLC
The Database Life Cycle describes the complete sequence of stages involved in creating, deploying, and maintaining a database system. These stages move from an initial conceptual understanding of business needs through logical design, physical implementation, and ongoing maintenance. Requirements Analysis occupies the first position in this sequence because no subsequent stage can proceed correctly without it. The logical design stage — where entities, attributes, and relationships are formally modeled — depends entirely on the accuracy of the requirements gathered here. If Requirements Analysis is rushed or incomplete, errors propagate forward into every downstream stage and become progressively more expensive to correct.
Within the DBLC, Requirements Analysis produces the raw material that the conceptual design stage transforms into an Entity-Relationship Diagram (ERD). The ERD in turn drives the logical design, which produces a normalized table structure. That structure then informs the physical design, which determines how the database is implemented on a specific platform. A weakness at the Requirements Analysis stage therefore does not stay contained — it surfaces later as missing entities, incorrect relationships, or business rules that the database cannot enforce.
Understanding this dependency is one reason experienced database designers treat Requirements Analysis as an investment rather than a preliminary step to get through quickly. The time spent in thorough requirements gathering consistently reduces the total time spent across the full DBLC.
Case Study: Requirements Analysis for Stories on CD, Inc.
Stories on CD, Inc. is a small, family-owned retail business selling children's audio CDs through two channels: in-store sales (30% of revenue) and mail-order (70% of revenue). The company was founded by Bob and Jane Martin, who have built a loyal customer base over several years. The business has experienced consistent growth, particularly during seasonal peaks around the school year and holidays, but its current data management system is struggling to keep pace.
The existing system is a flat-file database originally built by the Martins' son Ted when the business was much smaller. A flat-file database stores all data in a single table or a small number of unrelated tables, which works adequately for simple operations but creates serious problems as data volume grows. Ted's system now exhibits the classic symptoms of flat-file limitations: duplicate customer records created when the same person places both in-store and mail-order purchases, difficulty deleting distributor records without accidentally losing associated order data, and no clean way to track which customers are active buyers versus prospective customers who have not yet made a purchase.
The Martins have decided to transition to a relational database to address these problems and support the company's continued growth. They have engaged a database designer to lead the transition, beginning with Requirements Analysis. This case study follows that process from the first stakeholder interview through the documentation of business rules and data requirements.
Business Needs Analysis
The first task in Requirements Analysis is to understand the organization's business needs at a high level before drilling into specific data requirements. For Stories on CD, Inc., those needs emerge from conversations with Bob, Jane, and key staff members. The designer's role at this stage is to listen, ask clarifying questions, and resist the temptation to start designing solutions before the problem is fully understood.
The business needs identified for Stories on CD, Inc. include the following. The company needs to track both actual customers — those who have placed at least one order — and potential customers — those who have expressed interest but not yet purchased. This distinction matters for marketing, because the Martins run seasonal campaigns and need to target these two groups differently. The company also needs to manage order records that capture not just what was purchased but the channel through which the purchase was made, the applicable discount, and the shipping status for mail-order fulfillments.
Product management is another identified need. The CD catalog must be organized by age group — the company sells titles targeted at children aged 4 to 6, 7 to 10, and 11 to 14 — and each title needs a description suitable for inclusion in the marketing brochures the Martins mail to potential customers several times a year. Distributor information, currently maintained on Rolodex cards by Jane, needs to be captured in the database so that purchasing decisions can be supported with data rather than memory. Finally, the business needs to support seasonal sales analysis so that the Martins can identify which titles sell best in which seasons and adjust their inventory and marketing accordingly.
Multiple iterations of Requirements Analysis will be necessary to clarify details that are not immediately apparent. For example, the initial conversation establishes that the company tracks potential customers, but it does not define exactly what information is collected about a potential customer before they make a purchase, or how long a potential customer remains in the system without converting to an actual customer. These details will emerge in subsequent interviews and will refine the requirements document before design begins.
Key Entities and Attributes
Based on the business needs analysis, the database designer identifies the primary entities that the relational database must represent. An entity is any person, place, thing, or concept about which the organization needs to store data. For Stories on CD, Inc., the following entities are identified along with their key attributes.
The Customer entity requires a Customer ID as its primary key, along with first name, last name, address fields (street, city, state, and ZIP code), phone number, customer type (actual or potential), and order history for actual customers. The Order entity requires an Order ID as its primary key, a Customer ID foreign key to link each order to the customer who placed it, order date, CD ID to identify the product purchased, retail price, discounted price (calculated at 40% off retail), shipping status, and order type indicating whether the purchase was made in-store or by mail. The Product entity — representing individual CD titles — requires a CD ID as its primary key, title, age group classification, retail price, description text for marketing use, and current stock level.
The Distributor entity requires a Distributor ID as its primary key, company name, contact phone number, and a domestic U.S. address. Because all of the Martins' distributors are U.S.-based, international address fields are not required at this stage, though a well-designed schema would accommodate them if the business expanded. The Brochure entity requires a Brochure ID as its primary key, the mailing date on which the brochure was sent, a list of featured CD IDs linking the brochure to specific products, and the descriptive text used in that edition of the brochure.
These entities will be connected through foreign key relationships in the relational database. Customer ID will appear in the Order table as a foreign key, linking each order to the customer who placed it. CD ID will appear in both the Order table and the Brochure table as foreign keys. These relationships enforce referential integrity — a core property of relational databases that prevents orders from referencing customers who do not exist, or brochures from featuring CDs that have been removed from the catalog.
Business Rules and Data Requirements
Business rules are statements that define how data is created, managed, and accessed within the organization. They translate organizational policies into constraints that the database must enforce. Identifying business rules during Requirements Analysis is essential because rules that are not captured at this stage are frequently omitted from the design and must be retrofitted later, often at significant cost.
For Stories on CD, Inc., the following business rules are identified during the requirements gathering process. Each order must be associated with exactly one customer — an order cannot exist without a corresponding customer record. Each order may include one or more CD titles, which means the Order entity will require a related Order Line entity to handle the one-to-many relationship between an order and its line items. Discounted prices are calculated uniformly at 40% off the retail price for all CDs, which means the database can either store the calculated discounted price or derive it from the retail price at query time — a design decision that will be addressed in the logical design stage. Potential customers are stored in the Customer table with a customer type flag but without order history, since they have not yet made a purchase. Distributors are currently all U.S.-based, so only domestic address fields are required.
Data requirements define the queries and reports that the database must support. For Stories on CD, Inc., the identified data requirements include sales trend analysis by season, identification of top-selling CD titles by age group, customer order pattern analysis to support loyalty recognition, and targeted marketing lists segmented by customer type. A normalized database structure — one that eliminates redundancy and enforces data integrity — will ensure that these queries can be executed efficiently and accurately.
Requirements Analysis Activities
The Requirements Analysis stage is not a single conversation — it is a structured set of activities that together produce a complete picture of the organization's data needs. For Stories on CD, Inc., the designer will carry out four primary activities over the course of the module.
Interviewing data users is the starting point. The designer meets with Bob and Jane Martin, the store manager, the mail-order fulfillment staff, and any other stakeholders who interact with data on a regular basis. These interviews surface requirements that would never appear in a top-down analysis because the people doing the day-to-day work understand the edge cases, the exceptions, and the workarounds that the existing system forces them to use. Lesson 6 of this module addresses the interview process in detail.
Creating data flow diagrams maps how data moves through the business. For Stories on CD, Inc., a data flow diagram would trace the path of a mail-order from the moment a customer places the order through fulfillment, shipping, and payment. This mapping reveals which data is created at each step, which data is consumed, and where data crosses organizational boundaries — for example, when shipping information is passed to an external carrier. Lesson 7 covers data flow diagrams.
Defining user views establishes what data each category of user needs to see and in what form. The Martins' sales staff need an order entry view that shows customer information and available inventory. The marketing team needs a customer list view segmented by customer type and purchase history. The fulfillment staff needs a shipping queue view showing pending mail-order items and their status. Each view represents a slice of the full database tailored to a specific role. Lessons 8 and 9 address user views.
Documenting outputs produces the formal deliverables of the Requirements Analysis stage: entity lists with attribute definitions, a complete set of business rules, data flow diagrams, and user view specifications. These documents become the input to the conceptual design stage and serve as the reference against which the final database is validated. Lesson 10 covers requirements documentation.
Common Requirements Analysis Pitfalls
Several failure modes recur consistently in Requirements Analysis projects, and awareness of them helps a designer avoid the most common mistakes. The first is premature design — beginning to sketch table structures or write SQL before the requirements are fully understood. This tends to produce a database that solves the problem as it was understood on day one rather than the problem as it actually exists. The iterative nature of Requirements Analysis exists precisely to correct the designer's initial incomplete understanding.
The second pitfall is interviewing only senior stakeholders. Executives and managers can describe the business at a strategic level, but they frequently do not know the operational details that matter most for database design. The staff member who processes mail-order returns every day knows things about that workflow that the Martins may never have articulated. A thorough Requirements Analysis interviews people at every level of the organization who interact with the data.
The third pitfall is treating the first version of the requirements document as final. Requirements evolve as stakeholders review the documentation and recognize gaps or inaccuracies in what they described. Building in explicit review cycles — where stakeholders read the requirements document and confirm or correct it — is an essential part of the process rather than an optional extra.
What This Module Covers
This module follows the Requirements Analysis process for Stories on CD, Inc. from start to finish. Lesson 2 examines the purpose of Requirements Analysis in more detail, establishing why this stage exists and what a complete Requirements Analysis produces. Lessons 3 through 5 address business objects, their characteristics, and the business rules that govern them. Lessons 6 through 9 cover the four primary Requirements Analysis activities: interviewing data users, creating data flow diagrams, defining user views, and managing calculated fields within those views. Lesson 10 addresses requirements documentation, and Lesson 11 concludes the module with a summary of the complete Requirements Analysis for Stories on CD, Inc.
By the end of this module, the Stories on CD, Inc. case study will have a complete set of requirements ready to hand off to the conceptual design stage, where those requirements will be transformed into an Entity-Relationship Diagram.