Entity: PRODUCT Prod Code (Primary Key): A unique identifier for each product. Prod Descript: Description of the product. Prod_Price: Price of the product. Prod_On_Hand: Quantity of the product available. Vend_Code (Foreign Key): A reference to the VENDOR entity to identify the vendor of the product.

Entity: VENDOR Vend Code (Primary Key): A unique identifier for each vendor. Vend_Contact: Contact information for the vendor. Vend_AreaCode: Area code of the vendor's location. Vend_Phone: Phone number of the vendor.

Entity: SALES Sale_ID (Primary Key): A unique identifier for each sale. Sale_Date: The date on which the sale occurred. Sale_Amount: The total amount of the sale. Sale_Type: Indicates the type of sale (physical retail store, online, trade center).

Entity: CUSTOMER Cust_ID (Primary Key): A unique identifier for each customer. Cust_Name: The name of the customer. Cust_Address: The address of the customer. Cust_Type: Indicates whether the customer is a business or a consumer.

Entity: PAYMENT Payment_ID (Primary Key): A unique identifier for each payment. Payment_Method: The method of payment used for the sale (cash, debit card, credit card, DigPay).

Entity: ORDER Order_ID (Primary Key): A unique identifier for each order. Order_Date: The date the order was placed. Order_Status: Indicates the status of the order (regular, expedited). Order_Quantity: The quantity of paint ordered. Prod_Code (Foreign Key): A reference to the PRODUCT entity, identifying the product in the order. Cust_ID (Foreign Key): A reference to the CUSTOMER entity, identifying the customer who placed the order.

Entity: COMPLAINT Complaint_ID (Primary Key): A unique identifier for each complaint. Complaint_Description: Description of the complaint. Complaint_Rating: Rating provided by the customer (on a 5-star scale).

Entity: MARKETING Marketing_ID (Primary Key): A unique identifier for each marketing campaign. Marketing_Description: Description of the marketing campaign. Marketing_Employee: Employee responsible for the campaign. Marketing_Emails: A list of emails of previous customers.

Based on these entities and these findings, how can a relational model/relational database schema be constructed using this notation as a reference:

and how can the final model be described in continuous text + what are any assumptions made? (diagram in draw.io or something equivalent)

This is the case incase needed:

Diplo Paints (DP) is an Australian business selling high-end quality paints across the country. A household name, the company is a popular retailer of paints for both businesses (such as painters and designers) and consumers. They sell over six hundred colour variations, including primary and secondary colours, and tertiary colours made from a combination of pigments (mixed in exact proportions to the nearest mililitre (mL). There are three ways that the company makes sales - through its physical retail stores, via its website, and through trade centres (essentially marketplaces for businesses to connect and trade)). 1. Physical retail stores These stores are established in all states of Australia and New Zealand. Purchases made in store by consumers can be made using cash, debit, or credit card. The company also offers their own 'buy now, pay later' option, RirPay, which allows consumers to pay off their purchase in four monthly payments (up to a maximum value of $1,000 ). Small quantities of paint can be purchased in store; larger purchases (over 5L) require placing an online order (similar to shopping online). 2. Online website The online sales component allows consumers (but not businesses) to purchase large small amounts of paint delivered via two shipping options (regular and expedited). The same in-store purchase options are available through the website. Customers must first create an account to place an order, by recording personal details such as name, address, etc. 3. Trade centres Trade centres are monthly markets for businesses to buy products from other businesses. Goods are usually purchased on credit, with the full amount payable up to 90 days after settlement. These sales are usually large deals, with a Diplo sales representative responsible for making the sale. Since Diplo Paints offer a wide range of different paint products, it is impossible for the company to keep an appropriate amount on hand to sell. Instead, volumes of paint, delivered by various international suppliers, are stored in distribution centres and mixed onsite. At the same time, whenever a sale is made an order is created. Sometimes more than one order is created (e.e, when a large purchase is made, paints are often shipped in multiple orders). Each order is then shipped to the customer in either a 'regular', or 'expedited' status (depending on which option the customer opted for). Occasionally, an order is mixed incorrectly. In these cases, a consumer interacts with the Complaints team to resolve the problem. A record of the complaint is held, and the customer is afterwards asked to rate their experience on a 5 -star scale. At times, the Diplo Marketing team will conduct mass-communication marketing campaigns including all previous customers, informing them of special promotions or deals. For this purpose, the marketing team relies on a list of e-mails of previous customers. An employee can be part of both the Complaints and Marketing team. The special promotion or deal usually involves offering customers a promotional code which will give them a certain percentage off their purchase (provided the terms and conditions of the deal are met - these could be a minimum order quantity private shipping a minimum order value). Although the business has been operating successfully for years, a recent audit revealed the finding that: - Costs could not be accurately allocated: e. the business was aware of $1.5m paid to the trucking compary responsible for delivering paints to online customers, but there was no data relating to the breakdown of this amount. Fixing this is a priority for the company. Furthermore, there were long-standing problems that have caused issues in the past, including: - Poor data governance: the company often relied solely on paper records. There was also an incident in 2021 that resulted in an employee spilling paint on tax records for the previous year. - Strategy and marketing deficiencies: these teams were not sure which paints and/or colours were the best sellers, which products to advertise in different states, or target to different markets. In this light, the company has approached you to advise them on the best way forwarb They have heard of databases and are open to investing in a new information system to improve on current performance