1 / 35

Database Design for UCOP: Business Resource Center

Database Design for UCOP: Business Resource Center. Vishal Baheti Walid Barake Monica Barin Sruti Bharat Brian Fong Pooja Mehta Alkey Pandya Divya Sharma. Presentation Overview. Client Introduction Objectives and Implementation

mahola
Télécharger la présentation

Database Design for UCOP: Business Resource Center

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. Database Design for UCOP:BusinessResourceCenter VishalBahetiWalidBarake Monica BarinSruti Bharat Brian Fong Pooja Mehta AlkeyPandyaDivya Sharma Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  2. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  3. Client Introduction • UCOP stands for the University of California Office of the President • UCOP is headquartered in downtown Oakland, where just over half of its total 1,400 employees work • UCOP is the administrative, budgetary, and policy-making body for the whole UC System • Within UCOP, our client is the Business Resource Center (BRC) • BRC is designed to centralize all transaction processing Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  4. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  5. Objectives and Implementation OurImplementation: • Organization of tables and relationships • Creation of Access forms • Access storage capability/efficiency • Login screen, switchboard directing to forms and handy tasks • Reports show user which employees have signature authority • Organized by account, department, or employee BRC Objectives: • To make data organization more efficient • To facilitate data entry • To increase storage capability • To have a more user-friendly interface • To improve data design with an emphasis on Signature Authorization data • Different employees have different levels of approval • Name, scope of authority, signature specimen (.jpeg), and approval Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  6. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  7. EER Diagram

  8. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  9. Relational Design • Employee (Employee_ID, Fname, Lname, MI, Location_Code, Email, Job_Title, Signature, Password, Department_number5, Supervisor ID1, Team ID) 1a. BRC (Employee_ID1, Team ID6) • Account (Account Number, Account Type, Account Title, Department_Code3, Department Name)Domain (Transaction Type) = {Expenditure, Liability, Asset, Revenue, Fund Balance} • Department (Department_Code, Department_Name, Subdivision Code4) • Subdivision (Subdivision Code, Subdivision Title, Subdivision Head Employee ID1, Division_Code5) • Division (Division Code, Division Title) • Team (Team ID) • Position (Position ID, Team ID6, Position Title) • Fund (Fund ID, Fund Type) Domain (Fund Type) = {Loan, Plant, Current) • Account_and_Fund (Account Number2, Fund ID8, Amount, Year Added) • Asset_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID1, Account_Number2, Object_Purpose, FAU, Authorizers_Signature1) Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  10. Relational Design (continued) • Expenditure_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID1, Account_Number2, Object_Purpose, FAU, Authorizers_Signature1) • Liability_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID1, Account_Number2, Object_Purpose, FAU, Authorizers_Signature1) • Revenue_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID1, Account_Number2, Object_Purpose, FAU, Authorizers_Signature1) • Other_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID1, Account_Number2, Object_Purpose, FAU, Authorizers_Signature1) • Exemption_on_Transaction (Delegator_Employee_ID1, Delegated_Employee_ID1, Account_Number2, Text_Limit, Authorizer_Signature1) • Delegate (Delegator_Employee_ID1, Delegated_Employee_ID1, Account_Number2, Limitation_on_Amount, Delegation_Date, Authorizer_Signature1) • Signature Authorization (Account_Number2, Employee_ID1, Limit, Authorizer_Signature1) Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  11. Access Relationship View Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  12. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  13. Normalization Analysis Normalization #1: • In 2NF: Subdivision (Subdivision Code, Subdivision Title, Division Code, Subdivision Head Employee ID) • Converting to 3NF: Subdivision (Subdivision Code, Subdivision Title) SubdivisionHead (Subdivision Title, Subdivision Head Employee ID) Division (Subdivision Head Employee ID, Division Code) Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  14. Normalization Analysis Analysis of Normalization #1: 3NF is not the most optimal arrangement of this relation. Dividing the original 2NF subdivision relation into one 3NF relation and one 2NF relation makes it more convenient to find division code through subdivision title. • Optimal Normalization: Subdivision (Subdivision Code, Subdivision Title) SubdivisionHead (Subdivision Title, Subdivision Head Employee ID, Division Code) Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  15. Normalization Analysis Normalization #2: • In 1NF: Delegate (Delegator ID, Delegated ID, Account Number, Limitation Amount, Delegation Date) • Converting to BCNF (and 3NF): Limitation (Delegated ID, Limitation Amount) Del_Date (Delegator ID, Delegated ID, Account Number, Delegation Date) Analysis of Normalization #2: In this case, the BCNF is beneficial because each employee’s limitation amount is easier to access. Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  16. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  17. Query 1 • Determines which fund types accounts are receiving money from • Purpose: • Allows BRC to look at fund usage percentage • Forecast yearly demand per fund type • Determine the stable sources of funding and thus allocate funds accordingly Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  18. Query 1 in SQL 2. Description: Finds the percentage for different types of fund that accounts are receiving money from per year SELECT Fund.[Fund Type], Sum([Account and Fund].Amount)/[Total Money].Total*100 AS Percentage, [Account and Fund].[Year Added] FROM [Total Money], [Account and Fund], Fund WHERE [Account and Fund].[Fund ID]=Fund.[Fund ID] And [Account and Fund].[Year Added]=[Total Money].[Year Added] GROUP BY [Fund].[Fund Type], [Total Money].Total, [Account and Fund].[Year Added] ORDER BY [Account and Fund].[Year Added] DESC; 1. Description: Finds total money that comes from all the funds for each year SELECT Sum([Account and Fund].Amount) AS Total, [Account and Fund].[Year Added] FROM [Account and Fund] GROUP BY [Account and Fund].[Year Added];

  19. Query 2 • Forecast account usage for next month • Purpose: • Allows BRC employees to know which accounts will need funding • IEOR technique: Forecasting (using 3-period weighted moving average on all accounts) • Assign previous 3 months each with separate weights • Assist BRC in budget planning for the next month Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  20. Query 2 in SQL 1. Description: Assigns weights to 3 months according to the 3PWMA method and forecasts transaction amount for next month by each account SELECT Mon.[Account Number], (0.5*Sum(Mon.Amount)+0.3*Sum(Mon_1.Amount)+0.2*Sum(Mon_2.Amount)) AS [Next Month's Forecast] FROM Mon, Mon AS Mon_1, Mon AS Mon_2 WHERE (((Mon.Month)=[Enter a Current Month:]) And ((Mon.Year)=[Enter Current Year:]) And Mon.[Account Number]=Mon_1.[Account Number] And Mon_1.[Account Number]=Mon_2.[Account Number] And Mon.[Account Number]=Mon_2.[Account Number]) GROUP BY Mon.Month, Mon.[Account Number], Mon.Year, Mon_2.Month, Mon_2.[Account Number], Mon_2.Year, Mon_1.Month, Mon_1.[Account Number], Mon_1.Year HAVING (((Mon_2.Month)=[Enter 2 months prior:]) AND ((Mon_2.Year)=[Enter Current Year of Month:]) AND ((Mon_1.Month)=[Enter a Previous Month:]) AND ((Mon_1.Year)=[Enter a Previous Year:])); Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  21. Query 3 • Rank employees based on Return on Investment (ROI) • Where Employee Transaction Processing Efficiency is how fast an employee processes transactions. Points are assigned according to this speed, relative to distance from mean processing time [z-score] for that transaction object code • Purpose: • Allows BRC to determine most productive and most underperforming employees Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  22. Query 3 in SQL 2. Description: Gets Zscore (how many standard deviations away from the mean was the processing time for that transaction) SELECT ([Process Time Average and Standard Deviation].AvgOfProcessedTime - ProcessedTime.ProcessedTime)/[Process Time Average and Standard Deviation].StdDeviationofProcessedTime AS ZScore, ProcessedTime.[Employee ID] FROM [Process Time Average and Standard Deviation], ProcessedTime WHERE ProcessedTime.[Object Code]=[Process Time Average and Standard Deviation].[Object Code]; 1. Description:Finds standard deviation of all transactions that have the same object code SELECT Avg(ProcessedTime.ProcessedTime) AS AvgOfProcessedTime, StDev(ProcessedTime.ProcessedTime) AS StdDeviationofProcessedTime, ProcessedTime.[Object Code] FROM ProcessedTime GROUP BY ProcessedTime.[Object Code];

  23. Query 3 in SQL (continued) 3. Description: Sums total points of employee SELECT (Sum(ZScore.ZScore)) AS SumOfPoints, ZScore.[Employee ID] FROM ZScore GROUP BY ZScore.[Employee ID]; 4. Description: Divides Sum By Employee Salary to Get Return on Investment SELECT ([Sum of Employee ZScore Points].SumOfPoints/Employee.Salary)*100000AS ReturnOnInvestment, [Sum of Employee ZScore Points].[Employee ID] FROM [Sum of Employee ZScore Points], Employee WHERE ((([ZScore].[Employee ID])=[Employee].[Employee ID])) ORDER BY [Sum of Employee ZScore Points].SumOfPoints/Employee.Salary DESC; Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  24. Query 4 • Determine order of transactions based on Shortest Processing Time (SPT) algorithm • Purpose: • To minimize total transaction flow time • IEOR technique: SPT algorithm • Allows employees to judge their productivity based on distance away from projected total flow time. • Allow BRC to see which employees have ability to process more transactions given current flow time Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  25. Query 4 in SQL 2. DESCRIPTION: Transactions that have been assigned but haven’t been started SELECT [Transaction Union].TID, [Transaction Union].[Employee ID], [Transaction Union].[Assigned Date], [Transaction Union].[Start Date], [Transaction Union].[End Date] FROM [Transaction Union] WHERE [Transaction Union].[Start Date] IS NULL ORDER BY [Transaction Union].[Employee ID]; 3. DESCRIPTION: Find the average of the process time grouped by object code. SELECT [Transaction Union].[Employee ID], DateDiff("d", [Transaction Union].[Start Date], [Transaction Union].[End Date]) AS ProcessedTime, [Transaction Union].[Object Code] FROM [Transaction Union]; SELECT Avg(ProcessedTime.ProcessedTime) AS AvgOfProcessedTime, ProcessedTime.[Object Code] FROM ProcessedTime GROUP BY ProcessedTime.[Object Code]; 1. DESCRIPTION: Union query of all transactions SELECT [Expenditure Transaction].TID, [Expenditure Transaction].[Employee ID], [Expenditure Transaction].[Assigned Date], [Expenditure Transaction].[Start Date], [Expenditure Transaction].[End Date] FROM [Expenditure Transaction] UNION ALL SELECT [Asset Transaction].TID, [Asset Transaction].[Employee ID], [Asset Transaction].[Assigned Date], [Asset Transaction].[Start Date], [Asset Transaction].[End Date] FROM [Asset Transaction] UNION ALL SELECT [Liability Transaction].TID, [Liability Transaction].[Employee ID], [Liability Transaction].[Assigned Date], [Liability Transaction].[Start Date], [Liability Transaction].[End Date] FROM [Liability Transaction] UNION ALL SELECT [Other Transaction].TID, [Other Transaction].[Employee ID], [Other Transaction].[Assigned Date], [Other Transaction].[Start Date], [Other Transaction].[End Date] FROM [Other Transaction] UNION ALL SELECT [Revenue Transaction].TID, [Revenue Transaction].[Employee ID], [Revenue Transaction].[Assigned Date], [Revenue Transaction].[Start Date], [Revenue Transaction].[End Date] FROM [Revenue Transaction]

  26. Query 4 in SQL (continued) 4. Description: Average process time becomes the effective process time for all transactions with the same object code. SELECT [Transaction Not Started].[Employee ID], [Transaction Not Started].TID, AverageProcessedTime.[Object Code], AverageProcessedTime.AvgOfProcessedTime FROM AverageProcessedTime, [Transaction Not Started] WHERE AverageProcessedTime.[Object Code]=[Transaction Not Started].[Object Code] ORDER BY [Transaction Not Started].[Employee ID], AverageProcessedTime.[AvgOfProcessedTime]; Link to Excel Worksheet to calculate total flow time and display order in which transactions should be processed (for each employee) Total Flow time for Employee 111111111 Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  27. Query 5 • Determine the number of transactions for the following year, arranged by quarter and based on transaction purpose • Purpose: • To forecast expected workload per quarter and prepare for fluctuations in demand of transactions. • minimize total transaction flow time • IEOR technique: Forecasting, by using quarterly data based on object code. Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  28. Query 5 in SQL 1. DESCRIPTION: Find the frequency of object code by average between 2 years SELECT Count([Transaction Union].[Object Code])/2 AS ObjectCodeFrequency, [Transaction Union].[Object Code], DatePart("q",[Transaction Union].[Assigned Date]) AS Quarter FROM [Transaction Union] WHERE (((Month([Transaction Union].[Assigned Date]))>0 And (Month([Transaction Union].[Assigned Date]))<4) And ((Year([Transaction Union].[Assigned Date]))=2010 Or (Year([Transaction Union].[Assigned Date]))=2009)) GROUP BY [Transaction Union].[Object Code], DatePart("q",[Transaction Union].[Assigned Date]); 2. DESCRIPTION: Union all of the Quarters to get a compilation SELECT [Quarter 2].ObjectCodeFrequency, [Quarter 2].[Object Code], [Quarter 2].Quarter FROM [Quarter 2] UNION ALL SELECT [Quarter 3].ObjectCodeFrequency, [Quarter 3].[Object Code], [Quarter 3].Quarter FROM [Quarter 3] UNION ALL SELECT [Quarter 4].ObjectCodeFrequency, [Quarter 4].[Object Code], [Quarter 4].Quarter FROM [Quarter 4] UNION ALL SELECT [Quarter 1].ObjectCodeFrequency, [Quarter 1].[Object Code], [Quarter 1].Quarter FROM [Quarter 1]; Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  29. Presentation Overview • Client Introduction • Objectives and Implementation • EER Diagram • Relational Design • Normalization Analysis • Query Design • Access Implementation Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  30. Login Interface Employees login here: Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  31. Possible Login Errors If the user does not select a user name, the following error dialog appears: • If the user enters an invalid password, the following error dialog appears: After 3 unsuccessful attempts, the database will exit: Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  32. Successful Login User is allowed access when they enter a valid user name and password (example above). The following dialog then appears: Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  33. Successful Login (continued) Once logged in, it takes the user directly to the switchboard: And then the user can select any task from the button selection above. Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  34. Q/A Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

  35. Thank You Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

More Related