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.
1. Design a database to hold all this information. You can create synthetic keys as necessary or convenient. Use the synthetic keys ClientID and AttorneyID for clients and attorneys, respectively. Create a database design outline and an ER diagram.
2. In Access, create all the necessary tables for this database, and choose a reasonable datatype for each attribute. Assume phone numbers are in standard US format. Create all necessary relationships between the tables and enforce referential integrity. You do not have to enter any data or create any forms. Create a “Documenter” report for the database.
