Sample Data for Garden Glory |Туре Office Apartment Office Office Office Apartment Office ServiceDate ZIP 98119 City Seattle Lynnwood Bellevue Seattle Seattle Lynnwood Seattle Description PropertyName Eastlake Building Elm St Apts Jeferson Hill Eastlake Building Eastlake Building Elm St Apts Eastlake Building Street 123 Eastlake 4 East Elm 42 West 7th St 123 Eastlake 123 Eastlake 4 East Elm 123 Eastlake Amount 42.50 5/5/2014 Lawn Mow Lawn Mow Garden Service Lawn Mow Lawn Mow Lawn Mow |Lawn Mow 98223 5/8/2014 123.50 98040 98119 98119 98223 98119 5/8/2014 5/10/2014 5/12/2014 $ 53.00 42.50 42.50 5/15/2014 123.50 5/19/2014 24 42.50
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.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 5 images