Description
In this assignment we will be investigating some basic banking processes. If you are not familiar with some of these, you can speak with your parents or other adults to understand some of the comments. Discord will be a good place to discuss such topics as well and we will make sure each student understands the concepts.
Submission
You will be submitting a single .sql file for this assignment. Ensure the code is organized, follows the required style guide, and contains a comment header with your student name and student ID along with comments throughout the code explaining the steps taken.
Submit the single .sql file to blackboard.
Background
In the banking industry, there are many, many different areas where databases are required. We will simply cover bank accounts and everyday transactions that the general public performs in this assignment.
Tables
Create the following tables:
A3_ACCOUNT_TYPES
FieldName FieldType Size Req Default Note Example
AccountType string 1 Y PK C
TypeName string 15 Y Chequing
A3_CUSTOMERS
FieldName FieldType Size Req Default Note Example
customerID int (Auto) Y PK 4356
firstname string 20 Y John
lastname string 20 Y Smith
email string 50 Y john@domain.com
DOB date 19870516
phone string 10 9055551212
A3_ACCOUNTS
FieldName FieldType Size Req Default Note Example
accountNo int sequence 655142
accountType string 1 Y C FK C
customerID int Y FK 4356
currentBalance decimal 10,2 Y 0.00 Yes this will be a calc field, but this is one example where that rule is ignored.
Check constraint: > 0 456.78
dtOpen date sysdate 20200217
isActive tinyint y 1 1
A3_TRANSACTIONS
FieldName FieldType Size Req Default Note Example
transactionID Int (auto) Y PK 8535482
accountNo int Y FK 655142
transactionType string 1 Y D or C
(for Debit or Credit) D
amount decimal 10,2 Y 0.00 781.25
description string 25 Y Seneca Pay
transDate date Y sysdate 20200328
refNum string 12 Authorization code, bill account # etc. DC456778
or
4785125
NOTE: debits and credits are from the perspective of the person doing the transaction. From the perspective of the account owner, a debit is a withdrawal (negative) from their account where a credit is a deposit (positive) to their account. Therefore, debits will typically have a negative amount, credits will have a positive amount.
Tasks
Using:
• Appropriate identifiers for determining exact rows
• Proper style guide
• Checks to ensure sufficient funds are available
1. Create a series of transactions that suit the following scenarios. Make sure that each transaction includes the updating of the currentBalance in the accounts table.
a. Create a customer with your name
Using that customer, add the following transactions (complete with all steps required) – only need to do your side of the transactions. (i.e. no need to do your parent’s, landlord’s or other party’s perspectives)
b. You open a chequing account at the bank and deposit $500.00
c. You open a savings account and the bank and your parents deposit $1000.00 through e-transfer
d. You buy groceries at Walmart and debit $112.34 from chequing
e. You purchase a song from iTunes for $1.99 from chequing. You receive a confirmation code of CQ3E5RZ.
f. You have to pay first and last months rent on your new apartment. The monthly rent is $800 and you write a cheque (#005) for it (i.e. chequing account).
g. The cheque you wrote bounces (NSF – Non-Sufficient Funds) and the bank charges you a $45 fee
h. Your Mom and Dad withdrawal money from your RESP for school expenses $2000 which they deposit directly into your savings account.
i. You get a certified cheque for the first and last month’s rent plus the $25 fee your landlord charges you. There is an $8 fee for the certified cheque charged to you by the bank.
j. You go to the ATM to take out $40 cash to pay your friend back for helping you move your stuff into your new apartment.
k. Using online banking, you pay your Roger’s mobile phone bill of $64.45. You receive a confirmation code of X8U2 and your rogers account number is 345678.
2. Each month, the bank runs a balance statement. This statement produces a list of transactions and includes each transaction amount, the details of the transaction along with the total debits, total credits and ending bank balance.
Write a series of SQL statements that will perform these tasks
a. Outputs a list of transactions, in chronological order. – DIFFICULT BIT show the running balance beside each transaction in the transaction list.
Example Output
transID Date Details Amount Balance
1 3/1/2020 Opening 0.00 0.00
2 3/1/2020 Deposit 500.00 500.00
3 3/3/2020 Walmart Canada -112.34 387.66
4 3/4/2020 iTunes -1.99 385.67
BONUS MARKS: if debits and credits are shown in separate columns
transID Date Details Credits Debits Balance
1 3/1/2020 Opening 0.00 0.00
2 3/1/2020 Deposit 500.00 500.00
3 3/3/2020 Walmart Canada -112.34 387.66
4 3/4/2020 iTunes -1.99 385.67
b. write SQL to calculate the total debits and credits (separate totals even though the amounts are in the same field)
c. write SQL to verify that the sum of all transactions adds up to the same amount as the currentBalance. If not, output the difference.
3. Write a short paragraph (100-150 words) about your own personal experience of learning in DBS301. What overall things did you learn and how did this course help you become a better IT professional? Include this paragraph as a comment block in your sql file.
