CS425 - Database Organization - 2022 Fall

Course webpage for CS425 - 2022 Fall taught by Boris Glavic

Project Description

Overview

In this project you will develop a banking application that allows customers to manager their accounts and bank employees to run analytical queries and manage customers.

Users

The application should support two main types of users:

  • customers
  • employee

Employees are either tellers, loan specialists, or managers which each have different permissions in terms of what data they can access and what operations they can execute through the application.

Data

Branches

  • Branch: The bank operates multiple branches. For each branch we need to record the address at which the branch is located.

Employees

  • Employee: employees can be either tellers, loan specialists, or managers. For all employees we need to record personal information like their name, address, and SSN. Furthermore, we want to record their salaries and which branch they are working for.

Customers

  • Customer: For customers we record their name and addresses and their home branch.

Accounts

  • Account types: the bank has several account types a customer open, e.g., a variety of checking and saving accounts. Some accounts pay interest of a fixed interest rate. Some account types disallow negative balances (transactions resulting in negative balances would be rejected) and some account types will charge overdraft fees if the balance is below 0. Some account have monthly fees which may depend on the balance of the account (e.g., a checking account may waive its monthly fees if the account balance is more than $50.000).
  • Accounts: accounts have an account numbers and an account type (e.g., a checking account with certain benefits). We need to keep track of the current balance of an account. An account is owned by one or more customers.
  • Transactions: we also want to keep track of the transactions for each account. A transaction has a type (deposit, withdrawal, transfer, \ldots), an amount (e.g., positive for deposits and negative for withdrawals), and a description.

OPTIONAL BONUS: loans

  • Loans: Customers can have loans which have an amount, a runtime, and an interest schedule.

Access Control

  • customers should have only access to their accounts
  • tellers can read the current account balance of a customer and execute withdrawal and deposit transactions
  • managers can access all information
  • loan managers (in case you are implementing the bonus loan management) have access to loan information and customer information

Application

The application should distinguish at login between the four possible roles: managers, loan managers, tellers, and customers. Different functionality is available depending on which role is logged in.

Account Transactions

  • can be executed by: tellers, customers (for their own accounts only), managers
  • a withdrawl removes some dollar amount from the balance of an account.
  • a deposit increases the balance of an account.
  • a transfer moves money from one account to another (customers can move money from one of their accounts to another person's account.
  • an external transfer moves money between an account of this bank to an account of another bank.

Account Management

  • can be executed by: customers (for their own accounts), managers
  • create, delete accounts
  • show statement for a month: the statement of a (past) month of an account should list all the transactions for this account during this month order by time. Furthermore, the account balance after each transaction should be shown. Finally, you should show the final account balance for the account at the end of the month.
  • show pending transactions: pending transactions are the transactions of the current month that are not part of a statement yet.
  • can be executed by: managers
  • add interest, overdraft fees, account fees: this adds interest to all account for the manager's branch and applies overdraft fees for accounts with negative balances (for account types which have overdraft fees).

Analytics

  • can be executed by: manager

This feature can be used by managers to answer questions like the ones shown below (come up with your own ideas of what other analytical queries could be relevant).

  • calculate the total net worth of all customers from a branch or across all branches