Computer Assignment $1$ on Access Database

Part $1(60$ pts$)$ Create Queries

Queries a$,$ b$,$ c$,$ d$,$ e$,$ and f require Computer Assignment $1$ database file

a$)$ List the FirstName, LastName, and Phone Number of customers who cancelled their Furniture Orders. Furniture is a category in the Products Table $($Save the Query as Q$1).$

b$)$ Create a query that displays number of orders, total and average values of orders by Region Name. Value is a calculated field and is obtained by multiplying Price with Quantity $($Save the Query as Q$2).$

c$)$ Create a query that lists employee$$s first name, last name, salary, FICA, and Withholding. FICA and Withholding are calculated fields. FICA $=6\mathrm{.}75\%$ of Salary and Withholding $=20\%$ of Salary. Name FICA and Withholding as FICA and Withholding $($Save the Query as Q$3).$

d$)$ List the first name, and last name of customers where the third character in the last name is e $($Save the Q$4).$

e$)$ Create a parameter query to list employees$$ first name, last name, salary, and date hired by Department. Department name to be entered at run time $($Save the Query as Q$5).$

f$)$ List First Name, Last Name, and Salary of employees with a job rating of $<=2$ OR making more than $$88,000($Save the Query as Q$6).$ Sort the salary in Ascending order.

Part $2(40$ pts$)$ Creating Tables

Create two tables, DEPARTMENT and WORKER in Computer Assignment $1$ database file.

DEPARTMNENT table contains the following fields:

DeptID, DeptName, and NumberOfWorkers.

WORKER table contains the following fields:

WorkerID, WorkerName, Date$\_$Hired, Salary, and DeptID. DeptID is a foreign key.

$$ Define Primary keys for the two table

$$ Enforce referential integrity between them.

$$ Use appropriate data type for the fields.

Rules

i$)$ DeptId is a Text field, size $3$ bytes. First two characters are upper case letters, and third character is a digit.

ii$)$ Salary cannot be greater than $$100,000.$

iii$)$ Date$\_$Hired must be less than the current date

iv$)$ WorkerName cannot be blank