The goal of this assignment is to get you re-familiarized with relational databases, and specifically the standard query language for databases (SQL).

Garden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations. Garden Glory is owned by two partners. They employ two office administrators and a number of full- and part-time gardeners. Garden Glory will provide one-time garden services, but it specializes in ongoing service and maintenance. Many of its customers have multiple buildings, apartments, and rental houses that require gardening and lawn maintenance services. This case is in Chapter 3 (pages 232-235).

Assume that Garden Glory designs a database with the following tables.

OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)

OWNED_PROPERTY (PropertyID, PropertyName, Street, City, State, Zip, OwnerID)

GG_SERVICE (ServiceID, ServiceDescription, CostPerHour)

EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)

PROPERTY_SERVICE (PropertyID, ServiceID, ServiceDate, EmployeeID, HoursWorked)

The referential integrity constraints are:            

OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER

PropertyID in PROPERTY_SERVICE must exist in PropertyID in PROPERTY

ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE

EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE

Assume that OwnerID in OWNER, PropertyID in OWNED_PROPERTY, ServiceID in PROPERTY_SERVICE, and EmployeeID in EMPLOYEE are surrogate keys with values as follows:

OwnerID         Start at 1     Increment by 1

PropertyID      Start at 1     Increment by 1

ServiceID       Start at 1      Increment by 1

EmployeeID   Start at 1      Increment by 1

Sample data are shown in Figures 3-30, 3-31, 3-32, 3-33, and 3-34 (pages 233-234). OwnerType is either Individual or Corporation, PropertyType is Office, Apartments, or Private Residence, and ExperienceLevel is one of Junior, Senior, or Master. These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS as appropriate to obtain your results. Name your database GARDEN_GLORY.

Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the SQL Server, Oracle Database, or MySQL data types shown in Figure 3-5. For each SQL statement, write and show your results based on your data.

Complete the following activities:

1. Write CREATE TABLE statements for each of these tables.

2. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading updates and deletions and justify those assumptions.

(Hint: You can combine the SQL for your answers to parts A and B)

Present your results from parts A and B in an illustrated narrative form, in a single Microsoft Word document. Include copies of your SQL code, as well as any screen shots of the results in Access or other software you have used.