BankCorp Ltd Database Design Entity Relationship Model

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