Assume that Garden Glory designs a database with the following tables: OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType) PROPERTY ( PropertyID, PropertyName, Street, City, State, Zip, OwnerID) EMPLOYEE ( EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel) SERVICE ( PropertyID, EmployeeID, SeviceDate, HoursWorked) The referential integrity constraints are: OwnerID in PROPERTY must exist in OwnerID in OWNER PropertyID in SERVICE must exist in PropertyID in PROPERTY EmployeeID in 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 EmployeeID Start at 1 Increment by 1 Some sample data are shown in Figure 2- 32, but you need to create additional data to populate the tables

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

OWNER ( OwnerID, OwnerName, OwnerEmail, OwnerType)

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

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

SERVICE ( PropertyID, EmployeeID, SeviceDate, HoursWorked)

The referential integrity constraints are:

OwnerID in PROPERTY must exist in OwnerID in OWNER

PropertyID in SERVICE must exist in PropertyID in PROPERTY

EmployeeID in 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

EmployeeID Start at 1 Increment by 1

Some sample data are shown in Figure 2- 32, but you need to create additional data to populate the tables. ( Alternatively, your instructor may provide you with a data set.) Type is either Individual or Corporation, and ExperienceLevel is one of Junior, Senior, Master, or SuperMaster. 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 assump-tions. ( Hint: You can combine the SQL for your answers to questions A and B.)