Lab # : bsba bis245a-1

A.   Student Name:                                                                                     

B.    Lab 1 Part C Questions:

1. Entities in the conceptual model eventually are converted to tables in the database, and the attributes convert to the fields within the tables. Given this information, list the attributes (fields) found in the supplier table.

1. With the Customers table sorted in alphabetical order by city, what is the name of the first customer to appear in the table?

Companie G, Xie Ming-Yang

1. Who are the Customers located in the Tennessee?

Company BB Raghav and Company M, Ludick

1. List the tables along with the primary key for each.

1. List the tables “related” to the Orders table.

1. Open query “Inventory on Hold” by double clicking that query.  What data is in this query (give the column names)?  What is the Quantity on Hold for Northwind Dried Plums?

 —————————————————————————————————————————————————–

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- a Page 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-b Page 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- c Page 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-a Page 7 of 31 Page 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- a Page 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- b Page 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-d Page 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-e Page 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-f Page 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- a Page 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- b Page 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- c Page 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- d Page 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- e Page 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- f Page 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- h Page 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- a Page 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- b Page 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 C Page 31 of 31 Submit these files to the Week 1 iLab Dropbox. END OF LAB