Ilab Instructions Essay
Lab 1 of 7: Introduction to MS Visio and MS Access Scenario/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: . Relying on detailed instructions, create a simple conceptual model for a two-table database using MS Visio. 2. Without the detailed instructions, use experience gained in creating the first model to construct a similar conceptual model 3. Download an existing Microsoft Access database file; open the database, then find and identify different database objects. A. Deliverables: Section| Deliverable| Points| Part B, Step 4| YourName_Lab1. vsd (Visio Diagram)| 28| Part C, Step 6| YourName_Lab1C_Questions. docx| 12| B. Lab Steps: Preparation: 1. Get the Database from DocSharing: a. Download the Lab1_Nwind. ccdb file from your course “DocSharing” panel (Labs view) and save the file to your local drive. b. Download the Lab1C_Questions. docx from your course “DocSharing” and save the file to your local drive. 2. Using Citrix for MS Visio and/or MS Access c. If you are using the Citrix remote lab, follow the login instructions located in the iLab tab in Course Home. d. 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. Lab: Part A: Become Familiar with MS Visio| Step 1: Locate the Microsoft Office 2007 Applications| a.
If you are using Citrix, click on the Microsoft Office 2007 Applications folder b. If you are using Visio on a local computer, select Microsoft Office 2007 from your Program Menu | Step 2: Start Visio 2007| a. Click on Microsoft Visio 2007, and Microsoft Visio should open like the following screen. Figure 1- a b. Click the “OK” button on the Privacy Options dialog box to enable online help and to close the dialog. You should then see the following screen:Figure b Figure 1- b c. From the Templates Categories, click to select the “Software and Database” category. See next screen capture. Figure c Figure 1- c d. Figure 1- d
Under Featured Templates, notice that “Database Model Diagram” is selected. Double click on the diagram inside the orange box to select this template set, or click the “Create” button on the right side of the screen. The Visio Database Model Diagram template opens as follows:| 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 menu, select Options. Figure 2- a b. Select the Document option to display the following dialog box:Figure 2- b c.
Make sure the options selected in the General tab match the selections in the above image. Options in the Table tab should match the selections in the following image:Figure 2- cNotice the options selected:Figure 2- d d. Options selected in the Relationship tab should match the following image:Figure 2- e e. Click the “OK” button to close the dialog box. Then use the Zoom tool to increase the page size to 75% as shown below. Figure 2- f | 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. . 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 3- a 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. Figure 3- b c.
Click to select Columns in the Catgories list in the Properties pane, and enter the entity attributes (columns). d. Enter the Physical Name for the Customer Identification Number (CustID). The data type will automatically show the default data type, CHAR(10). Designate CustID as the primary key. 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)Your Visio diagram should match the image below:Figure 3- c e.
Create the second entity in the same manner, beginning with dragging and dropping a second entity shape from the Shapes pane to the worksheet. Name the entity Orders. Figure 3- d f. Add the following attributes to the Orders entity:OrderNumber (Order Number) designated as primary keyOrderDate (Order Date)OrderQty (Order Quantity)OrderStatus (Order Status)Your Visio ERD should now look like the one shown below:Figure 3- e g. 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 green 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 change from green to red and appear at the border of the customer entity. If it does not turn red, it is not properly connected. Try it again! Figure 3- f h. Notice in the image 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:Figure 3- g i. Connect the other end of the relationship (the one with the circle) to the Orders entity in the same way. Notice that now both connectors show red when the relationship is selected. Other changes also automatically occur to the diagram. 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 the example below:Figure 3- h | Step 5: Modify the Database Model| The diagram is good, but could use a few improvements. The diagram may be modified easily. 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. 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 the diagram below:Figure 4- a 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. Go to the Database menu, Options, Document, and select the Relationships tab. Select Show Verb Phrase, Forward Text. Figure 4- b d. Click the OK button to close the dialog box. Your relationships will now show the verb phrase has. 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- c | 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 build familiarity with the Visio program database options. Save your diagram as YourName Lab1A. 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 New Page. c. When the Page Setup dialog box opens, name the page Lab1 Part B. d. Double click on the page tab and rename it to Lab1 Part B also. e. | Step 2: Create a New ERD| 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. SupplierFax l.
SupplierHomePage 2. Entity for Products with the following attributes: m. ProductID (Primary Key) n. ProductName o. ProductPrice p. ProductDescriptionThe relationship is between Supplier and Products. In this case, the Supplier Provides 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 as YourName_Lab1. | End of Part B| Part C: Navigating MS Access 2007| 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. Follow the instructions for uploading files to Citrix, located in the iLab tab in Course Home. c. Download the Lab1C_Questions file from DocSharing. Save the file to your local drive as YourName_Lab1C_Questions. Open the file so that you can provide the answers as you go through the steps in Part C. | Step 1: Locate MS Office 2007 Applications| a. If you are using Citrix, click on the Microsoft Office 2007 Applications folder. b. If you are using Visio on a local computer, select Microsoft Office 2007 from your Program Menu. Step 2: Start MS Access 2007| d. Click on Microsoft Access 2007, and Microsoft Access should open like the following screen. | Step 3: Open Lab1_Nwind. accdb database file| a. Click the “Office” buttonOffice Button b. Navigate to locate the Lab1_Nwind. accdb file. c. Click to open the database. You should see the following screen:| Step 4: View the Tables| a. Click the OK button to close the dialog box. 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. | Step 5: Examine the Field Names| a. 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. 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 Lab1C_Questions document. c. Simple Filter of Data. Click the Country down arrow. Click on the Select All option to deselect the list of countries. Now scroll down the list and click on USA so that only USA is 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. Close the Suppliers table| Step 6: Relationships| a.
Click the Database Tools ribbon option to open the database tools ribbon. 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. . Click Queries from the All Access Objects list to display the list of queries. c. Open the Current Product List 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 CSubmit these files to the Week 1 iLab Dropbox. | END OF LAB|