tracking computer inventory for kaiser permanente n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Tracking Computer Inventory for Kaiser Permanente PowerPoint Presentation
Download Presentation
Tracking Computer Inventory for Kaiser Permanente

Loading in 2 Seconds...

play fullscreen
1 / 28

Tracking Computer Inventory for Kaiser Permanente - PowerPoint PPT Presentation


  • 109 Views
  • Uploaded on

Tracking Computer Inventory for Kaiser Permanente. Presenters Wilson Pranoto Muliawati Mandiro Marlina Kosasih. Overview. In the next 10 minutes… Company Background and Purpose EER Relational Schema Normalization Queries in SQL and in Access Query Snapshots IEOR Method Conclusion.

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 'Tracking Computer Inventory for Kaiser Permanente' - hammer


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
tracking computer inventory for kaiser permanente

Tracking Computer Inventory for Kaiser Permanente

Presenters

Wilson Pranoto

Muliawati Mandiro

Marlina Kosasih

overview
Overview

In the next 10 minutes…

  • Company Background and Purpose
  • EER
  • Relational Schema
  • Normalization
  • Queries in SQL and in Access
  • Query Snapshots
  • IEOR Method
  • Conclusion
the organization
The Organization

Established in 1950s by Dr. Sidney Garfield

Now …

  • America’s largest non-profit health organization
  • Committed to provide the best health service; thus efficient database is needed!

What Kaiser Need?

  • Efficient & Reliable Database
current setup simplified
Current Setup (Simplified)

Printer

Server

Computer # 1

Speed (Mhz), RAM (Mb), HD (Gb), Printer, etc

. . .

Computer # 2

Computer # N

slide6

Installed_On

Works_on

Date

End

Start

Time

(0,N)

(0,M)

(0,N)

(1,M)

(0,N)

Connected_To

Retailer

Manufacturer

Distributed_By

(1,1)

Port

Ordered_By

(0,N)

(0,N)

(0,N)

(1, M)

Has 2

(0,M)

(1, M)

Made_By

Located_At

Planner

Bought_from

(0,1)

(1,1)

(0,N)

(0,N)

(1, M)

(1, M)

(1,1)

Planner ID

Has 1

Employee

Room

Connected_To

should

(1, M)

(0,N)

(0,N)

(1,1)

(0,N)

Time

Schedule

Inventory

Mainframe

Located_At

Date

(1,1)

(1,1)

(1,1)

Dept.

(1,N)

O

User

Used_By

(1,N)

Connected_To

works

Date

(1,1)

(1,N)

(1,N)

(0,1)

O

Owner

Router

Owned_By

Maintainer

O

Hardware Maintainer

(0,N)

(0,M)

Repaired_By

RAM Price

HD Price

Printer

(1,1)

(1,1)

Computer

Software Maintainer

(1,M)

costs

costs

(1,1)

(1,1)

Components

Includes

(0,N)

Type

(0,1)

(1,N)

Type

Include

(0,N)

(1,M)

(0,N)

(0,N)

Software

Experience_In

(1,N)

Includes

(0,1)

Skill level

(1,N)

d

OS

Runs_on

(1,1)

Utility

normalization
Normalization
  • Employee

(SSN EID Lastname MI FirstName Birthday Sex Salary Position Hiredate Comment)

  • Repaired_by

Rb (MaintEIDIIDRepair_date Reason Repairable Cost_of_replacement ReasonID)

Rb1 (MaintEIIDIIDRepair_dateReasonID Repairable Cost_of_replacement)

Rb2 (ReasonID Reason)

hardware upgrade query
Hardware Upgrade Query

PurposeTo help IT managers in deciding which computer needs to be upgraded by providing some information about the computer’s utilization and their upgrading price.

SELECTDISTINCT [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
Hardware Upgrade Query (con’t)

UPGRADING QUERY (Sub Query)

SELECTCOMPUTER.[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 HardDisk min req]-COMPUTER.[HardDisk]>0,[Enter HardDisk min req]-COMPUTER.[HardDisk],0) AS HD_REQ, =IIf([Enter HardDisk min req]-COMPUTER.[HardDisk]>0,(1+Int(([Enter HARDDISK min req]-COMPUTER.[HardDisk])/60))*60,0) AS HD_UPGRADE Sum(USER.[Logout_time]-USER.[Login_time])/(24*365) AS UTILIZATION

FROM COMPUTER INNER JOIN USER ON COMPUTER.[UserID]=USER.[EID]

WHERE(((COMPUTER.[RAM])<[Enter RAM min req])) Or

(((COMPUTER.[HardDisk])<[Enter HardDisk min req]))

GROUP BYCOMPUTER.[IID], COMPUTER.[HardDisk], COMPUTER.[RAM];

schedule requirement query
Schedule Requirement Query

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

SELECTPLANNER.PDay, PLANNER.[PStarting Time], PLANNER.[PEnding Time]

FROMPLANNER

WHERE(((NOT EXISTS

(SelectWORKS.Day, WORKS.[Starting Time], WORKS.[Ending Time]

FromWORKS Where PLANNER.PDay = WORKS.Day AND PLANNER.[PStarting Time] = WORKS.[Starting Time] AND

PLANNER.[PEnding Time] = WORKS.[Ending Time]))=False));

bad component query
Bad Component Query

Purpose To help the management decide which brand they should buy or avoid to replenish their inventory.

COMPONENT1 QUERY (Sub Query)

SELECTDISTINCT REPAIRED_BY.[IID], COMPONENT.[CIID], COMPONENT.[CBrand], Count(COMPONENT.[IID]) AS NoOfRepairs

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 BYREPAIRED_BY.[IID], COMPONENT.[CIID], COMPONENT.[CBrand]

HAVING (((Count(COMPONENT.IID))>2));

COMPONENT_Bad QUERY (Sub Query)

SELECTCOMPONENT1.[CBrand], Count(COMPONENT1.[IID])

AS NoOfBadIID

FROMCOMPONENT1

GROUP BYCOMPONENT1.[CBrand];

bad component query con t
Bad Component Query (con’t)

COMPONENT_Total QUERY (Sub Query)

SELECTCOMPONENT.[CBrand], Count(COMPONENT.[IID]) AS NoOfTotalIID

FROM COMPONENT

GROUP BYCOMPONENT.[CBrand];

MAIN QUERY

SELECT DISTINCT C2.CBrand AS BRAND,(C3.NoOfBadIID/C2.NoOfTotalIID) AS RATIO

FROM COMPONENT_Total AS C2, COMPONENT_Bad AS C3;

forecasting query
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.

SELECTNB.Department, Sum(NB.NoBreakdown) AS TotalBreakdown,

Avg(NB.NoBreakdown) AS ExpectedValueOfBreakdowns, Avg(NB.[NoBreakdown])+(1.96*StDev(NB.[NoBreakdown]))AS UB, =IIf(Avg(NB.[NoBreakdown])- (1.96*StDev(NB.[NoBreakdown]))>0, Avg(NB.[NoBreakdown])-(1.96*StDev(NB.[NoBreakdown])),0) AS LB

FROM Number_of_Breakdown AS NB

GROUP BY NB.Department;

NUMBER OF BREAKDOWN Query (Sub Query)

SELECTI.[IID], I.[Department], Count(RB.[IID]) AS NoBreakdown

FROMINVENTORY 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 BYI.[IID], I.[Department];

point for workers query
‘Point’ For Workers Query

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

SELECTMP.[MaintEID], Sum(MP.[Points]) AS [Total Points]

FROMMAINTAINERPOINT AS MP

GROUP BYMP.[MaintEID]

ORDER BYMP.[MaintEID];

MAINTAINER POINT QUERY

SELECTMaintEID, Points1, Points2, Points1*Points2 AS Points

FROMREPAIRED_BY_POINT UNION ALL

SELECTMaintEID, Points1, Points2, Points1*Points2 AS Points

FROMWORKS_ON_POINT;

point for workers query con t
‘Point’ For Workers Query (con’t)

REPAIRED BY POINT QUERY

SELECT M.MaintEID, =IIf(([RB.ReasonID])<5,1,2) AS Points1, =IIf(([RB.End_Time]-

[RB.Start_Time])<0.04167,2,1) AS Points2

FROM Repaired_By AS RB, (SELECT DISTINCT M.MaintEID

FROM Maintainer AS M)

WHERE (((M.MaintEID)=RB.MaintEID) 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.MaintEID, =IIf(([WO.ReasonID])<5,1,2) AS Points1, =IIf(([WO.End_Time]-

[WO.Start_Time])<0.04167,2,1) AS Points2

FROM Works_On AS WO, (SELECT DISTINCT M.MaintEID

FROM Maintainer AS M)

WHERE (((M.MaintEID)=WO.MaintEID) And (WO.Work_Date Between

[Start Date (for example 01/01/2002)] And

[End Date (for example 12/31/2002)]));

linear programming application
Linear Programming Application
  • We want to use queries to do LP for maximizing utilization of remaining budget that IT department has for the year.
  • We use database to help estimate the remaining budget.
variables
Variables
  • X1R: # Employee Hours needed for regular
  • X1C: # Employee Hours needed for On Call
  • X2: # Software Upgraded
  • X3: # Purchasing new computer
  • X4: # Computer Repaired
  • X5: # buying new printer
  • X6: # purchasing router
  • X7: # building/creating/expanding network
  • X8J: # purchasing component (J = 1… n)
linear program
Linear Program
  • Max

s.t

  • (X1 * Salary) + (X2 * Upgrading Cost) + (X3 * computer_cost) + (X4 * (average_repair_cost-employee_cost)) + (X5 * printer cost) + (X6 * new router cost) + (X7 * Cost building new network) + ( X81 * monitor cost) + ( X82 * new keyboard cost) ≤ remaining budget
  • X1 ≤ (# employee needed to satisfy maintainer_requirement)
  • X2 ≤ (requested upgrading)
  • X3 ≤ (sum computer requirement from each department)
  • X4 ≤ (Forecasting # of breakdown for remaining time)
  • X5 ≤ (# required) – (# available) + (# working computer)
  • X6 ≤ requirement
  • X7 ≤ (# purchased computer) + (# current working computer)