讲解：31253、Database Programming、SQ

University of Technology, SydneyFaculty of Engineering andInformation TechnologySchool of SoftwareSubject Database Programming (31253)AssignmentAutumn 2019 Due date - 30-MAY-2019 at midnight Submit via email ….. Address will be providedINSTRUCTIONS1. This assignment is worth 50% of your final grade and requires a commitment of about 35 hoursfrom 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, preparedusing a suitable Word Processor. A hand written submission is not acceptable and will not bemarked.Your submission should include your ORACLE useridDO NOT INCLUDE YOUR PASSWORD5. 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 inyou must NOT recompile or modify any component of your system. I will be checking the objectmodification dates and if any object has been modified after the due date the assignment willnot 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 andincorporate these changes into your submission. The specification will be frozen one weekbefore the assignment is due. University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 2 of 158. I will be utilizing UTSOnline extensively to answer questions on specification and to providedetails of the existing data. I will assume that you will be checking the site regularly to makeyourself aware of the latest developments.9. Please take note of the due date, and work to that date, as extensions will be granted onlyunder 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 accepted10. 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 University11. I expect to return the marked Assignments 14 days after the submission date. University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 3 of 15Modification HistoryPlease NoteModifications to the Assignment specification will freeze one week prior to the duedateDate Comment25-MAR-2019 Document Created University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 4 of 15The Smartcard Financial Settlement SystemIntroductionFor the Assignment for Database Programming and Administration you arerequired to write a Financial Settlement System (FSS) for a Smartcard TransactionCentre.The Deliverables for the Assignment are structured in a way to enable you toachieve a mark that is related to the amount of work that you will contribute andthe level of knowledge that you have attained. The detailed marking structure canbe found at the rear of the document.OverviewFor some time now there has been a trial deployment of a Smartcard System invarious locations throughout the country. A number of different types of Smartcardterminals have been deployed at selected sites. The types of terminals includeParking Meters, Payphones, various Vending machines and ticketing machines atselected railway stations. A number of Smartcard enabled terminals have alsobeen placed in selected retail outlets like Newsagents and University canteensenabling the holders of the Smart cards to pay for their purchases using thesecards.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 themerchants, accepting the cards as payment, can be reimbursed. During the trialphase, the merchants were reimbursed manually, once a week. The settlementamount calculations have been done by the staff in our IT department and themerchants were sent a cheque for the amount of the settlement. During the initialtrial phase the merchants were not charged a fee on the Smartcard transactions.The Smartcard System is moving into the next phase of deployment. The numberof merchants will be increased and there is a requirement to automate theSettlement process. The payment to the Merchants will be done via a direct creditinto 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. Thefee to be charged will be a percentage of the total transactions for the month. Theactual 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 thefees 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 SettlementSystem. You are required to only create the daily settlement system andassociated report. The application is to run in the Oracle Database and is to bewritten using the PL*Sql language. University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 5 of 15The 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 anattachmentThe details of the application follow.Daily SettlementDeskbank FileThe FSS system will be required to run daily and at the conclusion of the run, willproduce a banking file that will be known as a Deskbank file. The Deskbank filewill be sent to the designated banking organization electronically, most likely usinga secure FTP channel.The Deskbank file, when run in the banking system will contain informationnecessary to credit the merchants bank account with the amount collected by theSmartcard transactions. The total of the deposits into the merchants accounts is tobe offset by a debit from our working bank account. The total of the deposits andthe 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 ReportIn addition to the Deskbank file, your system is to produce a daily reconciliationreport. The report will be used by the business unit and will show the bankingdetails generated by the Daily Settlement. The report will be created automaticallywhen the deskbank file is produced, however your system should be flexibleenough 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 ReportWe need to keep track of the busiest terminals and the amount of traffic theygenerate. A report is required for the business units.Each time the report is run it should produce Terminal Usage data for the currentmonth, up to and including the current date.The report specifications is provided. University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 6 of 15RUN TableThe FSS system will run each day and should settle all those transactions thathave not yet been settled. You are asked to create and maintain a RUN tablewhich 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 torun at any time. If the program is already running it cannot run again. If theprogram is restarted while another session is running then a log message is to bewritten into the logging table and the second program instance should terminategracefully.In order to minimize the banking costs we want to run only one settlement processper 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 amessage to the log table and terminate gracefully.If the program fails during the run it should log the failure reason to the loggingtable, update the run table with a status of FAIL. Also the fail reason should bewritten to the Run table so that production support know what needs to berepaired. The run end column should be populated. In such a situation, where thesettlement failed, your program can be rerun on the same day. The assumption isthat production support will have fixed the problem with the data so they may wishto run the settlement again. You MUST BE VERY CAREFULL however, that yourprogram does not duplicate the merchants settlements. This could send usbankrupt.The production support personnel will use the run table to assist them inmonitoring and maintaining the system in the event of failures. One record shouldbe 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 theproduction support team to monitor the progress of your run. The log table willcontain 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 willbe made available to you. I will show you which table the COMMON.log procedureutilizes for logging, You are of course free and encouraged to develop your ownlogging process. University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 7 of 15Email the Daily Settlement Report to a nominated personThis component is optional and only those students that wish to take on thechallenge can attempt this.The Daily Settlement Report once created can be emailed to a nominatedrecipient. I will provide you with the bulk of the code you need to generate theemail. You will need to modify the code I give you to add the file as an attachmentto the email. The code and the full details will be provided on UTS Online.Paramatising your codeSo that your system is easily maintained once in production and to reduce thepossibility of changes to the code I would like you to create a parameter table. Thepurpose of the parameter table is to store all the constants that you may hardcodein 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 andgreatly increase the flexibility of your code.Your table will be named DBP_PARAMETERI will supply the code to create the table. You are free to create any additionalattributes 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 thata user could restart the program multiple times on a given day. You shouldensure that the settlements are not duplicated. In the cases where the usertries to run the application more than once on a given day, the systemshould not allow. This is to be controlled by 代写31253留学生作业、代做Database Programming作业、SQL编程作业代写、代做SQL实验作业 代做the run table. If the programfails 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-runnableby the users at any time and for any given date. The existing FSS system is located on ORALAB in the DBP_ADMINschema. Select privilege has been granted on all objects in the schema toenable you to view the existing data. Your FSS system, when completedwill make up the Smartcard system. It will be standalone, but will also beintegrated into the existing tables. You should not make any modificationsto the existing structure because your changes might ‘break’ othercomponents of the system. The E-R Diagram and specifications for the existing database tables can befound on UTSOnline under the Assignment tab. University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 8 of 15 The daily transaction downloads, from the terminals, will be available in thedatabase environment and the transaction table will be constantly uploadedwith new transactions as they are downloaded from the terminals. This willbe done by an existing automated process. It is anticipated that the transaction tables will grow very quickly. We willadopt an archiving strategy to move the data from the productionenvironment into an, as yet, undefined environment. You can not assumethat the transaction data is always available for your use. Note: Archiving ofthe transaction data is not in the scope of this assignment The bank will charge us a fee for all banking transactions. For this reasonwe will not be settling trivial amounts each time the program runs. Totalsettlement amounts for a merchant that are less than the designatedminimum amount will not be settled in the daily transactions until the totalsettlement amount, during the month, reaches the minimum settlementamount. At the end of the month, any transactions that have not beenprocessed during the month are to be finalized, irrespective of the amount. The minimum settlement amount is configurable and is stored in theFSS_REFERENCE table under the identifier of Daily Minimum Settlement The reload of the Smartcard and the subsequent collection and banking ofthe 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 andThe report file name format is[StudentNumber]_DSREP_DDMMYYYY.rptNote that DDMMYYYY denote the day, month, year of the settlement date when thedeskbank file is created.When your system runs it will produce three files and an email.The files areThe deskbank banking fileThe Banking report fileThe Terminal Usage reportAn email will be sent to a nominated recipient with the Settlement report asan attachment (Optional) So that life is made easier for Laurie while automatically testing the system,you must follow the naming convention below University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 9 of 15Module NamePackage Pkg_FSS_SettlementDaily Settlement DailySettlementDaily Banking Summary DailyBankingSummaryTerminal Usage TerminalUsageExampleTo run the daily settlement I will typePkg_FSS_Settlement.DailySettlement;To run a report for today I will typePkg_FSS_Settlement.DailyBankingSummaryTo run a report manually for a different date, say 18-MAR-2019 I will typePkg_FSS_Settlement.DailyBankingSummary(‘18-MAR-2019’) University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 10 of 15Elements of the Smartcard SystemBelow is a picture gallery that illustrates some of the elements that go tomake up the Smartcard SystemSome of the Smartcard terminals to belocated in the merchant premisesVending machines with SmartcardreadersPayphone Installed with aSmartcard readerContact less Smartcard readers at arailway stationSmartcard enabled Parking meters University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 11 of 150 01WBC S/CARD BUS PAYMENTS 038759INVOICES 1605191015-010270249893 500000022905CARDORAMA F 201905160000431032-797 001006SMARTCARD TRANS 000000001032-099000701123 500000078400STELLA PICTURE CO P/L F 201905160000432032-797 001006SMARTCARD TRANS 000000001032-277000892386 500013659772TELSTRA PAYPHONE SERVICES F 201905160000433032-797 001006SMARTCARD TRANS 000000001034-002000136556 130013923599S/CARD BUS PMTS N 800900000000000032-797 001006SMARTCARD TRANS 000000001062-164010171526 500000025060DUNCANS -CATERING - LOFTUS F 201905160000435032-797 001006SMARTCARD TRANS 000000001083-001648518574 500000002300THE SMITHS SNACKFOOD CO. LTD F 201905160000436032-797 001006SMARTCARD TRANS 000000001096-006006623452 500000081640GERALDTON HEALTH SERVICES F 201905160000437032-797 001006SMARTCARD TRANS 000000001105-120954269240 500000003102THE UNIVERSITY OF TECHNOLOGY F 201905160000438032-797 001006SMARTCARD TRANS 000000001105-134506242640 500000050420FLORUM P/L T/A RUNDLE ARCADE NEW F 201905160000439032-797 001006SMARTCARD TRANS 000000007999-999 000000000000139235990013923599 000009The 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 BlanksTRANSACTION RECORD - TYPE 1 many recordsType One Detail RecordPosn. 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 Merchantss 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 TRANS116 8 GST Tax ZeroesCreate 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 BlanksHeader RecordData RecordFooter RecordUniversity of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 12 of 15A note on the dates on the above report.The report could be reprinted for any settlement date in the past. The dates thatare shown are for the date that the report is printed and the date that thesettlement was processed.SAMPLE DAILY BANKING REPORTSMARTCARD SETTLEMENT SYSTEMDAILY DESKBANK SUMMARYDate DD-Mon-YYYY Page xMerchant ID Merchant Name Account Number Debit Credit---------- ---------------------------------- ----------------- ---------- ---------700000100 CARDORAMA 015-010270249893 229.05700000200 STELLA PICTURE CO P/L 032-099000701123 784.00700000300 TELSTRA PAYPHONE SERVICES 032-277000892386 136597.72700000400 DUNCANS -CATERING – LOFTUS 062-164010171526 250.60700000500 THE SMITHS SNACKFOOD CO. LTD 083-001648518574 23.00700000600 GERALDTON HEALTH SERVICES 096-006006623452 816.40700000700 THE UNIVERSITY OF TECHNOLOGY 105-120954269240 31.02700000800 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.99Deskbank file Name : Dispatch Date : DD Mon YYYY****** End of Report ******Daily Banking Summary ReportSettlement Date University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 13 of 15The Terminal Usage Report will be prepared automatically each time thesettlement system runs. The report when run should produce the Terminal usagedata 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 ofTotal 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 REPORTReport Date: 15-Jun-2019 Page xUsage Month: April 2019TerminalID Terminal TerminalType Merchant Number of TotalTransaction Type Description Name Transactions Amount---------- -------- --------------------- ----------------------------- -------------- ----------------0022000010 VMS Snack Vending Machine The Smiths Snackfood Co. Ltd 525 $345.56---- ****** End of Report ****** University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 14 of 15Assignment Marking SchemeAssignment Weight = 50%Create a daily Deskbank file for delivery to the bank 40Daily banking report for the business unit. Should beproduced for any nominated date20Implement RUN Table correctly for program control 15Terminal Usage Report 10Email banking report to a nominated recipient 15TOTAL 100NoteThe first four items of the list are mandatory and should be produced by eachstudent as a minimum. The items areCreate a daily Deskbank file for delivery to the bank 40Daily banking report for the business unit 20Implement RUN Table for production support 15Terminal Usage Report 10University of TechnologyFaculty of Engineering andInformation TechnologyDatabase ProgrammingAssignment - Autumn 2019Page 15 of 15Glossary of TermsDeskbank FileA fixed width file that is used to communicate with the bankingsystem. The file contains the details of the direct debits and creditsthat are to be carried out by the bank on our behalf. The file isdesigned to be read by the banks computer system. It is crucial thatthe specification of the file is adhered to. If not, the banking systemwill fail and we will incur a financial penaltyTransaction DateThis is a date that a transaction is made and is recorder by theterminal. The date is unreliable because we do not have control overthe terminalDownload Date This is the Date that a transaction was downloaded into the system.The date is recorder by the Smartcard server.Transaction CodeThe values are either 13 or 50.13 is the code for a deposit into the nominated account50 is the code for a withdrawal from the nominated accountBanking FlagThis value is historic and is used by some systems. Our system willnot use this value; however the banking system requires that thisvalue is present. You should hardcode a suitable value.Lodgment RefThis is a unique sequence number created by concatenating thedate with a unique number for the day. This attribute should be usedto link all the transactions for a merchant throughout the dailysettlementTrace This is a hard coded value and is required by the deskbank systemFile Total A sum of the debits and the creditsCredit Total A SUM of all the credit statementsDebit Total A SUM of all the debit totalsRecord Count A number of records in the deskbank file, not including the headerand the footerProcessing Date The date and time that the transactions were processed and thedeskbank file was producedSettlement Date The date that the transactions were settled for each merchantMerchant Storekeeper or any person or company that trades goods or servicesin return for payment 转自：http://www.7daixie.com/2019050430752553.html