Design and Deployment of Vehicle Information System for Data Mining and Management

Design and Deployment of Vehicle Information System for Data Mining and Management

Akande Stephen Oluwashola1 Department of Mechanical Engineering Kogi State Polytechnic,

Lokoja, Nigeria

Oluyinka Ige2

Department of Computer Engineering Kogi State Polytechnic,

Lokoja, Nigeria

Abstract A vehicle information system (VIS) has been developed for vehicle maintenance and usage management. The various interfaces were developed using Visual Basic 6.0 while Microsoft Office Access 2010 was used for creating the database. Active X Data Object (ADO) was used to establish and maintain an open connection to the created database. Structure Query Language (SQL) was used in querying the database and exporting data to Microsoft Excel for further data analysis and report printing. VIS was tested and validated due to its use in vehicle maintenance planning and usage management in an organization. VIS was found to be very useful as the quality and effectiveness of fleet management have improved with the attendance reduction in vehicle downtime.

KeywordsFleet manager; vehicle; Life-cycle; maintenance; management

1. INTRODUCTION

   All information systems begin with data. Indeed, they were called "data processing systems". However, understanding data is the first step toward building an information system, but it is far from the only one. There is also a need to know what processes it can invoke to achieve its ends [1].

   It is a known fact that the basic approach to vehicle maintenance includes the following:

   • Daily routine maintenance

   • Preventive or periodic maintenance (Servicing or troubleshooting)

   • And Overhaul (repair or replacement of components that have broken down or failed).

   All the above maintenance operations/processes involve either repairs or replacement of parts of vehicles. If the records of fuel consumption per vehicle, kilometers covered per vehicle, and detailed records of various maintenance activities that were carried out in the workshop are being kept, it will get to a point where accessing or retrieving records will not only be difficult but sometimes impossible thus leading to delay in decision making and consequently poor fleet maintenance management. Hence, the importance or need to have an effective and efficient electronic or computerized vehicle information management system cannot be over quantified.

   Therefore, a need for engineers, workshop managers, and personnel that are responsible for vehicles maintenance management to have an efficient and effective data gathering and processing system to assist in decision-

   making and proper fleet management exist. This project will develop vehicle information system software that will enable engineers and other interested stakeholders to have an efficient and effective vehicle maintenance management system.

   The developed vehicle information system (VIS) will be for managing maintenance details, keeping track of vehicle documents and driver details among others.

   Deployment of VIS will certainly lead to better performers of the Mechanical Unit of the works department of the polytechnic. The VIS will also be useful to any organization with a fleet of vehicles.

   The project will also provide Information Computer Technology (ICT) skills to the personnel who will manage the system and transfer technology.

   Problem Statement/Justification

   The manual records keeping will be effective for relatively small maintenance units with few vehicles but will not suit the growing needs for vehicle maintenance services these days.

   Information technology has revolutionized the way things are being done worldwide. ICT has a positive influence on human life across the education sector, industry, transportation, economy, and engineering as a whole. ICT has enhanced the way vehicles or fleet is being maintained and managed by various organizations, thereby improving the effectiveness and efficiency of the organization's fleet management. Despite the benefit of ICT applications in the maintenance domain, developing economies around the world are yet to fully adopt ICT-based maintenance management for improved service delivery. Thus this research will develop and deploy a vehicle information system that will enhance vehicle maintenance and fleet management.

   1. Manual Record and Electronic Records Systems

      The manual records will be effective for relatively small maintenance units with few vehicles but will not suit the growing needs for vehicle maintenance services these days.

      The manual records require a significant amount of space to store them and will require an increasing amount of space to store them in the future. Moreover, information stored at

      different locations will be difficult to gather together in case a review is needed. Data search will also be time-consuming. Poor legibility is also associated with handwritten paper that is kept for a long duration. Locating maintenance records that are kept manually will also be cumbersome and inefficient. This might contribute to the poor maintenance culture of the organization. Electronic record-keeping is recommended for widespread adoption due to order entry which is found to reduce errors associated with handwritten documents.

   2. Characteristics of Electronics Record System

      The electronic vehicle records system is a positive step forward in other to provide adequate access to maintenance information.

      The required characteristics of electronic records should include, the system should allow vehicle maintenance information to be updated anytime and when due. The survey carried out by Khan [2] pointed out that the users of software base information systems agreed that electronic records aid in quick decision making and reduce errors considerably.

   3. Vehicle Maintenance Activities

   Maintenance data such as parts replaced, date of service and overhaul, cost of maintenance, the technician that works on a particular vehicle among others have been stored manually and sometimes electronically as an individual file for several years. Reuse of store data has not been efficient because of the difficulties in locating both the manually kept data and limited scattered electronic files on a computer system. The need to devise a system for data mining is very important; this will lead to improvement in the quality of maintenance services.

2. LITERATURE REVIEW

   Maintenance in any organization is the activities that involve equipment repair or keeping equipment in good working condition. Various tools can be used by engineers in maintenance management [3]. An example of such a tool is information and communication technology (ICT). Ghavifekr and Rosdy [4] view ICT as a shorthand for computers, software, networks, satellite links, and related systems that allow people to access and share information and knowledge in a variety of forms. Data collected from such activities must be stored in a well-defined format for ease of accessibility in making an informed decision; this gives rise to a need for a database. A central concept of a database is that of a collection of records, or pieces of knowledge systematically stored in a computer so that a computer program can consult it to answer questions for better retrieval and sorting, each record is usually organized as a set of data elements. The items retrieved in answer to queries become information that can be used to make decisions [5].

   Chen and Wei [6] created a vehicle management system based on radio frequency identification (RFD) using visual basic 6.0 and SQL server but they did not consider vehicle maintenance unlike what this research intends to achieve.

   Oladimeji et al. [7] designed a GSM-based vehicle security information system to verify the authenticity of ownership of a

   vehicle. Their work also did not put into consideration vehicle maintenance history and vehicle locations.

   However, a database is central to any good information system. Typically, for a given database, there is a structural description of the type of facts held in that database: this description is known as a schema. The schema describes the objects that are represented in the database and the relationships among them [8]. There are several different ways of organizing a schema, that is, of modelling the database structure: these are known as database models (or data models). The model in most common use today is the relational model, which in layman's terms represents all information in the form of multiple related tables each consisting of rows and columns. This model represents relationships by the use of values common to more than one table.

   Other models such as the hierarchical model, object- oriented model, and the network model use a more explicit representation of relationships [9-12].

   The advantage of a relational database is that the user does not have to be aware of any structure. Thus, they can be used with little training. Moreover, entries can be easily be added, deleted, or modified [13, 14]. However, the flexibility of relational databases allows the programmer to write queries that were not anticipated by the database designers [15]. As a result, relational databases can be used by multiple applications in ways the original designers did not foresee, which is especially important for databases that might be used for decades [16]. This has made the idea and implementation of relational databases very popular with businesses and it will therefore be adopted in the development of a database for the proposed vehicle information system.

3. METHODOLOGY

   The Vehicle information system interface was developed using Visual Basic 6.0 Software and Microsoft Office Access 2010. Microsoft Office Access 2010 was used for creating the database and visual basic software was used for the graphic user interface (GUI). ActiveX Data Objects (ADO) was used to establish an open connection to the created database. Furthermore, SQL (commonly expanded to structure Query Language) was used to create, modify, retrieve and manipulate data from the relational database.

4. RESULTS AND DISCUSSION

   The Vehicle information system interface was developed using Visual Basic 6.0 Software and Microsoft Office Access 2010. Microsoft Office Access 2010 was used for creating the database and visual basic software was used for the graphic user interface (GUI). ActiveX Data Objects (ADO) was used to establish an open connection to the created database. Furthermore, SQL (commonly expanded to structure Query Language) was used to create, modify, retrieve and manipulate data from the relational database.

   1. Input screen of the developed software

      Fig. 1 shows the input interface for adding the vehicle details such as car brand and work that was done on a particular vehicle into the VIS software.

      Fig. 1 Input Interface and Data for Maintenance Details

      Fig. 2 shows the input screen for monitoring the vehicle location and checking the vehicle document renewal status.

      Fig. 2 Input Screen and Data for Vehicles Location and Document Renewal Status

      Fig. 3 shows the input interface for adding the details of the fuel usage and kilometer covered by a particular vehicle.

      Fig. 3 Input interface and Data for Fuel Records

   2. Output screen for the developed software

   Fig. 4 shows the output screen for checking the maintenance status of a particular vehicle.

   Fig. 4 Output Screen and Data for Checking of Maintenance Status of Vehicles

   Fig. 5 shows the output records for fuel consumption by vehicles.

   Fig. 5 Output Screen for Fuel Records

   Fig. 6 shows the interface for checking for the maintenance details of a particular vehicle.

   Fig. 6 Output Screen for Checking of Selected Maintenance Details

   Fig. 7 shows fuels consumption records for all vehicles during the reporting period.

   Fig. 7 Print Output for Fuel Records

   Fig. 8 shows the report for vehicle renewal status with the date.

   Fig. 8 Print Output for Vehicles Documents Renewal Dates Status

   Fig. 9 shows the report for all vehicles brand and maintenance details.

   Fig. 9 Print Output for all Maintenance Details and Net Cost of Maintenance

5. VEHICLE INFORMATION SYSTEM

   From Fig. 1 user can enter the details of the vehicle such as vehicle brand, number plate, Year of acquisition, date of vehicle service, detail of service, date of overhaul, the month of the report, the amount spent, kilometer covered, a date for service and overhaul, the technician that did the job, Engineer/supervisor that supervised the job, Engine chassis number, year of the report. All the aforementioned data will be stored in a database in this interface for future reference. When the user clicks on the calculate button the total amount that was spent in maintaining the vehicle for that year of the report will be calculated and displayed on the interface. Through the interface, the user can keep track of the cost of maintaining the vehicle yearly thus enabling informed decisions to be taken by the engineer if there is a need to keep on using the vehicle or to scrap the vehicle. The user can also delete or modify the content and print the report from the same interface.

   From Fig. 2 the user can enter the vehicle registration date by clicking of set value button, specifying the number plate of the vehicle, and then clicking on the find vehicle button on the interface to find the vehicle document registration status. The user can also find the location of the vehicle; the fleet manager can also allocate the vehicle to another department or unit based on the logistic needs of the transport department. The report about a particular vehicle can be generated by clicking on display records.

   From Fig. 3 the user can enter the odometer reading, driver in charge of the vehicle, quantity of fuel used, specify if the fuel is from the company inventory or purchased from a gas station, calculate kilometer covered, export data to MS excel or edit the data and produced a report.

   From Fig. 1 the user can find out if the vehicle is due for service by clicking on the vehicle status and location button on the top ribbon. When this is done the output screen in Fig. 4 will be displayed showing if a vehicle is due for servicing or overhaul. The date for servicing or vehicle overhaul can be set in the input screen in Fig. 1. It can also be set at the output screen based on the distance covered by the vehicle as shown in Fig. 4. The result will enable the engineer to carry out the required preventive maintenance at the right time, thereby improving the service life of the vehicle.

   The clicking of the run report in Fig. 3 will display the fuel consumption pattern and cost for each vehicle in the fleet as shown in Fig. 5. The driver that purchases the fuel and the gas station where the fuel was purchased will also be shown. This information can be used to trace the source of fuel in case of adulteration by the seller.

   From Fig. 6 the user can check for maintenance cost per year or check for all maintenance costs for a particular vehicle irrespective of the year. The report can also be printed for future reference. By monitoring maintenance cost per year the fleet manager or engineer can take an informed decision concerning the desirability of keeping the vehicle in the fleet or selling the vehicle as scrap to avoid avoidable expenses.

   If the user clicks on the Record button in the interface in Fig. 1 and then selects the printable fuel consumption pattrn the output interface in Fig. 7 will be displayed that will allow the user to print information of fuel usage for each vehicle in the fleet. This interface also allows the fleet manager to know the total fuel usage and cost for the reporting period.

   To find out vehicle document renewal status for all the vehicles in the fleet, the user will need to click on the Record button on the interface in Fig. 1 and choose the option of Vehicle document renewal status; this will display the printable record as shown in Fig. 8. The provided information will help the fleet manager keep track of the current registration status of all vehicles in the fleet thereby, avoiding conflict with licensing officer or payment of defaulting fee to the regulatory agency.

   Moreover, to generate a printable report (see Fig. 9) about all vehicle overhaul maintenance, the user will need to click on the Record button in Fig. 1 and select the option of vehicle Information Report. The generated report will enable the fleet manager to monitor all the detailed maintenance activities such as parts replaced, date of maintenance, and cost. This information is very important for budgeting and ensuring transparency in vehicle parts replacement throughout the life- cycle of the vehicle.

6. CONCLUSION

Vehicle Information System (VIS) has been developed and deployed for vehicle maintenance and usage management in Kogi State Polytechnic, Lokoja, Nigeria. The VIS is specialized software for effective management of all the aspects of the Vehicle maintenance process such as parts or components replaced, the amount spent in maintaining each vehicle, type of maintenance carried out, the technician that carried out maintenance work.

VIS also keeps records of the fuel consumption pattern and the cost of maintenance throughout the service life of the vehicles. Keeping track of the maintenance cost will enable the fleet manager or engineer to take a proactive decision about the continued use of vehicles in an organization or selling off as scrap due to the rising cost of maintenance. The information that will be available through the developed vehicle information system will be needed in maintenance planning and budgeting since the scheduling for maintenances and cost is also part of the developed system.

ACKNOWLEDGMENT

I am grateful to TETFUND, Nigeria for sponsoring this research.

REFERENCES

[1.] Gerald M. Welnberg and D. Pigeller, Computer information system: an introduction to data processing. 1985, New York: McGraw Hill Companies, Inc. .

[2.] Khan, N.N. Hospital information systems: An aid to decision making. in 2010 3rd International Conference on Emerging Trends in Engineering and Technology. 2010. IEEE.

[3.] Milje, R., Engineering methodology for selecting condition based maintenance. 2011, University of Stavanger, Norway.

[4.] Ghavifekr, S. and W.A.W. Rosdy, Teaching and learning with technology: Effectiveness of ICT integration in schools. International Journal of Research in Education and Science, 2015. 1(2): p. 175-191.

[5.] Clifford Sarah H., Sawyer Stacey C., and Coulthard Glen J., Computers, communications, and information : a user's introduction. 1998, Boston: Irwin/McGraw-Hill.

[6.] Chen, X.-M. and Z.-H. Wei. Vehicle management system based on multi-node RFID cards. in Proceedings of the 30th Chinese control conference. 2011. IEEE.

[7.] Oladimeji Adegbola Isaac , Amana H. Yusuf , and Oyesolu M. O, GSM Based Vehicle Information Management System. INTERNATIONAL JOURNAL OF ENGINEERING RESEARCH & TECHNOLOGY (IJERT), 2018. 07(02).

[8.] Loomis, M.E.S., Data management and file processing. 1983, Englewood Cliffs, N.J.; Toronto: Prentice-Hall.

[9.] Abbott, R.J., An integrated approach to software development. 1986: John Wiley & Sons, Inc.

[10.] Silberschatz, A., H.F. Korth, and S. Sudarshan, Database system concepts. Vol. 4. 1997: McGraw-Hill New York.

[11.] Case, A.F., Computer-aided software engineering (CASE) technology for improving software development productivity. ACM SIGMIS Database: the DATABASE for Advances in Information Systems, 1985. 17(1): p. 35-43.

[12.] Williams, B.K., S.C. Sawyer, and S.E. Hutchinson, Using information technology: A practical introduction to computers & communications. 1999: ERIC.

[13.] Patrick, T., Start-to-finish Visual Basic 2005: learn Visual Basic 2005 as you design and develop a complete application. 2002: Pearson Education.

[14.] Deitel Harvey M., Deitel Paul J., and Nieto T. R., Visual Basic 6 : how to program H.M. Deitel, P.J. Deitel, T.R. Nieto. 1999, Upper Saddle River, N.J.: Prentice-Hall.

[15.] Vaughn, W.R., Hitchhiker's guide to Visual Basic and SQL Server.

1997: Microsoft Press.

[16.] Codd, E.F., The relational model for database management: version

2. 1990: Addison-Wesley Longman Publishing Co., Inc.