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.