Schemas Integration - Information Systems Reengineering, Integration and Normalization - page 276

,'
FXVWRPHU BQDPH
GDWH

ORDQFRQWUDFW
EHJLQ BGDWH
PDWXUH BGDWH

KRPH
ORDQ

KRPHORDQ
FXVWRPHU

ORDQ
FRQWUDFW

R

&XVWRPHU

DXWR
ORDQ

FDUORDQ
FXVWRPHU

GUDZ DFFXUH UHSDLG EDODQFHG

Q

DFFXPXODWH RSHQHG

0RUWJDJH
ORDQ
GUDZGRZQ

/RDQ
LQWHUHVW
W\SH

0RUWJDJH
ORDQ
UHSD\PHQW

0RUWJDJH
ORDQ
EDODQFH

Q

Q

ORDQFRQWUDF W
GUDZGRZQBGDWH
GUDZGRZQBDPW

ORDQFRQWUDF W
UHSD\PHQWBGDWH
UHSD\PHQW

ORDQFRQWUDF W
ORDQBEDOBGDWH
EDODQFHBDPW

ORDQ
KLVWRU\

DFFRXQW

R

DFFRXQW

IL[HG
LQWHUHVW
UDWH

LQGH[
LQWHUHVW
W\SH

SDVWORDQ
SDVWBORDQBVWDWXV

&RQVWUDLQWUXOHVLQVWRUHGSURFHGXUH

IL[HGLQWHUHVW BUDWH
DFFXUUHG BLQWHUHVW

KDV

,IEDODQFHBDPW!
7+(1ORDQBVWDWXV RXWVWDQGLQJ
(/6(,)EDODQFHBDPW
7+(1ORDQBVWDWXV SDLGBRII

LQGH[ BLQWHUHVWBHIIBGDWH
LQGH[ BLQWHUHVWBUDWH
DFFXUHG BLQWHUHVW

LQGH[
LQWHUHVW
UDWH

Fig. 7.16 Integrated loan system schema

In the first iteration, in step 1.1, there are two synonyms: Loan_status and Bal-
ance_amt such that the Loan_status can be derived from the Balance_amt. As a
result, we can replace Loan_status by Balance_amt with a stored procedure to de-
rive Loan_status value from Balance_amt. In step 2.2, the intermediate integrated
schema will be merged with the index rate schema. There is an overlapping gen-
eralization between the two schemas such that a loan must be on fixed or indexed
interest rate. Thus, by joining the integrated schema and the index rate schema with
overlap generalization, the two schemas can be integrated.
In the second iteration, in step 2.6, there is an implied relationship between the
Loan Contract schema and (Mortgage loan) Customer segment such that ID# is
used as an attribute in loan schema but as an entity key in customer schema. Thus,
we can derive cardinality from the implied relationship between these entities, and
integrate the two schemas into one EER model.
In the third iteration, in step 2.6, there is an implied relationship between the
Loan Contract schema and (Auto loan) Customer segment and integrate the two
schemas into one EER model. In step 3.1, the relationships between the loan con-
tract and the two customer entities can be merged into an overlap generalization as
shown in Fig. 7.16 .