Designing a Bank Database System
Introduction
In this project, I utilized Microsoft SQL Server to design and implement a relational database system for ZeeBank.
ZeeBank has been facing a significant challenge in managing its customer data and ensuring data accuracy. This project aimed to address the business problem through database design, schema development, data insertion, and query execution., I demonstrated proficiency in basic data analysis skills, including data modeling, database design, SQL implementation, and web interface development. Through this project, I provided a comprehensive solution that meets the bank requirements and delivers actionable insights for decision-making.
Zee Bank is a large mortgage bank with a diverse range of customer. The bank has experienced significant growth in the last few years. The bank has been facing a significant challenge in managing its customer data and ensuring data accuracy. The current systems used by the bank to manage customer data are outdated and have become a significant bottleneck in the bank’s operations. These systems are not integrated, leading to duplication of customer data, inaccurate reporting, and poor decision-making. They also do not have a central database that shows which employees manage which customers and their mortgages, making loan recovery tedious.
To address these challenges, Zee Bank has decided to implement a modern database system that can streamline their processes and provide real-time insights into their operations. The proposed database system will allow Zee Bank to manage their customer data more efficiently and ensure data accuracy, enabling them to make data-driven decisions, track branch, employee, customer, account and mortgages data thereby improving their business processes.
Business Requirement
In the bank, there are many different branches like VI branch, Island branch, Mainland branch, Pacific branch, Southern branch, and Western branch. Any customer must meet an employee and submit his records for an account to be opened for him before he can access a mortgage. A customer can open an account in different currencies like USD and EUR. The Employee gives the customer an account opening package and he is responsible for managing the customer. After inputting the customer details, the customer is assigned a customer number which is unique to that customer. The Credit score of the customer is then accessed. If the customer has a credit score between 400 and 700, the customer is entitled to a car mortgage or a home equity mortgage if the credit score is above 700. The customer may choose to apply for the loan immediately or apply within a 14-day window. A customer may or may not take a mortgage and a mortgage may or may not be taken by a customer.
Before getting access to the mortgage application, the customer must fulfil certain requirements like funding his account with a minimum of $100, getting a reference. After filling the loan application, the customer will be charged a processing fee of $500. A customer may agree to pay the processing fee or reject it if he is taking a home mortgage. If the customer rejects it, the customer must provide a co-signer who has a minimum sitting balance of $10,000 in his account. A customer mortgage account can either be a home mortgage or car mortgage but not both.
Also, an employee must belong to only one bank branch while a bank branch may have many employees. A customer must belong to one or more branches and a branch may have many customers. An employee may or may not manage an account while an account must be managed by an employee.
Table Description
1. BANK_BRANCH: This table consists of details about the various branches in the bank. The information stored in this table includes the Unique Id for the branch(Branch_ID), the name of the branch( Branch_Name), the city where the branch is (Branch_City),the date the branch was opened and the tax identification( Tax_ID ) for each branch.
Constraint: Branch_ID will be unique for each branch
2. EMPLOYEE: This table stores information about all the employees working for the bank and the branches they are associated with. Each employee is given an employee number. Employees can either be full time (Full time) or contract staff (Contract). This table contains the employee ID, first name, last name, SSN, Phone number, employee status, employee role and the branch the employee is attached to.
Constraint: Employee number is unique for each employee. Branch_ID must exist and be valid.
3. CUSTOMER: This table stores information about all the customers in the bank such as the customer ID, the first and last name of the customer, date of birth, phone number (if available) and the branch the customer is assigned to. After storing the necessary details, the customer’s details are sent for account opening and the customer is assigned an account ID.
Constraint: Customer number (Customer_ID) is unique for each customer. Branch number must exist and be valid. Customer number must exist in the Mortgage table.
4. ACCOUNT: This table stores details of the different accounts in the bank. The accounts are identified by the Account number which are generated by the computer. This table also stores the account name, type, date, currency, opening balance, and closing balance as well as the employee that is managing the account.
Constraint: Account number is unique for each account. Employee number must exist.
5. MORTGAGE: This table stores records of the different mortgages in the bank. This table stores the mortgage number, mortgage type, amount, date collected, credit score and the customer number. There are two types of mortgages: a home mortgage and a car
mortgage. A mortgage can be a home or car but not both. The Home mortgage table consists of the mortgage number, the type of home and the co-signer name (if applicable) while the car mortgage table consists of the mortgage number, the car type and every car must have insurance.
Constraint: Mortgage number is unique for each account. Customer number must exist.
Input Requirements:
6. User friendly Interface for Customer Information: The interphase should enable bank employees to add new customer, update customer information, delete customer information, and check personal information.
7. Interface for Mortgage application and Information: The interphase should enable customers to apply for mortgage and bank employees to approve mortgage application, modify mortgage details and record mortgage details.
8. Interface for Account Information: The interphase should enable bank officials to enter records for the accounts.
9. Interface to create new account, Close account, Modify account details.
10. Interface for Employee Information: The interphase should enable bank officials to add employee, update employee information, remove employee from the system.
Report Requirements:
11. Branch Report: Showing the list of branches, the customers tied to these branches and the types of mortgages taken.
12 Customer Report: List of all customers, their names, phone numbers and date of birth
13 Mortgage Report: Showing the mortgage numbers, mortgage types, the credit score of each mortgage taker and the date collected
14 Account Transaction Report: Showing the transaction details of each account such as the opening, balance, closing balance, currency, and account holders.
15 Employee Report: Showing the employee details, name, SSN, the role and employee status as well as the branch the employee is tied to.
Highlights
1. Entity-Relationship (E-R) Model- I created a comprehensive E-R model using Microsoft Visio, ensuring accurate representation of all entities, attributes, and relationships. I verified the labeling of entities and attributes according to the business problem description. I incorporated maximum and minimum cardinalities to depict relationships effectively.
2. Database Design Model: I transformed the E-R diagram into a database design model, considering normalization. I ensured accurate transformation of entities, specification of primary keys, candidate keys, and foreign keys. Attribute properties such as null status, data type, default value, and data constraints were specified and relationships were marked appropriately and depicted maximum and minimum cardinalities.
3. Database Schemas: I wrote schemas based on the database design model, defining the structure and relationships of the tables.
4. Database Implementation with SQL Server: I implemented the database design and schemas using MySQL Server, creating tables with appropriate field properties, primary keys, foreign keys, and constraints. I inserted different records into each relational table using SQL syntax.
5. Query Development- I developed relevant queries for the project scenario, including INNER JOIN, OUTER JOIN, sub-query, and joins involving three or more tables.
6. Active User Interfaces I designed active user interfaces using PHP to connect to the database.
Conclusion
Through this project, I demonstrated proficiency in basic data analysis skills, including data modeling, database design, SQL implementation, and web interface development. By addressing the business problem through a systematic approach, I provided a comprehensive solution that meets the requirements and delivers actionable insights for decision-making.
