Tracking Computer Inventory for Kaiser Permanente Presenters Wilson

Tracking Computer Inventory for Kaiser Permanente Presenters Wilson Pranoto Muliawati Mandiro Marlina Kosasih

Overview In the next 10 minutes… l Company Background and Purpose l EER l Relational Schema l Normalization l Queries in SQL and in Access l Query Snapshots l IEOR Method l Conclusion

The Organization Established in 1950 s by Dr. Sidney Garfield Now … l America’s largest non-profit health organization l Committed to provide the best health service; thus efficient database is needed! What Kaiser Need? l Efficient & Reliable Database

Current Setup (Simplified) Printer Server Computer # 1 Computer # 2 Speed (Mhz), RAM (Mb), HD (Gb), Printer, etc . . . Computer # N

Raw Data Spreadsheet

(0, N) (1, M) Retailer Ordered_By (0, N) Planner (0, N) (1, 1) (1, M) Has 2 Made_By (1, M) Bought_from Located_At (1, 1) (1, M) Planner ID Has 1 Employee should (1, M) (0, 1) (0, N) (1, 1) Connected_To Room (1, M) (0, N) Time Schedule (1, 1) O Dept. (1, N) works Connected_To Date (1, N) Maintainer Owner O Hardware Maintainer (0, N) (1, 1) (0, 1) Owned_By O RAM Price Start Date (0, N) HD Price (1, 1) costs (1, 1) Time (1, 1) costs Software (0, N) (0, 1) Installed_On (1, N) (0, 1) (1, N) OS Type Include (0, N) (1, M) Skill level d Components Includes (0, N) (0, M) (0, N) (1, M) (1, 1) Type Experience_In Printer End Works_on (1, N) Router (0, M) Repaired_By Computer Software Maintainer (0, N) Mainframe (1, 1) Used_By User (1, 1) Located_At Inventory Date Port (0, N) (0, M) Connected_To Manufacturer Distributed_By Runs_on (1, 1) Utility Includes

Relational Schema

M-N Relationship

Normalization Employee (SSN EID Lastname MI First. Name Birthday Sex Salary Position Hiredate Comment) l l Repaired_by Rb (Maint. EID IID Repair_date Reason Repairable Cost_of_replacement Reason. ID) Rb 1 (Maint. EIID Repair_date Reason. ID Repairable Cost_of_replacement) Rb 2 (Reason. ID Reason)

Access Interface

Hardware Upgrade Query Purpose To help IT managers in deciding which computer needs to be upgraded by providing some information about the computer’s utilization and their upgrading price. SELECT DISTINCT [UPGRADING]. IID, UPGRADING. RAM_REQ, UPGRADING. RAM_UPGRADE, RAM_PRICE. price AS RAM_PRICE, RAM_PRICE. RName AS BOUGHT_RAM_FROM, UPGRADING. HD_REQ, UPGRADING. HD_UPGRADE, HD_PRICE. price AS HD_PRICE, HD_PRICE. RName AS BOUGHT_HD_FROM, (RAM_PRICE. [price]+HD_PRICE. [price]) AS [TOTAL PRICE], UPGRADING. UTILIZATION FROM UPGRADING, HD_PRICE, RAM_PRICE WHERE (((RAM_PRICE. price) In (SELECT MIN(RAM_PRICE. price) FROM RAM_PRICE WHERE(RAM_PRICE. type)=UPGRADING. [RAM_UPGRADE])) AND ((HD_PRICE. price) In (SELECT MIN(HD_PRICE. price) FROM HD_PRICE WHERE (HD_PRICE. type)= UPGRADING. [HD_UPGRADE]))) ORDER BY UPGRADING. UTILIZATION DESC;

Hardware Upgrade Query (con’t) UPGRADING QUERY (Sub Query) SELECT COMPUTER. [IID], =IIf([Enter RAM min req]-COMPUTER. [RAM]>0, [Enter RAM min req]-COMPUTER. [RAM], 0) AS RAM_REQ, =IIf([Enter RAM min req]-COMPUTER. [RAM]>0, (1+Int(([Enter RAM min req]COMPUTER. [RAM])/32))*32, 0) AS RAM_UPGRADE, =IIf([Enter Hard. Disk min req]-COMPUTER. [Hard. Disk]>0, [Enter Hard. Disk min req]-COMPUTER. [Hard. Disk], 0) AS HD_REQ, =IIf([Enter Hard. Disk min req]-COMPUTER. [Hard. Disk]>0, (1+Int(([Enter HARDDISK min req]-COMPUTER. [Hard. Disk])/60))*60, 0) AS HD_UPGRADE Sum(USER. [Logout_time]-USER. [Login_time])/(24*365) AS UTILIZATION FROM COMPUTER INNER JOIN USER ON COMPUTER. [User. ID]=USER. [EID] WHERE (((COMPUTER. [RAM])<[Enter RAM min req])) Or (((COMPUTER. [Hard. Disk])<[Enter Hard. Disk min req])) GROUP BY COMPUTER. [IID], COMPUTER. [Hard. Disk], COMPUTER. [RAM];

In Access …

Schedule Requirement Query Purpose To have the ability to change or update the scheduled requirements as well as the maintainer’s schedules coinciding with the needed personnel for such departments like the ‘emergency room”. This also determines whether they need to hire more part time or full time employees to supply the possible demand of maintainers. SELECT PLANNER. PDay, PLANNER. [PStarting Time], PLANNER. [PEnding Time] FROM PLANNER WHERE (((NOT EXISTS (Select WORKS. Day, WORKS. [Starting Time], WORKS. [Ending Time] From WORKS Where PLANNER. PDay = WORKS. Day AND PLANNER. [PStarting Time] = WORKS. [Starting Time] AND PLANNER. [PEnding Time] = WORKS. [Ending Time]))=False));

In Access …

Bad Component Query Purpose To help the management decide which brand they should buy or avoid to replenish their inventory. COMPONENT 1 QUERY (Sub Query) SELECT DISTINCT REPAIRED_BY. [IID], COMPONENT. [CBrand], Count(COMPONENT. [IID]) AS No. Of. Repairs FROM COMPONENT, REPAIRED_BY WHERE (((COMPONENT. [IID])=REPAIRED_BY. [IID]) And (([enter year here])>COMPONENT. [Year bought] And ([enter year here])<=(COMPONENT. [Year bought]+1))) GROUP BY REPAIRED_BY. [IID], COMPONENT. [CBrand] HAVING (((Count(COMPONENT. IID))>2)); COMPONENT_Bad QUERY (Sub Query) SELECT COMPONENT 1. [CBrand], Count(COMPONENT 1. [IID]) AS No. Of. Bad. IID FROM COMPONENT 1 GROUP BY COMPONENT 1. [CBrand];

Bad Component Query (con’t) COMPONENT_Total QUERY (Sub Query) SELECT COMPONENT. [CBrand], Count(COMPONENT. [IID]) AS No. Of. Total. IID FROM COMPONENT GROUP BY COMPONENT. [CBrand]; MAIN QUERY SELECT DISTINCT C 2. CBrand AS BRAND, (C 3. No. Of. Bad. IID/C 2. No. Of. Total. IID) AS RATIO FROM COMPONENT_Total AS C 2, COMPONENT_Bad AS C 3;

In Access …

Forecasting Query Purpose To help forecast the needed maintainers, the number of needed repairs, and it can also predict the budget needed for IT Department. SELECT NB. Department, Sum(NB. No. Breakdown) AS Total. Breakdown, Avg(NB. No. Breakdown) AS Expected. Value. Of. Breakdowns, Avg(NB. [No. Breakdown])+(1. 96*St. Dev(NB. [No. Breakdown]))AS UB, =IIf(Avg(NB. [No. Breakdown])- (1. 96*St. Dev(NB. [No. Breakdown]))>0, Avg(NB. [No. Breakdown])-(1. 96*St. Dev(NB. [No. Breakdown])), 0) AS LB FROM Number_of_Breakdown AS NB GROUP BY NB. Department; NUMBER OF BREAKDOWN Query (Sub Query) SELECT I. [IID], I. [Department], Count(RB. [IID]) AS No. Breakdown FROM INVENTORY AS I INNER JOIN REPAIRED_BY AS RB ON I. [IID]=RB. [IID] WHERE (((I. [IIDType])="Computer") And ((RB. [Repairable])=Yes) And ((I. [Used_date]) Between #1/1/2001# And #12/31/2001#)) Or (((RB. [Repairable])=No)) GROUP BY I. [IID], I. [Department];

Forecasting Report

‘Point’ For Workers Query Purpose To know worker’s utilization and their contributions. In addition, the management can decide which employees to be promoted (such as bonuses or awards) or terminated. SELECT MP. [Maint. EID], Sum(MP. [Points]) AS [Total Points] FROM MAINTAINERPOINT AS MP GROUP BY MP. [Maint. EID] ORDER BY MP. [Maint. EID]; MAINTAINER POINT QUERY SELECT Maint. EID, Points 1, Points 2, Points 1*Points 2 AS Points FROM REPAIRED_BY_POINT UNION ALL SELECT Maint. EID, Points 1, Points 2, Points 1*Points 2 AS Points FROM WORKS_ON_POINT;

‘Point’ For Workers Query (con’t) REPAIRED BY POINT QUERY SELECT M. Maint. EID, =IIf(([RB. Reason. ID])<5, 1, 2) AS Points 1, =IIf(([RB. End_Time] [RB. Start_Time])<0. 04167, 2, 1) AS Points 2 FROM Repaired_By AS RB, (SELECT DISTINCT M. Maint. EID FROM Maintainer AS M) WHERE (((M. Maint. EID)=RB. Maint. EID) And (RB. Repair_Date Between [Start Date (for example 01/01/2002)] And [End Date (for example 12/31/2002)])); WORKS ON POINT QUERY SELECT M. Maint. EID, =IIf(([WO. Reason. ID])<5, 1, 2) AS Points 1, =IIf(([WO. End_Time] [WO. Start_Time])<0. 04167, 2, 1) AS Points 2 FROM Works_On AS WO, (SELECT DISTINCT M. Maint. EID FROM Maintainer AS M) WHERE (((M. Maint. EID)=WO. Maint. EID) And (WO. Work_Date Between [Start Date (for example 01/01/2002)] And [End Date (for example 12/31/2002)]));

In Access …

Linear Programming Application l We want to use queries to do LP for maximizing utilization of remaining budget that IT department has for the year. l We use database to help estimate the remaining budget.

Variables l l l l l X 1 R: # Employee Hours needed for regular X 1 C: # Employee Hours needed for On Call X 2: # Software Upgraded X 3: # Purchasing new computer X 4: # Computer Repaired X 5: # buying new printer X 6: # purchasing router X 7: # building/creating/expanding network X 8 J: # purchasing component (J = 1… n)

Linear Program l l l l l Max s. t (X 1 * Salary) + (X 2 * Upgrading Cost) + (X 3 * computer_cost) + (X 4 * (average_repair_cost-employee_cost)) + (X 5 * printer cost) + (X 6 * new router cost) + (X 7 * Cost building new network) + ( X 81 * monitor cost) + ( X 82 * new keyboard cost) ≤ remaining budget X 1 ≤ (# employee needed to satisfy maintainer_requirement) X 2 ≤ (requested upgrading) X 3 ≤ (sum computer requirement from each department) X 4 ≤ (Forecasting # of breakdown for remaining time) X 5 ≤ (# required) – (# available) + (# working computer) X 6 ≤ requirement X 7 ≤ (# purchased computer) + (# current working computer)

User Input From Data Needed Function Query Salary Upgrading Cost Upgrading Query Computer Cost Average Repair Cost Average (Cost of Repairing) Printer Cost New Router Cost of building up a new network Monitor Cost Keyboard Cost Remaining budget Annual budget - current expenses Expenses Include: a) Repaired Breakdown 2002 Sum (cost of breakdown) b) Employee Salary 2002 Sum (current employee * salary) c) Purchasing new equipment Sum (purchase * cost) Number of Employee hours needed Schedule Requirement Query Request Upgrading Total Computer Sum(computer) GROUP BY Dept) Number of Computer breakdown Forecasting Query Number of Printer breakdown Count(Broken Printer) Number of working computer Count(Broken Computer)

Thank You!!