1 | P a g e

TCSE1IIT Assessment 1

Assessment—Database and SQL Queries

Assessment Details

Assessment Title:

Database and SQL Queries

Assessment Type: Moodle Submission

Total Marks: 100

Submission Deadline: Check Moodle for details

Mark Weight: 20%

Objective

Demonstrate your knowledge and understanding of relational databases and query design.

Copying, Plagiarism

This is an individual assessment. You are explicitly instructed not to work in groups.

Plagiarism is the submission of somebody else’s work in a manner that gives the impression

that the work is your own. For individual assignments, plagiarism includes the case where two

or more students work collaboratively on the assignment. When it is detected, penalties are

strictly imposed to all involved parties.

Submission Guidelines

You need to submit two files,

1. An MS word document containing the SQL code AND your answers to the written

questions.

2. An SQL file that can be downloaded from the SQL Tool by clicking the download

database button, it will save a SQL file into your download folder.

You are required to upload your solutions to the Moodle. Please include your Student ID and

first name in the file names of both files.

2 | P a g e

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.

This will give you the correct highlighting, as shown below:

SELECT FirstName FROM Customer

3 | P a g e

Task 1 (5 marks)

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 (5 marks)

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:

CREATE VIEW Task2 AS SELECT * FROM Car

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.

I would suggest that you make sure the query works correctly before saving it as a view.

Task 3 (5 marks)

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

had.

Task 4 (5 marks)

Create a view that shows the details of all the parts that contain the word "Oil" and are under $100.

Task 5 (5 marks)

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.

Task 6 (5 marks)

Create a view that shows the same data as what is in the TaskStaff table.

But also provide an extra column named "StaffTaskCost" which shows the dollar amount of that staff

member's time on the given task.

You can assume that we charge the customer $150 per hour.

The TaskStaff table contains a column named Minutes, you can use this to compute the number of

hours and hence the number of dollars.

4 | P a g e

Tip: when dividing numbers, you may need to force the system to perform floating point division

instead of integer division. You can do this by adding a decimal place to the number.

For example, divide by 60.0 instead of 60.

Save this query as a view for later use.

Task 7A (10 marks)

Create a view that shows the total parts cost per task.

Make sure that you include the tasks that did not use any parts.

This task will require you to use left joins and sum()

Make sure to multiply the price by the count first, then do the sum().

Be aware that the left join may cause nulls to appear in the data. You must use the IFNULL function

to replace any nulls with zero. The IFNULL function will return the first non-null argument. In other

words, IFNULL(1, 3) will return 1, but IFNULL(null, 3) will return 3.

The result should have (TaskId,

TotalPartsCost)

You should save this as a view for later use.

Task 7B (10 marks)

Make another view that shows the total parts cost per job.

You should use the view you created from Task7A.

It would be wise to also use IFNULL() & left joins just in case there are jobs with no tasks associated

with them.

The result should have (JobId, TotalPartsCost)

You should save this as a view for later use.

Task 8 (10 marks)

Create a view that uses Task 6 which shows the total labour cost for each job.

Keep in mind that some of the tasks may involve multiple staff members.

The results should have 2 columns (JobId, TotalLabourCost) Making sure that all jobs show up, even if

they have no labour costs.

This query should be saved as a view for later use.

5 | P a g e

Task 9

The number plates on cars sometimes changes, especially when they are sold. Some cars may not

even have number plates yet. It is important that we are able to keep track of the cars when this

happens.

The table “Plate” contains a list of registration numbers, CarIds and the time they were entered into

the system—Timestamp.

A single car may have multiple number plates in the system.

Create a view that shows the newest registration number per CarId.

Part A (5 marks)

First make a view that shows the highest Timestamp per CarId. Give the view a name.

Part B (5 marks)

Then select the CardId, PlateNumber, and Timestamp from the Plate table, INNER JOINed to the view

you made in Part A.

Inside the ON clause for this join, require that BOTH the timestamp and CarId must match. The

final result should have (CarId, LastKnownNumberPlate)

Task 10 (10 marks)

Using Task 7 & 8, create a view that shows the total cost for each job, sorted by TotalCost in

descending order.

The results should include (JobId, LabourCost, PartsCost, TotalCost)

Save this as a view.

Task 11 (10 marks)

We allow some customers to pay in instalments.

Create a view that shows the total amount of money that has been paid towards each job.

Only show the jobs that have been completed. But make sure to include completed jobs even if they

have had no payments.

This can be achieved by selecting from the JobCompleted table, and left joining to the Payment table.

The result should have (JobId, AmountPaid, LatestTimestamp, NumberOfPayments)

LatestTimestamp should show the timestamp of the last payment made.

If there are any jobs that have not received any payments, use IFNULL() to ensure that the

AmountPaid shows zero and that LatestTimestamp instead shows the time of job completion.

6 | P a g e

Task 12A (5 marks)

Explain, with examples, the differences between a LEFT JOIN and an INNER JOIN.

Why would anyone use a LEFT JOIN?

Task 12B (5 marks)

SQL databases have a feature known as "transactions". Investigate what transactions and what

they do. Briefly describe, with examples, the four ACID properties of database transactions.

== END OF ASSESSMENT ==

Note:

• Ensure that your SQL code is correctly formatted with the syntax highlighting colours.

• Download the SQL file from the SQL Tool.

• Rename both word and SQL files to include your Student ID and First Name.

• Open the SQL file in Notepad++ and check that it has your tables and views.

• Submit both files to Moodle.

51作业君版权所有