Customer relationship management for banking system

California State University, San Bernardino
CSUSB ScholarWorks

Theses Digitization Project

John M. Pfau Library

2004
Customer relationship management for banking system
Pingyu Hou

Follow this and additional works at: https://scholarworks.lib.csusb.edu/etd-project Part of the Databases and Information Systems Commons, and the E-Commerce Commons
Recommended Citation Hou, Pingyu, "Customer relationship management for banking system" (2004). Theses Digitization Project. 2635. https://scholarworks.lib.csusb.edu/etd-project/2635
This Project is brought to you for free and open access by the John M. Pfau Library at CSUSB ScholarWorks. It has been accepted for inclusion in Theses Digitization Project by an authorized administrator of CSUSB ScholarWorks. For more information, please contact [email protected]

CUSTOMER RELATIONSHIP MANAGEMENT FOR BANKING SYSTEM
A Project Presented to the
Faculty of California State University,
San Bernardino
In Partial Fulfillment of the Requirements for the Degree
Master of-Science' in
Computer:Science
by Ping-Yu Hou
June 2004

CUSTOMER RELATIONSHIP MANAGEMENT FOR BANKING SYSTEM

A Project Presented to the
Faculty of California State University,
San Bernardino

by Ping-Yu Hou
June 2004
Approved by:

Dr. Kerstin Voigt, Chair, Computer Science

Date

Dr. David Turner

ABSTRACT Customer Relationship Management for Banking System (CRM BANKING) is a web application used by banks to study customer information. It interacts with banking data warehouse to retrieve and analyze customer's personal information, account profile, and transaction history. CRM BANKING applies statistics and data mining techniques to understand the correlation between banking data and its customers through pattern recognition. This information is crucial for customer relationship management and improvement.
iii

ACKNOWLEDGMENTS I thank the faculty of Computer Science department for giving me an opportunity to pursue my Master Science in Computer Science at California State University, San Bernardino. I gratefully acknowledge the invaluable guidance of my advisor, Dr. Kerstin Voigt, who directed me through this entire effort. I would also like to express my sincere appreciation to the other committee members, Dr. Ernesto Gomez and Dr. David Turner for their valuable suggestions. Last but not least, I would like to thank my family for their patience and encouragement during the course of my Master Science program.
iv

TABLE OF CONTENTS

ABSTRACT................................

iii

ACKNOWLEDGMENTS....................... . ................ iv

LIST OF TABLES . ........

viii

LIST OF FIGURES......................................... ix

CHAPTER ONE: INTRODUCTION

1.1 Purpose of this Project.....................

1

1.2 Project Products ..............................

2

CHAPTER TWO: CUSTOMER RELATIONSHIP MANAGEMENT FOR

BANKING SYSTEM ARCHITECTURE..........

3

2.1 Software Interfaces ..........................

5

CHAPTER THREE: DATABASE DESIGN

3.1 Data Analysis..... .................. ........

6

3.2 Database Schema Conceptual Model - ER

Diagram...... . . .......................... .. . . .

6

3.3 Database Schema Logical;. Mode,1

Relational Schema . ............................

9

3.4 Data Type and Details ............1............ 11

CHAPTER FOUR: PROJECT IMPLEMENTATION . ............... 17

4.1 Customer Relationship Management For Banking System .Graphical User- Interface Design....... ................................. 18

4.1.1 Customer Relationship Management For Banking System Login ............. 18

4.1.2 Inquire User Information Function .... 19

4.1.3 Add User Function . . ................... 20

4.1.4 Edit User Information Function...... 21

v

4.1.5 Delete User Function ....

22

4.1.6 Customer Inquiry Function ............ 23

4.1.7 Channel Affinity Function ............ 24

4.1.8 Percentile Analysis Function ......... 26

4.1.9 Segmentation Function ................ 31

4.1.10 Customer Behavior Analysis Function............................... 33

CHAPTER FIVE: DATA MINING ALGORITHMS ■

5.1 K-Means Algorithm ............................ 38

5.2 Decision Tree Algorithm...................... 3 9

CHAPTER SIX: SYSTEM VALIDATION

6.1 Unit Test..................................... 41

6.2' Subsystem Testing ............................ 45

6.3 System Testing................................ 46

CHAPTER SEVEN: MAINTENANCE MANUAL

7.1 Software Installation.................

47

7.1.1 RedHat Installation ................... 47

7.1.2 MySQL Installation .................... 48

7.1.3 JAVA 2 Platform, Standard Edition (J2SE) ................................. 49

7.1.4 Tomcat................................. 50

7.1.5 JAVA Database Connectivity (JDBC) .... 51

7.2 Variables Modification ....................... 52

7.2.1 System Variables ...................... 52

7.2.2 Copying Files......................... 52

vi

7.3 Customer Relationship Management For Banking System Installation/Migration ...... 53

7.4 Backup...................

53

7.4.1 System Backup......................... 53

7.4.2 Database Backup....................... 54

CHAPTER EIGHT: CONCLUSION AND FUTURE DIRECTIONS

8.1 Conclusion.................................... 55

8.2 Future Directions........................... , 55

REFERENCES.............................................. 5 7

vii

LIST OF TABLES Table 1. Structure of Table User..................... 11 Table 2 . Structure of Table Role..................... 11 , Table 3 . Structure of Table Cif...................... . 12 Table 4 . Structure of Table Pbacno . ................ . . 12 Table 5. Structure of Table Ckacno................... 13 Table 6. Structure of Table Ctacno................... 13 Table 7. Structure of Table Arff..................... 14 Table 8. Structure of Table Criteria................ 15 Table 9. Structure of Table Pbtxn.................... 15 Table 10. Structure of Table Cktxn.................... 16 Table 11. Unit Test Results........................... 41 Table 12. Subsystem Test Results...................... 46 Table 13 . System Test Results......................... 46
viii

LIST OF FIGURES

Figure 1. CRM BANKING System Architecture ...........

3

Figure 2. Banking Data Warehouse E-R Diagram ........

7

Figure 3. Application System E-R Diagram ............

8

Figure 4. CRM BANKING System Database

Relational Schema ..........................

9

Figure 5. Use Case Diagram............................ 17

Figure 6. Login Page.................................. 19

Figure 7. Inquire User Information Function ......... 20

Figure 8. Add User Function........................... 21

Figure 9 . Edit User Function....... ,................ 22

Figure 10 . Delete User Function....................... 23

Figure 11. Customer Inquiry Function ................. 24

Figure 12. Selection Page of Channel Affinity Function.........:.......................... 2 5

Figure 13. Applied Channels Statistic of Channel Affinity ..... .......... ................. 26

Figure 14. Group Selection Page of Percentile Analysis.................. ................. 2 7

Figure 15. Criterion Selection Page of

Percentile Analysis..... -............. .

28

Figure 16. Percentile Analysis Function by Customer Groups ............................ 29

Figure 17. Percentile Function by Account Groups...................................... 3 0

Figure 18. Percentile Function by Transaction Groups...... ............................. . . 31

Figure 19. Selection Page of Segmentation Function.................................... 32

Figure 20. A List of Customers by Segmentation Function.................................... 33

ix