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.
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.
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.
Figure 1. Simplified logical data model of the loan system
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.
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.
- Select the LOAN INTEREST entity in the Data Project Explorer. The properties of the entity display in the Properties view.
- Create business-period attributes in the entity by performing the
- Select the Business time period check box to add a business period.
- 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.
- The updated logical data model with business-period attributes is
shown in Figure 3.
Figure 3. Including temporal features in the updated logical data modelNote: 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.
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.
- Right-click the logical data model, then select Transform to
Physical Model from the context menu, as shown in Figure
Figure 4. Transforming to a physical data model
- Select the Create new model option on the Target Physical Data Model page.
- 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 transformationNote: 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.
- 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
- 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.
Generated temporal physical data model
Inspect the physical data model in the Data Project Explorer, as shown in Figure 7.
Figure 7. Viewing the physical data model
Upon inspecting the physical data model, you find that the following items are true.
- The LOAN_SCHEMA schema is created.
- Three tables, LOAN_INTEREST, BORROWER, and LOAN are created with the columns that were transformed from the attributes of the logical data model.
- The LOAN_INTEREST table is a temporal table.
- 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.
Figure 8. Loan physical data model
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.
Figure 9. Properties view of LOAN_INTEREST table
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.
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.
- 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
- Analyze the temporal properties by clearing the Physical Data Model check box.
- Select the Temporal Check option. Click Finish.
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.
- Right-click the LOAN_SCHEMA schema in the Data Project Explorer.
- Select Generate DDL as shown in Figure 11.
Figure 11. Generate DDL for the new schema
- 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
- 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.
- On the Select Connection page, select an existing
connection as shown in Figure 13, or specify a new connection by
Figure 13. Selecting a connection
- Verify the options that you selected on the Summary page, and click Finish to generate the DDL.
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.
Figure 14. Viewing the deployed loan model
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.
Figure 15. Verifying updates for the LOAN_INTEREST table
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.
Thanks to Erin Wilson for the valuable suggestions and the review of this article.
- 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.