Relational Modeling Example: The Bank Case

Modeling Steps:The Bank Case

• Step1: Semantic Modeling Using GSER -> bank.ser (for GSER)
• Step2: Operational Modeling Using GOER -> bank.oer (for GOER)

RPI's CIM Modeling Example:

• CIM Semantic Model -> cim.ser (for GSER) & Diagram Images
• CIM Operational Model -> cim.oer (for GOER) & Diagram Images
• Technical Paper for RPI's CIM model: Core Information Model

The Bank Case

Step 1 - Semantic E-R Modeling

This modeling example consists of seven reports required of a typical bank. These reports are modeled as SUBJECTs. These are numbered A to G below. These seven SUBJECTs are joined by a single CONTEXT called "process". The Data Items and Functional Dependencies carried by each SUBJECT are also given, as also two rules which can be entered as intra-subject rules for the first subject.

A. Branch Manager Report (Exception)

The data elements representing the entities of this report are:

BRANCH-NO, BRANCH-NAME, BRANCH-ADDR, MGR-NAME, ACCT-NO, ACCT-TYPE, CID, CUST-NAME, X-DATE, X-TIME, X-TYPE, AMOUNT, REASON-CODE, ACTION-CODE, REMARKS.

The FD relationships between the data elements are:

1. BRANCH-NO ---> BRANCH-NAME, BRANCH-ADDR, MGR-NAME. For a given BRANCH-NO there is only one BRANCH-NAME and BRANCH-ADDR (branch address), and also only one MGR-NAME (manager name).

2. ACCT-NO ---> ACCT-TYPE, BRANCH-NO, CID. For a given ACCT-NO (account number) there is only one ACCT-TYPE (account type). But there may be many accounts of the same type. This is a one-to-many mapping. 3. CID ---> CUST-NAME. A given CID (customer identification number) uniquely identifies the CUST-NAME (customer name).

But there may be many customers with the same name. This is also a one-to-many mapping.

4. ACCT-NO*X-DATE*X-TIME ---> X-TYPE, AMOUNT. The ACCT-NO (account number) with X-DATE (transaction date) and X-TIME (transaction time) uniquely identify the transaction. The X-TYPE (transaction type), and the AMOUNT are uniquely determined.

5 ACCT-NO*REASON-CODE ---> ACTION-CODE, REMARKS. The action to be taken (ACTION-CODE) and the REMARKS to be registered for an exception are dependent on the REASON-CODE and on the ACCT-NO (account number), that is, on the customer for whom an exceptional action has to be taken.

B. Branch Manager Report (Weekly Exception Summary)

The data elements representing the entities of this report are:

BRANCH-NO, BRANCH-NAME, BRANCH-ADDR, MGR-NAME, REASON-CODE, DSTRPT, DENDRPT, NO-X-TOTAL, NO-ACCTS-TOTAL, AMT-TOTAL

The FD relationships between the data elements are represented below.

6. BRANCH-NO ---> BRANCH-NAME, BRANCH-ADDR, MGR-NAME

7. BRANCH-NO.*DSTRPT*DENDRPT*REASON-CODE ---> NO-X-TOTAL, NO-ACCTS-TOTAL, AMT-TOTAL.

C. Branch Loan Status

The data elements representing the entities of this report are:

BRANCH-NO, BRANCH-NAME, BRANCH-ADDR, MGR-NAME, LOAN-NO, LOAN-TYPE, LOAN-ASSGND, TOTAL-LOAN-COLLECTED, TOTAL-LOAN-TO-BE-COLLECTED, INTEREST, BRANCH-NO, CID.

The FD relationships between the data elements are represented below.

8. BRANCH-NO ---> BRANCH-NAME, BRANCH-ADDR, MGR-NAME

9. LOAN-NO ---> LOAN-TYPE, LOAN-ASSGND, TOTAL-LOAN-COLLECTED, TOTAL-LOAN-TO-BE-COLLECTED, INTEREST, BRANCH-NO,CID

D. Teller Cash Drawer

The data elements representing the entities of this report are:

BRANCH-NO, BRANCH-NAME, BRANCH-ADDR, TELLER-NO, TELLER-NAME, B-DATE, COH-SOD, COH-EOD, CASH-RECD, CASH-DISPENSED, CHECKS-RECD-US, CHECKS-RECD-OTHER, CHECKS-RECD-TOTAL, AMT-RECD-US, AMT-RECD-OTHER, AMT-RECD-TOTAL, CHECKS-DISPENSED-TOTAL, NET-FLOW, AMT--CHECKS-DISPENSED-TOTAL.

The FD relationships between the data elements are represented below.

10. BRANCH-NO ---> BRANCH-NAME, BRANCH-ADDR

11. TELLER-NO ---> TELLER NAME

12. TELLER-NO*B-DATE ---> BRANCH-NO, COH-SOD, COH-EOD, CASH-RECD, CASH-DISPENSED, CHECKS-RECD-US, CHECKS-RECD-OTHER, CHECKS-RECD-TOTAL, AMT-RECD-US, AMT-RECD-OTHER, AMT-RECD-TOTAL, CHECKS-DISPENSED-TOTAL, NET-FLOW, AMT-CHECKS-DISPENSED-TOTAL.

E. Teller Audit Report

The data elements representing the entities of this report are:

BRANCH-NO, BRANCH-NAME, BRANCH-ADDR, TELLER-NO, TELLER-NAME, B-DATE, AUDIT-REASON, NO-X-TYPE1, NO-X-TYPE2, LARGEST-AMT-TYPE1, LARGEST-AMT-TYPE2

The FD relationships between the data elements are represented below.

13. BRANCH-NO ---> BRANCH-NAME, BRANCH-ADDR

14. TELLER-NO ---> TELLER-NAME

15. TELLER-NO*B-DATE ---> BRANCH-NO, AUDIT-REASON, NO-X-TYPE1, NO-X-TYPE2, LARGEST-AMT-TYPE1, LARGEST-AMT-TYPE2

F. INQUIRY Transaction

The data elements representing the entities of this report are:

ACCT-NO, ACCT-TYPE, BALANCE, CID, CUST-NAME, CUST-ADDR, CUST-TEL-NO, CUST-DOB.

The FD relationships between the data elements are represented below.

16. ACCT-NO ---> ACCT-TYPE, BALANCE, CID

17. CID ---> CUST-ADDR, CUST-TEL-NO, CUST-DOB

18. CUST-NAME*CUST-DOB <---> CID

G. DEPOSlT/WITHDRAWAL Transaction

The data elements representing the entities of this report are:

ACCT-NO, ACCT-TYPE, BALANCE, BRANCH-NO, BRANCH-NAME, BRANCH-ADDR, X-DATE, X-TIME, X-TYPE, X-NO, AMOUNT, TELLER-NO, PASSBOOK-LINE-NO.

The FD relationships between the data elements are represented below.

19. ACCT-NO ---> ACCT-TYPE, BALANCE, BRANCH-NO

20. BRANCH-NO ---> BRANCH-NAME, BRANCH-ADDR

21. ACCT-NO*X-DATE*X-TIME ---> X-TYPE, X-NO, AMOUNT, TELLER-NO, PASSBOOK-LINE-NO

Declaring Equivalence

IBMS allows you to declare equivalence between data items. Four data items in theis case are equivalent: b_date, x_date, dentrpt and dstrpt. In order to declare equivalence between these data items, click on "Insert", then choose "Equivalence". In the "Declared Equivalence" window, choose "Add" and then add the above four equivalences.

* Adding Operating Rules for Bank Case

In addition to data modeling, the IBMS also provides facility for including inter- and intra-subject production rules. Two such rules are encountered in the first report , the Branch Manager Report (Exception) report; that the automatic loan account "Checking Plus" may be overdrawn or a savings account exceeds the balance of $100,000, which is the maximum amount insured by the Federal Deposit Insurance Company. The way IBMS would handle these rules is by incorporating them as intra-subject rules for the given subject (Daily_Mgr_Rpt). The two rules and their IBMS syntax are as follows:

Rule Syntax: IF [Expression] THEN [Action]

(a) If a "Checking Plus" account is overdrawn, charge $15.00 to the account and inform the customer.

IF (AMOUNT < 0) AND (ACCT-TYPE="Checking Plus" ) THEN AMOUNT :="AMOUNT" 15.00; INFORM(CUST-NAME);

(b) If a savings account exceeds the balance of $100,000, inform the customer.

IF (AMOUNT > 100000) AND (ACCT-TYPE = "Savings") THEN INFORM (CUST-NAME);

To incorporate these rules in the Subject Daily_Mgr_Rpt:

• Click on the Subject with the right mouse button.
• Under the window "Rule List", click "Add".
• In the Rule Builder window that pops up, check type "operational" for the rule and give it an appropriate name, for example, Checking Plus.
• In the Edit Window, type the first rule as it is given above and click "OK".
• Repeat for the second rule.

Step 2 - Operational E-R Modeling

The CASE-tool is used to obtain an Operational Entity Relationship (OER) Model. This model contains the Operational Entities and three kinds of Relationships. At this stage, the modeling process is complete. The user can now use the CASE-tool to generate a database schema.

Acknowledgement:

Based on a similar example in S.Atre, Database: Structured Techniques for Design, Performance and Management, Wiley Interscience, 1980.