Project Scoping, Requirements Analysis, and Architecture Planning

Why Scoping and Planning Come First

Every database project that runs over budget, misses requirements, or ships with security holes shares a common root cause: someone started building before the problem was fully understood. In professional database administration, the planning phase is not optional overhead — it is the foundation that every later decision rests on. For your capstone, no build work begins until your project charter and entity-relationship diagram (ERD) carry an instructor's signature. This lesson explains what those documents are, how to produce them correctly, and why the sequence matters.

Understanding the Business Scenario

Your scenario centers on a regional nonprofit that manages donor records, grant funding cycles, and program delivery data. Before you can design a single table, you must understand what the organization actually does and what it needs from a database system. This is requirements analysis — the disciplined process of discovering, documenting, and validating what stakeholders need before any technical solution is chosen.

Requirements analysis starts with stakeholder interviews. A stakeholder is anyone who will use, fund, maintain, or be affected by the system. For this nonprofit, stakeholders typically include program staff who enter data daily, finance personnel who track grant expenditures, executive leadership who need summary reports, and possibly auditors or grant funders who require access to specific records. Use the structured interview worksheet your instructor provides. Ask open questions: What decisions do you make using data today? What data do you wish you had? What must the system never allow to happen? Document answers verbatim before interpreting them.

From those notes, draft a requirements specification. A useful one-page spec identifies at minimum: the entities the system must track (for this scenario, expect at minimum Donor, Donation, Grant, Program, Participant, and Staff); external data sources the system must connect to or import from (for example, a payment processor export file and a state program-eligibility database); the user roles and what each role is permitted to do (such as Data Entry Clerk, Finance Manager, and Read-Only Auditor); and any regulatory constraint that governs data handling. Nonprofits that receive federal grants often operate under data-retention and privacy requirements, and organizations handling health-related program data may fall under HIPAA or state privacy statutes. Identify the constraint by category — you do not need to cite a specific code section unless you are certain of it — but the constraint must appear in your requirements document because it will drive security boundary decisions in your architecture.

Writing the Project Charter

A project charter is a brief formal document that defines scope, objectives, assumptions, constraints, stakeholders, and deliverables. It exists to create shared understanding and prevent scope creep — the gradual addition of features that inflate cost and timeline without a deliberate decision. Your charter should answer four questions clearly: What will the system do? What is explicitly out of scope? Who approves changes to scope? What does "done" look like for each deliverable?

Scope boundaries are as important as what is included. For example, your charter might state that the system will store and report on donor and grant data but will not replace the organization's accounting software or provide public-facing web intake forms. Writing that boundary down prevents a well-meaning stakeholder from assuming those features are coming.

Drawing a Crow's-Foot ERD to Third Normal Form

An entity-relationship diagram is the visual language of data modeling. The crow's-foot notation shows entities as rectangles, attributes listed inside, and relationships as lines with symbols at each end indicating cardinality (one, many) and participation (mandatory, optional). You will draw yours using draw.io or pgModeler, both of which support crow's-foot style.

Normalization is the process of organizing data to reduce redundancy and protect integrity. Third normal form (3NF) requires that: every non-key attribute depends on the whole primary key (second normal form), and every non-key attribute depends only on the primary key, not on another non-key attribute (eliminating transitive dependencies). For example, storing a grant funder's mailing address in the Grant table rather than in a separate Funder entity would violate 3NF if that funder appears on multiple grants — the address would be repeated and could diverge across rows.

Annotate each entity with: the primary key (underlined or marked PK), any foreign keys (marked FK), the data type for each attribute (for example, VARCHAR(100), DATE, NUMERIC(12,2), BOOLEAN), and nullability — whether the column is required (NOT NULL) or optional. These annotations are not decorative; they directly inform the CREATE TABLE statements you will write in the build phase.

During peer review, use the rubric your instructor provides. Look specifically for missing primary keys, foreign key relationships that are implied in the scenario but not drawn, attributes that should be in a separate entity (a sign of unnormalized design), and data types that do not match the real-world domain of the data. Document at least two findings with the entity name, the issue, and a suggested correction. Constructive peer review is a professional skill valued on the job and assessed in industry certifications.

Architecture Planning and Security Boundaries

Your architecture diagram shows how components connect: the database server, application tier (if any), user workstations, external data sources, and network boundaries. It must answer: Where does data live — cloud-hosted (such as Amazon RDS, Azure Database for PostgreSQL, or Oracle Cloud) or on-premises? Who can reach the database server from the network? Where do encryption boundaries sit (data in transit, data at rest)? How are backups stored and who can access them?

Security boundaries are lines on your diagram that separate zones of trust. A database server should never be directly reachable from the public internet; it should sit behind an application layer or bastion host. Role-based access control (RBAC) maps your three user roles to specific database privileges — principle of least privilege means each role receives only the permissions it genuinely needs. Document these decisions in your charter so they are approved before build, not discovered after a security review.

Proceeding to Sign-Off

Present your draft charter and ERD at the 15-minute desk review. Bring printed or screen-shared versions of both documents. Listen to written feedback, revise, and resubmit. You must achieve a rubric score of 80 percent or higher on completeness, normalization correctness, and clarity before you are cleared to begin Lesson 2. This gate exists because a flaw in the data model is far cheaper to fix on paper than in a populated database.