Assignment 3
Sample Data for Garden Glory (Figure 2-29)
PropertyNamePropertyTypeStreetCity Zip ServiceDate Description Amount
EastlakeBuilding Office 123 EastlakeSeattle 98119 5/5/2010 Lawn Mow $42.50
Elm St Apts Apartment 4 East Elm Lynnwood 98223 5/8/2010 Lawn Mow $123.50
Jefferson Hill Office 42 West 7th StBellevue98040 5/8/2010 Garden Service $53.00
EastlakeBuilding Office 123 EastlakeSeattle 98119 5/10/2010 Lawn Mow $42.50
EastlakeBuilding Office 123 EastlakeSeattle 98119 5/12/2010 Lawn Mow $42.50
Elm St Apts Apartment 4 East Elm Lynnwood 98223 5/15/2010 Lawn Mow $123.50
EastlakeBuilding Office 123 EastlakeSeattle 98119 5/19/2010 Lawn Mow $42.50
For the questions below and using figure 2-29 above, copy and modify the table and data so it illustrates the relation structure from each question below. Remember to remove any duplicate rows in any tables. Highlight the primary key fields in yellow. You may need to review the hints at the top of Chapter 1 Review Questions on how to add and remove columns and rows to a Word table.
Try to create an insert, update, and delete anomaly for each problem using the following rules.
Insert a new property with the following data: “DSC, Office, 1770 Tech, Daytona, 32117”. No services have been performed on this new property.
The boss wants to change the description “Lawn Mow” to “Mowing” and highlight in red. How many places did you have to do it?
Delete the Garden Service that occurred on 5/8/2010 by striking it out. Are you missing anything other than the service information?
Finally, discuss if that relation structure is appropriate or not and why. For appropriateness, consider that a lot more data would be entered into this table. Don’t consider just the sample data given. The first one has been done for you.
0. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
1. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
2. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
3. PROPERTY (PropertyID, 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)
and SERVICE (ServiceDate, Description, Amount)
and SERVICE (ServiceDate, Description, Amount)
6. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate)
and SERVICE (ServiceID, ServiceDate, Description, Amount)
and SERVICE (ServiceID, ServiceDate, Description, Amount)
7. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceID)
and SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID).
and SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID).
8. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip) and SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)
9. It is perfectly acceptable to have duplicate values in a foreign key. However, there are still some other fields that are duplicated in the best design from above. Fix it and rewrite all the relation structures for Garden Glory. Note: you may have to introduce more entities to remove all of the duplication.
