Design Choices

I would create another table for customers. This table would store the customers delivery information so that it does not have to be entered every time they order for delivery. This would be done by holding a CustomerID foreign key in the DELIVERIES table to a CustomerID in the CUSTOMER table.The table could also be used in a similar way for the COLLECTIONS table. It would also make it easier for analytics of customer buying patterns.
Instead of having the table STAFF_ORDERS, replace it will a column called StaffID in the ORDERS table. This will save space in the database and will be more efficient to retrieve orders with a specific StaffID since it will not require a join.

Scheme Design

I have created the scheme following the specification. Some important notes on my rational are:

OrderID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY: This is a very important definition for the smooth functioning of the whole database. What this does is auto-generate a value for OrderID whenever a new row is inserted into the ORDERS table. It is important I have done this to avoid race conditions.
All foreign keys have ON DELETE CASCADE: I have defined the foreign keys in this way as having a null value wouldn't make sense. For instance, a OrderID is deleted whenever the input is invalid when inserting it or if a collection has passed its collectionDate by 8 days. Having the rows left with null values in place for the foreign keys would leave dangling rows, bloating the table and making analytics results skewed.

Collecting User Input

In this section, I will be explaining my thought process when designing the methods for inputting data from the terminal.

collectOrderInfo(): used to collect productIDs , quantities and the orderDate. It uses a loop to keep getting user input for products and quantities and then once they have input all products, asks for the date of the order. I validate the the productID is a valid product in the INVENTORY table and that there is enough of the product requested to fulful the order using checkStock(). If the user inputs lots of the same productIDs multiple time, any errors related to the number of stock will be caught when the product is being added to the order. The date is collected by the collectDate() method using validatation by the validateDate() method to make sure that it is in the format required by the database.
collectInteger(): this method is used to validate a input is integer and greater than zero. It is used to collect: productID, quantity and year.
collectStaffID(): this validates staffID input is from a valid staff item in the STAFF table using validateStaffID().
collectFutureDate(): used to collect a date for the delivery and collection options. This is validated to make sure that it is greater than or equal to the date of the order since, in the real world, it cannot be collected or delivered to some point in the past.
collectYear(): used to collect and validate that the input is a valid year. Makes use of the collectInteger() method.

The Options Methods

Here I will explain my thinking behind the options methods required by the coursework.

Option 1: This method makes use of 3 key methods: -- createOrder(): This method creates the order. The OrderID is generated by the database so the method uses the returnGeneratedKey() to validate and return the primary key generated for the just created row. -- addProducts(): This method adds all products given to the order. First the method calls upon the checkStock() method. This does two things: it makes sure the productID is valid and also it makes sure that there is enough stock to fulfil the order. If either of these conditions are violated, an appropriate error is displayed to the user. The product is not added to the order. If the product passes the above tests then it is added to the order. Finally, the ProductStockAmount in the INVENTORY table is reduce via reduceStock(). This method does not have to worry about making the value negative due to the above checks. Once all products in the order have gone through this process, the order is checked to make sure that it has at least one product associated with it. This is because all products could not be fulfilled (due to lack of stock or invalid productID). In this case, the order is deleted (via deleteOrder()) and the user is notified. -- addStaffOrder(): This method creates a row in the STAFF_ORDERS table. The staffID is validated to make sure that it associates to a staff row in the STAFF table. If the staffID given is not valid, the order is deleted and the user notified.
Option 2: This method makes use of the methods used in option 1 as well as the following method: -- addCollection(): This method creates a row in the COLLECTIONS table with the customer information and the orderID.
Option 3: This method makes use of the methods used in options 1 as well as the following method: -- addDelivery(): This method creates a row in the DELIVERIES table with the customer info and the orderID.
Option 4: I have used an inner join with the INVENTORY and ORDER_PRODUCTS tables on their productIDs. This new table is sorted by (ProductPrice * TotalSales) in descending order.
Option 5: My implementation of this method is not the way that I wanted to do it meaning that I had to settle with this way due to a lack of time. I will explain this then explain the other way that I was trying to do it. I first get all the orders that satisfy the constraints as defined in the coursework. This is so that I can print to the console which orders have been deleted. I then delete these rows. As seen in my schema, the ORDER_PRODUCTS has the FOREIGN KEY(OrderID) REFERENCES ORDERS(OrderID) ON DELETE CASCADE constraint. This means that when an order is deleted, the products associated with the order are too. However, before the ORDER_PRODUCTS row is deleted, the trigger UPDATE_STOCK_QUANTITY, also defined in the schema, adds the quantity of the product back on the ProductStockAmount in the INVENTORY table. I don't like this approach since I had to effectively run the same query twice. The way I wanted to do this option is as follows. Please can you give me feedback on why this didn't work as it ended up being a large time pit.

CREATE OR REPLACE PROCEDURE DELETE_ORDERS (ids OUT OrderIDs)
IS
    TYPE OrderIDs IS TABLE OF ORDERS.OrderID%TYPE;
    ids OrderIDs;
BEGIN
    DELETE FROM ORDERS
    WHERE OrderID IN
        (SELECT ORDERS.OrderID
        FROM ORDERS
        INNER JOIN COLLECTIONS
        ON ORDERS.OrderID = COLLECTIONS.OrderID
        WHERE ORDERS.OrderType = 'Collection' AND ORDERS.OrderCompleted = 0 AND COLLECTIONS.CollectionDate < to_date(?, 'DD-MON-YY') - 7)
    RETURNING OrderID BULK COLLECT INTO ids;

END DELETE_ORDERS;
/

What this procedure is doing is deleting the rows as defined above and then storing their OrderID into the ids variable via the returning function. This is more efficient than the above approach since the deletion and retrieval is done at once. However, I could not get the rows to be returned (only printed to console) and no lab tutors could help me with this. I'm disappointed I could not get this to work in practice but hope you appreciate the thought process behind it.

Option 6: I have made use of joining several tables, collecting the necasary columns from each. The resulting table is filtered so that only values of TotalValueSold greater than or equal to 50,000 are kept. It is then sorted by TotalValueSold in descending order.
Option 7: This option, like option 5, was not implemented in the exact way I want. Don't get me wrong that it is fully and correctly functional but I would like to explain how I had to do it and how I would have liked to. For this option, I created a string list in the database (using LISTAGG) of all of the OrderIDs that satisfy the specification (uses the view SALES_TABLE defined in the schema). Then, I use a pivot to make the OrderIDs be the fields in a new table with the staff the rows. I then format it like the question asks for. I would have liked to do all of this in a function or procedure so that it could be done in a single sql call rather than 2 in java but again I had the problem of returning a table from procedures and functions.
Option 8: This option first uses the view ALL_PRODUCT_SALES to get all of the productIDs that have sold more than £20000 in the given year. Next, i get the staffIDs of staff that have sold at least £30000 in the given year. I then get all the prodoctIDs, checking that they are also in the ALL_PRODUCT_SALES table. This makes sure that all the products were sold this year. I then count the distinct ProductIDs for each StaffID left in this table. Finally, I get all the staffIDs where the count of distinct ProductIDs is the same as the count of ProductIDs in the ALL_PRODUCT_SALES table. This leaves a table of all StaffID that satisfy the specification. I merge this with the STAFF table to get the name for each StaffID then format it to the console.

maxamuss/cs258

Design Choices

Scheme Design

Collecting User Input

The Options Methods