NATASCO Sdn. Bhd. is a transportation company providing services to transport factory
workers for MNC companies in Muar, Johor for the last 20 years. Now all the operations
are kept tracked manually. The company has its own garage and service center for the
vehicles but depends on other workshop for repairs of the engine and vehicle body. The
company’s new managing director would like to computerize all their operational and
maintenance processes to be more efficient and effective.
The company owns a total of 10 buses and 3 vans. There are 10 full time and 5 part time
drivers. Each bus is allocated to a route to fetch the factory workers and there are 8
routes to be covered at any given one time. Whereas the vans are used to ferry additional
workers which would be cover more than one route.
Each of the vehicle need to be serviced at certain intervals based on their mileage which
is done at the company’s own service center. They need to keep track the next service
date and what are the things need to be done. The vehicles also need to be inspected at
the Road Transportation Department (RTD) once or twice yearly for approval to get their
insurance and road tax. They also need to track the next inspection date for each vehicle.
The maintenance of the vehicles is tedious job as well keeping record of their repairs and
other related works. Here is the list of all the possible maintenance jobs that would be
done and they need to know when the last one was done to see if they need to do for the
next service schedule: change engine oil, change tyres, grease pump, brake checking,
gear-box checking, air-con checking and steering pump.
The company needs to keep track all the engine and body repairs done for each of the
vehicles with the following details: date, parts changed/repaired, cost, where (workshop
name).
The most critical part for the company’s operational function is to keep track the mileage
covered and the petrol consumption of each vehicle for every month. This is to monitor if
there is any extra wastage or over-used.
At any time the management would like to check who the driver for the particular route
or vehicle is. The management also would like to see the history of the vehicle’s repair
and maintenance reports. An alert should be send to the relevant person when the due
date for the maintenance or inspection is nearing with certain cut-off time period.
Make your own relevant or needed assumptions in answering the following question
based on the above case study scenario.
You are encouraged to use the SQL server 2005 to prepare you answers. You can submit
your codes and the database that you created which would have higher evaluation.
workers for MNC companies in Muar, Johor for the last 20 years. Now all the operations
are kept tracked manually. The company has its own garage and service center for the
vehicles but depends on other workshop for repairs of the engine and vehicle body. The
company’s new managing director would like to computerize all their operational and
maintenance processes to be more efficient and effective.
The company owns a total of 10 buses and 3 vans. There are 10 full time and 5 part time
drivers. Each bus is allocated to a route to fetch the factory workers and there are 8
routes to be covered at any given one time. Whereas the vans are used to ferry additional
workers which would be cover more than one route.
Each of the vehicle need to be serviced at certain intervals based on their mileage which
is done at the company’s own service center. They need to keep track the next service
date and what are the things need to be done. The vehicles also need to be inspected at
the Road Transportation Department (RTD) once or twice yearly for approval to get their
insurance and road tax. They also need to track the next inspection date for each vehicle.
The maintenance of the vehicles is tedious job as well keeping record of their repairs and
other related works. Here is the list of all the possible maintenance jobs that would be
done and they need to know when the last one was done to see if they need to do for the
next service schedule: change engine oil, change tyres, grease pump, brake checking,
gear-box checking, air-con checking and steering pump.
The company needs to keep track all the engine and body repairs done for each of the
vehicles with the following details: date, parts changed/repaired, cost, where (workshop
name).
The most critical part for the company’s operational function is to keep track the mileage
covered and the petrol consumption of each vehicle for every month. This is to monitor if
there is any extra wastage or over-used.
At any time the management would like to check who the driver for the particular route
or vehicle is. The management also would like to see the history of the vehicle’s repair
and maintenance reports. An alert should be send to the relevant person when the due
date for the maintenance or inspection is nearing with certain cut-off time period.
Make your own relevant or needed assumptions in answering the following question
based on the above case study scenario.
You are encouraged to use the SQL server 2005 to prepare you answers. You can submit
your codes and the database that you created which would have higher evaluation.
Asked Apr 21, 2013
0