ECET 450- WEEK 2 iLab and Practice exercise

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.

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.

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.

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.

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).

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 students. This is a typical M:N relationship that can be represented with a bridge entity.

As a rule, we do not want to have any M:N relationships in the database, so it is best to break them up by creating a new entity that contains the necessary columns to connect the other two tables. For this example, we would create an entity that contains just the STUDENT_ID and COURSE_NUM columns, as seen below.

Notice that the primary key of this new table is a combination PK and made of the primary key columns from the other two tables. Now, we can stick this new table between the other two tables and allow it to become the bridge (connection) table between them. Look what happens to the two columns in the ENROLL table when we make this adjustment to the diagram.

Now, the columns in the bridge entity help enforce data integrity. A student cannot be enrolled in the same course twice since the combined values in the student_id and course_num columns cannot exist more than once (the combined primary key). However, the individual columns act as foreign keys referencing back to the primary table and thus enforce referential integrity. This ensures that only valid students are registered in valid courses.

The use of bridges, or connection tables, is a very valuable tool in database development. They solve the multiple relationship problems and they allow for a much leaner database design. In this exercise, you will find a need to use this concept twice.

The Assignment

The primary entities below are bold and underlined . Key words or phrases that help identify the relationship between the entities have been underlined and the cases of a many to many relationship have been identified. For each entity, you should identify the necessary attributes required to describe the entity in a meaningful way. Next, identify the relationships between the entities and develop an entity relationship diagram to illustrate these relationships. Remember, you cannot have any multiple relationships, so think of where you can use a bridge table.

Draw your Relationship Diagram using MS Visio as your design tool. Be sure your diagram contains entities, attributes, and their relationships using Crow’s foot notation.

· Customers can place many orders , but each order can only be placed by one and only one customer. To be classified as a customer , there must be a related order and an order is valid only if related to a customer .

· Each order can consist of more than one book , and a book can appear on more than one order (this is a M:N relationship and not allowed). An order must contain at least one book to be valid. A particular book does not have to be part of an order to be a book.

· Books can have more than one author and an author can write more than one book (this is a M:N relationship and not allowed). Likewise, a book must have at least one author to be a book. An author must have written at least one of the books listed to be a valid author.

· A book can only have one an only one publisher , but a publisher can publish more than one book . To be listed, a publisher must have published at least one of the books listed.

When finished, your Relationship Diagram will probably contain seven entities (five major entities and two bridge tables). Be sure you have identified all of the relationships and have satisfied a resolution to all of the scenarios presented above.

Turn the following in for a grade:

a. This lab sheet with your name on it

b. Your complete diagram pasted into this document or as a separate document.

c. Add this assignment to the zip file containing all assignments for this week.