Database & SQL

You are working on a transactional database project for e-commerce The client wishes to maintain records on product returns.Marketing Research and Data Analysis

Management has requested that the following information is maintained in the database:

Per Return: return date; payment method; products included in the return, amount of each product per return, the purchase price of each product per return, the motive for return of each product; information about the Customer who made the return
Per Product: product SKU (Stock Keeping Unit); product description; brand
Per Customer: name, address, date of birth, telephone number and email.

a. Provide an Entity-Relationship Diagram (ERD) with Chen Style to describe the scenario provided. Show entities, attributes, and relationships, including cardinality and ordinality. Provide explanations for any relevant assumptions

b. Provide the physical database tables involved in the relationship between Products and Returns. Show the keys. Explain your solution.

c. Provide the sample SQL code to insert some plausible data in a table for “Return”, respecting data types and constraints.

d. Provide the sample SQL code to display how many returns a customer (ID = 10) has made that were paid with “Credit Card”.