Database Application Report: Case Study for ACCT6001, Semester 2

This report provides a comprehensive analysis of a database application, focusing on a case study involving Butler's Spreadsheet, a brokerage business. The report begins with an introduction highlighting the need for a database to replace the manual processes and spreadsheet-based data management currently employed by the company. It then presents an Entity-Relationship (E-R) diagram and a relational schema, detailing the structure of the database. The core of the report includes the design of database tables for lenders, property appraisers, and borrowers, complete with data examples. Several SQL queries are developed to retrieve specific information, such as the number of properties handled by each appraiser, lender details, and borrowing details. The report also presents forms for data entry and a sample report generated using a query. Finally, the report addresses IT security and privacy concerns, emphasizing the importance of safeguarding sensitive customer data and ensuring the integrity of the database. The bibliography includes relevant database and information systems references.

Running head: ACCOUNTING INFROMATION SYSTEM
Accounting information system
Name of the Student:
Name of the University:
Author Note

1
ACCOUNTING INFROMATION SYSTEM
Table of Contents
Introduction......................................................................................................................................2
Need for the implementation of the database..................................................................................4
E-R Diagram....................................................................................................................................4
Relational Schema...........................................................................................................................5
Database Tables...............................................................................................................................6
Queries.............................................................................................................................................8
Query 1: The number of properties handled by each property appraiser....................................9
Query 2: The details of the lenders..............................................................................................9
Query 3: The report for the borrowing details...........................................................................10
Forms and Reports.........................................................................................................................10
Forms.........................................................................................................................................10
Report........................................................................................................................................12
IT Security and Privacy.................................................................................................................13
Bibliography..................................................................................................................................16

2
ACCOUNTING INFROMATION SYSTEM
Introduction
The development of the report has been done here for the discussion on the importance
of the databases. The discussions relate with the case study of Butler’s Spreadsheet to make the
readers the understand the need for database with the current predicament faced by the butlers’
organization who deals with the brokerage business. Therefore, as per their business
requirements the company the stores the details of the Borrowers and the lenders of the property
in their data stores. The company currently maintain all their data in a single spreadsheet. The
details of the spreadsheet maintained by the organization is described below:
Borro
werN
o
Last
Nam
e
First
Nam
e
Current
Address
RequestedMo
rtgageAmoun
t
Len
derN
o
Lende
rNam
e
LenderOff
iceAddress
PropertyA
ppraiserN
o
PropertyAp
praiserNam
e
450 Ada
ms
Jenni
fer
4 Do Rd $245,000 13 Excel
Mortg
age
6 Shore Dr 8 Ad Appraiser
451 Ada
mson
Davi
d
5 So St $124,000 13 Excel
Mortg
age
6 Shore Dr 9 So Appraiser
452 Bron
sosn
Paul 3 To Dr $345,000 14 CCY 2 Buck Rd 10 Jay
Appraiser
453 Brow
n
Mari
etta
3 Go St $55,000 15 Advan
tage
Lender
s
3 Lake Dr 10 Jay
Appraiser
454 Charl
es
Kenn
eth
7 Do Rd $25,000 16 Capita
l
8 Coral Rd 8 Ad Appraiser

3
ACCOUNTING INFROMATION SYSTEM
Saving
s
455 Coult
er
Trace
y
13 So St $216,350 13 Excel
Mortg
age
6 Shore Dr 8 Ad Appraiser
456 Foste
r
Harol
d
8 To Dr $115,000 12 Nation
al
Mortg
age
7 Ten St 9 So Appraiser
457 Fran
k
Vern
on
14 Go St $90,000 12 Nation
al
Mortg
age
7 Ten St 10 Jay
Appraiser
458 Hol
mes
Heat
her
9 Do Rd $450,000 16 Capita
l
Saving
s
8 Coral Rd 10 Jay
Appraiser
459 Joha
nson
Sand
y
11 So St $70,000 15 Advan
tage
Lender
s
3 Lake Dr 9 So Appraiser
460 John
son
Jame
s
12 To
Dr
$15,000 12 Nation
al
Mortg
age
7 Ten St 10 Jay
Appraiser
461 Jones Holly 5 Go St $65,000 15 Advan
tage
3 Lake Dr 9 So Appraiser

4
ACCOUNTING INFROMATION SYSTEM
Lender
s
Need for the implementation of the database
The current business procedures of the organization are performed manually and all the
operations of the organization are performed using the spreadsheets. They have been facing a lot
of problem due to this, as they have to update their data manually in the system. In addition to
this, it becomes very difficult for the staffs in the organization to fetch results from the
spreadsheet as they are required to store the spreadsheet using different type of storing options.
Hence, development of a database would be very helpful of the staffs in the organization so that
they proceed with the different type of database transaction that would be helpful for their
business process.
E-R Diagram

5
ACCOUNTING INFROMATION SYSTEM
Relational Schema
After the normalization of the data in the spreadsheet the database structure can be
obtained. The structured database obtained is provided below:
Lender (LenderNo (pk), LenderName, LenderOfficeAddress)
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
The details included in the lender table are LenderNo, LenderName and LenderAddress.
Then we further proceed with the Property Details.
Property (PropertyAppraiserNo (pk), PropertyAppraiserName)
PropertyAppraiserNo PropertyAppraiserName
8 Ad Appraiser
9 So Appraiser
10 Jay Appraiser
The details contained in the table are the PropertyAppraiserNo, PropertyAppraiserName
having the primary key PropertyAppraiserNo.

6
ACCOUNTING INFROMATION SYSTEM
Borrowing details (BorrowerNo (pk), LastName, FirstName, CurrentAddress,
RequestedMortageAmount, LenderNo (fk), PropertyAppraiserNo (fk))
Borrower
No
LastNa
me
FirstNa
me
CurrentAddr
ess
RequestedMortgageAm
ount
Lender
No
PropertyApprai
ser No
450 Adams Jennifer 4 Do Rd $245,000 13 8
451 Adamso
n
David 5 So St $124,000 13 9
452 Bronson Paul 3 To Dr $345,000 14 10
453 Brown Marietta 3 Go St $55,000 15 10
454 Charles Kenneth 7 Do Rd $25,000 16 8
455 Coulter Tracey 13 So St $216,350 13 8
456 Foster Harold 8 To Dr $115,000 12 9
457 Frank Vernon 14 Go St $90,000 12 10
458 Holmes Heather 9 Do Rd $450,000 16 10
459 Johanson Sandy 11 So St $70,000 15 9
460 Johnson James 12 To Dr $15,000 12 10
461 Jones Holly 5 Go St $65,000 15 9
The details of the borrower table are BorrowerNo, LastName, FirstName,
CurrentAddress, RequestedMortageAmount, LenderNo, PropertyAppraiserNo where
BorrowerNo is the primary key. In addition to this the table is connected to the Lender and the
PropertyAppraiser table with the help of LenderNo and PropertyAppraiserNo which are the
foreign key in these tables.

7
ACCOUNTING INFROMATION SYSTEM
Database Tables
Borrowing details
Design
Table Data
Lender details
Design

8
ACCOUNTING INFROMATION SYSTEM
Table Data
Property Appraiser details
Design
Table Data

9
ACCOUNTING INFROMATION SYSTEM
Queries
A number of queries has been developed and the description of the queries have been
provided below:
Query 1: The number of properties handled by each property appraiser
SELECT PropertyAppraiser.PropertyAppraiserNo, PropertyAppraiser.PropertyAppriaserName,
Count(Borrower.BorrowerNo) AS CountOfBorrowerNo
FROM PropertyAppraiser INNER JOIN Borrower ON
PropertyAppraiser.PropertyAppraiserNo=Borrower.PropertyAppraiserNo
GROUP BY PropertyAppraiser.PropertyAppraiserNo,
PropertyAppraiser.PropertyAppriaserName;
Query 2: The details of the lenders
SELECT Lender.LenderName, Lender.LenderAddress, Lender.LenderNo

10
ACCOUNTING INFROMATION SYSTEM
FROM Lender;
Query 3: The report for the borrowing details
SELECT Borrower.BorrowerNo, Borrower.FirstName, Borrower.LastName,
Borrower.CurrentAddress, Borrower.RequestedMortgageAmount, Lender.LenderName,
PropertyAppraiser.PropertyAppriaserName
FROM PropertyAppraiser INNER JOIN (Lender INNER JOIN Borrower ON Lender.LenderNo
= Borrower.LenderNo) ON PropertyAppraiser.PropertyAppraiserNo =
Borrower.PropertyAppraiserNo;

11
ACCOUNTING INFROMATION SYSTEM
Forms and Reports
Forms
Lender Form
Property Appraiser Form

12
ACCOUNTING INFROMATION SYSTEM
Borrower Form

13
ACCOUNTING INFROMATION SYSTEM
Report
The query designed for creating the report is provided below:
SELECT Borrower.BorrowerNo, Borrower.FirstName, Borrower.LastName,
Borrower.CurrentAddress, Borrower.RequestedMortgageAmount, Lender.LenderName,
PropertyAppraiser.PropertyAppriaserName
FROM PropertyAppraiser INNER JOIN (Lender INNER JOIN Borrower ON Lender.LenderNo
= Borrower.LenderNo) ON PropertyAppraiser.PropertyAppraiserNo =
Borrower.PropertyAppraiserNo;
Using the above query the details in the report was created with the help of the user
details.

14
ACCOUNTING INFROMATION SYSTEM
IT Security and Privacy
In the given butler’s spreadsheet, it can be observed that the data plays the most crucial
role. Thus it becomes essential to ensure that proper security is provided to words that the stored
data. Every organization maintains a database for the purpose of storing the crucial data
regarding each customer. Thus the main concern of implementing IT security and privacy is to
ensure that the database is being protected from hackers. For the Financing organization it
becomes very much essential to maintain the confidentiality of the data as it holds crucial data of
the customers. This data needs to be maintained effectively so that it does not get effected by
attackers.
The main concern of IT security easy to protect the data, programs, networks and other
devices from unauthorized person. This ensures that only authorized users have the authority to
access the confidential data. Without IT security it will become difficult to manage the data
within the organization. Further it will become difficult to protect the confidential data from
getting breached. IT security helps in maintaining confidentiality as it ensures that the data are
being managed successfully. The organization mainly functions at providing better services
without sacrificing the needs of the customers. The Butler organization performs their business
by managing lending and borrowing data for the customers. There arises a huge need to have
proper implementation of privacy and security strategies that can ensure better performance
throughout the organization. Major benefits that are offered with the implementation of proper IT
security within the organization is that it improves the compliance rate, provides faster detection
of errors and offers better recommendation towards the organization. Apart from this the other
benefits that are offered includes reduction in inside fraud cases, data leakages and data theft.
The main reason behind IT security implementation is that it helps in fulfilling the needs of the

15
ACCOUNTING INFROMATION SYSTEM
organization without hampering the data. Database security is one of the major concern for every
organization as it ensures that the store data is being protected. Moreover, this also ensures that
the database is protected from physical damages. With the proper implementation of IT security
and privacy it becomes easy for the organization to manage the data. Security aspect needs to be
enhanced so that overall performance of the organization can be enhanced effectively. While
maintaining a database the main focus that is being maintained by alarm developer includes
maintaining the integrity of the database, ensuring proper privacy and including effective baby
vacation so that no unauthorized person gets the access over the database. database management
becomes essential as it ensures better performance throughout the organization.
IT security is mainly concerned with providing proper supported to words the butler
financing company. With the help of proper IT security, it will become easy for the organization
to enhance their strategies. In addition to this there is a huge needed to implement integrity for
the purpose of purpose of maintaining the database. The organization mainly concerned with
providing proper support the towards the Butler financing company. With the help of proper IT
security, it will become easy the organization to enhance their strategies. Innovation today’s
where is the usual needed to implement integrity within the database so that thing data can be
protected from duplication. It has been observed that It security and privacy are the main factors
in the development process for an organization. Data privacy helps in giving competitive
advantages towards the organization. These ensures that the organization is able to meet the
needs of the customers without sacrificing the privacy concern. Thus with the implementation of
proper security and privacy concept it will become easy to manage the stored data within the
organization.

16
ACCOUNTING INFROMATION SYSTEM
Bibliography
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education
India.
Van Aken, D., Pavlo, A., Gordon, G.J. and Zhang, B., 2017, May. Automatic database
management system tuning through large-scale machine learning. In Proceedings of the 2017
ACM International Conference on Management of Data (pp. 1009-1024). ACM.
Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data (pp.
1753-1758). ACM.