Bridge entity erd

Student Name:__________________ Date:_________

ECET450 Practice Exercise #2
Creating an Entity Relationship Diagram

In Lab 1, you worked with a simple Entity Relationship Model diagram that only depicted one side of the database relationship. Although this is a very important tool for database development, it only shows part of the picture. In this exercise (and in Lab 2) you will explore the process of creating an Entity Relationship Diagram (ERD). The ERD shows everything the E-R Model diagram does and more. In this new diagram, you will be able to show not only the relationship but also the cardinality (number to times the relationship exists), of the relationship. Finally, we learn whether the relationship is mandatory (no null values allowed for the foreign key) or optional (nulls are allowed in the foreign key).

In Lab 1, you had to set a parameter in Visio to get the tool to draw the correct diagram. For this exercise, you will be doing the same thing, only there are more things to select and change when you want to use Crow’s foot notation for the diagram. To begin, select the Database/Options/Document from the top menu bar once you are in Visio.

image1.png

Now we are going to take a different approach than we did in Lab 1. Once again, you should be looking at the Database Document Options box. The selection you make this time will create a different look to your diagram, depending on which way the buttons are set. If you select the IDEFIX button then the primary key column or columns will show at the top, but you will not see a PK designation for them. If you set the relationship button, you will see the PK next to the column. For the purposes of this exercise, we will leave the button as is.

image2.png

Next, you want to select the Table tab from the options box. In this window, you are going to unselect the radio button under the “IDEFIX optionality ‘(O)’” section. This will only clean up the document a little, but will not change the functionality of the diagram.

image3.png

There is one final change to make. Select the “Relationship” tab and you will see the last form that we will modify. For this diagram, you are going to want to use the Crow’s foot notation, so you want to make sure that this button is set. You also want to make sure that the relationship has a name in both directions. To set this, check the “Show verb phrase” radio button (the rest of the buttons should be fine). Compare your screen to the one shown below.

image4.png

If they look the same, select OK and move on to the diagram. The examples will use the same tables as before (INSTRUCTOR and CLASS). Once again, after you have your tables created then select the connection tool from the menu bar. Go through the same process of placing the cursor in the middle of the table with the primary key and dragging it to the table with the foreign key (remember not to release the mouse until the second table border turns red). You should now see a line that depicts the Crow’s foot notation (as displayed in the diagram below).

image5.png

Again, you will be repeating this process as you successfully complete this exercise and Lab 2 for this week.

Bridge Tables

In Lab 1, you used a bridge table in the final solution of #3. The concept of a bridge entity (table) or composite entity is first introduced on page 101 in the database design text. A detailed explanation of this type of entity is presented on pages 147-150. Bridge entities are very powerful tools that resolve many relationships within a database. In this exercise and in Lab 2, you will have several scenarios that will require the use of a bridge entity.

The structure of a bridge entity is centered on the primary key. Bridge entities can be identified by composite primary keys. The primary key is composed of foreign keys referencing the two tables that the bridge entity is trying to connect. To clarify this concept, we will look at the following example.

Let’s take a typical scenario of a student taking many courses and a course with many