Download "Creating a Crow's Foot ER Diagram in Visio: Defining COURSE Entity" and more Study notes Computer Science in PDF only on Docsity!
D ESIGNING D ATABASES WITH V ISIO P ROFESSIONAL :
A T UTORIAL
Microsoft Visio Professional is a powerful database design and
modeling tool. The Visio software has so many features that we
can’t possibly demonstrate all of them in this short tutorial.
However, we will show you how to:
❖ Start Visio Professional.
❖ Select the Crow’s Foot entity relationship diagram (ERD) option.
❖ Create the entities and define their components.
❖ Create the relationships between the entities and define the nature of those relationships.
❖ Edit the Crow’s Foot ERDs.
❖ Insert text into the design grid and format the text.
Α
A P P E N D I X
A
A p p e n d i x A
P R E V I E W
Once you have learned how to create a Visio Crow’s Foot ERD, you will be sufficiently familiar with
the basic Visio Professional software features to experiment on your own with other modeling and
diagramming options. We will also show you how to insert text into the Visio diagram to document
features you consider especially important or to simply provide an explanation of some segment of
the ERD.
With the Database selection shown in Figure A.1, move the cursor over the Database Model Diagram object. Note that your selection results in a square outline placed around the object. Also note that the cursor changes to a hand with a pointing finger, as shown in Figure A.2. In addition, you will see the Database Modeling Template description in the lower-left corner of your screen.
FIGURE A.2 The Database Model Object Selection
Click the Database Model Diagram selection shown in Figure A.2 to produce the screen shown in Figure A.3. Because we prefer a larger grid than the one shown in Figure A.3, we’ll start by clicking the size selection (zoom) list box located on the button bar at the top of the screen. Click the down arrow to generate the list of size options, shown as percentages. As you can tell in Figure A.3, we have selected the 100% option. When you click the 100% selection, the grid expands to fill the screen.
D E S I G N I N G D A T A B A S E S W I T H V I S I O P R O F E S S I O N A L : A T U T O R I A L 5
FIGURE A.3 The Drawing Board Size Option
By selecting the Visio Professional database option and its drawing board, you have completed the preliminary work required to create ERDs. You are now ready to draw the ERDs on the drawing board. We will use the Crow’s Foot option to show you how all the ERDs in the text were created.
6 A P P E N D I X A
A.2 SETTING THE STAGE FOR CREATING A CROW’S FOOT ERD
To select the Crow’s Foot option, select the Database, Options, Document… sequence shown in Figure A.4. (Note that the drawing grid has expanded in response to the 100% selection shown in Figure A.3.)
Move the cursor to the Relationship tab in Figure A.5 and click to select it to produce the screen shown in Figure A.6. Make sure that there is a check mark in the check box next to the Crow’s feet option to indicate that this option was selected. If there is no check mark, click this check box to select this option. (If a check box is “grayed out,” it cannot be changed. For example, at this point the check boxes under the Show verb phrase option cannot be changed, because the option has not been selected.)
FIGURE A.6 The Database Document Options, Relationship Tab
Examine Figure A.6, and note that we have not (yet) indicated that the relationship name is to be displayed; therefore, nor have we indicated how the names are to be displayed. We will return to this dialog box later to show you the effect of these selections and to demonstrate that you can edit the displays when you are working on them.
Next, select the Table tab in the Database Document Options dialog box, as shown in Figure A.7. Make sure that the check boxes are marked as shown here.
8 A P P E N D I X A
FIGURE A.7 The Database Document Options, Table Tab
Click the OK button shown in Figure A.7 to begin creating Crow’s Foot ERDs.
A.2.1 The Business Rules
To illustrate the development of the Visio Professional’s Crow’s ERD, we will create a simple design based on the following business rules:
- A course can generate many classes.
- Each class is generated by a course.
- A course may or may not generate a class.
Note that we have defined a class as a section of a course. This reflects the real world’s use of the labels class and course. Students have a class schedule, rather than a section schedule. The catalog that lists all the courses offered by a department is called a course catalog. Some courses are not taught each semester, so they may not generate a class during any given semester. In fact, some courses may be taught only when there is sufficient stu- dent demand.
D E S I G N I N G D A T A B A S E S W I T H V I S I O P R O F E S S I O N A L : A T U T O R I A L 9
A.3.1 The Database Properties Window
If the Table1 object has been selected as shown in Figure A.8, you will see the default Database Properties window at the bottom of the screen. (We will show you later in this section that this window’s location and for- mat may be changed to become the new default. However, we will start by using the standard default window shown in Figure A.8.)
As you examine the Database Properties window in Figure A.8, note the selection of the Definition option in the Categories: listing. (To select any option in the list, click it. The selection is indicated by the arrow to the left of the option. In this case, the arrow appears next to the Definition option.) At this point, the default Table1 label shows up in the Physical name: slot.
A.3.2 Creating the Default Database Properties Window
Depending on how you configured the Visio Professional software and/or on what operating system you use, you may not see the Database Properties window shown in Figure A.8. If your screen does not show a default Database Properties window, right-click the Table1 object in the grid to generate the Database Properties… option shown in Figure A.9.
FIGURE A.9 Selecting the Database Properties Option
Click the Database Properties… option shown in Figure A.9, and a Database Properties window appears somewhere on the screen. Figure A.10 shows you a typical result. In this example, the Database Properties window is located on the grid, next to the Table1 object. We will show you how to change the window’s loca- tion and format.
D E S I G N I N G D A T A B A S E S W I T H V I S I O P R O F E S S I O N A L : A T U T O R I A L 11
FIGURE A.10 The Moved Database Properties Window
A.3.3 Sizing the Database Properties Window You can size the Database Properties window as you would size any Windows object. For example, note that we have placed the cursor on the right margin (see Figure A.11) to change the cursor shape to a double-sided arrow in preparation for widening the window by dragging its right limit.
FIGURE A.11 Sizing the Database Properties Window
12 A P P E N D I X A
FIGURE A.13 Type the Entity’s Physical Name
When you have finished typing the COURSE label in the Physical name: slot as shown in Figure A.13, note that the conceptual table in the grid automatically inherits the COURSE label. You are now ready to start defin- ing the table columns.
14 A P P E N D I X A
A.3.6 Defining the Entity Attributes (Columns)
Each table column represents one of the characteristics (attributes or fields) of the entity. For example, if the COURSE entity, represented by the COURSE table, is described by the course code, the course description, and the course credits, you can expect to define three columns in the COURSE table. Table A.1 gives you a preview of the expected COURSE table structure. (We have also entered a few sample records to give you an idea of the COURSE table contents.)
To define the columns of the COURSE table, you must assign column names and characteristics. The first column in the COURSE table will be the CRS_CODE, which serves as the table’s primary key (PK). Because typical course code entries might be values such as CIS-456 or ACCT-234, each data entry involves a character string. In structured query language (SQL) terms, the CRS_CODE data are best defined as CHAR() data. Figure A. shows you how the CRS_CODE name and data characteristics were specified.
TABLE A.1 Some Sample COURSE Records
CRS_CODE CRS_TITLE CRS_DESCRIPTION CRS_CREDITS ACCT-345 Managerial Accounting Accounting as a management tool. 3 Prerequisites: Junior standing and ACCT 234 and 245. CIS-456 Database Systems Design Creation of conceptual models, logical 4 models, and design implementation. Includes basic database applications development and the role of the database administrator. Prerequisites: Senior standing and at least 12 credit hours in computer information systems, including CIS- and CIS-345. ECON-101 Introduction to Economics An introduction to economic history and 3 basic economic principles. Not available for credit to economics and finance majors.
D E S I G N I N G D A T A B A S E S W I T H V I S I O P R O F E S S I O N A L : A T U T O R I A L 15
When you have completed these steps, you will see the screen depicted in Figure A.14. Before we enter the remaining attribute names and characteristics, let’s first enlarge the Database Properties window by dragging its upper limit (see Figure A.15) to increase the desired space. This action will let us see all of the remaining attributes in the COURSE table. Now place the cursor on the second Columns line and get ready to enter the remaining attributes.
FIGURE A.15 Drag the Database Properties Box Limit to Show More Columns
The cursor changes shape when you place it on the database properties window edge
D E S I G N I N G D A T A B A S E S W I T H V I S I O P R O F E S S I O N A L : A T U T O R I A L 17
We are now ready to make the entries for the second COURSE attribute. Name this attribute CRS_TITLE. Typical entries are Database Design and Implementation or Intermediate Accounting. (Check the sample entries in Table A.1.) Therefore, the CRS_TITLE is a character field. Similarly, enter the CRS_DESCRIPTION entries. The course description is required, but it is not a PK. The CRS_CREDITS entries are numeric and they are required; they will be used at some point to help compute grade point averages for the students taking a sec- tion of this course. When the appropriate entries are made, the screen will look like Figure A.16. (Only a portion of the screen is shown to save space.) Note that the attribute names become boldfaced when the Req’d (required) option is checked for the Column property.) Selecting this option means that the end user will be required to make a value entry for the checked attribute—after the design has been implemented—when the table is opened for data entry.
FIGURE A.16 Enter the Remaining Columns
18 A P P E N D I X A
A.4 SAVING AND OPENING THE VISIO ERD
Because we don’t want to lose this first Visio Professional ERD segment, let’s save it in an appropriate folder. Use the File, Save As option to select the folder location and the filename, as shown in Figures A.17 and A.18.
FIGURE A.17 Select the Save As Option to Save the File
We can now go ahead and close the file—and, of course, make a backup copy! The next time we want to use the file, after we start Visio Professional, we use the standard Windows File, Open option to retrieve and open the file. In fact, that’s how we generated the screen shown in Figure A.20.
FIGURE A.20 Open the Previously Saved File
Note that the just-opened file shown in Figure A.20 does not show any entity properties. If you want to see this entity’s properties, right-click the COURSE table and select Database Properties to display its Database Properties window again.
You are now ready to define the CLASS entity, using the same techniques we used to create the COURSE entity. When you are done, the screen will look like Figure A.21.
FIGURE A.21 Adding the CLASS Entity
20 A P P E N D I X A
N O T E
The properties box shown in Figure A.19 was generated in Windows XP. If you use another Windows version, such as Windows 98, your Properties window will differ from the one in Figure A.19. However, the procedure for generating and using the Properties window is the same.
D E S I G N I N G D A T A B A S E S W I T H V I S I O P R O F E S S I O N A L : A T U T O R I A L 21
A.5 DEFINING RELATIONSHIPS
As you examine Figure A.21, note that we have not defined a foreign key (FK) in CLASS to relate CLASS to COURSE. Instead, we will let Visio Professional define the FK field when we specify the relationship between these two entities. Do not enter your own FK fields! (Visio Professional tells you what the relationship option will do for you—read the relationship text in Figure A.22).
FIGURE A.22 Select the Relationship Object
To create a relationship between the entities, first click the Relationship object shown in Figure A.22, drag it to the grid, and then drop it between the COURSE and CLASS entities to produce the results shown in Figure A.23.
FIGURE A.23 Drag and Drop the Relationship Object
As you examine Figure A.23, note that dropping the Relationship object on the grid produces the relationship line. Further note that the symbols at the two ends of the relationship line reflect default cardinalities of (1,1) and (0,N). Finally, remember that the relationship to be established between COURSE and CLASS reflects the busi- ness rule “One COURSE may generate many CLASSes.” Therefore, the COURSE represents the “one” side of the relationship and the CLASS represents the “many” side of the relationship.
Attach the “1” side of the relationship line to the COURSE entity by dragging the “1” end of the relationship line to the COURSE entity, as shown in Figure A.24. Note—and this is very important—that the relationship is not attached until the COURSE table is outlined in red. (You may have to drag the relationship line’s end all the way to the inside of the table before the red outline shows up.) When you release the relation- ship line, its attachment is verified by the red square on the entity (table) perimeter.
