Skip to main content

BankCorp Ltd Database Design Entity Relationship Model

By

Bank Corp Business Requirements

BankCorp Ltd. offer Wealth Management and Investment Services to its Personal and Corporate clients. Currently, data is stored in a number of systems across the company. This leads to “double keying” and as a result inefficiencies and errors.

They want to create a single database to consolidate all these details into one single source. They do not have any IT staff and they do not have the space or ability to look after any infrastructure on premise. They want you to design and develop the database only. Any data migration tasks will be taken care off in a future different project. The data model must be designed to meet 3NF (Third Normal Form) requirements.

In terms of the key entities they have given you the following list:
1 Customers
2 Accounts
3 Securities
4 Associations

A customer can have many accounts and an account can be secured by many securities. A customer can be associated to another customer in the bank e.g. a Property Development company may be associated with a Surveying company.

Associations can be two-way.

  • For example - Company A is the Surveyor of Company B 
  • Company B has Surveyor Company A 
  • A company cannot be associated to another company more than once. 

In terms of data updates they have the following requirement: 

They want the ability to create a new Customer and Association (from the newly created customer to another customer) via a parameterised Stored Procedure.
They want the ability to delete a customer and foreign key records associated with this Customer via a parameterised Stored Procedure.

In terms of reference data they have the following requirement: 

Accounts, Securities and Associations can be of several types. They do not want many tables to store each of these types. They want you to come up with a generic way to store all reference data in the database. In addition to this reference data may be removed from the database from time to time – they want any reference to data which has to be removed is done as a “soft delete” as opposed to a “hard delete”. This way it will remain in the database but will not be visible to the user.
In terms of MI they see real value in having a single view on all their clients and the touch points between them and as such want the following extract: They want an MI extract created as a SQL View which will return the association details between all clients (both sides of the relationship!!!).

Deleted Reference Data View 

The company also want visibility of what Reference Data has been deleted. They want to be able to query a SQL View which shows all reference data which has been deleted. They have come to you as an IT Consultant and they want these requirements developed into an enterprise solution. Along with the final solution you must supply the customer a Technical Design Document with the technical design details of the solution.

Get Project Solution by contacting us

- via WhatsApp: +92-324-7042178
- via email: codelogixstudio@gmail.com
Labels:

Comments

Post a Comment

Popular posts from this blog

The Zoo Management System - entity relationship diagram & MS Access Database

By
Zoo Management System - Project Details: You are the employee of a big, worldwide working Zoo Management Company. Your company is responsible for the Zoo management. Your boss thinks it would be a great idea to store all data for each Zoo in a brand new self-developed ZOO Management System. Up to now, the ZOO management company has maps of each ZOO available. Your boss knows that you took a course in introduction on an ERP system, so he asks you if you could help designing such a system. Each ZOO must have the same organizational structure, which should look like this: Each Zoo has a Zoo-Address. Each Zoo has many visitors (Visitor Ticket Process (VTP). Many Zoo-Attractions belong to a Zoo. Module 1: Entity Relationship Diagram Design a ER (entity-relationship) diagram for your ZOO Management System. Use the information provided below with the entities and its attributes. Put the entities in the correct relationship to each other (organizational structure). Module 2: DB Implem...
1 comment
Read more

Human Physiology by Stuart Ira Fox [PDF] (12th edition) free download

By
21 comments
Read more

Sudoku Game Project in Java with full source code including GUI task

By
Introduction The purpose of the project is to try in practice the use of recursion and object-oriented program design. Please make sure to read this entire note before starting your work. Pay close attention to the sections on deadlines, deliverables, and exam rules. The problem Your task in this part of the project is to write a solver for Sudoku puzzles. Sudoku puzzles are a kind of crossword puzzles with numbers where the following two conditions have to be met: In each row or column, the nine numbers have to be from the set [1,2,3,4,5,6,7,8,9] and they must all be different.  For each of the nine non-overlapping 3x3 blocks, the nine numbers have to be from the set [1,2,3,4,5,6,7,8,9] and they ust all be different. The following two figures show a Sudoku puzzle and its solution. The input The input of your program, the Sudoku puzzles, is given in text files. More specifically, they are represented as nine lines of nine characters separated by spaces. The characters can be of two...
Post a Comment
Read more