Acme Garage

Question: Analyze the current structure of the system used by Acme Garage to maintain the stock, purchase, vendor and transaction details and provide the updated structure with new proposed solution. Answer: Introduction The purpose of this report is to analyze the current structure of the system used by Acme Garage to maintain the stock, purchase, vendor and transaction details and provide the updated structure with new proposed solution. This new solution can help Acme Garage to implement a single point of contact for all kind of transaction instead of using manual books for maintain job details and access database for customer and vendor details. Acme Garage is using Job books to maintain the details for certain job with all parts details that are used in the job with the customer linked to this. Maintaining the historical data of these kind of job books is not easy. Maintaining the excel for stock is not a good idea with orders in a filing cabinet. We have provided the report with the details which are presented below: Business Overview Acme Garage is a car repair company that have 2 major section administrative and mechanics section. Kim is working in office and maintain all order, jobs, customer and stock data in different form i.e. database, excel and manual copies. Bill and ben works as mechanics in the garage and record the job data on paper with the parts used in job and labor charges. Key issues of the current system are as below: Order data and stock data maintained separately in form of excel and paper form so stock mismatch can be happen. Job book always check the part from stock excel so if in a case part is not used it should be back to logistic area and stock should be increased. Customer data maintained in database and before every job it should checked the payment received or not for past services. No integration between job and order Information of stock and information of data would separately maintained in the form of paper and form of an excel so that the mismatch of the stock that would get occurred. The part of the book of the job would check from the excel of the stock so if the part of the case was not used then it would get back to the area of the logistic and it would get raised the stock. Information of the customer should maintained in the database and the job would before each that would checked the received the payment or not from the previous services. Opportunities: The current system is used by Kim who have understanding of computer as she used excel to maintain stock and customer and vendor access database to create, retrieve and update ,delete the data. It is easier to create the application that can use the existing customer and supplier data in backend and create new tables to store rest of data. In this we have provided the present system that will get used by Kim who will have complete understanding the PC as they will get used the excel sheet for maintaining the client and stock and access the vendor the database for accessing for deleting, updating, retrieving and creating. Analysis After analyze the current system it observed that stock management process should be automate and customer and supplier database should be in a single database. Order data also be integrate with this system. Based on the new proposed system below are the ER Diagram, class diagram and DFD Class diagram Below is the class diagram which was created with the help of tables that were used in this project: DFD Below is the updated DFD which was created with the new details of the class diagram and we have provided the relationship also: Data Modelling (ER Diagram) Below is the modeling for the data that is based on the diagram of the ER and we have provided the relationship among both of them: As per the new proposed system I have identified below tables with their primary and foreign keys. Below I have provided all the table names and their data types with the foreign and primary keys: Table Name: Customer Column Name Type Key Customer_ID Varchar (50) Primary key Name Varchar (100) Email Varchar (100) ContactNo Varchar (20) Table Name: Supplier Column Name Type Key Sup_ID Varchar (50) Primary key Name Varchar (100) Address Varchar (200) ContactNo Varchar (20) Table Name: Job Column Name Type Key Job_ID Varchar (50) Primary key Job_Date datetime Part_ID Varchar (50) Foreign Key Table Name: Order Column Name Type Key Order_ID Varchar (50) Primary key Sup_ID Varchar (50) Foreign Key Part_ID Varchar (50) Foreign Key Order_Date datetime Quantity Decimal(5,0) Table Name: Invoice Column Name Type Key Pur_ID Varchar (50) Primary key Sup_ID Varchar (50) Foreign Key Pur_date datetime Quantity Decimal(5,0) Price Decimal(5,2) Table Name: Equipments Column Name Type Key Equipment_ID Varchar (50) Primary key Name Varchar (100) Price Decimal(5,2) Condition Varchar (10) Table Name: Supplier_part Column Name Type Key Sup_ID Varchar (50) Foreign Key Part_ID Varchar (50) Foreign Key Part_Desc Varchar (100) Table Name: Stock Column Name Type Key Stock_ID Varchar (50) Primary key Part_ID Varchar (100) Foreign Key Price Decimal(5,2) Quantity Decimal(5,0) Table Name: JoB_Part Column Name Type Key JOB_Part_ID Varchar (50) Primary key Job_date datetime Part_ID Decimal(5,0) Foreign Key Table Name: payment_cust Column Name Type Key Customer_ID Varchar (50) Primary key Payment_id Varchar (50) Foreign Key Transaction_ID Varchar (50) status Varchar (5) Table Name: Customer_job Column Name Type Key customer_ID Varchar (50) Foreign Key Job_ID Varchar (50) Foreign Key Requirement Specification Acme Garage needs to implement a new system to manage all transactions related to customer, supplier and job. Based on the requirements below are the functional and Non-functional requirements. Programming prerequisites determination sets up the premise for an understanding amongst clients and contractual workers or suppliers (in business sector driven undertakings, these parts might be played by the promoting and advancement divisions) on what the product item is to do and in addition what it is not anticipated that would do. Programming prerequisites particular allows a thorough appraisal of necessities before outline can start and lessens later overhaul. It ought to likewise give a reasonable premise to evaluating item costs, dangers, and calendars Functional Requirements: Most necessities definition concentrates for the most part on practical prerequisites, which are based upon the normal working of the item or framework to be made. Working regularly is compared with item/framework highlights for which you may have a menu or catch decision, for example, recognize a client, select a thing to arrange, and compute the sum due. System should capable to maintain the records of customer, suppliers and jobs It will raise an alert for pending payment in case a customer request for repair. Invoice for repair should be send as soon as job is complete and notification should be provided on regular basis. Low stock alert should be raised automatically and once approved this will share the PO with respective supplier. As soon as supplier deliver the goods stock will be updated and payment will be processed to the supplier. Income tax statement should be populated by fetching the expenses verses income with equipment value with depreciation in mind. Customer invoice will be generated once the job marked as complete. Non-functional Requirements: Nonfunctional necessities allude to an entire slew (I've distinguished more than 30) of qualities including execution levels, security, and the different "ilities, for example, convenience, dependability, and accessibility. Constantly, prerequisites definers get wrapped up in how the item/framework is relied upon to work and dismiss these additional components. New system will integrate with income tax department portal to submit annual returns. Notification can be on or off for respective customer or supplier System can be accessible over internet We should make sure that the system that was build will get available on the internet so that it can use ever. In the above we have provided both the requirements that are non-functional and functional those are used in the system. Proposed Business Solution The proposed business solution have captured the information of customer, supplier and JOB. The new system is designed in such a way that it would be able to cater below functions from UI. Below we have provided the details of the UI that can be used for the solution of the proposed business and also the reports that are used in the UI so that we will have all the details at one place. Details of UI: Below are the details which are used in the UI Customer: This UI should capable to add/update or delete the new and existing customer. Supplier: This UI should capable to add/update or delete the new and existing supplier. Part: This UI should capable to add/update or delete the new and existing part and their mapping with a job. Job: This UI should capable to add/update or delete the new and existing job details. Customer Payment: This UI should be able to process customer payment record and update the data in database and set notification for pending payments. Order: This UI should capable to add/update or delete the new and existing Orders. Invoice: This UI provides to generate Invoice based on the job data and previous payment history. Equipment: This UI should capable to add/update or delete the new and existing assets. Reports: Below are the reports that are used in the details of the project. Customer Details: This reports displays the details of certain or all customers Invoice for Customer: This is invoice details for individual customer within a date range. Order: All orders processes or order by supplier can be generated with the new system. Invoice for Supplier: This shows the invoice raised by supplier or suppliers for a specific date range. Job Details: This can be sued to get particular job details or can be have multiple jobs for a customer or jobs in a day or in a date range. Income tax statements: This can be populated all expenses verses income statement and fetch this information to further to income tax. Inventory: This report shows the current or historical data of inventory of products. Project Feasibility The new system required some cost to be implement below is the cost calculations Total time required to implement is about 500 hours at a rate of 120$ so final cost will be 60000$. Project is viable as the cost can be recovered within a years time so that the project will delivered successful. Here, we have explained the feasibility and also the cost and time that are needed for the execution of the project. By looking at that we will have all the details which will used in the project. Implementation This project can be implemented in 2 modes Intranet: In this solution application can be hosted with the Company premises and can only be access from office network only. Internet: This is the solution which is very popular these days.in this solution project will be hosed on a remote server and can be accessed from anywhere in the world with the help on internet. I choose to prefer 2nd option to implement the solution so that KIM can access the system from anywhere and also customer and suppliers can also be access the system and setting the data and making and receiving the payment from online system. We have described about both the 2 modes in the above which can be used in the project. Maintenance Further maintenance is required for the above proposed solution as this system runs over internet so below could be the maintenance requirements Hosting should be renewed every year or should be purchased as bulk for 5 or 10 years Data should be backed up from remote location to local machine so that application performance should be better and secure. Application should be reviewed annually so that if any change in data can be captured with implementing new requirements. Conclusions Acme is using manual data entry in excels and paper work with some data in database so chances of mistakes can be higher and duplicating the data is another major concerns. This new system help them in getting the correct and real time view of stock and help them in generating more revenue and interacting with suppliers and customer on daily basis. So, here we have provided the revised DFD and all the updated tables with their primary keys and types of data so that we can distinguish between older and new version.