Employee Management, Inventory and Purchasing System

Class and Section: IT 230 Group Name: GREGORIO_JOLCANAIN_FRASCO Group Members: Gregorio, Ralph Jolcanain, Sandra Frasco, Harry Nelson Date: August 19, 2012 Company/Office: Sagittarius Mines Inc. Title of Project Stage: Employee Management, Inventory and Purchasing System Sagittarius Mines Inc. REQUIREMENTS SPECIFICATION AND CONCEPTUAL DATA MODEL Employee Management, Inventory and Purchasing System Sagittarius Mines Inc. I. REQUIREMENTS SPECIFICATION The Organization Sagittarius Mines, Inc. SMI) is developing the Tampakan Copper-Gold Project in southern Philippine island of Mindanao, involving one of the world’s largest undeveloped copper-gold deposits. SMI is a contractor of the Philippine Government under the terms of a Financial and Technical Assistance Agreement (FTAA). Business Environment SMI currently operate from offices in Makati City in Manila, in General Santos and Digos cities and in the municipalities of Tampakan, Columbio, and Kiblawan. As at the end of 2010 we em ployed more than 370 staff and more than 1,000 contractors. SMI has a strong track-record as a company that works in a responsible manner.In fact, their commitment to corporate social responsibility in the Philippines has been recognized with numerous awards including the 2006, 2009 and 2010 Presidential Environmental Award, the 2010 Award of Distinction from the Safety Organization of the Philippines and recognition in 2010 as the Outstanding Stakeholder from the Department of Education in Region XII. Sagitarrius Mines Inc. is being managed by a general manager who oversees the Operations Manager, Human Resource Manager, Finance and Commercial Manager, Risk Management andProgram Manager.Operations Manager Human Resource Manager Finance and Commercial Manager Risk Management And Program Manager General Manager General Manager – Duties include Increases management's effectiveness by recruiting, selecting, orienting, training, coaching, counseling, and disciplining managers; co mmunicating values, strategies, and objectives; assigning accountabilities; planning, monitoring, and appraising job results; developing incentives; developing a climate for offering information and opinions; providing educational opportunities.Develops strategic plan by studying technological and financial opportunities; presenting assumptions; recommending objectives. Accomplishes subsidiary objectives by establishing plans, budgets, and results measurements; allocating resources; reviewing progress; making mid-course corrections. Coordinates efforts by establishing procurement, production, marketing, field, and technical services policies and practices; coordinating actions with corporate staff.Builds company image by collaborating with customers, government, community organizations, and employees; enforcing ethical business practices. Maintains quality service by establishing and enforcing organization standards. Maintains professional and technical knowledge by attending educat ional workshops; reviewing professional publications; establishing personal networks; benchmarking state-of-the-art practices; participating in professional societies. Operations Manager – An operations manager runs the day-to-day operations of an organization or business.Her sole purpose is to find ways to make the company more productive by providing effective methods in its business operations. An individual in this position usually prepares program budgets, facilitates several programs around the company, controls inventory, handles logistics and interviews and supervises employees. A person interested in operations management needs to have a strong leadership background and must know how to handle problems quickly and efficiently. In addition, she must have great communication skillsHuman Resource Manager- The Human Resources Manager originates and leads Human Resources practices and objectives that will provide an employee-oriented, high performance culture that emphasi zes empowerment, quality, productivity and standards, goal attainment, and the recruitment and ongoing development of a superior workforce. The Human Resources Manager coordinates implementation of services, policies, and programs through Human Resources staff; reports to the General Manager and serves on the executive management team; and assists and advises company managers about Human Resources issues.Risk Management And Program Manager -Risk and Program Manager advises organization on any potential risks to the profitability or existence of the company. They identify and assess threats, put plans in place for if things go wrong and decide how to avoid, reduce or transfer risks. Risk managers are responsible for managing the risk to the organization, its employees, customers, reputation, assets and interests of stakeholders.They may work in a variety of sectors and may specialize in a number of areas including enterprise risk, corporate governance, regulatory and operational risk , business continuity, information and security risk, technology risk, and market and credit risk. They prepare and administer the company’s property and casualty insurance program in compliance with state and federal laws and regulations. Develop and implement safety policies and loss prevention programs. Establish a climate that inspires achievement through direct upervision and motivation of department managers. Lead efforts in goal-setting and long-range planning, and provide leadership and daily direction. Critical Factors for the company needs to succeed are as follows: 1. Constant Availability of gadgets that are being used in the operations and communication 2. Availability of documents needed to monitor the operations. 3. Proper Execution of the Job orders from a department to another department. 4. Efficient and Fast Processing of Reports. EXISTING SYTEMThe current Employee Management, Inventory and Purchasing System are done manually. The Human Resource department, the inventory and the purchasing system are all done using Microsoft Excel for keeping and tracking records. After the hiring process, the profiles of the employees from the resumes of them are encoded and stored at Microsoft Excel and updates it also there, Once the files are needed by the IT department who are in-charged of the releasing and collecting of Gadgets to be used by the company employees, they will just send it through e-mail.As the process for the inventory of the gadgets goes, it is tracked by logging who borrowed and who will be liable for the gadget In Microsoft Excel. The employee who needs the gadget needs to email the IT department for the need of the item. Upon the request, it will be checked if there are available item and further notice is sent to the requestor with regards to the item borrowing. The item is also monitored if it is in good condition and need to be replaced by a new one. All transactions must be logged in Microsoft Excel so it will be document ed.Once there is a need to purchase a new item, the purchasing department must be notified. The purchasing process is also monitored using manual inputting data in Microsoft Excel. As the IT department notified the purchasing department that the need of item is situated, they will assess the ordering of the item from a supplier. And once the item is delivered by the supplier, It will be documented in the Excel that the item is now purchased. Statement of the Project Scope This project will cover the Employee Profiling System, Inventory Tracking System and Purchasing System of Sagittarius Mines Inc. . Employee Profiling : The Proposed system limits only in the maintenance of employee profile and creating fast and efficient employee profile report to all present and past employees whether regular, probationary, contractual, or contractors. 2. Inventory Tracking: The Proposed System focuses on tracking the deployment of items(gadgets), the returning, the condition of item and creating fast and efficient inventory report of all items borrowed and returned by every employee of SMI. 3. Purchasing : item delivery, purchasing order, maintain supplier list, item maintenanceThe Proposed System limits only with the delivery of the Item from the purchasig departemt to the IT department, the purcharing order, maintenance of the suppliers and items. The areas covered by the project are department of IT department, HR department and Purchasing department concerning to profiling, item inventory and purchasing. II. CONCEPTUAL DESIGN 2. 1 Purchase Order View 2. 1. 1 Employee Requests Item Entity: Employee Characteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo.Entity: Purchase Order Details Characteristics: PODNo, PPOCreationDate, POOrderDate, EmployeeID Entity: Purchase Item Details Characteristics: PIDNo, Quantity, PODNO, Supplier ID, COst, Remarks, Descrip tion, POItemNo 2. 1. 2 Supplier Delivers Item Entity: Supplier Characteristics: SupplierID, CompanyName, Address, ContactNo, EmailAddress Entity: Purchase Item Details Characteristics: PIDNo, Quantity, PODNo, Supplier ID, COst, Remarks, Description, POItemNo Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus . 2 Deliver Items View 2. 2. 1 Employee Delivers Item Entity: Employee Characteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo. Entity: Purchase Item Details Characteristics: PIDNo, Quantity, PODNO, Supplier ID, COst, Remarks, Description, POItemNo, Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1 Items View 3. 1. 1 Employee Borrowed Item Entity: EmployeeCharacteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo. Entity: Render Item Characteristics: RenderedItemNo, DateRendered, SerialNo, Employee ID Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1. 2 Employee Returns Item Entity: Employee Characteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo.Entity: Return Item Characteristics: ReturnItemNo, ReturnDate, Remarks, RenderedItemNo, SerialNo Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1. 3 Employee Lost Item Entity: Employee Characteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, D eprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo. Entity: Lost Item Characteristics: LostItemNo, DateLost, Remarks, EmployeeID, SerialNo Entity: ItemCharacteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1. 4 Employee Donate Item Entity: Employee Characteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo. Entity: Donate Item Characteristics: DonationItemNo, DonationDate, Status, EmployeeID, SerialNo Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1. 5 Employee Retire Item Entity: EmployeeCharacteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo. Entity: Retire Item Characteris tics: RetiredItemNo, RetiredDate, Status, EmployeeID, SerialNo Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1. 6 Employee Request Repair Item Entity: Employee Characteristics: EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, Status, ResignationDate, TeamNo, DeprtmentNo, StartDate, EndDate, ContactNo, EmploymentTypeNo.Entity: Repair Request Characteristics: RepairRequestNo, RequestDate, RepairStatus, DoneDate, , Employee ID Entity: Item Characteristics: Serial No, DeviceTypeNo, ReceivedItemNo, Cost, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus III. Logical Design 1. Functional Dependencies Item Table: 3NF SerialNo -;gt; Device Type No. , Received Item No. , Cost, Status, Item Type Device Type Table: 3NF DeviceTypeNo -;gt; Type, Model ,Brand Communication Services Table: 3NF AccountNo -;gt; Item_Serial No. , MIN, IMEI, PlanStartDate, PlanEndDate, Status, Rem arks, PlanCost, Employee ID Inventory Table : 2NFDisposeNo -;gt;DisposalDate, Status, Employee ID, SerialNo RetiredItemNo -;gt; RetiredDate, Status, Employee ID, SerialNo RendereditemNo -;gt;DateRendered, Employee ID, Serial No. Donation Item No -;gt; DonationDate, Status, Employee ID, SerialNo ReturnItemNo -;gt; ReturnDate, Remark, RendereditemNo, SerialNo LostItemNo -;gt; DateLost, Remarks, Employee ID, SerialNo 3NF Decomposition of Inventory Dispose Item: 3NF Field Name| | DisposeNo| PK| DisposalDate| | Status| | Employee ID| FK| SerialNo| FK| DisposeNo -;gt;DisposalDate, Status, Employee ID, SerialNo Return Item: 3NF Field Name| | RetiredItemNo| PK| ReturnDate| |Status| | Employee ID| FK| SerialNo| FK| RetiredItemNo -;gt; RetiredDate, Status, Employee ID, SerialNo RenderedItem: 3NF Field Name| | RenderedItemNo| PK| DateRendered| | Status| | Employee ID| FK| SerialNo| FK| RendereditemNo -;gt;DateRendered, Employee ID, SerialNo DonationItem: 3NF Field Name| | DonationItemNo| PK| D onationDate| | Status| | Employee ID| FK| SerialNo| FK| Donation Item No -;gt; DonationDate, Status, Employee ID, SerialNo ReturnItem: 3NF Field Name| | ReturnItemNo| PK| ReturnDate| | Status| | RendereditemNo| FK| SerialNo| FK| ReturnItemNo -;gt; ReturnDate, Remark, RendereditemNo, SerialNoLostItem: 3NF Field Name| | LostItemNo| PK| DateLost| | Remarks| | Employee ID| FK| SerialNo| FK| LostItemNo -;gt; DateLost, Remarks, Employee ID, SerialNo PurchaseTable: 2NF PIDNo -;gt; Quantity, PODNo, Supplier ID, Cost, Remarks, Description, POItemNo PODNo -;gt; POCreationDate, POOrderDate, Employee_Employee ID ReceivedItemNo -;gt; DateReceived, Remarks, PIDNo DeliveryNo-;gt; DeliveryStatus, DeliveryDate, ReceivedItemNo, Datereceived 3NF Decomposition of Purchase Purchase Item Details: 3NF Field Name| | PIDNo| PK| Quantity| | PODNo| FK| Supplier ID| FK| Cost| | Remarks| | Description| |POItemNo| | PIDNo -;gt; Quantity, PODNo, Supplier ID, Cost, Remarks, Description, POItemNo Purchase Order Det ails: 3NF Field Name| | PODNo| PK| POCreationDate| | POOrderDate| | Employee ID| FK| PODNo -;gt; POCreationDate, POOrderDate, Employee ID ReceivedItem: 3NF Field Name| | ReceivedItemNo| PK| DateReceived| | Remarks| | PIDNo| FK| ReceivedItemNo -;gt; DateReceived, Remarks, PIDNo Delivery Item:3NF Field Name| | DeliveryNo| PK| DeliveryStatus| | DeliveryDate| | ReceivedItemNo| | Datereceived| | DeliveryNo-;gt; DeliveryStatus, DeliveryDate, ReceivedItemNo, Datereceived Supplier Table: 3NFSupplier ID -;gt; CompanyName, Address, ContactNo, EmailAddress Employee Table: 2NF Employee ID -;gt; Fname, Lname, Mname, EmpAddress, Work Location, Position ID, Status, Resignation Date, TeamNo, DepartmentNo, Startdate, Enddate, EmploymentTypeNo TeamNo -;gt; TeamName, DepartmentNo, EmployeeID DepartmentNo –;gt; DepartmentName PositionNo -;gt; PositionTitle, Level WorkLocationNo -;gt; WorkLocationName EmploymentTypeNo -;gt; EmploymentType 3NF Decomposition of Employee Employeesummary: 3NF Field N ame| | Employee ID| PK| Fname| | Lname| | Mname| | EmpAddress| | Work Location| FK| Position ID| FK| Status| |ResignationDate| | TeamNo| FK| DepartmentNo| FK| Startdate| | Enddate| | EmploymentTypeNo| FK| Employee ID -;gt; Fname, Lname, Mname, EmpAddress, Work Location, Position ID, Status, Resignation Date, TeamNo, DepartmentNo, Startdate, Enddate, EmploymentTypeNo Team: 3NF Field Name| | TeamNo| PK| TeamName| | DepartmentNo| FK| EmployeeID| FK| TeamNo -;gt; TeamName, DepartmentNo, EmployeeID Department: 3NF Field Name| | DepartmentNo| PK| DepartmentName| | DepartmentNo –;gt; DepartmentName Position: 3NF Field Name| | PositionNo| PK| PositionTitle| | Level| | PositionNo -;gt; PositionTitle, Level EmploymentType: 3NFField Name| | EmploymentTypeNo| PK| EmploymentType| | EmploymentTypeNo -;gt; EmploymentType IV. Physical Design 1. Team Field Name| Data Type| Primary Key| Nullable| TeamNo| INT| Yes| No| TeamName| VARCHAR(50)| No| No| DepartmentNo| INT| No| No| EmployeeID| INT| N o| No| a. Indeces: Index_01 on TeamNo for Primary Key Index_02 on TeamName b. Referential Integrity : b. 1 Employee. TeamNo on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 2. Department Field Name| Data Type| Primary Key| Nullable| DepartmentNo| INT| Yes| No| DepartmentName| VARCHAR(50)| No| No| a. Indeces: Index_01 on DepartmentNo for Primary KeyIndex_02 on DepartmentName b. Referential Integrity : b. 1 Employee. DepartmentNo on DELETE Restrict on UPDATE Cascade b. 2 Team. DepartmentNO on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 Inserting a New Item: 3. Work Location Field Name| Data Type| Primary Key| Nullable| WorkLocationNo| INT| Yes| No| WorkLocationName| VARCHAR(50)| No| No| a. Indeces: Index_01 on WorkLocationNo for Primary Key Index_02 on WorkLocationName b. Referential Integrity : b. 1 Employee. WorkLocationNo on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 4. Position Field Name| Data Type| Primary Key| Nullable|PositionNo| INT| Yes| No| PositionTitle| VARCHAR(50)| No| No| Level| VARCHAR(50)| No| No| a. Indeces: Index_01 on PositionNo for Primary Key Index_02 on PositionTitle Index_03 on Level b. Referential Integrity : b. 1 Employee. PositionNo on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 5. Employment Type Field Name| Data Type| Primary Key| Nullable| EmploymentTypeNo| INT| Yes| No| EmploymentType| VARCHAR(50)| No| No| a. Indeces: Index_01 on Employment TypeNo for Primary Key Index_02 on EmploymentType b. Referential Integrity : b. 1 Employee. EmploymentType on DELETE Restrict on UPDATE Cascade c. Program Logic: . 1 6. Employee Field Name| Data Type| Primary Key| Nullable| EmploymentID| INT| Yes| No| Fname| VARCHAR(50)| No| No| Lname| VARCHAR(50)| No| No| Mname| VARCHAR(50)| No| No| EmpAddress| INT| No| No| WorkLocationNo| INT| No| No| PositionID| INT| No| No| Status| VARCHAR(20)| No| NO| ResignationDate| DATE| No| No| TeamNo| INT| No| No| DepartmentNo| INT| No| No| StartDate| DATE| No| No| End Date| DATE| No| No| ContactNo| INT| No| No| EmplomentTypeNo| INT| No| No| a. Indeces: Index_01 on EmploymentID for Primary Key Index_02 on Fname Index_03 on Lname Index_04 on Mname Index_05 on EmpAddress Index_06 on WorkLocationNoIndex_07 on PositionID Index_08 on Status Index_09 on ResignationDate Index_10 on TeamNo Index_11 on DepartmentNo Index_12 on StartDate Index_09 on EndDate Index_10 on ContactNo Index_11 on EmploymentTypeNo b. Referential Integrity : b. 1 Team. EmployeeID on DELETE Restrict on UPDATE Cascade b. 2 PurchaseOrderDetail. EmployeeID on DELETE Restrict on UPDATE Cascade b. 3 CommunicationServices. EmployeeID on DELETE Restrict on UPDATE Cascade b. 4 RenderedItem. EmployeeID on DELETE Restrict on UPDATE Cascade b. 5 DisposedItem. EmployeeID on DELETE Restrict on UPDATE Cascade b. 6 RetiredItem. EmployeeID n DELETE Restrict on UPDATE Cascade b. 7 DonationItem. EmployeeID on DELETE Restrict on UPDATE Cascade b. 8 LostItem. EmployeeID on DELETE Restrict on UPDATE Cas cade b. 9 RepairRequest. EmployeeID on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 7. Purchase Order Details Field Name| Data Type| Primary Key| Nullable| PODNO| INT| Yes| No| POCreationDate| DATE| No| No| POOrderDate| DATE| No| No| EmployeeID| INT| No| No| a. Indeces: Index_01 on PODNo for Primary Key Index_02 on POCreationDate Index_03 on POOrderDate b. Referential Integrity : b. 1 PurchaseItemDetails. PODNo on DELETE Restrict on UPDATE Cascade . Program Logic: c. 1 8. Supplier Field Name| Data Type| Primary Key| Nullable| SupplierID| VARCHAR(20)| Yes| No| CompanyName| VARCHAR(50)| No| No| Address| VARCHAR(80)| No| No| ContactNo| INT| No| No| EmailAddres| VARCHAR(30)| No| No| a. Indeces: Index_01 on SupplierID for Primary Key Index_02 on CompanyName Index_03 on Address Index_04 on ContactNo Index_05 on EmailAddress b. Referential Integrity : b. 1 PurchaseItemDetails. SupplierID on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 9. Item Field Name| Data Type| Primary Key| Nullable| SerialNo| VARCHAR(45)| Yes| No| DeviceTypeNo| INT| No| No|ReceivedItemNo| INT| No| No| Cost| DOUBLE| No| No| Status| VARCHAR(45)| No| No| ItemType| VARCHAR(45)| No| No| PIDNo| INT| No| No| a. Indeces: Index_01 on SerialNo for Primary Key Index_02 on Cost Index_03 on Status b. Referential Integrity : b. 1 CommunicationServices. SerialNo on DELETE Restrict on UPDATE Cascade b. 2 RenderedItem. SerialNo on DELETE Restrict on UPDATE Cascade b. 3 DisposedItem. SerialNo on DELETE Restrict on UPDATE Cascade b. 4 RetiredItem. SerialNo on DELETE Restrict on UPDATE Cascade b. 5 DonationItem. SerialNo on DELETE Restrict on UPDATE Cascade b. 6 RepairRequest. SerialNo on DELETE Restrict n UPDATE Cascade b. 7 LostItem. SerialNo on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 10. DeviceType Field Name| Data Type| Primary Key| Nullable| DeviceTypeNo| INT| Yes| No| Type| VARCHAR(20)| No| No| Model| VARCHAR(20)| No| No| Brand| VARCHAR(20)| No| No| a. Indeces: Index_0 1 on DeviceTypeNO for Primary Key Index_02 on Type Index_03 on Model Index_04 on Brand b. Referential Integrity : b. 1 Item. DeviceTypeNo on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 11. Purchase Item Detail Field Name| Data Type| Primary Key| Nullable| PIDNo| INT| Yes| No| Quantity| VARCHAR(45)| No| No|PODNo| INT| No| No| SupplierID| VARCHAR(20)| No| No| Cost| DOUBLE| No| No| Remarks| VARCHAR(100)| No| Yes| Description| VARCHAR(100)| No| No| POItemNo. | INT| No| No| DateReceived| DATE| No| No| DeliveryDate| DATE| No| Yes| DeliveryStatus| VARCHAR(45)| No| No| a. Indeces: Index_01 on PIDNO for Primary Key Index_02 on Quantity Index_03 on Cost Index_04 on Remarks Index_05 on Description Index_06 on POItemNo Index_07 on DateReceived Index_08 on DeliveryDate Index_09 on DeliveryStatus b. Referential Integrity : b. 1 Item. PIDNO on DELETE Restrict on UPDATE Cascade c. Program Logic: c. 1 V. Final Class Diagram