Database Management
Part III: SQL Views, Stored Procedures and Functions (20 marks) Answering the questions in this part requires you to undertake some independent investigation, since we did not cover stored functions and procedures in lectures. Read up on stored procedures and functions in MySQL, using information on other pages as necessary to answer the following. 1. Assume that the price values stored in the database do not include VAT. Create a function which, given a price, will return the VAT amount (assumed to be fixed at 20%). 2. Create another function which, given a price, will calculate the total amount to be paid, including the VAT. Make sure that you write the function in such a way that, if the VAT rate changes, this function does not need to be changed. 3. Use the two functions above in a query which lists for each purchase the user name, item description, stored item price, VAT amount and total price. 4. Create a view called 'purchases' which associates each item description with the user ids who bought the item. Even items which were not bought should appear. 5. Create another view called 'ratings' which associates each item description with the user ids who rated the item. Even items which were not rated should appear. 6. Create a procedure which takes a category value or the value '*' as input. When the procedure is called, it returns for each item description the number of users who bought the item and the number of users who rated it. For items which were not bought or not rated, zero should be returned. The output should be restricted to the category passed to the procedure, or be unrestricted if the category value passed was '*'. The procedure should make use of both of the views created above. Part III: SQL Views, Stored Procedures and Functions (20 marks) Answering the questions in this part requires you to undertake some independent investigation, since we did not cover stored functions and procedures in lectures. Read up on stored procedures and functions in MySQL, using information on other pages as necessary to answer the following. 1. Assume that the price values stored in the database do not include VAT. Create a function which, given a price, will return the VAT amount (assumed to be fixed at 20%). 2. Create another function which, given a price, will calculate the total amount to be paid, including the VAT. Make sure that you write the function in such a way that, if the VAT rate changes, this function does not need to be changed. 3. Use the two functions above in a query which lists for each purchase the user name, item description, stored item price, VAT amount and total price. 4. Create a view called 'purchases' which associates each item description with the user ids who bought the item. Even items which were not bought should appear. 5. Create another view called 'ratings' which associates each item description with the user ids who rated the item. Even items which were not rated should appear. 6. Create a procedure which takes a category value or the value '*' as input. When the procedure is called, it returns for each item description the number of users who bought the item and the number of users who rated it. For items which were not bought or not rated, zero should be returned. The output should be restricted to the category passed to the procedure, or be unrestricted if the category value passed was '*'. The procedure should make use of both of the views created above
