Lesson 1

Requirements Analysis is the first and most critical stage of the Database Life Cycle (DBLC). This stage involves gathering detailed information about the data a client needs to store and the conditions under which that data will be accessed. It requires multiple iterations to capture all necessary details, as initial discussions often reveal only part of the requirements. The goal is to create a comprehensive foundation for designing a database that meets the organization’s needs.

Learning Objectives for Lesson 1:

1. Explain the purpose of Requirements Analysis.
2. Identify business objects and describe their characteristics.
3. Explain the importance of business rules.
4. Explain the purpose of interviewing data users.
5. Explain the purpose of a data flow diagram.
6. List reasons for creating user views.
7. Describe the documents produced during Requirements Analysis.

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 in-store (30%) and mail-order (70%) channels. Founded by Bob and Jane Martin, the company has experienced growth, particularly during seasonal peaks, but its current flat-file database, built by their son Ted, struggles with duplicate data and inefficient deletions. The Martins aim to transition to a relational database to support scalability and improve operations. This case study illustrates how Requirements Analysis is applied to identify their database needs.

Business Needs Analysis

The company’s growth highlights the need for a scalable database to manage customers, orders, products, distributors, and marketing efforts. Key requirements include:

• Tracking both actual and potential customers to support marketing initiatives.
• Managing orders with details like discounts (40% off retail) and shipping status.
• Organizing product information for CDs, including descriptions for marketing brochures.
• Storing distributor details currently maintained on Rolodex cards.
• Supporting seasonal sales analysis and targeted marketing campaigns.

Multiple iterations of Requirements Analysis will be necessary to clarify details, such as defining “potential customers” or prioritizing distributor data fields.

Key Entities and Attributes

Based on the business needs, the following entities and their attributes are identified for the database:

• Customers: Customer ID, first name, last name, address (street, city, state, ZIP), phone number, customer type (actual or potential), order history.
• Orders: Order ID, Customer ID, order date, CD ID, retail price, discounted price (40% off), shipping status, order type (mail-order or in-store).
• Products (CDs): CD ID, title, age group (4–6, 7–10, 11–14), retail price, discounted price, description, stock level.
• Distributors: Distributor ID, name, contact phone, address (street, city, state, ZIP).
• Brochures: Brochure ID, mailing date, featured CDs (linked to CD IDs), description text.

These entities form the basis of a relational database, with primary keys (e.g., Customer ID, Order ID) and foreign keys (e.g., Customer ID in Orders) to link tables.

Business Rules and Data Requirements

Business rules define how data is managed and accessed. Examples for Stories on CD, Inc. include:

• Each order must be linked to one customer and one or more CDs.
• Discounted prices are calculated as 40% off the retail price for all CDs.
• Potential customers are stored without order history but targeted for marketing.
• Distributors are U.S.-based, requiring only domestic address fields.

Data requirements include supporting queries for sales trends by season, top-selling CDs by age group, customer order patterns, and marketing targets for potential customers. A normalized database structure will ensure efficient retrieval for these queries.

Requirements Analysis Process

The Requirements Analysis stage involves several activities to gather this information:

• Interviewing Data Users: Engage stakeholders (e.g., Bob, Jane, store staff) to understand workflows, data needs, and pain points with the current system.
• Creating Data Flow Diagrams: Map how data moves through the business (e.g., from customer order to shipping) to identify key processes and data interactions.
• Defining User Views: Develop views for different users (e.g., sales staff need order details, marketing needs customer lists) to ensure the database supports their tasks.
• Documenting Outputs: Produce requirements documents, including entity lists, attribute definitions, business rules, and data flow diagrams, to guide the next DBLC stages.

Next Steps

This analysis lays the groundwork for designing a relational database for Stories on CD, Inc. The next lesson will focus on creating a data flow diagram to further refine the requirements and map business processes.