Assessment item 1

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.

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: 

Display the details of all the Lab Technicians and their qualifications.

Expected result using sample data:

Display the Technician names and the number of appointments attended by each Technician in April. 

Expected result using sample data:

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:

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:

Present the summary of ‘samples’ sent by each Government Agency. 

Expected result using sample data:

1. Display the details of Lab Technicians who have attended more than one appointment.

Expected result using sample data:

1. 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: