ER Diagram for a Mechanic Shop’s Database

Connect with Assignment Expert Now

Assignment Task

Introduction and Background

A table diagram (not an ER Diagram) for a mechanic shop’s database is given below. 

• Please study the image above carefully. 
• It looks big and complicated, but each of your queries will only have a maximum of 2-3 joins. 
• You are required to create a view for each of the SQL query tasks.  
• Please give them a meaningful name, something like “Task3_CarDetails”. Avoid spaces in the names. 
• You must save your SQL code into a word document. You MUST save with the correct SQL syntax highlighting (the colourful text).
• To get the colourful text, click the copy SQL button in the SQL Tool, and click paste in MS Word. 

Task 1

Open the SQL Tool on LMS. 

• It is recommended that you clear your database first. To do this, click the “Clear Database” button. 
• Import the data into the SQLTool. You can do this by opening the provided SQL data file on LMS (DBAssessmentData.sql) and importing it into the SQLTool using the Open Database button. 
• You should see the new tables appear once the import is completed. 

   

Task 2 

Create a view that displays all cars in the database, sorted by Make & Model. 

To create a view, use the CREATE VIEW command like so: 

You can use the command DROP VIEW Task2; to delete the view. 

Remember that a view is a saved query, and you can always look at the SQL code of a view or table by clicking the small magnifying glass icon.      

Task 3 

Create a view that shows the details of each car (including its owner’s info),  and how many jobs it has had. 

Task 4

Create a view that shows the details of all the parts that contain the word “Battery” and are under $200.  

Task 5 

Create a view that shows the jobs that are not yet complete. 

• A job is considered complete when it leaves the shop. 
• The time this occurs is stored in the JobCompleted table. 
• If the job is not present in the JobCompleted table, then the job is not yet complete.  

This IT Assignment has been solved by our IT Experts at TVAssignmentHelp. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment Experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.

• Uploaded By : Roman

• Posted on : January 06th, 2020

• Downloads : 0

Apply on checkout*Coupon Code:- HNY2022

Get Flat 20% + 22%* Off on Our One-to-One Academic Assistance