DATABASE MANAGEMENT
Part II: SQL Queries (50 marks) Consider the following tables which model certain features of an online shop, allowing users to purchase Page 1 of 3 Database Management 2021 14/02/2021, 16:45 items and rate them. The online shop has a number of users, each of whom has an identifier ('id') and a name specified. The shop sells a number of items, each with an identifier ('id'), a description, a single category under which it is classified, and a current price. Users can buy items from the shop, with the price paid recorded for each purchase (the current price of an item can vary from one day to the next). Price should be a decimal number, allowing for two digits to the right of the decimal point. Users can also rate items sold by the shop. They do this by specifying a rating, an integer between 1 and 5, for an item. Note that 'price' in table 'item' is the current price of the item, 'price' in table 'buys' is the price the user paid for the item, and 'user' and 'item' in the tables 'buys' and 'rates' correspond to the identifiers of users and items, respectively. userid, name) item(id, description, category, price) buys(user, item, price) rates(user, item, rating) First create these tables using MySQL. You can create the tables in the database you used for the lab sessions (you do not have permission to create a new database using the Department's MySQL server), or wherever you wish if you are running your own copy of MySQL. Please use exactly the table and attribute names specified above. You should ensure that you choose appropriate primary and foreign keys, along with any other constraints you feel are necessary. You can assume that no user buys or rates the same item more than once. You can also assume that users can rate items they have note bought. Note that in order to specify foreign keys, you will need to specify the 'InnoDB'engine when creating the tables. The 'create table' interface in the MySQL Workbench allows you to specify foreign keys using a tab near the bottom of the interface. After you have created the tables, you should populate each one of them with a reasonable number of rows so that each query below produces a non-trivial result (for example, the constants mentioned in the queries should actually appear in the tables). You should submit a file containing both the schemas of your tables and a listing of the data in each table. How to do this is explained below. Now answer the following questions by formulating SQL queries that will return correct answers for any instance over the above database schema. Each question should be answered by using a single SQL query; e.g., do not use views: 1. Find the descriptions of items in the book category which cost less than 5.99. 2. Find the descriptions of items bought by the user with name "Smith". 3. Find the names of users who have bought items with a total value of more than 1000. 4. For each category, find the average current price of items in that category. 5. Find the descriptions of items which have not been rated by any user. 6. List the category and current price for all items costing more than 100, ordered by ascending category and, within category, by descending price. 7. Find the names of pairs of users who have bought the same item, removing pairs such as (x,x), and keeping only one of (x,y) and (y,x) for user names x and y. 8. Find the names of users and descriptions of items such that the user bought the item for a price greater than the current price. 9. For each item which has at least two ratings, find the average rating for the item. 10. For each user, find the descriptions of items they have bought but not rated. Submit a listing of the SQL queries and their results. Part II: SQL Queries (50 marks) Consider the following tables which model certain features of an online shop, allowing users to purchase Page 1 of 3 Database Management 2021 14/02/2021, 16:45 items and rate them. The online shop has a number of users, each of whom has an identifier ('id') and a name specified. The shop sells a number of items, each with an identifier ('id'), a description, a single category under which it is classified, and a current price. Users can buy items from the shop, with the price paid recorded for each purchase (the current price of an item can vary from one day to the next). Price should be a decimal number, allowing for two digits to the right of the decimal point. Users can also rate items sold by the shop. They do this by specifying a rating, an integer between 1 and 5, for an item. Note that 'price' in table 'item' is the current price of the item, 'price' in table 'buys' is the price the user paid for the item, and 'user' and 'item' in the tables 'buys' and 'rates' correspond to the identifiers of users and items, respectively. userid, name) item(id, description, category, price) buys(user, item, price) rates(user, item, rating) First create these tables using MySQL. You can create the tables in the database you used for the lab sessions (you do not have permission to create a new database using the Department's MySQL server), or wherever you wish if you are running your own copy of MySQL. Please use exactly the table and attribute names specified above. You should ensure that you choose appropriate primary and foreign keys, along with any other constraints you feel are necessary. You can assume that no user buys or rates the same item more than once. You can also assume that users can rate items they have note bought. Note that in order to specify foreign keys, you will need to specify the 'InnoDB'engine when creating the tables. The 'create table' interface in the MySQL Workbench allows you to specify foreign keys using a tab near the bottom of the interface. After you have created the tables, you should populate each one of them with a reasonable number of rows so that each query below produces a non-trivial result (for example, the constants mentioned in the queries should actually appear in the tables). You should submit a file containing both the schemas of your tables and a listing of the data in each table. How to do this is explained below. Now answer the following questions by formulating SQL queries that will return correct answers for any instance over the above database schema. Each question should be answered by using a single SQL query; e.g., do not use views: 1. Find the descriptions of items in the book category which cost less than 5.99. 2. Find the descriptions of items bought by the user with name "Smith". 3. Find the names of users who have bought items with a total value of more than 1000. 4. For each category, find the average current price of items in that category. 5. Find the descriptions of items which have not been rated by any user. 6. List the category and current price for all items costing more than 100, ordered by ascending category and, within category, by descending price. 7. Find the names of pairs of users who have bought the same item, removing pairs such as (x,x), and keeping only one of (x,y) and (y,x) for user names x and y. 8. Find the names of users and descriptions of items such that the user bought the item for a price greater than the current price. 9. For each item which has at least two ratings, find the average rating for the item. 10. For each user, find the descriptions of items they have bought but not rated. Submit a listing of the SQL queries and their results
