Question

In: Computer Science

Crescent Bank made it’s name by lending to farmers and agricultural machinery suppliers. Traditionally, Crescent has...

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.

Solutions

Expert Solution

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 )


Related Solutions

There are three loan officers at farmers National Bank. All decisions on mortgage loans are made...
There are three loan officers at farmers National Bank. All decisions on mortgage loans are made by one of these officers. The president of the bank would like to be sure that the rejection rate is about the same for the three officers. A sample of 200 recent applications yielded the following results. Is the rejection rate related to the officer that processes the loan? Use the 0.05 significance level. Loan Officer Felix    Otis Foxburrow Approved 50 70 55...
The construction industry in Japan has traditionally been dominated by its domestic suppliers. Few foreign construction...
The construction industry in Japan has traditionally been dominated by its domestic suppliers. Few foreign construction companies have won projects in Japan. What aspects of Japan's political forces may have influenced this local control over the Japanese construction market? What political or regulatory forces may lead to the opening of this market for foreign firms?
The agricultural supply chain is made up of farmers, food assemblers and brokers, food manufacturers and processors, food distributors and wholesalers, and food retailers.
The agricultural supply chain is made up of farmers, food assemblers and brokers, food manufacturers and processors, food distributors and wholesalers, and food retailers.A. Which sector in the supply chain can best be categorized as perfectly competitive? Why?B. Which sector in the supply chain can best be categorized as oligopolistic? Why?C. Which sector in the supply chain can best be categorized as monopolistically competitive? Why?
Technology has been very important to agricultural markets for the United States and has made it...
Technology has been very important to agricultural markets for the United States and has made it a very large food-producing nation in the world. When looking at the money that the US government spends on agricultural markets with the subsidies that they provide, do you think there could be other options for the money spent? How can you measure the inefficiency of the subsidies that are provided in the market?
Name two ways a bank minimizes credit risk before a loan is made. Name two ways...
Name two ways a bank minimizes credit risk before a loan is made. Name two ways it does this after the loan is made.
Group lending was the most striking innovation of the Grameen Bank, and it has been widely...
Group lending was the most striking innovation of the Grameen Bank, and it has been widely imitated by other microfinance initiatives. (a). How does group lending address the problems that limit the supply of credit to poor households from traditional formal-sector lenders (e.g., banks)? (b). A careful study of the operations of the Grameen Bank in the early 1990s concluded that each dollar of lending increased the consumption expenditures of borrowing households by 17 cents relative to what they would...
An officer of a micro-lending company in a developing country has made eight loans of $100...
An officer of a micro-lending company in a developing country has made eight loans of $100 each. Let Xi be a random variable representing the real rate of return on loan i, i = 1, ..., 8. The real rate of return on loan i is a random variable because a borrower sometimes delays or skips payments, and because inflation during the life of the loan can vary. Assume Xi is approximately normally distributed with mean 0.10, and standard deviation...
You are the lending officer for Bank 6/3. A company wants to borrow money and has...
You are the lending officer for Bank 6/3. A company wants to borrow money and has the following assets: building, inventory and fixtures. A. Discuss how to fully protect the loan. B. Discuss what the bank would do if the debtor fails to pay the loan.
Since the beginning of the year, the US central bank has lowered its key lending rate...
Since the beginning of the year, the US central bank has lowered its key lending rate from 1.5% to 0%. In addition, the US government has injected nearly $3T in capital (14% of 2019’s GDP) into the US market through targeted loan programs, grants, and taxpayer-targeted stimulus payments. These fiscal policies have been more aggressive than what many governments have done around the world. Based on our studies, we would expect these actions, absent other factors, would weaken the US...
North Bank has been borrowing in the U.S. markets and lending abroad, thereby incurring foreign exchange...
North Bank has been borrowing in the U.S. markets and lending abroad, thereby incurring foreign exchange risk. In a recent transaction, it issued a one-year $2.25 million CD at 4 percent and is planning to fund a loan in British pounds at 6 percent for a 2 percent expected spread. The spot rate of U.S. dollars for British pounds is $1.4500/£1. a. However, new information now indicates that the British pound will appreciate such that the spot rate of U.S....
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT