slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
presents PowerPoint Presentation
Download Presentation
presents

Loading in 2 Seconds...

play fullscreen
1 / 28

presents - PowerPoint PPT Presentation


  • 170 Views
  • Uploaded on

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

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 'presents' - sally


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

A++ Consulting Group

presents

TOR:

Technology & Operations Review

slide2

A++ Consulting

Our Team

Angela Carlin

Thomas Choi

Matthew Hedges

Matthew Iong

Harsh Karmarkar

David Ng

Ryan Salcedo

A++ Consulting

slide3

Executive Summary

  • Company Review
  • EER Diagram
  • Verbal Explanation of Queries
  • Implementation in Access
  • Q & A
slide4

Demand driven online publication

  • Industry papers reviewed and published
  • Editors around the globe

A++ Consulting

slide5

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

query 1 financial solicitation
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

slide8

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.

slide9

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;

slide10

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);

slide11

Query 2 – Most Referenced Papers

Purpose:

Returns the papers, grouped by their subject, that have been referenced the most by other papers.

slide12

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.

slide13

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;

slide14

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.

slide15

Query 3 – User Bias

Application:

Enables TOR to identify and notify users that regularly give ratings that vary significantly from the norm

slide16

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;

slide17

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;

slide18

Query 4 – Time Until Publication

Purpose:

Returns a distribution that illustrates how long it takes for a paper to be published once submitted

slide19

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

slide20

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;

slide22

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

slide23

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

slide24

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;

slide25

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;

slide28

?

A++ Consulting