Data modeling with InfoSphere Data Architect and Informix Dynamic Server

Page 5 of 13  Document options PDF - Fits A4 and Letter 1317 KB (43 pages) Get Adobe® Reader® My developerWorks needs you! Connect to your technical community Rate this tutorial Help us improve this content

Starting top down from logical models

Logical modeling deals with gathering business requirements and converting those requirements into a model. The logical model revolves around the needs of the business, not the database, although the needs of the business are used to establish the needs of the database. Modeling architects deal with logical modeling. When the model design meets the necessary requirements, they get translated to physical models and finally get deployed as physical artifacts to the database. This is a more typical scenario for new applications. Here is how you do it.

1. Right-click on the storesModel project, and select New > Logical Data Model. Give the model the name, storesLogicalModel and then click on Finish. A new Editor will come up on the right of the screen, as Figure 21 shows:
   
   Figure 21. Creating a new logical model
   
   
   

   Note: Because this is at the logical model level, we will deal with logical level items rather than database level items. So, in this model, we add entities, attributes and relationships and the like. When this model gets transformed to a physical model, the entities get mapped to tables, attributes get mapped to columns, and so on.

2. Let's create the model of a simple Book Store, as shown in Figure 22. The entities in this model are Book, Author, and BookCategory. For each of the Book entities, there could be more than one Author, and each Book gets slotted into one or more BookCategory entities. So there is a one-to-many relationship between Book and Author entities and BookCategory entities.
   
   Figure 22. Model that we will be creating
   
   
   
3. Create the entities and attributes using the Palette designer to the right of the diagram window, as shown in Figure 23:
   
   Figure 23. Creating an entity
   
   
   

   You can add elements to the entity by right-clicking (or hovering) on the entity and using the two buttons that appear (as shown in Figure 24) to add a Primary Key or attributes. Alternatively, you can add attributes in the Property Dialog on the bottom.

   
   
   Figure 24. Adding attributes
   
   
   

   Follow these steps to create all three entities and their primary keys and relationships:

   1. Create the Book entity.
   2. Add primary Key BookID.
   3. Add Attributes ISBN, DatePublished, BookTitle, and BookComments.
   4. Set the types, as shown in Figure 25:
      
      Figure 25. Properties of the Book entity
      
      
      
   5. Create the BookCategory entity.
   6. Add primary key BookID.
   7. Add attributes CatCode and CatDes.
   8. Set the types, as shown in Figure 26:
      
      Figure 26. Properties of the BookCategory entity
      
      
      
   9. Create the Author entity.
   10. Add primary keys BookID and AuthorID.
   11. Add attributes AuthorName and AuthorBio.
   12. Set the types, as shown in Figure 27:
       
       Figure 27. Properties of the Author entity
       
       
       
4. Add relationships by using the Palette designer. Alternatively, hover over the parent entity and drag the arrow to the desired child entity, as shown in Figure 28:
   
   Figure 28. Adding relationships
   
   
   
   • Create a relationship between Book and BookCategory. Drag the arrow Book to BookCategory, select Create Identifying Foreign Key Relationship, and pick Use the Existing Child Attribute/Column in the dialog that shows up.
   • Use the exact same method to create a relationship between Book and Author.
5. Once the logical model has been created, save the model by clicking on the Save button in the main toolbar. You can use the property view to change the type or any other detail for the entities.
6. Once you are done with your logical model, let's transform it to a physical model. Here are the steps to accomplish this:
1. In the storesModel project, navigate your way to storesLogicalModel.ldm.
2. In the main toolbar on top, click on the Transform to Physical Data Model button. Refer to Figure 18 to see where the button is. It is located next to the Transform to logical model button.
3. Select Create New Model and click on Next.
4. Choose Informix from the database drop down and click on Next.
5. Leave defaults on in the next screen and click on Next.
6. Click on Finish.

This creates a folder called storesLogicalModel.dbm in the Data Models folder and also in the Data Diagrams folder. Look at the diagram, and you will see Tables and Columns in the physical model, as Figure 29 shows:

Things you can do now

Now that you have a physical model, you can enhance the model further by adding any database-related entities (like indexes and triggers). For example, to add a trigger to the table Book, right-click on Book > Add Data Object > Trigger (Figure 30) and define the logic that goes into your trigger. Remember to save it:

Also, you can generate DDL statements and deploy them to the database. Do this by right-clicking on an object (such as a table) and then clicking on Generate DDL. Figure 28 shows you how to get to the Generate DDL option. Using the wizard, you can either save the script or run it on the server. You can create a single script for your entire physical model as well.

Back to top

	Page 5 of 13