please answer with great detail and explanations

ASSIGNMENT 2: GARDEN GLORY PROJECT QUESTIONS (Chapter Two) Submission: A single.doc or .pdf file to Canvas Note: Spelling check in Microsoft Word may block people seeing the underline for primary key. Please turn off the spelling check for your Word document or submit your answer as a PDF file. The spelling check for this Word document has already been turned off. In this assignment, you do NOT need to show the full tables in your answers. To answer Questions B.D and E. you just need to use the "relation representation" we discussed in class (use underline and italic to denote primary key and foreign key, respectively). Assume we have following PROPERTY table and SERVICES tables. Table 1 PROPERTY Table Property Name Property Address Owner Name OwnerEmail OwnerType Eastlake Building 123 Eastlake, Seattle, WA 98119 DT Enterprises dte a dte.com Corporation Jeferson Hill 42 West 7th St, Bellevue, WA 98007 DT Enterprises dte a dte.com Corporation Elm St Apts 4 East Elm St. Lynwood, WA 98223 Sam Douglas Doug Samuels @ somewhere.com Individual Lake View Apts 1265 32nd Ave, Redmond, WA 98052 Sam Douglas Doug Samuels @ somewhere.com Individual Jones House 1456 48th St, Bellevue, WA 98007 Mary Jones Mary Jones@ somewhere.com Individual Table 2 SERVICES Table Service Description Service Date Service Fee Property Name PropertyAddress Now Lawn 5/5/2014 $42.50 Jones House 1456 48th St, Bellevue, WA 98007 Plant Annuals 5/8/2014 $300.00 Eastlake Building 123 Eastlake, Seattle, WA 98119 Garden Weed Control 5/8/2014 $75.00 Lake View Apts 1265 32nd Ave, Redmond, WA 98052 Trim Hedge 5/10/2014 $150.00 Elm St Apts 4 East Elm St, Lynwood, WA 98223 Mow Lawn 5/12/2014 $120.00 Eastlake Building 123 Eastlake, Seattle, WA 98119 Trim Tree 5/12/2014 Elm St Apts 4 East Elm St, Lynwood, WA 98223 Employee Name Employee Phone Sam Smith 206-254-1234 John Evanston 206-316-5555 Dale Murray 206-298-5452 Sam Smith 206-254-1234 John Evanston 206-316-5555 John Evanston 206-316-5555 $200.00 A. B. Comment on following designs (whether the primary keys are appropriate). (20 points) Hint: The answer may not be as simple as a yes or no. Explain and justify your answer. 1. PROPERTY (Property Name. Property Address, OwnerName, OwnerEmail, OwnerType) 2. PROPERTY (Property Name. Property Address, OwnerName, OwnerEmail. OwnerType) 3. PROPERTY (Property Name, Property Address, OwnerName OwnerEmail. OwnerType) 4. PROPERTY (Property Name, Property Address, OwnerName OwnerEmail, OwnerType) Add ID columns called Property ID and OwnerID into the PROPERTY table. Answer following questions. (25 points) 1. List one candidate key in this relation. 2. List at least two functional dependencies. What is the determinant in each functional dependency? 3. Is the PROPERTY relation well-formed? Why? 4. If the relation is not well-formed, normalize it to well-formed relations. In the well- formed relations, please indicate the primary keys, foreign keys, and referential integrity constraints. Comment on following designs (whether the primary keys are appropriate). (20 points) Hint: The answer may not be as simple as a yes or no. Explain and justify your answer. 1. SERVICE (Service Description, ServiceDate. ServiceFee, Property Name, Property Address, EmployeeName. EmployeePhone) 2. SERVICE (ServiceDescription. ServiceDate. ServiceFee, Property Name, Property Address, EmployeeName. EmployeePhone) 3. SERVICE (Service Description, ServiceDate. ServiceFee, Property Name, Property Address, EmployeeName, Employee Phone) C. 4. SERVICE Service Description, ServiceDate. ServiceFee, Property Name, Property Address, EmployeeName. EmployeePhone) D Add ID columns called ServiceID. PropertyID and EmployeeID into the SERVICE table. Answer following questions. (25 points) 1. List one candidate key in this relation. 2. List at least two functional dependencies. What is the determinant in each functional dependency? 3. Is the SERVICE relation well-formed? Why? 4. If the relation is not well-formed, normalize it to well-formed relations. In the well- formed relations, please indicate the primary keys, foreign keys, and referential integrity constraints. So far we have dealt with the PROPERTY and SERVICE tables separately. You may realize that there is a strong connection between them. In fact, they are both part of the Garden Glory company's database. Can you merge the tables you designed in Question B.4 and D.4 into a final design of the company's database? (10 points) E