Must have Microsoft visio and access to complete assignment

Scenario You have been asked to extend the database you developed in Lab 2 to also include customer data, to populate the tables in the database with sample data, and to create some queries using this data to illustrate how the database can supply information that is useful to management. The purpose of this lab is to provide experience with modifying a database, entering data into tables, and creating simple queries. Using MS Visio, you will add a customers entity to the ERD you created in Lab 2. Based on the modified ERD, you will add the corresponding customers table to the MS Access database from Lab 2. You will then enter sample data into the tables in this database and create some basic queries to illustrate how data from the tables can be presented to satisfy managers’ information needs. Upon completing this lab, you will be able to 1. modify an existing MS Visio ERD to include new entities and relationships; 2. modify an existing MS Access database to include new entities and relationships; 3. enter data into tables in a MS Access database using datasheet view; and 4. create and run simple queries in MS Access. D. Deliverables Section Deliverable Points Part A Step 4 YourName_Lab3.vsdx (Visio diagram) 4 Part D Step 4 YourName_Lab3.accdb (Access database) (Combination of Parts B, C and D. 36 E. Lab Steps Preparation 1. Download the files Lab3.vsdx and Lab3.accdb from the Files section> Lab Files. 2. If you are using Citrix for MS Visio and/or MS Access, follow the login instructions located in the Lab tab in the Introduction and Resources area, and upload the Lab3.vsdx and Lab3.accdb files to your working folder on the Citrix server. (You can see a video about how to do this by clicking the link “Saving From Citrix” under Citrix Tutorials on the right side of the Lab page in the Introduction and Resources area.) Lab Part A: Modify a Visio ERD from Data Requirements and Business Rules Step 1: Launch Visio and open ERD. a. Open Microsoft Office, Visio application or b. If you are using Citrix, click on Microsoft Office Applications folder to start Visio. c. Open the Lab3.vsdx file. You should see the following ERD. Step 2: Add a Customers entity to the ERD. a. Drag an entity shape from the shapes window and drop it onto the diagram under the orders entity. b. Enter the physical name customers for the new entity. c. Enter the following attributes (columns) with the corresponding data types. CustID Short text (10) CustLastName Short text (25) CustFirstName Short text (25) CustStreet CustCity Short text (25) Short text (20) CustST Short text (2) CustPostalCode Short text (10) CustPhone Short text (10) d. Designate CustID as the primary key by selecting the PK checkbox. e. Click the required checkbox for each attribute to make all attributes required. Step 3: Define Relationship Between Customers and Orders. a. Drag a relationship shape from the shapes pane and drop it onto the diagram. Connect the one end of the relationship line (with two straight lines) to customers, and connect the many end of the relationship line (with the crow’s foot symbol) to orders. b. With the relationship line selected, select name in the categories list and change the verb phrase from has to place. c. Visio should have automatically added a CustID attribute to the orders entity as a foreign key (FK1). Select the orders entity and click the required checkbox for the CustID column. Your ERD should now look like this. Step 4: Deliverable for Lab 3 Part A Save your completed diagram as YourName_Lab3.vsdx. End of Part A Part B: Modify an Access database. Step 1: Launch MS Access and Open Database. 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. c. Click on Microsoft Access to launch the application. d. Click the open button on the file menu, navigate to the Lab3.accdb file, and click to open the database. e. If you see a yellow security warning across the top of the screen, just below the ribbon, click the enable content button to enable the database content. If you see a security warning dialog box asking if you want to make the file a trusted document, click yes. You should now see a window like the following. Step 2: Create Customers Table in Database. a. Select the create ribbon tab and click table design in the tables group. A new table will be created and opened in design view. b. Enter the fields and data types for the customers table you added to the Visio ERD in Part A. For each field, remember to set the field size and required properties in the field properties area in the lower half of the window. Also, remember to designate the CustID field as the primary key for the table. Your completed table should look like this. c. Click the save button (disk icon) in the upper left corner of the window to save the table. In the Save As dialog, enter customers as the table name and click OK. d. Click the X on the right side of the screen opposite the customers tab to close the table. Step 3: Add Foreign Key CustID to Orders Table. a. In the navigation pane on the left side of the window, right-click on the orders table and select design view. b. Scroll to the end of the list of fields and enter CustID in the first open row in the field name column (immediately below OrderShipZip). Set the data type to text. In the field properties pane, set the field size property to 10 and the required property to yes, as shown. c. Click the save button (disk icon) in the upper right of the window to save your changes to the table. d. Click the X on the right opposite the orders tab to close the table. Step 4: Establish Relationship Between Customers and Orders Tables. a. Select the database tools ribbon tab, and click on the relationships button. b. Click the show table button on the relationship tools/design ribbon tab. c. In the show table dialog, select the customers table and click add, then click close. d. Drag the CustID field from the customers table and drop it onto the CustID field in the orders table. When the edit relationships dialog appears, check the box for enforce referential integrity, and click create. The relationships window should now look like this. e. Click the close button in the relationships group on the relationship tools/design ribbon tab to close the relationships window. When prompted, click yes to save your changes to the relationships window. End of Part B Part C: Enter Data Into Tables. Step 1: Enter Data in Customers Table. a. In the navigation pane on the left side of the window, double-click the customers table to open it in datasheet view. b. Enter data for the five records shown below. (You can widen the columns by dragging the borders between the column headings, as in Excel.) c. Click the X on the right opposite the customers tab to close the table. If asked whether you want to save changes to the table layout, click yes. Step 2: Enter Data in Suppliers Table. a. In the navigation pane on the left side of the window, double click the suppliers table to open it in datasheet view. b. Enter data for the five records shown below. c. Close the suppliers table, saving layout changes if any. Step 3: Enter Data in Products Table. a. In the navigation pane on the left side of the window, double click the products table to open it in datasheet view. b. Enter data for the five records shown below. c. Close the products table, saving layout changes, if any. Step 4: Enter Data in Orders and OrderLine Tables. a. In the navigation pane on the left side of the window, double click the orders and OrderLine tables to open them in datasheet view. (You will be alternating entering data into each table, so it is convenient to have them both open at once on different tabs.) b. Enter data for the following records into these tables. Orders: OrderLine: Orders: OrderLine: c. Close the orders and OrderLines tables, saving layout changes, if any. End of Part C Part D: Create Queries. Step 1: Create Customer Phone List Query. In this step, you will use query design view to create a query that lists the customer ID, last name, first name, and phone number of all customers, in alphabetical order by last name, first name. a. Select the create ribbon tab, and click query design. b. In the show table dialog, select the customers table and click add, then close. c. Drag the bottom edge of the customers table rectangle so that you can see all the fields. d. Double click the CustID, CustLastName, CustFirstName, and CustPhone fields in that order. As you double click each field, it will appear in the query design grid at the bottom of the window. e. In the query design grid, click in the sort row in the CustLastName column, and use the drop down list that appears to select ascending sort order. Do the same in the CustFirstName column. f. Click the save button (disk icon) at the upper left of the window. In the save as dialog, enter customer phone list as the query name, and click OK. g. On the query tools/design tab of the ribbon, click the run button to run the query. You should see the following output. h. Click the X on the right opposite the customer phone list tab to close the query. If you are prom