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.
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.





Question 1
Based on the above case study, prepare a complete set of tables that would be needed to
Support NATASCO to develop system that would enable the storage of all the information.
Identify all the important attributes for all the tables that you suggest. Provide a suitable
Data type for each of attributes to reflect the usability.
Develop a normalized database for all the tables and you should show an example of each of
The 1st, 2nd and 3rd normalization process involved for your tables.
[25 Marks]
Question 2
(a) For each of the tables created in question 1, identify all the primary keys, foreign
Keys and also any composite keys that would be used to maintenance the
Database.
[10 Marks]
(b) Write the SQL commands to be used to create each of the tables that you would
Included in the creation of the database for NATASCO. Assume you own
Constraints and data verifications.
[15 Marks]
Question 3
Analyse the case study and identify the entire possible user requirement for NATSCO’s
Daily and monthly operations as described above. List them down as different
Relationship with possible type of cardinality.
Draw an estimated complete ER diagram that would be able to represent NATSCO’s
Complete database.
[25 Marks]
Question 4
(a) Insert some of the appropriate data for each of the tables that you had created in
The database. Use the SQL commands to insert 5 different tuples for each of the
Tables.
[10 Marks]
(b) Create your own queries (10) that would be used by the NATASCO’s
Management team to help them in their daily activities of using the database. Use
The SQL commands to write your queries and provide the answer from you
Sample data above.
[15 Marks]

Give a complete answer

Please don't ask or expect us to do your homework for you.

No dear.. its a kind help..

Love you so much if you can answered..

Any body there to help me..
In this assgnment..
Please be my hero..
Will love you so much once you can help me..
Most appreciated your kind help

We are not going to do your work for you. If you post what you came up with, then we can critique it.