Introduction In This Project: Assisting A Pet Rescue Chat

Introductionin This Project You Will Assist A Pet Rescue Charity With

In this project, you will assist a pet rescue charity with managing their donations-related data. The charity organizes an annual donation drive, dividing the city into donation areas (each covering approximately six postal codes) and assigning volunteer groups to each area. Volunteers go door-to-door collecting donations, which can be cash, checks, or credit card payments. Weekly, group leaders compile donation records for their areas and send them to the charity's main office. The office loads these records into a central database, rejecting invalid entries, to perform data analysis.

The data sources include a central donations repository, hosted in an Oracle database on host db8.fast.sheridanc.on.ca, with each group working on their own database. The credentials are provided, including server and database details, along with user accounts for database administration and data storage. The master addresses are stored in a SQL Server database, which must be used to update the address data in the Oracle database, converting data types and generating IDs as needed. Donation lists are submitted as CSV files, with each volunteer submitting at least 15 entries, including valid and invalid addresses, to ensure at least 60 unique donors across three or four lists.

Tasks include refreshing address data from the master table, loading donation lists into the central repository with validation, creating a star schema for donations, developing views for summarized data, establishing database security, and preparing a proposal and report documenting these processes. The solution involves using SQL, PL/SQL, and data integration tools, with specific responsibilities assigned within the group, and deliverables comprising detailed reports, code, donation files, and summaries.

Paper For Above instruction

In this project, the primary objective is to develop an integrated and efficient data management system for a pet rescue charity’s donation drive. The system will facilitate data collection, validation, transformation, and analysis while ensuring data security and integrity. This comprehensive solution involves multiple technical phases, including data extraction, transformation, loading (ETL), database schema design, and security setup, using various tools and programming languages like SQL, PL/SQL, and Talend Data Integrator.

Address Data Refresh and Transformation

The initial task focuses on synchronizing the address information between the SQL Server master address table and the Oracle database utilized by the charity. Since the master table lacks postal codes, the process will include merging supplementary postal code data from other sources or maintaining a lookup table within the Oracle environment. This synchronization involves connecting to the SQL Server using an ODBC or linked server connection, extracting current address data, transforming data types as necessary, and inserting or updating records in the Oracle address table. The IDs generated dynamically will serve as foreign keys in the donation table, requiring careful handling of existing IDs to avoid duplicates or inconsistencies. This process not only updates the address data but also ensures referential integrity within the database schema.

Donation List Processing and Validation

The next key component involves importing donation lists submitted by volunteers into the central repository. Using either SQL scripts, PL/SQL procedures, or ETL tools like Talend Data Integration, the data will be validated against existing addresses and mandatory field constraints. Records with invalid addresses—such as non-matching postal codes or incomplete address details—are to be rejected and recorded in separate CSV files for review by volunteer coordinators. Furthermore, null values in critical columns must prompt rejection to maintain data quality. This validation ensures only legitimate donations are stored, preserving the accuracy and reliability of subsequent analysis.

Design and Implementation of Star Schema

To support analytical reporting, a star schema will be constructed with a fact table capturing each donation event, thus establishing the lowest granularity at the individual donation level. The schema will also include dimension tables such as Date, Address, and Volunteer Group. The fact table’s grain combines day, address, and volunteer, with supplementary measures like total and average donation amounts per grouping. Data loading procedures will be devised to periodically update the star schema from the central donation repository, using SQL or PL/SQL ETL routines, maintaining data consistency and temporal accuracy for reporting purposes.

Creating Summarized Views for Data Analysis

Using SQL views, the system shall provide summarized insights such as total and average donations by day, month, and year, enabling managers to identify trends over time. Additional views will show aggregated donations by address and postal code, revealing high-concentration donation zones. Further, views for volunteer contributions, grouped by volunteer and volunteer group leader, will facilitate performance tracking and resource allocation decisions. These views rely on the star schema and are designed for performance, with indexes as necessary to optimize query execution.

Database Security and User Management

Security configuration involves creating distinct users with specific privileges. A user named DMLUser will be granted full DML permissions on core tables—address, donations, and volunteer—to enable data manipulation and updates. Meanwhile, a user named Dashboard will have read-only access to the analytical views to support reporting functionalities without risking data integrity. Proper role assignment and privilege restrictions are vital to protect sensitive data and maintain operational controls across the database environment.

Proposal and Project Documentation

The initial proposal will outline the technology stack, including the choice of tools and methodologies for each task, responsibilities assigned among group members, and tentative timelines. The subsequent detailed report will document each step of the processes—data extraction, transformation, validation, schema design, and reporting—with embedded screenshots and descriptive explanations. The export files, source code, and donation lists will serve as supporting artifacts for transparency, reproducibility, and validation of the implemented solution.

Summary and Personal Reflection

Each group member will produce a personal summary reflecting on their contributions, challenges faced, and any deviations from the original plan, providing insight into teamwork and project management. Suggestions for improving the data collection process will focus on streamlining volunteer data entry, enhancing validation routines, or automating the report generation to improve operational efficiency. Overall, this project aims to enhance the charity’s data handling capabilities, enabling better decision-making and resource deployment for the pet rescue operation.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
• Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management (8th ed.). Cengage Learning.
• Harrington, J. L. (2016). Relational Database Design and Implementation (4th ed.). Morgan Kaufmann.
• O’Neil, P., & O’Neil, E. (2014). Database: Principles, Programming, and Performance (2nd ed.). Morgan Kaufmann.
• Simitsidis, G., et al. (2020). Data Integration for Better Decision-Making: A Tutorial. International Journal of Data Management, 12(3), 112-129.
• IBM. (2021). Implementing Data Warehousing Solutions in SQL Server and Oracle: Best Practices. IBM Knowledge Center.
• Talend. (2023). Talend Data Integrator User Guide. Talend Inc.
• Oracle. (2023). Oracle Database SQL Language Reference. Oracle Documentation.
• Microsoft. (2022). SQL Server Integration Services (SSIS). Microsoft Docs.
• Sneiderman, B., & Kahn, K. (2019). Designing Databases for Analytics: Best Practices for Data Warehousing. Journal of Data Science, 17(2), 34-52.