ACCT6001: Database Design and Implementation for Butler Financing

This report presents a comprehensive case study on database design and implementation for 'The Butler Financing,' a mortgage brokerage. It begins with an introduction and justification for using a database system, highlighting benefits like speed of creation, redundancy reduction, and Microsoft Office integration. The core of the report includes an Entity-Relationship (ER) diagram illustrating the database structure, followed by a detailed explanation of the normalization process, transforming the initial raw data into the third normal form (3NF) to eliminate data redundancy and improve data integrity. The report also covers the creation of queries for data retrieval and presents examples of forms and reports, demonstrating the practical application of the database. The report concludes by summarizing the key findings and emphasizing the significance of MS Access as a valuable tool for data management, storage, and reporting, ensuring data accuracy and efficiency for business operations.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation: A Case Study of
‘The Butler Financing’
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Introduction:....................................................................................................................................2
Justification:.....................................................................................................................................2
ER-Diagram:....................................................................................................................................3
Normalization:.................................................................................................................................4
Query:..............................................................................................................................................9
Forms and Reports:..........................................................................................................................9
Conclusion:....................................................................................................................................11
References:....................................................................................................................................12

2DATABASE DESIGN AND IMPLEMENTATION
Introduction:
Butler financing organization aims to run a business of mortgage brokerage which
matches with the lender and borrowers. The organization aims to store some of the data where
Butler maintaining both its borrower and lender. The complete data is stored in the spreadsheet
which needs to be updated for lender and mortgage. A list of error is being encountered at the
time of updating that can harm the business. Along with this, the spreadsheet requires to be
updated for retrieve different kind of data.
In the coming pages of the report, a justification has been given for the use of database.
Also, the benefits of using Microsoft Access for creating forms and tables has been discussed in
details.
Justification:
Fast way for Creating Database System: Microsoft Access is one of the best tools for
creating a system at the heart. It is inclusive of different aspects which are needed for speed up
the whole process for building database application (Kralj & Hren, 2018). A list of an array is
made available for the overall functionality.
No Redundancy: MS access allow different users to easily store, sort, retrieve data and
store information in the database. In some cases, data is completely manipulated without any
kind of redundancy by analyzing the relationship between sets of data. Redundancy is all about
duplication of data on different files. It merely wastes the media storage on a different system.

3DATABASE DESIGN AND IMPLEMENTATION
Microsoft Office Integration: Database is created in the Microsoft access that helps in
much easy access to data within Microsoft office based application. Data is completely stored in
the access database and also it can be exported to the MS Excel.
Understanding the Structure: MS access will merely allow the user to have a view of all
the available database in a graphical way (Pławinska et al., 2018). All the relationship in between
the windows is merely used for highlight relationship among the database.
Sample database: MS access is inclusive of some of the sample database application for
a different user. This kind of application will help the user to have an idea with respect to real-
world tables and queries. User can easily have an understanding with respect to the
interconnected objects in the database.

4DATABASE DESIGN AND IMPLEMENTATION
ER-Diagram:
Figure 1: Entity Relationship Diagram of Butler Financing
(Source: Created by Author)
Normalization:
The initial raw database table is in first normal form because there is no multivalued
attribute. All the attributes in the table is atomic.
Borro
wer No
Last
name
First
name
Curren
t
addres
s
Reques
ted
mortga
ge
amoun
t
Lender
No
Lender
name
Lender
office
addres
s
Proper
ty
Apprai
ser No
Proper
ty
Apprai
ser
Name

5DATABASE DESIGN AND IMPLEMENTATION
450 Adams Jennifer 4 Do
Rd
$245,00
0
13 Excel
Mortga
ge
6 Shore
Dr
8 Ad
Apprais
ers
451 Adams
on
David 5 So St $124,00
0
13 Excel
Mortga
ge
6 Shore
Dr
9 So
Apprais
ers
452 Bronso
n
Paul 3 To Dr $345,00
0
14 CCY 2 Buck
Rd
10 Jay
Apprais
ers
453 Brown Mariett
a
3 Go St $55,000 15 Advant
age
Lenders
3 Lake
Dr
10 Jay
Apprais
ers
454 Charles Kennet
h
7 Do
Rd
$25,000 16 Capital
Savings
8 Coral
Rd
8 Ad
Apprais
ers
455 Coulter Tracey 13 So
St
$216,35
0
13 Excel
Mortga
ge
6 Shore
Dr
8 Ad
Apprais
ers
456 Foster Harold 8 To Dr $115,00
0
12 Nationa
l
Mortga
ge
7 Ten
St
9 So
Apprais
ers
457 Frank Vernon 14 Go
St
$90,000 12 Nationa
l
Mortga
ge
7 Ten
St
10 Jay
Apprais
ers
458 Holmes Heather 9 Do
Rd
$450,00
0
16 Capital
Savings
8 Coral
Rd
10 Jay
Apprais
ers
459 Johanso
n
Sandy 11 So
St
$70,000 15 Advant
age
Lenders
3 Lake
Dr
9 So
Apprais
ers
460 Johnso
n
James 12 To
Dr
$15,000 12 Nationa
l
Mortga
ge
7 Ten
St
10 Jay
Apprais
ers
461 Jones Holly 5 Go St $65,000 15 Advant
age
Lender
3 Lake
Dr
9 So
Apprais
ers

6DATABASE DESIGN AND IMPLEMENTATION
To be in the second normal form, the database must have no partial dependency. The
partial dependency occurs when an attribute in database table is dependent on attribute other than
primary key. It is because, non-key attribute dependent on another non-key attribute can cause
database anomalies. The dependency has been shown in the following section.
Borrower No  Last name, First name, Current address, Requested mortgage amount
Lender No  Lender name, Lender office address
Property Appraiser No  Property Appraiser Name
The following shows the tables in second normal form.
Borrower
No
Last name First name Current
address
Requested
mortgage
amount
Lender No
450 Adams Jennifer 4 Do Rd $245,000 13
451 Adamson David 5 So St $124,000 13
452 Bronson Paul 3 To Dr $345,000 14
453 Brown Marietta 3 Go St $55,000 15
454 Charles Kenneth 7 Do Rd $25,000 16
455 Coulter Tracey 13 So St $216,350 13
456 Foster Harold 8 To Dr $115,000 12
457 Frank Vernon 14 Go St $90,000 12
458 Holmes Heather 9 Do Rd $450,000 16
459 Johanson Sandy 11 So St $70,000 15
460 Johnson James 12 To Dr $15,000 12
461 Jones Holly 5 Go St $65,000 15

7DATABASE DESIGN AND IMPLEMENTATION
Lender No Lender name Lender office
address
Property
Appraiser No
Property
Appraiser
Name
13 Excel Mortgage 6 Shore Dr 8 Ad Appraisers
13 Excel Mortgage 6 Shore Dr 9 So Appraisers
14 CCY 2 Buck Rd 10 Jay Appraisers
15 Advantage
Lenders
3 Lake Dr 10 Jay Appraisers
16 Capital Savings 8 Coral Rd 8 Ad Appraisers
13 Excel Mortgage 6 Shore Dr 8 Ad Appraisers
12 National
Mortgage
7 Ten St 9 So Appraisers
12 National
Mortgage
7 Ten St 10 Jay Appraisers
16 Capital Savings 8 Coral Rd 10 Jay Appraisers
15 Advantage
Lenders
3 Lake Dr 9 So Appraisers
12 National
Mortgage
7 Ten St 10 Jay Appraisers
15 Advantage
Lender
3 Lake Dr 9 So Appraisers
It is essential to remove any transitive dependency from the database to covert the
database into 3NF or third normal form. There is one transitive dependency in each table in the
database. Two new tables is introduced after removing the transitive dependency. The final
database consist of the following 4 tables.
Borrower No Last name First name Current address
450 Adams Jennifer 4 Do Rd
451 Adamson David 5 So St
452 Bronson Paul 3 To Dr

8DATABASE DESIGN AND IMPLEMENTATION
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 No Lender name Lender office address
13 Excel Mortgage 6 Shore Dr
13 Excel Mortgage 6 Shore Dr
14 CCY 2 Buck Rd
15 Advantage Lenders 3 Lake Dr
16 Capital Savings 8 Coral Rd
13 Excel Mortgage 6 Shore Dr
12 National Mortgage 7 Ten St
12 National Mortgage 7 Ten St
16 Capital Savings 8 Coral Rd
15 Advantage Lenders 3 Lake Dr
12 National Mortgage 7 Ten St
15 Advantage Lender 3 Lake Dr
Property Appraiser No Property Appraiser Name
8 Ad Appraisers
9 So Appraisers
10 Jay Appraisers

9DATABASE DESIGN AND IMPLEMENTATION
10 Jay Appraisers
8 Ad Appraisers
8 Ad Appraisers
9 So Appraisers
10 Jay Appraisers
10 Jay Appraisers
9 So Appraisers
10 Jay Appraisers
9 So Appraisers
MortageRequest Requested
mortgage
amount
Borrower No Lender No Property
Appraiser No
1 $245,000 450 13 8
2 $124,000 451 13 9
3 $345,000 452 14 10
4 $55,000 453 15 10
5 $25,000 454 16 8
6 $216,350 455 13 8
7 $115,000 456 12 9
8 $90,000 457 12 10
9 $450,000 458 16 10
10 $70,000 459 15 9
11 $15,000 460 12 10
12 $65,000 461 15 9

10DATABASE DESIGN AND IMPLEMENTATION
Query:
Query1
borrowerNo FullName lenderName appraiserName
450 Jennifer Adams Excel Mortgage Ad Appraisers
451 David Adamson Excel Mortgage So Appraisers
455 Tracey Coulter Excel Mortgage Ad Appraisers
Forms and Reports:
Borrower Form:
Lender Form:

11DATABASE DESIGN AND IMPLEMENTATION
Property Appraiser Form:
Mortgage Request Form:
Excel Mortgage Report:

12DATABASE DESIGN AND IMPLEMENTATION
Conclusion:
From the above study, it is concluded that the MS Access database can become most
significant tool for data management. The database can store data for a very long time.
Moreover, the MS Access allows creating reports very easily with query design tool. Each data
in the database can be extremely valuable to business. The organization will not worry about data
redundancy as MS access will organize all the data in a relational database. The database allows
entering data using forms which can be customized to fulfil management requirement. Only
issues with the MS Access database is that every copy of access cannot be synchronized into one
core server.

13DATABASE DESIGN AND IMPLEMENTATION
References:
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Eessaar, E. (2016). The database normalization theory and the theory of normalized systems:
finding a common ground. Baltic Journal of Modern Computing, 4(1), 5.
Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems. Pearson.
Hingorani, K., Gittens, D., & Edwards, N. (2017). REINFORCING DATABASE CONCEPTS
BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND
NORMALIZATION TOGETHER FOR DESIGNING ROBUST DATABASES. Issues
in Information Systems, 18(1), 148-155.
Kralj, D., & Hren, N. (2018, January). Framework project of database development: Record
keeping of protective equipment. In 7. Međunarodni stručno-znanstveni skup" Zaštita na
radu i zaštita zdravlja"/7th International Professional and Scientific Conference"
Occupational safety and health".
Lewis, K. A., Tzilivakis, J., Warner, D. J., & Green, A. (2016). An international database for
pesticide risk assessments and management. Human and Ecological Risk Assessment: An
International Journal, 22(4), 1050-1064.

14DATABASE DESIGN AND IMPLEMENTATION
Li, J., Li, C., Luo, Y., Zhao, Y., & Mao, X. (2015). Entity Relationship Modeling Approach
Based on Micro-Blog Tag. International Journal of Multimedia and Ubiquitous
Engineering, 10(7), 157-170.
Noh, H. N., Bahari, M., & Zakaria, N. H. (2018). A Conceptual Model of Database
Normalization Courseware Using Gamification Approach. In PROCEEDINGS OF NEW
ACADEMIA LEARNING INNOVATION (NALI) SYMPOSIUM 2018 (p. 23).
Pławinska-Czarnak, J., Ochnio, J. Z., Zarzynska, J., Bogdan, J., Kaba, J., Majewska, A., &
Bagnicka, E. (2018). Design and implementation of a database enhancing the collection,
management and analysis of data in an animal sciences project. Animal Science Papers
and Reports, 36(2), 159-170.
Roy-Hubara, N., Rokach, L., Shapira, B., & Shoval, P. (2017). Modeling graph database
schema. IT Professional, 19(6), 34-43.
Shi, L., Zhao, L., Wong, A., Wang, D., & Mok, V. (2015). Mapping the relationship of
contributing factors for preclinical Alzheimer’s disease. Scientific reports, 5, 11259.