Part A: Implementing Your UML Data Model with Access $(10$ marks$)$

Based on the UML class diagram you created in Phase I, construct a database using MS Access.

a$.$ Create a table for each class and for each many$-$to$-$many relationship.

b$.$ Establish appropriate relationships among the tables using foreign keys.

In your MS Access file, navigate to "Database Tools" and link all the tables.

Capture a screenshot with all the attributes of each table displayed clearly.

Create a new MS Word file, copy and paste the screenshot to the Word file.

Save the Access file.

Part B: Creating queries with MS Access: $(30$ marks$)$

Create a new MS Access file and save it as "Database Project$\_$Last Name, First Name".

Import all Excel files provided in the zip file into the new MS Access file and establish all necessary

relationships.

In the new MS Access file, use either the MS Access query design tools or SQL to create a query for each of

the following questions:

Q$1.$ Which customers are located in St$.$ Catharines?

Q$2.$ How many customers are located outside of Ontario?

Q$3.$ What is the name of the customer that purchased the highest amount of furniture in November?

Q$4.$ Which inventory type $($item$)$ generated the highest amount of sales revenue in the $3^("$rd $")$ quarter of $2023?$

Q$5.$ Based on the entire dataset, which salespersons earned more than $$2,000$ commission?

Q$6.$ Which inventory category generated the highest gross profit in June?

Q$7.$ Which sales invoices had an outstanding balance as of September $30,2023?$ Include the invoice

number, invoice amount, amount of cash received, and outstanding balance for each invoice in your

output.

Q$8.$ Which bank account$($s$)$ processed more than $$10,000$ of deposits in the $2^("$nd $")$ quarter of $2023?$

Q$9.$ Which customer spent the most on Beds in May?

Q$10.$ Based on the entire dataset, who are the top three most valuable customers in terms of the gross

profits earned? Resources

Events

Agents $4)$ For each of the above TEN queries, run the query and take a screenshot of the query result.

$5)$ Copy and paste the TEN screenshots to the MS Word file you created in Part A$.$

$6)$ Provide your answers to the above TEN questions in the Word file.