CS2855 Assessed Coursework 2
This assignment must be submitted via the anonymous submission script
Due date: Tue 3 Dec 2019 at 14:00
Learning outcomes assessed
This assignment covers basic SQL queries and definition, as well as functional
dependencies (FDs, for short), normalisation of relational databases
and (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. Customers
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 referentialintegrity
constraints that should hold (such as foreign keys), taking
care for attributes that should not be null.
2. Define queries in SQL to obtain the following information:
(a) The names of all products manufactured by ‘Samsung’, in ascending
ordered by price.
(b) The names of all products that had at least one rating of 3 or
more stars. No duplicate names should be returned.
(c) For all products, their name, average star rating, and most recent
rating date. The results should be in descending order of average
star rating.
(d) For all cases in which the same customer rated the same product
more than once, and in some point in time gave it a lower rating
2
than before, return the customer name, the name of the product,
and the lowest star rating that was given.
Part 2: Normalisation
Question 3. Let F be the following collection of functional dependencies
for relation schema R = (A, B, C, D, E):
(i) Compute three non-trivial new FDs in the closure F + of F (i.e., FDs
that are not already in F and also are not trivial). Describe briefly how
you computed these new FDs (namely, how you applied Armstrong’s
axioms, step by step, to computed them).
(ii) Provide at least one candidate key for R, and briefly explain how
you obtained it (namely, explain briefly why it is indeed a candidate
key).
Question 4. Let ebay prod vendor=(prod id, price, model num, vendor,
delivery price, storage) be a relation scheme. And let the following be
the FDs that hold on ebay prod vendor:
prod id → price model num
vendor storage → deliveray price
Decompose ebay prod vendor into a (set of) relational schemes via a
lossless-join decomposition, and explain why it is indeed a lossless-join decomposition
(namely, show briefly that the criterion for lossless-join decomposition
described in class holds on your relations).
Question 5. Let R = (A, B, C, D) be a relation schema and let F = {C →
D, C → A, B → C} be a set of FDs. Is R in Boyce-Codd normal form
(BCNF) with respect to F? If it is explain why, otherwise decompose R
into a set of schemas in BCNF.
Marking criteria
This coursework is assessed and mandatory and is worth 5% of your total
final grade for this course. Some marks will be given if the approach was
correct even if the final answer is not complete.