FUNCTIONAL REQUIREMENTS DOCUMENT (FRD)
Project: FinTech Fraud Detection & Risk Analytics
|
Document Info |
Details |
|
Version |
1.0 |
|
Author |
Priyanka Chaudhari, Business Analyst |
|
Based On |
BRD v1.0 |
|
Status |
Approved |
1. FUNCTIONAL REQUIREMENTS, DATA LAYER
FR-01: Transaction Schema
|
Field Name |
Data Type |
Description |
Required |
|
transaction_id |
TEXT (PK) |
Unique transaction identifier |
Yes |
|
customer_id |
TEXT |
Customer reference ID |
Yes |
|
transaction_date |
DATE |
Date of transaction (YYYY-MM-DD) |
Yes |
|
transaction_time |
TIME |
Time of transaction (HH:MM:SS) |
Yes |
|
merchant_name |
TEXT |
Merchant name |
Yes |
|
merchant_category |
TEXT |
Merchant business category |
Yes |
|
transaction_amount |
DECIMAL(10,2) |
Transaction value in USD |
Yes |
|
state |
TEXT |
US state of transaction |
Yes |
|
hour_of_day |
INTEGER |
Hour extracted from transaction_time (0-23) |
Yes |
|
is_fraud |
INTEGER |
Fraud flag: 1=Fraud, 0=Legitimate |
Yes |
|
fraud_reason |
TEXT |
Reason for fraud flag (if applicable) |
No |
|
status |
TEXT |
Transaction status: Approved/Declined/Flagged |
Yes |
2. FUNCTIONAL REQUIREMENTS, SQL ANALYSIS
|
Req ID |
Query Name |
Description |
Output |
|
FR-SQL-01 |
KPI Overview |
Total txns, fraud count, fraud rate %, exposure, volume |
Single summary row |
|
FR-SQL-02 |
Category Analysis |
Fraud rate and exposure grouped by merchant_category |
Ranked table by fraud rate |
|
FR-SQL-03 |
Time-of-Day Analysis |
Fraud rate grouped by hour_of_day, top 10 highest-risk hours |
Top 10 hours table |
|
FR-SQL-04 |
State Risk Ranking |
Fraud exposure and rate by state |
State ranking table |
|
FR-SQL-05 |
Amount Bucket Analysis |
Fraud rate across 5 amount tiers (<$50 to >$1K) |
Bucket comparison table |
|
FR-SQL-06 |
Customer Profiling |
Top 10 customers by fraud exposure |
High-risk customer list |
|
FR-SQL-07 |
Monthly Trend |
Fraud rate and monthly loss by calendar month |
12-month trend table |
|
FR-SQL-08 |
Fraud Reason Breakdown |
Count and exposure by fraud_reason category |
Reason analysis table |
|
FR-SQL-09 |
Merchant Risk Scorecard |
CREATE VIEW classifying merchants HIGH/MEDIUM/LOW risk |
Reusable view object |
3. FUNCTIONAL REQUIREMENTS, EXCEL WORKBOOK
|
Req ID |
Tab Name |
Contents |
Chart Required |
|
FR-XL-01 |
Executive Summary |
7 KPIs with benchmark and status, 5 findings, recommendations |
No |
|
FR-XL-02 |
Fraud by Category |
Category-level fraud rate, txn count, exposure, risk tier |
Yes, bar chart: fraud rate by category |
|
FR-XL-03 |
Amount Risk Buckets |
5 amount tiers with fraud rate and avg fraud amount |
No |
|
FR-XL-04 |
Monthly Trend |
12-month fraud rate and monthly loss table |
Yes, column chart: fraud rate by month |
4. FUNCTIONAL REQUIREMENTS, POWER BI DASHBOARD
|
Req ID |
Page |
Visuals Required |
Slicers |
|
FR-PBI-01 |
Executive Overview |
3 KPI cards, bar chart by category, line chart by month |
Status, Date |
|
FR-PBI-02 |
Merchant Risk Scorecard |
Matrix: merchant x category, treemap by exposure |
State, Category |
|
FR-PBI-03 |
Time & Amount Analysis |
Column chart by hour, bar chart by amount bucket |
Category, Date range |
5. NON-FUNCTIONAL REQUIREMENTS
|
Req ID |
Category |
Requirement |
|
NFR-01 |
Performance |
SQL queries must return results in < 5 seconds on 5K row dataset |
|
NFR-02 |
Usability |
Power BI dashboard must be operable without SQL knowledge |
|
NFR-03 |
Maintainability |
All SQL queries documented with inline comments |
|
NFR-04 |
Portability |
Dataset provided as CSV, no database server dependency for reviewers |
|
NFR-05 |
Traceability |
All requirements traceable to BRD objectives via RTM |
|
NFR-06 |
Documentation |
BRD, FRD, RTM, User Stories delivered as standalone .docx files |
6. DATA VALIDATION RULES
|
Field |
Validation Rule |
Action on Failure |
|
transaction_amount |
Must be > 0 |
Reject record, log error |
|
is_fraud |
Must be 0 or 1 only |
Reject record, flag for review |
|
hour_of_day |
Must be 0-23 integer |
Derive from transaction_time or reject |
|
status |
Must be Approved/Declined/Flagged |
Default to Unknown, flag for review |
|
transaction_date |
Must be valid date format YYYY-MM-DD |
Reject record, log error |
|
merchant_category |
Must match approved category list |
Flag as Uncategorized |