slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Final Presentation “Home Architect & Contractor Database” PowerPoint Presentation
Download Presentation
Final Presentation “Home Architect & Contractor Database”

Loading in 2 Seconds...

play fullscreen
1 / 30

Final Presentation “Home Architect & Contractor Database” - PowerPoint PPT Presentation


  • 123 Views
  • Uploaded on

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. Presentation Outline. Group III. 1.0 Project Summary Client Description

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Final Presentation “Home Architect & Contractor Database”' - gyda


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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

presentation outline
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
1 1 client description
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

1 2 client needs
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

1 3 approach
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

slide8

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

slide10

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

query 1 customer referrals
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
query 1 code and implementation
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
query 2 employee effectiveness
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
query 2 code and implementation
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
query 2 code putting it all together
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
query 3 advertising value
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
query 3 code
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
query 4 proposal forecast
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

query 4 cubic interpolation
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,
query 4 code
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.

query 5 sub contractor rating
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

query 5 code and implementation
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]));

normalization analysis
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

normalization analysis1
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

normalization analysis2
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

questions
Questions?

Group III

  • “Home Architect & Contractor” Database
slide30

Group III

The End

Thanks for your Attention