1 Overview of the Assignment
The assignment builds on the BrizzleSarnies database you designed in Assignment 1. The physical database schema which you must use for this assignment as well as some sample data can be found in the associated BrizzleSarnies.sql script which can be downloaded from Blackboard (next to the assignment specification). The tables also exist under the SHARED schema on the university’s Oracle server. Note that you only have SELECT access on the tables in the SHARED schema so you cannot update the tables. To test tasks which require updating the tables, you have to run the associated script in your own schema. The logical schema of the database can be found in Appendix A. Note that when marking your work, we will be using the same database schema as in BrizzleSarnies.sql, so you are not allowed to alter the structure of the tables.The assignment requires you to finish 4 tasks related to the implementation, testing and evaluation of a database application, and alternative approaches to the relational model. The assignment is worth 25% of the overall mark for the module.
2 Task Specification
You are required to finish all the following 4 tasks:- Task 1 (SQL): This task requires you to write Oracle SQL DML statements. For each of the following, give the Oracle SQL DML query which fulfils the request:
- a) List the IDs and names of all sandwiches, sorted alphabetically on the sandwich name.
- b) Return the number of stores in Bristol (i.e. where the postcode of store starts with BS).
- c) For each pending (has not been completed yet) order, list the OrderID, the sandwich name used in the order and the name of the bread used in the order. The result need to be sorted by order date, most recent first in the first instance and then by sandwich name alphabetically.
- Task 2 (PL/SQL) (3 Marks): This task requires you to write Oracle PL/SQL code:
- a) Write a stored procedure that receives as input 2 dates representing start and end of an interval, and displays the IDs and order dates of orders placed in that time interval, sorted according to the date of their placing, earliest first.