I wrote my term project But I want your expert to correct it and implement some input. i want him to insert a trigger where I had specified in my term project. This is my term project as following:

 

 

 

 

 

 

 

ADVANCED DATABASE MANAGEMENT

______________________________________

2015 Summer 2

 

 

 

 

Term Project: How to Prevent a Dual Loan in Real Time

 

 

 

By Muana Charles Mukuamu

 

 

AUGUST 2015

 

Professor:

 

Facilitator:

In this term project, the focus will be on real problem presented to us by a manager of a loan company in Charleston, South Carolina. Because of confidentiality, the name of this company will not be revealed during this term project. This loan company has two branches in the same city of Charleston in South Carolina. Each branch or location has its own database and each database does not communicate to the other database. In this term project, the two branches are named as branch A and branch B.

According to this company loan rules, the customer can borrow the money to different locations. But she or he can borrow the money in any location when he or she did not have an active loan. This means that in order to borrow the money, normally, the customer must pay off the current t loan or never borrow the money in the past. Unfortunately as this company presented to us, some customers tried to trick their system by going to one location borrow the money, without to pay off the present loan, they are going also in second location to borrow the money. The fact that these two locations are not connected, it is very hard to know in real time if this customer has a current loan which is not paid off. This situation hurt this company dramatically in term of money and loss of customers.

During this term project, it will be proposed three solutions how this company can coordinate the database system between these two branches in order to detect this malicious practice; Therefore to be able to prevent this dual loan in real time. The critical process is to be able to prevent this hateful practice before the second loan had been authorized when a customer did not paid off the current loan. This term project will provide a solution for a present time and also project an answer for this business loan company as they are planning to grow in a near future. Finally, it will propose the solution in case the loan company grows exponentially.

The first recommendation in order to prevent an unauthorized duplication loan is to implement a database-link system.

Database Link

A database link will provide a connection between the branch A and branch B. The loan officer will be able to access the authorized tables in the other database location. With the database link system, the two locations databases will be able to communicate. In that case, a database link will offer access to each location to see the data of other location. But it will be implement a system with a database-link that gives to the other site loan officer to see specific tables without to have a privilege to update, delete, insert or create objects of the other database server but to have only the read only access on these objects or tables. It will be possible to implement a trigger which will be able to check these tables and be able to know any time there is a duplicate data in the customer credential table and customer loan status tables and returns a result that the loan must be denied.

(Please can you provide a kind of trigger example this can be implement to track duplicate data)

The access will be giving to each location to see the records on the customer credential table and the customer loan status table. Because the customer credential table and status table have a customer_id , social security number, has a primary key, it will be able to track the system to see if the customer’s address, the customer’s name, the customer’s driver license already exist in other location database. This database links will help to access these objects on another database. The good think is that database A and database B, both runs in oracle systems. It means that there is not a need to use Oracle Heterogeneous services.

There are some prerequisites to create a database link. To create a private database link, the user must have a create database link system privilege. Also to create a public database link, you must have Create public database link system privilege. When a database link is created, it can be used to refer to tables on the other database by appending @dblink to the table. To query a table on the other database, this can be done with a select statement to track if it would be any duplication of the data with a new insertion of new data. The syntax to create a database link is as following:

Create database link

Connect to to identified by

Using ‘’;

Steps to create a database link to access the customer credential table and the customer loan status table:

1. Remote database is database A

2. Local database is database B

3. Login to the remote database as manager

4. Identified a username and password it will be used for the database link

5. Do a tnsping to determine the correct string of the remote database

Tnsping database A

 

 

The more interesting is after the loan officer will be able to find out the customer credential exists in the order location; it will be the time to check the customer status loan table to see if the customer has any active loan. If it is the case that the customer has an active loan already, this officer will be able to advise the customer to pay off the first current loan before to open a new loan.

Therefore the customer loan request will be denied. This is the way this company can reinforced their rules without to give malicious customers a chance to break their loan system.

It is obvious that the database-link can be just a temporary alternative that company can adopt but in the meantime, it is recognized that database-link cannot be scalable if the company has planning to grow. This first recommendation is the cheapest one but not the scalable one in case the company will grow in the near future. If the company will add more locations, it will be complexed to coordination all these sites just using the database link. The database link can be as a temporary fixed if the company can afford in this particular time to adjust to the new and scalable database management system.

 

The second recommendation in order to prevent an unauthorized loan is to implement a distributed database management system.

Distributed Database Management System

As Connolly and Begg said “A distributed database is a logically interrelated collection of shared data (and a description of this data) physically distributed over a computer network.” The Distributed Database Management System (DDBMS) helps the user to access data not only at his own local database but be able also to access database to the remote database. In the distributed database management system, the concept is about the sharing and the management system of information.

When there is a need to share information, the distributed database management system is one of the best approaches to adopt. The data to share can be distributed in two ways in the distributed database management system. Data can be partitioned in horizontal partitioning where data are partitioned by rows of the table, or data can be partitioned in vertical partitioning where data are partitioned by attributes (columns) of the tables. In one side, these rows can be grouped together by using SQL Union operator, and in the other side, the attributes can put together by using the SQL joint. It is obvious that all technics involved in the distributed database management system will help this company to be able to track any malicious customer who has intention to break their loan system. In the following lines, it will be demonstration how some features involved in database management system can help to enforce this company policy concerning a dual loan.

Fragmentation

For this present case, the vertical partition will be an appropriate one if the target information is to know if the customer did not pay off a current loan. The approach is to be able to record information about the customer credential and compare that to the other site with the status of the loan the customer has in the other branch. With a distributed database management system, it is possible to have a replication of these two tables in each location. Therefore every time there is an update on these tables, with the distributed database management system, this update will be adjusted to the other location two tables also. In that case, each location can continue to function independently to the other one. Most of the banks use separate application for handling the loan records and for back office functions. Therefore separate partitions must support these applications. So, if any customer has taken loan from one branch and he/she is not paying it on time, second loan from any branch cannot be approved.

There is numerous perspective that let us make a correlation in the middle of unified and dispersed database management system. Database administration framework is any product that oversees and controls the capacity, association, security, recovery and indispensable of information in a particular database, though distributed database management system comprise of solitary database that is separated into numerous parts. Each one piece is incorporated on one or more machine and controlled by autonomous database (DBMS). So, by using these features of the distributed database management system, the customer can be easily identified.

Two Phase Commit(2PC)

A Two phase Commit protocol help to secure the integrity of data in a distributed database management system. The initial site in the transaction needs to have an approval of another site before to commit a transaction. With this mechanism in place the system will be able to check before to insert any data. This means, a system can be put in place to verify certain elements before to commit (accept a loan) or to abort a transaction (denied a loan). In practice it is obvious that each customer has unique social security number (SSN), an unique driver license, therefore, with the two phase commit in place, this distributed database management system, will be able to track certain elements key before to commit in order to accept the integration of new loan in any loan site. The advantage to have distributed database management system is the fact that before approve a loan, the loan officer will be able to run a query in the replicated two tables to know if the customer exists in the other location and be able to confirm through the loan status table if he/she has a loan that is not paid off yet.

If the database is centralized and separated to another branch, it will be difficult to track a malicious customer who wants to trick this loan company.

Replication

A replication is a tactics to duplicate data from one location to one or more location. With this technic in place, it is very beneficial to each location to have these target tables to their location. If these two tables can be replicated all the time in both sites, one location to another location, it is clear that to track the status of loan of each customer will be very easy. Therefore, this information will be available to both sites. This replication will help to improve the security and the accuracy of the information of customer information in the two branches. In the case of failure of network connection, these two branches can be able to continue to operate because with a replication, the information about customer loan status will be in their branches.

Continuous Operation

With distributed database management system in place, it will be no interruption of the business in order to add new sites. The company can be operational when site can be added or removed from the database management system. As this company is call to grow, it is wise to implement a system which can accept to receive more sites without to interrupt or change dramatically the database architecture.

Distribution Transparency

The distribution transparency will help loan officer to perceive their database as a single logical database. He will be able to check the record of customer status loan of both locations as it was only one branch. Because from the replicated tables, it is possible to track customer record as he was in both locations in the same time.

Network System

The network connection established between these branch A and branch B will help the security, the performance and the implementation of new business. The network connection with a implementation of a web browser to connect to the two database at anytime and anywhere. This accommodation will help to be able to bring more customers through advertisement through internet. This can increase the business size dramatically. Through tablets, loan officer can connect to the database and be able to work with customers. This can help to reduce a cost to buy many computers.

If the number of the location become exponentially increase the implementation of the replication tools become very necessary.

This situation gives to way to the third solution: Golden Gate.

Golden Gate

Golden Gate will be a third solution in case of dramatically exponential increase. Golden Gate is Oracle's strategic real-time data replication tools. Golden Gate with his features is fast and robust solution for replication of multiple transactions between different databases systems in the real time. The data integrity and guaranteed zero data loss make Golden Gate an attractive tools for many big companies. With Golden Gate, data is sent in "real time" with sub-second speed.

Most big banks and financial companies implement Golden Gate to their database systems in order to conduct operational and analytical transactions in real time.

Oracle Golden Gate has a capability to capture data, to filter, and distribute data in real time across homogeneous or heterogeneous locations. Golden gate deliver data with consistency and integrity by maintaining referential integrity between the source and the target database.

The big problem for the Oracle Golden Gate is the price to afford in order to maintain multiple locations. The license of Golden Gate is very expensive. It will be not a good decision for a small loan company to incorporate the Golden Gate replication tools.

In conclusion, it is obvious that the Distributed Database Management system is the best solution for this small loan company in order to prevent a dual loan in the real time. It is no doubt that the database link solution can be the reasonable and cheapest one. But as this company is calling to grow in the near future, it is wise to implement now a durable and scalable solution by adopting the distributed database management system.

The database management system can not only tackle this dual loan problem but also can impact the business of this company. The distributed database management system with a replication in place will help to guarantee the credibility of the loan status of each customer. The move to the network system and the advisement through Internet can increase the potential of this small company. The convenience of the payment through internet and the connectivity of the two branches will solve accessibility and availability issues.

This incorporation of the distributed database management system will help malicious customers not to be exposed to temptation to contract extra loan when he/she did not pay off the current one. Although Golden Gate is a fast and robust solution for replication, but Golden Gate is not a good solution for this small company to afford this expensive license for each database.

The Distributed database Management System remains the best option for this small loan company. It is scalable, secure and affordable. This accommodation system can impact the future of this small company by implementing excellent way to do business in this modern society. It is a serious mistake to have a loan company with two or more branches which are not connected. In business practice, this way is not acceptable. This company can go off business if they do not resolve this horrible situation on time.

References:

Database Systems, A Practical Approach to Design, Implementation, and Management, SIXTH edition by Thomas Connoly & Carolyn Begg.

http://www.inf.unibz.it/dis/teaching/DDB07/ln/ddb01.pdf

http://www.webopedia.com/TERM/D/distributed_database.html

ozu, MT. & Valduriez, P.(2011). Principles of distributed database systems.

Sringer Science & Business Media.

Silberschatz, A.Stonebraker, M. & Ullman, J.D.(1990). Database Systems:

Achievements and opportunities. ACM Sigmod Record, 19(4),6-22