Skip to content
BranchesTags

Repository files navigation

Depression Risk Management System

Course: DADS 4002 – Basic Programming and Database Management (2/2568)

A terminal-based application for assessing depression risk, managing person records, running data analytics, and estimating health insurance premiums — backed by a MySQL database and styled with the Rich library.

Table of Contents

  1. Requirements
  2. Setup
  3. How to Run
  4. System Architecture
  5. Role-Based Access
  6. Features
  7. Database Schema
  8. Risk Scoring Logic
  9. File Structure
  10. Logs and Reports

Requirements

  • Python 3.10+
  • MySQL 8.0+
  • Python packages: 
    mysql-connector-python
pandas
matplotlib
rich

Install packages:

pip install mysql-connector-python pandas matplotlib rich

Setup

  1. Create the database in MySQL:

    CREATE DATABASE DADS4002P;

  2. Configure connection — edit .env in the project root:

    host=localhost
user=root
password=your_password
database=DADS4002P

  3. Create tables — run once to set up all 4 tables:

    python Table_dim_fact.py

  4. Load data — either use the in-app Admin → L option, or run:

    python seed.py

    This loads data_file/depression_data.csv (~413,000 rows) into the database.

How to Run

python main.py

The application starts with a role selection menu.

System Architecture

depression_data.csv
        │
        ▼  (Admin → Load CSV)
┌───────────────────┐     ┌──────────────────────────┐
│    dim_person     │────▶│   fact_person_snapshot   │
│  (demographics)   │     │  (links person ↔ health) │
└───────────────────┘     └──────────────────────────┘
                                      │
┌──────────────────────────┐          │
│   dim_health_behavior    │◀─────────┘
│  (lifestyle/health data) │
└──────────────────────────┘

┌───────────────────┐
│  fact_assessment  │  ← stores every risk assessment run
└───────────────────┘

The CSV is normalized into a star schema on load:

  • Person demographics → dim_person
  • Health/lifestyle data → dim_health_behavior
  • The link between them → fact_person_snapshot

Role-Based Access

When you start the app, you choose a role:

Feature Admin User
Depression Risk Assessment ✓ ✓
Insurance Premium Calculator ✓ ✓
CRUD (Create/Read/Update/Delete) ✓ ✗
Person Risk Lookup ✓ ✗
Data Analytics ✓ ✗
Load CSV ✓ ✗
Batch Insurance Analytics ✓ ✗

Features

1. Depression Risk Assessment

Menu key: I (both roles)

A step-by-step questionnaire that collects 9 health behavior factors and calculates a depression risk score out of 17.

Questions asked:

Factor Options
Smoking Status Non-smoker / Former / Current
Physical Activity Active / Moderate / Sedentary
Alcohol Consumption Low / Moderate / High
Dietary Habits Healthy / Moderate / Unhealthy
Sleep Patterns Good / Fair / Poor
History of Mental Illness Yes / No
History of Substance Abuse Yes / No
Family History of Depression Yes / No
Chronic Medical Conditions Yes / No

Output:

  • Risk score (0–17) and risk level (LOW / MODERATE / HIGH)
  • Table of contributing risk factors with points
  • Personalized recommendation
  • Database context — compares your profile against similar records in the database
  • Assessment saved to fact_assessment table and logs/system_log.txt

User role only: A 4-panel matplotlib chart is shown after the result:

  • Semicircular gauge showing score out of 17
  • Horizontal bar chart of contributing factors
  • Donut chart showing risk percentage
  • Suggestions panel with healthy behavior notes

2. CRUD — Person Records

Admin only

Create (C)

Adds a new person to dim_person. Prompts for:

  • Name, Age
  • Marital Status (Single / Married / Divorced / Widowed)
  • Education Level (High School / Associate / Bachelor's / Master's / PhD)
  • Number of Children
  • Employment Status (Employed / Unemployed / Self-Employed / Student / Retired)
  • Income

Read (R)

Displays all records in dim_person as a Rich-styled table with ID, name, demographics, and income.

Update (U)

Enter a person_id and leave any field blank to skip it. Only the fields you fill in are updated.

Delete (D)

Enter a person_id. The record is removed from fact_person_snapshot first (child rows), then from dim_person (parent row) to preserve referential integrity.

3. Person Risk Lookup

Menu key: P (Admin only)

Enter any person_id to see their full profile plus a calculated depression risk score — all in one view:

  • Demographics panel (name, age, marital status, education, children, employment, income)
  • Health Behavior table with color-coded values (green = low risk, yellow = moderate, red = high risk)
  • Risk Assessment panel showing the computed score and level based on their stored health data

4. Data Analytics

Menu key: A (Admin only)

Five SQL-powered insights, each displayed as a Rich table with an actionable finding, and saved as a .txt report to logs/.

# Insight What it answers
1 Employment Status & Mental Health Risk Which employment group has the highest combined rate of mental illness and family depression history?
2 Sleep Patterns & Lifestyle Risk Correlation How does sleep quality relate to substance abuse, high alcohol use, and sedentary lifestyle?
3 Age Group Analysis – Income & Mental Health Which age groups have the highest mental illness rates and the lowest average income?
4 Depression Risk Overview What percentage of all records fall into HIGH / MODERATE / LOW risk, broken down by employment status?
5 Compound Primary Risk Factor Analysis How does carrying multiple primary risk factors (mental illness, substance abuse, family history, chronic conditions) affect overall depression risk?

Select 6 – Run All Analyses to execute all five in sequence.

Each insight generates a .txt report saved to logs/ (e.g. insight1_employment_20260428.txt), one file per day per insight.

5. Load CSV Data

Menu key: L (Admin only)

Loads data_file/depression_data.csv (~413,000 rows) into the database in three stages:

  1. dim_person — unique person demographics
  2. dim_health_behavior — unique health behavior combinations
  3. fact_person_snapshot — links person ↔ health behavior

Uses upsert logic (INSERT ... ON DUPLICATE KEY UPDATE) so re-loading the CSV does not create duplicates. Progress is shown per table with inserted vs. updated row counts.

6. Insurance Premium Calculator

Menu key: N (both roles)

Estimates annual health insurance premiums based on lifestyle risk factors using a rule-based multiplier model.

Interactive Mode (User role / Admin option 1)

Enter your personal details:

  • Age (used to determine base rate by age group)
  • Smoking status, Alcohol consumption, Sleep patterns, Physical activity, Dietary habits
  • Chronic conditions, Mental illness history, Substance abuse history, Family depression history

Output:

  • Summary panel: age group, base rate, total multiplier, risk tier, monthly and annual premium estimate
  • Risk Factor Breakdown table: each factor's multiplier and dollar impact
  • Savings Tips table: specific actions you can take to lower your premium and estimated annual saving

Risk Tiers:

Tier Condition
Standard Total multiplier ≤ 1.30×
Rated Total multiplier 1.31× – 1.75×
High-Risk Total multiplier > 1.75×

Batch CSV Mode (Admin option 2)

Runs the premium calculation on every row in depression_data.csv and displays:

  • Overall risk tier distribution (Standard / Rated / High-Risk) with counts, percentages, and average premiums
  • Average premium breakdown by employment status
  • Average premium breakdown by age group

Results are saved to insurance_results.csv in the project root.

Database Schema

dim_person

Column Type Description
person_id INT (PK, AUTO) Unique identifier
name VARCHAR(255) Full name
age INT Age in years
marital_status VARCHAR(100) Single / Married / Divorced / Widowed
education_level VARCHAR(100) High School → PhD
number_of_children INT Number of children
employment_status VARCHAR(100) Employed / Unemployed / etc.
income DECIMAL(12,2) Annual income

dim_health_behavior

Column Type Description
health_behavior_id INT (PK, AUTO) Unique identifier
smoking_status VARCHAR(50) Non-smoker / Former / Current
physical_activity_level VARCHAR(50) Active / Moderate / Sedentary
alcohol_consumption VARCHAR(50) Low / Moderate / High
dietary_habits VARCHAR(100) Healthy / Moderate / Unhealthy
sleep_patterns VARCHAR(100) Good / Fair / Poor
history_of_mental_illness VARCHAR(10) Yes / No
history_of_substance_abuse VARCHAR(10) Yes / No
family_history_of_depression VARCHAR(10) Yes / No
chronic_medical_conditions VARCHAR(10) Yes / No

fact_person_snapshot

Column Type Description
snapshot_id INT (PK, AUTO) Unique identifier
person_id INT (FK) References dim_person
health_behavior_id INT (FK) References dim_health_behavior
record_count INT Number of CSV rows this combination represents

fact_assessment

Column Type Description
assessment_id INT (PK, AUTO) Unique identifier
assessment_date DATETIME Timestamp of assessment
smoking_status … chronic_medical_conditions VARCHAR The 9 health behavior inputs
risk_score INT Calculated score (0–17)
risk_level VARCHAR(20) LOW / MODERATE / HIGH

Risk Scoring Logic

Each factor adds points to a total score out of 17:

Factor Value Points
History of Mental Illness Yes +3
Family History of Depression Yes +2
History of Substance Abuse Yes +2
Sleep Patterns Poor +2
Sleep Patterns Fair +1
Alcohol Consumption High +2
Alcohol Consumption Moderate +1
Smoking Status Current +2
Smoking Status Former +1
Physical Activity Sedentary +1
Chronic Medical Conditions Yes +1
Dietary Habits Unhealthy +1

Risk Levels:

Score Level
0 – 4 LOW
5 – 8 MODERATE
9 – 17 HIGH

This tool provides an estimate based on known risk factors. It is not a medical diagnosis.

File Structure

NIDA_PROJ/
├── main.py                   # Entry point — role menu and routing
├── crud.py                   # CRUD operations + CSV loader
├── identify_depression.py    # Risk assessment + matplotlib chart
├── analytics.py              # 5 SQL analytics insights
├── logger.py                 # Logging to logs/system_log.txt + report saver
├── ui.py                     # Shared Rich helpers (console, styles)
├── .env                      # Database connection config (not committed to git)
├── Table_dim_fact.py         # Creates the 4 MySQL tables (run once)
├── seed.py                   # Loads depression_data.csv into the DB
├── insurance/
│   ├── insurance_menu.py     # Insurance UI — interactive + batch modes
│   ├── base_rates.py         # Age group base rates and tier thresholds
│   └── premium_calculator.py # Multiplier calculation logic
├── data_file/
│   └── depression_data.csv   # Source dataset (~413k rows)
├── logs/                     # Auto-created — system log + analytics reports
└── insurance_results.csv     # Auto-created on batch run

Logs and Reports

All logs are written to the logs/ directory (created automatically on first run).

File Content
system_log.txt Timestamped event log for every action (role, assessment, analytics, CSV load)
insight1_employment_YYYYMMDD.txt Analytics report — Employment & Mental Health
insight2_sleep_YYYYMMDD.txt Analytics report — Sleep & Lifestyle
insight3_agegroup_YYYYMMDD.txt Analytics report — Age Group Analysis
insight4_risk_overview_YYYYMMDD.txt Analytics report — Risk Distribution
insight5_compound_risk_YYYYMMDD.txt Analytics report — Compound Risk Factors

One report file is created per insight per day. Re-running the same insight on the same day overwrites the file.

About

THIS IS A PART OF PROJECT NIDA5002

Topics

data-analysis-projectdepression-detectionhealthcare-analyticsmysqlnida-projectpythonrisk-assessment

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Used by

Contributors

Languages