1 / 26

Nevada Partnership For Homeless Youth

Nevada Partnership For Homeless Youth. Presented by Team 4: Simon tiu Browly DO Moonsoo Choi Jason cai Christina you Loraine li Katy nomurA. Organization Overview.

penn
Télécharger la présentation

Nevada Partnership For Homeless Youth

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Nevada Partnership For Homeless Youth Presented by Team 4: Simon tiu Browly DO Moonsoo Choi Jason cai Christina you Loraine li Katy nomurA

  2. Organization Overview • Nevada Partnership for Homeless Youth (NPHY) is an organization that focuses on eliminating homelessness among Nevada’s Youth. • In order to tackle this immense problem strategically, NPHY has mobilized a few successful programs: • Drop-in Center • Independent Living • Emergency Center • Data not only enables NPHY to assess their effectiveness, it also allows NPHY to plan for future growth and projects.

  3. Project Review • DP 1 • Project Conceptualization • Simplified EER Diagram • Defined entities • DP 3 • Major query revision • Complexities added with AMPL and sub-queries • Query implementation in SQL • Minor EER refinement • Forms implemented • DP 2 • Query Creation • EER expansion • Classes • Fundraising Events • Implementation of queries in relational algebra • Relations implementedin Access • Final Result • Query Implementation • Analysis Options • Complete EER • Normalization Analysis

  4. Access Relationships

  5. Query 1 – Demand Seasonality Observation: Demand for services exhibits strong seasonality. Purpose: Extract the data of how many services are provided for each different month over previous years during the same month. The query will count numbers of different services provided monthly. Usage of the data: For each month, we apply a linear regression function .lm in R to find linear regression equation: ŷ = ax + b. Where ŷ is the estimated demand per month. X is the year we are looking at. If the linear regression line shows strong confidence, NPHY can use the line to predict future demand for a specific month and type of service. If the linear equation shows a increasing or decreasing trend, it can also tell NPHYto expand or reallocate resources. SQL: SELECT p.Program_Type AS ProgramType, s.Month AS [Month], s.Year AS [Year], Count(s.client_ID) AS CountOfclient_ID FROM service AS s, program AS p WHERE (((p.program_ID)=[s].[program_ID])) GROUP BY p.Program_Type, s.Month, s.Year, [p].[Program_Type] And [s].[Month] And [s].[Year] ORDER BY s.Month, s.Year;

  6. Query 1 - Access Regression line summary: lm(formula = demand[jan, ]$count ~ demand[jan, ]$year) Residuals: Min 1Q Median 3Q Max -13.182 -4.291 -3.309 4.691 21.764 Coefficients: Estimate Std. Error t value Pr(>|t|) (Intercept) -1.392e+04 1.973e+03 -7.054 5.96e-05 *** demand[jan, ]$year 6.982e+00 9.838e-01 7.097 5.69e-05 *** --- Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 Residual standard error: 10.32 on 9 degrees of freedom Multiple R-squared: 0.8484, Adjusted R-squared: 0.8316 F-statistic: 50.37 on 1 and 9 DF, p-value: 5.686e-05 • Therefore, b = -1.392e+04 , a = 6.982e+00 for equation ŷ = ax + b

  7. Query 2 – Event Analysis Purpose: To aid in fundraising by looking at which events raise the most money and forecasting times in which demand is high. Use: The organization will be able to observe which type of event cost less and profit more, and determine which type of events to hold more often.  • In addition, based on which type of resource is donated the least, for the future events, the organization may ask the donors for specific type of resources. Observation: Not all events are created equal. In order to make fundraising more efficient, we want to visually observe the effectiveness of different events in terms of monetary benefit.

  8. Query 2 - Access SQL: SELECT a.DateofEvent, Sum(Donation.Monetary_Value) AS SumOfMonetary_Value, Count(Donation.Donor_ID) AS No_of_Donors, -1*Sum(Donation.Type_1_Food) AS Type_1_Food, -1*Sum(Donation.Type_2_Clothing) AS Type_2_Clothing, -1*Sum(Donation.Type_3_Money) AS Type_3_Money, -1*Sum(Donation.Type_4_Furniture) AS Type_4_Furniture, -1*Sum(Donation.Type_5_Other) AS Type_5_Other, a.Event_ID, a.Event_Type, Sum(a.Cost) AS Cost, Sum(a.Profit) AS Profit, ((a.Revenue)-(a.Cost))/((a.Cost)) AS Profit_Margin FROM Donation, Fr_Event AS a WHERE (((Donation.Event_ID)=[a].[Event_ID])) GROUP BY a.DateofEvent, a.Event_ID, a.Event_Type, ((a.Revenue)-(a.Cost))/(a.Cost);

  9. Query 3 – Asset Management • cij: cost of event i during month j • eij: donation received of event i during month j • xij=1 if event i during month j will be held, =0, otherwise • yj: the money leftover • dj: demand of money in month j Observation:The supply and demand of inventory (food, supplies, etc.) can be modeled as a classic inventory problem. Purpose: To optimize donation revenue while taking into consideration the event costs. Implementation: This is an integer-programming problem that will be solved by using AMPL. The objective is to maximize the monthly donation from donors based on historical revenue subtracted by the cost of the selected held events. The costs of running events depend on the event location and month.

  10. Query 3 – Access SQL:TRANSFORM Avg(Fr_Event.Cost)SELECT Fr_Event.Event_ID, Avg(Fr_Event.Cost) AS [Yearly Avg]FROM Fr_EventGROUP BY Fr_Event.Event_IDPIVOT Format([DateofEvent],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

  11. Query 3 – Access SQL:TRANSFORM Avg(Fr_Event.Profit) AS AvgOfProfitSELECT Fr_Event.Event_IDFROM Fr_EventGROUP BY Fr_Event.Event_IDPIVOT Format([DateofEvent],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

  12. Query 3 – Access Cont. SQL: SELECT Format([Month/Year],'mmm') AS [Month], Avg([Query 3: Monthly Demand (1/2)].Total) AS DemandFROM [Query 3: Monthly Demand (1/2)]GROUP BY Format([Month/Year], 'mmm'); SQL: SELECT Format([Consumption Date],'mmm/yyyy') AS [Month/Year], Sum(Resources.[Monetary_Value]) AS TotalFROM Donation INNER JOIN (Resources INNER JOIN Resource_Consumption ON Resources.[Item_ID] = Resource_Consumption.Item_ID) ON Donation.Donation_ID = Resources.[Donation_ID]GROUP BY Format([Consumption Date],'mmm/yyyy');

  13. Query 3—Massaging the Data

  14. Query 3: AMPL

  15. Query 3: AMPL Running AMPL

  16. Query 4 – Ranking Donors Observation: We want to create a scale to measure donors. An effective ranking of donors will allow us to focus our fundraising efforts on areas of weakness and enable us to use a variety of other queries (i.e. Query 5) Purpose: Rank the donors according based on user-defined heuristics. Implementation: In order to rank these donor appropriately, we take into consideration the monetary values of the items that they donate and the variety of items. Our sample access implementation is as follows:

  17. Query 4 - Access SQL: SELECT D.Donor_ID, (Sum (Monetary_Value * weight)) * (1 + Count (Item_type)/10) AS Donor_IndexFROM Donor AS D, Resource AS R, Resource_weight AS RWWHERE D.Donor_ID = R.Donor_ID AND R.Item_type = RW.Item_typeGROUP BY D.Donor_ID;

  18. Query 5 – Event Planning Observation: In order to raise money most effectively, events should be placed closer to donors who will contribute more. Calculating an event location closer to the more important donors as rated by our previous query will give us a better sense of an optimal location. Purpose: Find an optimal location for events considering donor importance. Implementation: • From SQL, we get two tables: one for latitude and longitude of the potential locations for the fundraising events (Plat, Plong) and another for the location of donors (Donor_ID, latitude, longitude). • Input the data extracted from SQL and construct a function in Matlab to find out the optimum location of the fundraising event. • For each potential location i, sum up the weighted distance to every donor according to the formula: The max of the sum of these weights will give us the best location.

  19. Query 5 - Access Select L.Latitude AS Plat, L.Longitude AS Plong From Donor AS D, Location AS L Where D.Location_ID = L.Location_ID AND D.Hold_event = 1 UNION Select L.Latitude AS Plat, L.Longitude AS Plong From FR_Event AS E, Location AS L Where E.Location_ID = L.Location_ID Possible Locations MatLab SELECT Donor.Donor_ID, Location.Latitude, Location.Longitude FROM Location, Donor WHERE (((Location.Location_ID)=[Donor].[Location_ID])) ORDER BY Donor.Donor_ID;

  20. Normalization Analysis • Donor Relation • Donor(SSN27, Donor_ID, Last_Name, First_Name, E-mail, Phone, Address, City, State/Province, Zip_Code, Country/Region, Location_ID44) • This is already in first normalized form because it contains no multivalued attributes.

  21. Functional Dependencies • {SSN, Donor_id}{Add,City,State} • {SSN} {Fname,Lname} • {Donor_id} -> {E-mail,Phone, Location_id} • {State} {Country} • {Add,City,State} {Zip} • {Zip} {City,State}

  22. 2NF Normalization • Dname(SSN27, Fname, Lname) • DContact(Donor_id, E-mail, Phone, Location_ID44) • DAdd(SSN27, Donor_id, Add, City, State, Zip, Country) • This removes all partial dependencies that the non-prime attributes have on the CK by making them each their own relation. Donor(SSN27, Donor_ID, Last_Name, First_Name, E-mail, Phone, Address, City, State/Province, Zip_Code, Country/Region, Location_ID44)

  23. 3NF Normalization Dname(SSN27, Fname, Lname) DContact(Donor_id, E-mail, Phone, Location_ID44) DAdd(SSN27, Donor_id, Add, City, State, Zip, Country) • Dname(SSN27, Fname, Lname) • DContact(Donor_id, E-mail, Phone, Location_ID44) • DAdd(SSN27, Donor_id, Add, City, State) • LocationofDonor(Add, City, State, Zip) • CountryLocation(State, Country) • LocationofDonor removes the transitive dependency that exists from Add, City, and State determining country, and zip. Moreover, the State determines the Country.

  24. BCNF Normalization • Dname(SSN27, Fname, Lname) • DContact(Donor_id, E-mail, Phone, Location_ID44) • DAdd(SSN27, Donor_id, Add, City, State) • LocationofDonor(Add, zip) • ZipofDonor(Zip, City, State) • CountryLocation(State, Country) Dname(SSN27, Fname, Lname) DContact(Donor_id, E-mail, Phone, Location_ID44) DAdd(SSN27, Donor_id, Add, City, State) LocationofDonor(Add, City, State, Zip) CountryLocation(State, Country) Because Add, City, State determine zip, but zip determines city and state, the previous normalization was in 3NF but not in BCNF. By breaking up the LocationofDonor relation into two relations LocationofDonor and ZipofDonor, the relation can be normalized into BCNF.

  25. Future Work • Add More Queries • Ex: add a query that will produce a schedule for employees and volunteers • Ex: add a query that will help plan future outreach, possibly via social networking • Improve Current Queries • Ex: add more graphs and features to the current queries to improve functionality • Automation of the Process • Add functionality that will read files to automate data entry • Write macros to automate usage of the database

More Related