Reference no: EM132517867
31253 Database Programming Assignment - University of Technology Sydney, Australia
Assignment Title - Forecasting Electricity Demand
Task - The electricity traders are required to bid into the market daily for the amount of electricity that is to be purchased to satisfy the customer demand. As the traders bid into the market, AEMO will instruct a generator to produce that amount of electricity. This means that the trader (on behalf of the retailer) will have to pay for the generated energy, whether it is used or not. Likewise heavy penalties are imposed on retailers if they do not balance their consumption with the generated volume.
For that reason, the estimation of the volume of energy to be consumed is critical to the profitability or the organization. In order to minimize the risk to the organization, your task is to
Forecast the energy requirements for each TNI, LR, FRMP combination, daily at each half hour interval.
Your forecast should be calculated for two weeks into the future.
The forecast should be for each TNI, LR, FRMP at each of the 48 half hour intervals.
Each forecast record that you create should have the STATEMENT_TYPE marked as FORECAST.
The forecast half hour values for each day in the future should be based on the average of the half hour values for the previous same day and half hour combination ie Sundays should be forecast as an average of only the Sundays in the past, Mondays should only be the average of the Mondays in the past etc.
Holidays are a special case. If you are forecasting for a day in the future that is a holiday then you must only average out the consumption of the previous holidays. If there is no past holiday consumption data then use the past Sundays consumption data for the future holiday forecast. Do not mix the two, either use Sundays consumption or holiday consumption. The day type is immaterial for Public Holidays.
When forecasting for future days that are not holidays you must not use days in the past that are holidays to determine the average consumption.
THE FORECAST DATA WILL BE PLACED INTO THE TABLE LOCAL_RM16. My table NEM_RM16 or the view v_NEM_RM16 will be the source of the consumption data and you will place the forecast data into your table LOCAL_RM16.
Your program will run every day in production so at some point in time you will be looking back and see your FORECAST data for some TNI's. These FORECAST values should be ignored. You must consider only actual consumption values to determine the average future consumption. Ie STATEMENT_TYPE is not equal to 'FORECAST'.
Update the CHANGE_DATE column to system date for any records created or updated.
There are a number of other columns in the table that are not relevant to this Assignment. You can hardcode any value you like or leave the values blank. It is up to you.
Produce an XML file written to the Operating System that lists the total consumption for each TNI for a single day. The forecast output date will be the date after the run date ie (sysdate + 1). If your program is forecasting on 03-APR-2020 then the xml output of forecast data should be for 04-APR-2020 only.
Note - Assignment to be completed using PL/SQL please. Also please insert as much explanation throughout the code as you can so that I can explain what methods were used. Thank you!
Attachment:- Database Programming Assignment Files.rar