Creating an ERD using Microsoft VISIO

In this exercise, you’ll use VISIO to create the ERD shown here.

Open VISIO. Select Software and Database from the list of template categores (shown on the left). Select Database Model Diagram. (If you don’t see the option to select a drawing type, select File from the menu bar then select New … Software and Database from the list of templates. If you don’t every see Software and Database as a template option, you probably don’t have the correct version of Visio installed. Use a computer in the BC lab.)

Drag-n-drop an entity from the Shapes panel (on the left) into the drawing grid (on the right). Change the generic name initially provided by typing “Patient” for the Physical name and Conceptual Name in the Database Properties panel (lower right).

In the Database Properties panel, switch to the Columns category. Type the name of an column (an attribute). Type FNAME then click the Edit button to change the default fields size of 10 characters.

Select the Data Type tab and change the field length to 12 and switch from “fixed length” to “variable length.”

Enter the columns names shown here.

Modify the Patient_ID column data type as shown below. Reduce the “precision” of the field which give the total number of digits the field can have. Set the scale to zero, which means only integers will be stored. (The scale value determines how many digits to the right of the decimal place are allowed.)

Also, make the patient name fields required (see above) and make Patient_ID the primary key (PK).

Create a second entity called Appointment and create the columns as shown below. Designate all the columns as required and mark Appt_ID as the primary key.

Create a relationship between the PATIENT and APPOINTMENT entities. What is this relationship?

· Any one patient (Bill Jones, for example) may have no appointments but may have many.

· Any one appointment (#2191 at 4 p.m. on 5/21) must be for one patient and no more than one.

These statements show that there is a one-to-many relationship between PATIENT and APPOINTMENT. In VISIO the terminology for this relationship describes PATIENT as the “parent” table and APPOINTMENT as the “child” table. Creating an appointment record is dependent on being associated with a valid patient ID in the PATIENT table.

To create this relationship, click the Relations icon in the Shapes panel. Click inside PATIENT then drag-n-drop the relationship into APPOINTMENT. Note what happens: VISIO creates a foreign key in the APPPOINTMENT table. Having a common field (or fields) is what forms the “relationship” between two tables.

Your diagram should look like the example below.

The relationship doesn’t look what we’re used to seeing in ERDs. You can change how VISIO displays the relationship by modifying the Options in the DATABASE menu. Under Options select Document… (see below).

Check the settings shown below and click OK.

Now, the diagram should look like the example shown below—minimum and maximum cardinality are shown. The relationship shown matches the description of the relationship given earlier except the minimum number of patients that can be related to an appointment is one (i.e., you can have an appointment without specifying which patient the appointment is for).

To change the modality, click the relationship between the two entities. Select Miscellaneous in the Database Properties panel. Under Relationship type switch to Identifying then switch back to Non-identifying. Now, the minimum number of patients related to an appointment should be 1 instead of zero as shown below.

Create another entity call MEDICAL HISTORY. This time, however, uncheck Sync Names and make the physical name different from the conceptual name, as shown below.

Create the columns shown here. Edit the data types as shown, too. Don’t create a primary key yet.

Create a relationship between PATIENT and MEDICAL HISTORY, with PATIENT has the “parent.” Modify the relationship settings as shown below. The relatinoship between PATIENT and MEDICAL HISTORY is one-to-one and Patient_ID serves as the primary key and foreign key in MEDICAL HISTORY.

Save your ERD.

VISIO_Intro.doc

Page 5 of 5