Final Presentation “Home Architect & Contractor Database”

1. Final Presentation“Home Architect & Contractor Database” Team Members • Ryan Coughran • Kenneth Chong • Jean Lai • Marco A Marquez • Benjamin Hans • Henry Hsue • Mark Liu Group III IEOR 115 Prof. Goldberg

2. Presentation Outline Group III • 1.0 Project Summary • Client Description • Client Needs • 2.0 Progress Summary • Simplified EER Diagram • Relational Schema and View in MS Access • 3.0 Final Analysis and Implementation • Queries • Normalization

3. 1.1 Client Description Group III • More Than a Carpenter, Inc. • Residential design/build contractor specializing in architectural design, remodeling, and historical restoration. • They offer a broad range of services ranging from small handiwork to new home design and construction. • They hire subcontractors to complete each stage of their designs. 1.0 Project Summary

4. 1.2 Client Needs Group III • Current Database • List of MS Outlook contacts to track client info • Bookkeeping software to manage individual projects • Missing Functionality • No way of analyzing data related to customers and contracts • No way to relate advertising efforts to sales • No way to analyze the company’s success in terms of presentations and sales calls 1.0 Project Summary

5. 1.3 Approach Group III • Our goal for the Home Architect and Contractor database was to create a flexible system that would track data about the following: • Advertising • Clients • Employees • Sales calls, visits, and presentations • Sub-contractors • Potential and current contracts • Contract stages and company resources used on them 1.0 Project Summary

6. 2.0 Progress Summary Group III

7. 2.1 DP Review I – Simplified EER Diagram

8. comes_from makes for a OTHER REALTOR VISIT d,t creates for a WEB BUDGET ADVERTISEMENT gives for a WORD_OF_MOUTH PRESENTATION created for d,t referred_by 1,N 1,N 1,N 1,N 1,N 1,N 1,N 1,N 1,N Supervises 1,N instance_of 0,1 0,1 SIGN PAMPHLET VEHICLE CUSTOMER EMPLOYEE PROPOSAL Associated_with d,t Liason_to VEH_INST CLIENT LEAD o,p PROPERTY OFFICE_STAFF 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 0,N 1,N 0,N d,t yields signs Is_for BILLBOARD_TYPE LAWNSIGN_TYPE designs_for instance_of requires CONTRACT DESIGNER instance_of includes d,t STAGE PERMIT LAWN_INST 1,1 1,1 1,1 1,1 1,1 1,1 1,1 1,1 works_on HANDWORK needs needs BILL_INST RENOVATION SUB-CONTRACTOR MATERIAL EQUIPMENT NEW_CONSTRUCTION

9. 2.2 DP Review II – Relational Schema

10. 1. ADVERTISEMENT (AID, Date_published) • 1a. WORD_OF_MOUTH (WMID, AID1, Fname, Lname) • 1aa. REALTOR (Realtor_ID, WMID1a, Company) • 1ab. OTHER_WM (OWMID, AID1a, Description) • 1b. WEB (Web_ID, AID1, Website, Web_Address, Monthly_Cost) • 1c. PAMPHLET (Pamphlet_ID, AID1, Type_of_pamphlet, Color, Location, Cost, Count) • 1d. VEHICLE (Vehicle_ID, AID1, VIID1d1, Manufacturer, Model, Year, Color) • 1d1. VEH_INST (VIID) • 1e. SIGN (Sign_ID, AID1, Location, Size) • 1ea. LAWNSIGN_TYPE (LSTID, Sign_ID1e, LSIID1ea1, Lawn_sign_type, Cost) • 1ea1. LAWN_INST (LSIID) • 1eb. BILLBOARD_TYPE (Billboard_ID, Sign_ID1e, BIID1eb1, Billboard_type, Monthly_Cost) • 1eb1. BILL_INST (BIID, AID1eb) • 2. COMES_FROM(AID1, Proposal_ID12) • 3. CONTRACT (CID, Proposal_ID12, Est_time, Type, Description, Date_signed) • 3a. HANDWORK (CID3, Room) • 3b. RENOVATION (CID3, Room) • 3c. NEW_CONSTRUCTION (CID3, description) • 4. PERMIT (Permit_ID, City, Ordinance, Date_applied, Date_received) • 5. REQUIRES (Permit_ID4, CID3) • 6. CUSTOMER (CusID, RcusID, Fname, Lname, MI, Street_address, City, State, Zipcode) • 6a. LEAD (LeadID, CusID5) • 6b. CLIENT (Client_ID, CusID5, CID3, Num_contracts) • 7. REFERED_BY(CusID6, RcusID6) • 8. PHONE_NUM (CusID6, Phone_number) • 9. EMAIL (CusID6, Email_address) • 10. EMPLOYEE(ESSN, SupSSN, Lname, Fname, MI, Salary, Email, Phone, Start_Date) • 10a. OFFICE STAFF(OSID, ESSN10, Branch, Office_No) • 10b. DESIGNER (Designer_ID, ESSN10, Skill, Software) • 11. SUPERVISES(ESSN10, SupSSN10) • 12. PROPOSAL(Proposal_ID, PID20, Name, Description, PDay, PMonth, PYear, PID20,CusID6) • 13. VISIT(Visit_ID, Date, Time_Spent) • 14. BUDGET(Budget_ID, Proposal_ID12, Total_Cost) • 15. PRESENTATION(Presentation_ID, Date, Time Spent, ClientResponse, Who_Presented_To) • 16. STAGE(Stage_ID, CID3, Level, Forecasted_Start_Date, Actual_Start_Date, Finish_Date, Est_cost, Actual_cost, Customer_rating, Sub_ID17) • 17. SUB_CONTRACTOR(Sub_ID, Name_of_company, Representative, Email, Phone) • 18. EQUIPMENT(EID, Name, Quantity, Type, Date_Acquired) • 19. MATERIAL(MID, Name, Amount, Cost, Date_Acquired) • 20. PROPERTY(PID, Name, Street_address, City, Country, Zipcode, Type, Built_date) • 21. PROP_VISIT(Proposal_ID12, Visit_ID13) • 22. EMP_VISIT(ESSN10, Visit_ID13) • 23. EMP_BUDGET(ESSN10, Budget_ID14) • 24. EMP_PRES(ESSN10, Presentation_ID15) • PROP_PRES(Proposal_ID12, Presentation_ID15) • 26. YIELDS(Proposal_ID12, CID3) • 27. DESIGNS_FOR(Proposal_ID12, ESSN10b) • 28. EQUIPMENTS_STAGE(Stage_ID16,EID18) • 29. MATERIALS_STAGE(Stage_ID16, MID19) • 30. PROPERTY_CONTRACT(PID20,CID3)  • 31. LIAISON_TO(Proposal_ID12, ESSN10a) • 32. PROP_ADV (Proposal_ID12, AID1) 2.0 Progress Summary

11. 2.3 DP Review III – MS Access Relationship View

13. 3.0 Final Analysis and Implementation Group III

14. Query 1: Customer Referrals Group III • Description: Which customers are most successful at referring new clients? • Output: Referral statistics for each customer. • Benefit: By finding our most valuable clients in terms of customer referrals, we can reward those clients who provide us with the most business. This will encourage more referrals and strengthen customer relationships. 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

15. Query 1 Code and Implementation Group III SELECT r.RcusID, Sum(b.Total_Cost) AS TotRev, Avg(b.Total_Cost) AS AvgRev, Var(b.Total_Cost) AS VarRev, Count(b.Total_Cost) AS NumRefContracts FROM Referred_By AS r, Customer AS cu, Proposal AS p, Budget AS b, Contract AS co WHERE cu.CusID = r.CusID AND cu.CusID = p.CusID AND p.Proposal_ID = b.Proposal_ID AND co.Proposal_ID = p.Proposal_ID GROUP BY r.RcusID ORDER BY Sum(b.Total_Cost) DESC; 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

16. Query 2: Employee Effectiveness Group III c Description: For each employee, produce a rating that takes into account the number of presentations delivered and the number of visits made, with respect to the number of contracts struck as a result. Output: Employee’s SSN, Rating Reason: This query is one way to evaluate the performance of our employees. Through this query we are able to place a rating on all our employees. This will ensure that it is fair and accurately reflects the employee’s performance. We will use this query to assess whether an individual employee will receive a raise. 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

17. Query 2: Code and Implementation 1 SELECT ep.ESSN, Count(ep.Presentation_ID) AS countPresentationContract FROM EMP_PRES ep, Proposal pr, Contract c, Prop_Pres pp WHERE ep.Presentation_ID= pp.Presentation_ID AND pr.Proposal_ID=pp.Proposal_ID AND pp.Proposal_ID=c.Proposal_ID GROUP BY ep.ESSN ORDER BY ep.ESSN; SELECT ev.ESSN, count(ev.Visit_ID) AS countVisitContract FROM EMP_VISIT ev, Proposal pr, Contract c, Prop_Visit pv WHERE ev.Visit_ID = pv.Visit_ID AND pr.Proposal_ID = pv.Proposal_ID AND pv.Proposal_ID = c.Proposal_ID GROUP BY ev.ESSN ORDER BY ev.ESSN; 3 • SELECT ep.ESSN, count(ep.Presentation_ID) AS countPresentation • FROM EMP_PRES ep • WHERE EXISTS (SELECT * • FROM Query2a) • GROUP BY ep.ESSN • ORDER BY ep.ESSN; • SELECT ev.ESSN, Count(ev.Visit_ID) AS countVisit • FROM EMP_VISIT ev • WHERE EXISTS (SELECT * • FROM Query2b) • GROUP BY ev.ESSN • ORDER BY ev.ESSN; 4 2 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

18. Query 2: Code, Putting it all together Group III 5 SELECT e.ESSN, 0.5*{(Sum(q2a.countPresentationContract)) / (Sum(q2c.countPresentation))} +0.5*{(Sum(q2b.CountVisitContract)) /Sum((q2d.CountVisit))} AS EmpRating FROM Employee e, Query2a q2a, Query2b q2b, Query2c q2c, Query2d q2d WHERE e.ESSN=q2a.ESSN AND q2a.ESSN=q2b.ESSN AND q2a.ESSN=q2c.ESSN AND q2b.ESSN=q2d.ESSN GROUP BY e.ESSN ORDER BY 0.5*{(Sum(q2a.countPresentationContract)) / (Sum(q2c.countPresentation))} +0.5*{(Sum(q2b.CountVisitContract)) / Sum((q2d.CountVisit))} AS EmpRating DESC; 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

19. Query 3: Advertising Value c Description: For each advertising category, calculate its return on investment (ROI). Output: (Profit – Cost)/Cost Benefit: This query allows our client to see which forms of advertising are providing revenue and which are resulting in a loss. 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

20. Query 3: Code Group III SELECT pa.Pamphlet_ID ( sum(b.Total_cost) – pa.Quantity*pa.Cost )/( pa.Quantity*pa.Cost) AS PamphletRating FROM Pamphlet pa, Budget b, Advertisement a, Proposal pr, Contract c, Prop_Adv pa WHERE pa.AID = a.AID AND a.AID = pa.AID AND pa.Proposal_ID = pr.Proposal_ID AND pr.Proposal_ID = b.Proposal_ID AND pr.Proposal_ID = c.Proposal_ID GROUP BY pa.Pamphlet_ID ORDER BY ( sum(b.Total_cost) – pa.Quantity*pa.Cost )/( pa.Quantity*pa.Cost) DESC; 3.0 Final Analysis & implementation • “Home Architect & Contractor” Database

21. Query 4: Proposal Forecast c Description: How many proposals can we expect to receive this month based on how many we’ve received in the last several months. Output: A table showing how many proposals were received in each of the last twelve months. A cubic-spline forecast is made using this data. Benefit: This information will help our client budget for the next month, develop a short-term business strategy, and assess the feasibility of future business expansion. • “Home Architect & Contractor” Database 3.0 Final Analysis & implementation

22. Query 4: Cubic Interpolation • Using the SQL output, we can use this result and interpolate the demand for the next year. • We use a piecewise continuous function, passing through each of the values of the table. The form of the equation is as follows, • We also need to put boundaries in the 1st and 2nd order derivative,

23. Query 4: Code Forecasting for the following month from all previous data: SELECT Month, Year, Count(Proposal_ID) FROM Proposal GROUP BY Month, Year ORDER BY Year ASC, Month ASC; Forecasting for an upcoming month given data for that month from previous years: SELECT Month, Year, Count (Proposal_ID) FROM Proposal WHERE Month = ‘insertmonthhere’ GROUP BY Year ORDER BY Year ASC; Constraints can be added to the “SELECT” and/or “WHERE” statements if we are interested in looking at a narrower period of time to forecast demand, or in examining timespans other than months.

24. Query 5: Sub-contractor Rating c Description: Which subcontractors complete jobs most cost-effectively? Output: A rating based upon the actual cost of completing a project relative to the cost stated in the contract, for each project in which a subcontractor takes part. Benefit: Serves as one criterion with whichthe company can determine which subcontractors to pursue more involved business relationships with and to recommend to customers. Consequently, provides an incentive to subcontractors to make more accurate estimates. • “Home Architect & Contractor” Database 3.0 Final Analysis & implementation

25. Query 5: Code and Implementation SELECT STAGE.Stage_ID, STAGE.Est_Cost, STAGE.Actual_Cost, 1-Abs([Est_Cost][Actual_Cost])/[Actual_Cost] AS Stage_Rating FROM STAGE GROUP BY STAGE.Stage_ID, STAGE.Est_Cost, STAGE.Actual_Cost, 1-Abs([Est_Cost]-[Actual_Cost])/[Actual_Cost] HAVING (((2*[Actual_Cost])>=[Est_Cost])) UNION SELECT STAGE.Stage_ID, STAGE.Actual_Cost, STAGE.Est_Cost, 0 AS StageRating FROM STAGE GROUP BY STAGE.Stage_ID, STAGE.Actual_Cost, STAGE.Est_Cost HAVING (((2*[Actual_Cost])<=[Est_Cost]));

26. Normalization Analysis c First Normal Form • 6. CUSTOMER (CusID, Proposal_ID12, RcusID, Fname, Lname, MI, Street_address, City, State, Zipcode) • 8. PHONE_NUM (CusID6, Phone_number) • 9. EMAIL (CusID6, Email_address) • Customer is in first normal form because all the variables are atomic (indivisible) and single valued. The relation, PHONE_NUM, allows customers to have multiple phone numbers. • “Home Architect & Contractor” Database 3.0 Final Analysis & implementation

27. Normalization Analysis c Second Normal Form • 16. STAGE(Stage_ID, CID3, Level, Forecasted_Start_Date, Actual_Start_Date, Finish_Date, Est_cost, Actual_cost, Customer_rating, Sub_ID17) • STAGE is second normal form because there are no partial dependencies. Stage_ID and CID3 make up a composite key so this case is non-trivial. All other attributes depend on the composite key entirely and not a part of it. • “Home Architect & Contractor” Database 3.0 Final Analysis & implementation

28. Normalization Analysis c Third Normal Form • 10. EMPLOYEE(ESSN, SupSSN, Lname, Fname, MI, Salary, Email, Phone, Start_Date) • EMPLOYEE is in third normal form because every nonprime attribute is dependent on every CK in R and there are no transitive dependencies. • “Home Architect & Contractor” Database 3.0 Final Analysis & implementation

29. Questions? Group III • “Home Architect & Contractor” Database

30. Group III The End Thanks for your Attention