Assignment: Introduction to Databases

1 BrizzleSarnies Logical Schema

Overview of the Assignment

The assignment builds on the BrizzleSarnies database you designed in Assign- ment 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 spec- ification). 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 described in detail in Section 2.

This is an individual assignment.

If you have questions about this assignment, please post them to the discus- sion board on Blackboard or contact the module leader.

2 Task Specification

You are required to ftnish 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 Or- derID, 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): 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.

• Task 3 (Python & Oracle): This task requires you to write a Python desktop application that interfaces with the BrizzleSarnies database. Your application needs to perform the following 2 tasks:

a) Allow the user to add new types of bread to the database. The user will be prompted to enter the bread name, the number of calories per loaf, and the loaf price (in pence). The ID used for the new bread type must be in sequence and takes into consideration the most re- cent BreadID in the database. Perform the required checks to, for instance, ensure that the new bread type does not already exist.

b) Allow the user to obtain the cost of a particular order. The user will be prompted to enter an OrderID, and he/she will get back the cost of the order if it exists. If the order does not exist, the user should see an appropriate message.

• Task 4 (MongoDB): This task requires you to write Mon- goDB code. Perform the following 2 tasks:

a) Design a MongoDB database that stores the Sandwich information from the BrizzleSarnies relational database. You only need to store information about sandwiches and fillings involved in them and noth- ing else, i.e. you do not need to worry about stores, orders, breads, and categories of fillings. Include only details of the first 5 sand- wiches, i.e. sandwiches where SandwichID ≤ 5.

b) Give a MongoDB query that returns all sandwiches whose names starts with B.

3 Deliverables

This assignment requires online submission via Blackboard. You need to submit the following 3 files:

1. SQLAnswer.sql: This must contain the valid SQL statements for Tasks 1 & 2 and any other Oracle SQL or Oracle PL/SQL code you're using for your application in Task 3. You need to check that this is correctly formatted and runs as a SQL script. Include comments to clarify which part is related to which task.

2. PYAnswer.py: This must contain all the Python code you used for an- swering Task 3. You need to make sure that your code executes correctly. Include comments to detail how one should run your application.

3. MongoAnswer.js: This should contain all the MongoDB related queries that answers Task 4. Again, you need to make sure that your code runs correctly.

Important Notes:

1. You must use the above file names in your submission.

2. For Tasks 2 and 3, a demonstration is mandatory and without demon- strating your work, you will get 0 marks for those tasks. Demonstrations will take place the following week to the submission deadline. Timetable of demonstrations will be published nearer the time.