Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 41 of 62
ANSWERS TO GARDEN GLORY PROJECT QUESTIONS
If you have not already implemented the Garden Glory database shown in Chapter 3 in a
DBMS product, create and populate the GARDEN_GLORY database now in the DBMS
of your choice (or as assigned by your instructor).
A. You need about 20 SERVICE transactions in the database. Write the needed SQL
statements for any needed additional SERVICE transactions and insert the data into
your database.
See the files DBC-e08-MSSQL-GG-CH08-Create-Tables.sql, and insert into the
PROPERTY_SERVICE TABLE using DBC-e08-MSSQL-GG-CH08-Insert-New-Data.sql.
/***** PROPERTY_SERVICE *****************************************************/
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 42 of 62
INSERT INTO PROPERTY_SERVICE VALUES(1, 1, ’05-JUN-17′,1, 4.50);
INSERT INTO PROPERTY_SERVICE VALUES(3, 3, ’08-JUN-17′,1, 4.50);
INSERT INTO PROPERTY_SERVICE VALUES(2, 2, ’08-JUN-17′,3, 2.75);
INSERT INTO PROPERTY_SERVICE VALUES(6, 5, ’10-JUN-17′,5, 2.50);
INSERT INTO PROPERTY_SERVICE VALUES(5, 4, ’12-JUN-17′,5, 7.50);
INSERT INTO PROPERTY_SERVICE VALUES(8, 4, ’15-JUN-17′,4, 2.75);
/*********************************************************************************/
B. Design a data warehouse star schema for a dimensional database named GG-DW. The
fact table measure will be HoursWorked.
C. Create the GG-DW database in a DBMS product.
This was done in SQL Server 2016.
See the file DBC-e08-MSSQL-GG–DW–Create-Tables.sql.
CREATE TABLE TIMELINE(
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 43 of 62
CREATE TABLE EMPLOYEE(
CREATE TABLE SERVICE_HOURS(
TimeID Int NOT NULL,
PropertyID Int NOT NULL,
EmployeeID Int NOT NULL,
HoursWorked Numeric (4,2) NULL,
CONSTRAINT SERVICE_HRS_PK
D. What transformations of data will need to be made before the GG-DW database can be
loaded with data? List all the transformations, showing the original format of the
GARDEN_GLORY data and how it appears in the GG-DW database.
E. Write the complete set of SQL statements necessary to load the transformed data into
the GG-DW database.
Note: the TimeID values were computed from a date string using Excel:
=DATEVALUE(“04-Oct-2017”) will display 43012
See the file DBC-e08-MSSQL-GG–DW–Insert-Data.sql.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 44 of 62
— 5/8/2017 => 42863
INSERT INTO TIMELINE VALUES(
42863, ’08-May-17′, 5, ‘May’, 2, ‘Qtr2’, 2017);
— 5/10/2017 => 42865
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 45 of 62
— 6/12/2017 => 42898
— 6/15/2017 => 42901
INSERT INTO TIMELINE VALUES(
42901, ’15-Jun-17′, 6, ‘Jun’, 2, ‘Qtr2’, 2017);
— 7/19/2017 => 42935
INSERT INTO TIMELINE VALUES(
42935, ’19-Jul-17′, 7, ‘Jul’, 3, ‘Qtr3’, 2017);
/***** EMPLOYEE *************************************************************/
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 46 of 62
INSERT INTO [PROPERTY] VALUES(
4, ‘Lake View Apts’, ‘Redmond’, ‘WA’, ‘98052′);
INSERT INTO [PROPERTY] VALUES(
/***** SERVICE **************************************************************/
— 5/5/2017 => 42860, etc, see above
INSERT INTO SERVICE_HOURS VALUES(42860, 1, 1, 4.50);
INSERT INTO SERVICE_HOURS VALUES(42863, 3, 3, 4.50);
INSERT INTO SERVICE_HOURS VALUES(42863, 2, 2, 2.75);
INSERT INTO SERVICE_HOURS VALUES(42865, 6, 5, 2.50);
INSERT INTO SERVICE_HOURS VALUES(42891,1, 1, 4.50);
INSERT INTO SERVICE_HOURS VALUES(42894,3, 1, 4.50);
/****************************************************************************************
/
F. Populate the GG-DW database, using the SQL statements you wrote to answer part E.
This is self-explanatory, just run the script.
G. Write an SQL query similar to the one shown in the text on page 497 (should be page
499) that uses the hours worked per day as the measure.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 47 of 62
See the file DBC-e08-MSSQL-GG–DW–CH08-Questions-G-H.sql.
/* ******** Question G Query ******************************************/
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 48 of 62
H. Write the SQL view equivalent of the SQL query you wrote to answer part G.
Views are covered in Appendix E. See the file DBC-e08-MSSQL-GG–DW–CH08-Questions-G-H.sql.
/* ******** Guestion H View Defintion *********************************/
CREATE VIEW GGDWTotalServiceHoursView AS
SELECT E.EmployeeID, E.ExperienceLevel,
I. Create the SQL view you wrote to answer part H in your GG-DW database.
This is self-explanatory, just run the above CREATE VIEW script (copy and paste to query tab).
J. Create the Microsoft Excel 2016 workbook named GG-DW–BI–Exercises.xlsx.
This is self-explanatory. See file: DBC-e08-GG–DW–BI–Exercises.xlsx.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 49 of 62
K. Using either the results of your SQL query from part G (copy the results of the query into
a worksheet in the GG-DW-BI.xlsx workbook and then format this range as a worksheet
table) or your SQL view from part I (create a Microsoft Excel data connection to the
view), create an OLAP report similar to the OLAP report shown in Figure 8-16. (Hint: If
you need help with the needed Microsoft Excel actions, search in the Microsoft Excel
help system for more information.)
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 50 of 62
ANSWERS TO JAMES RIVER JEWELRY PROJECT QUESTIONS
[NOTE: The James River Jewelry Project Questions are available online for Appendix D,
which can be downloaded from the textbook’s Web site:
www.pearsonhighered.com/kroenke. The solutions for these questions will be
included in the Instructor’s Manual for each chapter]
A. You need about 20 PURCHASE transactions in the database. Write the needed SQL
statements for any needed additional PURCHASE transactions and insert the data into
your database.
B. Design a data warehouse star schema for a dimensional database named JRJ-DW. The
fact table measure will be RetailPrice.
Chapter Eight – Big Data, Data Warehouses and Business Intelligence Systems
Page 51 of 62
C. Create the JRJ-DW database in a DBMS product.
This is being created in SQL Server 2016.
See the file DBC-e08-MSSQL-JRJ-DW–Create-Tables.sql.
);
CREATE TABLE ARTIST(
ArtistID Int NOT NULL,
ArtistName Char(25) NOT NULL,
CONSTRAINT ARTIST_PK PRIMARY KEY(ArtistID)
);