5915 Database Design ASSIGNMENT 2
This assignment has 150 marks which constitutes 20% of the total marks for this unit.
You need to submit the design documents as set out in the Submissions Section in separate documents for this assignment AND your database (Access Database). The documents include a Logical Entity Relationship Diagram in third normal form, together with a Data Dictionary including ALL entities and attributes and the database documentation as described in the assignment specification below. Please contact Tariq, Gurpreet and Trish if you have any queries about this assignment.
1. General Information
The purpose of this assignment is to provide you with experience in logical design then implementing a solution for a The Canberra Community Bank’s database. Your solution should be implemented as a database system using Microsoft Access. This assignment will help you to understand the nature and purpose of logical design and implementation. It offers you experience in managing a technical database project.
This assignment is to be attempted by groups of 3 or 4 students. Each group is collectively responsible for both the submission and the outcome. There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment. You will present the progress of your assignment to your tutor during your tutorial in week 11.
Make sure that you 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. Each group member is advised to retain a full copy of the material submitted for this assignment.
Page 1 of 5
2. Problem Description
The problem description is that given in Assignment 1. The major differences are the need for a LOGICAL design (i.e. in third normal form) and requirements that relate to the way that the implementation (a PHYSICAL database) must work.
3. Requirements
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. For security reasons the views of customer and staff should be different.
All reports and input screens should be easy to read, use and understand. It should be logically laid out for novice users.
The bank database program should provide the following facilities:
1. Three types of computer-generated reports are produced by the bank database system.
• 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 that 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 $20.00 is added to the customer account for an overdue payment. If a payment is not made after a period of two months from the due date legal actions is usually taken and the customer’s account will be suspended. 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 and all records of overdue for each account as well as total amount of overdue charges. This report is generated on weekly basis.
• The second report is generated daily. It shows the total number of keycards that are reported to be missing, so that new keycards can be ordered (by staff for customers).
• The third report shows the details of the new accounts that are created in the last 7 days with the details of the branch in which these accounts were created. This report is provided to the management of the bank for staffing purposes of each branch.
Page 2 of 5
Please note that 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 5 branches with 50 full time and 40 part time staff members.
2. The bank staff should be able to use the bank database system to:
• Enter the details of new loans as well as changes to the existing loans to the bank database system,
• Enter the details of new customers to the bank database system and change the details of the existing customers,
• Check the availability of a staff member for loan consultation with a customer,
• Make an appointment for a customer to consult with a staff,
• Check the total number of existing customers,
• Check the total number and details of branches,
• Check the details of bank staff members in a branch or a section
• Check the details of keycards that are reported to be missing,
• Check customer’s loan account for any overdue payments,
• Check all the transactions that a customer has made in the last six months.
• Check the details of all customers that have lost their keycards ordered by
customer last name.
• Check the total number of lost keycards for pthe past 30 days.
3. The customers should be able to use the bank database to:
• Check the details of their accounts and loans that the customer has as well as any overdue and fines for particular loan with their overdue dates, if any,
• Check the availability of a staff member for a loan consultation with a customer,
• Make an appointment to consult with a staff,
• Order a statement for his/her account/s (loan, saving etc.)
• Check a his/her loan account for any overdue payments,
• Check the location and details of all branches of the bank
• Check details of transactions for each of their accounts
• Check total number of staff at each branch.
• Check the telephone number of each branch ordered by branch number.
4. Maintenance manual
Once you have developed the bank database system you need to write a manual that will help those who have to maintain the database system. This is NOT a user manual or a manual describing HOW to use the bank database system. This is a technical manual written for database designers, application developers, and those who might have the need to modify (add to, remove from, adjust) the bank database system.
Page 3 of 5
The maintenance manual should be well presented, easy to follow, easy to read, concise (do not put things in the manual unless they are absolutely essential to understand the database application so that it can be maintained). For example: logical data model (E-R diagram) that reflects implementation decisions, a listing or any query used and a data dictionary.
5. Assignment submission
Submit the items listed below on unit’s Canvas site by due date:
A. Your design documents:
• Logical ERD derived from group members Conceptual ERD (first
Assignment);
• The final Conceptual ERD; and
• The final Data Dictionary.
B. Your Access database;
C. The database documentation; and
D. A completed cover page (see under Assignment 2)
This is a total of SIX files – put all these files in the in 1 folder and zip and submit the folder – only one submission per group is required.
You need to include with your program the details of all assumptions that you have made for the Bank database.
MARKING SCHEME
Presentation in tutorial 10 CONCEPTUAL ERD 20 LOGICAL ERD (converted from the conceptual ERD) 20 Data Dictionary 20 Database documentation including maintenance manual (see below)* 10 Tables 20 Data 15 Queries 15 Forms 10 Reports 10
TOTAL 150
Page 4 of 5
*This may include introduction, instructions on how to operate the software, screen dumps of your Access tables, forms, reports and future directions. Presentation and style should be paid attention to
Note: Please remember that late submission, unless authorised by the lecturer, may not be accepted.
Page 5 of 5
Reviews
There are no reviews yet.