Problem 1. Please create the following.

Problem 1. Please create the following tables with appropriate primary keys & foreign keys. [30 points] The list of tables is: – Users table with 3 columns: userid (user id), username, password; – Category table with 2 columns: cid (category id), cname (category name, for example, TV, cell phone, laptop). – Product table with 5 columns: pid (product id), cid (category id),pname (product name), brand (brand of product), price. – Feature table with 2 columns: fid (feature id), fname (feature name). E.g., screen size, weight, memory size, hardisk size. – Product_feature table with 3 columns: pid (product id), fid (feature id), fvalue (feature value), you can assume that feature values are numerical – Review table with 6 columns: rid (review id), userid (user id), pid (product id), rdate (review date), score (review score from 1 to 5), rcomment (review comments) Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints. [20 points] Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. [50 points, 10 points each] Task 1: Return the total number of reviews Task 2: Return all reviews submitted after 2016-5-1. Task 3: Return product names under the category TV. Task 4: return the product name and average score. Task 5: Return the names of product under the category TV and with average rating above 4.0

Leave a Reply

Your email address will not be published. Required fields are marked *