Analysing and implementing a solution for a bank database

Assignment Help Database Management System
Reference no: EM13836912

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.

Reference no: EM13836912

Questions Cloud

Explain the essence of purchasing parity power : Discuss the international parity conditions. Explain the essence of purchasing parity power. What would happen to purchasing parity power exchange rate if the law of one price were true? What is the difference between the purchasing parity power and ..
To arrange the elements of an integer array : Write a ‘C’ functions to arrange the elements of an integer array in such a way that all the negative elements are before the positive elements. The array is passed to it as an argument.
Prepare a paper on the k-mart scandal : The topic is The K-Mart scandal. The paper should include the below headings. APA foromat.
Financial trends and industry comparisons for a company : financial trends and industry comparisons for a company
Analysing and implementing a solution for a bank database : 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.
Questions from wage issues and economic supplements : Questions from Wage Issues and Economic Supplements - HR manager of a union or a union representative.
Prepare an assignment according to references : You need to prepare 500 words assignment according to my references
Hummus bar-dipping into international markets : A Case Brief should not exceed 500 words (one single-spaced typed page). It should be written with the assumption that the reader is familiar with the details of the case.
Write paper that explain about agile enterprise architecture : Write a paper about 1500 words that explain about using agile principles on implementing Enterprise architecture with insights on its relation to Zachman framework (Agile Enterprise Architecture Framework/life cycle).

Reviews

Write a Review

 

Database Management System Questions & Answers

  Dml stands for

DML stands for

  Construct a diagram using rationale to map the arguments

Construct a diagram using Rationale to map the arguments about a moral claim that you have identified in the article/case study: Include all supporting and objecting claims.

  Create an entity-relationship diagram and design

create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices

  Develop an e-r diagram for the library database

Develop an E-R diagram for the library database. The relation schemas for the library database. Normalization of the relations (your relations should be in 4NF)

  Relations of airline flight information

The relations given below keep track of airline flight information:Flights(flno: integer, from: string, to: string, distance:integer, departs: time, arrives: time, price: real)Aircraft(aid: integer, aflame: string, cruisingrange: integer)

  Implement that entity as a database table with two subtypes

Implement that entity as a database table with two subtypes, Games and Movie.

  Explain data mining write the major characteristics and

define data mining. what are the major characteristics and objectives associated with data mining? what professions

  Determine airports with late flights to toronto-database

Determine airports with late flights to Toronto on a big plane. "Late" means departing after 9:00pm; "big" means with capacity 150 passengers or more

  Problem 1for the normal form game below solve for all nash

problem 1for the normal form game below solve for all nash equilibria and provide a justification for players

  Describe binary lock function

Describe relationships with example. Also illustrate degree of relationship for that example. What do you mean by locks. Write dow a binary lock function.

  Create a schema that supports the company business

Create a schema that supports the company's business and processes. Explain and support the database schema with relevant arguments that support the rationale for the structure

  What is the most common disease for each age group

Explore a possible relationship between the DRG_PRICE and the TOTAL_CHARGES. Is there a linear relationship between these two properties?

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd