DB

icanusesomehelp

feedback1_gp1.doc

Home >Information Systems homework help >DB

Spectrum Loan Servicing

Business Case

TABLE OF CONTENTS

1 Cover page 1

1.1 Title 1

1.2 Course Name 1

1.3 Name of Authors 1

1.4 Assignment Title 1

1.5 Date 1

1.6 Page Numbers 1

2 Table of contents…………………………………………………………………………….2

3 Section 1 - Business Case Abstract 3

4 Section 2 - Business Case Description 4

5 Section 3 - Business Rules 8

6 Section 4 - Logical ERD (Entity Relationship Diagram) 9

7 Section 5 - Data Dictionary 10

8 Section 6 - DDL scripts 13

9 Section 7 - Case Examples 15

10 Section 8 - DML Scripts 16

11 Section 9 - Works Cited / Bibliography 24

1. bUSINESS cASE Abstract

This project is about Spectrum loan servicing Inc. Basically, A company deals with the loans portfolio and service them on behalf of different Mortgage Originator who lends a required amount to the borrower. Moreover, borrower mortgage their properties as a security to the mortgage originator.

In this project, we have created a loan servicing database which shows the relationship between a borrower, Loans, Mortgage Originator and Mortgage Servicer. The whole project is divided in different steps to explain the loan servicing database in detailed manner. The steps includes Business Description, Business rules, ERD – Entity Relationship Diagram, DDL – Data definition language, DML – Data manipulation language, Data dictionary, case examples, screenshots of the tables created & bibliography with the references.

The purpose behind this project is to understanding the database concepts and experiencing the same with the live example by playing with it in depth. I, MahmadSamir Zaveri & Asfaw Tessema would like to thank James Adams for allowing us to experience this amazing opportunity and learn many aspects out of it.

2 bUSINESS cASE DESCRIPTION

Spectrum loan servicing Inc. (SLSI) is a loan servicing company which deals with different types of loans in United States of America. There are different types of loans which are handled by SLSI such as commercial loans, residential loans, REO loans and Interest Only loans etc.

Basically, SLSI plays a role of middleman between the loan originator and the borrower. Moreover, SLSI charges a service fee from loan originator to service the loan. These loans can be a dead loan where borrowers are not paying anything or filled a bankruptcy. There are different steps followed by SLSI for different situations.

Below are some of the situation where SLSI plays their role;

· Bankruptcy: In this scenario, SLSI given a role to contact the borrower to convert the status from bankruptcy to modification with different programs such as HAMP (HAMP – Home Affordable Modification Programme)& Non-HAMP (Private Modification programme offered by company itself) loans.

· Foreclosure: In this scenario, SLSI gives an opportunity to pay the unpaid principle balance with the certain period of time. If not accomplished, SLSI will go ahead and foreclose the property of the borrower and clear the debt.

· Modification: In this scenario, a borrower files a modification and request the loan originator or a servicer to convert the loans criteria to make it affordable. There are different types of modification which allows a borrower to change the status of the loan from regular to different status as mentioned below;

· HAMP Loan: HAMP loan is basically introduced by President of United States of America ‘MR. BARRACK H. OBAMA’ to help the borrower with affordable interest rates and long term. There are different criteria to get eligible for fall under this kind of programme. Moreover, if servicer and customer fulfils the criteria to get eligible in this programme, both servicer and borrower gets incentives for the same.

· NON-HAMP: Non-HAMP is an option given by a servicer once a request received from a borrower. In this programme, borrower is asked to submit necessary document such as paystubs, taxation documents, etc. to change the interest rates and the term of the loan to make it easier for the borrower.

· Balloon Loan: This is again a type of loan where borrower will pay only interest for the period of time and the rest of amount will be paid in full together to the servicer.

For Example: Mr. ‘A’ is a borrower who is entitle with the Balloon loan. Hence, he will pay only Principle + Interest for the loan for the first 5 years and after that he will pay the whole remaining payment together of 20 years to the servicer at once.

· Interest-Only Loan: This is again a type of programme where a borrower pays only interest for a period of time and after that he will start paying principle and Interest.

For Example: Mr. ‘A’ is a borrower who is entitle with the IO loan. Hence, he will pay only interest for the loan for the first 10 years and after that he will pay the rest of 20 years with Principle + Interest to the servicer.

RELATIONSHIP BETWEEN LOAN ORIGINATORS, LOAN SERVICER & BORROWER:

Below is the diagram which explain how the loan originator transfer the service rights of the particular loan to the servicer. Below are the basic steps to explain the above diagram;

Mortgage Servicer : Mortgage servicer applies for the bunch of loans to service for the Originator such as Fannie Mae, Ginnie Mae and Freddie Mae (Investor of the loan) who does auctions for the loans to service. Basically, the cheaper servicer rates with better service wins the auction. Once auction won by a servicer gets the right transferred to service the loans. Borrower gets a notification regarding the transfer of this service.

Mortgage Originator : Mortgage originator are the main player of this process as they are the lender of the amount owed by a borrower by mortgaging the property. Basically, Mortgage originator lends a loans to the borrower and held the servicer rights to the servicer to service the loans in terms of collecting money with the interest from the borrower.

Borrower: Borrower is again one of the important player who relates in this process as an important aspect. Basically, borrower request a lender to lend the money to fulfil his requirement by mortgaging his property as a security for the amount given by a originator. Borrower deals with servicer to repay the payments on time as per the rules and regulation designed by originator.

All these three major aspects are related to each other and have to play their own role to complete the requirement of each other.

Mission of Spectrum Loan Servicing Inc.

Provide the qualitative & effective service to each and every customer with the same level.

Vision of Spectrum Loan Servicing Inc.

Expansion of services by satisfying large number of customer, hiring professional personnel, use of latest and advanced technology along with the better environment.

Commitments of Spectrum Loan Servicing Inc.

Commitment towards our Customers: SLSI is committed to provide the best service 24*7 to the customer when and where required along with the hassle free procedures.

Commitment towards our Employees: By treating them well and giving a right resource at a right place. Moreover, by providing better facilities along with the high wages compared to market rates.

Commitment towards our Clients: By providing more & better services than the demanded services by our client.

3 Business Rules

· A borrower will have one to many loans from different MortgageOriginator

· A borrower will have one to many loans from different MortgageServicer

· A borrower will have one loan from the one MortgageOriginator

· A servicer can have one to many loans to service

· A Mortgage Originator can have one to many loans to Originate

· A loan can be serviced by one to many servicer

· A Mortgage Originator can have one to many servicer to transfer the service rights

· A Mortgage Servicer can have one to many Originator to get loans transferred from to service the loans

· A borrower can be serviced by one to many servicer

4 LOGICAL ERD (ENTITY RELATIONSHIP DIAGRAM)

5 DATA DICTIONARY

image3.emf

Entity nameEntity descriptioncolumn namecolumn descriptionData type

lengt

primary

keynullableunique

Borrower

borrows money from

mortgage originator

B_id

for the unique identification of

the borrowerINTEGERTRUEFALSETRUE

Namename of the borrowerpborrNameName of the borrowerVARCHAR20FALSEFALSEFALSE

CoborrNameName of the co borrowerVARCHAR20FALSEFALSEFALSE

prim_Phoneprimary phone numberINTEGERFALSEFALSEFALSE

Alt_PhoneAlternative Phone numberINTEGERFALSEFALSEFALSE

P_EmailPrimary emailVARCHAR50FALSEFALSEFALSE

Co_EmailCo borrower emailVARCHAR50FALSEFALSEFALSE

P_SSNBorrower SSNINTEGRFALSEFALSEFALSE

CO_SSNCo Borrower SSNINTEGERFALSEFALSEFALSE

Mail_AddrAddress of the borrowerVARCHAR20FALSEFALSEFALSE

Prop_AddrAddress of the propertyVARCHAR20FALSEFALSEFALSE

image4.emf

Entity nameEntity descriptioncolumn namecolumn descriptionData typelengthprimary keynullableunique

mortgage

originator

lender of the moneyMo_id

for the unique identification of

the mortgage originatorINTEGERTRUEFALSETRUE

Name

Name of the mortgage

originatorVARCHAR20FALSEFALSEFALSE

Address

Address of the mortgage

originatorVARCHAR20FALSEFALSEFALSE

Phoneprimary phone numberINTEGERFALSEFALSEFALSE

citycity of the mortgage originatorVARCHAR10FALSEFALSEFALSE

EmailPrimary emailVARCHAR50FALSEFALSEFALSE

image5.emf

Entity nameEntity descriptioncolumn namecolumn descriptionData typelengthprimary keynullableunique

mortgage

servicer

collects money from borrowerMS_id

for the unique identification of

the mortgage servicerINTEGERTRUEFALSETRUE

NameName of the mortgage servicerVARCHAR20FALSEFALSEFALSE

Address

Address of the mortgage

servicerVARCHAR20FALSEFALSEFALSE

Phoneprimary phone numberINTEGERFALSEFALSEFALSE

citycity of the mortgage servicerVARCHAR10FALSEFALSEFALSE

EmailPrimary emailVARCHAR50FALSEFALSEFALSE

MO_Id

Foreign keyINTEGERFALSETRUEFALSE

Entity nameEntity descriptioncolumn namecolumn descriptionData typelengthprimary keynullableunique

loansType of loansLoanId

for the unique identification of

the loanINTEGERTRUEFALSETRUE

Int_rateThe rate of interestINTEGERFALSEFALSEFALSE

Orig_princ_bal

Original principal amount/

balanceINTEGERFALSEFALSEFALSE

Unp_pric_balUnpaid principal amountINTEGERFALSEFALSEFALSE

Loan_typeLoan typeVARCHAR10FALSEFALSEFALSE

TermTerm of the loanINTEGER50FALSEFALSEFALSE

6 DDL SCRIPTS

CREATE TABLE TblBorrower

(

B_Id int NOT NULL,

PBorrName VARCHAR(20) NOT NULL,

CoBorrName VARCHAR(20),

Prim_Phone Int Not Null,

Alt_Phone Int Not Null,

P_Email_Add VARCHAR(50),

Co_Email_Add VARCHAR(50),

P_SSN Int NOT NULL,

Co_SSN Int,

Mail_Addr VARCHAR(20),

Prop_Addr VARCHAR(20) NOT NULL,

MortgageOriginator_FK int NOT NULL,

MortgageService_FK int NOT NULL

);

ALTER TABLE TblBorrower ADD CONSTRAINT Borrower_PK PRIMARY KEY (B_Id);

CREATE TABLE TblMortgageOriginator

(

MO_Id int NOT NULL,

Name VARCHAR(20) NOT NULL,

Address VARCHAR(20),

Phone Int,

City VARCHAR(10),

Email VARCHAR(50)

);

ALTER TABLE TblMortgageOriginator ADD CONSTRAINT MortgageOriginator_PK PRIMARY KEY (MO_Id);

CREATE TABLE TblMortgageServicer

(

MS_Id int NOT NULL,

Name VARCHAR(20) NOT NULL,

Address VARCHAR(20),

Phone Int,

City VARCHAR(10),

Email VARCHAR(50),

MortgageOriginator_FK int NOT NULL

);

ALTER TABLE TblMortgageServicer ADD CONSTRAINT MortgageServicer_PK PRIMARY KEY (MS_Id);

CREATE TABLE TblLoans

(

LoanId int NOT NULL,

Int_Rate Int NOT NULL,

Org_Princ_Bal Int NOT NULL,

Unp_Princ_Bal Int NOT NULL,

Loan_Type VARCHAR(10) NOT NULL,

TERM Int NOT NULL,

MortgageOriginator_FK int NOT NULL,

MortgageService_FK int NOT NULL

);

ALTER TABLE TblBorrower ADD CONSTRAINT Borrower_MortgageOriginator_FK FOREIGN KEY (TblMortgageOriginator_MO_Id ) REFERENCES TblMortgageOriginator(MO_Id) ON DELETE CASCADE ;

ALTER TABLE TblBorrower ADD CONSTRAINTBorrower_MortgageServicer_FK FOREIGN KEY (TblMortgageServicer_MS_Id ) REFERENCES TblMortgageServicer (MS_Id) ON DELETE CASCADE ;

ALTER TABLE tblLoans ADD CONSTRAINT Loan_MortgageOriginator_FK FOREIGN KEY (MortgageOriginator_FK ) REFERENCES TblMortgageOriginator(MO_Id) ON

DELETE CASCADE ;

ALTER TABLE tblLoans ADD CONSTRAINT Loan_MORTGAGESERVICE_FK FOREIGN KEY (MORTGAGESERVICE_FK ) REFERENCES TblMortgageServicer (MS_Id) ON DELETE CASCADE ;

ALTER TABLE TblMortgageServices ADD CONSTRAINT MortgageServicer_MortgageOriginator_FK FOREIGN KEY (MortgageOriginator_FK ) REFERENCES TblMortgageOriginator(MO_Id) ON DELETE CASCADE ;

7 CASE EXAMPLES – SAMPLE DATA SCRIPTS

· 'JESSICA GEORGE' has a loan with the 'AMERICAN FINANCE’ which is located at '6000 OLD NAVY DR’ in New Jersey.

· 'JESSICA GEORGE' has a Co-Borrower who has entitled with the same loan with 'AMERICAN FINANCE’ which is located at '6000 OLD NAVY DR’ in New Jersey.

· 'EVELYN MORTGAGE’ is the MortgageOriginator of different loans and have transferred rights to different servicer.

· 'LUCAS SMITH' & 'JENNIFER EDWARDS are borrower and co-borrower having a loan from MortgageServicer with property mortgaged as '250 SHEFFIELD in Arizona.

· 'GREAT LAKES FINANCE' is a Mortgage servicer servicing the loans portfolio in Nevada with different MortgageOriginator.

8 DML SCRIPTS

The following SQL will create new records for Tbl Borrower table;

INSERT INTO TblBorrower

(PBORRNAME,COBORRNAME,PRIM_PHONE,ALT_PHONE,P_EMAIL_ADD,CO_EMAIL_ADD,P_SSN,CO_SSN,MAIL_ADDR,PROP_ADDR)

VALUES ('KATHY SOLOMON', 'ROBERT LEE', 1232344567,3214560000, '[email protected]', '[email protected]',0000006969,0000007878', '5555 ABC DR VA', '5900 ABC Dr VA');

INSERT INTO TblBorrower

(PBORRNAME,COBORRNAME,PRIM_PHONE,ALT_PHONE,P_EMAIL_ADD,CO_EMAIL_ADD,P_SSN,CO_SSN,MAIL_ADDR,PROP_ADDR)

VALUES ('MARTHA ANDREW','SUSAN MATHEW',1234567878,7008007878,'[email protected]','[email protected]',1113334545,2222224546,'4775 W VA', '4795 W VA’);

INSERT INTO TblBorrower

(PBORRNAME,COBORRNAME,PRIM_PHONE,ALT_PHONE,P_EMAIL_ADD,CO_EMAIL_ADD,P_SSN,CO_SSN,MAIL_ADDR,PROP_ADDR)

VALUES ('PETER JACOB', 'MARTIN GARY', 5554444545,6665554545, '[email protected]', '[email protected]', 8889998989, 7778887878,’5500 CHAIN BRDG FL, '5555 CHAIN BRDG FL’);

INSERT INTO TblBorrower

(PBORRNAME,COBORRNAME,PRIM_PHONE,ALT_PHONE,P_EMAIL_ADD,CO_EMAIL_ADD,P_SSN,CO_SSN,MAIL_ADDR,PROP_ADDR)

VALUES ('LUCAS SMITH','JENNIFER EDWARDS',1111111212,1223331212,'[email protected]','[email protected]',2222222121,5555557800,'250 SHEFFIELD AZ', '250 SHEFFIELD AZ’);

INSERT INTO TblBorrower

(PBORRNAME,COBORRNAME,PRIM_PHONE,ALT_PHONE,P_EMAIL_ADD,CO_EMAIL_ADD,P_SSN,CO_SSN,MAIL_ADDR,PROP_ADDR)

VALUES ('JESSICA GEORGE','ANTHONY STEVENS',4448887878,6665555858,'[email protected]','[email protected]',7777779898,9998887897, '225 WOODSDIE LANE LORTON VA','235 WOODSIDE LANE LORTON VA');

INSERT INTO TblBorrower

(PBORRNAME,COBORRNAME,PRIM_PHONE,ALT_PHONE,P_EMAIL_ADD,CO_EMAIL_ADD,P_SSN,CO_SSN,MAIL_ADDR,PROP_ADDR)

VALUES ('MATHEW LEE', 'ALBERT SIMPSON','7777778888',9999997777', '[email protected]', '[email protected]', 1115552222, 1118889898, '7800 OLD KEENE MILL RD BURKE VA', '9800 SHEFFIELD GREEN LORTON VA');

The following SQL will create new records for Tbl Mortgage Originator table