程序代写案例-CSE1IIT

欢迎使用51辅导,51作业君孵化低价透明的学长辅导平台,服务保持优质,平均费用压低50%以上! 51fudao.top
La Trobe University
Department of Computer Science
and Information Technology

CSE1IIT Database Assignment
15% of your total grade
Objective
Demonstrate your knowledge and understanding of relational databases and query design.
Due Date
See LMS for details

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 5 days past the due date.
Copying, Plagiarism
This is an individual assignment. You are explicitly instructed not to work in groups.
DO NOT COPY & PASTE FROM WEBSITES! It must be in your own words.
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
Please put your SQL code and answers in a MS word document.
You are required to upload BOTH the word document AND the SQL file from the SQL tool to LMS.
Click the “Download Database” button to get a copy of the database SQL file.
Make sure that you have been saving your answers as views.
Part A
Problem Description
You have been contracted to design a simple database system for a car mechanic shop.
When a car is brought to the shop, we create a "job" for it.
The job has a start time, an end time, and the customer that requested the work.
Each job consists of one or more tasks, each task is completed by a staff member.
Some tasks may require multiple staff members to complete.
The staff involved in each task need to be stored along with the number of hours the staff member
spent working on the task so we can calculate their pay.
A task may also require new parts from our inventory which need to be stored in the database.
Parts will also need to be catalogued, each part has a name, description, and a price.
Each car has a VIN, a make (the brand of the car), a model and a colour.
Cars also have the registration plate number stored in the database, but many of the cars in the shop
don't have plates yet, so this value may be null.
The customers and staff also need to be stored in the database, both will have a name, and an address.
We also offer payment plans, where the customer can pay off the job in monthly payments.
We will need to keep track of each payment and when it was received.
A table diagram (not an ER Diagram) is provided below.

Please study the above diagram carefully.
This assignment also has written questions, if you get stuck on an SQL task, try to do the written tasks
instead until you can get help.
I would advise that you save each task as a view.
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 downloading the SQL file (DatabaseAssignData.sql)
from LMS and using the “Open Database” button to load the file you downloaded.
You should see the new tables appear.
You can now delete the SQL code you pasted, only work on one query at a time in the SQL Tool.
Task 2
Create a query that displays all the cars in the database sorted by Make & Model.
Save this as a view for later use.
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.
Do not create a view until you are sure the code is correct.
You can view the code a view uses by clicking the magnifying glass icon.
Expected row count: 10
Task 3
Create a query that shows the details of each car (including its owner info), and how many jobs it has
had. A car may show up multiple times if it changed owners.
Task 4
Create a query that shows the details of all the parts that contain the word "Battery" and are under
$200
Use this And this
Task 5
Create a query 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 complete value is not present, 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 query 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 input.
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 query 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
All cars on the roads have a Vehicle Identification Number (VIN) which is unique to that car.
Our database could use the VIN as the CarID since they should be unique.
Alternatively, we could simply assign our own number to each car as they are added to the DB and store
the VIN separately in its own column.
List some pros and cons for each approach.
Task 10
Using Task 7 & 8, create a query that shows the total cost for each job.
Sorted by TotalCost descending. Do not round the numbers.
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.
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.
Do not round the numbers.

Task 12
Consider this code (don’t run it yet)
SELECT 0.3 / 0.1

A) What do you expect the output to look like?
B) What do you get when you run it in the SQL tool?
Investigate how floating-point numbers work.
Why do you get these strange decimal places?
Do you think that floats (reals) are a good way to store money values in our database?
Can you think of another way to store money in the database?
Task 13A
Using Task 10 & 11 create a query 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 13B
Using Task 13A, 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 must NOT 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"
Task 13C
Assuming that today's date was 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 14
Using the download database button

Make sure you submit the file it gives you in addition to your Microsoft Word document.
Part B
This part contains questions that could appear in an exam.

Task 1
Database servers can have multiple users connected to them at the same time.
SQL databases have a feature known as "transactions" to help deal with multi user environments.

Investigate what a transaction is and what they do.
Give at least a paragraph for each of the four ACID properties (You can refer to online materials but
write the answers in your own words!)

Task 1A
Consider a bank database that has a database with tables shown below.

Customer(CustomerId, Name, Address, Phone)
Account(AccountId, AccountName, CustomerId, AccountBalance)
What SQL code you would need for two customers would transfer money to each other (UPDATE:
https://www.w3schools.com/sql/sql_update.asp).
Task 1B
Consider what could happen If there was a power outage.
What potential issues this database could have and how transactions could prevent these problems.
(You can refer to online materials but write the answers in your own words!)

Task 2
The bank database in Task 1A is rather simple.
Consider if you were asked to design a simple database for a bank to manage accounts, balances, and
credit cards.
How would you do it?
Include an ER diagram and explain your choices (at least 3 entities).


Task 3
Consider an SQL database with these two tables with data.

If you were to run the queries below on a database containing the above tables what would the result
look like?
You should answer this question using tables in MS Word. Don’t forget to include the column names.
3A
SELECT A, B, D
FROM Alpha
INNER JOIN Beta ON Alpha.C = Beta.C
ORDER BY B
3B
SELECT D, COUNT(A) AS MyCount
FROM Alpha
INNER JOIN Beta
ON Alpha.C = Beta.C
GROUP BY D

3C
SELECT B, C
FROM Alpha
WHERE B LIKE 'C%'
3D

SELECT D, B
FROM Beta LEFT JOIN Alpha ON Alpha.C = Beta.C
3E

SELECT A, B, C
FROM Alpha
ORDER BY (A-3)*(A-3), B


欢迎咨询51作业君
51作业君

Email:51zuoyejun

@gmail.com

添加客服微信: abby12468