This is a solution of Essay On Acme Garage Assignment in which we observe the data flow of the business data of Acme garage and made a new database system for it.
This report is prepared for the Acme Garage case study. The Acme garage specializes in repairing the cars. Presently the Acme garage update and maintain its various data such as; customer data, suppliers’ data, car parts invoice data, etc. in the Microsoft Excel spreadsheet. We investigate the overall business and business communication of Acme garage. We observe the data flow of the business data of Acme garage and made a new database system for it, so that it can easily maintain their various business data. We choose the Microsoft Access to develop a new database systems for the Acme garage.
The Acme garage is providing the car repairing services to their customers. When the customers comes for repairing their cars, the Acme always check the customer has paid previously for work done or not before fixing the car. After the car repair and ready to deliver to the customer, an invoice report of whole work done is provided to the user after this the customer needs to pay the full amount within 30 days.
The acme kept the detail of the customers and job in the customers file and job file respectively. The employee who is responsible for updating and maintaining the database, maintain the database. The mechanics are repairing the cars and also write the entries into the job book. Its stock the car parts which is used frequently and purchase it’s from the parts supplier regularly. The parts which are used less frequently are ordered to the suppliers when required; the Acme is invoiced by the suppliers for purchasing of these parts. Reference to the Stock file will indicate if parts required for a particular job are in stock otherwise the parts will have to be ordered in. In case where items are removed from the stock, the balance of stock needs to be reduced. The cost of the car parts is added from the invoice which is received at the time of delivery of parts. After completing the job, hours of labour are maintained in the job file and the stock file. The order file will be used to determine the parts price which will record in job file also. Finally, the job file helps to produce an invoice for the customer. At the end of the month all the invoice will be paid in full sent from the supplier.
The key activities of the Acme garage are given below:
- Repairing Cars.
- Maintaining customer and suppliers database.
- Generating invoice report of the work done for the customers.
- Ordering of car parts.
- Managing suppliers for parts and bills.
- Maintaining stocks of frequently used and less frequently used car parts.
- Dealing with bank and also sends the income tax return every year.
The Acme garage facing several types of problems in running its business easily due to the lack of advance and new updated technology, the problems are:
Complexity: The Acme is currently maintaining all its data like customer data, invoice data, suppliers’ data, etc. manually in spreadsheet database and files, which is quite complex work for the business.
Invoice generation: The Acme generating the invoice for the customer manually with the help of job file data. So the process of manually generating the invoice will take time and more chance of doing mistakes in it.
Keeping the records of mechanics and other staffs: The Acme manually keeping the records of working staffs and mechanics such as: attendance, working hours, salary and others information in job file.
Cash payment: The Acme uses the cash payment mode so sometimes in case if the customer out of city, than it suffers from late payment.
Stock maintenance:The acme maintain the car parts stock manually by making the file of parts available in the stock and also have to use the file for deciding the price of parts in generating the invoice.
Maintaining income tax return data: The Acme maintaining the various income tax return data manually in a file so it is quite complex.
The present system of Acme garage has some business gaps, so that it’s not grab the more customers reach.
We all are knows that, today’s the IT is an important part for all the type of business to run the business smoothly and managing well. It is also helpful in providing the better services for the customers. The Acme is using the old methods to maintain the various data and other activities of business. It has no online portal so that user can contact with it 24*7 when they wants. The customers have to go to the garage for any queries related to the car repairs. Also they have to go to the garage for receiving the cars and bill pay. So that we can say that the lack of information technology is the biggest business gaps of the Acme garage. There is no delivery option of the car at customers’ doorsteps; it is also a gap for business in Acme garage.
There are various opportunities available for the Acme garage by implementing the new system:
Easy maintenance of the business data
- Easy maintenance of the customer data
- Easy maintenance of the work done and stocks
- Better services available for customers comparing existing services
- Grab the customer attentions
- Easy maintenance of supplier and parts order reorder data
- Easily maintain the stocks report
- Easy to generate the invoice report
The functional decomposition is a fundamental industry analysis. It breaks the complex problem into the successive layers for more manageable and comprehensive pieces.
The functional decomposition of the Acme garage system is given below:
- Adding new customer
- Update/delete/retrieve customers’ data.
- Update/delete/retrieve suppliers’ data.
- Update/delete/retrieve stocks data.
- Generating the automatic invoice receipt.
- Login of the garage staffs and mechanics.
- Managing income tax return data.
- Manage order and reorder of car parts.
- Calculate and alter payment schedule.
- Calculate penalty.
- Repairing of cars
- Customer need to register
- Customer has to clear due payment for services
- After clearing due payment payments, customer can get the repairing
- Job manager assigns job to employee
- Job manger add, update and delete the customer database, order database, job database, Asset registered database, and Invoice database
- Employee checks stock database for parts and updates stock and order database
- Employee asks for stock to supplier when stock not available
- Employee gives job status to job manager
- At the end of the month job manager clears all the payment of orders and income tax
- Customer has to login first to get the services.
- Customer has to clear the due payments first then only services can be given.
- Customer details are entered in the customer table.
- Stock table need to be checked by the employee that the parts are available or not.
- Parts are ordered to supplier if not in stock.
- Stock and order table should be updated.
- Job manager should assign and take updates of the job and can update job, order and customer table.
- An invoice should be created for each order and service.
- An asset should be created for income tax.
Notations used in ERD
PK – Primary Key
FK – Foreign Key
Cust – Customer
Emp – Employee
Equip – Equipment
Business rules adopted
- Microsoft Visio 2013 (Professional)
- Crow’s Foot Database Notation
- We used a rectangle box which represents the entity. In the entity box we write the name of entity at the top and attributes name in the attribute.
- We can set primary key and foreign key on the attributes of the entity by right clicking on the attribute and select ‘set primary key’ or ‘set foreign key’.
- Primary key attributes, primary key separator and attributes can also be added by drag and drop.
- The relationships between the entities can be shown through the ‘relationship’ option. We can drag and drop the relationship option and can adjust it accordingly.
Definition of all the entities
Customer –In this, all the details of the registered customers is stored.
Supplier – In this, all the details of suppliers is stored.
Stock – In this, the stock of the parts in the garage is stored.
Employee – In this, all the details of the employees working in the company are stored.
Position – In this, the position of the employee in the company is stored.
Order – In this, all the details of the parts purchased from the supplier is stored.
Job – In this, the details of the job done by the employee is stored.
Invoice – In this, all the invoices made are stored.
Asset Registered – In this, the depreciation of the equipment on the income tax return is stored.
English Interpretation of all the relationships
- Customer has one-to-many relationship with job.
- Job has many-to-one relationship with the employee.
- Employee has one-to-one relationship with the position (One employee can have only one position in the company).
- Supplier has one-to-many relationship with the order.
- Order has one-to-one relationship with the Invoice (On one order one invoice is created).
- Stock has many-to-one relationship with Asset Registration.
Customer – cust_id, Cust_name, Cust_address
Job – Job_id, Cust_id, Working_hours
Employee – Emp_id, Emp_name, Position_id
Position – Position_id, Position_name
Supplier – Supplier_id, Supplier_name, Supplier_company_name
Order – Order_id, equip_id, Supplier_id
Invoice – Invoice_id, Order_id, Income_tax_amount
Stock – equip_id, equip_name, type_of_parts
Asset Registration – Asset_id, Income_tax_return_dep, equip_id
Customer Use Case
Employee Use Case
Supplier Use Case
Job Manager Use Case
The functional requirement is further divided into the several types:
Details of stored data:
- Inventory items: Name, location, uses, personnel uses, amounts of uses, cost of item.
- Project names: Project start and end date, materials needed.
Details of output:
- Inventory list screen
- Add new/delete/update item screen
- Check availability screen
- Check location screen
- Parts order and reorder screen
- Print report screen: Inventory report, personnel report, invoice report, etc.
Details of input:
- Current inventory system info
- Customer/supplier name
- Car name
Details of processing information:
- Uses details
- Cost details
- Personnel uses
- Project uses
The non-functional requirements of the new system of Acme garage are given below:
- MS window 2000 server
- MS access
- Laser printer
- 10Base- T Network Interface card
- 300 MB hard disk space
- 256 MB RAM
- User name and password identification for all the users
- MS windows Primary Domain controller
- Daily back-ups of database
- Data restoration within the 24 hours of data loss
- Simple user interface
- No large user manual required
From the cost point of view, there is a requirement to spend some amount of money to build such system but it will be worth spending. The cost will be spending to train the staff of the database to access its functionalities. It comprises of operational cost, personnel cost, maintenance cost and technical expenditure.
The system is made or developed in Microsoft access database so it has various benefits, some of them are:
- User friendly: It is familiar to window look and feel which is appealing to the most users.
- Concurrent users: Its support approximately 250 users.
- Custom design: Customized forms can be created which will make it easier to enter the information.
- Scalable: It can be upgraded according to the size of your business.
- Legacy data: You can easily import the various formats of data so that the existing data is not lost. This will save many lots of time of data entry or input.
- Software integration: It has been designed to integrate or used with the other software very well in the Microsoft Office Suit.
- Implementation and maintenance: The implementation and maintenance of the MS access database is cheaper compared to other, due to its simplicity.
There is a risk of data corruption in the MS access database. In the Microsoft access so many reasons are available which may corrupt or damage your MDB file. The reasons are classified into two categories:
- Hardware reason: Whenever the hardware fails in transferring or storing the data of the MS access database, the database seems to be corrupted. There are mainly three types of hardware failures:
- Data storage Device failure: In case; if the hard disk has some bad sectors and the database is stored in these sector then the data may be corrupted.
- Power failure: If the power is failure while you accessing the MS database then it may damage of corrupt your database system.
- Faulty networking device: If the network interface cards, routers, cables, hubs and any other devices constituting the network links have problem, then the remote access of the MDB database may make it corrupted.
- Software reason:
- Incorrect file system recovery: Some times when you want to recover your lost data by hiring any data recovery tool then the file recovered may cause to corruption of database.
- Write operation abort: Incase if the access database shutdown abnormally then it may cause of data corruption.
- Virus or other malicious software: Various malicious software like; Trojan.Win32.Cryzip.a will infect and damage the access database, so that it is invisible.
The options for implementing a multi user MS access application are given below:
- Compact the database often: Compacting of the database allows queries to use the new table statistics to create the new optimization plan. During the compaction process, pages of records and indexes are reordered in the contiguous blocks for the faster sequential access of the database.
- Design for the multiuser environment, not a single user: The access database provides many features, even for the novice user like name AutoCorrect options and many others. So design a multiuser environment of the database not a single user.
- Verify the folder privileges: All the users must have the RWCD privileges for the shared folder, in order to share the database for simultaneous use.
- Keep a persistent connection open: Minimize the numbers of connection, which are made by the each client, and increase the performance of tables which is linked or in relation. Create a table in the backend of the database which has only one record for doing this.
- Use indexes: You have to establish indexes on any fields which are used for sorting or as criteria in queries. Without indexes, the entire Record set is transferred over the network prior to any filtering
- Avoid using the Domain aggregate functions: The domain Aggregate Functions are used to look up for a value or calculate statistics within a record set. These include DCount, DLookup, DMax, DMin, DSum, DAvg, DFirst, DLast, DStDev, DStDevP, DVar, and DVarP, all of which may adversely affect performance.
- Speed-up the tab controls: If the database include a tab control which contains the sub forms, implementing a tab-on demand techniques which can be very helpful.
- Close inactive sessions: You must have to consider a close inactive techniques for close the sessions. This method or technique helps to prevent the user from keeping the database open for the long time periods with no activity.
- Optimize the multi user environment for the client/server architecture: Optimize the multi user environment for the client/server architecture to be aware of other issues such as:
- Folder name should not exceed the eight characters.
- The database path should not traverse through the multiple folders.
- Make sure the antivirus software installed in your system is not configured to scan for the network folders.
- And the last option is to configure your system and check the hardware and clean out the temporary files and drag the hardware.
The maintenance request is categorized in mainly two types:
- Planned maintenance request:
- Non-critical patches are scheduled on the quarter basis.
- The maintenance of the database is scheduled during the business hours.
- Application team should anticipate upgrading the version of database in every 2-3 years.
- Database service will provide the notification regarding the upgrading of the database version.
- Database services will not support the version of the database software which is no longer supported by the vendors.
- Unplanned maintenance request: In the unplanned maintenance request urgent repair, patch and update of data and database version comes to prevent from the database failure.
Request for implementing changes
The implementing changes requests of the Acme garage system are given below:
- Request for Change type: The user request for the type of changes like: emergency, fix, new or enhancement. It simply means why the user want to change the system.
- Impact of the new system: It means user request for the impact of the new system. Which type of system impact user wants in the new changed system like high impact, low impact or medium impact?
This report is prepared by analyzing the whole business process of the Acme garage. The system is planned specially for the key work of the acme garage like for managing the various business data of the Acme garage, generating invoice receipt for the customer, managing stocks for the car parts, price of the car parts, managing suppliers data and work hour of the staffs working there. With the help of this system, the Acme garage can doing its business easily and also can offer the better services to its customers so that it can also help to grab the customers.
Support.office.com. (2016). Create a simple report – Access. [online] Available at: https://support.office.com/en-us/article/Create-a-simple-report-408e92a8-11a4-418d-a378-7f1d99c25304 [Accessed 18 Mar. 2016].
(DBMS), A. (2012). Excellence: Advantages and Disadvantages of Database Management System (DBMS). [online] Navdeep19.blogspot.in. Available at: http://navdeep19.blogspot.in/2012/04/advantages-and-disadvantages-of.html [Accessed 18 Mar. 2016].
Quackit.com. (2016). Querying a Database. [online] Available at: http://www.quackit.com/database/tutorial/querying_a_database.cfm [Accessed 18 Mar. 2016].
Assignment Help Site provide assignment writing service based on case study requirements in affordable prices and we are providing most flexible online assignment writing help, so book your Assignment with us,Order Now.