Please submit SQL statements as text file. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle.
Problem 1. Please create the following tables with appropriate primary keys & foreign keys.
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.
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