University Database Design and Implementation - SQL, Forms, and Report

This assignment focuses on database design and implementation using Microsoft Access. It includes a series of SQL queries to extract specific information from the database, such as details of government agencies, lab technicians and their qualifications, appointment data, and test results. The solution provides the SQL code for each query and presents the results. The assignment also includes a query analysis section, where the logic and expected results of specific queries are examined. Additionally, the assignment involves creating a form and a report within Access to display and summarize the data. The provided solution demonstrates the practical application of database concepts, including querying, data retrieval, and report generation. The queries cover various aspects of the database, including data aggregation, filtering, and joining multiple tables to retrieve the required information. The analysis section provides insights into the query logic and expected output. The form and report showcase how to present the database data in a user-friendly manner. The assignment demonstrates the ability to design, implement, and manage a database effectively.

Running head: DATABASE DESIGN AND IMPLEMEMTATION
Database Design and Implementation
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN AND IMPLEMEMTATION
Table of Contents
Part A – SQL Queries......................................................................................................................2
Query 1: 1. List the Government Agency ID, Name, Contact number for all Government
agencies, as shown below. A single column result is required...................................................2
Query 2: Display the details of all the Lab Technicians and their qualifications........................2
Query 3: Display the Technician names and the number of appointments attended by each
Technician in April......................................................................................................................3
Query 4: List the details of Lab test(s) that were never performed in any of the appointments
till date and time..........................................................................................................................4
Query 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...............................................................4
Query 6: Present the summary of ‘samples’ sent by each Government Agency.........................5
Query 7: Display the details of Lab Technicians who have attended more than one
appointment.................................................................................................................................6
Query 8: Display the details of tests that were never performed in any of the appointments and
also contain the word ‘Advanced’ in its name.............................................................................7
Query 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.......................................................................................7
Query 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............................8
Query 11: List, alphabetically, the number of “appointments” for each and every test..............8
Part B – Query Analysis..................................................................................................................9
Query 10......................................................................................................................................9
Query 11....................................................................................................................................10
Part C – Form.................................................................................................................................12
Part D – Report..............................................................................................................................12

2
DATABASE DESIGN AND IMPLEMEMTATION
Bibliography..................................................................................................................................14

3
DATABASE DESIGN AND IMPLEMEMTATION
Part A – SQL Queries
Query 1: 1. List the Government Agency ID, Name, Contact number for all
Government agencies, as shown below. A single column result is required.
SELECT (Gov_Agency.Gov_Agency_ID & "- Name: " & Gov_Agency.Gov_Agency_Name &
" - Contact Number: " & Gov_Agency.Gov_Agency_Contact) AS [Government Agency Details]
FROM Gov_Agency;
Figure 1: Query 1
Source: (created by author)
Query 2: Display the details of all the Lab Technicians and their qualifications.
SELECT Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Lab_Technician.Contact_Number, Lab_Tech_Quals.Qualification
FROM Lab_Technician INNER JOIN Lab_Tech_Quals ON Lab_Technician.LabTechnician_ID
= Lab_Tech_Quals.Lab_Technician_ID;

4
DATABASE DESIGN AND IMPLEMEMTATION
Figure 2: Query 2
Source: (created by author)
Query 3: Display the Technician names and the number of appointments attended
by each Technician in April.
SELECT Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Count(LabAppointment.Appointment_No) AS [Total Number of Appointments]
FROM Lab_Technician INNER JOIN LabAppointment ON Lab_Technician.LabTechnician_ID
= LabAppointment.Lab_Tech_ID
GROUP BY Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Month([App_Date])
HAVING (((Month([App_Date]))=4));
Figure 3: Query 3
Source: (created by author)

5
DATABASE DESIGN AND IMPLEMEMTATION
Query 4: List the details of Lab test(s) that were never performed in any of the
appointments till date and time.
SELECT Lab_Tests.Test_ID, Lab_Tests.Test_name, Lab_Tests.Description, Lab_Tests.Cost
FROM Lab_Tests
WHERE (((Lab_Tests.Test_ID) Not In (Select Test_ID from LabTest_Appointment)));
Figure 4: Query 4
Source: (created by author)
Query 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.
SELECT Lab_Tests.Test_ID, LabAppointment.Appointment_No, LabAppointment.App_Date,
LabAppointment.App_Time, Lab_Technician.LabTechnician_ID
FROM (Lab_Technician INNER JOIN LabAppointment ON Lab_Technician.LabTechnician_ID
= LabAppointment.Lab_Tech_ID) INNER JOIN (Lab_Tests INNER JOIN
LabTest_Appointment ON Lab_Tests.Test_ID = LabTest_Appointment.Test_ID) ON
LabAppointment.Appointment_No = LabTest_Appointment.Appoint_No
ORDER BY Lab_Tests.Test_ID;

6
DATABASE DESIGN AND IMPLEMEMTATION
Figure 5: Query 5
Source: (created by author)
Query 6: Present the summary of ‘samples’ sent by each Government Agency.
SELECT Gov_Agency.Gov_Agency_ID, Gov_Agency.Gov_Agency_Name,
Count(Sample.Sample_ID) AS [Number of samples sent]
FROM Sample INNER JOIN (LabAppointment INNER JOIN (Gov_Agency INNER JOIN
Gov_Agency_Referral ON Gov_Agency.Gov_Agency_ID =
Gov_Agency_Referral.Gov_Agency_ID) ON LabAppointment.Appointment_No =
Gov_Agency_Referral.Appointment_No) ON Sample.Sample_ID =
LabAppointment.Sample_ID
GROUP BY Gov_Agency.Gov_Agency_ID, Gov_Agency.Gov_Agency_Name;

7
DATABASE DESIGN AND IMPLEMEMTATION
Figure 6: Query 6
Source: (created by author)
Query 7: Display the details of Lab Technicians who have attended more than one
appointment.
SELECT Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Lab_Technician.Contact_Number
FROM Lab_Technician INNER JOIN LabAppointment ON Lab_Technician.LabTechnician_ID
= LabAppointment.Lab_Tech_ID
GROUP BY Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Lab_Technician.Contact_Number
HAVING (((Count(LabAppointment.Appointment_No))>1));
Figure 7: Query 7

8
DATABASE DESIGN AND IMPLEMEMTATION
Source: (created by author)
Query 8: Display the details of tests that were never performed in any of the
appointments and also contain the word ‘Advanced’ in its name
SELECT Query4.Test_ID, Query4.Test_name, Query4.Description, Query4.Cost
FROM Query4
WHERE (((Query4.Test_name) Like "*Advanced*"));
Figure 8: Query 8
Source: (created by author)
Query 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
SELECT LabAppointment.Appointment_No, LabAppointment.App_Date,
Lab_Tests.Test_name, Lab_Technician.LabTechnician_Name, Sum(Lab_Tests.Cost) AS
Total_Cost
FROM Lab_Technician INNER JOIN (Lab_Tests INNER JOIN (LabAppointment INNER JOIN
LabTest_Appointment ON LabAppointment.Appointment_No =
LabTest_Appointment.Appoint_No) ON Lab_Tests.Test_ID = LabTest_Appointment.Test_ID)
ON Lab_Technician.LabTechnician_ID = LabAppointment.Lab_Tech_ID
GROUP BY LabAppointment.Appointment_No, LabAppointment.App_Date,
Lab_Tests.Test_name, Lab_Technician.LabTechnician_Name, Month([App_Date])
HAVING (((Month([App_Date]))=2)) OR (((Month([App_Date]))=3));

9
DATABASE DESIGN AND IMPLEMEMTATION
Figure 9: Query 9
Source: (created by author)
Query 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.
SELECT TOP 1 Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Lab_Technician.Contact_Number, Count(Lab_Tech_Quals.Qualification) AS
NumberOfQualification
FROM Lab_Technician INNER JOIN Lab_Tech_Quals ON Lab_Technician.LabTechnician_ID
= Lab_Tech_Quals.Lab_Technician_ID
GROUP BY Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Lab_Technician.Contact_Number
ORDER BY Count(Lab_Tech_Quals.Qualification) DESC;
Figure 10: Query 10
Source: (created by author)

10
DATABASE DESIGN AND IMPLEMEMTATION
Query 11: List, alphabetically, the number of “appointments” for each and every
test.
SELECT Lab_Tests.Test_ID, Lab_Tests.Test_name, Lab_Tests.Description,
Count(LabTest_Appointment.Appoint_No) AS NumberOfAppointments
FROM Lab_Tests INNER JOIN LabTest_Appointment ON Lab_Tests.Test_ID =
LabTest_Appointment.Test_ID
GROUP BY Lab_Tests.Test_ID, Lab_Tests.Test_name, Lab_Tests.Description;
Figure 11: Query 11
Source: (created by author)
Part B – Query Analysis
Query 10
For the analysis of the query we start with the expected result from the query. The
expected result for the query to display the technician with the most qualifications is provided
below:
Lab Technician ID Name Contact Number Total Number of Qualifications
108 Tony Stark 94025462 5
The query was required to display the Present Lab Technician ID, Name, Contact
Number, Total Number of Qualifications. We have also proceeded with same group for the
attributes for the query. The query used for this task was SELECT TOP 1
Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,

11
DATABASE DESIGN AND IMPLEMEMTATION
Lab_Technician.Contact_Number, Count(Lab_Tech_Quals.Qualification) AS
NumberOfQualification
FROM Lab_Technician INNER JOIN Lab_Tech_Quals ON Lab_Technician.LabTechnician_ID
= Lab_Tech_Quals.Lab_Technician_ID
GROUP BY Lab_Technician.LabTechnician_ID, Lab_Technician.LabTechnician_Name,
Lab_Technician.Contact_Number
ORDER BY Count(Lab_Tech_Quals.Qualification) DESC;
The Present Lab Technician ID, Name, Contact Number, Total Number of Qualifications
were used for the selection query at first. After the selection was complete the results were sorted
in the Descending order. The selection TOP 1 was done after the ordering of the query. The final
result obtained after performing the query is displayed in figure number 10.
Query 11
For the analysis of this query we start with the expected result from the query. The
expected result for the query to display the technician with the most qualifications is provided
below: