Our existing system is antiquated.
We want to redevelop it so that it is more robust and extendable,
and so it has better performance.
The current system handles many operations.
We want to first redevelop the portion that manages
information on accounts, loans, employees, branches and customers.
This is the scope of your design.
The most important information for us is, of course,
the customers' bank accounts.
Each account has an account number,
which is unique within a branch.
Obviously, we need to know to whom the account belongs,
and the account's current balance.
Joint accounts are allowed,
which means an account can have more than one owner.
We need to know the account type;
for example, savings, chequing, and loan.
We also want to record
in which branch the account was opened.
With today's information technology,
most types of transactions and services can be handled in any branch;
but certain services can only be handled by the branch
in which the account was opened.
We also want to record when the account was created.
Note that at our bank,
a loan account acts as a personal line of credit.
For a loan account,
we also need to record who the loan officer was who authorized it,
the amount, the interest rate, and an end date
(when the loan account will be closed).
For a savings account,
we need also to record an interest rate.
For each customer,
we need to know his or her full name,
the 9-digit social insurance number (SIN)
for reporting interests earned to the government
(for filing tax returns),
a phone number,
and an address.
Each customer has a customer number, unique across the bank.
We have a large number of branches across the country.
Each branch has a unique 5-digit branch number.
For each branch, we want to record its address, main phone number,
and office hours.
The office hours vary from branch to branch,
and are usually different for different days of the week.
For example, many branches are open from 9am to 4pm
on Mondays, Tuesdays and Wednesdays;
9am to 8pm on Thursdays and Fridays;
and 10am to 2pm on Saturdays.
Hence, you need the record the office hours (start time and close time)
for each branch for each day of the week.
In this database,
we also want to include employee information.
For each employee,
we keep track of a unique employee number, name, address, his or her boss,
salary, and date of birth.
Assume that each employee has one, and only one, boss.
For each of the loan officers, who approves loans,
we need to record the maximum loan amount
that he/she is authorized to approve for each loan.
We want to record where each employee is assigned to work.
By where, we mean which branch or head office.
If it is the head office, record 00000.
An employee can be assigned to work in more than one place.
We also want to know the start date and end date,
if applicable,
of the current and past assignments.
Obviously, we need a record of all the account transactions,
which include deposits and withdrawals.
For a loan account, a payment (payback) is equivalent to a deposit;
and further borrowing against the loan is equivalent to a withdrawal
from the account.
Each transaction has a unique transaction number,
and indicates which account
(associated with one, and only one, account, of course),
transaction type (deposit or withdrawal),
amount, and date.
For each transaction,
we also want to record who authorized it;
that is, which customer associated with the account
performed the transaction.
We would like to have the computer to manage much more information.
But the above are the critical items,
so let's focus on them for this design.
|