1. Complete questions 2-7

Note:

1. Complete questions 2-7 in part B of the Garden Glory Project on p.129 and 130 of the textbook (8th

edition). [5 pts. Each]

a. There may be more than one service performed at a property on a given date. Use this

information when you answer questions.

b. Comment on each question whether a given design is good and provide reasons for that. For

example, the answer for Q1 is This design is not appropriate because the designated Primary

Key (PK) (PropertyName) in the table cannot determine some other attributes of the same

table (e.g., ServiceDate, Description, Amount); in other words, the designated PK is not

unique. Your answers need to be specific and should clearly pinpoint the design problem

with references to the relevant attributes in the table and the reason of violation. Any vague

answer like This design is bad because the PK is not good, will not be accepted and would

result in 0 point.

c. Note that the foreign keys are italicized instead of dashed underlined.

d. Clearly label answers for each question; failure to do so may be subject to 5 points penalty.

e. If you need to make assumptions, make sure they are reasonable assumptions.

Questions

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)

Fig. 2-34

Sample Data for Garden Glory

PropertyName	PropertyType	Street	City	Zip	ServiceDate	Description	Amount
Eastlake Building	Office	123 Eastlake	Seattle	98119	5/5/2016	Lawn Mow	$ 42
Elm St Apts	Apartment	4 East Elm	Lynnwood	98223	5/8/2016	Lawn Mow	$ 123
Jeferson Hill	Office	42 West 7th St	Bellevue	98040	5/8/2016	Garden Service	$ 53
Eastlake Building	Office	123 Eastlake	Seattle	98119	5/10/2016	Lawn Mow	$ 42
Eastlake Building	Office	123 Eastlake	Seattle	98119	5/12/2016	Lawn Mow	$ 42
Elm St Apts	Apartment	4 East Elm	Lynnwood	98223	5/15/2016	Lawn Mow	$ 123
Eastlake Building	Office	123 Eastlake	Seattle	98119	5/19/2016	Lawn Mow	$ 42