Question

Crescent Bank made it’s name by lending to farmers and agricultural machinery suppliers. Traditionally, Crescent has helped farmers by providing loans for property, plant, and equipment (PPE), and by closing the gap between agricultural machinery retailers and the agricultural machinery original equipment manufacturers (OEM). Associated with these banking activities are CRUD operators - Creating accounts, Reading accounts, Updating accounts, and Deleting accounts...with more accounts being created than deleted. Because of how well Crescent has performed in the agricultural sector, it now has locations in several towns and dabbles with other business customers and several thousand individual accounts.

Recently, Bainbridge Farms, one of Crescent’s major agricultural customers has asked if the bank would be open to providing insurance for it and it’s employees. Bainbridge understands that the insurance industry isn’t exactly banking, but how could it be that different? Because of the learning curve, Bainbridge was hoping Crescent would start by providing life insurance policies (as the policies are only closed when a customer either closes the account or (unfortunately) dies). But Bainbridge is hoping that Crescent will extend beyond life insurance to provide insurance for crops, property, plant, and equipment.

For the above scenario answer the following questions:

1.

Identify all hierarchy of data elements necessary to create a database for this case. This means identifying the name of the database, all files, records identified, fields, and values as presented in the case.

Please present these items in the following format:

Database Name -

<Name>

Files -

<File1...>

(Field 1 associated with File1) : Value if present

(Field 2 associated with File1) : Value if present...

(Field X associated with File1) : Value if present

<File X>

(Field 1 associated with FileX) : Value if present

(Field 2 associated with FileX) : Value if present...

(Field X associated with FileX) : Value if present

Records -

<Record 1...>

<Record X>

2.

Create an ERD for Crescent Bank that addresses all elements of the case as identified in question 1. Please, only include the primary and foreign keys that will create the appropriate relationships to successfully model the diagram.

Answer 1

Database Name:

crescent_bank_db

Files / Tables:

Customers: ( table which contains details of all its customers )

1. customer_id: ( Primary Key )
2. status: Active / Inactive ( Shows customer's account status if it is active or deactivated due to death )
3. name: ( Name of customer )
4. age: (Age of customer )
5. mobile: ( Contact Number of customer )
6. address: ( Address of customer )

Accounts: ( table which contains details of loan accounts of its customers )

1. account_no: ( Primary Key )
2. customer_id: ( Foreign Key )
3. loan_amount: ( Loan amount taken by the customer )
4. disbursed_date: ( Date on which the loan was disbursed )
5. due_date: ( Date on which the loan is due )
6. outstanding_principal: ( Amount which is still due after some partial repayments)
7. repaid_date: ( Last date on which some repayment was made on loan )
8. collateral: Yes / No ( If some collateral was given by the customer against that loan )
9. status: Financed / Repaid ( Loan status if it is Financed or Repaid or any other status)

Insurance: ( table which contains details of insurance )

1. insurance_id: ( Primary Key)
2. customer_id: ( Foreign Key)
3. insurance_type: LIFE / CROP / PROPERTY / PLANT / EQUIPMENT ( Depicts the type of insurance taken)
4. policy_date: ( contains date when the insurance policy was issued )
5. sum_assured: ( total amount to be paid on insurance )
6. policy_tenure: ( tenure of policy in years )
7. premium_mode: Monthly / Quarterly / Half-Yearly / Annually
8. premium_amount: ( Amount to be paid as premium )
9. premium_due_date: ( Date on which the next premium will become due )
10. Nominee: ( Nominee of the customer )
11. documents_submitted: ( Info of all documents submitted towards the insurance policy )