Exp19_access_ch09_hoeassessment – food inspections 1.0

Exp19_Access_Ch09_HOEAssessment – Food Inspections 1.0

Project Description:

Your job at the Chicago Health Inspection Agency has been fun and challenging. You have been making daily updates to the Agency’s database with the help of your boss, Anne Serdifone. The database tables may already be normalized; however, you need to examine the tables to verify this. Based on your understanding of the rules of normalization, you decide to recommend some design changes to the database.

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.

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.

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.

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.

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.

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.

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

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.

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

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

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].

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

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

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.

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