You have been hired as an intern in the Oxford office of Emerson and Faulkner, a regional accounting firm operating in Alabama, Mississippi, and Tennessee. Ralph Waldo, the managing partner of the firm, knows that you have completed the undergraduate AIS course at BGSU. RW, as he likes to be called, wants you to build a database to keep track of the accountants working on various engagements. RW currently uses the following form to keep track of engagements. Each client may have multiple engagements.

Using the sample form, create a fully normalized data model for the Client Engagements application. You will provide your answer using the concept database format as exposed in Figure 14.22 of the textbook.

The table names are all in CAPS followed by the list of field names between parentheses. The primary keys are in Bold and Underlined. The foreign keys are in italic.

Copy and use the following starting table:

ENGAGEMENT (EngageID, EngageTypeID, EngageDescription, EngageManagerID, EngageManagerName, ClientID, ClientName, ClientAddress, AcctID, AcctName, AcctCertifications, Hours, BillableHours, BillingRate, EngageFee)

Hints

1. The engagement Manager is an accountant (The EngageManagerID field will store the same value as the AcctID field). You will have an ACCOUNTANTS table with the AcctID as the primary key (PK) field. The EngageManagerID will be the foreign key (FK) field in the ENGAGEMENTS table determining which accountant will be the manager for the engagement. The EngageManagerName field is the same as the AcctName field and it is already stored in the ACCOUNTANTS table. Thus, do not create an ENGAGEMANAGER table. It will be redundant because all the info would already be stored in the ACCOUNTANTS table.

2. EngageFee is NOT derived/computed based on other fields (SUM of the Hours * Billing Rate). Thus, you need to keep it in the ENGAGEMENTS table.

3. Each accountant will have a series of certifications. This is another case of repeating groups. You will need to apply rule #2 again to handle this situation. Create another table with a composite key (AcctID + AcctCertifications). You will also create a table (CERTIFICATIONS) to store the description of each certification code (1 is CPA, 2 is CMA, ...).

Client Engagement Assignments Engagement ID: 00207 Type: 1-Audit Manager: 103-R. Thomas Client Information Client ID: 0109 Name: MicroStar Software Address: 1414 East 82nd Northport, AL 35402 Hours Billable Accountants on Engagement ID Name Certifications 101 G. Jefferies 103 R. Thomas 1,3 Hours 25 Billing Rate $100.00 N N 5 $125.00 Y N 10 $50.00 104 N. Smith *1=CPA 2-CMA. 3-CISA. 4-WebTrust Fee Charged: $5,250.00 FIGURE 14.22 Conceptual Database Tables Attributes CUSTOMER (Customer No., Customer Company, Customer First Name, Customer Last Name, Customer Street Address Customer City, Customer State, Customer ZIP, Customer Phone, Customer Email SALES ORDER (Sales Order No. Sales Order Date, Sales Order Balance, Sales Order Subtotal, Sales Order Tax, Sales Order Shipping, Sales Order Miscellaneous, Customer No.) SALES ORDER LINE (Sales Order No., Item No.. Line Item Quantity, Line Item Unit Price, Line Item Tax, Line Item Discount Line Item Total) ITEM (Item No., Item Name, Item Description) Client Engagement Assignments Engagement ID: 00207 Type: 1-Audit Manager: 103-R. Thomas Client Information Client ID: 0109 Name: MicroStar Software Address: 1414 East 82nd Northport, AL 35402 Hours Billable Accountants on Engagement ID Name Certifications 101 G. Jefferies 103 R. Thomas 1,3 Hours 25 Billing Rate $100.00 N N 5 $125.00 Y N 10 $50.00 104 N. Smith *1=CPA 2-CMA. 3-CISA. 4-WebTrust Fee Charged: $5,250.00 FIGURE 14.22 Conceptual Database Tables Attributes CUSTOMER (Customer No., Customer Company, Customer First Name, Customer Last Name, Customer Street Address Customer City, Customer State, Customer ZIP, Customer Phone, Customer Email SALES ORDER (Sales Order No. Sales Order Date, Sales Order Balance, Sales Order Subtotal, Sales Order Tax, Sales Order Shipping, Sales Order Miscellaneous, Customer No.) SALES ORDER LINE (Sales Order No., Item No.. Line Item Quantity, Line Item Unit Price, Line Item Tax, Line Item Discount Line Item Total) ITEM (Item No., Item Name, Item Description)