MGMT6001 Butler Financing Company Database | Assignment Help
Introduction
Database is an organized collection of logically related data, in other word it’s a collection of information which is organized for easily access able, managed and updated. So, it is form of organized data into rows, columns and tables, which is listed to make it simple to find relevant information. When data are organized it gets updated, expanded and deleted as new information is added. To control all the data database management system (DBMS) is used which is a group of programs that control the database to supply a connection between the database and its users and other application programs. Its objective is to provide a convenient and effective method of defining, storing, retrieving, and control the data contained in the database. MSAccess, LibreOffice, MySQL, Oracle are the different types of DBMS.
The Butler Financing Company is a company that runs business of mortgage brokerage. The main objective of the company is to keep record of all borrowers, lenders and the property appraiser involved in financing process. For the last 5 years it is providing services to the customer with satisfactory results and profits. The company is using spreadsheet to keep all the records of its borrowers, lenders and property appraiser in table list form. Since they are doing it manually there is high chances of data duplication, higher data redundancy, user error, difficult for users to process, and take too long to use. Even the record stored need to update manually by employee. So, to reduce all these circumstances the company need to upgrade system to database.
Justification
The database is the set of data collection which are stored and retrieved using electronic means. Database keep records in very organized way which helps to find out the right one in quickly manner. So, the database management system supports for the storage and manipulation of the data.
Issues
When company becomes bigger more customer might come for the services. There will higher number of borrower and lender. To handle such big number of data in manual process is time consuming and will not work properly. For such cases spreadsheet is not good choice for the large data. There will be conflict in finding exact data in spreadsheet but while using database it will be easy to find the find the data. Thus, it is more reliable to manage large data in database. When there is large number of data it is not accurate in spreadsheet. That mean it takes more time update the data in spreadsheet but It is easy to update large number of data in database with different users. While there are chances of data duplication in spreadsheet. The users in the spreadsheet cannot work together with same data at the same time because of this there is always higher chance of data duplication and deleting of data. Security is one of the major issues in spreadsheet. Anyone can get easy accessing, deleting and updating the saved data in spreadsheet. But in database there is security for the data, where user can set privileges for accessing, deleting and updating the saved data in database. To avoid such kinds of scenario from the company the management must replace their data entry system to database management system.
Database Design
E-R Diagram
An Entity Relationship (ER) diagram is a type of diagram that exemplify how “entities” relate to each other within a system. ER diagrams are most frequently used to design or debug relational databases in the fields of business information systems, education, research and in many other fields. The entity can be people, objects or concepts which directly relates to each other.
The Butler financing Company who runs the mortgage brokerage business whose job is to provide services to their borrower and lenders. In the given business scenario of the company the main entity of system can be considered as borrower, lender and property appraiser. Within this entity its attributes are also identified. For the borrower entity, borrower number, first name, last name, current address and requested mortgage amount can be examined as its attributes. Here the borrower number has unique number that clearly identifies the borrower first name, last name, current and the requested mortgage amount when we search for the borrower number. It applies to lender and the property appraiser also where the they have also the unique lender number and property appraiser that will show us the list of lender with its name office address and which property appraiser is chosen by the borrower. In the lender entity it has lender name, lender office address which are its attributes. As well as property appraiser number and name are the attributes. The ER diagram shows the relationship between the borrower and lender is many to many, that means one borrower can borrow money from one or more than one lender same as one lender can lend money to one or more than one borrower. The same relationship is applied to lender and property appraiser also. The following diagram shows the relationship between borrower, lender and property appraiser.
Figure 1: ER Diagram
Microsoft Access Files
Tables
- There are 3 tables created for the borrower as borrower list, lender as lender list and property appraiser as property appraiser list. We can enter the data of borrower, lender or property appraiser in the respective table.
Figure 2: Borrower List
Figure 3: Lender List
Figure 4: Property appraiser list
Queries
Query 1
SELECT Borrower_List.[Borrower No], Borrower_List.[First Name], Borrower_List.[Current Address], Borrower_List.[Requested Mortgage Amount], Borrower_List.[Lender No], Borrower_List.[Property Appraiser No]
FROM Borrower_List;
Figure 5: Borrower list query
The given SQL query shows the total number of borrower with their lender number and property appraiser number.
Query 2
The SQL of given query shows the total amount of mortgage from every lender the borrower requested.
Figure 6: Lender name with total mortgage amount
SELECT Lender_List.[Lender No], Lender_List.[Lender Name], Property_Appraiser_List.[Property Appraiser No], Property_Appraiser_List.[Property Appraiser Name]
FROM Property_Appraiser_List INNER JOIN (Lender_List INNER JOIN Borrower_List ON Lender_List.[Lender No] = Borrower_List.[Lender No]) ON Property_Appraiser_List.[Property Appraiser No] = Borrower_List.[Property Appraiser No];
Form
The following form shows the unique borrower number with the first and last name of the borrower with the current address he/she is residing. As well as, it shows the requested mortgage amount with the lender no and the property appraiser no. we can add data in the form or we can update in table as well. Where ever we enter the data it will make changes in both.
Figure 8: Borrower form
Report
This is the report of borrower list where we can see the total requested mortgage amount with the lender and the property appraiser who has evaluated the property for the borrower. Similarly, we have the lender report and property appraiser report.
Figure 9: Borrower list report
Figure 10: Lender list report
