Learning outcomes assessed
This assignment covers basic SQL queries and definition, as well as functional dependencies (FDs, for short), normalisation of relational data base sand (lossless-join) decomposition of tables.
The assessed learning outcomes are implementing a relational model in
SQL and developing queries of different complexity, and understanding of
the basic notions and applications of table normalisation.

Instructions
You must submit two files:
1.For the SQL part (Part 1), you must submit a text file named“queries. sql” that contains the SQL queries in order. Comment lines can be added to the file as lines beginning with the double dash (“- -”) symbol.
2.For the normalisation part (Part 2), submit a PDF file, with a clearly
written solution. Solutions that are presented in an unclear format
(i.e., not a PDF), low-resolution pictures, or haphazard handwriting
will not be marked (since we will not be able to asses their correctness).

NOTE: All the work you submit should be solely your own work. Coursework submissions are routinely checked for this.

Assignment Part 1: SQL
Consider the following relational model that represents part of an online
shop’s database:
product(prod id, product name, product price, product manufacturer)
customer(cust id, customer name, customer address)

rating(prod id, cust id, rating date, rating stars)
Foreign Key: prod id references prod id in product table
Foreign Key: cust id references cust id in customer table

sale(sale id, prod id, cust id, sale quantity, sale cost)
Foreign Key: prod id references prod id in product table
Foreign Key: cust id references cust id in customer table

These schemas represent product information, including sales. Cus-
tomers can provide ratings for products in a 0 to 5 stars fashion. It is possible for clients to rate the same product more than once.

1.Give an SQL definition of this database, including the referential-
integrity constraints that should hold (such as foreign keys), taking care for attributes that should not be null.