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, middle name, last name, date hired, hourly billing rate, and date promoted to partner (blank if the attorney is not a partner). 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). Each case is identified by a unique “docket number” and has a name and description. A case is always for a single client, but a client may have more than one case. Finally, you want to keep track of attorney billable hours. Each record of billable hours involves one attorney working on one case. For each billable hours record, the database should remember which attorney did the work, which case was involved, the date, the number of hours billed (fractions are allowed), and a description of the work performed.
A. Draw an ER diagram and write a database design outline for this application. Use the synthetic keys “ClientID” and “AttorneyID” for clients and attorneys, respectively.
B. Create a database matching your design in Microsoft Access. Create all the necessary tables and relationships between them.
C. Use the form wizard to make a form that shows all the information on cases, plus the associated client first and last name. Instead of simple text box for the client ID field, substitute a combo box that also shows the client first and last name when you pull it down (make a combo box that includes the client ID, client first name, and client last name, and does not hide the key field). There should be a scrolling subform showing all the case’s billable hours information. The scrolling section should show the date, hours billed, attorney last name, and description.
D. Use the form wizard to make a form showing all the data for each attorney, with a scrolling subform showing all his or her hours billed. The subform should show the case name, date, hours spent, and description.
E. Use the form wizard to create a form showing each client, with a scrolling subform for all of their cases, showing the docket number and case name.
