Create the Garden Glory Operational Data Store

The first step for this project is to create the Garden Glory operational data store. Review the Garden Glory project in chapter 3 to understand the requirements for the tables and the referential integrity constraints. The tables in the Garden Glory operational data store are OWNER, OWNED_PROPERTY, EMPLOYEE, GG_SERVICE, and PROPERTY_SERVICE. These tables contain the following columns.

OWNER (OwnerID, OwnerName, OwnerEmailAddress, 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

Use data types appropriate for MS SQL Server and for the type of data in the column. These tables have the following referential integrity constraints:

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

Define the following primary keys as surrogate keys with values starting at 1 and incrementing by 1:

OwnerID in OWNER, PropertyID in OWNED_PROPERTY, ServiceID in GG_SERVICE, EmployeeID in EMPLOYEE, and PropertyServiceID in PROPERTY_SERVICE. MS Server SQL uses the IDENTITY(n,n) syntax to create a surrogate key.

Create the Garden Glory Data Warehouse

Create the Garden Glory data warehouse using a star schema design. The dimension tables are EMPLOYEE, PROPERTY, and TIMELINE. The fact table is SERVICE_HOURS, and the fact table measure is HoursWorked. The following is a diagram of the tables in the Garden Glory data warehouse.

Create the SQL for the Extract, Transform, and Load (ETL) for the Garden Glory Data Warehouse

Consider what transformations will be needed to load the data from the Garden Data operational data store into the Garden Glory data warehouse. Write the SQL statements to load the transformed data into the GG Data Warehouse.

Create the SQL to Query the Garden Glory Data Warehouse

Write and execute SQL queries in the GG data warehouse that produce the following result sets:

HoursWorked by all employees in June 2017

HoursWorked in the property that had the most hours worked

HoursWorked by all employees in the city of Bellevue Washington in the first quarter of 2017

HoursWorked by senior level employees in the second quarter of 2017 grouped by city and state

After you complete these queries in the GG data warehouse, try to create queries that produce the same result set in the GG operational data store. Are these queries more difficult in the operational data store?

Project Presentation

Create a PowerPoint presentation for the project containing the following elements:

A database diagram of the Garden Glory operational data store

A database diagram of the Garden Glory data warehouse

A description of the extract, transform, and load (ETL) SQL statements required to load data from the operational data store to the data warehouse

The advantages of using denormalized data in a data warehouse for OLAP queries. Use the data warehouse queries as an example.

Garden Glory Data in Operational Data Store

The primary keys like O_PKn are a symbolic representation of the integer primary key that MS SQL Server assigns with the IDENTITY syntax. These primary keys correspond to the symbolic representation of the foreign keys like O_FKn. The first one or two characters are taken from the name of the table.

OWNER Table

OwnerID	OwnerName	OwnerEmailAddress	OwnerType
O_PK1	'Mary Jones'	'Mary.Jones@somewhere.com'	'Individual'
O_PK2	'DT Enterprises'	'DTE@dte.com'	'Corporation'
O_PK3	'Sam Douglas'	'Sam.Douglas@somewhere.com'	'Individual'
O_PK4	'UNY Enterprises'	'UNYE@unye.com'	'Corporation'
O_PK5	'Doug Samuels'	'Doug.Samuels@somewhere.com'	'Individual'