Lab For LU10 – Data Analysis and Data Modeling in Visio

Overview

In this lab, we will learn to map and then draw the Logical models with Microsoft Visio using the ERD’s we created in the previous lab..

Learning Objectives

Upon completion of this learning unit you should be able to:

§  Understand the concept of logical data modeling

§  Construct Relational Model Diagrams

§  Demonstrate how to map ideas from the conceptual model into the logical model.

§  Develop entity relationships and define various types of attributes

Lab Goals

Our lab goals are to:

  1. Learn to build Logical models in Microsoft Visio.
  2. Create the 4 Logical models from the ERD you drew in the previous lab.

You will accomplish this by drawing the 4 diagrams from the previous lab as logical models. Draw each logical model in its own Visio document and hand in all 4 diagrams by FTPing them to your account space on the ISTSTUDENTS server. To submit in the learning unit assessment, paste the URL to the Visio file in the text box of the LUA.

What you will need to begin

1.  A copy of Microsoft Visio 2003 (or higher)

2.  Access to your FTP account on ISTSTUDENTS (same logon and password as you use for SQL Server)

3.  The Digital copies or printouts of your 4 conceptual models from last week.

4.  You might need this week’s reading and PowerPoint to figure out how to map the Conceptual Model into the logical Model.

Part 1: Using Microsoft Visio for Logical Modeling

Overview

This section will explain how to use Microsoft Visio to create Logical Models. Microsoft Visio has built-in database tools for creating logical data models so this should be more comfortable than creating conceptual models. The following is a Demo of how to create a logical model:

Step 1: Create New Document

The first step is to create a new document.

From the Visio Menu, choose: File à New à Database à Database Model Diagram

This will create your blank Visio page, and load the default database toolset.

Step 2: Setting up Visio for Logical Modeling

  1. From the menu, choose Database à Options à Driver and select Microsoft SQL Server then click Ok.
  2. From the menu, choose Database à Options à Document, and be sure to set-up the settings as follows:
  3. Then when you’re done, click Ok.

Step 3: Add Tables and Columns

Drag and drop an entity onto the page. You should see a database properties window below. Fill it out as follows:

Next add columns and set data types as follows:

You want your table to look like this:

Now try to add another entity, and set it up like this:

You’re ready to try a FK relationship.

Step 4: Adding foreign keys

Use the relationship tool to add a foreign key. To do this, drag and drop the line on the page and then connect the arrow head ß to the primary key side (i.e. the one side of the relationship) and the tail to the foreign key side (i.e. the many side of the relationship). For example, since a department employs many employees, the arrow should point towards the departments table like this:

Notice how when you connected the arrow to both entities the foreign key was automatically added to the employees table. If you need to make employees “existence dependent / weak” then make sure to set the department_id to required:

Next click on the relationship line, and you will see the following:

This dialog shows that you can change the foreign key around as you wish by selecting the columns and clicking ß disconnect à and ß associate à

Step 5: Adding check constraints, defaults, and unique constraints

To add a check constraint:

  1. Click on the employees table.
  2. Select check from the database properties window.
  3. Click the Add button.
  4. In the code editor window, under Body, type
  5. In the code editor window, under Properties type
  6. If you’re successful you should see the following in the database properties window:

To add a Unique Constraint:

  1. Click on the employees table.
  2. Select Indexes from the database properties window.
  3. Click the New… button.
  4. In the Create Index dialog, enter:
    and click Ok.
  5. In the Properties window, select Unique constraint only for the index type, and add employee_ssn to the indexed columns like this:

To add a Default Value:

  1. Click on the employees table.
  2. Select Columns from the database properties window.
  3. Click on the employee_hiredate column row, and click the Edit… button.
  4. In the column properties page on the Defintion tab. Enter getdate() and then select is an expression or function call.
    When you’re ready click Ok.

Summary

This concludes how to implement a logical model in visio. When you’re done you should have something like this:

If you would like to generate a data-dictionary from this logical model, then do the following:

To Generate a Data Dictionary from this Logical Model:

  1. From the menu, choose Database à Report
  2. From the New Report Wizard, choose a Table Report and click Finish.
  3. Change the pre-defined report to Combined Report this shows the most information.
  4. Print, Preview, or Export the report to RTF to edit further.

Part 4: Creating the 4 diagrams from class exercise & handing it in.

Take the 4 diagrams from class you drew last week and map and draw them as logical models.

·  One document per diagram. (4 Visio Documents total) Name each file one with the appropriate number at the end lu10-1.vsd for example.

·  Upload the document to your space on ISTSTUDENTS using FTP

·  Verify you can access the document over HTTP:
http://ist-s-students.syr.edu/yourlogon/ lu10-1.vsd

·  Once you are done paste the url in the textbox for the learning unit assessment to turn in the lab.

4 / 7