5915 Database Design ASSIGNMENT 1
Due date: Friday Week 6 at 11:55 pm
This assignment has 100 marks which constitutes 15% of the total marks for this unit.
You need to submit your design documents (Part 1) as well as the answer to Normalisation question (Part 2) on a separate document for this assignment. The design documents include a conceptual Enhanced Entity Relationship Diagram together with a Data Dictionary as described in the assignment specification below. Please contact Tariq if you have any queries about this assignment.
General Information
This is an individual assignment and each student is responsible for both the submission and the outcome.
Please NOTE
1. There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment.
2. Plagiarism will attract severe penalties in accordance with the guidelines set out in the subject outline
3. Please use Assignment 1 Coversheet and include your student ID, your full name and signature.
4. An electronic copy of this assignment should be submitted via the Canvas site using the assignment drop box by the due date and time.
5. 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.
1 of 4
PART 1 (55 marks)
The purpose of this assignment is to provide you with experience in analysing and designing a solution for a Bank database. This assignment will help you to understand the nature and purpose of data analysis and conceptual design.
Modelling (Total Marks 55)
Read the description of Canberra Community Bank and its business operations then answer the questions that follow.
The Canberra Community Bank was established and has several branches in Canberra. The bank requires you 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), gender and types of account a customer has. The name (first and last name), contact telephone number and address (street number, street name, suburb, post-code, city) of customer’s next-of-kin is also stored. For each customer the system records and stores all activities of each account and loan for each customer, including deposits, withdrawals, transfers and fines.
The bank offers several types of loans and accounts. Loans are managed under loan section and accounts are managed by account section of the bank. There are several types of loans, ( see table below). All loans can be approved as either fixed or variable rate loan. The current rates for these loans are as follows:
When a customer joins the Bank, he or she is assigned an account number and their details are recorded. Every customer is provided with an account number, a keycard number and a copy of the rules relating to accounts and details of monthly bank charges for their account.
The bank has many staff members and for each staff member the bank stores details (staff first and last name, staff number, position, gender, date of birth, section, internal phone number, office number, branch number) in the database. Each branch has six sections (information section, loan section, administration section, account section, security section and lost-stolen card section. The information about each section (section name, location, several phone and fax numbers) is stored in the database.
If a customer wants a loan, the customer has to organise an appointment to meet with a loan staff for an interview with a loan officer which takes only 30 minutes. After the interview, the loan officer either accepts or rejects the customer¡¯s loan request.
The bank account types that have account keeping fees and interest rates (see table below).
Type of loan
Fixed rate
Variable rate
First Home Buyer Loan
6.7%
7.2%
Business Loan
7.3%
7.5%
Personal Loan
7.9%
8.9%
2 of 4
Account Type
Interest Earned Per Annum
Account Keeping Fees
High performance saving
4.5%
$5 per month
Medium performance saving
3%
$5 per month
Fixed term saving (6 Months)
5%
$5 per month
1 Year Fixed term saving
6%
$5 per month
Business account
3.2%
$10 per month
A customer can have several types of accounts but there is a limit of 6 accounts at any particular time. The customer’s identity is proven with their keycard number and their account number. The customer’s keycard number and their account number are used to access customer records.
For each customer the details of each transaction (transaction number, customer number, DateAndTime, account number, amount, withdrawn or deposited) is stored in the database. (Note: a transfer is a withdrawal from one account with an equal deposit on another account.) For each transaction a receipt is issued to the customer.
If a customer has a loan, the customer should make a payment for their loan every month. The amount of payment depends on the amount, duration and type of the loan. If a customer makes late payment, a late fee charge of $20.00 is added to his/her loan account.
The bank also provides an appointment reservation facility where customers can make an appointment to consult with staff members about their loan. For a reservation a customer enters their 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 if a staff member is available at that particular time. Otherwise the timetable for staff at the branch requested by customer is displayed on screen. The customer can then browse the timetable, check the availability of staff and make an appointment.
Requirements
(a) Identify the main entities of Canberra Community Bank.
(b) Identify the main relationships between the entities.
(c) Determine the multiplicity constraints for each relationship.
(d) Identify attributes and associate them with entity or relationships.
(e) Determine primary key attributes for each strong entity.
(6 Marks) (5 Marks) (5 Marks) (6 Marks) (5 Marks)
(f) Use your answers (a) to (e) to represent the data requirements
of Canberra Community Bank as a Conceptual data model
(ERD (18) and Data Dictionary (10)). State any assumptions necessary
to support your design. (28 Marks)
3 of 4
Normalisation (Total Marks 45)
PART 2
The following relation lists doctor/patient appointment data in a relational database for the Melba Medical Association (MMA).
DoctorId
DoctorName
PatNo
PatName
ApptDate
ApptTime
RoomNo
S111
Mahat McCote
P103
Mark Grade
12/11/2015
10:00 AM
R15
S111
Mahat McCote
P108
Jill Karnt
12/11/2015
12:30 PM
R15
S124
Helen de Troy
P111
Mark Dunn
12/11/2015
10:00 AM
R10
S124
Helen de Troy
P111
Mark Dunn
14/11/2015
10:00 AM
R11
S132
Robin Redbrest
P108
Jill Karnt
14/11/2015
4:30 PM
R15
S133
Van Halen
P113
Paul O¡¯Smoke
15/11/2015
6:00 PM
R13
It is assumed that MMA has several doctors and several rooms for them to consult with patients. On each day of patient appointments, a doctor is allocated to a specific room for that day. However on a given day a room may be allocated to more than one doctor but at no time would two doctors sit together in the same room.
Each appointment is of 30 minutes duration. A patient can have an appointment with any of the doctors in MMA. A patient cannot have two appointments in MMA on one day.
Requirements
(a) The above table is subject to update anomalies. Using the data in the table, provide examples of insertion, modification and deletion anomalies. (15 marks)
(b) Identify the candidate keys of the above relation, and select one of them as the primary key. (7 marks)
(c) Identify the functional dependencies on the alternate keys as well as the primary key.
(5 marks)
(d)Using the functional dependencies identified in part (c), normalise the above relation to 3NF showing any Foreign Keys. (No need to include data in the normalised tables).
(18 marks)
SUBMISSIONS
Submit your design documents (Analysis, Conceptual ERD, Data Dictionary) for Part1 and your answers for Part 2 via Canvas by the due date as specified above together with a cover page containing your name and student number.
MARKING SCHEME
Analysis
Conceptual Model (ERD and Data Dictionary) Normalisation
Part 1, requirements (a)¡ú(e) 27
Part 1, requirement (f)
28 45
TOTAL 100
4 of 4
Reviews
There are no reviews yet.