Refer to the assignment 3 for definitions of what is meant by a database design outline and an entity-relationship diagram.

 
Q1.  Restaurant Health Inspection Office:  Your office oversees restaurant health inspections for Somerton county.  You have a staff of inspectors, for each of whom you want to store a first name, last name, date hired, office phone number, and mobile phone number.  For each restaurant in the county, you want to store a name, address, city, state, zip code, phone number, and date opened.  You also want your system to remember who owns each restaurant.  Each restaurant has one owner, but an owner may have many restaurants.  For each owner, you want to store a name, address, city, state, zip code, phone number, and e-mail address.  Assume that you do have access to a zip code table.

An inspection involves a single inspector visiting a single restaurant.  For each inspection, you also want to store the date/time that the inspection started.  98% of inspections are "pass" inspections, meaning that no health violations were found.  The remaining 2% are "fail" inspections.  For each fail inspection, you want to store a 255-character description of the health violations found.

Design a database to store this information, taking care not to waste 255 blank characters of storage to describe violations for "pass" inspections.  Draw an entity-relationship diagram and write a database design outline.
 

Q2.  Law Office:  You are trying to automate the records for your uncle's law firm.  Multiple attorneys work for the firm, and for each of them you want to store their first name, last name, date hired, hourly billing rate, and date promoted to partner (blank if the attorney is not a partner).  Each attorney has multiple cases, which is identified by a unique "docket number".  Each case has a name and description, is worked on by only one attorney, and involves a single client.  For each client, you want to store a first name, last name, middle name, phone number, address, city, state, and zip code (assume you do not have a zip-code table).  Finally, you want to keep track of the billable hours spent on each case. For a single case, this comprises information that looks like
 

Draw an entity-relationship diagram and write a database design outline.
 

Q3.  Microsoft Access Implementation:  Take your solution to Q2 above and create it in Microsoft Access:

1. Create all the necessary tables and relationships between tables. 
2. Use the form wizard to make a form that shows all the information on cases, plus the associated client first and last name.  There should be a scrolling subform showing all the case's billable hours information.  On this form, the attorney should be chosen through a combo box showing the attorney's last name. 
3. Use the form wizard to make a form showing all the data for each attorney, with a scrolling subform showing all the attorney's cases, including the docket number, case name, associated client's first name, and associated client's last name.
4. Use the form wizard to create a form showing each client, with a scrolling subform for all of their cases, showing docket number, case name, attorney first name, and attorney last name.
5. Create a "switchboard" that has buttons bringing up each of the forms you just created.  Remove the scroll bars and navigation buttons from the switchboard (using the form properties box).  Using the Startup... option from the Tools menu, make the switchboard appear automatically when Access opens the database.
6. Make all your forms, including the switchboard, look reasonably attractive.

Enter some test data to make sure your database application functions as intended (you don't have to enter very much -- just 3-4 records per table).  Hand in the resulting .mdb file electronically to the "drop box" for this course on the Sakai system at https://sakai.rutgers.edu/portal.