< Previous | Next >

Adding relationships between tables by using the overview diagram and the Properties view

In the last exercise, you created a simple overview diagram that contains a subset of the tables that are in the sample_model.pdm physical data model. In this exercise, you will modify the overview diagram, and also use the overview diagram to modify the underlying data model.

Some of the objects on the palette are used for annotation only, and do not change the underlying data model. For example, you can add notes or text objects to the diagram to document information about model objects or relationships. You can also add geometric shapes to the diagram that contain text or that visually group diagram objects together. You can modify the display characteristics of the diagram by changing font or color options, or by changing diagram properties in the Properties view. All of these options change the diagram properties, but do not modify the underlying data model.

The Data area of the palette contains data model objects. When you add or modify data model objects by using the palette or the diagram surface, you modify the underlying data model. Changes that you make to data objects in a diagram are automatically saved to the diagram, but you must explicitly save the model file to save the changes to the model.

In the Properties view in the Data perspective, you can view and modify the properties of data model objects. Typically, you use the diagram editor and the Properties view together to modify data model objects. When an object is highlighted in either a diagram or the Data Project Explorer, the Properties view changes to show the properties of that object.

First, you add a Note object that describes the diagram.

To add relationships between tables by using the overview diagram and the Properties view:

  1. Click the Note object on the diagram palette. If the Note object is not visible, click the drop down arrow at the top of the diagram palette to see available annotation objects.
    Screen capture showing the diagram palette.
  2. Click again on the diagram surface to add the Note object to the diagram. Hold the left mouse button on the diagram surface, and drag the Note object to size it on the diagram. When you release the left mouse button, your cursor is active in a text box inside the Note object.
  3. In the text box inside the Note object, type my SAMP schema diagram, and press Enter. Now, use the diagram to modify the underlying data model by adding a relationship between the EMPLOYEE and EMP_PHOTO tables.
  4. Click on the diagram surface.
  5. In the Properties view, select the Show key and Show non-key check boxes.
  6. Right-click on the diagram surface, and select Arrange All.
  7. Find the EMPLOYEE table in the diagram by clicking a blank area in the diagram and pressing Ctrl+F. In the window that opens, type EMPLOYEE in the Find field, and click Find Next. Then click Close to close the window. The EMPLOYEE table is moved to the visible area of the diagram and highlighted. In the Properties view, you can see properties of the EMPLOYEE table.
  8. Click the Columns tab in the Properties view.
  9. Select the check box in the Primary Key column next to EMPNO to mark the EMPNO column as the primary key. Note the changes in the diagram. EMPNO is now displayed in the key compartment of the EMPLOYEE table in the diagram, with an icon next to it to indicate that EMPNO is the primary key.
    Screen capture showing the EMPLOYEE table in the diagram with the changes as described in this step.
  10. Use the Find window again to find and select the EMP_PHOTO table in the diagram.

    This table has a primary key that consists of the EMPNO and PHOTO_FORMAT columns.

    There are two ways to draw a relationship between tables in the diagram editor. You can use the pop up action bars on the diagram surface, or you can use the diagram palette. First, you will create a relationship between the EMPLOYEE table and the EMP_PHOTO table by using the pop up action bars on the diagram surface.

  11. Hover your mouse over the EMPLOYEE table object in the diagram and you should see two arrows appearing outside of the table, pointing in opposite directions. You will use the arrow that points away from the EMPLOYEE table that represents a relationship from parent to child to draw a relationship between the EMPLOYEE table and the EMP_PHOTO table.
    Screen capture showing the EMPLOYEE table in the diagram with arrows appearing, as described.
  12. Drag the arrow that points away from the EMPLOYEE table, and drop it on the EMP_PHOTO table. In the menu that opens, select Create New Identifying FK Relationship
    Screen capture showing the diagram and menu as described in this step.
    .
    A relationship object is drawn on the diagram between the EMPLOYEE and the EMP_PHOTO tables.
  13. Select the relationship object, and click the Details tab in the Properties view. Notice that all foreign key clauses are automatically constructed.
    Screen capture showing the Details tab of the Properties view, as described.
  14. Now, add some information to the relationship properties to identify the roles of each table in the relationship.
    • In the Inverse Verb Phrase field, type photo.
    • In the Verb Phrase field, type employee.
    • On the Referential Integrity page, set the referential integrity type On Delete to CASCADE by selecting CASCADE from the list.
      Screen capture showing the Referential Integrity tab of the Properties view, as described.
    Now, use the diagram palette to add an identifying relationship from EMP_RESUME to EMPLOYEE.
  15. Click the Identifying relationship object from the palette.
  16. Click the EMP_RESUME table and hold the left mouse button.
  17. Drag the cursor to the EMPLOYEE table and release the left mouse button to draw the relationship.
    Screen capture showing the diagram with the new relationship drawn, as described.
  18. Following the same steps as before, mark the DEPTNO column as the primary key for the DEPARTMENT table by selecting the DEPARTMENT table and modifying the Columns tab in the Properties view.
  19. Use the diagram surface or the diagram palette to add a non-identifying optional relationship from DEPARTMENT to EMPLOYEE.
  20. Select the relationship object in the diagram, and click the Details tab in the Properties view. Now, set the WORKDEPT column in the EMPLOYEE table as the key column, replacing DEPTNO.
  21. Click the ellipsis (...) button next to the Key Columns field on the Details page.
  22. In the window that opens, select the check box next to WORKDEPT and clear the check box next to DEPTNO.
  23. Click OK. Now that you have modified the data model through the diagram, the label for the sample_model.pdm file has an asterisk (*) character next to it in both the Data Project Explorer and in the physical model editor title bar. This indicates that changes have been made to the model that have not yet been saved.
    Screen capture showing the asterisk (*) character displayed, as described.
  24. To save your diagram changes to the model, click File > Save All from the main menu.