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 respect of Project Stage Employee steering, Inventory and purchase 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 development the Tampakan Copper-Gold Project in southern Philippine island of Mindanao, involving one of the worlds largest undeveloped copper-gold deposits. SMI is a contractor of the Philippine regime 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 employ ed more than 370 staff and more than 1,000 contractors. SMI has a strong track-record as a company that whole act upons 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 recourse Organization of the Philippines and recognition in 2010 as the Outstanding Stakeholder from the subdivision of Education in Region XII. Sagitarrius Mines Inc. is being managed by a general manager who oversees the trading operations Manager, Human Resource Manager, Finance and Commercial Manager, Risk Management and course of instruction Manager.Operations Manager Human Resource Manager Finance and Commercial Manager Risk Management And course of instruction Manager General Manager General Manager Duties include Increases steerings effectualness by recruiting, selecting, orienting, training, coaching , counseling, and disciplining managers communicating 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 do 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 physical compositions, and employees enforcing ethical business practices. Maintains quality service by establishing and enforcing organization standards. Maintains professional and tec hnical knowledge by attending educational 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 prep bes 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 dialogue 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 emphasizes 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 somewhat 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 prize threats, put plans in place for if things go wrong and decide how to avoid, reduce or transfer risks. Risk managers be 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, regulator y and operational risk, business continuity, information and pledge risk, technology risk, and market and credit risk. They prepare and administer the companys property and casualty insurance program in compliance with state and national laws and regulations. Develop and implement safety policies and loss prevention programs. Establish a climate that inspires achievement through direct upervision and motivation of part 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 employ 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 affect of Reports. EXISTING SYTEMThe current Employee Management, Inventory and Purchasing System are do manually. The Human Resource dep artment, 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 put down 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 record.Once there is a need to grease ones palms 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 recent 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 either employee of SMI. 3. Purchasing item delivery, purchasing order, maintain supplier list, item maintenanceThe Proposed System limits only with the delivery of the concomitant 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 face 2. 1. 1 Employee Requests item Entity Employee Characteristics EmployeeID, FName, LName, EmpAddress, WorkLocationID, PositionID, term, leniencyDate, groupNo, DeprtmentNo, StartDate, EndDate, ContactNo, affaircaseNo.Entity Purchase Order Details Characteristics PODNo, PPOCreationDate, POOrderDate, EmployeeID Entity Purchase feature Details Characteristics PIDNo, Quantity, PODNO, provider ID , COst, Remarks, Description, 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, Device attributeNo, 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, comprise, Status, ItemType, DateReceived, DeliveryDate, DeliveryStatus 3. 1 Items View 3. 1. 1 Employee Borrowed Item Entity EmployeeChara cteristics 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, woo, 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, DeprtmentNo, 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 Characteristic s 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. system of logical Design 1. Functional Dependencies Item display panel 3NF SerialNo -gt Device Type No. , Received Item No. , Cost, Status, Item Type Device Type put back 3NF DeviceTypeNo -gt Type, Model ,Brand Communication Services board 3NF AccountNo -gt Item_Serial No. , MIN, IMEI, PlanStartDate, PlanEndDate, Status, Remarks, PlanCost, Employee ID Inventory Table 2NFDisposeNo -gtDisposalDate, Status, Employee ID, SerialNo RetiredItemNo -gt RetiredDate, Status, Employee ID, SerialNo RendereditemNo -gtDateRendered, 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 bowl Name DisposeNo PK DisposalDate Status Employee ID FK SerialNo FK DisposeNo -gtDisposalDate, 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 -gtDateRendered, Employee ID, SerialNo DonationItem 3NF Field Name DonationItemNo PK DonationDate Status Employee ID FK SerialNo FK Donation It em 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 Details 3NF Field Name PODNo PK POCreationDate POOrderDate Employee ID FK PODNo -gt POCreationDate, POOrderDate, Employee ID Rece ivedItem 3NF Field Name ReceivedItemNo PK DateReceived Remarks PIDNo FK ReceivedItemNo -gt DateReceived, Remarks, PIDNo Delivery Item3NF 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 Name 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 Fnam e, 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 pristine primaeval Nullable TeamNo INT Yes No TeamName VARCHAR(50) No No DepartmentNo INT No No EmployeeID INT No No a. Indeces Index_01 on TeamNo for Primary detect Index_02 on TeamName b. Referential impartiality b. 1 Employee. TeamNo on efface trammel on UP get wind go down c. Program system of logic c. 1 2. Department Field Name Data Type Primary Key Nullable DepartmentNo INT Yes No D epartmentName 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 cut down on modify 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 NullablePositionNo 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 EndDate 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 Cascade 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 PurchaseItemDeta ils. 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 NoReceivedItemNo 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 DisposedIt em. 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_01 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 NoPODNo 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 De liveryStatus 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