Description of this paper





Question;Lab 1 of 7: Introduction to MS Visio and AccessNote!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 OVERVIEWScenario and SummaryYou 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.DeliverablesPart B: Step 4: YourName_Lab1.vsd (Visio Diagram)Part C: Step 6: YourName_Lab1C_Questions.docxiLAB STEPSPage 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


Paper#36629 | Written in 18-Jul-2015

Price : $28