CS258 Database Systems: Assignment (2017/2018)
A department store has recently expanded its services to increase the number of ways of
purchasing items from the store. As well as being able to purchase in-store, a customer can
place an order and collect from the store or arrange a home delivery. A store also tracks
whether an order has been completed - for an in-store purchase this is immediate, for collec-
tions and deliveries this occurs once the item is in the hands of the customer (ORDERS).
The store keeps a track of its inventory. To do so, it stores an item description, the item
price and the number of items currently in stock (INVENTORY). For an order of any type,
the amount of each product that is in the order must be stored, and the amount in stock
must be reduced correspondingly (ORDER PRODUCTS). Multiple di erent products can
be part of the same order.
If the customer purchases an item in-store, then no customer details are needed, just the
information about the products in the order as speci ed above. If either of the other ordering
options is followed, then the amount of stock must still be reduced, but it is also necessary to
store some additional details. For in-store collection, the store keeps track of an order-id, a
customer name (as rst and last name) and the expected collection date (COLLECTIONS),
but does not keep the customer address. For a delivery, the store keeps track of not only an
order id, but the customer name, a delivery address and the requested delivery date (DE-
LIVERIES).
The store also decided to re ne its analytics by re ning its inventory information to include
details of its sta (for the purposes of testing, assume that all existing and future orders are
associated with one and only one member of sta ). Each member of sta has a sta id, a
sta name and is considered responsible for a particular order. Any sta member can sell
any particular item in the store, and they can sell more than one kind of item.
The goal of this coursework is for you to develop a Java application, using JDBC, and a
database for this system. You will need to provide SQL statements for creating the tables
(see Section 2), develop a selection of functions (Section 4) and test your application using
test data you come up with yourself. Where possible, you should aim to perform. any
operations using SQL, rather than extracting and processing data within the
Java application.
1
1 Submission
Your nal submission should consist of a single le, Assignment.java. Your submitted solu-
tion should not actively perform. the following:
Create data table
Insert test data
We will destroy and recreate the tables with test data as part of the marking process. Your
submission does however have to interact with the tables described in this assignment, and
it is expected that you will perform. the process of creating the tables and inserting data for
your own testing purposes to ensure your solution functions correctly (it is recommended
you maintain a separate schema le to load into SQLPlus to do this).
Submit a single le named Assignment.java via Tabula by noon on Thursday
7th December 2017.
2 Initialisation [20%]
Write SQL code to create the tables from Figure 1. Use the following types for the respective
columns:
integer (ProductID, OrderID, ProductQuantity, ProductStockAmount, Sta ID)
varchar(30) (ProductDesc, OrderType, FName, LName, House, Street, City)
numeric(8,2) (ProductPrice)
integer (OrderCompleted)
Date (OrderPlaced, DeliveryDate, CollectionDate)
For OrderType, acceptable values are ’InStore’ , ’Collection’ or ’Delivery’.
For OrderCompleted, 0 is treated as an uncompleted order, and 1 as a completed order.
Ensure the table names and attribute names match those provided.
Specify key and referential integrity constraints so as to capture the description given in the
introduction as accurately as possible. Also specify any additional constraints you believe
apply to the system. Provide justi cation for your choices in comments.
Include the SQL code (and your justi cations) as a comment at the beginning of the sub-
mitted .java le.INVENTORY
ProductID ProductDesc ProductPrice ProductStockAmount
ORDERS
OrderID OrderType OrderCompleted OrderPlaced
ORDER PRODUCTS
OrderID ProductID ProductQuantity
DELIVERIES
OrderID FName LName House Street City DeliveryDate
COLLECTIONS
OrderID FName LName CollectionDate
STAFF
Sta ID FName LName
STAFF ORDERS
Sta ID OrderID
Figure 1: Table Schemas
3 Design Choices [5%]
The tables as designed in Figure 1 should not be changed for this coursework. However, if
you were to make any modi cations, what changes might you make to improve the table
structure? Highlight your answer by naming the comment ’Design Choices’ and state your
answer as a comment at the top of the submitted Assignment.java le.4 Application [75%]
Write (and test) a Java program that repeatedly displays the menu below and allows the
user to select one of the options. Each option is speci ed below along with the expected
output format. A skeleton le is provided for you to use, and it is expected that your nal
submission will be a completed version of this skeleton le that completes any indicated
methods, and expands upon the existing main() method to provided the application func-
tionality. You may add additional methods should you so wish, but you must implement
the provided incomplete methods to implement the functionality described in the le and
in this document. Once an option has completed, the program should return to the menu
(until Quit is selected).
You should not read any command line inputs in these methods, the data they use should be
passed as parameters to the respective methods. As part of the menu functionality, the data
should be read in via prompts in the main method (use readEntry) and put into appropriate
data structures for passing to these methods. The methods do however handle any data
printing to the screen. When testing your code, you can create a separate testing method
that contains hard-coded test cases passed as arguments without the need to enter data at
a prompt.
Document your solutions and justify any decisions you make in comments. It is expected
that your program appropriately handle any error cases that might occur, such as invalid
inputs or failures that arise out of violating constraints applied on the database.
MENU:
(1) In-Store Purchases
(2) Collection
(3) Delivery
(4) Biggest Sellers
(5) Reserved Stock
(6) Sta Life-Time Success
(7) Sta Contribution
(8) Employees of the Year
(0) Quit
Enter your choice:
4.1 Purchases: Options 1-3
Options 1-3 deal with storing order information. When someone makes a purchase, it is
necessary to create a new order and store the details. Anytime a new order is created you
must:
Store the date the order was made (as speci ed by a user).
Store a productID and the quantity of that product sold, for each and every product
that is part of the order.
Reduce the amount of stock of the product by the amount sold, for each and every
product that is part of the order.
Store whether an order has been completed or not
Store the Sta ID of the employee who has added the order
4.1.1 In-Store Orders (Method option1)
If an order is an instore order (Order type ‘InStore’), it is automatically considered a com-
pleted order, and this should be stored alongside the information above that applies to all
orders. No additional customer information is needed.
This option should be implemented using the method ‘option1’. It should take a Database
Connection (conn), an int array of productIDs that belong to the order, an int array of
quantities (the index of the quantity array corresponds to the index of a matching produc-
tID in productIDs), the date which the order was made as a string and the ID of the sta
member as an int. This string should correspond to the default Oracle Date formatting (i.e.
’DD-Mon-YY’ such as ’09-Nov-17’).
Once any data storage or updates are complete, the option should print out a list of updated
product quantities with their product ID , one per line.
The below is an example of the formatting to be expected when running Option 1:
Example Input
Enter your choice : 1
Enter a product ID : 1049
Enter the quantity sold : 5
I s there another product in the order ? : Y
Enter a productID : 1048
Enter the quantity sold : 2
I s there another product in the order ? : N
Enter the date sold : 01 Jan 10
Enter your s t a f f ID : 5
Example Output (Database should also be updated)
Product ID 1049 stock i s now at 20.
Product ID 1048 i s now at 48.
4.1.2 Collection Orders (Method option2)
If an order is a collection order (OrderType ‘Collection’), it defaults to uncompleted. In
addition to the information that must be stored for all orders, It is also necessary to nd out
and store:
The date (same formatting as above) that the item will be collected.
The rst and last name of the person collecting the order.
This option should be implemented using the method ‘option2’. It should take a Database
Connection (conn), an int array of productIDs that belong to the order, an int array of quan-
tities (the index of the quantity array corresponds to the index of a matching productID in
productIDs), the date which the order was made as a string, the data of the collection, the
rst and last name of the collector as strings and the ID of the sta member as an int. Any
dates should correspond to the default Oracle Date formatting (i.e. ’DD-Mon-YY’ such as
’09-Nov-17’).
Once any data storage or updates are complete, the option should print out a list of updated
product quantities with their product ID, one per line.
The below is an example of the formatting to be expected when running Option 2:
Example Input
Enter your choice : 2
Enter a product ID : 1049
Enter the quantity sold : 5
I s there another product in the order ? : Y
Enter a productID : 1048
Enter the quantity sold : 2
I s there another product in the order ? : N
Enter the date sold : 01 Jan 10
Enter the date of c o l l e c t i o n : 10 Jan 10
Enter the f i r s t name of the c o l l e c t o r : Brian
Enter the l a s t name of the c o l l e c t o r : Smith
Enter your s t a f f ID : 5
Example Output (Database should also be updated)
Product ID 1049 stock i s now at 20.
Product ID 1048 i s now at 48.
4.1.3 Delivery Orders (Method option3)
If an order is a delivery order (OrderType ‘Delivery’), it defaults to uncompleted. In addition
to the information needed for all orders, It is also necessary to nd out and store:
The date (same formatting as above) that the item will be delivered.
The rst and last name of the person receiving the order.
The house, street and city that the delivery is going to.
This option should be implemented using the method ‘option3’. It should take a Database
Connection (conn), an int array of productIDs that belong to the order, an int array of quan-
tities (the index of the quantity array corresponds to the index of a matching productID in
productIDs), the date which the order was made as a string, the data of the delivery, the
rst and last name of the receipient as strings, the house, street and city of the address as
strings and the ID of the sta member as an int. Any dates should correspond to the default
Oracle Date formatting (i.e. ’DD-Mon-YY’ such as ’09-Nov-17’).
Once any data storage or updates are complete, the option should print out a list of updated
product quantities with their product ID, one per line.
The below is an example of the formatting to be expected when running Option 3:
Example Input
Enter your choice : 3
Enter a product ID : 1049
Enter the quantity sold : 5
I s there another product in the order ? : Y
Enter a productID : 1048
Enter the quantity sold : 2
I s there another product in the order ? : N
Enter the date sold : 01 Jan 10
Enter the date of d e l i v e r y : 10 Jan 10
Enter the f i r s t name of the r e c i p i e n t : Brian
Enter the l a s t name of the r e c i p i e n t : Smith
Enter the house name/no : Corner Cottage
Enter the s t r e e t : End Lane
Enter the City : Liverpool
Enter your s t a f f ID : 5
Example Output (Database should also be updated)
Product ID 1049 stock i s now at 20.
Product ID 1048 i s now at 48.
4.2 Biggest Sellers (Method option4)
The store is interested in knowing which items are their biggest sellers of all time by value.
Produce a comma separated output that lists the product id, the item description and the
total amount of money made by selling them (from all orders ever), sorted in descending
order. List one product per line.
This option should be implemented using the method ‘option4’. It should take a Database
Connection (conn) as a parameter.
The below is an example of the formatting to be expected when running Option 4:
Example Input
Enter your choice : 4
Example Output
ProductID , ProductDesc , TotalValueSold
2049 , Oak Chair , $50000
108 , Pine Chair , $49323
1078 , Turquoise Lamp, $47934
4.3 Reserved Stock [10] (Method option5)
While the store reserves items for in-store collection, it places a limit on how long it will wait
for a customer. If the order is uncompleted (i.e. not picked up) and is a collection order,
then for a given date the program must (a) identify all orders that are 8 days or older than
the provided date, (b) re-add the items from the order to the product stock amount and (c)
delete the order and any data pertaining to that order (we will assume someone else will take
care of the customers refund). Write an option that given a date, nds any such violations
of the rule and applies the above process. As output you should print out the order id of
the orders that have been cancelled.
This option should be implemented using the method ‘option5’. It should take a Database
Connection (conn) and a target date as a string parameter. This string should correspond
to the default Oracle Date formatting (i.e. ’DD-Mon-YY’ such as ’09-Nov-17’).
The below is an example of the formatting to be expected when running Option 5:
Example Input
Enter your choice : 5
Enter the date : 07 Nov 17
Example Output (Database should also be updated)
8
Order 1034 has been c a n c e l l e d
Order 1078 has been c a n c e l l e d
4.4 Sta Analytics
4.4.1 Sta Life-Time Success (Method option6)
The sta employee lifetime awards are coming up! Obtain a list of sta who have sold at
least $50000 of items during the store’s lifetime and print their names alongside the amount.
Print one employee per line, and sort in descending order by the total value sold.
This option should be implemented using the method ‘option6’. It should take a Database
Connection (conn) as a parameter.
The below is an example of the formatting to be expected when running Option 6:
Example Input
Enter your choice : 6
Example Output
EmployeeName , TotalValueSold
Brian Jackson , $51042
Susan Smith , $50001
4.4.2 Sta Contribution (Method option7)
The stores wishes to know who is responsible for selling the most of the highest rated prod-
ucts. For any item that has sold over $20000 in the store’s lifetime, print the name of any
member of sta who has sold at least one of any of these products, alongside the amount
they have sold of each of these products (0 is possible), one employee per line.
The output should be formatted such that lists the quantity of each of the products sold by
that employee sold over the store lifetime, with a heading line that lists the product IDs.
Sort the results in descending order by the total monetary value of the high-selling products
only (greater than $20000 in the store’s lifetime) sold by each employee.
This option should be implemented using the method ‘option7’. It should take a Database
Connection (conn) as a parameter.
The below is an example of the formatting to be expected when running Option 7:
Example Input
Enter your choice : 7
9
Example Output
EmployeeName , Product 1489 , Product 2078 , Product 189
Brian Jackson , 42 , 0 , 92
Susan Smith , 0 , 18 , 100
Peter Williams , 2 , 15 , 46
4.4.3 Employees of the Year (Method option8)
The store CEO has noted the great success of their employees this year, and decides to hold
an employee of the year ceremony. Given a speci c year, to narrow down the candidates,
they wish to nd the names of sta who have sold at least $30000 of products in the speci ed
year, and sold at least one of each of the items that have sold over $20000 worth of product
for the speci ed year. Print out the employees’ rst name and last name, one employee per
line.
This option should be implemented using the method ‘option8’. It should take a Database
Connection (conn) as a parameter and a target year as an integer parameter (the integer
will be in YYYY format, not YY).
The below is an example of the formatting to be expected when running Option 8:
Example Input
Enter your choice : 8
Enter the year : 2017
Example Output
Brian Jackson
Susan Smith
Peter Williams
5 What constitutes a good answer?
There is no marking preference to whether you use statements or prepared statements for
this coursework (we will not be assessing security here, though it should be a consideration
for your future SQL!)
You should handle errors appropriately! This means thinking about what potential errors
might occur, and catching them when they do so.
Good answers are thoroughly tested answers!
You should favour using SQL over processing data in Java where possible.
For all of your answers you supply appropriate comments describing how your code/SQL
works. In addition, as with all programming, you should adhere to programming best prac-
tices with respect to formatting etc.
6 Final remarks
Please make sure your programs work on daisy. Keep a copy of everything you submit! You
may discuss with fellow students the material covered in lectures and seminars, but you are
not allowed to collaborate on the assignment. The University of Warwick takes plagiarism
seriously, and penalties will be incurred if any form. of plagiarism is detected. Copying, or
basing your work on, solutions written by people who have not taken this module is also
counted as plagiarism. This includes material that has been downloaded from the internet.
Good luck!