INST2005 Database Systems Assessed Design Exercise 2018
The Project
The United Community Bank (UCB) is a well established regional bank operating primarily in South England, but with several branches in key cities and towns in the North. The last 5 years saw the bank transform into a fully computerised bank. Keeping in pace with its philosophy of responsive customer service, UCB decided to offer online banking services to its customers. The new online banking service would fully integrate with the core banking solution already implemented at the bank. The online banking service is open to savings account customers and current account holders.
The basic entity for explaining all operations in the bank is the customer. A customer may have one or more accounts in the bank spread across one or more branches. Customers open bank accounts with a specified opening balance and thereafter may either deposit funds into the account or withdraw amounts from them. For each customer the system wants to record name, personal information (e.g. address, phone number), customer id, password, accounts held. For each account, the system should store basic account information (e.g. account number, sort code), the list of transactions and the interest associated with the account and the available overdraft (only for current accounts).
One of the most frequent requests for information by customers is querying on balance and information regarding specific transactions carried out in the bank. In addition to this, the system should be able to perform the following actions: registration of new customers, opening new accounts for customers, deposit transactions, withdrawal transactions, maintaining security password information on customers.
This site will not handle financial transactions but it will pass data on to the core banking solution, so you do not have to worry about handling the cash aspects, but the database must be capable of providing a set of data containing the details of the customer and his/her transactions.
The Assignment
Design and develop an E-R-A model suitable for the project assigned above. Identify all entities, attributes and relationships in the model, and the primary keys for all entities. All data in the model should be normalised to 3NF, with the normalisation process demonstrated in full. You should document any and all assumptions you may make, and provide reflective design notes.
Tutorial support:
You will be assigned to a group for tutorial support in week 8 (Wednesday 28 February 2018) – please come prepared with any questions on the project, and consider the tutorial to be a client interview. NB although tutorials will be held in groups, this is an INDIVIDUAL PROJECT which everyone must undertake by themselves.
If there is anything you do not understand about the assignment that you do not resolve at your tutorial please contact me (Kosmas Kosmopoulos, k.kosmopoulos@ucl.ac.uk) and practicals’ tutor (Ioannis Lignos, i.lignos@ucl.ac.uk) individually.
Submitting your assignment
Your submission should contain the following:
- An accurate and properly notated E-R-A diagram of your model. A hand-drawn diagram is entirely adequate, but should be drawn neatly and scanned for electronic submission. You may break your model down onto several sheets for clarity if you so wish.
- A set of design notes of no more than 1000 words detailing any assumptions you have made and commenting on your design decisions and the design development process (e.g. what issues did you have to resolve, how did you resolve them and why, what happened in the normalisation process, what might you have done differently if given the option, etc).
You will be assessed on the accuracy and completeness of your model, and the appropriateness and relevance of your design notes.
Your model and notes should be submitted no later than 4pm on Friday 9th March 2018 via Moodle in accordance with normal DIS coursework submission procedures.