Programming辅导、Database辅导、SQL编程语言调试、SQL讲解 讲解SPSS|辅导Web开发
- 首页 >> OS编程 University of Technology, Sydney
Faculty of Engineering and Information
Technology
Subject Database Programming (31253)
Assignment
Autumn 2020
Assignment Title
Forecasting Electricity Demand
Due date - TBA
I will announce the delivery method
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 2 of 16
INSTRUCTIONS
1. This is an individual assignment and is worth 50% of your final grade. The Assignment will
require a commitment of about 25 hours and addresses objectives 1, 2, 3, 4 and 5 as outlined
in the Subject Outline.
2. For ease of marking, I would like you to document your approach to the solution of the problem.
Please keep your description short, relevant and to the point. It is strongly recommended that
you utilize diagrams to convey your ideas. Your description will help me understand your code
better. Please indicate any features that you are particularly proud of or that you want to be
noted during the marking process.
3. You are expected to submit a professional presentation in a soft copy format, prepared using a
suitable Word Processor. A hand written submission is not acceptable and will not be marked.
4. I will be looking at your code and data in the Database, so please DO NOT include any code in
your submission.
5. Please include your ORACLE userid on the front page of your submission where I can easily
find it. DO NOT INCLUDE YOUR PASSWORD
Also please provide your email address so the marking sheet can be sent to you.
6. There may be errors and ambiguities in the assignment specification. If so,
corrections/clarifications will be posted to the subject site on UTSOnLine. You are expected to
regularly check the site and incorporate any changes into your submission. The specification
will be frozen two weeks before the due date.
7. Please take note of the due date, and work to that date, as extensions without penalty will be
granted only under exceptional circumstances. Students should be aware that a penalty will be
applied for late submissions.
Penalty is as follows
+ Up to one week late incurs a 20% penalty.
+ Up to two weeks late incurs a 40% penalty.
+ Assignments which are more than two weeks late will not be accepted.
8. I expect to return the marked assignments no later than 14 days after the submission date.
Since I will not see you to hand back the Assignment and the marking sheet please include your
email address on your submission so that I can email the marks to you.
I will send you a comment sheet which will include the breakdown of marks and my observations
noted while running/marking your code.
9. Students are advised to make themselves familiar with the Academic Misconduct statement
detailed in the Student Guide and conduct themselves according to the expectations of the
University.
10. It has been noted in the past that due to lack of industry experience, many students find it
difficult to understand some of the Assignment requirements. To aid in understanding I will
utilize UTSOnLine extensively to convey ideas and to clarify any misunderstandings. As part of
this subject you will be expected to check the postings on UTSOnLine regularly.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 3 of 16
Table of Contents
INSTRUCTIONS ............................................................................................... 2
Document History ............................................................................................ 3
Glossary of Terms ............................................................................................ 4
Overview ......................................................................................................... 5
Information to help Understanding .................................................................... 6
Distribution regions in NSW ........................................................................... 6
Diagrammatic Representation of Power Distribution ........................................ 7
Database specifics............................................................................................ 8
NEM_RM16 table .......................................................................................... 8
DBP_HOLIDAY .............................................................................................. 8
MESSAGE Logging ........................................................................................ 8
Additional Objects ....................................................................................... 10
Database Diagram ...................................................................................... 11
Specification of the RM16 report ..................................................................... 12
Your Task................................................................................................... 12
Deliverables ................................................................................................... 14
Marking Scheme ......................................................................................... 16
Document History
31-MAR-2020 Initial DRAFT version released to students
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 4 of 16
Glossary of Terms
BASIC METER A meter that measures electricity flow as a continuous value. A
Speedo is an example of a Basic meter. Consumption is
calculated by subtracting one value from another.
INTERVAL METER A meter that measures electricity consumption as a continuous
flow but records the meter value each half hour interval.
TNI Terminal Node Identifier. It is a logical grouping of distribution
terminating points from the generator to the delivery point.
Physically, TNI’s comprise of many metering points from many
different generators
FRMP Financially Responsible Market Participant. This is the retailer
who is responsible for the purchase and the resale of electricity.
The purchase is from AEMO and the sale is to the customer base
in the TNI.
LR Local Retailer. This is the Market Participant that is responsible
for the Transmission and delivery of the electricity to a region.
When the electricity market was government controlled an LR
was allocated to maintain electricity supplies to a region
STATEMENT TYPE AEMO produces RM16 statements at regular intervals. The full
volume of the electricity consumption may not be known at the
time that the statement is produced. Statement type then
denotes the status of the report. The values are PRELIM,
REVONE, REVTWO, FINAL
RM16 Is a statement issued by AEMO each week. The statement is
issued to each market participant and shows only their
consumption for a TNI in a given day. The consumption volumes
are provided at half hour intervals.
Market Participant The electricity market is made up of a number of generators and
retailers. The market can be looked at as a pool where the
generators add to the pool (sell) and the retailers buy from the
pool to service their customers. AEMO is the controller of the pool
and all settlements are done with AEMO.
The individual organizations, whether generators or retailers are
the Market Participants.
MDP Meter Data Provider. The code of the organization that reads the
meter
CHANGE_DATE The field is updated every time any change is performed on the
record
AEMO Australian Energy Market Operator
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 5 of 16
Overview
There is considerable activity in the electricity industry at the moment with the
NSW Governments recent privatization the retail electricity industry. NSW has
joined Victoria, South Australia, Queensland and Tasmania to become part of the
deregulated Electricity market.
As a result, electricity retailers will be canvassing existing customers to sign up
with them and have their electricity supplied by them. The reality of course is that
the customers’ electricity will still be produced by the same generators and
delivered to the customers’ home by the same distribution network. What will
change though is who will be responsible to ensure that the correct volume of
electricity is produced to satisfy demand and who will be the billing agent that the
clients will pay their electricity bills to.
In order to make the electricity market function, all the retailers and generators
participate in what is known as an Electricity Market.
The market is a pool where the producers bid to supply and the retailers bid to
buy from to supply their customers. This pool is managed and controlled by a
government authority called AEMO (Australian Energy Market Operator). The
buying and selling of the electricity is done by electricity traders working for the
various Energy companies like Energy Australia, Origin, Alinta, TRUEnergy and
many others.
The bidding process by the sellers and buyers is what determines the electricity
price and this price is set at 5 minute intervals. AEMO aggregates the 5 minute
prices into half hour intervals and publishes these prices continually.
The electricity traders need to be able to determine as accurately as possible what
will be the total consumption for their consumers is in order to be able to
purchase the right amount of energy and capitalize on market fluctuations at each
one of the half hour intervals.
AEMO produces a historical consumption report at regular intervals and provides
the consumption data for each of the participants in the market. Each participant
report is for the total volume of that participant only, broken down by LR and
FRMP regions. This report is known as a RM16 report and is produced and
distributed weekly. This data will be stored in a table called NEM_RM16
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 6 of 16
Information to help Understanding
Distribution regions in NSW
The map of NSW shows how the state is divided into distribution boundaries
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 7 of 16
Diagrammatic Representation of Power Distribution
Showing how many generators provide power to one TNI
TNI TNI
TNI
TNI
TNI
Market Region
Generator
Generator
Electricity is distributed using high voltage cables from the various generators that are
located throughout the states.
Each market region has many terminating points where the electricity flow is metered,
stepped down in voltage and then distributed to the consumers.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 8 of 16
Database specifics
NEM_RM16 table
The RM16 data from AEMO is delivered into a table called NEM_RM16.
The consumption data is stored at half hour intervals.
The NEM_RM16 has an object as an attribute. The object is called VALUE_HH.
The VALUE_HH object is a nested table object of type RM16_DAY which in turn is
an array of 48 objects RM16_HH which is a record of two elements HH and
VOLUME. This object is used to represent the 48 daily half hour elements and
their associated consumption volume.
The script to create the objects and the RM16 tables will be made available on
UTSOnLine
You will create your own LOCAL_RM16 table to store your calculated FORECAST
values. Your table will be a direct copy of either my table with the object or my
view with the object unwrapped into individual attributes. This view is named
V_NEM_RM16. You a free to use either the object or the unwrapped version of
the object It all depends on what you are most comfortable with
If you chose to go with the object then the details on how to work with the object
will be provided in class and on UTSOnLine.
The basic select statement is
SELECT r.* , v.*
FROM NEM_RM16 r, TABLE(VALUE_HH) v
Do not copy my tables into your schema. When marking I will set test data in the
table in my schema. If you copy the table into your schema then your program
will not see the test data that I generate. Look at the ER Diagram. You will be
creating tables LOCAL_RM16, DBP_PARAMETER and the RUN_TABLE as a
minimum in your schema. You are of course free to create any other tables that
are appropriate to your system design
DBP_HOLIDAY
This table has the list of days that are deemed holidays. If a date appears in this
table then it is to be treated as a holiday. Please do not copy my table into your
schema. During marking I will set test data in my table. If you copy the table into
your schema then your program will not see this test data when I mark.
MESSAGE Logging
Your program should log its progress into a log table. This table is called
DBP_MESSAGE_LOG. You do not have direct access to insert into this table, rather
a procedure has been developed to enable you to log your messages.
The procedure is called COMMON.LOG.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 9 of 16
How to log
In your code issue the following statement
COMMON.LOG()
Eg COMMON.LOG('Before updating the LOCAL_RM16 table ');
To view your message
SELECT *
FROM DBP_MESSAGE_LOG
WHERE USERID = USER
ORDER BY MSG_DATE desc;
RUN Table
Your forecasting system will run each day and forecast the electricity demand two
weeks into the future. You are asked to create and maintain a RUN table
which should keep track of the run dates and the status of each run. There should
be only one record in the run table for your program per day
There are two things that your run table should check
• Your program should only ever run once per day.
• You must use the run table to ensure that only one instance of the
program is to run at any one time. If the program is already running it
cannot run again. If there is a large volume of data then your program may
take some time to complete. If the program is restarted while another
session is running then a log message is to be written into the logging table
and the second program instance should terminate gracefully. No entry into
the Run table is required.
The table specification is given to you in the supplied ER Diagram. I will also give
you the sql to create this table.
DBP_PARAMETER Table
To avoid and/or minimize repeated code I will ask that you create a Parameter
table. The specifications are given to you in the supplied ER Diagram. I will also
give you the SQL to create the table.
You are to place any constants into this table, rather than hard coding the values.
Having the values in the table allows the users to change those values. If the
values are hardcoded in the program then a code change and subsequent recompilation
is required to make the changes.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 10 of 16
Additional Objects
It is unlikely that you will require additional tables, however if you feel that your
solution will be enhanced by additional tables, views or any other object then you
are free to create as many additional objects as you like.
But please be aware – Do not duplicate any tables in your schema that are a
copy of my tables and data. The data source for your program are the tables in
my schema. Your program output will go into the table that you create in your
schema. This will be LOCAL_RM16
During marking I will be manipulating data in my schema to check how your
program behaves. If you copy tables and data into your schema and write your
program to access your data instead of mine then the changes I make during run
time will not be reflected in your program run. Your program will then not work
correctly and this will be reflected in your mark.
You are free though to create any additional objects that you feel will help your
program to work efficiently. One such table may be your local copy of the
parameter table. You could use that to store your program specific parameters.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 11 of 16
Database ER Diagram
The view v_NEM_RM16 is created with the query
CREATE OR REPLACE FORCE VIEW dbp_admin.v_nem_rm16 (company_code,
settlement_case_id,
AS
SELECT r.company_code, r.settlement_case_id, r.settlement_run_id,
r.statement_type, r.tni, r.metertype, r.frmp, r.lr, r.mdp,
r.change_date, r.DAY, r.transaction_id, v."HH", v."VOLUME"
FROM nem_rm16 r, TABLE (r.value_hh) v;
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 12 of 16
Specification of the RM16 report
The RM16 report is broken down as follows
• Each state is broken into a number of Distribution areas referred to as
Patches.
• Each patch in divided into a number of TNI’s (Terminal Node Identifiers).
These are the logical divisions where the very high voltage cables from the
generators terminate and the flow of power is metered. There will be a
number of termination points in each TNI.
• For every TNI there is a Local Retailer (LR) and a FRMP (Financially
Responsible Market Participant)
• The consumption data is provided for each day and broken down into half
hour intervals.
• Statement type – AEMO produces data at regular intervals but due to
various reasons not all the data is available at publishing time. AEMO still
releases the data but depending on it’s completeness marks the data with a
STATEMENT_TYPE. The statement type is like a version control which
describes the quality of the information provided. The values are PRELIM,
REVONE, REVTWO and FINAL.
• Meter type is the classification of the meter. Ie BASIC, INTERVAL etc (See
the glossary of Terms for meter definition). The meter type has no impact
on your task
Your 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
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 13 of 16
• 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.
Filename of the output file will be in the format
_eg U1234567_01-APR-2020.xml
Note: I will provide the code to produce the xml data. You will need to
modify the given code to work in your environment. Change to the given
code will be a trivial exercise.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 14 of 16
Deliverables
Note I will be looking for a professional presentation produced by professionals.
In your submission you are expected to deliver
• Technical Design of your Solution.
• A working version of your solution. It is not acceptable to provide code that
will not run.
• I will be running your program in the database.
I will also be marking the code that is in the database so DO NOT provide a
hard copy of your code in your submission.
• Your submission will be via an email. The email will have the design
document as an attachment and you will provide me with your Database
userid [YOU MUST NEVER GIVE OUT YOUR PASSWORD]. I will run and
look at your code in the database and mark the email attached Design
document
Note: The database does a timestamp each time the code is changed or
compiled. Make sure you do not do anything that will update this timestamp
after the due date.
Note:
The Assignment code is to be written in a package. The package should be named
PKG_2020Main. To allow me to automate the marking process, you should
name your entry module RM16_forecast.
I will issue the following command to run your program
BEGIN
PKG_2020Main.RM16_forecast;
END;
(If the call fails then it is not easy to run )
You should also honor the table names that I have given you. Any additional
object that you create can be at your discretion, but I will be looking for the
documentation for these objects in your Technical Spec. Your document should
include my ER Diagram and if you add any additional objects you should
document them there
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 15 of 16
You are free to create other packages but remember that the purpose of a
package is to Amalgamate like code so generally one application, one package.
But if you feel that you wish to amalgamate your general library functionality into
a separate module then it may be appropriate to create a second package. Any
more than two packages is very strongly discouraged.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 16 of 16
Marking Scheme
Total weight of Assignment 50%
Design of the Problem Solution 10
Professional Presentation 5
Compiles Correctly and is easy to run 5
Code Modularization 5
Appropriate commenting and documentation 5
Variable Naming (self documenting) 10
Appropriate exception handling 10
Is the code tight and concise 15
Is the code easily maintainable 5
Run against the full data, how did the code perform 10
XML File produced 15
General look and feel of the code 5
As a guide these are the sort of things that I look for
Design
Can I understand how the solution is implemented by reading the design
document.
Presentation
Diagrams, E-R Diagram (Even if you simply copy mine), Language, Font, Graphics,
Index Page, Headings, headers and footers etc
Does it look good?
Modularization
Is the code in a package?
In the package, has the code been split into procedures and function
ie broken down to manageable modules. The term cohesion and coupling should
be paramount in your thinking.
Maintainable
Are values repeated? Eg Date format DD-MON-YYYY
A parameter table should be used to manage hard coded components of your
code.
Does the same code repeat throughout the solution?
Faculty of Engineering and Information
Technology
Subject Database Programming (31253)
Assignment
Autumn 2020
Assignment Title
Forecasting Electricity Demand
Due date - TBA
I will announce the delivery method
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 2 of 16
INSTRUCTIONS
1. This is an individual assignment and is worth 50% of your final grade. The Assignment will
require a commitment of about 25 hours and addresses objectives 1, 2, 3, 4 and 5 as outlined
in the Subject Outline.
2. For ease of marking, I would like you to document your approach to the solution of the problem.
Please keep your description short, relevant and to the point. It is strongly recommended that
you utilize diagrams to convey your ideas. Your description will help me understand your code
better. Please indicate any features that you are particularly proud of or that you want to be
noted during the marking process.
3. You are expected to submit a professional presentation in a soft copy format, prepared using a
suitable Word Processor. A hand written submission is not acceptable and will not be marked.
4. I will be looking at your code and data in the Database, so please DO NOT include any code in
your submission.
5. Please include your ORACLE userid on the front page of your submission where I can easily
find it. DO NOT INCLUDE YOUR PASSWORD
Also please provide your email address so the marking sheet can be sent to you.
6. There may be errors and ambiguities in the assignment specification. If so,
corrections/clarifications will be posted to the subject site on UTSOnLine. You are expected to
regularly check the site and incorporate any changes into your submission. The specification
will be frozen two weeks before the due date.
7. Please take note of the due date, and work to that date, as extensions without penalty will be
granted only under exceptional circumstances. Students should be aware that a penalty will be
applied for late submissions.
Penalty is as follows
+ Up to one week late incurs a 20% penalty.
+ Up to two weeks late incurs a 40% penalty.
+ Assignments which are more than two weeks late will not be accepted.
8. I expect to return the marked assignments no later than 14 days after the submission date.
Since I will not see you to hand back the Assignment and the marking sheet please include your
email address on your submission so that I can email the marks to you.
I will send you a comment sheet which will include the breakdown of marks and my observations
noted while running/marking your code.
9. Students are advised to make themselves familiar with the Academic Misconduct statement
detailed in the Student Guide and conduct themselves according to the expectations of the
University.
10. It has been noted in the past that due to lack of industry experience, many students find it
difficult to understand some of the Assignment requirements. To aid in understanding I will
utilize UTSOnLine extensively to convey ideas and to clarify any misunderstandings. As part of
this subject you will be expected to check the postings on UTSOnLine regularly.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 3 of 16
Table of Contents
INSTRUCTIONS ............................................................................................... 2
Document History ............................................................................................ 3
Glossary of Terms ............................................................................................ 4
Overview ......................................................................................................... 5
Information to help Understanding .................................................................... 6
Distribution regions in NSW ........................................................................... 6
Diagrammatic Representation of Power Distribution ........................................ 7
Database specifics............................................................................................ 8
NEM_RM16 table .......................................................................................... 8
DBP_HOLIDAY .............................................................................................. 8
MESSAGE Logging ........................................................................................ 8
Additional Objects ....................................................................................... 10
Database Diagram ...................................................................................... 11
Specification of the RM16 report ..................................................................... 12
Your Task................................................................................................... 12
Deliverables ................................................................................................... 14
Marking Scheme ......................................................................................... 16
Document History
31-MAR-2020 Initial DRAFT version released to students
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 4 of 16
Glossary of Terms
BASIC METER A meter that measures electricity flow as a continuous value. A
Speedo is an example of a Basic meter. Consumption is
calculated by subtracting one value from another.
INTERVAL METER A meter that measures electricity consumption as a continuous
flow but records the meter value each half hour interval.
TNI Terminal Node Identifier. It is a logical grouping of distribution
terminating points from the generator to the delivery point.
Physically, TNI’s comprise of many metering points from many
different generators
FRMP Financially Responsible Market Participant. This is the retailer
who is responsible for the purchase and the resale of electricity.
The purchase is from AEMO and the sale is to the customer base
in the TNI.
LR Local Retailer. This is the Market Participant that is responsible
for the Transmission and delivery of the electricity to a region.
When the electricity market was government controlled an LR
was allocated to maintain electricity supplies to a region
STATEMENT TYPE AEMO produces RM16 statements at regular intervals. The full
volume of the electricity consumption may not be known at the
time that the statement is produced. Statement type then
denotes the status of the report. The values are PRELIM,
REVONE, REVTWO, FINAL
RM16 Is a statement issued by AEMO each week. The statement is
issued to each market participant and shows only their
consumption for a TNI in a given day. The consumption volumes
are provided at half hour intervals.
Market Participant The electricity market is made up of a number of generators and
retailers. The market can be looked at as a pool where the
generators add to the pool (sell) and the retailers buy from the
pool to service their customers. AEMO is the controller of the pool
and all settlements are done with AEMO.
The individual organizations, whether generators or retailers are
the Market Participants.
MDP Meter Data Provider. The code of the organization that reads the
meter
CHANGE_DATE The field is updated every time any change is performed on the
record
AEMO Australian Energy Market Operator
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 5 of 16
Overview
There is considerable activity in the electricity industry at the moment with the
NSW Governments recent privatization the retail electricity industry. NSW has
joined Victoria, South Australia, Queensland and Tasmania to become part of the
deregulated Electricity market.
As a result, electricity retailers will be canvassing existing customers to sign up
with them and have their electricity supplied by them. The reality of course is that
the customers’ electricity will still be produced by the same generators and
delivered to the customers’ home by the same distribution network. What will
change though is who will be responsible to ensure that the correct volume of
electricity is produced to satisfy demand and who will be the billing agent that the
clients will pay their electricity bills to.
In order to make the electricity market function, all the retailers and generators
participate in what is known as an Electricity Market.
The market is a pool where the producers bid to supply and the retailers bid to
buy from to supply their customers. This pool is managed and controlled by a
government authority called AEMO (Australian Energy Market Operator). The
buying and selling of the electricity is done by electricity traders working for the
various Energy companies like Energy Australia, Origin, Alinta, TRUEnergy and
many others.
The bidding process by the sellers and buyers is what determines the electricity
price and this price is set at 5 minute intervals. AEMO aggregates the 5 minute
prices into half hour intervals and publishes these prices continually.
The electricity traders need to be able to determine as accurately as possible what
will be the total consumption for their consumers is in order to be able to
purchase the right amount of energy and capitalize on market fluctuations at each
one of the half hour intervals.
AEMO produces a historical consumption report at regular intervals and provides
the consumption data for each of the participants in the market. Each participant
report is for the total volume of that participant only, broken down by LR and
FRMP regions. This report is known as a RM16 report and is produced and
distributed weekly. This data will be stored in a table called NEM_RM16
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 6 of 16
Information to help Understanding
Distribution regions in NSW
The map of NSW shows how the state is divided into distribution boundaries
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 7 of 16
Diagrammatic Representation of Power Distribution
Showing how many generators provide power to one TNI
TNI TNI
TNI
TNI
TNI
Market Region
Generator
Generator
Electricity is distributed using high voltage cables from the various generators that are
located throughout the states.
Each market region has many terminating points where the electricity flow is metered,
stepped down in voltage and then distributed to the consumers.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 8 of 16
Database specifics
NEM_RM16 table
The RM16 data from AEMO is delivered into a table called NEM_RM16.
The consumption data is stored at half hour intervals.
The NEM_RM16 has an object as an attribute. The object is called VALUE_HH.
The VALUE_HH object is a nested table object of type RM16_DAY which in turn is
an array of 48 objects RM16_HH which is a record of two elements HH and
VOLUME. This object is used to represent the 48 daily half hour elements and
their associated consumption volume.
The script to create the objects and the RM16 tables will be made available on
UTSOnLine
You will create your own LOCAL_RM16 table to store your calculated FORECAST
values. Your table will be a direct copy of either my table with the object or my
view with the object unwrapped into individual attributes. This view is named
V_NEM_RM16. You a free to use either the object or the unwrapped version of
the object It all depends on what you are most comfortable with
If you chose to go with the object then the details on how to work with the object
will be provided in class and on UTSOnLine.
The basic select statement is
SELECT r.* , v.*
FROM NEM_RM16 r, TABLE(VALUE_HH) v
Do not copy my tables into your schema. When marking I will set test data in the
table in my schema. If you copy the table into your schema then your program
will not see the test data that I generate. Look at the ER Diagram. You will be
creating tables LOCAL_RM16, DBP_PARAMETER and the RUN_TABLE as a
minimum in your schema. You are of course free to create any other tables that
are appropriate to your system design
DBP_HOLIDAY
This table has the list of days that are deemed holidays. If a date appears in this
table then it is to be treated as a holiday. Please do not copy my table into your
schema. During marking I will set test data in my table. If you copy the table into
your schema then your program will not see this test data when I mark.
MESSAGE Logging
Your program should log its progress into a log table. This table is called
DBP_MESSAGE_LOG. You do not have direct access to insert into this table, rather
a procedure has been developed to enable you to log your messages.
The procedure is called COMMON.LOG.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 9 of 16
How to log
In your code issue the following statement
COMMON.LOG(
Eg COMMON.LOG('Before updating the LOCAL_RM16 table ');
To view your message
SELECT *
FROM DBP_MESSAGE_LOG
WHERE USERID = USER
ORDER BY MSG_DATE desc;
RUN Table
Your forecasting system will run each day and forecast the electricity demand two
weeks into the future. You are asked to create and maintain a RUN table
which should keep track of the run dates and the status of each run. There should
be only one record in the run table for your program per day
There are two things that your run table should check
• Your program should only ever run once per day.
• You must use the run table to ensure that only one instance of the
program is to run at any one time. If the program is already running it
cannot run again. If there is a large volume of data then your program may
take some time to complete. If the program is restarted while another
session is running then a log message is to be written into the logging table
and the second program instance should terminate gracefully. No entry into
the Run table is required.
The table specification is given to you in the supplied ER Diagram. I will also give
you the sql to create this table.
DBP_PARAMETER Table
To avoid and/or minimize repeated code I will ask that you create a Parameter
table. The specifications are given to you in the supplied ER Diagram. I will also
give you the SQL to create the table.
You are to place any constants into this table, rather than hard coding the values.
Having the values in the table allows the users to change those values. If the
values are hardcoded in the program then a code change and subsequent recompilation
is required to make the changes.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 10 of 16
Additional Objects
It is unlikely that you will require additional tables, however if you feel that your
solution will be enhanced by additional tables, views or any other object then you
are free to create as many additional objects as you like.
But please be aware – Do not duplicate any tables in your schema that are a
copy of my tables and data. The data source for your program are the tables in
my schema. Your program output will go into the table that you create in your
schema. This will be LOCAL_RM16
During marking I will be manipulating data in my schema to check how your
program behaves. If you copy tables and data into your schema and write your
program to access your data instead of mine then the changes I make during run
time will not be reflected in your program run. Your program will then not work
correctly and this will be reflected in your mark.
You are free though to create any additional objects that you feel will help your
program to work efficiently. One such table may be your local copy of the
parameter table. You could use that to store your program specific parameters.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 11 of 16
Database ER Diagram
The view v_NEM_RM16 is created with the query
CREATE OR REPLACE FORCE VIEW dbp_admin.v_nem_rm16 (company_code,
settlement_case_id,
AS
SELECT r.company_code, r.settlement_case_id, r.settlement_run_id,
r.statement_type, r.tni, r.metertype, r.frmp, r.lr, r.mdp,
r.change_date, r.DAY, r.transaction_id, v."HH", v."VOLUME"
FROM nem_rm16 r, TABLE (r.value_hh) v;
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 12 of 16
Specification of the RM16 report
The RM16 report is broken down as follows
• Each state is broken into a number of Distribution areas referred to as
Patches.
• Each patch in divided into a number of TNI’s (Terminal Node Identifiers).
These are the logical divisions where the very high voltage cables from the
generators terminate and the flow of power is metered. There will be a
number of termination points in each TNI.
• For every TNI there is a Local Retailer (LR) and a FRMP (Financially
Responsible Market Participant)
• The consumption data is provided for each day and broken down into half
hour intervals.
• Statement type – AEMO produces data at regular intervals but due to
various reasons not all the data is available at publishing time. AEMO still
releases the data but depending on it’s completeness marks the data with a
STATEMENT_TYPE. The statement type is like a version control which
describes the quality of the information provided. The values are PRELIM,
REVONE, REVTWO and FINAL.
• Meter type is the classification of the meter. Ie BASIC, INTERVAL etc (See
the glossary of Terms for meter definition). The meter type has no impact
on your task
Your 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
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 13 of 16
• 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.
Filename of the output file will be in the format
Note: I will provide the code to produce the xml data. You will need to
modify the given code to work in your environment. Change to the given
code will be a trivial exercise.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 14 of 16
Deliverables
Note I will be looking for a professional presentation produced by professionals.
In your submission you are expected to deliver
• Technical Design of your Solution.
• A working version of your solution. It is not acceptable to provide code that
will not run.
• I will be running your program in the database.
I will also be marking the code that is in the database so DO NOT provide a
hard copy of your code in your submission.
• Your submission will be via an email. The email will have the design
document as an attachment and you will provide me with your Database
userid [YOU MUST NEVER GIVE OUT YOUR PASSWORD]. I will run and
look at your code in the database and mark the email attached Design
document
Note: The database does a timestamp each time the code is changed or
compiled. Make sure you do not do anything that will update this timestamp
after the due date.
Note:
The Assignment code is to be written in a package. The package should be named
PKG_2020Main. To allow me to automate the marking process, you should
name your entry module RM16_forecast.
I will issue the following command to run your program
BEGIN
PKG_2020Main.RM16_forecast;
END;
(If the call fails then it is not easy to run )
You should also honor the table names that I have given you. Any additional
object that you create can be at your discretion, but I will be looking for the
documentation for these objects in your Technical Spec. Your document should
include my ER Diagram and if you add any additional objects you should
document them there
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 15 of 16
You are free to create other packages but remember that the purpose of a
package is to Amalgamate like code so generally one application, one package.
But if you feel that you wish to amalgamate your general library functionality into
a separate module then it may be appropriate to create a second package. Any
more than two packages is very strongly discouraged.
University of Technology
Faculty of Engineering and Information Technology
Database Programming
Assignment - Autumn 2020
Page 16 of 16
Marking Scheme
Total weight of Assignment 50%
Design of the Problem Solution 10
Professional Presentation 5
Compiles Correctly and is easy to run 5
Code Modularization 5
Appropriate commenting and documentation 5
Variable Naming (self documenting) 10
Appropriate exception handling 10
Is the code tight and concise 15
Is the code easily maintainable 5
Run against the full data, how did the code perform 10
XML File produced 15
General look and feel of the code 5
As a guide these are the sort of things that I look for
Design
Can I understand how the solution is implemented by reading the design
document.
Presentation
Diagrams, E-R Diagram (Even if you simply copy mine), Language, Font, Graphics,
Index Page, Headings, headers and footers etc
Does it look good?
Modularization
Is the code in a package?
In the package, has the code been split into procedures and function
ie broken down to manageable modules. The term cohesion and coupling should
be paramount in your thinking.
Maintainable
Are values repeated? Eg Date format DD-MON-YYYY
A parameter table should be used to manage hard coded components of your
code.
Does the same code repeat throughout the solution?