Learning Outcomes:
1. Develop a data model for a business application.
2. Design and build a database to store and retrieve data for a business application.
Assessment Overview
The assignment focuses on the technical design and provision of a new database server and the development of a new database for a Financial Services company accompanied with a Technical Design document.
Assessment Task
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 customer cannot be associated to another customer more than once under the same Association Type.
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 Management Information 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.
Technical Document
The accompanying Technical Document should cover (but not limited to):
1. Scope of the document.
2. Technical Design to include:
a. ER Diagram
b. Physical Model in Third Normal Form (3NF)
c. Technology used
d. Embedded SQL File containing all DDL and DML statements.
3. Testing.
4. Reflection on Learnings.
5. References.
