Transcript maderagroup - Berkeley Industrial Engineering and

Maderagroup
Team 5 Final Presentation
Nanavati Low
Nancy Chunchun Huang
Grace Li Gu
April Yikun Yang
Katie Hing-Man Mak
Ellen Yueyilin Qi
Project Overview
Queries
Relational
Schema
Normalization
Analysis
Access
Relationships
Client
Introduction
Simplified
EER Diagram
Client Introduction
“Big picture consultation for start ups
and social mission organizations focused
on growth strategies.”
Executive Team:
Kath Delaney, Principal and Founder
Jill Freeman, Vice President
Erik Johnson, Madera Group Media Executive Producer
Client Services
Services include:
• Website development/design, SEO features
• Video and multimedia production
• Donor research
• Executive and board coaching in fundraising, social
media and organizational growth
• Management and formulation of online fundraising
campaigns
• Email giving and listserv development
• Producing and integrating videos and multimedia
EER Diagram
Access Relationships
Relational Schema | Entities
1. Organization (OrganizationID, OrganizationName, Website, City)
2. Person (PID, First Name, MI, Last Name, HomePhone, CellPhone,
OfficePhone, FaxNo, StreetNo, Street, Apt, City, Country, ZIP)
2a. Other (PID2)
2b. Employee (PID2,, SSN, Wage)
2ba. Public Relations (PID2b)
2bb. Intern (PID2b, IsWorking, AvailableHourPerWeek)
2bc. Contractor (PID2b, Specialty, Category,
CommissionFromProject)
2c. Reporter (PID2, OrganizationSource)
2d. Client (PID2, StartDate, EndDate)
2e. Donor (PID2, AddToMailingList)
2f. Consultant (PID2, SSN, JobDescription, Wage)
3. Meeting (MeetingID, DateofMeeting, Time, Location, Remark,
DateofResponse)
4. AreaofInterest (Area)
5. Donation (DonationID, ProjectID12, Amount, Source,
ConfirmationNumber, SpecialRequest, PID2e, DepositedInto16a,
DonationDate, Time, ReasonOfDonation)
6. FundraisingPlatform (FID, FundraisingPlatformName, Type, Time,
Amount, NumofView, PaymentMethod)
7. CorporatePortfolio (PortfolioID, CorporateDescription)
8. Advertisement (AdID, AdvertisementName, NumofPrint,
NumofViews, NumOfLikeShare, PersonInCharge2bc, ProjectID12,
AuthorizedBy, Cost, EstTotalAmountRaised)
9. AdPlatform (AdPlatformID, PlatformName, Type)
10. Event (EventID, EventName, Description, DateOfEvent, Address,
City, State, Zip, ProjectID12, Type, Cost, EstTotalAmountRasied)
11. Proposal (ProjectID12, ProposalID, IsPresentedTo2d,
ProposalAttachment, ProposalType, Description)
12. Project (ProjectID, Type(Profit, Non-Profit, or Pro-Bono),
StartDate, EndDate, Description, Budget, TargetAmount, Client2d,
Campaign14)
13. Day (Day)
14. Campaign (CampaignID)
15. ProjectAccount (AccountNumber, Amount, ProjectID12)
16. Transaction (TransactionNumber, AccountNumber15,
TransactionDate, Time, Amount, ProjectID12)
16a. Deposit (TransactionNumber16, PaidByClient2d)
16b. Withdrawal (TransactionNumber16,
isSalaryForEmployee2b, isSalaryForConsultant2f, isForEvent10,
isForAd8)
Relational Schema | N:M Relations, Multivalued Attribute(s)
N:M Relations
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
OrgIsofAoI (AreaOfInterest4, Organization1)
PersonIsPartOfOrganization (Organization1, Person2)
DonatesThrough (Donor2e, FundraisingPlatform6)
DonatesFor (Donor2e, Project12)
EmpWorksOnPrj (EmployeePID2b, ProjectID12, StartDate, EndDate)
EmpWorksOnDate (EmployeePID2b, Day13, StartDate, EndDate)
EmpManagesAd (EmployeePID2b, AdID8)
CPIsMadeFrom (PortfolioID7, ProjectID12)
CPIsPresentedTo (ClientPID2d, PortfolioID7)
IsConsultingFor (ConsultantPID2f, ProjectID12)
CampaignIsofAoI (CampaignID14,AreaOfInterest4)
MeetingForProject (MeetingID3,ProjectID12)
MeetingParticipant (PID2,MeetingID3)
AdIsPutOnAdplatform (AdPlatformID9,AdID8, StartDate, EndDate)
Multivalued Attribute(s)
44.
45.
46.
Email (PID2, Email)
InternSkills (InternPID2bb, Skill)
Assignment (ProjectID12, Assignment, HourDemand)
31. ContactorContactsReporter (PIDofContractor2bc, PIDofReporter2c,
DateofContact, Time, Description)
32. EventManagedBy (PID2b, EventID10)
33. FundraisingPlatformRaisesForProject (FID6, ProjectID12)
34. DonationDonatedtoFundraisingPlatform (DonationID5, FID6)
35. EmployeeUpdatesFundraisingPlatform (PID2b, FID6)
36. ReporterReportsOnProjects (PID2c, ProjectID12)
37. PersonAttendsEvent (PID2, EventID10)
38. AdvertisementIsDisplayedAtEvent (AdID8, EventID10)
39. AdvertisementIsDisplayedOnFundraisingPlatform (AdID8, FID6)
40. PersonIsInterestOfAOI (PID2, Area4)
41. ProjectIsOfAoI (ProjectID12, Area4)
42. PRFindsClient (PRPID2ba, ClientPID2d)
43. ProjectIsWorkingOnDate (ProjectID12, WorkingDate13)
Queries
Query 1
Potential Donation Optimization
and Traveling Salesman Problem
Justification:
1. Maximize client potential donation amount on business trips
2. Choose donors to visit based on how likely they are to donate and what amount
Create schedule of who and when to visit in minimizing distance for ease of planning
Query 1: Potential Donation Optimization and
Traveling Salesman Problem
Description
Determine which donors to visit on one business trip then minimize travel path given a Zip Code parameter and time availability.
Access
1. SQL
Run SQL Code to generate
Donation History Table
2. Input Zip Code
Input Radius of Visitation
3. Input Form
Input n potential donors to visit.
AMPL
CPLEX
R
6. R Code
Run R Code using rate of decay based on
historical donation data. Calculates and
determines if significant time has passed
since last donation. This will eliminate
donors and output a list of donors to visit.
7. R Code
Run R Code that uses least squares
regression to predict donation amount.
Output will rank top n potential donors.
Discard
8. AMPL & CPLEX
Run CPLEX code using
Traveling Salesman
formulation.
Query 1: Potential Donation Optimization and
Traveling Salesman Problem
SQL Code
SELECT d.PID, donation.Amount, aoi.Area,
donation.DonationDate, p.ZIP
FROM donor AS d, person AS p, personisinterestofaoi AS aoi,
donation
WHERE ((([donation].[PID])=[d].[PID]) AND
(([d].[PID])=[p].[PID]) AND ((aoi.PID)=[p].[PID]) AND
((aoi.Area)='Environmental')) AND Donation.DonationDate
>= #12/4/2011#;
Query 1: Potential Donation Optimization and
Traveling Salesman Problem
Donor expected donation
If X [i,j] = 1 then
Visit Donor i on Day j
X [i,j] = 1 Donor is visited
X [i,j] = 0 Donor is not visited
Query 2
Project Assignment Allocation
Justification:
1.
2.
3.
4.
Best match overall between intern skill set and company demands
Interns allocation method to ensure all tasks are covered
Minimize cost of total intern payments
Ensure all interns are assigned to the task that they are able to do
Query 2: Project Assignment Allocation
Assumptions
Description
Find the best assignment of project tasks for each intern by
minimizing the total cost of salary paid to interns.
1.
2.
3.
4.
5.
Intern
Experience Data
& Assignment
Demands
Access
1. SQL
Run SQL Code to
generate Intern
and project table
Each intern is able to finish the assignments they are allocated
Each assignment only requires one skill
Multiple interns can work on the same assignment and one
intern can work on multiple assignments
Each intern works only one shift on each day that they work
Skills include: Writing, Microsoft Office Suite, Social
Networking, Research, Access, Graphic Design
CSV
2. CSV file export
Export intern experience
and company projects
table
AMPL
CPLEX
3. Run AMPL
Format Linear
Program
Define Variables,
Constraints, and
apply objective
function
Query 2: Project Assignment Allocation
SQL Code
InternSkills
SELECT s.InternPID, s.Skill
FROM InternSkills s, Intern I
WHERE i.IsWorking AND i.PID = s.InternPID;
InternAvailability
SELECT PID, AvailableHourPerWeek
FROM Intern
WHERE IsWorking;
HourlyWage
SELECT i.PID, e.Wage
FROM Employee e, Intern i
WHERE i.IsWorking AND i.PID = e.PID;
Assignment
SELECT Assignment, HourDemand
FROM Assginment
WHERE ProjectID = XXX;
Generate Table listing:
-Intern ID
-Intern Skill
-Available Hours per Week
-Intern Wage
-Assignment Hour Demand
Query 2: Project Assignment Allocation
Linear Program Model
Query 2: Project Assignment Allocation
AMPL Code
Query2.mod &Query2.dat
AMPL/CPLEX Output:
Results include assignment match ratings with each intern
Query 3
Non-linear Least Squared Regression
to Sort Events by Donation
Justification:
1.
2.
Find effectiveness of each event/advertisement
Compare frequency and total donation amounts across all events/advertisement for a project
Query 3: Non-linear Least Squared Regression to
Sort Events by Donation
Description
Find the donation of amount and effectiveness of each event for a project.
Access
1. SQL Code
Run SQL Code for 2
tables
- Table containing
donation amount
and frequency by
day for each project
- Table containing
dates of each event
for a particular
project
R
2. R Plot
Generate a plot that graphs
donation frequency vs. time
for a particular project, mark
time each event took place
with vertical lines
4. R Code
Assume total donation amount
is comprised of several events,
which are proportionally
distributed to the result of the
Rayleigh distribution functions.
3. R Plot
Retrieve nonlinear Least
squares best fit Rayleigh
Distribution as the resulting
donation for each event
Calculate the donation resulted
from each event and calculate
the rate of return accordingly
Query 3: Non-linear Least Squared Regression to
Sort Events by Donation
SQL Code Part 1
SELECT DATEDIFF("d", p.StartDate, d.DonationDate) AS DS,
COUNT(d.Amount) AS Total, SUM(d.Amount) AS Amount
FROM Donation d, Project p
WHERE p.ProjectID=### AND d.ProjectID = p.ProjectID
GROUP BY DATEDIFF("d", p.StartDate, d.DonationDate);
SORT BY (d.Day - p.StartDate) AS DS ASD;
Generate Table:
Donation ID
Donation Date
Project Start Date
Donation Amount
SQL Code Part 2
SELECT e.EventID, e.Type, DATEDIFF("d", p.StartDate, e.DateOfEvent) AS DS
FROM Event e, Project p
WHERE p.ProjectID=e.ProjectID AND p.ProjectID=###;
UNION
SELECT a.AdID, ad.Type, DATEDIFF("d", p.StartDate, a.DateofAd) AS DS
FROM Advertisement a, AdPlatform ad, AdIsPutOnAdplatform ada, Project p
WHERE p.ProjectID = ### AND a.ProjectID = p.ProjectID AND
ada.AdID = a.AdID AND ad.AdPlatformID = ada.AdPlatformID;
Generate Table:
Event ID
Event Type
Event Day
Project Start Date
Query 3: Non-linear Least Squared Regression to
Sort Events by Donation
Non confounded donation
frequency vs. time graph for
each event
Query 4
Justification:
1.
Analyze event-event influence
2.
Choose optimal set of events/ads
Project Promotion Optimization
Query 4: Project Promotion Optimization
Description
Which events should be held considering how events affect one another for new project?
Access
1. Access Form
Enter proposed
parameters
2. SQL
Generate table for costs and
returns of all relevant events
and advertisements
R
3. R Code
Generate
• covariance matrix
• expected returns based
on past data
• Calculations on proposed
parameters
AMPL
CPLEX
4. AMPL/Complex
Solve integer program
(adaptation of
portfolio optimization
model from E120)
Query 4: Project Promotion Optimization
SQL Code
SQL Code
SELECT e.Type,w.isForEvent
FROM Withdrawal as w, Event as e, ProjectIsOfAoI as pa, Transaction as t
WHERE pa.Area=’XXX’ AND w.TransactionNumber = t.TransationNumber
AND t.ProjectID = pa.ProjectID AND e.ProjectID = pa.ProjectID
GROUP BY e.Type
UNION
SELECT ad.Type,w.isForAdPlatform
FROM Withdrawal as w, IsPutOn as ad, Advertisement as a,
ProjectIsOfAoI as pa, Transaction as t
WHERE pa.Area=’XXX’ AND w.TransactionNumber = t.TransationNumber
AND t.ProjectID = pa.ProjectID AND a.ProjectID = pa.ProjectID AND
a.AdID = ad.AdID
GROUP BY ad.Type;
Query 4: Project Promotion Optimization
Query 4: Project Promotion Optimization
AMPL Code
Query 5
Return on Investment and
Forecasting for Contractors
Justification:
1.
2.
Monitor learning curve of contractors
Compare contractors' projected ROI to choose best fit for project
Query 5: Return on Investment and Forecasting
for Contractors
Description
Which contractor should Madera Group hire for a project?
Access
1. SQL Code
Generate Table
- Contractor Data (ID,
Amount Paid, Time)
- Events, Amount,
Frequency
R
2. Calculate ROI
Calculate each contractor
3. Graph R Plot
Plot ROI vs. time graph
for each contractor over
all project finish dates
4. Run R Regression
Use non-linear least
squares regression to fit
into a logarithmic curve
of a + b*log(c*x) which
accounts for ROI
improvement due to a
decaying learning curve
5. Graph R Plot
Plot all curves on one
graph. Compare ROI of
different contactors on
the same task.
6. R Analysis
Predict which contractor to
use how much company
can expect to pay by
ranking the predicted ROIs
given time t
Query 5: Return on Investment and Forecasting
for Contractors
SQL Code
SELECT c. CommisionFromProject
FROM EmpWorksOnPrj e, Contractor c
WHERE e.PID = c.PID AND c.PID = ###;
Generate Table listing:
Contractor’s commission for
each project
Normalization Analysis
Person | 1NF
Person (PID, First Name, MI, Last Name, Email, HomePhone, CellPhone, OfficePhone, FaxNo, Address)
→
Person (PID, First Name, MI, Last Name, HomePhone, CellPhone, OfficePhone, FaxNo, Address)
Email (PID, Email)
Donation | 1NF & 2NF, 3NF & BCNF
DonationID
Project
ConfirmationNumber
Source
Amount
Special
Request
PID
DepositedInto
Day
Time
ReasonOfDonation
1NF and 2NF
Donation (DonationID, Project12, Amount, Source, ConfirmationNumber, SpecialRequests, PID2e, depositedInto16a, Day,
Time, ReasonOfDonation)
→ 3NF
Donation (DonationID, ConfirmationNumber, Source, Project12, depositedInto16a)
ConfirmationNumberInfo (ConfirmationNumber, Source, Amount, SpecialRequest, PID2e, Day, Time, ReasonOfDonation)
Proposal | 1NF, 2NF & 3NF
ProjectID
ProposalID
ProjectID
ProposalID
X
ProjectID
presentedto
ProposalAttachment
ProposalAttachment
Description
Description
ProposalType
1NF
ProposalType
X
PresentedTo
1NF
Proposal (ProjectID12, ProposalID, presentedTo2d, ProposalAttachment, Description, ProposalType)
→2NF and 3NF
{ProjectID12, ProposalID} → {ProjectID12, ProposalID,,ProposalAttachment, ProposalType}
{ProjectID} → {ProjectID2, presentedTo2d}
→ BCNF
ProjectProposal(ProjectID12, ProposalID,Description, ProposalType)
AttachmentForProject(ProposalAttachment, ProjectID12)
AttachmentForProposal(ProposalAttachment, ProposalID)
PresentTo (ProjectID12, presentedTo2d)
2NF & 3NF
Thank you
Queries
Normalization
Analysis
Relational
Schema
Madera Group
Kath Delaney
Access
Relationships
Client
Introduction
Simplified
EER Diagram