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 | ||
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
- 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.
- 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)
- You open a chequing account at the bank and deposit $500.00
- You open a savings account and the bank and your parents deposit $1000.00 through e-transfer
- You buy groceries at Walmart and debit $112.34 from chequing
- You purchase a song from iTunes for $1.99 from chequing. You receive a confirmation code of CQ3E5RZ.
- 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).
- The cheque you wrote bounces (NSF – Non-Sufficient Funds) and the bank charges you a $45 fee
- Your Mom and Dad withdrawal money from your RESP for school expenses $2000 which they deposit directly into your savings account.
- 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.
- 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.
- 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.
- 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
- Outputs a list of transactions, in chronological order. – DIFFICULT BIT show the running balance beside each transaction in the transaction list.
Example Output
- Outputs a list of transactions, in chronological order. – DIFFICULT BIT show the running balance beside each transaction in the transaction list.
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 |
- write SQL to calculate the total debits and credits (separate totals even though the amounts are in the same field)
- write SQL to verify that the sum of all transactions adds up to the same amount as the currentBalance. If not, output the difference.
- 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.