Assume that Garden Glory designs a database with the following tables: OWNER (OwnerID,...

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

OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)

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

GG_SERVICE (ServiceID, ServiceDescription, CostPerHour);

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

PROPERTY_SERVICE (PropertyServiceID, 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

OWNED_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 PROPERTY, 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

PropertyServiceID Start at 1 Increment by 1

Sample data are shown in Figures 3-30, 3-31, 3-32, 3-33, and 3-34. 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 you write, show the results based on your data.

Write SQL statements and answer questions for this database as follows:

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

B. 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 questions A and B.)

C. Write SQL statements to insert the data into each of the five Garden Glory database tables. Assume that any surrogate key value will be supplied by the DBMS. Use the data in Figures 3-30, 3-31, 3-32, 3-33, and 3-34.

D. Write SQL statements to list all columns for all tables.

E. Write an SQL statement to list LastName, FirstName, and CellPhone for all employees having an experience level of Master.

F. Write an SQL statement to list Name and CellPhone for all employees having an experience level of Master and Name that begins with the letter J.

G. Write an SQL statement to list the names of employees who have worked on a property in Seattle. Use a subquery.

H. Answer question G but use a join using JOIN ON syntax.

I. Write an SQL statement to list the names of employees who have worked on a property owned by a corporation. Use a subquery.

J. Answer question I but use a join using JOIN ON syntax.

K. Write an SQL statement to show the name and sum of hours worked for each employee.

L. Write an SQL statement to show the sum of hours worked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel, in descending order.

M. Write an SQL statement to show the sum of HoursWorked for each type of OWNER but exclude services of employees who have ExperienceLevel of Junior.

N. Write an SQL statement to show all properties and the services performed at those properties. The output from this statement should include any properties that have not had any service performed at them. The SQL statement output should list PropertyID, PropertyName, PropertyType, PropertyServiceID, ServiceID, ServiceDate, and ServiceDescription. (Hint: Use JOIN ON syntax.)

O. Write an SQL statement to show all properties and the services performed at those properties. The output from this statement should include any Garden Glory services that have not been performed at any property. The SQL statement output should list PropertyID, PropertyName, PropertyType, PropertyServiceID, ServiceID, ServiceDate, and ServiceDescription. (Hint: Use JOIN ON syntax.)

P. Write an SQL statement to modify all EMPLOYEE rows with ExperienceLevel of Master to SuperMaster.

Q. Write an SQL statement to switch the values of ExperienceLevel so that all rows currently having the value Junior will have the value Senior and all rows currently having the value Senior will have the value Junior.

R. Given your assumptions about cascading deletions in your answer to question B, write the fewest number of DELETE statements possible to remove all the data in your database but leave the table structures intact. Do not run these statements if you are using an actual database!