Lab 1 of 7: Introduction to MS Visio and Access

Note!Submit your assignment to the Dropbox located on the silver tab at the top of this page.

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

Remember ThisConnect to the iLab here.

ILAB OVERVIEW

Scenario and Summary

You have been asked to create two conceptual database models using MS Visio Database Model Diagram Template. The purpose of this lab is to have you gain familiarity with the various modeling tools needed to create a conceptual model (entity relationship diagram) of a database. You will create two conceptual models.

You will then open an existing Access database to explore database objects, and to experiment with simple data manipulation using filters and sorts, and to begin elementary work with relationships.

Upon completing this lab, you will be able to the following.

• Relying on detailed instructions, create a simple conceptual model for a two-table database using MS Visio.
• Use experience gained in creating the first model to construct a similar conceptual model without the instructions.
• Download an existing Microsoft Access database file. Open the database, find and identify different database objects in this database.

Deliverables

Part B: Step 4: YourName_Lab1.vsd (Visio Diagram)

Part C: Step 6: YourName_Lab1C_Questions.docx

ILAB STEPS

Page 1 of 31

A. Lab # : BSBA BIS245A-1

B. Lab 1 of 7 : Introduction to MS Visio and MS Access

C. Lab Overview–Scenario/Summary

TCOs:

1. Given a business situation in which managers require information from a

database, determine, analyze and classify that information so that reports

can be designed to meet the requirements.

2. Given a situation containing entities, business rules, and data

requirements, create the conceptual model of the database using a

database modeling tool.

Scenario:

You have been asked to create two conceptual database models using MS Visio

Database Model Diagram Template. The purpose of this lab is to have you gain

familiarity with the various modeling tools needed to create a conceptual model

(entity relationship diagram) of a database. You will create two conceptual

models.

You will then open an existing Access database to explore database objects and

to experiment with simple data manipulation using filters and sorts and to begin

elementary work with relationships.

Upon completing this lab, you will be able to

1. relying on detailed instructions, create a simple conceptual model for a

two-table database using MS Visio;

2. use experience gained in creating the first model to construct a similar

conceptual model without the instructions; and

3. download an existing Microsoft Access database file. Open the database;

find and identify different database objects in this database.Page 2 of 31

D. Deliverables

Section Deliverable Points

Part B Step

4

YourName_Lab1.vsd (Visio Diagram)

Part C Step

6

YourName_Lab1C_Questions.docx

E. Lab Steps

Preparation

1. Get the Database from Doc Sharing:

a. Download Lab1_Nwind.accdb file from your course Doc Sharing

panel (Labs view), and Save the file to your local drive

b. Download Lab1C_Questions.docx from your course Doc Sharing

panel, and Save the file to your local drive.

2. Using Citrix for MS Visio and/or MS Access

a. If you are using the Citrix remote lab, follow the login instructions

located in the iLab tab in Course Home.

b. You will have to upload the Lab1_Nwind.accdb file to your Citrix

drive. Follow the instructions for uploading files to Citrix, located in

the iLab tab in Course Home.Page 3 of 31

Lab

Part A: Become Familiar with MS Visio

Step 1: Locate the Microsoft Office Applications

a. If you are using Citrix, click on the Microsoft Office Applications folder.

b. If you are using Visio on a local computer, select it from your Program list.

You may need to select Microsoft Office from your Program Menu, and then

select Visio.

Step 2: Start Visio

a. Click on Microsoft VISIO and Microsoft VISIO should open like the following

screen. Notice that the highlighted diagram template is the Database Model

and Diagram, in the recently used templates. You will not see this in your

templates as you have not yet used this tool. You will need to click on the

Software and Database category (circled in red below) in the Template

Categories area.

Figure 1- aPage 4 of 31

b. From the Templates Categories, click to select the Software and Database

category. See the next screen capture. The Database Model Diagram

template is selected.

Figure 1 Figure a-bPage 5 of 31

c. Under Choose a Template, double-click the Database Model Diagram to

select this template set, opening the template as follows: (An alternate

method is to select the Database Model Diagram Template, and then click

the Create button to open the new file.)

Figure 1- cPage 6 of 31

Step 3: Set Visio Database Options for Crow’s Foot Notation

a. Change VISIO settings to use Crow’s Foot notation for the model diagrams.

NOTE: This notation will most closely match the models used in your

textbook. From the Database ribbon, select Modeling Preferences.

Figure 3-aPage 7 of 31Page 8 of 31

b. Once you click on Modeling Preferences, the following dialog box should

appear with the Logical Diagram default settings as shown below. These

settings suit our purposes. If your settings do not match those shown,

correct them at this time. Do not close the dialog box.

c. In the Database Modeling Dialog box, click the Logical Misc tab. The default

settings work well, except for the Name Conflict Resolution selection.

Change this to Do Not Allow. Make sure that all remaining settings match

those shown below. Click the OK button at the bottom of the dialog box to

save these settings and click OK to close the Database Modeling Preferences

dialog box.

Figure 3- aPage 9 of 31

d. From the Database ribbon, select the Display Options to open the dialog box

shown below. The General tab should be selected, and the default options

set as shown below. If your settings are different, set them as shown. Do

not close the dialog box as changes may be needed on the remaining two

tabs in this dialog box.

Figure 3- bPage 10 of 31

e. Click to select the Table tab in the dialog box. If your settings do not match

those shown below, make the necessary changes.

Figure 3-dPage 11 of 31

f. Click to select the Relationship tab in the dialog box. The default settings are

different from those needed. Change your settings to match those shown

below.

Figure 3-ePage 12 of 31

g. Click the OK button to close the dialog box. Click to select the Home ribbon.

Using the Zoom Slider, on the lower right area of the application, to increase

the size of your page to at least 75%. This allows display of more detail in

your diagrams. See illustration below.

Figure 3-fPage 13 of 31

Step 4: Develop the Conceptual Model for a Two-Table Database

Now that all settings are correct, it’s time to model a simple database. The

database will include two entities.

a. To create the first entity, drag and drop the entity shape from the Shapes

window to the worksheet. Notice (see drawing below) that the entity shape

is selected showing the sizing handles, and that the properties for the object

are displayed in the Database Properties pane below the worksheet.

Figure 4- aPage 14 of 31

b. In the Database Properties pane, enter the Physical Name CUSTOMERS. The

conceptual name will also change because the Sync names when typing

check box is checked. Notice that the entity name in the entity shape on the

worksheet changes as well. See below.Page 15 of 31

c. Click to select Columns in the Catgories list in the Properties pane, and enter

the entity attributes (columns).

Enter the Physical Name, CustID for the Customer Identification Number .

The data type will automatically show the default data type, CHAR(10)

Designate CustID as the primary key by selecting the PK checkbox. Continue

to add the remaining columns as shown:

CustFirstName (Customer First Name)

CustLastName (Customer Last Name)

CustStreet (Customer Street)

CustCity (Customer City)

CustST (Customer State)

CustPostalCode (Customer Postal Code)

CustPhone (Customer Telephone)

Figure 4- bPage 16 of 31

Your VISIO diagram should match the illustration below

d. Create the second entity in the same manner beginning with draging and

dropping a second entity shape from the Shapes pane to the worksheet.

Name the entity Orders.

Figure 4- cPage 17 of 31

e. Add the following attributes to the Orders entity:

OrderNumber (Order Number) designated as primary key

OrderDate (Order Date)

OrderQty (Order Quantity)

OrderStatus (Order Status)

Your VISIO ERD should now look like the one shown below.

Figure 4- d

Figure 4- dPage 18 of 31

f. Now, define the relationship between the two entities. To do this, drag and

drop the relationship shape from the Shapes pane to the worksheet. The

relationship will have small boxes (connectors) at each end of the line

showing that the relationship is currently selected. To connect the two

entities, click and drag the connector with the two straight lines to the

middle of the Customers entity. When Customers shows a heavy red border,

drop the connector. It should then show a small red box inside the connector

at the border of the Customer entity. If the red box does not appear, it is

not properly connected. Try it again!

Figure 4- ePage 19 of 31

g. Notice in the drawing above that the connector does not actually move until

you drop the end of it into the middle of the entity. However, you should see

a hashed line from the relationship shape to the entity shape. Once you drop

the connector, your diagram should match the one below.Page 20 of 31

h. Connect the other end of the relationship (the one with the circle and “crows

foot”) to the Orders entity in the same way. Notice that now both connectors

show red squares when the relationship is selected. Other changes

automatically occur to the diagram also. Notice that the primary key from

the Customers entity now shows in the Orders entity. The FK1 denotes this

as a foreign key in the Orders entity. Also notice that both ends of the

relationship now have circles. See example below.

Figure 4- fPage 21 of 31

Step 5: Modify the Database Model

The diagram is good, but could use a few improvements. The diagram may be

easily modified.

a. First, change the Customers entity. Click on the entity to select it, and to

reopen the associated properties. Click the Required check boxes for each of

the attributes.

Notice that as each is changed to show required, the name in the entity

changes from regular to bold font.

Figure 4- hPage 22 of 31

b. Now select the Orders entity and do the same thing.

Notice when you change CustID in the Orders entity to required, the circle

on the Customer end of the relationship changes to a straight line. This will

be explained later in the course. See diagram below.

Changes may also be made to modify the settings for the document. For

instance, the relationship would be more easily understood if it were named.

c. Click on the relationship to select it, and then select Name from the

Categories in the Property pane. Because Customers place Orders, enter

Place in the Verb Phrase space. See below.

Figure 4- aPage 23 of 31

Step 6: Deliverable for Lab 1 Part A

The diagram is now complete. As you move through the course, you will create

more sophisticated diagrams. However, the purpose of this lab is to just build

familiarity with the VISIO program database options.

Save your diagram as YourName_Lab1A.vsd. We will use this file for Part B

End of Part A

Part B: Create a New Conceptual Model Diagram (ERD)

Step 1: Open the Visio Diagram File

a. Open the same VISIO file created in the first part of the lab.

b. Go to the Insert menu, and insert a Blank Page.

c. Double click on the page tab and rename it to Lab1 Part B also.

Step 2: Create a New ERD

Figure 4- bPage 24 of 31

Use the new page to deveop an ERD showing the following:

1. Entity for Suppliers with the following attributes:

a. SupplierID (Primary Key)

b. SupplierCompanyName

c. SupplierContactName

d. SupplierContactTitle

e. SupplierAddress

f. SupplierCity

g. SupplierRegion

h. SupplierPostalCode

i. SupplierCountry

j. SupplierPhone

k. SuppleirFax

l. SuppleirHomePage

2. Entity for Products with the following attributed:

a. ProductID (Primary Key)

b. ProductName

c. ProductPrice

d. ProductDescription

The relationship between Suppliers and Products. In this case, the Suppliers

Provide Products.

Step 3: Modify the Diagram

Modify the model by making all attributes of both entities required.

Step 4: Deliverable for Lab 1 Part B

Be sure you save your diagram before closing as YourName_Lab1.vsd.

End of Part B

Part C: Navigating MS Access

Preparation

a. Download the Lab1_Nwind.accdb file from Doc Sharing and save it to a local

drive

b. If you are using Citrix for MS Access, you will have to upload the

Lab1_Nwind.accdb file to your Citrix drive or allow Citrix access to your

system. Follow the instructions for uploading files to Citrix, located in the

iLab tab in Course Home.

c. Download the Lab1C_Questions.docx file from Doc Sharing. Save the file to Page 25 of 31

your local drive as YourName_Lab1C_Questions.docx. Open the file so you

can provide the answers as you go through the steps in Part C.

Step 1: Locate MS Office Applications

a. If you are using Citrix, click on Microsoft Office Applications folder.

b. If you are using Access on a local computer, select Microsoft Office from your

Program Menu.

Step 2: Start MS Access

d. Click on Microsoft Access, and Microsoft Access should open like the following

screen.

Step 3: Open Lab1_Nwind.accdb database file.

a. Click the Open button option on the File menu.Page 26 of 31

b. Navigate to locate the Lab1_Nwind.accdb file.

c. Click to open the database. You should see the following screen. You will not

log in, so click the Close button on the Login form.Page 27 of 31

Step 4: View the Tables

a. Notice the All Access Objects list on the left of your screen. Click on Tables

to expand the list to show all tables in the Northwind Traders Database. From

the list of tables, double click on Suppliers to open the Suppliers table. See

below.

b. Notice that the entire table is not visible. Use the horizontal scroll bar to view

the remaining fields in the Suppliers table. Complete your response to

Question 1 in the Lab1C_Questions document.Page 28 of 31

c. Close the Supplier Table by clicking on the Close Object Button circled below.

Step 5: Examine the Field Names

a. Open the Customer Table and examine the field names. Notice that each has

a small down arrow at the right side of the field. These are used to filter the

table based on specified criteria. Scroll over, and click the filter arrow in the

City field. Your screen should appear as follows.Page 29 of 31

b. Simple Sort of Data. Click the Sort A to Z option at the top of the list of

options. Notice that all records in the table are now sorted in alphabetical

order by City. Answer Question 2 in the Lab1C_Questions document.

c. Simple Filter of Data. Click the State/Province down arrow. Click on the

Select All option to deselect the list of States. Now scroll down the list and

click on TN so that only Customers in Tennessee are selected. Click the OK

button to apply the filter. Answer Question 3 in the Lab1C_Questions

document.

d. Remove the filter by selecting the Select All option in the State/Province

down arrow menu. Close the Customers table, but do not close the database.

When prompted to save the Customers table select No.

Step 6: Relationships

a. Click to open the Database Tools ribbon. Page 30 of 31

b. Click the Relationships button. This allows you to view the database schema

made up of the tables with attributes and keys, and the relationships

between the tables. (See below.) Scroll bars can be used to view parts of the

diagram not on the screen. The key symbol indicates which fields act as

primary keys for the tables.

c. Based on the information in the diagram, answer Questions 4 and 5 in the

Lab1C_Questions document.

Step 7: Queries

a. Click the Close button on the Database Tools ribbon to close the

Relationships diagram.

b. Click Queries from the All Access Objects list to display the list of queries.

c. Open the Inventory on Hold query.

d. Answer Question 6 of Part 3 in the Lab1C_Questions document.

e. Close Access.

Step 8: Deliverables for Part C

Save your Lab1C_Questions document as YourName_Lab1C_Questions. Make

sure you have provided your answers.

End of Part C

Lab 1 Final Deliverables

a. YourName_Lab1.vsd (Visio Diagram) – from Lab 1 Part B

b. YourName_Lab1C_Questions.docx – from Lab 1 Part CPage 31 of 31

Submit these files to the Week 1 iLab Dropbox.

END OF LAB

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.