Assignment title: Information
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