Excise I Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure 2-31 shows data that Regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT: PROJECT (ProjectID, EmployeeName, EmployeeSalary) FIGURE 2-31 Sample Data for Regional Labs A. Assuming that all functional dependencies are apparent in this data, which of the following are true? 1. ProjectID EmployeeName 2. ProjectID EmployeeSalary 3. (ProjectID, EmployeeName) EmployeeSalary 4. EmployeeName EmployeeSalary 5. EmployeeSalary ProjectlD 6. EmployeeSalary (ProjectID, EmployeeName) B. What is the primary key of PROJECT? C. Are all the nonkey attributes (if any) dependent on the primary key? D. In what normal form is PROJECT? E. Describe two modification anomalies that affect PROJECT. F. Is ProjectID a determinant? If so, based on which functional dependencies in part A? G. Is EmployeeName a determinant? If so, based on which functional dependencies in part A ? H. Is (ProjectID, EmployeeName) a determinant? If so, based on which functional dependencies in part A? I. Is EmployeeSalary a determinant? If so, based on which functional dependencies in part A? J. Does this relation contain a transitive dependency? If so, what is it? K. Redesign the relation to eliminate modification anomalies. Excise II Garden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations. Garden Glory is owned by two partners. They employ two office administrators and a number of full- and part-time gardeners. Garden Glory will provide one-time garden services, but it specializes in ongoing service and maintenance. Many of its customers have multiple buildings, apartments, and rental houses that require gardening and lawn maintenance services. Figure 2-32 shows data that Garden Glory collects about properties and services. FIGURE 2-32 Sample Data for Garden Glory A. Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data and also on the basis of what you know about service businesses. B. Given your assumptions in part A, comment on the appropriateness of the following designs: 1. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount) 2. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount) 3. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount) 4. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount) 5. PROPERTY (PropertylD, PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount) 6. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate) and SERVICE (ServiceDate, Description, Amount) 7. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate) and: SERVICE (ServicelD, ServiceDate, Description, Amount) 8. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServicelD) and: 9. SERVICE (ServicelD, ServiceDate, Description, Amount, PropertyID) 9. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip) and: SERVICE (ServicelD, ServiceDate, Description, Amount, PropertyID) C. Suppose Garden Glory decides to add the following table: SERVICE-FEE (PropertyID, ServicelD, Description, Amount) Add this table to what you consider to be the best design in your answer to part B. Modify the tables from part B as necessary to minimize the amount of data duplication. Will this design work for the data in Figure 2-31? If not, modify the design so that this data will work. State the assumptions implied by this design. Excise III James River Jewelry is a small jewelry shop. While James River Jewelry does sell typical jewelry purchased form jewelry vendors, including such items as rings, necklaces, earrings, and watches, it specializes in hard-to-find Asian jewelry. Although some Asian jewelry is manufactured jewelry purchased from vendors in the same manner as the standard jewelry is obtained, many of the Asian jewelry pieces are often unique single items purchased directly from the artisan who created the piece (the term "manufactured" would be an inappropriate description of these pieces). It has a small but loyal clientele, and it wants to further increase customer loyalty by creating a frequent buyer program. In this program, after every 10 purchases, a customer will receive a credit equal to 50 percent of the sum of his or her 10 most recent purchases. This credit must be applied to the next (or " 11th")purchase. Figure D-1 shows data that James River Jewelry collects for its frequent buyer program. Figure D-1: Sample Data for James River Jewelry A. Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data and also on the basis of what you know about retail sales. B. Given your assumptions in part A, comment on the appropriateness of the following designs: 1. CUSTOMER (Name, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount) 2. CUSTOMER (Name, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount) 3. CUSTOMER (Name, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount) 4. CUSTOMER (CustomerID, Name, Phone, Email, InvoiceNumber, InvoiceDate, PreTaxAmount) 5. CUSTOMER (Name, Phone, Email) and PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount) 6. CUSTOMER (Name, Phone, Email) and PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, Email) 7. CUSTOMER (Name, Email) and PURCHASE (InvoiceNumber, Phone, InvoiceDate, PreTaxAmount, Email) C. Modify what you consider to be the best design in part B to include a column called AwardPurchaseAmount. The purpose of this column is to keep a balance of the customers' purchases for award purposes. Assume that returns will be recorded with invoices having a negative PreTaxAmount. D. Add a new AWARD table to your answer to part C. Assume that the new table will hold data concerning the date and amount of an award that is given after a customer has purchased 10 items. Ensure that your new table has appropriate primary and foreign keys. Note: 1) Read Textbook Chapter 1-2 and review lecture PPT before working on the project excises. 2) Students can submit the homework as a group (no more five students for each group)