Database Design Project

This assignment involves designing a database for a financial service provider. The solution outlines the entities involved: Branch, Employee, Customer, Transaction, AccountType, Accounts, and CustomerAccounts. Each entity's attributes are defined, along with the relationships between them. The design considers functionalities such as account creation, transactions (withdrawals, deposits, transfers), and customer detail updates. The solution includes an Entity-Relationship Diagram (ERD) and detailed descriptions of each entity and its attributes, including primary and foreign keys. The relationships between entities are explained, clarifying the cardinality (one-to-one, one-to-many) for each connection. The documentation thoroughly explains the purpose and structure of the database, ensuring it can effectively manage all aspects of the financial service provider's operations.

Problem Analysis
To design a database for a financial service provider as discussed in my assignment, first there are
branches of the service provider and in all the branches there are employees and employees are headed
by branch manager which is present in each branch. A person can be customer by opening their account
in any of the branch and each customer is guided by the bank employee and in future he will be his
mentor to do any stuff in the bank. After opening their account in the bank customer can able to withdraw,
deposit and transfer money to another account. Customer can also able to change its details like address.
The design of the database should handle all this stuff and everyone can able to do their task correctly.
Users
UserBrief DescriptionFunction Needed
Bank EmployeeThe person who work in any
branch of the bank.
They will able to create a new account
for the customer and provide help in
the future and can manage existing
user.
CustomersThe person who has bank
account.
They will be able to update their
personal details and do all the banking
stuff.
Entities
Key: Red Text = Brief Description, Green Text = Relationship descriptions
Branch - Contain all the information of all branches. A branch is related to the its employee.
Employee - Contain all the information of each employee of their respective branch. Employee will add
the new user for the branch and access the type of account.
Customer - Contain all the personal information of each customer. Customer will do transaction and can
access its account.
Transaction - Contain the record of all the transaction done by the user. Transaction is done by any
customer and with each transaction there is updation in customer accounts.
AccountType - Contain how many types of account is provided by the bank. Employee will access the
account type to see the data of all accounts.
Accounts - Contain the banking information of all the customers. Accounts of user keep on updating
while the customer will do any transaction.
CustomerAccounts - Contain the information of related account number. Relation between the
customers is there.

Entities with Attribute
Key: (E) = Entity, (R, between [E] and [E]) = Relationship
Branch (E)
branchNumber {PK} - Unique number for each branch.
branchManager - Manager of the branch.
branchAddress - Address of the the branch.
Employee (E)
employeeID {PK} - Unique ID of employee.
employeeName - Name of the employee.
Consists (R, between employee and branch)
branchNumber {FK} - Branch number to which employee is associated.
Customer (E)
customerID {PK} - ID of the customer.
customerName - Name of the customer.
customerAddress - Address of the customer.
customerDateTime - Date and time on which customer account is generated.
Generates(R, between customer and employee)
holdStatus - Status of the customer account that is active or inactive.
Transaction (E)
transactionID {PK} - ID of transaction.
transactionType - Type of the transaction.
amount - Amount of the transaction.
Do(R, between customer and transaction)
customerID {FK} - ID of the customer who done transaction.
receiverID {FK} - ID of the customer who receives money.
Account Type (E)
accountNumber {PK} - Account number of each account type.
accountType - Type of account.
Intrest - Intrest given by the bank.
minBalance - Minimum balance for that account.
maxCreditLimit - Maximum credit limit given to this account.
fees - Fees taken by the bank.
Accounts (E)
accountID {PK} - Accountid of accounts.
employeeID {FK} - Employee ID of the employee associated with account.
Balance - Remaining balance of the account.
creditLimit - Credit limit of the account given by employee.
Give type(R, between accountType and account)
accountNumber {FK} - Account number of which account is managed.
holds(R, between customer and account)
customerID {FK} - ID of the customer.

Customer Accounts (E)
customerAccountID {PK} - Unique ID of customer account.
customerIDs - All IDs of customer to a particular account.
getDetails(R, between account and customerAccount)
accountID {FK} - ID of the customer account.

Entity Relationship Documentation

Branch
The branch element is consist of of three attributes and branch number is treated as the primary key
because it will be unique. This attribute is gives the information of all the branches.
Employee
Employee consist of 3 attributes in which employee ID as the primary key and branch number as the
foreign key because it shows under which branch doe this employee work. This contain the basic
information of the employee.
Relationship between Branch and Employee
As there is will be multiple employee under each branch so that why I put one on the branch side and * on
the Employee side.
Customer
It contain total five attributes in which there is customer ID as the primary key which is unique. It does
contain all the basic information of the customer.
Relationship between Customer and Employee
There will be multiple customer under each employee because it the duty of employee to generate or add
new customer.
Transaction
It contain all the transaction of each user and transaction ID uses as primary key to differentiate each
transaction. To keep record of which transaction belongs to which customer there is customer ID as
foreign key.
Relationship between Customer and Transaction
Each customer can do multiple transaction and all the records goes in this attribute related to customer
transaction so it will be one to many relationship between customer and Transaction.

Account Type
It contain six attribute and account number as the primary key. In this there is data of the different type of
account facility provided by the bank is recorded.
Accounts
It contain six attribute and the account Id which is also unique is taken as primary key. It record the
account of each customer and customer’s account type is recorded over there and it is treated as foreign
key.
Relationship between Accounts and Customer
Each account is related to each registered customer and one customer has only one account.
Customer Accounts
It does contain three attributes and each record is differentiated by customer account ID and is treated as
primary key. It does contain account Id as foreign key which is getting from the account attribute.
Relationship between Customer Accounts and Accounts.
A customer had its only on account and and it does registered in customer account attribute once so
there is for each account present in the the account attribute there is only one to one relationship between
customer account and accounts.