support@w4writers.com +44 7743 307695
Mar 31, 2023

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.

Recent Post

Order this Assignment now

Total: GBP120

fables template