Introduction

This article series is presented in two parts. This article (Part 1) describes application-period temporal data modeling and shows how you can leverage InfoSphere Data Architect 7.6 to perform the following tasks.

• Create a temporal logical data model.
• Transform the temporal logical data model into a temporal physical data model for DB2® for z/OS® version 10 (New-Function mode).
• Generate DDL with temporal-specific syntax for DB2 for z/OS version 10 (New-Function mode) that you can run on the database server.

Part 2 in this series describes bitemporal modeling that requires modifications to an existing model, and shows how you can use InfoSphere Data Architect version 7.6 to add bitemporal support to a physical data model.

Back to top

Problem description

You are a member of the IT division of the Sample Bank. Your team wants to develop an application for the home loan department. The home loan department has specified the following requirements that the application must account for.

• The bank offers home loans to borrowers at variable interest rates that are linked to a benchmark rate that is specified by the Central Bank for a loan duration that the customer specifies.
• The bank revises the home loan interest rates every time that they receive notification that the benchmark rate will be modified.
• The notification for the benchmark rate change is issued by the Central Bank two weeks before the actual date that the rate change is effective. The home loan interest rate is then calculated based on the new benchmark rate and is manually entered into the system.
• The monthly installment bill is based on the loan amount and interest rate at that time.
• The interest rate changes must be tracked over the duration of the loan.

Back to top

Model design

As shown in Figure 1, you create a simplified version of the logical data model called Loan_Model.ldm, that contains entities, attributes, and relationships that model the non-temporal requirements that are listed in the previous problem description.

The LOAN entity models information about a particular loan, including the following.

• LOAN ID: unique identifier code for a loan disbursed by the bank.
• BORROWER ID: unique identifier code for the borrower.
• LOAN AMOUNT: amount of the loan disbursed by the bank to the borrower.

The BORROWER entity models information about a borrower, including the following.

• BORROWER ID: unique identifier code for the borrower.
• BORROWER NAME: name of the borrower.
• BORROWER ADDRESS: address of the borrower.

The LOAN INTEREST entity models the information about the interest rate and expected installment payments of a borrower towards a loan, including the following.

• LOAN ID: unique identifier code for a loan disbursed by the bank.
• INTEREST: variable interest rate for the loan that is computed based on the benchmark rate.
• INSTALLMENT AMOUNT: the installment amount that the borrower pays, based on the variable interest rate.

Back to top

Creating application-period temporal attributes in an entity

The current design does not allow the changes in the interest rate to be tracked. In order to track the changes in interest rate for a loan, you decide to add business-period attributes to the LOAN INTEREST entity. The attributes model a way to track the changes to the interest rate over a period of time that is relevant to the business. You are sure to specify that there is no overlap between the business periods, because there is only one interest rate that is associated with a loan during this business period.

Note: The terms application period and business period are used interchangeably in this article. Both terms are phrases that describe how to model changes over a period of time that is relevant to the business.

This can be achieved by performing the following steps.

1. Select the LOAN INTEREST entity in the Data Project Explorer. The properties of the entity display in the Properties view.
2. Create business-period attributes in the entity by performing the following steps.
   1. Select the Business time period check box to add a business period.
   2. Select the Business period without overlap option to specify that there is no overlap of business periods for the selected LOAN INTEREST entity, as shown in Figure 2.
   
   
   Figure 2. Adding business-period attributes to the LOAN INTEREST entity
   
   
   When you selected these options, the APPLICATION_START_TIME and APPLICATION_END_TIME attributes were added to the LOAN INTEREST entity that model the ability to track and query changes to the interest rate.
   
   Note: You cannot delete the application-period columns. The Delete icon is disabled for application period attributes, as shown previously in Figure 2. When you disable the business-period attributes, the relevant attributes are automatically removed.
3. The updated logical data model with business-period attributes is shown in Figure 3.
   
   Figure 3. Including temporal features in the updated logical data model
   
   
   Note: The icon for the temporal entity is different from the icon that denotes a standard entity. The temporal entity icon contains a clock decorator, as seen on the LOAN INTEREST entity previously shown in Figure 3.

Back to top

Transform the logical data model to a physical data model

You have approved the logical data model design with the home loan department of the Sample Bank. In order to model the physical storage, you must transform the temporal logical data model into a temporal physical data model for the DBMS that you use, DB2 for z/OS version 10 (New-Function mode).

Perform the following steps to transform the model.

1. Right-click the logical data model, then select Transform to Physical Model from the context menu, as shown in Figure 4.
   
   Figure 4. Transforming to a physical data model
   
   
   
2. Select the Create new model option on the Target Physical Data Model page.
3. From the Physical Data Model File page, specify a file name. Then, select DB2 for z/OS and V10 (New-Function Mode) as shown in Figure 5.
   
   Figure 5. Specify database, version, and location for transformation
   
   
   Note: As of version 10, DB2 for z/OS supports temporal models in New-Function mode. As of version 7.6, InfoSphere Data Architect supports modeling for this database type.
4. From the Options page, specify LOAN_SCHEMA in the Schema name field, as shown in Figure 6. Keep all of the other default options.
   
   
   Figure 6. Transform options page
   
   
   
5. From the Output page, view the transformation status, then click Finish to generate the physical data model. The physical data model Loan_Model.dbm is created from the logical data model.

Back to top

Generated temporal physical data model

Inspect the physical data model in the Data Project Explorer, as shown in Figure 7.

Upon inspecting the physical data model, you find that the following items are true.

1. The LOAN_SCHEMA schema is created.
2. Three tables, LOAN_INTEREST, BORROWER, and LOAN are created with the columns that were transformed from the attributes of the logical data model.
3. The LOAN_INTEREST table is a temporal table.
4. Two foreign-key relationships, LOAN_BORROWER_FK and LOAN_INTEREST_LOAN_FK, are created.

A diagram view of the loan physical data model is shown in Figure 8.

You can select the LOAN_INTEREST table in the Data Project Explorer to display the properties of the table in the Properties view. From here, you can verify that the Business time period and Business period without overlap options are selected, as shown in Figure 9.

Now that you've verified the model, you should analyze the model, correct any errors, and then generate the DDL script that you can share with the database administrator in order to deploy your changes.

Back to top

Analyze the model

Before the DDL is generated, use the Analyze Model wizard to analyze the physical data model for common design errors. Because InfoSphere Data Architect version 7.6 supports DB2 for z/OS version 10 (New-Function Mode) temporal modeling, the workbench can analyze models for common temporal design errors.

To analyze the model, perform the following steps.

1. Right-click the location (database) node of the physical data model, then select Analyze Model as shown in figure 10 to open the Analyze Model wizard. Because you selected a physical data model, the options for physical data models are selected by default.
   
   Figure 10. Analyze Model
   
   
   
2. Analyze the temporal properties by clearing the Physical Data Model check box.
3. Select the Temporal Check option. Click Finish.

Back to top

Generate DDL

Now that you have validated your model, perform the following steps to generate the DDL script to deploy the model, then share the script with the database administrator to run the script on the database server.

1. Right-click the LOAN_SCHEMA schema in the Data Project Explorer.
2. Select Generate DDL as shown in Figure 11.
   
   Figure 11. Generate DDL for the new schema
   
   
   
3. Complete the following pages of the Generate DDL wizard.
• Do not change the default settings on the Options page.
• Do not change the default settings on the Objects page.
• On the Save and Run DDL page, as shown in Figure 12, specify a file name. Keep the file name in mind so that you can easily share the file with a database administrator so that they can deploy your changes.
  
  Figure 12. Save and Run DDL page
  
  
  
4. If your organization allows you to deploy data models directly to the server, you can select the Run DDL on Server option before you click Next.
5. On the Select Connection page, select an existing connection as shown in Figure 13, or specify a new connection by clicking New.
   
   Figure 13. Selecting a connection
   
   
   
6. Verify the options that you selected on the Summary page, and click Finish to generate the DDL.

Back to top

Inspect the deployed model in the database server

After the database administrator runs the DDL script, you can inspect the deployed changes in the Data Source Explorer by expanding the nodes down to the LOAN_SCHEMA schema. Upon viewing the model, verify that the BORROWER, LOAN, and LOAN_INTEREST tables were added under the LOAN_SCHEMA schema, as shown in Figure 14.

You can further verify some of your changes. From the Properties view of the workbench, select the LOAN_INTEREST table to display its properties. When you open the Columns tab, you see that the LOAN_INTEREST table is a business-period table that doesn't allow for overlapping time periods, as shown in Figure 15.

Back to top

Conclusion

In this article, you learned how to fulfill application-period temporal requirements for a home loan department of a bank. You learned how InfoSphere Data Architect version 7.6 and DB2 for z/OS version 10 (New-Function Mode) can provide a convenient way to model temporal data. With little effort, you transformed a logical data model that included a temporal entity into a physical data model with a temporal table. Then, you analyzed the model and generated the DDL script that is required to deploy those changes to the target database.

In Part 2 of this series, you will learn how InfoSphere Data Architect version 7.6 helps you model bitemporal requirements for data.

Back to top

Acknowledgements

Thanks to Erin Wilson for the valuable suggestions and the review of this article.

Resources

Learn

• Use an RSS feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
  
  
• Review the A Matter of Time: Temporal Data Management in DB2 for z/OS article.
  
  
• Get the resources you need in the InfoSphere Data Architect area on developerWorks.
  
  
• Download a trial version of InfoSphere Data Architect V7.6.0 and learn how to create a bitemporal model efficiently.
  
  
• Participate in the discussion forum.
  
  
• Get the resources you need in the Information Management area on developerWorks, to advance your skills on a wide variety of IBM Information Management products.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Follow developerWorks on Twitter.
  
  
• Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  
  

Discuss

• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Balaji Kadambi is an Advisory Software Engineer at the IBM lab in Bengaluru, India. He is currently part of the InfoSphere Data Architect development team, and was involved in developing the bitemporal feature in InfoSphere Data Architect V7.6.0. His areas of interest includes Data modeling and Databases. Balaji has over twelve years of experience working in the commercial software industry.

Rahul Jain is a Staff Software Engineer at the IBM lab in Bengaluru, India. He is currently part of the InfoSphere Data Architect development team. His areas of expertise includes Data modeling and Eclipse platform technologies. Rahul has a master's degree in Information Technology from IIIT, Bengaluru, India.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Problem description
• Model design
• Creating application-period temporal attributes in an entity
• Transform the logical data model to a physical data model
• Generated temporal physical data model
• Analyze the model
• Generate DDL
• Inspect the deployed model in the database server
• Conclusion
• Acknowledgements
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers