Quick Links: Github Repo

Table of Contents:

1. Introduction

2. Process

3. Results

4. Learnings

Introduction:

This project involved designing and implementing a database system for the Patient Assistance Network (PAN), a non-profit organization supporting patients through personalized care and resources. The database tracks a wide variety of organizational data, including clients, employees, volunteers, donors, and their interactions. By combining expansive database design, SQL query development, and Java-based application functionality, this project enables PAN to manage operations efficiently and retrieve critical information to drive decision-making.

• Goal: The primary objectives of the project were to:

  • Design and implement a scalable database system to track PAN’s clients, volunteers, employees, donors, and teams.

  • Develop SQL queries and procedures to handle common operations, such as managing personnel, tracking donations and expenses, and generating reports.

  • Build a user-friendly Java application that integrates seamlessly with an Azure SQL database to execute queries and manage data.

• Key Questions:

  • How can PAN efficiently manage and query data for clients, teams, and personnel?

  • What reports can be generated to provide actionable insights, such as total expenses by employee or donors who also work as employees?

  • How can the system ensure scalability for growing operational needs?

Process:

• Tools: Azure SQL Database for database creation and query execution and Eclipse IDE with JDBC for developing the Java application to interact with the database.

• Methodology:

  • Database Design: Created tables with appropriate constraints and indexing strategies for optimized performance. This included careful mapping of the data requirements, such as tracking emergency contacts, team memberships, and donations.

  • Query Development: Wrote 15 key SQL queries to handle database operations, including adding personnel, generating reports, and performing bulk updates.

  • Java Application: Developed an interactive program with a user-friendly menu, allowing users to execute queries, import/export data, and manage the database.

  • Time Management: To meet the project deadline, I divided tasks into smaller, manageable goals with estimated completion times. This structured approach helped ensure steady progress and allowed time for testing and debugging.

Results:

The completed system supports:

• Data Management: Seamless addition, retrieval, and update of data for clients, volunteers, employees, and donors.

• Advanced Queries: Efficient execution of complex queries, such as total expenses by employee or donors who are also employees.

• User Interaction: A Java-based interface for streamlined database operations.