[SOLVED] COMPLETE LAB VISIO DATABSE TASK (Due DEVRY)

Section

Deliverable

Points

Part B Step 4

YourName_Lab1.vsdx (Visio Diagram)

Part C Step 8

YourName_Lab1C_ScreenPrints.docx

Part A: Become Familiar With MS Visio

Step 1: Locate the Microsoft Office Applications

a.If you are using Cirtix, 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

• Click on Microsoft Visio and it should open like the following screen. Notice that the list of diagrams is shown. At the upper left-hand side, there are two tab options: featured and categories. Click on the categories section, as shown circled in red below.
• Select the database and software option from the categories listed, as seen below.
• From the template categories, click to select the software and database category. See the next screen capture. The database notation diagram template is selected.
• Under choose a template, double-click the database model diagram to select the template set, opening the template as follows. (An alternate method is to select the UML database notation model diagram template, and then click the create button to open the new file.)

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

• Change Visio setting 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 top menu bar, select connectors. From the menu bar on the left, select More Shapes. Select software and database from the first list. From the second list, select UML database notation. From the last Window, make sure that UML database notation is selected as well as Crow’s Foot notation. Please see the figure below.
• Click to select the view tab to see the view ribbon (toolbar). Select zoom from the list and select at least 75%. This allows display of more detail in your diagram. See the diagram below.

Step 4: 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.

• To create the first entity, drag and drop the entity shape from the shapes toolbox on the left to your open, empty diagram worksheet. Notice (See drawing below.) that the entity shape is selected showing the sizing handles. No properties are shown. This version of Visio uses GUI to create the table.
• Click on the new entity. There are four parts. The entire entity (green outline), the entity name (at the top), the attribute name with a key, which is the primary key, and remaining attribute names. Once you have the entity name box selected, double click entity name and enter CUSTOMERS. See below.
• Double click the key attribute name after you have selected the box and enter CustID. Continue entering the following information in attribute name boxes by double clicking each and typing in the name. There are only two listed, and you will need seven total. To add additional attribute names, right click the attribute name border until you see insert attribute before/after in the list of options. Add five additional attribute names, and complete this step by adding the following information.
• Create the second entity in the same manner beginning with dragging and dropping a second entity shape from the toolbox on the left. Name the entity ORDERS.
• Add the following attributes to the orders entity.
• Now define the relationship between the two entities. To do this, drag and drop the relationship shape from the toolbox on the left. To connect the two entities, first add another attribute (CustID) to the orders table. Next, click and drag the connector to the diagram surface. Click and drag the end with two lines and drop it on CustID in the customers table. A green circle should appear when the entity is connected. Pick up the other end of the connector (with Crow’s feet) and drag it to the attribute list, and connect it with a green circle to CustID on the orders table. After connecting one end to the customers table, if the unconnected end of the connector does not have a Crow’s feet, right click the relationship, select “Set End Symbol” from menu, and choose “1 or more.” See below.

CustFirstName

CustLastName

CustStreet

CustCity

CustST

CustPostalCode

CustPhone.

Your Visio diagram should match the figure below.

OrderNumber (as primary key)

OrderDate

OrderQty

OrderStatus

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

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. From here, you can click the bold, font color, and font size from the Home tab as you would in any other Microsoft program, and make changes such as bold, black font color, and 14 font size.

b.Select the orders entity and do the same formatting.

c.Select and right click the relationship line. Select edit text from the options in the dropdown menu. Type in the action that takes place between these two tables. Hint: Orders are placed. Results should look like the results below with font size and colors changed.

Step 6: Deliverable for Lab 1 Part A

The diagram is not 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 and its database options.

Save your diagram as YourName_Lab1A.vsdx.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 Part A Lab.

b.Rename the page 1 Lab Part A.

c.Go to the insert menu and insert a blank page.

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

Step 2: Create a New ERD

Use the new page to develop an ERD showing the following.

1.Entity for suppliers with the following attributes:

• SupplierID (Primary Key)
• SupplierCompanyName
• SupplierContactName
• SupplierContactTitle
• SupplierAddress
• SupplierCity
• SupplierRegion
• SupplierCountry
• SupplierPhone
• SupplierFax
• SupplierHomePage

2.Entity for products with the following attributes.

• ProductID (primary key)
• ProductName
• ProductPrice
• 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 related using a shared primary key in the related table.

Step 4: Deliverable for Lab 1 Part B

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

End Part B

Part C: Navigating MS Access

Preparation

a.Download the Lab1_Nwind.accdb file from the Week 1 Lab page, and save it to a local drive on your computer.

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 Lab area under the Introduction and Resources area.

c.Download the Lab1C_ScreenPrints.docx file from the Week 1 Lab page. Save the file to your local drive as YourName_Lab1C_ScreenPrints.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

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

Step 3: Open Lab1_Nwind.accdb database file.

Click the open button on the menu on the left as indicated with the red circle below.

a.Navigate to locate the Lab1_Nwind.accdb file on your local drive or in Citrix.

b.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.

Step 4: View the Tables

• Notice the all access objects list on the left side of your screen. Click on all tables to expand the list to show all tables in Northwind traders database. From the list of tables, double click on suppliers to open the suppliers table. See instructions for 2016 below.
• Close the Window for the starting form.
• Close the login dialog box, selecting the red X in the upper right corner.
• Select “Show All” under tables.
• 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.

Left click and drag the suppliers table to the workspace on the right.

.

Step 5: Examine the Field Names

• Open the customer table and examine the field names. Notice that each has a small down arrow at the right side of the field name at the top 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.

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 Lab1CQuestions document.

c.Simple filter of data: Click the state/province down arrow. Click on the select all option or 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 customer table by clicking the x on the customer tab. When prompted to save the customers table, select no.

Step 6: Relationships

a.Click to open the database tools ribbon (toolbar).

b.Click the relationships button. This allows you to view the database schema made up of the tables with attributes and keys and relationships between 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 tab to close the relationships diagram.

b.Click queries from the all access objects list on the left to display the list of queries available.

c.

d.Open the suppliers list query.

e. Select the view option in the upper left tool bar. Click once to see query design and take a screen print. Click the view option again and the query results return. Take a screen print of the query results. Add both screen prints to a word document for the Part C deliverable.

f.Close access.

Step 8: Deliverable for Part C

Save your Lab1C_Questions document as YourName_Lab1C_ScreenPrints. 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_ScreenPrints.docx – from the Lab 1 part C.

c.Submit these files.

END OF LAB