Database Management - Spring Term - 2018

Compulsory Coursework - 20% of the Final Mark


Part I: Entity-Relationship Diagram (25 marks)

Consider modelling certain features of an online shop which allows users to purchase items and rate them. The online shop has a number of users, each of whom has an identifier and a name specified. The shop sells a number of items, each with an identifier, 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). 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. You should assume (initially) that users are allowed to rate items that they have not bought.

Draw an entity-relationship diagram that models the above description of an online shop. Make sure that you specify all details in the diagram, including cardinality (mapping) constraints, participation constraints, keys. etc. Please use the ERD notation used in lectures. Submit a file containing your diagram. You do not necessarily have to use a tool or sophisticated drawing program to produce the diagram; even a hand-drawn and scanned diagram is fine if it is neat and legible.

How would you change your diagram to reflect the situation in which a user can only rate items that they have bought?

Part II: SQL Queries (50 marks)

The following tables and associated attributes model a shop as described above, where 'price' in table 'item' is the currect 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.

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. For this part, you can assume that no user buys or rates the same item more than once. You do not need to assume that users can only rate items they have 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. Please refer to the instructions on how to export tables and their definitions.

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 (assume that we don't have to worry about currency units, and price is just an integer).
  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. Find the descriptions of items which have been bought by the most users.
  7. List the category and current price for all items costing more than 100, ordered by ascending category and, within category, by descending price.
  8. Find the names of pairs of users who have bought the same item, removing duplicate pairs and pairs such as (x,x) for user name x.
  9. Find the names of users and descriptions of items such that the user bought the item for a price greater than the current price.
  10. For each user, find the descriptions of items they have bought but not rated.
  11. For each item which has at least 2 ratings, find the average rating for the item.
  12. For each user, find the items which they have not bought and which have the highest average rating.

Hand in a listing of the SQL queries and their results. Write a sentence or two on each query describing what you did.

Part III: SQL Stored Procedures and Functions (25 marks)

This part asks you to do some independent work; you can use the Web as an information source, but I encourage you to use other material as well. Your answer should NOT exceed two sides of A4, point size 11, single line spacing, and should be done on a word processor. Make sure to reference all your information sources. Any direct quotations should be included in quotes, with citation(s) provided. Remember that we take plagiarism very seriously, and your submissions may be processed by a plagiarism detection system.

Most relational database systems allow user-defined procedures and functions to be stored in a database and to be referred to in user queries. The programming language constructs which can be used when defining these functions and procedures include familiar ones such as loops, conditional statements, etc. The part of the SQL standard that deals with these constructs is called the Persistent Storage Module (PSM). You should first explain what capabilities are provided by the standard, and then survey to what extent the major database management systems (Oracle, Microsoft SQL Server, IBM DB2 and MySQL) support these capabilities, as well as any additional features they may provide.