Design a database to automate the intake process for an estate planning attorney. The current process...

Transcribed Image Text:

Design a database to automate the intake process for an estate planning attorney. The current process is based on capturing information in the attached form which every client completes. Specifically, the forms capture the family members who may be the beneficiaries of the estate, as well as a list of the various types of assets and the estimated values. You are hired to develop a database back-end that will serve as the backend to a web-based front-end that another team is developing. Design a logical schema for implementing this database on a relational database management system. List constraints, including primary-key and foreign-key constraints. Also, list any constraints and business rules uncaptured by the ERD. Joint Asset Questionnaire for Estate Planning JOINT ASSET QUESTIONNAIRE [Date] Husband's name [husband's name] U.S. Citizen [Y/N] Husband's birth date: [husband's birth date] Social Security number: [Social Security number] Wife's name [wife's name] U.S. Citizen [Y/N] Wife's birth date: [wife's birth date] Social Security number: [Social Security number] Names (and dates of birth) of children, their spouses, and grandchildren Children Dates of Birth Spouse Grandchildren Dates of Birth ITEMIZATION OF ASSETS Please list as many items as applicable in each category of assets in the appropriate column, depending on who owns the asset. Also include approximate value of asset in category. Residence Cottage or condominium Other real estate Bank accounts and other cash deposits Marketable securities 1. Address Value: S Registered in Husband's Name 2. Address Value: S 3. ... 1. Address Value: S 2. Address Value: S 1. Address Value: S 1. 2. Address Value: S Bank and Account Info Value: S Beneficiary Added: 2. Bank and Account Info Value: S Beneficiary Added: Registered in Wife's Name 1. Address Value: S 2. Address Value: S 1. Address Value: S 2. Address Value: S 1. Address Value: S 2. Address Value: S 1. *** Bank and Account Info Value: S Beneficiary Added: 2. Investment Account Info 2. Investment Account Info Value: $ Value: $ Beneficiary Added: Beneficiary Added: *** 1. Address Value: S Registered in Joint Name 2. Address Value: S 1. Address Value: S 12. Address Value: S 1. Address Value: S *** 2. Address Value: S 2. Bank and Account Info 2. Bank and Account Info Value: $ Beneficiary Added: Value: $ Beneficiary Added: 1. Bank and Account Info Value: S Beneficiary Added: 1. Investment Account Info 1. Investment Account Info 1. Investment Account Info Value: S Value: $ Beneficiary Added: Value: S Beneficiary Added: Beneficiary Added: 2. Investment Account Info Value: $ Beneficiary Added: O Closely-held business interest Pension or profit- sharing plans, IRAS, 401(k)s, 403(b)s, etc. Other assets amount less policy loans) Life insurance (face 1. Life Insurance and Beneficiary Info Value: S TOTAL Registered in Husband's Name 1. Business and Ownership 1. Info Value: S 2. Business and Ownership 2. Value: S 2. Life Insurance and Beneficiary Info Value: S 1. Employer Name and Account Info Value: S Beneficiary Added: 2. Employer Name and Account Info Value: S Beneficiary Added: 1. Asset Details Value: S 2. Asset Details Value: S $ Registered in Wife's Name Business and Ownership 1. Value: $ Business and Ownership 2. Value: S 1. Life Insurance and Beneficiary Info Value: S 2. Life Insurance and Beneficiary Info Value: S 1. Employer Name and Account Info Value: S Beneficiary Added: *** 2. Employer Name and Account Info Value: S Beneficiary Added: 1. Asset Details Value: $ 2. Asset Details Value: S Registered in Joint Name Business and Ownership Value: S Business and Ownership Value: S 1. Life Insurance and Beneficiary Info Value: $ 2. Life Insurance and Beneficiary Info Value: S 1. Employer Name and Account Info Value: S Beneficiary Added: 2. Employer and Account Info Value: $ Beneficiary Added: 1. Asset Details Value: S 2. Asset Details Value: S Design a database to automate the intake process for an estate planning attorney. The current process is based on capturing information in the attached form which every client completes. Specifically, the forms capture the family members who may be the beneficiaries of the estate, as well as a list of the various types of assets and the estimated values. You are hired to develop a database back-end that will serve as the backend to a web-based front-end that another team is developing. Design a logical schema for implementing this database on a relational database management system. List constraints, including primary-key and foreign-key constraints. Also, list any constraints and business rules uncaptured by the ERD. Joint Asset Questionnaire for Estate Planning JOINT ASSET QUESTIONNAIRE [Date] Husband's name [husband's name] U.S. Citizen [Y/N] Husband's birth date: [husband's birth date] Social Security number: [Social Security number] Wife's name [wife's name] U.S. Citizen [Y/N] Wife's birth date: [wife's birth date] Social Security number: [Social Security number] Names (and dates of birth) of children, their spouses, and grandchildren Children Dates of Birth Spouse Grandchildren Dates of Birth ITEMIZATION OF ASSETS Please list as many items as applicable in each category of assets in the appropriate column, depending on who owns the asset. Also include approximate value of asset in category. Residence Cottage or condominium Other real estate Bank accounts and other cash deposits Marketable securities 1. Address Value: S Registered in Husband's Name 2. Address Value: S 3. ... 1. Address Value: S 2. Address Value: S 1. Address Value: S 1. 2. Address Value: S Bank and Account Info Value: S Beneficiary Added: 2. Bank and Account Info Value: S Beneficiary Added: Registered in Wife's Name 1. Address Value: S 2. Address Value: S 1. Address Value: S 2. Address Value: S 1. Address Value: S 2. Address Value: S 1. *** Bank and Account Info Value: S Beneficiary Added: 2. Investment Account Info 2. Investment Account Info Value: $ Value: $ Beneficiary Added: Beneficiary Added: *** 1. Address Value: S Registered in Joint Name 2. Address Value: S 1. Address Value: S 12. Address Value: S 1. Address Value: S *** 2. Address Value: S 2. Bank and Account Info 2. Bank and Account Info Value: $ Beneficiary Added: Value: $ Beneficiary Added: 1. Bank and Account Info Value: S Beneficiary Added: 1. Investment Account Info 1. Investment Account Info 1. Investment Account Info Value: S Value: $ Beneficiary Added: Value: S Beneficiary Added: Beneficiary Added: 2. Investment Account Info Value: $ Beneficiary Added: O Closely-held business interest Pension or profit- sharing plans, IRAS, 401(k)s, 403(b)s, etc. Other assets amount less policy loans) Life insurance (face 1. Life Insurance and Beneficiary Info Value: S TOTAL Registered in Husband's Name 1. Business and Ownership 1. Info Value: S 2. Business and Ownership 2. Value: S 2. Life Insurance and Beneficiary Info Value: S 1. Employer Name and Account Info Value: S Beneficiary Added: 2. Employer Name and Account Info Value: S Beneficiary Added: 1. Asset Details Value: S 2. Asset Details Value: S $ Registered in Wife's Name Business and Ownership 1. Value: $ Business and Ownership 2. Value: S 1. Life Insurance and Beneficiary Info Value: S 2. Life Insurance and Beneficiary Info Value: S 1. Employer Name and Account Info Value: S Beneficiary Added: *** 2. Employer Name and Account Info Value: S Beneficiary Added: 1. Asset Details Value: $ 2. Asset Details Value: S Registered in Joint Name Business and Ownership Value: S Business and Ownership Value: S 1. Life Insurance and Beneficiary Info Value: $ 2. Life Insurance and Beneficiary Info Value: S 1. Employer Name and Account Info Value: S Beneficiary Added: 2. Employer and Account Info Value: $ Beneficiary Added: 1. Asset Details Value: S 2. Asset Details Value: S

Answer rating: 100% (QA)

Solutions Step 1 Designing a database for an estate planning attorney to automate the intake process involves capturing information about clients beneficiaries and assets Heres a logical schema for th... View the full answer