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.
CS2855 Assessed Coursework 2 Assignment-UK
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.
CS2855 Assessed Coursework 2 Assignment-UK
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 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):
D → A
BA → C
C → E
E → DB
(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
CS2855 Assessed Coursework 2 Assignment-UK
Decompose ebay prod vendor into a (set of) relational schemes via a lossless-join decomposition, and explain why it is indeed a lossless-join de-
composition (namely, show briefly that the criterion for lossless-join decom-
position 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.
ORDER This CS2855 Assessed Coursework 2 Assignment NOW And Get Instant Discount
Read More :-
6EC501 CW1 Coursework CW1 Part 2 Assignment-Derby University UK.
