Click image for larger version. 

Name:	fc9d8e9a-dd99-4fb8-a2e9-a90a613d79e8.jpg 
Views:	3 
Size:	108.5 KB 
ID:	17440

Can anyone tell me if the attached relational database looks correct.

my assignment details for it are below

The main Entities required include:
• Car
• Mechanic
• Part
You may however need to add more entities.
The Entities are related as follows:
• A Mechanic can service many Cars
• A Mechanic may use a number of Parts during a Service
• The garage maintains a stock of all the Parts
• Each Service is assigned to a single Mechanic
• A Car can be serviced a number of times, by possibly different mechanics.
Consider carefully the fields required for each entity and the associated domain values.
You may need to do some requirements analysis regarding the type of information typically held relating to this type of application so that your model is reasonability realistic with meaningful data. Meaningful data makes it easier to check the validity of the query results. Ensure your data model can support the functionality required in part (b) of this question.

(a) Using the Model First approach and the Entity Framework Designer in Visual Studio develop a normalised Entity Relationship Model for the scenario described above. Then generate a database schema from the model you have developed.
Now add sample data to the database to allow reasonably realistic testing to be carried out. Submit the Entity Framework Model you developed and using “Show Table Data” provide screen dumps of the data stored in each of the database tables. (10 marks)

(b) Using your database of information (from part a) develop a Garage Admin System using a Visual Studio C# Console application. It should have functionality to allow you to:
1) List (sorted on Surname) the names of all the Mechanics employed in the garage. (2 marks)
2) Given a Car “RegNumber” list the names of all the Mechanics (in order of service date) who have serviced that Car. (3 marks)
3) Given a Mechanic’s “StaffNumber” list all the Cars (RegNumber, Make, Model and OwnerTelNum) that were serviced by that particular Mechanic and the date each service took place. . (5 marks)
4) Given a “ServiceWorkNum” for a particular Service, list the registration number of the Car involved and the “PartNumber”, “PartName” and “Price” of all Parts used during the Service. (5 marks)
5) Given a Car “RegNumber” list details of the parts that have been replaced in that Car for all Services carried out. (7 marks)
6) Given a Car “RegNumber” calculate the cost of the most recent Service if the charge for a mechanic is £30 per hour. You should use at least two different car parts during the service.