Database Design for UCOP Business Resource Center Vishal

Database Design for UCOP: Business Resource Center Vishal Baheti Walid Barake Monica Barin Brian Fong Pooja Mehta Alkey Pandya Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010. Sruti Bharat Divya Sharma

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.

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.

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.

Objectives and Implementation BRC Objectives: 1. 2. 3. 4. 5. 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 Our Implementation: 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 Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

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.

EER Diagram

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.

Relational Design 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. Employee (Employee_ID, Fname, Lname, MI, Location_Code, Email, Job_Title, Signature, Password, Department_number 5, Supervisor ID 1, Team ID) 1 a. BRC (Employee_ID 1 , Team ID 6) Account (Account Number, Account Type, Account Title, Department_Code 3, Department Name) Domain (Transaction Type) = {Expenditure, Liability, Asset, Revenue, Fund Balance} Department (Department_Code, Department_Name, Subdivision Code 4) Subdivision (Subdivision Code, Subdivision Title, Subdivision Head Employee ID 1, Division_Code 5) Division (Division Code, Division Title) Team (Team ID) Position (Position ID, Team ID 6, Position Title) Fund (Fund ID, Fund Type) Domain (Fund Type) = {Loan, Plant, Current) Account_and_Fund (Account Number 2, Fund ID 8, Amount, Year Added) Asset_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID 1, Account_Number 2, Object_Purpose, FAU, Authorizers_Signature 1) Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

Relational Design (continued) 11. 12. 13. 14. 15. 16. 17. Expenditure_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID 1, Account_Number 2, Object_Purpose, FAU, Authorizers_Signature 1) Liability_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID 1, Account_Number 2, Object_Purpose, FAU, Authorizers_Signature 1) Revenue_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID 1, Account_Number 2, Object_Purpose, FAU, Authorizers_Signature 1) Other_Transaction (TID, Transaction_Type, Assigned_Date, Start_Date, End_Date, PID, Object_Code, Amount, Employee_ID 1, Account_Number 2, Object_Purpose, FAU, Authorizers_Signature 1) Exemption_on_Transaction (Delegator_Employee_ID 1, Delegated_Employee_ID 1, Account_Number 2, Text_Limit, Authorizer_Signature 1) Delegate (Delegator_Employee_ID 1, Delegated_Employee_ID 1, Account_Number 2, Limitation_on_Amount, Delegation_Date, Authorizer_Signature 1) Signature Authorization (Account_Number 2, Employee_ID 1, Limit, Authorizer_Signature 1) Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

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

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.

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

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

Normalization Analysis Normalization #2: In 1 NF: Delegate (Delegator ID, Delegated ID, Account Number, Limitation Amount, Delegation Date) Converting to BCNF (and 3 NF): 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.

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.

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.

Query 1 in SQL 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]; 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;

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.

Query 2 in SQL 1. Description: Assigns weights to 3 months according to the 3 PWMA 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. Amou nt)+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 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.

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.

Query 3 in SQL 1. Description: Finds standard deviation of all transactions that have the same object code SELECT Avg(Processed. Time. Processed. Tim e) AS Avg. Of. Processed. Time, St. Dev(Processed. Time. Processed. T ime) AS Std. Deviationof. Processed. Time, Processed. Time. [Object Code] FROM Processed. Time GROUP BY Processed. Time. [Object Code]; 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]. Avg. Of. Processed. Time)/[Proc ess Time Average and Standard Deviation]. Std. Deviationof. Processed. Ti me AS ZScore, Processed. Time. [Employee ID] FROM [Process Time Average and Standard Deviation], Processed. Time WHERE Processed. Time. [Object Code]=[Process Time Average and Standard Deviation]. [Object Code];

Query 3 in SQL (continued) 3. Description: Sums total points of employee 4. Description: Divides Sum By Employee Salary to Get Return on Investment SELECT (Sum(ZScore)) AS Sum. Of. Points, ZScore. [Employee ID] FROM ZScore GROUP BY ZScore. [Employee ID]; SELECT ([Sum of Employee ZScore Points]. Sum. Of. Points/Employee. S alary)*100000 AS Return. On. Investment, [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]. Sum. Of. Points/Employee. S alary DESC; Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

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.

Query 4 in SQL 1. DESCRIPTION: transactions Union query of all 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] 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], Date. Diff("d", [Transaction Union]. [Start Date], [Transaction Union]. [End Date]) AS Processed. Time, [Transaction Union]. [Object Code] FROM [Transaction Union]; SELECT Avg(Processed. Time) AS Avg. Of. Processed. Time, Processed. Time. [Object Code] FROM Processed. Time GROUP BY Processed. Time. [Object Code];

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, Average. Processed. Time. [Object Code], Average. Processed. Time. Avg. Of. Processed Time FROM Average. Processed. Time, [Transaction Not Started] WHERE Average. Processed. Time. [Object Code]=[Transaction Not Started]. [Object Code] ORDER BY [Transaction Not Started]. [Employee ID], Average. Processed. Time. [Avg. Of. Processe d. Time]; 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 11111 Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

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.

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 Object. Code. Frequency, [Transaction Union]. [Object Code], Date. Part("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], Date. Part("q", [Transaction Union]. [Assigned Date]); 2. DESCRIPTION: Union all of the Quarters to get a compilation SELECT [Quarter 2]. Object. Code. Frequency, [Quarter 2]. [Object Code], [Quarter 2]. Quarter FROM [Quarter 2] UNION ALL SELECT [Quarter 3]. Object. Code. Frequency, [Quarter 3]. [Object Code], [Quarter 3]. Quarter FROM [Quarter 3] UNION ALL SELECT [Quarter 4]. Object. Code. Frequency, [Quarter 4]. [Object Code], [Quarter 4]. Quarter FROM [Quarter 4] UNION ALL SELECT [Quarter 1]. Object. Code. Frequency, [Quarter 1]. [Object Code], [Quarter 1]. Quarter FROM [Quarter 1]; Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

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.

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

Possible Login Errors If. If the. After user 3 does enters unsuccessful not anselect invalid attempts, a password, user name, the database thefollowing willerror exit: errordialogappears: Team 3. Professor Ken Goldberg. IEOR 115. December 10, 2010.

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.

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.

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

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