La Trobe University
Department of Computer Science
and Information Technology
CSE1ITF Assignment 2
SP2 – 2018
10% of your IIT grade
Objective
Demonstrate your knowledge and understanding of relational databases and query design.
Due Date
TBA via the LMS.
Delays caused by computer downtime cannot be accepted as a valid reason for a late submission
without penalty. Students must plan their work to allow for both scheduled and unscheduled
downtime.
The LMS will be configured to allow you to submit as many times as you like, the most recent version
will be marked.
Late submissions will incur a 5% penalty for each day that it is late.
If you change your submission after the due date it is considered a late submission and will incur a
5% penalty for each day that it is late
Assignments will NOT be accepted after 10:00 AM Tuesday 3rd of May
Copying, Plagiarism
This is an individual assignment. 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. The Department of Computer Science and IT treats
plagiarism very seriously. When it is detected, penalties are strictly imposed.
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 LMS. Please include your student ID in the file
names of both files.
Introduction
This task continues from the mechanic shop database.
A table diagram (not an ER Diagram) is provided below.
Please study this image 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 decent 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, like so
SELECT FirstName FROM Customer
Task 1
Open the SQL Tool on LMS.
It is recommended that you clear you database, 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 and
importing it into the SQLTool using the Open Database button.
You should see the new tables appear.
Use this And this
Task 2
Create a view that displays all the 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, you can 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
Create a view that shows the details of each car (including its owner 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.
Task 6
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 $90 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.
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
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
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
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.
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 NumberPlate contains a table of registration numbers, CarIDs and the time they were
entered into the system.
A single car may have multiple number plates in the system.
Create a view that shows the newest registration number per CarID.
Part A
First make a view that shows the highest Timestamp per CarID. Give the view a name.
Part B
Then select the CardID, NumberPlate, 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
Using Task 7 & 8, Create a view that shows the total cost for each job.
Sorted by TotalCost descending.
The results should include (JobId, LabourCost, PartsCost, TotalCost)
Save this as a view.
Task 11
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.
Task 12A
Using Task 10 & 11 Create a view that shows the outstanding balance of each completed job is.
The result should have (JobId, BalanceOutstanding, NumberOfPayments, LastPaymentTimestamp)
This can be done by using Task 11 LEFT JOINed to task 10, and subtracting the amounts.
Task 12B
Using Task 12A, create a view that shows the outstanding balance per customer.
You can simply sum the outstanding balance of all the jobs that belong to that customer, and max
the last payment date.
Customers who have no jobs do NOT need to appear.
The result should have (CustomerId, FirstName, LastName, BalanceOutstanding,
NumberOfPayments, LastPaymentTimestamp, LastPaymentDate)
LastPaymentDate is the human readable format of LastPaymentTimestamp.
This should be in the format of “dd-mm-yyyy”
If BalanceOutstanding shows strange decimal places you can use the round() function.
Round(BalanceOutstanding, 2) will round the value of BalanceOutstanding to two decimal places.
Task 12C
Assuming that today’s date is 10-10-2016, (UnixTime: 1476057600)
Create a view that shows the customers who have not paid for over 45 days AND have a
BalanceOutstanding greater than zero.
Task 13
Consider this code:
SELECT 26.3 – 10.52 – 15.78 AS Result;
What should the answer be?
Type this into the SQL Tool and verify what it says.
Investigate online how floating-point numbers work and why did the rounding issue occur?
Are floating point numbers appropriate for working with money?
Can you think of an alternate way to store money in the database?
Task 14
Investigate database constraints. What kinds of constraints are available and what do they do?
The SQL Tool uses SQLite internally, you can look up the documentation for SQLite online.
Task 15
Explain what the difference between a LEFT JOIN and an INNER JOIN.
Why would anyone ever use a LEFT JOIN?
Task 16
SQL databases have a feature known as “transactions”. Investigate what a transaction is and what
they do.
Give at least a paragraph for each of the four ACID properties.
Task 17
Investigate what a database trigger is, how are they useful?
Can you think of an example of when a trigger would be useful?
Task 18
1. Ensure that your SQL code is correctly formatted with the syntax highlighting colours.
2. Download the SQL file from the SQL Tool.
3. Make both files have your student in the name.
4. Open the SQL file in Notepad++ and check that it has your tables and views.
5. Submit both files to LMS.
== END OF ASSIGNMENT ==
The post CSE1ITF Assignment 2 appeared first on My Assignment Online.