1 / 28

presents

A++ Consulting Group. presents. TOR: Technology & Operations Review. A++ Consulting. Our Team. Angela Carlin Thomas Choi Matthew Hedges Matthew Iong Harsh Karmarkar David Ng Ryan Salcedo. A++ Consulting. Executive Summary. Company Review EER Diagram Verbal Explanation of Queries

sally
Download Presentation

presents

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. A++ Consulting Group presents TOR: Technology & Operations Review

  2. A++ Consulting Our Team Angela Carlin Thomas Choi Matthew Hedges Matthew Iong Harsh Karmarkar David Ng Ryan Salcedo A++ Consulting

  3. Executive Summary • Company Review • EER Diagram • Verbal Explanation of Queries • Implementation in Access • Q & A

  4. Demand driven online publication • Industry papers reviewed and published • Editors around the globe A++ Consulting

  5. TOR EER Diagram (1,M) Monitors_ Acct (1,N) (1,M) (0,N) Monitors_ Paper Monitors_ User (0,M) Subscribes (1,N) o (1,N) (0,1) Discusses (0,M) (0,N) Donates_To (1,N) (0,N) (0,1) Accepts References_Internal (0,M) Is_On (0,N) (0,M) (1,1) Belong_To (1,N) (1,N) (0,M) d (0,M) (1,N) Submits (1,N) (0,M) Has (0,M) Reference_Outside Reviews (0,M) Has (0,N) (0,M) (0,N) (1,N) TOR_ACCOUNTS USER ADMINISTRATOR EDITOR SUBJECT INSTITUTION UNREGISTERED UNDER REVIEW (0,M) AUTHOR Views E READER (0,N) PAPER WORKING PUBLISHED KEYWORD OUTSIDE_PAPER A++ Consulting

  6. Query 1 – Financial Solicitation Purpose: Gives TOR an idea of how much money they can expect to receive from a particular institution should they request a donation from that institution

  7. Query 1 – Financial Solicitation Application: 1) TOR will be able to target the most generous institutions in the future for financial aid. 2) Also, TOR can filter out the institutions that are expected to give the lowest donations and pursue them more aggressively in order to receive more donations.

  8. Query 1 – Financial Solicitation SQL (4 sections): TOR_Avg SELECT AVG(DT.Amount) AS TOR_Avg FROM Donates_To AS DT; ================================ All_Individual_Donations SELECT DT.SponsorID AS SponsorID, COUNT(DT.SponsorID) AS Num, AVG(DT.Amount) AS Avg_Donation FROM Donates_To AS DT GROUP BY [SponsorID]; ================================ Qualified_Donors SELECT * FROM All_Individual_Donations WHERE Num>2;

  9. Query 1 – Financial Solicitation Expected Donations SELECT DISTINCT DT.SponsorID AS SponsorID, I.InstitutionName AS Name,((QD.Num*QD.Avg_Donation)/(QD.Num+2))+((2* TA.TOR_Avg)/(QD.Num+2)) AS Weighted_Expected_Donation FROM Donates_To AS DT, Institution AS I, Qualifed_Donors AS QD, TOR_Avg AS TA WHERE (QD.SponsorID=DT.SponsorID And I.InstitutionID=DT.SponsorID And QD.SponsorID=I.InstitutionID);

  10. Query 2 – Most Referenced Papers Purpose: Returns the papers, grouped by their subject, that have been referenced the most by other papers.

  11. Query 2 – Most Referenced Papers Application: 1) Allows TOR to track papers that contain the most important, useful content 2) Helps TOR determine which topic is gaining momentum and is widely discussed in the industry.

  12. Query 2 – Most Referenced Papers SQL: SELECT S.Field, P.Title, COUNT(RI.Referencing_PID) AS Num_of_Times_Referenced FROM Paper AS P, References_Internal AS RI, Subject AS S, Is_On AS IO WHERE (P.PID=RI.Referenced_PID And P.PID=IO.PID And S.SubjectID=IO.SubjectID) GROUP BY S.Field, P.Title ORDER BY Num_of_Times_Referenced DESC;

  13. Query 3 – User Bias Purpose: Returns a list of users ranked by the number of times their ratings lie outside of the 90 percent confidence interval for each paper’s rating.

  14. Query 3 – User Bias Application: Enables TOR to identify and notify users that regularly give ratings that vary significantly from the norm

  15. Query 3 – User Bias SQL (3 sections): Ratings_Stats SELECT DISTINCT R.WorkingID, STDEV(R.InsightRating+R.ReadibilityRating) AS Rating_STD,AVG(R.InsightRating+R.ReadibilityRating) AS Avg_Rating FROM Reviews AS R GROUP BY R.WorkingID; ================================ Biased_Reviews SELECT R.ReaderID AS ReaderID, COUNT(R.ReaderID) AS Biased_Reviews FROM Ratings_Stats AS RS, Reviews AS R WHERE (ABS(R.InsightRating+R.ReadibilityRating- RS.Avg_Rating)>(1.25*RS.Rating_STD) And (R.WorkingID=RS.WorkingID)) GROUP BY R.ReaderID;

  16. Query 3 – User Bias SQL (continued): Biased_Reviewers(#3) SELECT DISTINCT BR.ReaderID AS ReaderID, U.Fname AS Fname, U.Lname AS Lname, U.Email AS Email,BR.Biased_Reviews FROM [User] AS U, Institution AS I, Biased_Reviews AS BR, Belongs_To AS BT WHERE (BR.ReaderID = U.UserID) ORDER BY BR.Biased_Reviews DESC;

  17. Query 4 – Time Until Publication Purpose: Returns a distribution that illustrates how long it takes for a paper to be published once submitted

  18. Query 4 – Time Until Publication Application: 1) TOR can better evaluate its publishing process 2) Show prospective authors approximate timetable if they submit a paper

  19. Query 4 – Time Until Publication SQL: SELECT DATEDIFF (“y”, P.DateSubmitted,Pu.DatePublished) AS Time_as_working_paper FROM Published AS Pu, Paper AS P WHERE P.PID = Pu.PublishedPaperID;

  20. Query 4 – Time Until Publication

  21. Query 5 – Paper Forecasts Purpose: Forecasts the number of papers that will be submitted in the upcoming month for each subject, using an exponential smoothing model

  22. Query 5 – Paper Forecasts Application: 1) Gives TOR a better grasp of underlying trends in the industry 2) Gives TOR understanding of which topics are the most popular among its readers

  23. Query 5 – Paper Forecasts SQL (3 Sections): LP_Query SELECT S.SubjectID, COUNT(P1.PID) AS Val FROM Paper AS P1, Is_On AS O, Subject AS S WHERE ((P1.DateSubmitted Between #1/1/1998# And #12/31/1998#) And P1.PID=O.PID And O.SubjectID=S.SubjectID) GROUP BY S.SubjectID; ================================ CP_Query SELECT S.SubjectID, COUNT(P1.PID) AS Val FROM Paper AS P1, Is_On AS O, Subject AS S WHERE ((P1.DateSubmitted Between #1/1/1999# And #12/31/1999#) And P1.PID=O.PID And O.SubjectID=S.SubjectID) GROUP BY S.SubjectID;

  24. Query 5 – Paper Forecasts SQL ( continued): Forecasting Papers (#5) SELECT DISTINCT S.Field, LP.Val AS Last_Period_Total, CP.Val AS This_Period_Total, 0.6*CP.Val+(1-0.6)*LP.Val AS Next_Period_Forecast FROM Subject AS S, CP_Query AS CP, LP_Query AS LP WHERE S.SubjectID=CP.SubjectID;

  25. Data Entry Forms

  26. Search Forms

  27. ? A++ Consulting

More Related