The Basics - ER and ORM Diagrams

(Page 6 of 8 )

Entity-relationship (ER) diagrams (sometimes called ERDs) were introduced in the mid-1970s, originally by Dr. Peter Chen. They’re used for conceptual modeling of relationships between entities, just as their name suggests. The fact that ER diagrams are used for conceptual modeling makes them different from the database model diagrams used in this book. The idea with ER diagrams is that anyone involved in a project should be able to understand an ER diagram. ER diagrams consist of different entity types, relationships, attributes, and types.

The entities are real-life objects, such as users, and sometimes they’re more abstract than that, but they must be meaningful to the business. The attributes are used to describe the entities, and the relationships are used between entities.

One problem with ER diagrams is that there’s no real standard, although most people use ER diagrams with only small variations. One notation that seems to have been standardized is the crow’s feet notation, which is generally used to show cardinality of relationships between entities, using 0 (zero or more entity instances), 1 (one or more entity instances), or n (several entity instances). See Figure 1-11 for an example.

Figure 1-11.  Crow's feet notation on a simple ER diagram

Object Role Modeling (ORM) is a method for designing database models at the conceptual level, meaning you’re using terms and diagrams that can be understood by the user. A comparison to UML with regards to visual software communication is in order, because that’s what it is: an easy way of visually communicating a database model at a level that’s understood by all parties in a project. ORM diagrams are different from the database model diagrams we use in this book in the way they communicate their content. Check out the ORM Web site (http://www.orm.net) for more information.

---

In Exercise 1-9, you’ll start a new database model diagram and add a table entity to it.

EXERCISE 1-9

1. Open VEA (if it isn’t already open).

2. Create a new database model by selecting File -> New -> Database -> Database Model Diagram. VEA should now look like Figure 1-12.

Figure 1-12.  VEA open with a new database model diagram

   3.  Create an entity by dragging the Entity icon from the Entity
        Relationship tab in the Shapes window (usually located on the
        top left—see Figure 1-12).

   4.  Double-click the new entity (Table1) on the diagram to see the
        properties for it. You can also right-click the entity and select
        Properties from the pop-up menu. Now the Database Properties
        window is shown at the bottom of the Visio workspace, as in
        Figure 1-13. This is where you can set the properties, such as
        the name, for the entity that is selected on the database
        model diagram.

           Figure 1-13.  The Database Properties window open in the
                                   Visio workspace

Now that you’ve added the table⁵ entity to the diagram, it’s time to set the properties of the table. Exercise 1-10 shows how to do this.

EXERCISE 1-10

1. Name the table entity User in the Physical name text box in the Database Properties window. When you start typing, you’ll notice that the text in the Conceptual name text box is synchronized with the contents of the Physical name text box. However, this only happens if you’ve selected the Sync names when typing check box, which is next to the Physical name text box.

2. Select the Columns category from the Categories list.

3. Type ID in the Physical Name column and select Row ID from the Data Type list. Make sure that Portable data type is selected as the Show option (see Figure 1-14).

4. Select the Req’d (Required) and PK (Primary Key) columns.

5. Repeat steps 3 and 4, creating two new columns named Name and Password of data type VarChar (variable length string). Make sure you select the Req’d column. The Database Properties window should now look like Figure 1-14.

 
Figure 1-14.  The Database Properties window open on the Columns category page

---

NOTE In our example, the physical name and the conceptual name are the same, and we think it’s wise to keep it that way. However, you can choose to have two different names for the table entity: one that’s used in the database and one that’s used in your diagrams. You would do this if you have a conceptual name with spaces or other characters that are not supported by the naming scheme implemented by the database on which you’re creating the table.

---

In Figure 1-14, you can see the Database Properties window open on the Columns category page. There is one small problem with the two VarChar columns (Name and Password)—they’ve automatically been set to a maximum length of 10 characters, which isn’t enough in our table. Therefore, you need to change the maximum length to 50 to accommodate a longer name and password in the table. As you can also see from Figure 1-14, the data type is named SBCS VarChar(10). SBCS stands for Single Byte Character System, which refers to the standard Western character sets that require only one byte for storing a character. However, if you need to store characters from other character sets such as Japanese or Chinese, which use two bytes of storage per character, you need to use the Double Byte Character System (DBCS).

In Exercise 1-11, you’ll adjust the length of two table columns.

---

NOTE We’re aware that 50 characters for a password might seem a bit excessive, but from a user’s point of view, it’s better to have too much space than too little. For this example, just follow the exercise instructions, which demonstrate how to change the size of a column.

---

EXERCISE 1-11

1. Select the Name column and click the Edit button. This brings up the Column Properties dialog box.

2. Select the Data Type tab, as shown in Figure 1-15.

Figure 1-15.  The Column Properties dialog box showing the Data Type tab

   3.   Type 50 in the Length text box.

   4.   Click OK.

   5.   Repeat steps 1 through 4 for the Password column.

---

Figure 1-15 shows the Data Type tab of the Column Properties dialog box. You can see the data type properties for the Name column. We’ve set the length to 50 to accommodate a longer name than the default of 10.

Once you’ve changed the column lengths for the two columns, the Database Properties window should look like Figure 1-16. You can see that the column lengths have been changed to 50, because of the changes you made in the Column Properties dialog box.

Figure 1-16.  The Database Properties window with updated column lengths

---

NOTE If you need to know more about databases in general and database programming with C# or VB .NET, we can recommend these titles from Apress: Database Programming with VB .NET, Second Edition, written by Carsten Thomsen, ISBN: 1-59059-032-5; and Database Programming with C#, written by Carsten Thomsen, ISBN: 1-59059-010-4.

---

All the columns are now in place, with the ID column as the unique row ID, which we can later use for joining with other tables. We can also use Name as a unique value column, but in keeping with the way we apply the database normalization forms to our database schemas, this is how we proceed. The ID column has already been designated as the primary key of our table, meaning all values in this column must be unique and null values are disallowed.

Since the Name column is an excellent candidate for searching for a user when performing a lookup from the Logon process, it’s a good idea to create an index on this column. An index will generally facilitate a faster lookup. Exercise 1-12 shows how to add an index.

1. Select the Indexes category from the Categories list.

2. Click the New button, which brings up the New Index dialog box.

3. Type IX_Name in the Non-unique index name text box.

4. Click OK. The Database Properties window, with the Indexes page open, should now look like Figure 1-17.

Figure 1-17.  The Database Properties window showing the Indexes page

   5.  Select the Name element of the Available columns list, and
        click the Add button to add the Name column to the index.

   6.  Select Unique index only from the Index type list. The IX_Name 
        index now consists of the Name column, and the index is a 
        unique index. (There are other options on the Index type list, 
        but which unique index option is the best is database-specific.) 
        The index is shown on the diagram as U1 next to the Name 
        column.

   7.  That’s all you need for this simple database, so save it to 
        \EDWVSNETUMLMSF\Chapter 01\Database.vsd.

This article is excerpted from Enterprise Development with Visual Studio .NET, UML, and MSF by John Erik Hansen and Carsten Thomsen (Apress, 2004; ISBN 1590590422). Check it out at your favorite bookstore today. Buy this book now.

Next: Generating the Database >>
 

More .NET Articles
More By Apress Publishing

Comments On: The Basics

>>> Be the FIRST to comment on this article!