Sale!

Assignment 3 DBS301

$30.00 $18.00

Category: You will Instantly receive a download link for .zip solution file upon Payment || To Order Original Work Click Custom Order?

Description

5/5 - (8 votes)

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.
    1. 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)

  1. You open a chequing account at the bank and deposit $500.00
  2. You open a savings account and the bank and your parents deposit $1000.00 through e-transfer
  3. You buy groceries at Walmart and debit $112.34 from chequing
  4. You purchase a song from iTunes for $1.99 from chequing. You receive a confirmation code of CQ3E5RZ.
  5. 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).
  6. The cheque you wrote bounces (NSF – Non-Sufficient Funds) and the bank charges you a $45 fee
  7. Your Mom and Dad withdrawal money from your RESP for school expenses $2000 which they deposit directly into your savings account.
  8. 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.
  9. 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.
  10. 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.
  1. 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

    1. 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

 

  1. write SQL to calculate the total debits and credits (separate totals even though the amounts are in the same field)
  2. write SQL to verify that the sum of all transactions adds up to the same amount as the currentBalance. If not, output the difference.
  1. 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.

Related products