RECENT ASSIGNMENT

Semester 1, 2015
Database Design 5915 and Database Design G 6672
Assignment 2
Due date: Friday Week 12 of Semester 2 2015 at 7:00pm
This assignment has 45 marks which constitutes 22.5% of the total marks for this unit.
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.
4. Assignment submission
For the scenario in the problem above:
(a) Identify entity types and their attributes, including the primary keys and any foreign keys for each entity (Your designed database should be in 3rd Normal Form).
(b) Develop a data dictionary for you database,
(d) Compile the E-R diagram of the system. Your ER diagram should also display all entities and their relationships as well as relationship multiplicities. (state all assumptions that you have made).
(e) Implement a database using MS Access for the bank based on your designed database.
Note: The bank database program developed by your team should have well designed screens that are easy to use, understand and follow by novice bank staff and customers. A novice user should be able to use the bank database application (program) by reading instruction on the computer screen of your program. The views of customer and staff should be different. All screens should be easy to read, use and understand. It should be logically laid out for novice users.
Include with your assignment a cover page containing the first and last name of all students in your team as well as their student id, your tutorial day and time, subject name and number as well as the name of your tutor. You need to include with your program the details of all assumptions that you have made for the Bank database. Submit all your design documents with your assignment (the database program) to the Moodle website of you subject on the due date specified.
For students enrolled in Database Design G, you are required to have the following details into your design and implementation of your assignment 2:
The bank has five seminar rooms in their main office. These seminar rooms are provided to all loan staff who wants to organize a seminar. The cost of hire of a seminar room is free for loan staff. Seminar room details are stored in bank database. The seminar room details are: room number, room size, room location. Detail of staff that booked a seminar room is also stored in the bank database. These details are: staff number, staff first and last name, dates for which a seminar room is hired and details of all seminar rooms booked by all staff. For each seminar the details of staff that organized a seminar as well as the details of each customer that attend the seminar is recorded in the bank database. After each seminar the bank sends a questionnaire about each seminar to the customers that attended the seminar. The details of each questionnaire that is send to each customer with each customer response are stored in the bank database. These details are: customer name (first and last name), contact telephone number, address (street number, street name, suburb, post-code, city, state), questionnaire number and customer response (i.e. opinion of the customer about the usefulness of the seminar).
Marking Scheme
DOCUMENTATION (Hard copy)
Entity, attributes, primary keys and E-R diagram 15
Data Dictionary 10
Sub Total 25
IMPLEMENTATION (Soft copy)
Tables and data (5 rows of data for each table) 5
Queries 5
Forms 2.5
Reports 2.5
Sub Total 20
TOTAL Mark 45
Note: Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment. Every group member is advised to retain a full copy of the material handed in for this group assignment. Only one group member will submit the assignment on behalf of all group members.