we need to write the codes and get the answers as shown in the tables in word file. And write the methodology used in solving Q10 and Q11.
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 have to complete all the following parts of this assessment taks:
A. SQL Queries
B. Query Analysis
C. Form
D. Report
Important
Download the Microsoft Access database, Chemical_Composition_Analysis.mdb, 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 need to write 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 Q1, Q2, ......, 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 C and Part D and save them in your database.

Chemical Composition Analysis.accdb
The database, Chemical_Composition_Analysis.mdb, contains tables that can store the data related to the appointments made by the clients for analysing the chemical composition of materials collected by the government agencies. Tests are conducted on materials only when referred by the government agencies. Tests are conducted by the Lab technicians, who have expertise in various chemical tests. In order for the tests to be conducted, the employee of the respective government agency needs to make an appointment with a particular lab technician. Only one material/sample is tested by the assigned lab technician in that appointment. If there is more than one sample, then tests are carried out only when there are subsequent appointments. As per the testing process, similar tests may be conducted on multiple samples in different appointments.
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.
Hint: To get the desired output, you need to check the column headings; grouping and sorting of data displayed; removal of duplicate data; and other aspects of the query.
1. List the Government Agency ID, Name, Contact number for all Government agencies, as shown below. A single column result is required.
Expected result using sample data:
Government Agency Details
501- Name: Vic-Police- Contact Number: 66010
502- Name: Federal agency- Contact Number: 66600
503- Name: Vic-Health- Contact Number: 66601
504- Name: Qld-Police- Contact Number: 66602
505- Name: Qld-Health- Contact Number: 66603
506- Name: SA-Police- Contact Number: 66604
507- Name: SA-Health- Contact Number: 66605
508- Name: ACT-Police- Contact Number: 66606
509- Name: ACT-Health- Contact Number: 66607
510- Name: WA-Police- Contact Number: 66608
511- Name: WA-Health- Contact Number: 66609
512- Name: NT-Police- Contact Number: 66611
513- Name: NT-Health- Contact Number: 66612
514- Name: Tas-Police- Contact Number: 66613
515- Name: Tas-Health- Contact Number: 66614
2. Display the details of all the Lab Technicians and their qualifications.
Expected result using sample data:
Technician ID Name Contact Qualification
106 Adam Williams 94025464 Bachelor of Biomedicine
107 Eve Stark 94025463 Bachelor of Chemical Engineering
101 Kevin Peterson 94025467 Lab Technician Certificate
101 Kevin Peterson 94025467 Bachelor Biomedicine
102 Michael Clarke 94025468 Bachelor of Chemical Engineering
102 Michael Clarke 94025468 Lab Technician Certificate
103 Ricky Ponting 94025469 Bachelor of Science
103 Ricky Ponting 94025469 Certificate IV
104 Sally Jones 94025466 Certificate IV
104 Sally Jones 94025466 Bachelor of Chemical Engineering
108 Tony Stark 94025462 Master of Science
108 Tony Stark 94025462 Bachelor of Science
108 Tony Stark 94025462 Certificate IV
108 Tony Stark 94025462 Diploma in IT
108 Tony Stark 94025462 Lab Technician Certificate
105 Victoria Lannister 94025465 Bachelor of Science
3. Display the Technician names and the number of appointments attended by each Technician in April.
Expected result using sample data:
Technician ID Name Total no of appointments
108 Tony Stark 1
107 Eve Stark 1
106 Adam Williams 1
105 Victoria Lannister 1
104 Sally Jones 1
101 Kevin Peterson 1
4. List the details of Lab test(s) that were never performed in any of the appointments till date and time.
Expected result using sample data:
Test_ID Test_name Description Cost
9 Advanced confirmation Analysis It includes various additional tests 9000
10 Advanced Trace Analysis This is an advanced Trace analysis and consumes significant amount of time 10000
11 Advanced Petroleum Analysis Involves additional tests 15000
5. Present the details of all lab tests that have been performed in the appointments till now. Display the results in ascending order of Test ID.
Expected result using sample data:
Test ID Appointment ID Appointment Date Appointment Time Lab Technician
1 309 26/04/2019 15.00 101
1 316 1/07/2019 10.00 108
1 301 22/02/2019 10.00 101
2 302 7/03/2019 11.00 102
2 310 1/05/2019 9.00 102
2 317 2/07/2019 9.00 107
3 311 2/05/2019 10.00 103
3 318 3/07/2019 10.00 106
3 303 21/03/2019 10.00 103
4 312 3/05/2019 12.00 104
4 304 2/04/2019 9.00 104
5 313 3/06/2019 13.00 105
5 305 16/04/2019 10.00 105
6 314 4/06/2019 11.00 106
6 306 18/04/2019 10.00 106
7 315 5/06/2019 9.00 107
7 307 22/04/2019 9.00 107
8 308 24/04/2019 11.00 108
6. Present the summary of ‘samples’ sent by each Government Agency.
Expected result using sample data:
Gov_Agency_ID Gov_Agency_Name No of samples sent
501 Vic-Police 1
502 Federal agency 1
503 Vic-Health 1
504 Qld-Police 1
505 Qld-Health 1
506 SA-Police 1
507 SA-Health 1
508 ACT-Police 1
509 ACT-Health 1
510 WA-Police 1
511 WA-Health 1
512 NT-Police 1
513 NT-Health 2
514 Tas-Police 2
515 Tas-Health 2
7. Display the details of Lab Technicians who have attended more than one appointment.
Expected result using sample data:
LabTechnician_ID LabTechnician_Name Contact_Number
101 Kevin Peterson 94025467
102 Michael Clarke 94025468
103 Ricky Ponting 94025469
104 Sally Jones 94025466
105 Victoria Lannister 94025465
106 Adam Williams 94025464
107 Eve Stark 94025463
108 Tony Stark 94025462
8. Display the details of tests that were never performed in any of the appointments and also contain the word ‘Advanced’ in its name.
Expected result using sample data:
Test ID Name Description Cost
9 Advanced confirmation Analysis It includes various additional tests 9000
10 Advanced Trace Analysis This is an advanced Trace analysis and consumes significant amount of time 10000
11 Advanced Petroleum Analysis Involves additional tests 15000
9. Display the Appointment ID, Appointment Date, Test Name, Technician name, Total cost for the tests conducted in the months of February and March. Display the results in descending order of Appointment Date.
Hint: Total charges of an appointment are calculated as the sum of the tests’ costs conducted in that appointment.
Expected result using sample data:
Appointment ID Appointment Date Test Name Test conducted by Total Cost for the Test
303 21/03/2019 Materials Analysis and Testing Ricky Ponting 5000
302 7/03/2019 Element Trace Analysis Michael Clarke 6000
301 22/02/2019 Chemical Trace Analysis Kevin Peterson 5000
10. Display the Lab Technician with most qualifications. Present Lab Technician ID, Name, Contact Number, Total Number of Qualifications (i.e., most number of qualifications)? Display the results in descending order of the total number of qualifications.
Expected result using sample data:
Lab Technician ID Name Contact Number Total Number of Qualifications
108 Tony Stark 94025462 5
11. List, alphabetically, the number of “appointments” for each and every test.
Hint: Use “Union”.
Test ID Test_Name Description Nbr of Appointments
1 Chemical Trace Analysis Chemical residue analysis studies include the identification of unknown substances, diagnosing and resolving contamination incidents and troubleshooting to rapidly solve problems. We deploy a wide range of identification techniques for residue trace analysis. 3
2 Element Trace Analysis Elemental analysis and testing include identification and quantification of elements in a sample, determination of the elemental composition, and trace level elements. 3
3 Materials Analysis and Testing Materials analysis services evaluate material quality and provide the necessary insight to improve performance and resolve failure or contamination issues. Materials such as polymers, plastics, composites, metals, alloys, ceramics, paper and board have diverse properties that impact performance. 3
4 Petroleum Analysis Petroleum analysis capabilities include trace analysis, purity and component testing, quality control testing, detailed hydrocarbon analysis, contamination identification and much more. 2
5 Mineral Assay and Testing Provide sample preparation facilities and a wide range of Total Quality Assurance mineral testing services 2
6 Reverse Engineering and Formulation Reverse engineering of a chemical formulation provides specific information about the composition of formulated products and how these components interact 2
7 Failure Analysis Laboratory Failure analysis and investigation can determine the root cause of failure should your product, component or asset fail or not perform as expected. 2
8 Confirmation Analysis Contamination testing, detection, identification and analysis are crucial steps towards resolving suspected contamination of products and processes. 1
9 Advanced confirmation Analysis It includes various additional tests 0
10 Advanced Trace Analysis This is an advanced Trace analysis and consumes significant amount of time 0
11 Advanced Petroleum Analysis Involves additional tests 0
Part B – Query Analysis
Explain the methodology of the SQL Query that you used to answer any one of the following queries:
• Q10
• Q11
Part C – Form
Using _Composition_Analysis.accdb database, develop a form which can be used for data entry for appointments. You are allowed to use any number of tables and any suitable layout for developing that form. Name the form as “Lab Appointments”.
Part D – Report
Using _Composition_Analysis.accdb database, create a report that provides details relating to appointments and the tests conducted during the appointment. Your report should include a page heading with the date, column headings, report totals, and page numbering. A sample report for this part is shown below; your report should closely resemble this:
How and what to submit
You must make an electronic submission for this assessment using the appropriate assessment submission link on the unit website. Your submission must contain the following files in a single ZIP file:
1. A Microsoft Access file containing all tables, queries, form and report relevant to Part A, Part C and Part D.
2. A Microsoft Word document containing your answer to Part B of this assessment.
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 Methodology explained appropriately 2
C Form can be used for data entry 3
D Report displays appropriate data. Appropriate heading provided. All data are visible and in the correct sorted order. 3
Late Penalty 5% of available marks per calendar day late
Plagiarism
Total 30