Solution Of Case Study_java_kolkata

Solution of Case Study - Development of Consumer Relationship Management system for Indian Automobile giant XYZ Limited This solution approach is made on back end database Oracle and front interface technologies with Java/JSP/HTML.

Step -1 The data is contained in the four data files and they will be loaded into Oracle tables via SQL/Oracle loader. These four intermediate tables are: • Sales_File_TAB • ServiceHistory_File_TAB • CustomeCommunication_File_TAB • Dealer_File_TAB These tables will not have any keys and they may be properly indexed for performance. Improvement. These tables would have same number of columns matching exactly with fields of the respective files.

Step-2 A data model needs to be created to accommodate the data from the intermediate tables. These target tables (total 7 nos.) will have inter-relationship and to be used for all front-end queries. The data model could be structured in different ways and normalize as per the transaction needs. In this case study, any standard normalized data model is sufficient to serve the purpose. To create the data model use the relationship requirement between Product/Customer/Dealer/Service History etc as mentioned in the case study. For solution purpose a sample Data model is given below: Data Model Diagram

PK

VehicleChasisNum VihicleModel ModelYear ExteriorColor InteriorColor ModelType

PK,FK3 PK,FK2

CustomerID VehicleChasisNum

FK1

FK2

FK1 FK3

PK

DealerID DealerStDate DealerEndDate DealerZone DealerDist DealerName DealerAddress DealerCity DealerState DealerPostCode DealerPhone DealerPhExtn DealerBillingName DealerBillingAddress DealerBillingCity DealerBilllingState DealerBillingPostCode

Customer

ServiceID DerlerID CustomerID VehilceChasisNum Miledge RODate TechnicianName ServiceDescription LaborAmount ServiceAmount Amount ServiceFeddback ServiceHistoryCol1 DealerID VehicleChasisNum

PK

RetailSalesDate DealerID FinancedFlag FinanacedBy LoanAmount LoanTenure SalesmanMan DealerPreInspectionDate VehicleRegistrationNum SalesPrice

ServiceHistory PK

Dealer

VehicleOwner

Product

CustomerID CustomerFName CustomerMName CustomerLName Cust_DOB Sex CustEmail CustomerPhone

CustomerAddress PK,FK1 PK PK

CustomerID Addressline1 Addressline2 City State PostalCode

CommunicationEvent PK

CommunicationID

FK1

CustomerID ChannelType BrocureRequestDate PriceRequestDate LoanPayoffDate AdhocCommDate CommVehicleModelType CommVehicleModelYear VehicleOwn ContactMethod PlannedToBuyDate PlannedToLoanPayOffDate CommunicationContent

Data Model for Case Study- Indian Automobile CRM

Step-3 Once the data model is designed and finalize, tables will be created and all related primary/secondary keys will be defined.

Step-4 To load the data in 7 target tables, a number of store procedures need to be created. The List of Store Procedures with sequence of operations are given below: 1. SP_Dealer_Load This store procedure will select data from Dealer_File_TAB and insert dealer related data in Dealer table. This SP should run first to ensure all dealer data are populated.

2. SP_Sales_Load. This Store Procedure will select data from Sales_File_Tab table and insert in following tables. • Customer • CustomerAddress • Product • VehicleOwner CustomerID is a unique number which will generate from the system and incremented for next record. Dealer Number (DealerID) will be verified from Dealer table and if not found, records will be rejected. 3. SP_Service_History_Load This store procedure will insert data in Customer, CustomerAddress and Servicehistory tables. Proper Data will be selected from ServiceHistory_File_TAB table and get updated or inserted in • Customer table • Customeraddress table Service data will be selected from Servicehistory_File_TAB table and inserted in ServiceHistory table. ServiceID is a unique number that get generated from system for the first time (ex- 1) and incremented for subsequent records. If any VIN/VehicleChasisNum is not found in product table, then records will be rejected, as no product information is available in Database. 4. SP_Customer_Communication_Load This SP will insert the data in CommunicationEvent, Customer and CustomerAddress tables. Relevant data will be selected from CommunicationEvent_File_TAB table and updated or inserted in Customer and CustomerAddress table. Communication data will be selected and inserted in CommunicationEvent table. For each Communicationevent a unique number will be generated and get attached to the CommunicationID field.

Step-5 Once the data are loaded in tables by different store procedures, we are ready to create the front-end screens and they will be mapped to the table fields through Java API/JSP etc. JDBC/ODBC driver needs to be setup for database connectivity. Some sample screen shots are given and these could be created through JSP or HTML scripts. Data will be fetched through Java API and JSP scripts wherever applicable. For User log on screen, another table will be created where user ID and Password (encrypted) will be stored. This table can be created earlier with proper data fed it in. Sample Screen -1

Sample Screen –2

Sample Screen – 3

Sample Screen – 4

Sample Screen – 5

Additional screens need to be created as per case study requirements.