Assessment details for ALL students
Assessment item 1
Due date: Friday of Week 5 (11:45pm AEST) ASSESSMENT
Weighting: 30% 1
Length: NA
Objectives
This assessment item relates to unit learning outcomes 1 and 2 in the unit profile.
Introduction
Students must complete all the following parts of this assessment tasks:
A. SQL Queries
B. Form
C. Report
Important
Download the Microsoft Access database, Century Engineering Job system.accdb, from the unit website and use it to complete this assessment.
If you get a security warning message when opening the downloaded database, then you need to open/click the options and then choose the option “enable this content”.
Do not modify the structures of the tables and relationships. Do not create additional tables. It is not recommended to delete any of the existing records from the downloaded database (hereafter referred as ‘your database’).
You must create SQL queries in your database and make sure those queries produce the desired results as shown in Part A. Save and name those queries suitably such as Q01, Q02, ......, Q10 respective to the question that you are answering.
You need to create a MS Access-form and also a MS Access-report that are related to Part B and Part C and save them in your database.
Century Engineering Job System database
The database, Century Engineering Job system.accdb, contains tables the following tables:
• Customer – this table lists customers of Century Engineering, including prospective customers who have not yet requested a job
• Job – this table lists jobs that have been requested by customers. The Customer ID indicates the customer who requested the job.
• Employee – this table lists employees of Century Engineering.
• JobAssignment – this table lists which employees have been assigned to which job. It also specifies how many hours to date each employee has spent on that job.
• Invoice – this table lists invoices for jobs. One invoice is generated for each job some time after the job is marked as completed. A job may be completed on one day but the invoice might not be generated until a few days later. At this stage, an invoice only covers one job. Jobs that are not complete will not have an invoice.
• Payment – this table lists payments that have been made against invoices (if any).
Part A – SQL Queries
Using your database, write SQL queries to answer all the questions in this part. Each of the following questions has an information request followed by the expected results when your database has the given sample data.
Please note:
• Where a column has been renamed in the expected result, you must reproduce that name in your results. For example, if JobID appears as JobNbr in the expected result, then you must make this column name appear as JobNbr in your output.
• Simple queries are preferred. You may lose marks if your query is overly complex.
• You are not required to enforce row order on your queries unless specified.
• Be careful of duplicates; remove duplicates if necessary.
• You are not required to use one type of clause or construct (e.g. you are not required to use an explicit join over an implicit join) unless specifically instructed to do so in the question.
• Your SQL must continue to produce the correct answer even if the data changes. As an example, if asked to list employees over the age of 18, your query must always list employees who are over the age of 18 at the time that the query is executed. As time passes, the actual results would change as more employees age past 18 years.
Questions:
1. List all customers with their address order by customer name.
Expected result using sample data:
ID Customer Address
1 David Tennant 10 East St, Albion, QLD
2 Jodie Whitaker 20 West St, Breakfast Crk, QLD
5 Matt Smith 45 South Ave, Albion, QLD
3 Matt Smith 30 John Cres, Logan, QLD
4 Peter Capaldi 40 Smith Ave, Logan, QLD
2. List all jobs together with the name and mobile number of the customer who logged the job. Order by date logged.
Expected result using sample data:
JobNbr DateLogged Status FirstName LastName Mobile
1 17-Dec-19 Completed David Tennant 0400 123 456
2 20-Dec-19 Completed Jodie Whitaker
3 22-Dec-19 Completed Peter Capaldi
4 28-Dec-19 Completed David Tennant 0400 123 456
5 03-Jan-20 Completed Jodie Whitaker
7 05-Jan-20 Pending Peter Capaldi
6 05-Jan-20 In progress Jodie Whitaker
9 05-Feb-20 In progress David Tennant 0400 123 456
8 05-Feb-20 Completed David Tennant 0400 123 456
3. List customers who do not have any phone number listed (neither mobile nor telephone).
Expected result using sample data:
FirstName LastName Address Email Mobile Telephone
Peter Capaldi 40 Smith Ave p.Capaldi@bluebox.com.au
4. How many jobs are there for each status? Order the output so that the status with the most number of jobs appears at the top.
Expected result using sample data:
Status NbrJobs
Completed 6
In progress 2
Pending 1
5. For each completed job, how many hours in total were used?
Expected result using sample data:
ID JobDescription TotalHours
1 Fabricate and fit chequerplate toolbox to suit Toyota LC trayback ute 5
2 Repair crack in Bobcat bucket & load test 16
3 Fabricate steel shelving t/s commercial kitchen 6
4 Repair crack in aluminium tray Toyota LC 4
5 Fabricate roll cage t/s go kart 8
6. Which employees have never been assigned to a job?
Expected result using sample data:
ID Employee Email
5 Erin Johnson erin@century.com.au
7. List all invoices; show the total excluding GST, the GST amount, and the total including GST. Note: the existing InvoiceTotal is the Total including GST. You can calculate the total excluding GST by deducting the amount in the GST column. Order by the customer last name and then first name.
Expected result using sample data:
Customer Total excl GST GST Total incl GST
Capaldi, Peter $1,000.00 $100.00 $1,100.00
Tennant, David $100.00 $10.00 $110.00
Tennant, David $50.00 $5.00 $55.00
Tennant, David $500.00 $50.00 $550.00
Whitaker, Jodie $900.00 $90.00 $990.00
8. List all jobs for customer number 1 that are either:
a. completed and have a quoted cost $500, or
b. are in progress.
Expected result using sample data:
ID JobDescription QuotedCost Status CustomerID
1 Fabricate and fit chequerplate toolbox to suit Toyota LC trayback ute $600.00 Completed 1
9 Re-inforce swaybar Toyota LC $550.00 In progress 1
9. Which customers have more than $1000 worth of invoices in total? Use the InvoiceTotal column in your calculations.
Expected result using sample data:
FirstName LastName Expr1002
Peter Capaldi $1,100.00
10. List jobs where invoice total was more than the quoted cost.
Expected result using sample data:
JobNbr JobDescription QuotedCost InvoiceTotal
2 Repair crack in Bobcat bucket & load test $850.00 $990.00
3 Fabricate steel shelving t/s commercial kitchen $950.00 $1,100.00
11. List all invoices that are outstanding. You must be sure include those invoices which have no payments at all. Order by the invoice total descending. NOTE: you will need to research the Access function nz() – this function is used to replace a null with a 0. If you try to sum a column with nulls in it, the result will be a null.
Expected result using sample data:
FirstName LastName InvoiceNbr InvoiceDate GST InvoiceTotal TotalPayment
Peter Capaldi 3 10/01/2020 $100.00 $1,100.00 0
Jodie Whitaker 2 10/01/2020 $90.00 $990.00 600
David Tennant 5 7/02/2020 $10.00 $110.00 0
Part B – Form
Using the Century Engineering Job System database, develop a form which can be used for data entry for jobs. You are allowed to use any number of tables and any suitable layout for developing that form. Name the form as “Job management”.
Part C – Report
Using Century Engineering Job System database, create a report that lists jobs and all assignments to those jobs. A sample report for this part is shown below. Your report should
• Include a title, date, column headings and page numbering,
• closely resemble the sample report below; you may choose different colours/fonts, but the meaning of the report must be the same and it must be professional in appearance.
• Have column headings/names named appropriately; for example, do NOT leave your column headings as Employee.ID or QuotedCost; this would not be professional in appearance.
How and what to submit
You must make an electronic submission for this assessment using the appropriate assessment submission link on the unit website. Zip your Microsoft Access database file. Your Microsoft Access file must contain all tables, queries, form and report.
Assessment 1 marking guide:
Part Item Maximum marks Your marks Comments
A Q1 2
Q2 2
Q3 2
Q4 2
Q5 2
Q6 2
Q7 2
Q8 2
Q9 2
Q10 2
Q11 2
B Form can be used for data entry 4
C Report displays appropriate data. Appropriate heading provided. All data are visible and in the correct sorted order. 4
Late Penalty 5% of available marks per calendar day late
Plagiarism
Total 30
GET ANSWERS / LIVE CHAT