presents

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
TOR_ACCOUNTS
USER
ADMINISTRATOR
EDITOR
SUBJECT
INSTITUTION
UNREGISTERED
UNDER REVIEW
(0,M)
AUTHOR
Views
E
READER
(0,N)
PAPER
WORKING
PUBLISHED
KEYWORD
A Consulting
OUTSIDE_PAPER
6
(No Transcript)
7
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

8
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.

9
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 Numgt2

10
Query 1 Financial Solicitation

• Expected Donations
• SELECT DISTINCT DT.SponsorID AS SponsorID,
  I.InstitutionName AS Name,((QD.NumQD.Avg_Donatio
  n)/(QD.Num2))((2 TA.TOR_Avg)/(QD.Num2)) AS
  Weighted_Expected_Donation
• FROM Donates_To AS DT, Institution AS I,
  Qualifed_Donors AS QD, TOR_Avg AS TA
• WHERE (QD.SponsorIDDT.SponsorID And
  I.InstitutionIDDT.SponsorID And
  QD.SponsorIDI.InstitutionID)

11
Query 2 Most Referenced Papers

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

12
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.
  

13
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.PIDRI.Referenced_PID And
  P.PIDIO.PID And S.SubjectIDIO.SubjectID)
• GROUP BY S.Field, P.Title
• ORDER BY Num_of_Times_Referenced DESC

14
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 papers rating.

15
Query 3 User Bias

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

16
Query 3 User Bias

• SQL (3 sections)
• Ratings_Stats
• SELECT DISTINCT R.WorkingID, STDEV(R.InsightRating
  R.ReadibilityRating) AS Rating_STD,AVG(R.Insight
  RatingR.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.InsightRatingR.ReadibilityRating- RS
  .Avg_Rating)gt(1.25RS.Rating_STD) And
  (R.WorkingIDRS.WorkingID))
• GROUP BY R.ReaderID

17
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

18
Query 4 Time Until Publication

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

19
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

20
Query 4 Time Until Publication

• SQL
• SELECT DATEDIFF (y, P.DateSubmitted,Pu.DatePub
  lished)
• AS Time_as_working_paper
• FROM Published AS Pu, Paper AS P
• WHERE P.PID Pu.PublishedPaperID

21
Query 4 Time Until Publication
22
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

23
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

24
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.PIDO.PID And
  O.SubjectIDS.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.PIDO.PID And
  O.SubjectIDS.SubjectID)
• GROUP BY S.SubjectID

25
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.6CP.Val(1-0.6)LP.Val AS Next_Period_Forecast
  
• FROM Subject AS S, CP_Query AS CP, LP_Query AS
  LP WHERE S.SubjectIDCP.SubjectID

26
Data Entry Forms
27
Search Forms
28
?
A Consulting