Celia's Coffee Emporium (CCE UK Ltd.) has grown from a small company based in a small market
town, to one of the region's leading suppliers of coffee machines, accessories and various coffees
from around the world. However, they have never quite managed to move away from using a
paper-based filing system for storing customer, sales, and stock information. To cope with their
growth and allow for more efficiency, the company has decided to computerise their customer and
stock management system. You have been tasked with developing a database application to meet
their needs.
Current Position
Currently, CCE UK Ltd. record details of all customers (including their name, address, telephone
number, date of birth and bank details, i.e., bank name, address, sort code and account number)
who purchase a product. Records are also kept of every purchase that takes place in any of the
stores.
A stock warehouse is also kept. This makes it possible to see where any given product (including
its identifier, type, name, description and cost) is currently stored. The stock warehouse also
contains details of all purchases, and it allows stores to move stock from one store to another.
This is particularly useful for those customers who wish to purchase a product which is not
available in their local store.
The organisation would also like to be able to create management reports, which may, for example,
show all sales at a particular store or a group of stores, between certain dates.
(The table names are Customer, Purchase, store, stock and purchases)

a.) Subsequently, produce a single SQL script file which can be run within Oracle SQL
Developer without error, and:
i. drops and then creates your tables (correctly ensuring that any referential integrity
issues can be resolved)
ii. inserts sample data into every table - suggest 5-10 rows of data per table
iii. that creates the PL/SQL code described in task (e) below
iv. includes calls to your PL/SQL stored procedures, supplying sample test data. Do
not prompt for user input, just supply test data as parameters to your procedure
calls. Make sure your test calls cater for valid and invalid data examples to
demonstrate thorough testing of each stored procedure.

b.) Using Oracle PL/SQL develop:
i. a PL/SQL stored procedure (and any associated code) which allows for registration
of new customers.
ii. a PL/SQL stored procedure (and any associated code) which allows an existing
customer to purchase a single item from a specific store. This transaction must
allow the client to specify an item (by its code) to be purchased, provide accurate
cost information and deal with any problems, e.g., insufficient stock, etc. Note that
it is not necessary to create a shopping basket and there is no requirement to
implement purchasing functionality such as linking to PayPal - you are
simply required to store purchase information within the database.
iii. a PL/SQL stored procedure (and any associated code) which allows the company to
produce a management report showing all purchases between two dates for a
specific store. Make sure you show detailed information in the resulting output, e.g.,
item name and description, not just an item id.
Each PL/SQL stored procedure may require you to develop other PL/SQL stored
functions, triggers and cursors that you deem necessary to fully implement the required
functionality.