Skip to content

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

Β 

History

3 Commits

Folders and files

Repository files navigation

RushMore Pizzeria Enterprise Database System πŸ“‹ Project Overview This project successfully replaces RushMore Pizzeria's fragile JSON-based ordering system with a robust, cloud-based PostgreSQL database solution deployed on Microsoft Azure. The system handles multiple locations, complex menu management, inventory tracking, and provides comprehensive analytics capabilities while maintaining data integrity and customer privacy through synthetic data generation.

πŸ—οΈ Architecture & Technology Stack Cloud Provider: Microsoft Azure

Database: PostgreSQL 17 (Azure Database for PostgreSQL Flexible Server)

Programming Language: Python 3.8+

Data Generation: Faker library for synthetic data

Configuration Management: Environment variables (.env)

Database Connection: psycopg2 with SSL encryption

Diagram Tool: Draw.io for Entity-Relationship Diagram

πŸ“Š Database Schema Design Entity-Relationship Diagram https://docs/ERD.png

Tables Structure Core Business Tables Stores - Pizza location information (5 stores)

Customers - Customer PII and contact details (1,000+ customers)

Menu_Items - Product catalog (25+ items across pizzas, drinks, sides)

Ingredients - Raw materials inventory (40+ ingredients)

Transaction Tables Orders - Master transaction records (5,000+ orders)

Order_Items - Order line items (15,000+ line items)

Relationship Tables Menu_Item_Ingredients - Many-to-many relationship for recipes

Key Design Principles 3rd Normal Form compliance

Referential Integrity with proper foreign key constraints

Cascade behaviors for data consistency

Performance optimization with strategic indexing

πŸš€ Implementation Steps Phase 1: Database Design & Modeling βœ… Completed Tasks:

Designed normalized database schema

Rushmore_postgresql_azuredb

Created comprehensive ERD using draw.io

Schema Diagram drawio

Defined table relationships and constraints

Wrote complete SQL schema creation script

Phase 2: Cloud Deployment on Azure βœ… Completed Tasks:

Provisioned Azure Database for PostgreSQL Flexible Server

Configured networking and security settings

Set up firewall rules for secure access

Deployed empty database schema to cloud

Rushmore_pizzeria_server

Phase 3: Data Population & Masking βœ… Completed Tasks:

Developed Python data generation script using Faker image

Implemented environment-based configuration image

Generated realistic synthetic data:

5 physical store locations

1,000+ customer records

40+ ingredients with inventory tracking

25+ menu items across categories

5,000+ customer orders

15,000+ order line items

Maintained referential integrity during population image

Phase 4: Validation & Testing βœ… Completed Tasks:

Data integrity validation

Relationship verification

Performance testing with large dataset

Connection security with SSL

πŸ“ Project Structure text rushmore-pizzeria-db/ β”œβ”€β”€ sql/ β”‚ β”œβ”€β”€ schema.sql # Complete database schema β”‚ └── analysis_queries.sql # Business intelligence queries β”œβ”€β”€ scripts/ β”‚ └── populate_database.py # Data population script β”œβ”€β”€ config/ β”‚ └── .env.example # Environment template β”œβ”€β”€ docs/ β”‚ β”œβ”€β”€ ERD.png # Entity-Relationship Diagram β”‚ β”œβ”€β”€ azure_deployment_screenshots/ β”‚ └── data_validation_screenshots/ β”œβ”€β”€ requirements.txt # Python dependencies └── README.md # This document πŸ› οΈ Setup & Installation Prerequisites Python 3.8 or higher

Azure account with PostgreSQL access

Git for version control

Step 1: Environment Setup bash

Clone repository

git clone cd rushmore-pizzeria-db

Create virtual environment

python -m venv venv venv\Scripts\activate # Windows

Install dependencies

pip install -r requirements.txt Step 2: Azure Database Configuration Create Azure Database for PostgreSQL Flexible Server

Configure firewall to allow your IP address

Create database named rushmore_db

Note connection details from Azure Portal

Step 3: Local Configuration bash

Copy environment template

copy config.env.example .env

Edit .env with your Azure credentials

DB_HOST=your-server.postgres.database.azure.com DB_NAME=rushmore_db DB_USER=your-admin-user DB_PASSWORD=your-secure-password DB_PORT=5432 DB_SSLMODE=require Step 4: Database Deployment sql -- Execute schema creation \i sql/schema.sql Step 5: Data Population bash

Populate with synthetic data

python scripts/populate_database.py

Test connection only

python scripts/populate_database.py --test-connection

Keep existing data

python scripts/populate_database.py --keep-existing

πŸ” Business Intelligence & Analytics Pre-built Analytical Queries The system includes comprehensive SQL queries for business analysis:

  1. Financial Performance Total revenue per store

Average order value

Monthly revenue trends

Customer lifetime value

  1. Customer Insights Top 10 most valuable customers
Top10MostValue

Customer retention rates

New vs returning customer analysis

  1. Product Performance Most popular menu items
MostPopularMenuItem

Category performance analysis

Ingredient usage patterns

  1. Operational Metrics Busiest hours and days
BusiestHour

Store performance comparison

Order volume trends

Sample Analytics Output sql -- Top selling menu items SELECT mi.name, mi.category, SUM(oi.quantity) as total_sold, SUM(oi.quantity * oi.unit_price) as total_revenue FROM Menu_Items mi JOIN Order_Items oi ON mi.item_id = oi.item_id GROUP BY mi.item_id, mi.name, mi.category ORDER BY total_sold DESC LIMIT 10;

πŸ”’ Security & Compliance Data Protection Synthetic Data Generation: No real customer PII used

Environment Variables: Secure credential management

SSL Encryption: Encrypted database connections

Azure Security: Enterprise-grade cloud security

Privacy by Design GDPR-compliant data modeling

PII isolation in Customers table

Audit trails for all transactions

Secure data masking practices

πŸ“ˆ Performance & Scalability Optimization Features Strategic Indexing: Performance-optimized queries

Connection Pooling: Ready for high-traffic applications

Azure Scaling: Flexible compute and storage options

Query Optimization: Efficient analytical queries

Scalability Ready Supports 10,000+ concurrent customers

Handles 50,000+ monthly orders

Inventory management for 100+ ingredients

Multi-location expansion ready

🎯 Key Achievements βœ… Successfully Implemented Production-Ready Database: Fully normalized, cloud-deployed RDBMS

Data Integrity: Referential integrity with proper constraints

Scalable Architecture: Azure-based with growth capacity

Business Intelligence: Comprehensive analytics capabilities

Security Compliance: Enterprise-grade security practices

Documentation: Complete technical and operational documentation

πŸ“Š Data Generation Results Stores: 5 locations across major cities

Customers: 1,000+ synthetic customer profiles

Ingredients: 40+ raw materials with inventory tracking

Menu Items: 25+ products across categories

Orders: 5,000+ transaction records

Order Items: 15,000+ line items

Recipes: Complete ingredient mapping for all menu items

πŸš€ Future Enhancements Planned Capabilities Real-time Analytics: Power BI integration

Mobile API: RESTful API for mobile applications

Inventory Automation: Real-time stock level updates

Customer Loyalty Program: Points and rewards system

Predictive Ordering: Machine learning for demand forecasting

Multi-language Support: International expansion ready

πŸ› οΈ Troubleshooting Guide Common Issues & Solutions Connection Issues bash

Test DNS resolution

python scripts/populate_database.py --test-connection

Verify environment variables

echo %DB_HOST% # Windows Data Population Issues bash

Clear and repopulate

python scripts/populate_database.py

Check database logs in Azure Portal

Performance Optimization sql -- Check query performance EXPLAIN ANALYZE SELECT * FROM Orders WHERE order_timestamp > NOW() - INTERVAL '30 days'; πŸ“ž Support & Maintenance Operational Support Database Monitoring: Azure Metrics and Alerts

Regular Backups: Automated Azure backups

Performance Tuning: Quarterly query optimization

Security Updates: Regular patch management

Technical Support For technical issues, refer to:

Azure PostgreSQL documentation

Project troubleshooting guide

Database administration team

πŸŽ“ Learning Outcomes This project demonstrates comprehensive skills in:

Cloud Database Management: Azure PostgreSQL deployment and configuration

Data Modeling: Normalized database design and ERD creation

Python Programming: Data generation and database interaction

SQL Expertise: Complex query writing and optimization

DevOps Practices: Environment management and deployment automation

Business Intelligence: Analytical query design and implementation

πŸ“„ License & Attribution This project is developed as a capstone project for RushMore Pizzeria. All code and documentation are provided for educational and demonstration purposes.

πŸ”— Quick Start Summary bash

1. Setup

git clone python -m venv venv venv\Scripts\activate pip install -r requirements.txt

2. Configure

copy .env.example .env

Edit .env with Azure credentials

3. Deploy

Run schema.sql in Azure PostgreSQL

4. Populate

python scripts/populate_database.py

5. Analyze

Run queries from sql/analysis_queries.sql

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages