Abc Assignment Help

ACCT6001 Database on MS Access Platform for Butler Financial Companys Mortgage Assessment 4 Answer

ASSESSMENT BRIEF
Subject Code and Name
ACCT6001 Accounting Information Systems
Assessment
Assessment 4: Database Application – Case Study
Individual/Group
Individual
Length
1500 words +/-10%
Learning Outcomes
.
This assessment addresses the following subject learning outcomes:
  1. Explain the characteristics of relational databases and their role in creation and communication of business intelligence
  2. Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases.
  3. Communicate with IT professionals, stakeholders and user
groups of information systems.

Weighting
30%
Total Marks
100 marks

Context:

The aim of this assessment is to assess the student’s ability to solve business problems using database design tool and software. It also aims to enable students to think about the impacts of using IT in Businesses and communicate key issues through a written report.

Many companies depend on the accurate recording, updating and tracking of their data on a minute-to-minute basis. Employees access this data using databases. An understanding of this technology allows business professionals to be able to perform their work effectively

Instructions:

Create a database from Butler’s spreadsheet that does not have any data anomalies. Use appropriate E-R diagrams to explain the relationships. To test the database, you can create 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

You are able to use MS Access to complete this assessment.

Submission Instructions:

A word document containing:

  1. Introduction - a summary of the case study
  2. Justification of the use of database
  3. Database Design: E-R Diagram
  4. Microsoft Access files used in creating the tables, forms, queries and reports

Criteria:


  • Need for the implementation of the database
  • E-R Diagram
  • Normalised Database Tables
  • Queries
  • Forms and Reports

Case Study:

The Butler Financing com[any runs a mortgage brokerage business that matches lenders and borrowers. The table below lists some of the data that Butler maintains on its borrowers and lenders. 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.

Answer

Introduction

The aim of the report is to provide information about the database built on MS Access platform for the Butler Financial Company’s Mortgage business. As data is an important asset for any business, so they need to ensure proper processes to capture, store, and process data. Traditional file based systems are no longer efficient enough to store and process data as a business grows. Hence, they need suitable database applications to store and process data. 

In the following sections of the report, Butler Financial Company has been used as a case study. The report contains information about transforming their spreadsheet based system into a suitable relational database developed on the MS Access platform. 

Justification of the Use of the Database 

Butler Financial Company runs a mortgage brokerage business. They keeps details of borrowers, lenders and property appraisers in order to match borrowers against lenders to make a deal successful. Once they are also to match and close a deal successful then they earn commission as brokerage service charge. Currently they are storing all data in a spreadsheet based application that require manual updates to be done for each new borrower, lender or mortgage. The update process is highly prone to errors and it needs to sort the spreadsheet in different ways to ensure that data is retrieved based on different conditions. 

The justifications of using database for the Butler Financial Company are listed below. 

  • A database management system helps to store data based on inherent logics and relationships of data.
  • A DBMS helps to deal with data redundancy issue. For example, if the same data is used across multiple files, then it needs to keep all those files updates when the data is updated. If one file is missed then that will contain an obsolete version of data. Now, if any application tries to take data from that particular file, then it will process the obsolete version of data and that will product wrong or obsolete results. This is not desirable in any business. Hence, it needs a database system where enforcing referential integrity helps to keep track of the updates through all relations or tables in a database. So, at any point of time, all relations or tables in a database will contain updated data only (Abraham Silberschatz, Korth & Sudarshan 2015)
  • It helps to automate data input process. Hence, it does not need to make manual data entry for all tables. So, there are lesser chances of errors and the consistency of data in a data base is more than a file based system. 
  • Users are allowed to retrieve data from a database easily using various forms of queries, reports etc. 
  • The cost associated to data storage, retrieval and processing is optimized. 
  • Normalized databases are used to improve performance and consumption of computing resources in terms of storage and processing power. All these not only helps to reduce cost, but also improves the overall performance related to data management for a business (Abraham Silberschatz, Korth & Sudarshan 2015)
  • It makes reporting easy and automated.
  • It helps to run different queries on data sets to sort and process results in different ways. 
  • It also helps to improve security and privacy of data by implementing different security mechanisms on the datasets of a database. Each field of a database can be locked from unauthorized access and alteration. Different views of same dataset can be created to ensure access of different stakeholders to a database for different purposes (Abraham Silberschatz, Korth & Sudarshan 2015)
  • User access controls can be implemented on a database. For example, a database can be password protected to allow only a set of authorized users to make changes in the database or to view content of the database. 
  • It helps to increase integrity of data and to reduce anomalies like insertion anomalies, deletion anomalies, and update anomalies from a database. 

Database Design: ER Diagram 

The given spreadsheet of Butler Financial Company is presented below. It is assumed that the name of the un-normalized version of the table is Mortgage. 

ER Diagram 1

ER Diagram 2


A close inspection on the data presented in the spreadsheet table above shows that there are following functional dependencies in the Mortgage table. 

Borrower No à Last Name, First Name, Current Address

Lender No à Lender Name, Lender Office Address 

Property Appraiser No à Property Appraiser Name 

Borrower No, Lender No, Property Appraiser No à Requested Mortgage Amount

So, a candidate key (or primary key) for the Mortgage table can be {Borrower No, Lender No, Property No}. It is a composite key. And all other attributes are dependent on the primary key. There is no multivalued attribute. Hence, the Mortgage table is in 1NF. 

But there are partial dependencies as some of the attributes are partially dependent on the selected primary key. The decomposed relations based on partial dependencies are, 

  • Borrower (Borrower No, Last Name, First Name, Current Address) 
  • Lender (Lender No, Lender Name, Lender Office Address)
  • Property Appraiser (Property Appraiser No, Property Appraiser Name) 
  • Mortgage (Borrower NoLender NoProperty Appraiser No, Requested Mortgage Amount)

All these relations along with the updated Mortgage relation are in 2NF. Again there is no transitive dependency on any of the relations. Hence, all these are also in 3NF. 

The normalized set of relations are, 

  • Borrower (Borrower No, Last Name, First Name, Current Address) 
  • Lender (Lender No, Lender Name, Lender Office Address)
  • Property Appraiser (Property Appraiser No, Property Appraiser Name) 
  • Mortgage (Borrower NoLender NoProperty Appraiser No, Requested Mortgage Amount)

The ER diagram is, 

 ER diagram

The business rules are, 

  • A lender lends to multiple borrower. 
  • A property appraiser appraises multiple mortgage properties. 

The assumptions are, 

  • A borrower can have multiple mortgage on different properties 

Database Implementation: MS Access Database 

The database contains the following normalized tables, queries, forms and reports. 

Normalized Database tables 

Borrower
BorrowerNo
LastName
FirstName
CurrentAddress
450
Adams
Jennifer
4 Do Rd
451
Adamson
David
5 So St
452
Bronson
Paul
3 To Dr
453
Brown
Marietta
3 Go St
454
Charles
Kenneth
7 Do Rd
455
Coulter
Tracey
13 So St
456
Foster
Harold
8 To Dr
457
Frank
Vernon
14 Go St
458
Holmes
Heather
9 Do Rd
459
Johanson
Sandy
11 So St
460
Johnson
James
12 To Dr
461
Jones
Holly
5 Go St


Lender
LenderNo
LenderName
LenderOfficeAddress
12
National Mortgage
7 Ten St
13
Excel Mortgage
6 Shore Dr
14
CCY
2 Buck Rd
15
Advantage Lenders
3 Lake Dr
16
Capital Savings
8 Coral Rd


Property Appraiser
PropertyAppraiserNo
PropertyAppraiserName
8
Ad Appraisers
9
So Appraisers
10
Jay Appraisers


Mortgage
BorrowerNo
LenderNo
PropertyAppraiserNo
RequestedMortgageAmount
450
13
8
$245,000.00
451
13
9
$124,000.00
452
14
10
$345,000.00
453
15
10
$55,000.00
454
16
8
$25,000.00
455
13
8
$216,350.00
456
12
9
$115,000.00
457
12
10
$90,000.00
458
16
10
$450,000.00
459
15
9
$70,000.00
460
12
10
$15,000.00
461
15
9
$65,000.00


Queries 

  1. Query 1 shows the borrowers with borrower number and name, who took out loans from the lender named, Excel Mortgage and details of the appraisers of the properties. 

The SQL code is, 

SELECT Borrower.BorrowerNo, Borrower.LastName, Borrower.FirstName, Lender.LenderName, [Property Appraiser].PropertyAppraiserName
FROM [Property Appraiser] INNER JOIN (Lender INNER JOIN (Borrower INNER JOIN Mortgage ON Borrower.BorrowerNo = Mortgage.BorrowerNo) ON Lender.LenderNo = Mortgage.LenderNo) ON [Property Appraiser].PropertyAppraiserNo = Mortgage.PropertyAppraiserNo
WHERE Lender.LenderName="Excel Mortgage";


The output of the query is, 

 output of the queryThe other query lists the names of all borrowers, lenders, and property appraisers of Butler Financial Company. This has been used in the report in the database. 

Forms and Reports

  • The Borrower form is there to enter details of a new borrower. 

 Borrower form

  • The Lender form is there to enter details of a new lender.

Lender form

  • The Property Appraiser Form is there to enter details of a new property appraiser. 

Property Appraiser Form

  • The mortgage form is there to enter the requested quote of a new mortgage deal. 

mortgage form

  • The report shows the names of all borrowers, lenders, and property appraisers of Butler Financial Company

names of all borrowers, lenders, and property appraisers IT Security and Privacy

Security and privacy of data is very important. It can be ensured by protecting a database with suitable security solutions like user access control, encryption and by implementing suitable policies for enforcing security standards and rules (Abraham Silberschatz, Korth & Sudarshan 2015). For example, the database can have restricted access to some employees only. Some employees may be allowed to only view data based on searches. Another group of employees may be given the right to change and add data in the tables. It needs to protect the data tables in a database first as those contains raw data. Use of forms and reports helps to reduce direct access to the tables containing data in a database. 

Conclusion 

The report contains the details of transforming the spreadsheet base file of Butler Financial Company into an MS Access database. The report contains justifications of the use of database and how it has been normalized and implemented in the relational database. It also contains information about the conceptual ER design and privacy and security aspects of using a database in a business.  

Customer Testimonials