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:
- In the Data Project Explorer, expand the HR.ldm logical data model,
the HR package, and the Diagrams folder.
- Double-click the HR entity diagram to open it in the diagram editor.
- 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.
- Customize the Transform as property for
this generalization object by selecting Roll Up instead
of Separate Table from the list.
- 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.
- Save the HR.ldm logical data model.
- Repeat the steps in the previous excercise 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.
- Repeat the steps in the previous exercise to create a new overview
diagram under the HR_rollup schema and display column compartments in the
diagram editor.
- Click the HR_rollup tab in the editor area to see the diagram.
- Double-click the HR_rollup tab to maximize the diagram editor view.
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.
- Save and close the HR_rollup_pdm.dbm file and the HR_rollup diagram.