Studio I.3 TSER DATA Modeling

(Updated: August 5, 2005)

Part I

SER Modeling

• Steps for starting the IBMS and creating an Enterprise-level Subject are exactly the same as in Studio 1.2.
• Let us call this Enterprise-level Subject, 'Bank', and assign it 53 data items: branch_no, branch_name, branch_addr, mgr_name, acct_no, acct_type, cid, x_date, x_time, x_type, amount, reason_code, action_code, remarks, dstrpt, dendrpt, no_x_total, no_accts_total, amt_total, loan_no, loan_type, loan_assgnd, total_loan_collected, total_loan_to_be_collected, interest, 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, audit_reason, no_x_type1, no_x_type2, largest_amt_type1, largest_amt_type2, balance, cust_name, cust_addr, cust_tel_no, cust_dob, x_no, passbook_line_no .
  • cid is the customer ID.
  • x-date, x-tme and x-type refer to transaction date, time and type respectively. All other data items have self descriptive names. Use your judgement for the data types and length. For example, cid may be alpha-numeric in the bank branch that you a re modeling, in which case it will be of type character.
• The seven reports required for this bank are modeled as seven subjects, namely, Branch Manager Report (Exception), Branch Manager Report (Weekly Exception Summary), Branch Loan Status, Teller Cash Drawer, Teller Audit Report, INQUIRY Transac tion and DEPOSlT/WITHDRAWAL Transaction.
• Decompose the enterprise-level Subject and create the above seven subjects.
• Assign the data items and the functional dependencies to the seven Subjects as given in the list of FDs.
• Declare equivalence between data items. Four data items in this 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 three equivalencies. Use any one of the four data items as your reference and then make the other three equivalent to that one. For example:
  • b_date -> x-date
  • b_date -> dentrpt
  • b_date -> dstrpt
• Next, we need to create a Context to link the above seven Subjects:
  • Click on the Context icon (second row, second column in the tool box) and drop it on the work area.
  • Name this Context 'Transaction' and click OK.
• Next, we will create flows between the Subjects and the Context:
  • Select the 'two way flow icon' (third row, second column in the tool box) and click on a Subject and the Context to create the two way flow.
  • We will also add two operating rules to our model.
  • Your SER model is complete here.

SER TO OER Mapping

• Save the SER file and go to the next step, SER-to-OER mapping.
  • The first window that pops-up is the 'Key Verification' window. You will see all the seven subjects listed there. You can check the keys for any one of them and also replace a primary key with an alternative key. If you are sure about the correctne ss of keys and functional dependencies that you have entered, you can skip this step.
  • In any case, the system proceeds to normalize all Subjects into OER submodels one at a time.
• Next window to pop-up is 'FR --> MR'.
  • Depending on the user defined semantic, a Functional Relationship (FR) has the option of becoming a Mandatory Relation. For example, the OE Loan_Status and OE OE_branch_no have a foreign key relationship because the primary key branch_no of the latter is a non primary key of the former. This FR will also be an MR if delete an instance of branch_no, all matching values of the non-key branch_no and associated loan_no in the OE Loan_Status necessitates its deletion. This is NOT the case here. The branch_no 123 may have granted a loan_no 456. While the instance branch_no 123 may get deleted, we will still like to keep record of the loan_no 456.
    But this case has two MR situations. Between OE 'Inquiry' and OE 'OE_cid', the PK cid is a nPK in OE 'Inquiry'. By converting this FR into an MR, what we are achieving is that is a particular cid=123 is deleted, then the acc_no corresponding to cid=123 is also deleted from the OE 'Inquiry'. The other MR is between the OE 'Loan_Status' and 'OE_cid' where PK cid is a nPK in OE 'Loan_Status'. Note the windows to make these two MRs could be seperated by "Name Determination" windows discussed below.
  • You can read more about FR --> MR conversion by clicking on the Help button in the pop-up window.
• The next window to pop-up is the 'Name Determination' window.
  • After the normalization step, the system proceeds to merge the entities and relationships that are common to more than one OER submodels (Subjects). This is the final step in the mapping process and consolidates all the OER submodels into a single application OER model.
  • This is done by (a) merging entities and relationships in all the submodels that have the same key and (b) by the creation of foreign key links.
  • However, these common entities and relationships most likely would have different names that need to be consolidated. The system recognizes these different names and pops up a new window each time it processed a common entity (OR) or relationship (PR). The 'Name Determination Window' gives you the option of either selecting one common name for the OE/PRs to consoloidate or define a completely new name, ideally one more appropriate in meaning.
• This will end SER to OER mapping and you will get the "Mapping Successful!!!" message.
• Next, go to the OER modeling option and pick out the OER file created in the above step. Select 'redraw' option under 'tools' and 'fully optimize' your results to view your OER. Use the right mouse button to edit OEs/PRs to give them meaningful name s.
• You can compare your OER with what we got.

Schema Generation

• Exit the OER Modeling module, click on SchemaGeneration and choose Oracle.
• Under File, select Generate Schema option.
  • The system will prompt you to select the .ser and the .oer files in that order.
• The software will generate the required schema file and prompt you to name it with a .sql extension. Save the file with some appropriate name, bank.sql, for example.
• Use any text editor, notepad for example, to view your schema (bank.sql) file.

Part II

In this section you will import the schema into PostgreSQL and manipulate the database tables by adding relevant data items and adhering to the integrity constraints of the database schema. See Studio 1.1 on how to install PostgreSQL. Since the bank.sql file contains an Oracle schema, a few, but slight changes must be made to make this compatible with PostgreSQL, which can be accomplished manually. Make the following changes to the bank.sql file in a text editor such as Notepad:

• Remove all Oracle REMARK statements
• Remove all AUTOCOMMIT statements
• Replace the NUMBER attribute with the INT4 attribute

Save the file. From the command line (e.g. Microsoft Windows XP - START > RUN > Type cmd). In the window that pops up change to the PostgreSQL directory (cd C:\Program Files\PostgreSQL\8.0\bin) and execute the following command: (N.B. To execute these commands you must have completed Studio 1.1 in its entirety.)

createdb bankcase

The PostgreSQL Server will confirm the creation of this database. Then execute the following command to import the bank.sql file into the bankcase database.

psql -d bankcase < bank.sql

The PostgreSQL Server will confirm the of creation of the database objects in the bankcase database.

You can then manipulate the database bankcase via the pgAdmin III application, or via the command line.

Deliverables

• An executive summary stating what you have done and included in the report, plus (optional) discussion or remark.
• A printout of the SER model, the OER model, and the schema generating script.