Subject
INSTRUCTIONS
School of Software
Database Programming (31253)
Assignment Autumn 2018
Due date – 31-MAY-2018 at midnight Submit via email ….. Address will be provided
University of Technology, Sydney
Faculty of Engineering and Information Technology
1. 2.
3.
4.
5. 6.
7.
This assignment is worth 50% of your final grade and requires a commitment of about 35 hours from each student..
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.
You are to produce a design document of your code. Another developer should be able to build your system from your design. This is required in case we contract the work out. It is strongly recommended that you utilize diagrams to convey your ideas and design. An ER diagram is a must in the submission, after all this is a database environment. Please indicate any features that you are particularly proud of or that you want to be noted during the marking process.
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
A hard copy of your code should not be included in the submission, only the design document. The code will be marked in the database.
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.
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
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
incorporate these changes into your submission. The specification will be frozen one week before the assignment is due.
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.
Page 2 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
Modification History
Please Note
Modifications to the Assignment specification will freeze one week prior to the due date
Date |
Comment |
18-MAR-2018 |
Document Created |
Page 3 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
Introduction
The Smartcard Financial Settlement System
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.
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.
Page 4 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
The components of the application are
- The Daily Settlement file and a corresponding report
- A report to identify any potential fraud
- System control using a RUN table
- An email to a nominated recipient with the Banking report file as anattachment
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 ConstraintsDaily 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.
Fraud Report
The security department’s role is to maintain the integrity and the validity of the Smartcard system. They have asked for a Fraud report.
The report is to list all those cards and the associated transactions where there is suspicion of fraud.No sample of the report exists so you are free to design a sensible report format of your own.
Page 5 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
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.
Page 6 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
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.
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 thanthe 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.
- 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
Page 7 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
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 fileThe fraud report (Optional)
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 belowExample
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-2018 I will type Pkg_FSS_Settlement.DailyBankingSummary(‘18-MAR-2018’)
Module |
Name |
Package |
Pkg_FSS_Settlement |
Daily Settlement |
DailySettlement |
Daily Banking Summary |
DailyBankingSummary |
FraudReport |
FraudReport |
Page 8 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
Elements of the Smartcard System
Below is a picture gallery that illustrates some of the elements that go to make up the Smartcard System
Smartcard enabled Parking meters
Vending machines with Smartcard readers
Some of the Smartcard terminals to be located in the merchant premises
Contact less Smartcard readers at a railway station
Payphone Installed with a Smartcard reader
Page 9 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
Header Record
Create the Header record as follows : FIRST RECORD – TYPE 0 1 record
Type Zero Descriptive Record
Posn. Size 1 1 2 17
19 2
21 3
24 7
31 26
57 6
63 12 Description “INVOICES”
75 6 Processing Date “DDMMYY” format” 81 40 Not Used Blanks
Field Comments
Zero (0)
Record Type Not Used Reel Sequence F.I. Code Not Used User
Blanks Start at 01
User B.S.B.
"S/CARD BUS PAYMENTS" 038759
"WBC" (Bank mnemonic code) Blanks
Data Record
TRANSACTION RECORD – TYPE 1 many records Type One Detail Record
Posn. Size
- 1 1
- 2 7
9 9
- 18 1
- 19 2
21 10 31 32 63 3 66 15 81 16 97 16
116 8
Field Record Type B.S.B. Account No. Bank Account
Comments 1
BSB in 999-999 format
Blank 13 -- debit, 50 -- credit Zero filled, in cents. Merchants's Account title
Not Used
Tran. Code
Value
Title
BankingFlag ‘ F ’ Ledger code Lodgement Ref. TRANSACTION_SEQ_NUMBER
Trace Remitter GST Tax
"032-797 001006" eg. "SMARTCARD TRANS" Zeroes
Footer Record
Create the Footer record as follows : LAST RECORD TYPE 7 – 1 record
Type Seven File Total Record Posn. Size Field Comments
1 1 2 7 9 12
21 10 31 10 41 10 51 24 75 6 81 40
Type 7 Filler “999-999”
Not Used File total Credit total Debit total Not Used Record Count Not Used
Blanks Zero filled, in cents. Zeroes filled in cents. Zero filled, in cents. Blanks Number of Data records Blanks
Page 10 of 13
The Deskbank File Specifications
0 01WBC S/CARD BUS PAYMENTS
1015-010270249893 500000022905CARDORAMA
1032-099000701123 500000078400STELLA PICTURE CO P/L
1032-277000892386 500013659772TELSTRA PAYPHONE SERVICES
1034-002000136556 130013923599S/CARD BUS PMTS
1062-164010171526 500000025060DUNCANS -CATERING – LOFTUS
1083-001648518574 500000002300THE SMITH’S SNACKFOOD CO. LTD
1096-006006623452 500000081640GERALDTON HEALTH SERVICES
1105-120954269240 500000003102THE UNIVERSITY OF TECHNOLOGY
1105-134506242640 500000050420FLORUM P/L T/A RUNDLE ARCADE NEW F 201805160000439032-797 7999-999 000000000000139235990013923599 000009
038759INVOICES 160518 F 201805160000431032-797
001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000 001006SMARTCARD TRANS 00000000
F 201805160000432032-797 F 201805160000433032-797 N 800600000000000032-797 F 201805160000435032-797 F 201805160000436032-797 F 201805160000437032-797 F 201805160000438032-797
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
Daily Banking Summary Report
SAMPLE DAILY BANKING REPORT
Date DD-Mon-YYYY
Merchant ID Merchant Name
———- ———————————-
Settlement Date
700000100 700000200 700000300 700000400 700000500 700000600 700000700 700000800
CARDORAMA
STELLA PICTURE CO P/L
TELSTRA PAYPHONE SERVICES DUNCANS -CATERING – LOFTUS
THE SMITH’S SNACKFOOD CO. LTD GERALDTON HEALTH SERVICES
THE UNIVERSITY OF TECHNOLOGY FLORUM P/L T/A RUNDLE ARCADE NEW S/CARD BUS PMTS
Account Number Debit ----------------- ---------- 015-010270249893 032-099000701123 032-277000892386 062-164010171526 083-001648518574 096-006006623452 105-120954269240 105-134506242640 034-002000136556
Page x
Credit --------- 229.05 784.00 136597.72 250.60 23.00 816.40 31.02 504.20
BALANCE TOTAL
Deskbank file Name : <file name>
Dispatch Date : DD Mon YYYY
139235.99 139235.99
****** End of Report ******
SMARTCARD SETTLEMENT SYSTEM DAILY DESKBANK SUMMARY
139235.99
————- ————-
A note on the dates on the 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.
Page 11 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
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 |
Fraud Report |
10 |
Email banking report to a nominated recipient |
15 |
TOT AL |
100 |
Note
The first three 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 |
Page 12 of 13
University of Technology Faculty of Engineering and Information Technology Database Programming Assignment – Autumn 2018
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. |
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 |
Page 13 of 13