University of Technology, Sydney
Faculty of Engineering and
Information Technology
School of Software
Subject Database Programming (31253)
Assignment
Autumn 2019
Due date - 30-MAY-2019 at midnight
Submit via email ….. Address will be provided
INSTRUCTIONS
1. This assignment is worth 50% of your final grade and requires a commitment of about 35 hours
from each student..
2. Your task is to produce a working solution to the problem given in this specification. The
solution will be written in the PL*Sql language and will run in your schema in the LOZ database.
Please be aware that you must produce code that compiles and runs.
3 You will be producing a report which will be a Terminal Usage report. I would like you to provide
me with a design document for that report. You should include an ER diagram showing where
the source of the report data is.
4. You are expected to submit a professional presentation for your design document, prepared
using a suitable Word Processor. A hand written submission is not acceptable and will not be
marked.
Your submission should include your ORACLE userid
DO NOT INCLUDE YOUR PASSWORD
5. A hard copy of your code should not be included in the submission, only the design document
for the report. The code will be marked in the database.
6. I will be looking at and running your code in the Database. Once the assignment is handed in
you must NOT recompile or modify any component of your system. I will be checking the object
modification dates and if any object has been modified after the due date the assignment will
not be marked.
7. There may be errors and ambiguities in the assignment specification. If so,
corrections/clarifications will be posted to the subject web sites. You are expected to check and
incorporate these changes into your submission. The specification will be frozen one week
before the assignment is due.
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 2 of 15
8. I will be utilizing UTSOnline extensively to answer questions on specification and to provide
details of the existing data. I will assume that you will be checking the site regularly to make
yourself aware of the latest developments.
9. Please take note of the due date, and work to that date, as extensions will be granted only
under extenuating circumstances. Late submissions are to be negotiated with the lecturer.
Students should be aware that a penalty of up to 50% may be applied for late submissions.
Assignments submitted later than two weeks will not be accepted
10. Students are advised to make themselves familiar with the Academic Misconduct statement
detailed in the Subject Outline and conduct themselves according to the expectations of the
University
11. I expect to return the marked Assignments 14 days after the submission date.
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 3 of 15
Modification History
Please Note
Modifications to the Assignment specification will freeze one week prior to the due
date
Date Comment
25-MAR-2019 Document Created
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 4 of 15
The Smartcard Financial Settlement System
Introduction
For the Assignment for Database Programming and Administration you are
required to write a Financial Settlement System (FSS) for a Smartcard Transaction
Centre.
The Deliverables for the Assignment are structured in a way to enable you to
achieve a mark that is related to the amount of work that you will contribute and
the level of knowledge that you have attained. The detailed marking structure can
be found at the rear of the document.
Overview
For some time now there has been a trial deployment of a Smartcard System in
various locations throughout the country. A number of different types of Smartcard
terminals have been deployed at selected sites. The types of terminals include
Parking Meters, Payphones, various Vending machines and ticketing machines at
selected railway stations. A number of Smartcard enabled terminals have also
been placed in selected retail outlets like Newsagents and University canteens
enabling the holders of the Smart cards to pay for their purchases using these
cards.
The term electronic cash is often used when talking about Smartcard transactions;
however the electronic cash needs to be converted to real cash so that the
merchants, accepting the cards as payment, can be reimbursed. During the trial
phase, the merchants were reimbursed manually, once a week. The settlement
amount calculations have been done by the staff in our IT department and the
merchants were sent a cheque for the amount of the settlement. During the initial
trial phase the merchants were not charged a fee on the Smartcard transactions.
The Smartcard System is moving into the next phase of deployment. The number
of merchants will be increased and there is a requirement to automate the
Settlement process. The payment to the Merchants will be done via a direct credit
into their nominated bank accounts and is to be done daily.
Each month the merchants will be charged a fee for the use of the Smartcard. The
fee to be charged will be a percentage of the total transactions for the month. The
actual amount is yet to be negotiated and it will be uniform for every transaction.
The fee collection will be done via a direct debit from the merchant bank account.
The merchant will also be sent a statement showing the money banked and the
fees charged for the month. NOTE: that the Monthly Settlement is not part of the Assignment.
It is here only to help in understanding the system.
Your task is to write the application for the Smartcard Financial Settlement
System. You are required to only create the daily settlement system and
associated report. The application is to run in the Oracle Database and is to be
written using the PL*Sql language.
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 5 of 15
The components of the application are
The Daily Settlement file and a corresponding report
A report to show terminal usage
System control using a RUN table
An email to a nominated recipient with the Banking report file as an
attachment
The details of the application follow.
Daily Settlement
Deskbank File
The FSS system will be required to run daily and at the conclusion of the run, will
produce a banking file that will be known as a Deskbank file. The Deskbank file
will be sent to the designated banking organization electronically, most likely using
a secure FTP channel.
The Deskbank file, when run in the banking system will contain information
necessary to credit the merchants bank account with the amount collected by the
Smartcard transactions. The total of the deposits into the merchants accounts is to
be offset by a debit from our working bank account. The total of the deposits and
the debits is to reconcile to zero.
This file is intended to be read by the banks systems.
A sample Deskbank file and the file specification can be found in the Appendix
****See the Note on Minimum Settlement under System Constraints
Daily Settlement Report
In addition to the Deskbank file, your system is to produce a daily reconciliation
report. The report will be used by the business unit and will show the banking
details generated by the Daily Settlement. The report will be created automatically
when the deskbank file is produced, however your system should be flexible
enough to allow the manual creation for a given settlement date.
This file is intended to be read by humans.
A sample report is attached in the Appendix.
Terminal Usage Report
We need to keep track of the busiest terminals and the amount of traffic they
generate. A report is required for the business units.
Each time the report is run it should produce Terminal Usage data for the current
month, up to and including the current date.
The report specifications is provided.
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 6 of 15
RUN Table
The FSS system will run each day and should settle all those transactions that
have not yet been settled. You are asked to create and maintain a RUN table
which should keep track of the run dates and the status of each run.
You must use the run table to ensure that only one instance of the program is to
run at any time. If the program is already running it cannot run again. 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.
In order to minimize the banking costs we want to run only one settlement process
per day. Use the run table to ensure that only one settlement is run per day.
If the settlement was already run on any day then the program should log a
message to the log table and terminate gracefully.
If the program fails during the run it should log the failure reason to the logging
table, update the run table with a status of FAIL. Also the fail reason should be
written to the Run table so that production support know what needs to be
repaired. The run end column should be populated. In such a situation, where the
settlement failed, your program can be rerun on the same day. The assumption is
that production support will have fixed the problem with the data so they may wish
to run the settlement again. You MUST BE VERY CAREFULL however, that your
program does not duplicate the merchants settlements. This could send us
bankrupt.
The production support personnel will use the run table to assist them in
monitoring and maintaining the system in the event of failures. One record should
be created in this table for each run of the program.
Also, you are asked to maintain a log of your program runs which will enable the
production support team to monitor the progress of your run. The log table will
contain a timestamp and a periodic entry to mark the progress of your system.
Note: The logging is to be done by using the COMMON.log procedure which will
be made available to you. I will show you which table the COMMON.log procedure
utilizes for logging, You are of course free and encouraged to develop your own
logging process.
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 7 of 15
Email the Daily Settlement Report to a nominated person
This component is optional and only those students that wish to take on the
challenge can attempt this.
The Daily Settlement Report once created can be emailed to a nominated
recipient. I will provide you with the bulk of the code you need to generate the
email. You will need to modify the code I give you to add the file as an attachment
to the email. The code and the full details will be provided on UTS Online.
Paramatising your code
So that your system is easily maintained once in production and to reduce the
possibility of changes to the code I would like you to create a parameter table. The
purpose of the parameter table is to store all the constants that you may hardcode
in the code. Example of constraints that you will paramatise are date formats,
email addresses etc.
Having the ability to paramatise your code will greatly reduce the maintenance and
greatly increase the flexibility of your code.
Your table will be named DBP_PARAMETER
I will supply the code to create the table. You are free to create any additional
attributes in the table but you must have the columns that I provide as a minimum.
System Constraints
The FSS system should settle the transactions only once. It is possible that
a user could restart the program multiple times on a given day. You should
ensure that the settlements are not duplicated. In the cases where the user
tries to run the application more than once on a given day, the system
should not allow. This is to be controlled by the run table. If the program
fails on a day then a rerun is allowable but it is vital that
Settlements are not duplicated.
Each transaction is settled if the merchant total is greater than
the minimum amount
The Daily reports and the production of other reports should be re-runnable
by the users at any time and for any given date.
The existing FSS system is located on ORALAB in the DBP_ADMIN
schema. Select privilege has been granted on all objects in the schema to
enable you to view the existing data. Your FSS system, when completed
will make up the Smartcard system. It will be standalone, but will also be
integrated into the existing tables. You should not make any modifications
to the existing structure because your changes might ‘break’ other
components of the system.
The E-R Diagram and specifications for the existing database tables can be
found on UTSOnline under the Assignment tab.
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 8 of 15
The daily transaction downloads, from the terminals, will be available in the
database environment and the transaction table will be constantly uploaded
with new transactions as they are downloaded from the terminals. This will
be done by an existing automated process.
It is anticipated that the transaction tables will grow very quickly. We will
adopt an archiving strategy to move the data from the production
environment into an, as yet, undefined environment. You can not assume
that the transaction data is always available for your use. Note: Archiving of
the transaction data is not in the scope of this assignment
The bank will charge us a fee for all banking transactions. For this reason
we will not be settling trivial amounts each time the program runs. Total
settlement amounts for a merchant that are less than the designated
minimum amount will not be settled in the daily transactions until the total
settlement amount, during the month, reaches the minimum settlement
amount. At the end of the month, any transactions that have not been
processed during the month are to be finalized, irrespective of the amount.
The minimum settlement amount is configurable and is stored in the
FSS_REFERENCE table under the identifier of Daily Minimum Settlement
The reload of the Smartcard and the subsequent collection and banking of
the reload money is not a component of this system.
The Deskbank file name will take the following format
[StudentNumber]_DS_DDMMYYYY.dat for the daily file and
The report file name format is
[StudentNumber]_DSREP_DDMMYYYY.rpt
Note that DDMMYYYY denote the day, month, year of the settlement date when the
deskbank file is created.
When your system runs it will produce three files and an email.
The files are
The deskbank banking file
The Banking report file
The Terminal Usage report
An email will be sent to a nominated recipient with the Settlement report as
an attachment (Optional)
So that life is made easier for Laurie while automatically testing the system,
you must follow the naming convention below
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 9 of 15
Module Name
Package Pkg_FSS_Settlement
Daily Settlement DailySettlement
Daily Banking Summary DailyBankingSummary
Terminal Usage TerminalUsage
Example
To run the daily settlement I will type
Pkg_FSS_Settlement.DailySettlement;
To run a report for today I will type
Pkg_FSS_Settlement.DailyBankingSummary
To run a report manually for a different date, say 18-MAR-2019 I will type
Pkg_FSS_Settlement.DailyBankingSummary(‘18-MAR-2019’)
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 10 of 15
Elements of the Smartcard System
Below is a picture gallery that illustrates some of the elements that go to
make up the Smartcard System
Some of the Smartcard terminals to be
located in the merchant premises
Vending machines with Smartcard
readers
Payphone Installed with a
Smartcard reader
Contact less Smartcard readers at a
railway station
Smartcard enabled Parking meters
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 11 of 15
0 01WBC S/CARD BUS PAYMENTS 038759INVOICES 160519
1015-010270249893 500000022905CARDORAMA F 201905160000431032-797 001006SMARTCARD TRANS 00000000
1032-099000701123 500000078400STELLA PICTURE CO P/L F 201905160000432032-797 001006SMARTCARD TRANS 00000000
1032-277000892386 500013659772TELSTRA PAYPHONE SERVICES F 201905160000433032-797 001006SMARTCARD TRANS 00000000
1034-002000136556 130013923599S/CARD BUS PMTS N 800900000000000032-797 001006SMARTCARD TRANS 00000000
1062-164010171526 500000025060DUNCANS -CATERING - LOFTUS F 201905160000435032-797 001006SMARTCARD TRANS 00000000
1083-001648518574 500000002300THE SMITH'S SNACKFOOD CO. LTD F 201905160000436032-797 001006SMARTCARD TRANS 00000000
1096-006006623452 500000081640GERALDTON HEALTH SERVICES F 201905160000437032-797 001006SMARTCARD TRANS 00000000
1105-120954269240 500000003102THE UNIVERSITY OF TECHNOLOGY F 201905160000438032-797 001006SMARTCARD TRANS 00000000
1105-134506242640 500000050420FLORUM P/L T/A RUNDLE ARCADE NEW F 201905160000439032-797 001006SMARTCARD TRANS 00000000
7999-999 000000000000139235990013923599 000009
The Deskbank File Specifications
Create the Header record as follows :
FIRST RECORD - TYPE 0 1 record
Type Zero Descriptive Record
Posn. Size Field Comments
1 1 Record Type Zero (0)
2 17 Not Used Blanks
19 2 Reel Sequence Start at 01
21 3 F.I. Code "WBC" (Bank mnemonic code)
24 7 Not Used Blanks
31 26 User "S/CARD BUS PAYMENTS"
57 6 User B.S.B. 038759
63 12 Description "INVOICES"
75 6 Processing Date “DDMMYY" format"
81 40 Not Used Blanks
TRANSACTION RECORD - TYPE 1 many records
Type One Detail Record
Posn. Size Field Comments
1 1 Record Type 1
2 7 B.S.B. BSB in 999-999 format
9 9 Account No. Bank Account
18 1 Not Used Blank
19 2 Tran. Code 13 -- debit, 50 -- credit
21 10 Value Zero filled, in cents.
31 32 Title Merchants's Account title
63 3 BankingFlag ‘ F ’ Ledger code
66 15 Lodgement Ref. TRANSACTION_SEQ_NUMBER
81 16 Trace "032-797 001006"
97 16 Remitter eg. "SMARTCARD TRANS"
116 8 GST Tax Zeroes
Create the Footer record as follows :
LAST RECORD TYPE 7 - 1 record
Type Seven File Total Record
Posn. Size Field Comments
1 1 Type 7
2 7 Filler "999-999"
9 12 Not Used Blanks
21 10 File total Zero filled, in cents.
31 10 Credit total Zeroes filled in cents.
41 10 Debit total Zero filled, in cents.
51 24 Not Used Blanks
75 6 Record Count Number of Data records
81 40 Not Used Blanks
Header Record
Data Record
Footer Record
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 12 of 15
A note on the dates on the above report.
The report could be reprinted for any settlement date in the past. The dates that
are shown are for the date that the report is printed and the date that the
settlement was processed.
SAMPLE DAILY BANKING REPORT
SMARTCARD SETTLEMENT SYSTEM
DAILY DESKBANK SUMMARY
Date DD-Mon-YYYY Page x
Merchant ID Merchant Name Account Number Debit Credit
---------- ---------------------------------- ----------------- ---------- ---------
700000100 CARDORAMA 015-010270249893 229.05
700000200 STELLA PICTURE CO P/L 032-099000701123 784.00
700000300 TELSTRA PAYPHONE SERVICES 032-277000892386 136597.72
700000400 DUNCANS -CATERING – LOFTUS 062-164010171526 250.60
700000500 THE SMITH'S SNACKFOOD CO. LTD 083-001648518574 23.00
700000600 GERALDTON HEALTH SERVICES 096-006006623452 816.40
700000700 THE UNIVERSITY OF TECHNOLOGY 105-120954269240 31.02
700000800 FLORUM P/L T/A RUNDLE ARCADE NEW 105-134506242640 504.20
S/CARD BUS PMTS 034-002000136556 139235.99
------------- -------------
BALANCE TOTAL 139235.99 139235.99
Deskbank file Name : <file name>
Dispatch Date : DD Mon YYYY
****** End of Report ******
Daily Banking Summary Report
Settlement Date
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 13 of 15
The Terminal Usage Report will be prepared automatically each time the
settlement system runs. The report when run should produce the Terminal usage
data for the current month up to and including the run date.
The report data should be ordered so that the most active terminal, in terms of
Total Transaction Amount, appears first then all the others in descending order.
The report is a compulsory component of the Assignment and must be produced.
TERMINAL USAGE REPORT
SMARTCARD SETTLEMENT SYSTEM
TERMINAL USAGE REPORT
Report Date: 15-Jun-2019 Page x
Usage Month: April 2019
TerminalID Terminal TerminalType Merchant Number of TotalTransaction
Type Description Name Transactions Amount
---------- -------- --------------------- ----------------------------- -------------- ----------------
0022000010 VMS Snack Vending Machine The Smith's Snackfood Co. Ltd 525 $345.56
--
--
****** End of Report ******
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 14 of 15
Assignment Marking Scheme
Assignment Weight = 50%
Create a daily Deskbank file for delivery to the bank 40
Daily banking report for the business unit. Should be
produced for any nominated date
20
Implement RUN Table correctly for program control 15
Terminal Usage Report 10
Email banking report to a nominated recipient 15
TOTAL 100
Note
The first four items of the list are mandatory and should be produced by each
student as a minimum. The items are
Create a daily Deskbank file for delivery to the bank 40
Daily banking report for the business unit 20
Implement RUN Table for production support 15
Terminal Usage Report 10
University of Technology
Faculty of Engineering and
Information Technology
Database Programming
Assignment - Autumn 2019
Page 15 of 15
Glossary of Terms
Deskbank File
A fixed width file that is used to communicate with the banking
system. The file contains the details of the direct debits and credits
that are to be carried out by the bank on our behalf. The file is
designed to be read by the banks computer system. It is crucial that
the specification of the file is adhered to. If not, the banking system
will fail and we will incur a financial penalty
Transaction Date
This is a date that a transaction is made and is recorder by the
terminal. The date is unreliable because we do not have control over
the terminal
Download Date This is the Date that a transaction was downloaded into the system.
The date is recorder by the Smartcard server.
Transaction Code
The values are either 13 or 50.
13 is the code for a deposit into the nominated account
50 is the code for a withdrawal from the nominated account
Banking Flag
This value is historic and is used by some systems. Our system will
not use this value; however the banking system requires that this
value is present. You should hardcode a suitable value.
Lodgment Ref
This is a unique sequence number created by concatenating the
date with a unique number for the day. This attribute should be used
to link all the transactions for a merchant throughout the daily
settlement
Trace This is a hard coded value and is required by the deskbank system
File Total A sum of the debits and the credits
Credit Total A SUM of all the credit statements
Debit Total A SUM of all the debit totals
Record Count A number of records in the deskbank file, not including the header
and the footer
Processing Date The date and time that the transactions were processed and the
deskbank file was produced
Settlement Date The date that the transactions were settled for each merchant
Merchant Storekeeper or any person or company that trades goods or services
in return for payment
版权所有：编程辅导网 2021 All Rights Reserved 联系方式：QQ:99515681 微信：codinghelp 电子信箱：99515681@qq.com
免责声明：本站部分内容从网络整理而来，只供参考！如有版权问题可联系本站删除。
