Business Purpose

There is a company that maintains a company library of tools that are needed for various building maintenance jobs employees perform. Employees of the company may use a tool from the library by checking it out to complete an assigned job and returning it when they are finished with it. There may be more than one of a particular tool in the library (e.g., there may be 6 identical hammers available for checkout).

Tools and employees can interact with the tool library in three ways.

Employees point of view: Employees can check out a tool, in which case it is on loan to that employee after it has been checked out to that individual. When a tool is on loan to an employee there is an active loan that encompasses this information. An employee can also create a request to hold a tool of a particular description that is currently not available in the library (because all are checked out). The request to hold a tool not in the library is a reservation. A tool is on hold if it is in the library waiting to be loaned to a particular employee, and this information is known because the employee made a reservation for a tool and when it came back into the library, it was put on hold, clearing the reservation. Finally, an employee can return a tool to the library, at which point the active loan becomes inactive.

Tools point of view: A tool can be in the library available for an employee to check out. A tool can be out of the library on loan to an employee. A tool can be in the library but not available for any employee to check out except one the employee who put a hold on it.

If a tool is returned to the library and there is an active reservation request for a tool of that description, then the tool is immediately put on hold using the information from the reservation. The hold expires after 48 hours have elapsed since the start of the hold. Once a tool is put on hold for an employee, it cannot be loaned out to a different employee until the hold is cancelled or expires. If the employee with the hold on a tool checks the tool out, the hold is no longer active and a loan for the tool for that employee becomes active.

Some Business Rules

A job has a job status of either in progress, pending, or complete. A job with a status of pending is a job that has been suggested but not authorized by a department or person with the authority to authorize it. Once authorized, a jobs status changes from pending to in progress. The job information system is not part of the tool library information system.

An employee can check out a tool or put a hold on a tool without associating the need for the tool with a particular job. If the employee has a job id and status, that information can be put into the tool library, but it is optional.

A tool reservation is removed from the reservation list when a tool of the type specified in the reservation is put on hold for the employee who made the reservation. Removed from the reservation list means that the reservation is marked inactive in the database.

A tool loan has a status of active or inactive. A tool loan becomes active when the employee leaves the tool library with the tool. The due date for the return of the tool is set based on the employees estimate of how long they will need the tool or the due date is set to 7 days from the checkout date, whichever is sooner.

A tool loan is active when the tool has been checked out and not yet returned. The loan becomes inactive when the employee returns the tool.

A tool hold is active when a tool is awaiting check out by a particular employee for a particular or unspecified job. The hold becomes inactive when (a) the employee comes in and checks out the tool or (b) 48 hours elapse after the start of the hold.

Each employee has a company-provided email address that is unique to him or her.

Only employees can check out tools from the tool library

5. Create complete data scenarios (fake but realistic employee information, job information,

CIS 4338 Exercise One-01 Understanding Business Processes 3

a. a tool loan when the tool is in the library

tool information, dates, etc.) for three sample tool library transactions that illustrate each of the following: b. a tool reservation when a tool is not in the library

c. a tool hold when the tool is held after it is returned to the library

BASE DATA

Tools Table

Tool_ID	Tool_type	Tool_Avail	Tool_Quantity
01	Ratchet	Y/N	5
02	Screwdriver	Y/N	5
03	Hammer	Y/N	5
04	Paintbrush	Y/N	5
05	Pliers	Y/N	5

Employee Table

Emp_Email	Emp_F_Name	Emp_LName	Emp_Address	Emp_Phone	Emp_Start_Date
edir@central	Edi	johnson	1234 Sesame Street	123-456-7890	1/19/2015
darac@central	toro	Kha	5678 Clifford Street	098-765-4321	5/17/2009
PatrickStar@ central\	Patrick	Star	4567 Malcom Street	376-893-6598	4/15/2010
JohnStewart@ central	John	Stewart	3689 Galleria Ave	420-762-2953	3/21/2013
MichaelJackson@ central	Michael	Jackson	8356 Washington Ave	834-562-0483	5/13/2018

Jobs Table

Job_Id	Dept_ID	Job_Des	Job_Status
01	01	Plumbing	Active/Inactive
02	02	Painting	Active/Inactive
03	03	Air Conditioning	Active/Inactive
04	04	Carpeting	Active/Inactive
05	05	Locksmith	Active/Inactive
06	06	Plumbing	Active/Inactive

Department Table

Dept_Id	Dept_Name	Dept_Desc
01	HR	Human Resources
02	Marketing	Marketing
03	Technology	Tech
04	Accounting	Acc
05	Purchasing	Purch

Employee/Tool Availability Table

Emp_Email	Tool_ID	Tool_Status
edirews@central.com	01	Active/Reserved/Hold
dhara@central.com	04	Active/Reserved/Hold
PatrickStar@ central.com	03	Active/Reserved/Hold
JohnStewart@ central.com	02	Active/Reserved/Hold
MichaelJackson@ central.com	01	Active/Reserved/Hold

Main Table

Actions Table

ID	Emp_ID	Job_ID	Tool_ID	Request_Type	Request_Stat	Req_Date
1	3	3	2	1	Active	1/19/2019
2	21	6	5	2	Inactive	1/29/2017
3	2	4	6	1	Active	03/21/2016
4	1	3	5	1	Active	08/08/2017
5	5	6	7	1	Active	12/30/2017

Requests_Info Tables

Request_Type table

Request_ID	Request_Code
1	Tool_Request
2	Tool_Loan

Reservation table

Reservation ID	Tool ID	Employee ID
01	01	01
05	03	03
07	02	04

These are the data scenarios that were created

Create a data flow diagram for the processes of the tool library. Use the Data Flow Model Diagram template available in MS Visio. The template annotated with Create dataflow diagrams using the Gane-Sarson (DFD) notation best matches the information given in the online content which is linked to next.