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 j
ob 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作业君
