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.
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 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: A.
Write CREATE TABLE statements for each of these tables.
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.