Figure 2-34 shows data that Garden Glory collects about properties and services. A....

Figure 2-34 shows data that Garden Glory collects about properties and services.

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 (PropertyID, 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 (ServiceID, ServiceDate, Description, Amount)

8. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate)

and:

SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)

9. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip)

and:

SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)

C. Suppose Garden Glory decides to add the following table:

SERVICE_FEE (PropertyID, ServiceID, 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-34? If not, modify the design so that this data will work. State the assumptions implied by this design.