You are working on a transactional database project for e-commerce The client wishes to maintain records on product returns: Database & SQL Assignment, UCD, Ireland

1. You are working on a transactional database project for e-commerce The client wishes to maintain records on product returns.

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”.

2. Consider a database system to store student grades per modules taken. Below is a partial Relational Schema:

Provide the SQL code to solve the following problems:

• In a single query, list all the modules (title) taken by each student (firstname, surname) and the grade obtained, excluding students without any module enrolments and modules without students.
• Create an SQL View to list all the lecturer (firstname, surname) module grade averages, i.e. the average of grades attributed by each lecturer per module they
• Create a trigger to check if, before inserting a new lecturer salary, the value is not negative, and if it is negative, set it to zero.
• Is the Relational Schema provided in this question in the Third Normal Form? Why?