Step

Instructions

Points Possible

1

Start Access. Open the downloaded Access file named Exp19_Access_Ch09_HOEAssessment_Food_Inspections.accdb. Grader has automatically added your last name to the beginning of the filename.

0

2

Anne has asked you to review the table structure to see if any changes should be made. You decide to apply the rules of normalization to the Inspections table. Open the Inspections table in Datasheet view. Look for repeating groups in the Inspections table. The Violation field has multiple values separated by bars. This qualifies as a repeating group. This information has been added to a new table and will be removed from this table. For simplicity’s sake, the information for inspection violations has been preloaded to a table called Violations, so removing the Violation field from the Inspections table is required to conform to 1NF. Switch to Design view. Click the row selector for the Violation field and click Delete on the Home tab in the Records group. Click Yes when asked for confirmation, and switch to Datasheet view. Click Yes when prompted to save.

15

3

After you remove the Violation field, you will examine the Inspections table and convert to second normal form. Examine the Inspections table and notice there are multiple Inspections with Dominick Cortez as the Business Owner. Switch to Design view for the Inspections table. Business Owner is determined by Business ID and are attributes of the business, not a specific inspection. Fields will need to be removed to satisfy 2NF. In a real-world scenario, you would need to create a separate table for this information, but the Businesses table is provided so you do not have to perform data entry. Examine the Inspections table. Click the row selector for the Business Owner field and click Delete in the Records group of the Home tab, clicking Yes in response to the warning. You deleted this field because it is not functionally dependent on the entire primary key. Save the Inspections table. Switch to Datasheet view.

11

4

The final step to improve the Agency’s Inspections table is to convert to third normal form: The value of a non-key field cannot be functionally dependent on the value of another non-key field. Look for any non-key field values in the Inspections table that are functionally dependent on another non-key field value. Facility Type ID (non-key) is functionally dependent on Business ID (non-key). If you know the Business ID, you can determine the Facility Type ID, which will help you determine the Facility Type. A table named Facility Types already exists. Switch to Design view in the Inspections table. Delete the Facility Type ID field, clicking Yes in response to all warnings you receive. You delete the Facility Type ID field because it is functionally dependent on the Business ID field and therefore is not allowed in the Inspections table. Normally, this would then require you to set up a new table, but as you already have a Facility Types table, you can simply delete the Facility Type ID field. Note that this may not always be the case. Switch to Datasheet view, saving the table. The table now meets 3NF criteria. Close the Inspections table.

11

5

You will now create relationships for the tables in the database. Due to the changes you made to the design, you will add relationships to ensure the database functions correctly. Click the Database Tools tab and click Relationships in the Relationships group to show the Relationships window. Click Show Table. Click each table and click Add. Once you have added all five tables, click Close to close the Show Table dialog box.

0

6

Drag the Inspection Type ID field from the Inspection Types table to the Inspection Type ID field in the Inspections table. The Inspection Types and Inspections tables are related by the common Inspection Type ID field. Select the Enforce Referential Integrity and Cascade Update Related Fields options. Click Create to create this relationship.

7

7

Repeat the steps to connect the Businesses and Inspections tables via the Business ID field.

7

8

Repeat the steps to connect the Businesses and Facility Types tables via the Facility Type ID field. You may also notice the Inspections and Violations tables are not yet linked. You are leaving these tables unlinked intentionally as you want to discuss the contents with your supervisor. Save the relationships and close the Relationships window.

7

9

Select the Inspections table, and create a basic report using the Report tool. Save the report as Inspections Report. Close the report.

4

10

Select the Violations table and create a report using the Report tool. Save the report as Violations Report. Close the report.

4

11

Click the Create tab and click Navigation in the Forms group. Select the Horizontal Tabs option. A new Horizontal Tabs navigation form displays. Drag the Inspections Report from the Navigation Pane to [Add New].

6

12

Add Business Report, Facility Types Report, Inspections Types Report, and Violations Report, in that order to the navigation form.

16

13

Switch to Form View and click each tab to test the form. Save the navigation form with the default name and close the form.

5

14

To simplify the look and functionality of the database, you will hide the Navigation Pane and set the Navigation Form to open automatically when the database is opened. Click the File tab, click Options, and in the Access Options dialog box, click Current Database. Scroll to the Navigation section of the dialog box and deselect Display Navigation Pane. The Navigation Pane will now be hidden from view. In the Navigation Options, click the arrow next to Display Form: and select the newly created Navigation Form. Click OK to close the dialog box. Click OK. The Navigation Form will now display upon opening the database. Save and close the database. Re-open the database to test the changes. The Navigation Form should display upon opening the database and the Navigation Pane should not be visible.

7

15

Save the database. Close the database, and then exit Access. Submit the database as directed.

0

Total Points

100