Introduction: this is data from a pet rescue charity with managing their donations related data. The charity
Question:
Introduction: this is data from a pet rescue charity with managing their donations related data. The charity organizes an annual donation drive. The city is divided to donations areas (every donation area is approximately 6 postal codes) and assign a group of volunteers to every area. The volunteers will go door to door in their designated areas in order to collect donations from the residents. They can collect cash, checks, or credit card payments. At the end of every week the volunteering group leaders will fill in a list with the donation record that were collected in their area and send it to the charity main office. The staff in the main office will load the list to a central table after rejecting non-valid entries and then use the data to perform analysis.
Data Source: Central Donations Repository The repository includes: Address: unit_num, street_number, street_name, street_type, street_direction, postal_code, city, province Volunteer: first_name, last_name, group_leader Donation: donor_first_name, donor_last_name, donation_date, donation_amount, payment_method.
Donation List:
The list of donors is stored in a comma separated file. A sample named DonorsList.csv (provided) shows a list that the volunteer group lead sends to the main office. It illustrates the schema and contains only two entries. DO NOT USE EITHER OF THOSE ENTRIES IN YOUR SUBMISSION. Each member of the group (presumably a volunteer with a distinct volunteer no) should provide a separate list (in csv format) for their "area" with at least 15 entries. As a result, each group will have several (equal to the number of group members) donor lists. In total there must be at LEAST 30 distinct names/addresses on at LEAST 3 separate lists. Tasks 1. need to make an SQL database with the required tables using SSMS and populate the developed data in DonorsList.csv into their appropriate tables. 2. Based on the SQL database, need to make an appropriate dimensional modeling (only one) for the data analysis purpose. The focal point is to answer the questions in the next task. 3.Ques The average and sum of the donation by day, month, and year The average and sum of the donations by postal code and City in a specific month. define the city and month as variables to allow flexibility. The amount collected per payment method from the city with highest $ value of donations. Define the payment method as variable to allow flexibility.
Management Science The Art of Modeling with Spreadsheets
ISBN: 978-1118582695
4th edition
Authors: Stephen G. Powell, Kenneth R. Baker