🏦 Food Bank Inventory System

A relational database system for managing multiple food banks, their inventories, donors, recipients, and food distributions. Built as part of CMPE 172 — Enterprise Software at San José State University.

🎯 Overview

This project models a complete food bank management system using a relational database with 8 interconnected tables, 23 SQL queries (Basic → Intermediate → Advanced), and mathematical notation for relational algebra operations.

✨ Features

• Multi-location Management — Track and manage multiple food banks simultaneously
• Donor & Recipient Tracking — Full CRUD for donors, recipients, and food items
• Inventory Monitoring — Real-time stock levels per food bank with low-stock alerts
• Distribution Logging — Record food distributions from banks to recipients
• Inter-bank Transfers — Log inventory transfers between food banks
• Reporting — Total inventory per bank, most transferred items, top donors
• Relational Algebra — Mathematical notation for projection, selection, and join operations

🗄️ Database Schema

┌──────────────┐    ┌──────────────┐    ┌──────────────────┐
│  Food_Bank   │    │    Donor     │    │    Food_Item     │
├──────────────┤    ├──────────────┤    ├──────────────────┤
│ store_id (PK)│    │ donor_id (PK)│    │ item_id (PK)     │
│ name         │    │ name         │    │ name             │
│ address      │    │ type         │    │ category         │
└──────┬───────┘    └──────────────┘    └────────┬─────────┘
       │                                         │
       ▼                                         ▼
┌──────────────────┐              ┌──────────────────────┐
│ Inventory_Entry  │              │   Distribution       │
├──────────────────┤              ├──────────────────────┤
│ food_bank (FK)   │              │ distribution_id (PK) │
│ item (FK)        │              │ food_bank (FK)       │
│ quantity         │              │ recipient (FK)       │
└──────────────────┘              └──────────────────────┘

🛠️ Tech Stack

Component	Technology
Database	MySQL
Application	Java
Queries	23 SQL scripts (Basic, Intermediate, Advanced)
Modeling	ER Diagrams, Relational Algebra

📁 SQL Queries

Basic (5 Queries)

File	Description
BasicQ1FoodbankByName.sql	List all food banks sorted alphabetically
BasicQ2InventoryEntry.sql	Count inventory entries per item
BasicQ3NameWithS.sql	Recipients whose name starts with "S"
BasicQ4StaffNameAndPlace.sql	Staff names and their food bank
BasicQ5InventoryIDandName.sql	Items with quantity < 100

Intermediate (5 Queries)

File	Description
IntermediateQ1Donations.sql	Food items with donor info and quantities
IntermediateQ2RecipientsAndLocation.sql	Recipients and their distributing bank
IntermediateQ3ShelfLife.sql	Items expiring within 6 months
IntermediateQ4Staff&Roles.sql	Staff, food bank, and role details
IntermediateQ5LastDist.sql	Most recent distribution per recipient

Advanced (5 Queries)

File	Description
AdvancedQ1Top3Donors.sql	Top 3 donors by distributed quantity
AdvancedQ2HighestCanned.sql	Bank with most canned goods distributed
AdvancedQ3HigherThanAvg.sql	Banks distributing above average
AdvancedQ4Top5Highest.sql	Top 5 banks by total distributed items
AdvancedQ5MoreThanAvgRecieved.sql	Recipients above average received items

Mathematical Notation (8 Queries)

Relational algebra representations using projection (π), selection (σ), and join (⋈) operations.

🚀 Getting Started

Prerequisites

• MySQL Workbench or any MySQL client
• Java JDK 11+

Setup

git clone https://github.com/amanimran786/FoodBankInventorySystem.git
cd FoodBankInventorySystem

1. Open MySQL Workbench and connect to your server
2. Run the CREATE DATABASE script
3. Run the insert scripts to populate tables
4. Execute queries from Basic → Intermediate → Advanced

👥 Authors

• Aman Imran — aman.imran@sjsu.edu | Portfolio
• Albert Van — albertvan755891@gmail.com

📄 License

This project is open source and available for educational purposes.