COMPREHENSIVE PROBLEM The Butler Financing Company runs a mortgage brokerage business that matches lenders and borrowers....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
COMPREHENSIVE PROBLEM The Butler Financing Company runs a mortgage brokerage business that matches lenders and borrowers. Table 4-14 lists some of the data that Butler maintains on its borrowers and lend- ers. The data are stored in a spreadsheet that must be manually updated for each new borrower. lender, or mortgage. This updating is error-prone, which has harmed the business. In addition, the spreadsheet has to be sorted in many different ways to retrieve the necessary data. TABLE 4-14 Butler Financing Company Spreadsheet REQUESTED MORTGAGE LENDER LENDER AMOUNT NUMBER NAME $245,000 13 Excel $124,688 BOR- ROWER LAST FIRST CURRENT NUMBER NAME NAME ADDRESS 450 Adams Jennifer 450 Peachtree Rd. 451 452 453 454 455 456 457 458 459 460 461 Adamson David Bronson Paul Brown Foster Frank Marietta 310 Loop Highway Charles Kenneth 3 Commons Blvd. Coulter Tracey 1367 500 Loop Jones Highway Johnson James 312 Mountain $345,000 View Dr. Harold 678 Loop Highway Holly Peachtree Rd. Vernon 210 Bicayne Blvd. 1121 Bicayne Blvd. Holmes Heather Johanson Sandy 817 Mountain View Dr. 985 Loop Highway 1650 Wash ington Blvd. $57,090 $34,000 $216,505 $117,090 $89,000 $459,010 $67,900 $12,000 $67,890 13 14 15 16 13 12 12 16 15 12 15 Mortgage Excel Mortgage CCY Advantage Lenders Capital Savings Excel Mortgage National Mortgage National Mortgage Capital Savings Advantage Lenders LENDER OFFICE ADDRESS 6890 Sheri- dan Dr. 6890 Sheri dan Dr. 28 Buck- head Way 3345 Lake Shore Dr. 8890 Coral Blvd. 6890 Sheri- dan Dr. 750 16 St. 750 16 St. 8890 Coral Blvd. 3345 Lake Shore Dr. National 750 16 St. Mortgage Advantage 3345 Lake Lenders Shore Dr. PROPERTY PROPERTY APPRAISER APPRAISER NUMBER NAME 8 Advent 9 10 10 8 8 10 10 9 10 9 Appraisers Independent Appraisal Service Jones Property Appraisers Jones Property Appraisers Advent Appraisers Advent Appraisers Independent Appraisal Service Jones Property Appraisers Jones Property Appraisers Independent Appraisal Service Jones Property Appraisers Independent Appraisal Service Requirements: 1. Create a database from Butler's spreadsheet that does not have any of the data anomalies explained in class discussions. To test the database, prepare a query to show which borrowers (both borrower number and name) took out loans from Excel Mortgage and who the appraiser was for each loan. COMPREHENSIVE PROBLEM The Butler Financing Company runs a mortgage brokerage business that matches lenders and borrowers. Table 4-14 lists some of the data that Butler maintains on its borrowers and lend- ers. The data are stored in a spreadsheet that must be manually updated for each new borrower. lender, or mortgage. This updating is error-prone, which has harmed the business. In addition, the spreadsheet has to be sorted in many different ways to retrieve the necessary data. TABLE 4-14 Butler Financing Company Spreadsheet REQUESTED MORTGAGE LENDER LENDER AMOUNT NUMBER NAME $245,000 13 Excel $124,688 BOR- ROWER LAST FIRST CURRENT NUMBER NAME NAME ADDRESS 450 Adams Jennifer 450 Peachtree Rd. 451 452 453 454 455 456 457 458 459 460 461 Adamson David Bronson Paul Brown Foster Frank Marietta 310 Loop Highway Charles Kenneth 3 Commons Blvd. Coulter Tracey 1367 500 Loop Jones Highway Johnson James 312 Mountain $345,000 View Dr. Harold 678 Loop Highway Holly Peachtree Rd. Vernon 210 Bicayne Blvd. 1121 Bicayne Blvd. Holmes Heather Johanson Sandy 817 Mountain View Dr. 985 Loop Highway 1650 Wash ington Blvd. $57,090 $34,000 $216,505 $117,090 $89,000 $459,010 $67,900 $12,000 $67,890 13 14 15 16 13 12 12 16 15 12 15 Mortgage Excel Mortgage CCY Advantage Lenders Capital Savings Excel Mortgage National Mortgage National Mortgage Capital Savings Advantage Lenders LENDER OFFICE ADDRESS 6890 Sheri- dan Dr. 6890 Sheri dan Dr. 28 Buck- head Way 3345 Lake Shore Dr. 8890 Coral Blvd. 6890 Sheri- dan Dr. 750 16 St. 750 16 St. 8890 Coral Blvd. 3345 Lake Shore Dr. National 750 16 St. Mortgage Advantage 3345 Lake Lenders Shore Dr. PROPERTY PROPERTY APPRAISER APPRAISER NUMBER NAME 8 Advent 9 10 10 8 8 10 10 9 10 9 Appraisers Independent Appraisal Service Jones Property Appraisers Jones Property Appraisers Advent Appraisers Advent Appraisers Independent Appraisal Service Jones Property Appraisers Jones Property Appraisers Independent Appraisal Service Jones Property Appraisers Independent Appraisal Service Requirements: 1. Create a database from Butler's spreadsheet that does not have any of the data anomalies explained in class discussions. To test the database, prepare a query to show which borrowers (both borrower number and name) took out loans from Excel Mortgage and who the appraiser was for each loan.
Expert Answer:
Answer rating: 100% (QA)
The relational schema for the given table is as shown below BorrowerLenderBorrowerNo LastName FirstName CurrentAddress RequestedMortgageAmount LenderNo LenderName LenderOfficeAddress PropertyAppraiserNo PropertyAppraiserName The primary key is BorrowerNo First normal form According to the first normal form the relation should contain only atomic values ie the relation should not contain any repeating groups As there are no repeating groups in the relation so the relation BorrowerLender is in the first normal form Second normal form According to the second normal form the relation must be in first normal form and each nonkey attribute must depend only on primary key As there are no partial dependencies in the relation so the relation is in the second normal form Third normal form According to the third normal form the relation must be in second normal form and any nonkey attribute should not describe any nonkey attribute LenderNo is a nonkey attribute that functionally determines a nonkey attributes LenderName LenderOfficeAddress So it violates third normal form PropertyAppraiserNo is a nonkey attribute that functionally determines a nonkey attribute PropertyAppraiserName So trivial dependency exists in the relation Hence the relation fails to satisfy third normal form So to satisfy third normal form decompose the relation BorrowerLender as shown below Borrower BorrowerNo LastName FirstName CurrentAddress RequestedMortgageAmount LenderNo PropertyAppraiserNo Lender LenderNo LenderName LenderOfficeAddress PropertyAppraiser ... View the full answer
Related Book For
Cost management a strategic approach
ISBN: 978-0073526942
5th edition
Authors: Edward J. Blocher, David E. Stout, Gary Cokins
Posted Date:
Students also viewed these accounting questions
-
A common measure of productivity, as explained in this chapter and as used by many business and trade organizations including the U.S. Bureau of Labor Statistics, is the ratio of output to input,...
-
A financing statement that does not have the debtor's exact name should still be effective because creditors should always be protected when debtors default.
-
A common measure of productivity, as explained in this chapter and as used by many business and trade organizations, including the U.S. Bureau of Labor Statistics, is the ratio of output to input,...
-
A business wishes to have $80,000 ten years from now to make a down payment on a capital asset. How much money would the business need to invest in equal annual payments at a compound interest rate...
-
A company is criticized because only 13 of 43 people in executive-level positions are women. The company explains that although this proportion is lower than it might wish, it's not a surprising...
-
Do you agree or disagree with the following statement? Television viewers are passive viewers of ads, whereas Internet users take an active role in choosing what to look at-so customers on the...
-
Brian Rafferty ingested finasteride, a drug prescribed to him to treat an enlarged prostate. Finasteride is a generic version of Proscar, a drug manufactured by Merck. Some time after he started to...
-
In the course of routine checking of all journal entries prior to preparing yearend reports, Diane Riser discovered several strange entries. She recalled that the presidents son Ron had come in to...
-
What ways can I make my Relational Data Model conversion meet the following requirements listed below? Am I on the right track? What corrections do I need to address? ERD: My Conversion of ERD onto a...
-
Electro-Phi Inc. (the "Company") is a utility provider that sells electricity to the public. The Company produces the electricity using various forms of power generation, including the burning of...
-
A 4.5 keV electron moves in a circular orbit that is perpendicular to a magnetic field of 0.75 T. (a) Find the radius of the orbit. (b) Find the frequency and period of the orbital motion.
-
If company A is sending money to Company B, then what is the disbursement float for Company A?
-
A spherical buoy of diameter 0.6m and mass 30kg is attached to the seabed by mooring rope and floats fully submerged. determine the tension in the mooring rope. the density of sea water is 1020kgm^-3.
-
5. For the 0-1 Knapsack Problem, the item #1 has weight of 4 and the value of $12; the item #2 has weight of 1 and the value of $1; the item #3 has weight of 5 and the value of $10; and the item #4...
-
Let = {0, 1} and consider the state-transition diagram given in Figure 1. 0 1 A 0 0 B D 1 0 Figure 1: State-transition diagram for Question 4. (a) Give examples of three strings that are accepted by...
-
What is the process involved in cleaving plasmid DNA using restriction enzymes, and how does this method contribute to molecular biology research and genetic engineering endeavors?
-
Several electrons move at speed 6.90 x 105 m/s in a uniform magnetic field with magnitude B= 0.720 T directed downward. Find the magnetic force on the electron at point c. Enter a positive value if...
-
You are the newly appointed tax practitioner to complete Emilys tax return and have downloaded the prefill report for Emilys tax return (hint, you can read what a prefill report is here (Links to an...
-
Lopez & Co. uses flexible budgets for cost control. During March, Lopez spent 2,850 machine hours to produce 10,800 units and incurred $13,000 in total factory overhead, of which $4,500 was for fixed...
-
What are the limitations of joint product cost allocation?
-
The direction of a variance (favorable or unfavorable) is irrelevant in decisions on whether or not to investigate the variance. Do you agree? Why or why not?
-
A phenotypically abnormal individual has a phenotypically normal father with an inversion on one copy of chromosome 7 and a phenotypically normal mother without any changes in chromosome structure....
-
Explain how polytene chromosomes of Drosophila are produced, and describe the six-armed structure they form.
-
Male honeybees, which are haploid, produce sperm by meiosis. Explain what unusual event (compared with other animals) must occur during spermatogenesis in honeybees to produce sperm. Does this...
Study smarter with the SolutionInn App