Database Design

Assignment

1. General Information

The purpose of this assignment is to provide you with experience in analysing, designing and implementing a solution for a bank database. Your solution should be implemented as a program for a database system using Microsoft Access. This assignment will help you to understand the nature and purpose of database analysis, design and implementation. It offers you experience in managing a technical database project.

This assignment is to be attempted by groups of 4 to 5 students. Each group is collectively responsible for both the submission and the outcome. Individual efforts will not be marked. There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment.

2. Problem Description

The City Community Bank (CCB) was established recently. It has ten branches in Australia. The bank requires your team to design a database system for the bank. The bank database will record and store the data about each bank customer including their first and last name, postal address and home address (street number, street name, suburb, post-code, city, state), gender and type/s of accounts a customer has. The name (first and last name), contact telephone number and address (street number, street name, suburb, post-code, city, state) of customer's next-of-kin is also stored. For each customer the system records and stores all activities (i.e. deposit, withdrawal) of each account for each customer, including any loans, overdrafts and fines.

The bank offers several types of loans and bank accounts. Loans are managed under loan section and accounts are managed by account section of the bank. There exist several types of loans, namely First Home Buyer Loan, Investment loan, Business Loan and Personal Loan. All these loans can be approved as fixed rate or variable rate loans. The current rates for these loans are:

Type of loan Fixed rate Variable rate

First home buyer loan 5% 4.5%

Investment loan 5.5% 4.5%

Personal loan 6.9% 7.9%

Business loan 5.5% 4.5%

When a customer joins the Bank, he or she is assigned an account number and his/her details are recorded. Every customer is provided with an account number, a keycard number and a sheet of paper showing the rules relating to accounts and monthly charges of the bank for their account.

The bank has several staff members. For each staff member the following data is stored in the database system of the bank: staff first and last name, staff number, position, gender, date of birth, name of the section he/she works in, internal telephone number, office number and branch number. The bank consists of six sections. These sections are namely: information section, loan section, administration section, account section, security section and lost-stolen card section.

The information about each section is stored in the bank database. The information about each section is: section name, location. Each section has up to five telephone numbers.

To take a loan a customer makes an appointment with one of the staff in loan section of the bank. The customer can also browse the bank loan details on Internet or on a computer at the bank.

The Bank account types fall into a few different types, each with different account keeping fees, interest rates and rules. The account types are: High performance saving account, Fixed term saving account and Business saving account. The interest earned and account keeping fees for these accounts are:

Account type Interest earned Account keeping fees

High performance saving 3.5% $5 per month

Fixed term saving 6.3% $5 per month

Business saving account 3.2% $10 per month

A customer can have several types of accounts. However a customer is limited to a maximum of seven accounts at any particular time. A customer's identity is established using his/her keycard number and account number. Customer's keycard number and account number is used to access customer's record.

Overdraft charges are noted in the customer's records. If there are any outstanding overdrafts on an account, the computer system will provide a notice to the customer with their overdraft details and the due date for payment. This notice is then sent to the customer and stored in the bank database.

The details of all transactions (transaction number, customer number, account number, amount withdrawn or deposited and account balance) are stored in the bank database.

Each customer's keycard number has an expiry date. A new keycard is sent to each customer five day before their keycard expiry date. Alternatively a customer can contact the bank at the following telephone number: 1800 888844 to receive a new keycard. The staff at account section once notified by the customer will deactivate the existing keycard and the customer will be provided with a new keycard within 24 hours. The details of the new keycards issued are stored in the database of the bank.

If a customer has a loan then the customer should make a payment for his/her loan every two weeks. The amount of payment depends on the amount, duration and type of the loan. If a customer makes a late payment then a late fee charge of $10.00 is recorded in the bank database for that customer.

The bank system also provides a loan appointment reservation facility where customers can make an appointment to consult with staff members about their loan. To make a loan appointment a customer enters his/her name (first and last name), contact details (telephone number and postal address), branch number the preferred time and date of appointment. An appointment is then made for the customer. Each customer has also a login name and password that the customer can use to access the net banking facilities of the bank.

The bank has a loyalty program. Bank customers can enroll in the bank loyalty program and earn 1 point for every dollar of interest paid for a loan. When a customer has 10000 points then the customer will receive a $10 voucher. The details of all loyalty points and vouchers given to all customers are recorded in the bank database. These details are: customer number, name (first and last name), customer mobile phone number and email address, voucher number, loan number and issue date and expiry date of the voucher.

Two types of computer-generated reports are produced by the bank database system.

Report 1: The first report shows all overdue payments for loans. This report is stored using customer's name and account number. It is used to contact the customers with overdue payments (A computer prepared notice is sent to the customers who have overdue payments). Customers are reminded to make a payment for the specified overdue payment and of the late charges that is incurred. A late charge of $10.00 is applied to customer account for an overdue payment. This report will provide the following information: first and last name of customer, postal address (street number, street name, suburb, post-code, city), account number, contact telephone number, overdue amount. This report is generated on weekly basis.

Report 2: The second report shows the details of all accounts at each branch. This report is provided to the bank management for staffing purposes of each branch.

The bank has 5000 customers about half of them have a loan. The rest are customers who may have one or more accounts. The bank has 10 branches with 75 full time and 20 part time staff members.

The bank staff should be able to use the bank database system to:

Enter the details of new customers to the bank database system,

Enter the details of new loans,

Make an appointment for loan consultation,

Check the total number of existing customers at each branch,

Check the details of all branches,

Check the details of bank staff members in each branch

View Report 1 as described above

View Report 2 as described above

The customers should be able to use the bank database to:

Check the availability of a staff member for a loan consultation with a customer,

Check the location and details of all branches of the bank

Check total number of staff at each branch.

Check the telephone number of each branch ordered by branch number.