Information IST352 Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
Building Data Models using Microsoft Visio
Building the data model is arguably the most important aspect of good database design. The data
model is used as a vehicle that allows the Systems Analyst to experiment with the database
structure before the actual database is actually built. This process is known as data modeling.
With database tools becoming relatively easy to use, this data modeling step is often skipped
resulting in poorly designed databases; therefore, making databases that perform poorly, difficult
to maintain and difficult to store and retrieve data.
During the data modeling process, the Systems Analyst collects the appropriate attributes,
determines their data names and datatypes, and then organizes them into logical entities. The
analyst can then use data modeling tools, like Microsoft Visio, to create a data model and to
display the model in a way that it can be communicated and understood by the end user.
In this lab exercise, we will explore Visios basic features for building a data model using EntityRelationship Diagrams or ERDs. Visio is a powerful modeling tool that allows the analyst the
flexibility to build and revise database models using a variety of database notations, as well as,
saving the model in order to build the SQL for creating the physical database tables.
Materials needed to do this Lab
Microsoft Visio
Learning Objectives
The student will learn to:
Describe database terminology: data model, ERD, crows foot notation, cardinality, oneto-many relationship, many-to-many relationships, attributes and associative (or
composite or bridge entities)
Identify and interpret business rules
Build data models
Use the basic functions and features of a database modeling tool, Microsoft Visio
Lab Setup Establishing Visio Parameters
Microsoft Visio has an extensive library of functions and features that initially seem
overwhelming. This library contains a number of Shapes Palettes that make it easy for
Information Technology professionals to create data models and represent them as Entity
Relationship Diagrams. To make it easier to build your ERDs set your parameters as follows:
1. Launch Visio
Select Visio by clicking on the blue and white Visio icon or selecting Visio from the
Start Programs Microsoft Office Visio. Sometimes you may find Visio as a
separate implementation and not part of Microsoft Office.
1
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
2. Open up a new Visio Document
To create an Entity Relationship Diagram select: File New Software and Database
Database Model Diagram. You can use either the Metrics or US units. Visio will
create an Entity Relationship Shapes Palette on the left side, a work area in the upper
middle and a Database Properties area in the lower portion of your screen. You should
see a screen that looks like this:
Drag these
ERD
symbols onto
your work
area.
2
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
3. Increase your Modeling Work Area
In order to better read the names of the objects that you assign to your database model,
increase your work area size by going to the View tab and using the Zoom Icon.
Increase your
work area by
using the
Zoom icon in
the View Tab
3
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
4. Snap and Glue
This feature increases your ability to attach a relationship connector to an entity or table
object. This is where we tell Visio to connect to any surface of the symbols we want
connected. To keep your relationship lines easy to read you need to reset the default Snap
& Glue options: Hit Alt+ F9 on your keyboard OR Select View-Visual Aids.
Click on the
small arrow at
the bottomright near
Visual Aids
Set your options to look like the following dialog box, and then click OK.
4
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
5. Crows Foot Notation
To set the Crows foot notion as the default for your relationship connectors: Database
Display Options Relationships Crows feet Defaults Set As OK.
You should see a screen that looks like this:
Set Crows
feet notation
as the default
You are now ready to build your Entity Relationship Diagram and assign your attributes to the
appropriate entities to it using the business rules below.
5
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
Building Your Data Model
BuyRight is a large retail enterprise that has been successful in marketing low-cost imported
goods to consumers through their chain of 6,000 stores. BuyRight currently sells its product
through their stores but wants to capture a piece of the global market by opening up an online
store. The management team at BuyRight has decided that a new database system would be an
improvement to their existing record-keeping system.
As BuyRights systems Analyst, you are hired to create a database design that will improve the
record-keeping of BuyRights business data specific to their new online store. You decide to
build a logical database model using an ERD from the following business rules and data that you
have collected from interviews. Your plan is to use your ERD as a communication tool to
indicate to the BuyRight management team the data you plan to include in their new database.
Business Rules
1. An employee can have only one title. A title can be shared by many employees.
2. A customer can be served by one employee. An employee can service many customers.
3. An employee can process many orders. A order can be processed by one employee
4. A customer can place many orders.
5. An order can consist of many products. A product can appear on an order only once.
6. A product can be on many orders. If product quantity on hand is zero set the back order
status to B for back-ordered.
7. A product can be supplied by many vendors. A vendor can supply many products.
Data
Employee: employee ID, name, SSN, address, home and cell phone numbers, salary, hire
date, birth date
Employee Title: employee title ID, title short name, title description
Customer: customer ID, name, address, home phone number, membership date, status
Order: order ID, order date, type, payment order total amount (sum of all the total product
amounts for a given order), order status
OrderProduct: order ID, line number, product ID, quantity, product unit price, total product
amount (quantity multiplied by unit price), back order status
Product: product ID, product name, product type, quantity on hand, product unit cost,
product unit price, inventory value (quantity on hand multiplied by product unit cost)
Vendor: vendor ID, vendor name, address, phone, preferred vendor status, quantity supplied,
vendor product id
6
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
Design Considerations
Assign good attribute names to the atomic level.
Establish meaningful attribute datatypes based on what you believe to be logical for
that field. For example: Order Status could be: pending, shipped, backordered,
picked, billed, or closed. So a datatype of CHAR, TEXT or VARCHAR with a length
of 12 would be considered appropriate here.
Attributes that record money amounts may need to be in dollars and cents.
The foreign keys have not been identified in the above data, so be sure to include the
foreign keys that establish entity relationships.
Make sure that you address any many-to-many relationships think associative
1. Creating Entities (eventually these will be tables)
Drag your entities from the Shapes Palette and place them in your work area. You will
notice that Visio gives each entity a default name of Table1, Table2, Table3, etc. for as
many entities that you include in your ERD. You will need one entity for each table in
your database design.
It is a good idea to rename your tables by clicking on each entity and using the Database
Properties section. Select Definition then type in your Physical name. You will notice
that Visio will use your physical name as the conceptual name as the default. This feature
is for analysts that wish to give separate physical and conceptual names. In many large IT
shops there are separate naming conventions for physical names and conceptual names.
Physical names are the actual names of the database tables. For example, Systems
Analysts might use a conceptual name of Customer; but use a physical database table
name of CustomerMaster.
2. Creating Columns
After you have successfully named your table, enter in each column (attribute) for that
table. You do this by selecting Columns. You will see a grid that should look familiar.
You will notice that this portion of the screen looks like Microsoft Access Table Design
View. Here you enter the attribute name, select the appropriate datatype from the
dropdown list and identify if the attribute is a required field and identify if the attribute is
a primary key. As you enter your attributes you will notice that the primary keys have a
unique place in your entity and each column is represented in the entity below the
primary key. Helpful hint: You do not need to add the foreign keys to your table. Visio
will do this for you when you establish the relationships among tables in step 3.
3. Establishing Relationships
Once all of your attributes have been entered, you will need to establish the relationships
among the various entities. To do this you need to drag the Relationships connector from
the Shapes Palette and place it on the work area. Place one end of the Relations connector
line into one entity until the green end of the connector turns red. Repeat the process with
the other end. You will notice that the entity with a foreign key attribute is identified with
a FK and placed to the left of the attribute. You will also notice that the foreign key is
given the same name as the primary key from the associated table. You need to rename
it to be consistent with the other names in that table. Your screen should look like
this:
7
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
Important, you
need to rename the
foreign key to be
consistent with
your naming
conventions within
your table
You can change the cardinality of your relationships by clicking on the relationship line
and using the Miscellaneous Database Properties.
In the cardinality
column you can
select the number
of entity
occurrences you
need to represent
on the many side of
your relationship
The Relationship type
determines if the existence
of the entities are dependent
on each other. Identifying
means existence-dependent,
non-identifying means the
entities dont need each
other to exist
8
IST352 Information Analysis of Organizational Systems
Data Modeling
Lab 3
Fall 2011
Name ________________________________________ (20 points)
1. Give two reasons why it is a good idea to develop a data model before you start
building your database (2)
a. ________________________________________________________________
________________________________________________________________
b. ________________________________________________________________
________________________________________________________________
2. From the provided business rules and your revised Data Model give examples of the
following business rule types (see class notes.) (3)
a. Derivation: _______________________________________________________
b. Structural Assertion: _______________________________________________
c. Action Assertion: __________________________________________________
3. Model 1: Print and attach your original ERD Data Model created from page 6. (5)
4. Model 2: You have reviewed your original data model (above) with the users and
you learn that some requirements where missing. Change your model to reflect the
following new business rules. Print and attach your revised ERD Data Model. (10)
a. An employee can work in many departments. A department can have many
employees. A department can have a number, name and a department manager.
Managers want to know how much time (in hours to hundredths of an hour) each
employee spends working in each department.
b. An order can be handled by many employees. Managers want to know how much
time (in minutes) each employee spends processing an order.
c. A specific product can appear on an order more than once, negating the second
half of business rule 5 on page 6.
d. A product belongs to one product line. A product line can have many products. A
product line id, name and a product manager
9