Exercise 5: Modifying transformation properties to roll up

Before you begin, you must complete Exercise 4: Transforming the logical data model to a physical data model.

In this exercise, you will modify the properties of a generalization object in the HR.ldm logical data model, then transform again to a physical data model. You will then observe how the changes to the generalization object properties affected the transformation.

To modify transformation properties to roll up:

  1. In the Data Project Explorer, expand the HR.ldm logical data model, the HR package, and the Diagrams folder.
  2. Double-click the HR entity diagram to open it in the diagram editor.
  3. In the diagram, select the link between the supertype EMPLOYEE and the subtype REGULAR.

    This link represents a relationship called a generalization. A generalization is a relationship that can only be created by using the diagram editor.

    On the General tab in the Properties view, you can see that the Transform as property is set to Separate Table. This explains why the diagram under HR.dbm you saw in the last exercise has four tables, mapping closely to the four entities in HR.ldm, because these entities are set to transform as separate tables. The Separate Table option is used for a normalized design.

  4. Customize the Transform as property for this generalization object by selecting Roll Up instead of Separate Table from the list.
  5. Select the link between the supertype EMPLOYEE and the subtype HOURLY and notice that the Transform as property for this generalization has also been changed to Roll Up.
  6. Save the HR.ldm logical data model.
  7. Repeat the steps in Exercise 4 to transform the HR.ldm logical data model to a new physical data model.

    In this case, specify HR_rollup_pdm as the name for the new physical data model file, and HR_rollup as the schema name.

  8. Repeat the steps in Exercise 4 to create a new overview diagram under the HR_rollup schema and display column compartments in the diagram editor.
  9. Click the HR_rollup tab in the editor area to see the diagram.
  10. Double-click the HR_rollup tab to maximize the diagram editor view.

    Screen capture showing the HR_rollup diagram as described.

    Notice the differences between this physical data model and the one that was created in the last exercise. In this case, the REGULAR and HOURLY tables were not created, and the EMPLOYEE table has more columns and a foreign key mapping to table EMPLOYEETYPE. The Roll Up option is used for one type of denormalized pattern.

  11. Save and close the HR_rollup_pdm.dbm file and the HR_rollup diagram.

Now you are ready to begin Exercise 6: Modifying transformation properties to roll down.