Background
Tradeline Furniture Wholesale is a Canadian-based company that supplies a broad range of high-quality furniture to retail outlets and institution customers. The company has built a solid reputation by offering diverse and affordable furniture collections sourced from renowned manufacturers. With its centralized operations and direct customer engagement strategy, Tradeline serves businesses and other organizations across the Greater Toronto Area (GTA) and beyond.

Inventory
Tradeline Furniture Wholesale offers ten distinct categories of furniture, catering to varying customer needs and preferences. These categories include living room sets, bedroom furniture, dining room collections, office furniture, outdoor pieces, and more. Each category contains a range of products with unique identification numbers and suggested retail prices.

Sales
To streamline customer service and maintain personalized relationships, Tradeline assigns every new customer to a dedicated salesperson. Salespersons are responsible for managing all transactions and interactions with their assigned group of customers. This structure ensures consistent communication and enables the sales team to build strong client relationships.
Salespersons also have the authority to negotiate deals on high-volume orders or offer discounts on slowermoving inventory. This flexibility helps optimize inventory turnover while rewarding customer loyalty and promoting long-term partnerships.
The commission rate for each salesperson is determined through a contractual arrangement, remaining consistent regardless of the items sold.

Payment Methods
Tradeline provides customers with flexible payment options to facilitate ease of transactions and encourage
JNTING \& TECHNOLOGY $\square$
臼
8
$A X$
AiXu

Assignments
Discussions
属
草
国
$\square$

Grades
Course Tools
promoung rong-term paruersmps.
The commission rate for each salesperson is determined through a contractual arrangement, remaining consistent regardless of the items sold.

Payment Methods
Tradeline provides customers with flexible payment options to facilitate ease of transactions and encourage repeat business. Clients can choose from three payment methods:
1. Immediate Payment: Full payment is made in cash at the time of purchase.
2. Installment Plan: Payments are made over a six-month period.
3. Deferred Payment: Payment is due by the last day of the following month.

This range of options accommodates different customer preferences and financial situations, fostering greater customer satisfaction and loyalty.

Cash Receipts
While the majority of Tradeline's cash receipts come from customer transactions, the company also receives funds from other sources, such as interest payments from banks or financial investments. Regardless of the source, each cash receipt is processed by a member of Tradeline's cashier team and deposited into one of the company's bank accounts by the end of the day on which the payment is received.

Other Information
Details about furniture, emplos
Page $1 / 3$ and $Q^{ ext {nk }}$
any related transactions. For sis
Page 1/3
antered into the database prior to
Y. HST) can be ignored.
INTING \& TECHNOLOGY
吅哭
-
AX
AiXu
-
白
$\square$
Grades
Course Tools
Issignments
Discussions

Other Information
Details about furniture, employees, customers, and bank accounts are typically entered into the database prior to any related transactions. For simplicity, sales discounts and Harmonized Sales Tax (HST) can be ignored.

Phase I Instructions

Based on the information given, prepare a UML class diagram for Tradeline Furniture with a MS Office application (e.g. PowerPoint, Word etc.). Use the REA model to help you determine the entities. Assign the attributes listed below to appropriate entities or many-to-many relationships.
\begin{tabular}{|c|l|c|l|c|l|}
\hline 1. & Sale_Inv_Num & 12. & Sale_Date & 23. & Suggested_Selling_Price \
\hline 2. & Qty_On_Hand & 13. & Customer_Num & 24. & Actual_Sale_Price \
\hline 3. & AR_Balance & 14. & Emp_Name & 25. & Inventory_Category \
\hline 4. & Customer_City & 15. & Customer_Name & 26. & Bank_Acct_Num \
\hline 5. & CR_Date & 16. & Inventory_Num & 27. & Commission_Rate \
\hline 6. & Emp_DOB & 17. & Bank_Balance & 28. & Sale_Amount \
\hline 7. & CR_Amount & 18. & CR_Num & 29. & Item_Description \
\hline 8. & Emp_SIN & 19. & Bank_Type & 30. & Emp_Num \
\hline 9. & Customer_Address & 20. & Item_Unit_Cost & 31. & Actual_Sale_Qty \
\hline 10. & Amount_Applied & 21. & Bank_Name & 32. & Cust_Postal_Code \
\hline 11. & Bank_Location & 22. & Emp_Position & & \
\hline
\end{tabular}

Notes:
a. CR: Cash Receipts
UNTING \& TECHNOLOGY
㧹品
마응
$\square$
甸
ి
Ax
$A i X_{u}$

Assignments

Discussions

Grades

Course Tools

Phase II Instructions
Part A: Implementing Your UML Data Model with Access ( 10 marks)
1) 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.
2) In your MS Access file, navigate to "Database Tools" and link all the tables.
3) Capture a screenshot with all the attributes of each table displayed clearly.
4) Create a new MS Word file, copy and paste the screenshot to the Word file.
5) Save the Access file.

Part B: Creating queries with MS Access: ( 30 marks)
1) Create a new MS Access file and save it as "Database Project_Last Name, First Name".
2) Import all Excel files provided in the zip file into the new MS Access file and establish all necessary relationships.
3) 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:
Q1. Which customers are located in St. Catharines?
Q2. How many customers are located outside of Ontario?
Q3. What is the name of the customer that purchased the highest amount of furniture in November?
Q4. Which inventory type (item) generated the highest amount of sales revenue in the $3^{ ext {rd }}$ quarter of 2023?
Q5. Based on the entire dataset, which salespersons earned more than $\$ 2,000$ commission?
Q6. Which inventory category generated the highest gross profit in June?
Q7. 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.
Q8. Which bank account(s) processed more than $\$ 10,000$ of deposits in the $2^{ ext {nd }}$ quarter of 2023 ?

Question

Background
Tradeline Furniture Wholesale is a Canadian-based company that supplies a broad range of high-quality furniture to retail outlets and institution customers. The company has built a solid reputation by offering diverse and affordable furniture collections sourced from renowned manufacturers. With its centralized operations and direct customer engagement strategy, Tradeline serves businesses and other organizations across the Greater Toronto Area (GTA) and beyond.

Inventory
Tradeline Furniture Wholesale offers ten distinct categories of furniture, catering to varying customer needs and preferences. These categories include living room sets, bedroom furniture, dining room collections, office furniture, outdoor pieces, and more. Each category contains a range of products with unique identification numbers and suggested retail prices.

Sales
To streamline customer service and maintain personalized relationships, Tradeline assigns every new customer to a dedicated salesperson. Salespersons are responsible for managing all transactions and interactions with their assigned group of customers. This structure ensures consistent communication and enables the sales team to build strong client relationships.
Salespersons also have the authority to negotiate deals on high-volume orders or offer discounts on slowermoving inventory. This flexibility helps optimize inventory turnover while rewarding customer loyalty and promoting long-term partnerships.
The commission rate for each salesperson is determined through a contractual arrangement, remaining consistent regardless of the items sold.

Payment Methods
Tradeline provides customers with flexible payment options to facilitate ease of transactions and encourage
JNTING \& TECHNOLOGY $\square$
臼
8
$A X$
AiXu

Assignments
Discussions
属
草
国
$\square$

Grades
Course Tools
promoung rong-term paruersmps.
The commission rate for each salesperson is determined through a contractual arrangement, remaining consistent regardless of the items sold.

Payment Methods
Tradeline provides customers with flexible payment options to facilitate ease of transactions and encourage repeat business. Clients can choose from three payment methods:
1. Immediate Payment: Full payment is made in cash at the time of purchase.
2. Installment Plan: Payments are made over a six-month period.
3. Deferred Payment: Payment is due by the last day of the following month.

This range of options accommodates different customer preferences and financial situations, fostering greater customer satisfaction and loyalty.

Cash Receipts
While the majority of Tradeline's cash receipts come from customer transactions, the company also receives funds from other sources, such as interest payments from banks or financial investments. Regardless of the source, each cash receipt is processed by a member of Tradeline's cashier team and deposited into one of the company's bank accounts by the end of the day on which the payment is received.

Other Information
Details about furniture, emplos
Page $1 / 3$ and $Q^{	ext {nk }}$
any related transactions. For sis
Page 1/3
antered into the database prior to
Y. HST) can be ignored.
INTING \& TECHNOLOGY
吅哭
-
AX
AiXu
-
白
$\square$
Grades
Course Tools
Issignments
Discussions

Other Information
Details about furniture, employees, customers, and bank accounts are typically entered into the database prior to any related transactions. For simplicity, sales discounts and Harmonized Sales Tax (HST) can be ignored.

Phase I Instructions

Based on the information given, prepare a UML class diagram for Tradeline Furniture with a MS Office application (e.g. PowerPoint, Word etc.). Use the REA model to help you determine the entities. Assign the attributes listed below to appropriate entities or many-to-many relationships.
\begin{tabular}{|c|l|c|l|c|l|}
\hline 1. & Sale_Inv_Num & 12. & Sale_Date & 23. & Suggested_Selling_Price \
\hline 2. & Qty_On_Hand & 13. & Customer_Num & 24. & Actual_Sale_Price \
\hline 3. & AR_Balance & 14. & Emp_Name & 25. & Inventory_Category \
\hline 4. & Customer_City & 15. & Customer_Name & 26. & Bank_Acct_Num \
\hline 5. & CR_Date & 16. & Inventory_Num & 27. & Commission_Rate \
\hline 6. & Emp_DOB & 17. & Bank_Balance & 28. & Sale_Amount \
\hline 7. & CR_Amount & 18. & CR_Num & 29. & Item_Description \
\hline 8. & Emp_SIN & 19. & Bank_Type & 30. & Emp_Num \
\hline 9. & Customer_Address & 20. & Item_Unit_Cost & 31. & Actual_Sale_Qty \
\hline 10. & Amount_Applied & 21. & Bank_Name & 32. & Cust_Postal_Code \
\hline 11. & Bank_Location & 22. & Emp_Position & & \
\hline
\end{tabular}

Notes:
a. CR: Cash Receipts
UNTING \& TECHNOLOGY
㧹品
마응
$\square$
甸
ి
Ax
$A i X_{u}$

Assignments

Discussions

Grades

Course Tools

Phase II Instructions
Part A: Implementing Your UML Data Model with Access ( 10 marks)
1) 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.
2) In your MS Access file, navigate to "Database Tools" and link all the tables.
3) Capture a screenshot with all the attributes of each table displayed clearly.
4) Create a new MS Word file, copy and paste the screenshot to the Word file.
5) Save the Access file.

Part B: Creating queries with MS Access: ( 30 marks)
1) Create a new MS Access file and save it as "Database Project_Last Name, First Name".
2) Import all Excel files provided in the zip file into the new MS Access file and establish all necessary relationships.
3) 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:
Q1. Which customers are located in St. Catharines?
Q2. How many customers are located outside of Ontario?
Q3. What is the name of the customer that purchased the highest amount of furniture in November?
Q4. Which inventory type (item) generated the highest amount of sales revenue in the $3^{	ext {rd }}$ quarter of 2023?
Q5. Based on the entire dataset, which salespersons earned more than $\$ 2,000$ commission?
Q6. Which inventory category generated the highest gross profit in June?
Q7. 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.
Q8. Which bank account(s) processed more than $\$ 10,000$ of deposits in the $2^{	ext {nd }}$ quarter of 2023 ?

Accepted Answer

It looks like you have a detailed description of Tradeline Furniture Wholesale and some instructions for a project involving UML class diagrams and database implementation. If you have specific questions about the content or need help with a particular aspect of the project, feel free to ask! Whether it's about creating the UML diagram, understanding the payment methods, or anything else related to the project, I'm here to help!

Sia