Topic 6 Module 3 Module 3: Lab 2: Maintaining the Museum Gift Shop Database This lab is found on page AC175-176. Please be certain to respond to question 16 of the assignment via a separate Word document. Use the database modified in Lab 2 of Module 2 for this assignment or see your instructor for information on accessing the files required for this book. Instructions: Maintaining the Museum Gift Shop Database The manager of the Science Museum gift shop needs to change the database structure, add validation rules, and update records. Also, a volunteer at the gift shop was asked to add some items to the database. By mistake, the volunteer created a new database in which to store the items. These items need to be added to the Museum Gift Shop database. To complete this assignment, you will be required to use the Data Files. Please contact your instructor for information about accessing the Data Files. Use the database modified in Lab 2 of Module 2 for this assignment or see your instructor for information on accessing the files required Perform the following tasks:

1. Open the Lab 2 Museum Gift Shop database, and then open the Item table in Design view.

2. Add a lookup field, Item Type, to the Item table. The field should appear after the Description field. The field will contain data on the type of item for sale. The item types are ACT (activity, game), BKS (book), and NOV (novelty, gift).

3. Add the following validation rules to the Item table and save the changes: a. Make Description a required field. b. Specify the legal values ACT, BKS, and NOV for the Item Type field. Include validation c. Assign ACT as the default value for the Item Type field. d. Specify that the number on hand must be between 0 and 50, inclusive. Include validation

4. Using a query, assign the value ACT to the Item Type field for all records. Save the query as Update Query.

5. Create a split form for the Item table and save it as Item Split Form.

6. Use the split form to change the item type for items 6234, 6345, and 7123 to BKS. Change the item type for items 3663, 4583, 6185, 8196, and 8344 to NOV.

7. Open the Lab 2 Additional Items database from the Data Files.

8. Create and run a query to append the data in the Additional Items table to the Item table in the Lab 2 Museum Gift Shop database. Save the query as Append Query and close the Lab 2 Additional Items database.

9. Open the Lab 2 Museum Gift Shop database and then open the Item table. The result of the append query will be the table shown in Figure 384

10. Create an advanced filter for the Item table. Filter the table to find all items with fewer than 10 items on hand. Sort the filter by Item Type and Description. Save the filter settings as a query and name the filter Reorder Filter. Clear the filter from the Item table.

11. Using a query, delete all records in the Item table where the description starts with the letter M. Run the query and save it as Delete Query.

12. If requested to do so by your instructor, right-click the Item table in the Navigation Pane, click Table Properties, and add a description for the Item table that includes your first and last name and the date you completed this assignment. Save the change to the table property.

13. Specify referential integrity between the Vendor table (the one table) and the Item table (the many table). Cascade the update but not the delete.

14. Add the Item Type field to the Item Status Report. It should follow the Description field.

15. Submit the revised database in the format specified by your instructor.

16. There are two ways to enter the validation rule in Step 3d. What are they? Which one did you use?