A Guide to Sql 9th Edition Chapter 8 Problem 9scg
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 file DBC-e06-MSSQL-GG-CH08-Insert-New-Data.sql .
/*************************************************************************/
/* */
/* Kroenke and Auer - Database Concepts (6th Edition) Chapter 08 */
/* */
/* Additional Garden Glory Database Data */
/* */
/* These are the Microsoft SQL Server 2012 SQL code solutions */
/* */
/*************************************************************************/
/***** SERVICE *******************************************************/
INSERT INTO [SERVICE] VALUES ( 1 , 1 , '05-JUN-12' , 4.50 );
INSERT INTO [SERVICE] VALUES ( 3 , 3 , '08-JUN-12' , 4.50 );
INSERT INTO [SERVICE] VALUES ( 2 , 2 , '08-JUN-12' , 2.75 );
INSERT INTO [SERVICE] VALUES ( 6 , 5 , '10-JUN-12' , 2.50 );
INSERT INTO [SERVICE] VALUES ( 5 , 4 , '12-JUN-12' , 7.50 );
INSERT INTO [SERVICE] VALUES ( 8 , 4 , '15-JUN-12' , 2.75 );
INSERT INTO [SERVICE] VALUES ( 4 , 1 , '19-JUN-12' , 3.00 );
INSERT INTO [SERVICE] VALUES ( 7 , 2 , '19-JUN-12' , 2.50 );
INSERT INTO [SERVICE] VALUES ( 1 , 1 , '05-JUL-12' , 4.50 );
INSERT INTO [SERVICE] VALUES ( 3 , 3 , '08-JUL-12' , 4.50 );
INSERT INTO [SERVICE] VALUES ( 2 , 2 , '08-JUL-12' , 2.75 );
INSERT INTO [SERVICE] VALUES ( 6 , 5 , '10-JUL-12' , 2.50 );
INSERT INTO [SERVICE] VALUES ( 5 , 4 , '12-JUL-12' , 7.50 );
INSERT INTO [SERVICE] VALUES ( 8 , 4 , '15-JUL-12' , 2.75 );
INSERT INTO [SERVICE] VALUES ( 4 , 1 , '19-JUL-12' , 3.00 );
INSERT INTO [SERVICE] VALUES ( 7 , 2 , '19-JUL-12' , 2.50 );
/***************************************************************************/
B. Design a data warehouse star schema for a dimensional database named GG-DW. The fact table measure will be HoursWorked.
We'll do a fairly simple dimensional database. We'll use a fact table named SERVICE_HOURS, a TIMELINE dimension table, a PROPERTY dimension table and an EMPLOYEE dimension table.
C. Create the GG-DW database in a DBMS product.
This was done in SQL Server Express.
See the file DBC-e06-MSSQL-GG-DW-Create-Tables.sql .
CREATE TABLE TIMELINE (
TimeID Int NOT NULL,
[Date] DateTime NOT NULL,
MonthID Int NOT NULL,
MonthText Char ( 15 ) NOT NULL,
QuarterID Int NOT NULL,
QuarterText Char ( 10 ) NOT NULL,
[Year] Int NOT NULL,
CONSTRAINT TIMELINE_PK PRIMARY KEY ( TimeID )
);
CREATE TABLE [PROPERTY] (
PropertyID Int NOT NULL,
PropertyName Char ( 50 ) NOT NULL,
City Char ( 35 ) NOT NULL,
[State] Char ( 2 ) NOT NULL,
Zip Char ( 10 ) NOT NULL,
CONSTRAINT PROPERTY_PK PRIMARY KEY ( PropertyID ),
);
CREATE TABLE EMPLOYEE (
EmployeeID Int NOT NULL,
LastName Char ( 25 ) NOT NULL,
FirstName Char ( 25 ) NOT NULL,
ExperienceLevel Char ( 15 ) NOT NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY ( EmployeeID )
);
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
PRIMARY KEY ( TimeID , PropertyID , EmployeeID ),
CONSTRAINT SH_TIMELINE_FK FOREIGN KEY ( TimeID )
REFERENCES TIMELINE ( TimeID )
ON UPDATE NO ACTION
ON DELETE NO ACTION ,
CONSTRAINT SH_PROPERTY_FK FOREIGN KEY ( PropertyID )
REFERENCES PROPERTY ( PropertyID )
ON UPDATE NO ACTION
ON DELETE NO ACTION ,
CONSTRAINT SH_EMPLOYEE_FK FOREIGN KEY ( EmployeeID )
REFERENCES EMPLOYEE ( EmployeeID )
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
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.
There will be none needed. We are excluding some non-aggregating data in the GARDEN_GLORY database, but we are not changing anything.
E. Write the complete set of SQL statements necessary to load the transformed data into the GG-DW database.
See the file DBC-e06-MSSQL-GG-DW-Insert-Data.sql .
/***** TIMELINE ******************************************************/
INSERT INTO TIMELINE VALUES (
41034 , '05-May-12' , 5 , 'May' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41037 , '08-May-12' , 5 , 'May' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41039 , '10-May-12' , 5 , 'May' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41041 , '12-May-12' , 5 , 'May' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41044 , '15-May-12' , 5 , 'May' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41048 , '19-May-12' , 5 , 'May' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41065 , '05-Jun-12' , 6 , 'Jun' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41068 , '08-Jun-12' , 6 , 'Jun' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41070 , '10-Jun-12' , 6 , 'Jun' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41072 , '12-Jun-12' , 6 , 'Jun' , 2 , 'Qtr2' , 20122 );
INSERT INTO TIMELINE VALUES (
41075 , '15-Jun-12' , 6 , 'Jun' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41079 , '19-Jun-12' , 6 , 'Jun' , 2 , 'Qtr2' , 2012 );
INSERT INTO TIMELINE VALUES (
41095 , '05-Jul-12' , 7 , 'Jul' , 3 , 'Qtr3' , 2012 );
INSERT INTO TIMELINE VALUES (
41098 , '08-Jul-12' , 7 , 'Jul' , 3 , 'Qtr3' , 2012 );
INSERT INTO TIMELINE VALUES (
41100 , '10-Jul-12' , 7 , 'Jul' , 3 , 'Qtr3' , 2012 );
INSERT INTO TIMELINE VALUES (
41102 , '12-Jul-12' , 7 , 'Jul' , 3 , 'Qtr3' , 2012 );
INSERT INTO TIMELINE VALUES (
41105 , '15-Jul-12' , 7 , 'Jul' , 3 , 'Qtr3' , 2012 );
INSERT INTO TIMELINE VALUES (
41109 , '19-Jul-12' , 7 , 'Jul' , 3 , 'Qtr3' , 2012 );
/***** EMPLOYEE ******************************************************/
INSERT INTO EMPLOYEE VALUES (
1 , 'Smith' , 'Sam' , 'Master' );
INSERT INTO EMPLOYEE VALUES (
2 , 'Evanston' , 'John' , 'Senior' );
INSERT INTO EMPLOYEE VALUES (
3 , 'Murray' , 'Dale' , 'Junior' );
INSERT INTO EMPLOYEE VALUES (
4 , 'Murphy' , 'Jerry' , 'Master' );
INSERT INTO EMPLOYEE VALUES (
5 , 'Fontaine' , 'Joan' , 'Senior' );
/***** PROPERTY *******************************************************/
INSERT INTO [PROPERTY] VALUES (
1 , 'Eastlake Building' , 'Seattle' , 'WA' , '98119' );
INSERT INTO [PROPERTY] VALUES (
2 , 'Elm St Apts' , 'Lynwood' , 'WA' , '98223' );
INSERT INTO [PROPERTY] VALUES (
3 , 'Jefferson Hill' , 'Bellevue' , 'WA' , '98007' );
INSERT INTO [PROPERTY] VALUES (
4 , 'Lake View Apts' , 'Redmond' , 'WA' , '98052' );
INSERT INTO [PROPERTY] VALUES (
5 , 'Kodak Heights Apts' , 'Rochester' , 'NY' , '14604' );
INSERT INTO [PROPERTY] VALUES (
6 , 'Private Residence' , 'Bellevue' , 'WA' , '98007' );
INSERT INTO [PROPERTY] VALUES (
7 , 'Private Residence' , 'Bellevue' , 'WA' , '98007' );
INSERT INTO [PROPERTY] VALUES (
8 , 'Private Residence' , 'Rochester' , 'NY' , '14604' );
/***** SERVICE **********************************************************/
INSERT INTO SERVICE_HOURS VALUES ( 41034 , 1 , 1 , 4.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41037 , 3 , 3 , 4.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41039 , 2 , 2 , 2.75 );
INSERT INTO SERVICE_HOURS VALUES ( 41039 , 6 , 5 , 2.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41041 , 5 , 4 , 7.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41044 , 8 , 4 , 2.75 );
INSERT INTO SERVICE_HOURS VALUES ( 41048 , 4 , 1 , 3.00 );
INSERT INTO SERVICE_HOURS VALUES ( 41048 , 7 , 2 , 2.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41065 , 1 , 1 , 4.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41068 , 3 , 3 , 4.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41068 , 2 , 2 , 2.75 );
INSERT INTO SERVICE_HOURS VALUES ( 41070 , 6 , 5 , 2.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41072 , 5 , 4 , 7.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41075 , 8 , 4 , 2.75 );
INSERT INTO SERVICE_HOURS VALUES ( 41079 , 4 , 1 , 3.00 );
INSERT INTO SERVICE_HOURS VALUES ( 41079 , 7 , 2 , 2.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41095 , 1 , 1 , 4.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41098 , 3 , 3 , 4.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41098 , 2 , 2 , 2.75 );
INSERT INTO SERVICE_HOURS VALUES ( 41100 , 6 , 5 , 2.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41102 , 5 , 4 , 7.50 );
INSERT INTO SERVICE_HOURS VALUES ( 41105 , 8 , 4 , 2.75 );
INSERT INTO SERVICE_HOURS VALUES ( 41109 , 4 , 1 , 3.00 );
INSERT INTO SERVICE_HOURS VALUES ( 41109 , 7 , 2 , 2.50 );
/**************************************************************************/
F. Populate the GG-DW database, using the SQL statements you wrote to answer part E .
This is self-explanatory.
G. Write an SQL query similar to the one shown in the text on page 420 that uses the hours worked per day as the measure.
See the file DBC-e06-MSSQL-GG-DW-CH08-Questions-G-H.sql .
/* ******** Question G Query ******************************************/
SELECT E . EmployeeID , E . ExperienceLevel ,
P . PropertyID , P . City , P . State , P . Zip ,
SUM ( SH . HoursWorked ) AS TotalHoursWorked
FROM EMPLOYEE E , SERVICE_HOURS SH , [PROPERTY] P
WHERE E . EmployeeID = SH . EmployeeID
AND P . PropertyID = SH . PropertyID
GROUP BY E . EmployeeID , E . ExperienceLevel ,
P . PropertyID , P . City , P . State , P . Zip
ORDER BY E . EmployeeID , E . ExperienceLevel ,
P . State , P . City , P . Zip ;
H. Write the SQL view equivalent of the SQL query you wrote to answer part G.
See the file DBC-e06-MSSQL-GG-DW-CH08-Questions-G-H.sql .
/* ******** Guestion H View Defintion *********************************/
CREATE VIEW GGDWTotalServiceHoursView AS
SELECT E . EmployeeID , E . ExperienceLevel ,
P . PropertyID , P . City , P . State , P . Zip ,
SUM ( SH . HoursWorked ) AS TotalHoursWorked
FROM EMPLOYEE E , SERVICE_HOURS SH , [PROPERTY] P
WHERE E . EmployeeID = SH . EmployeeID
AND P . PropertyID = SH . PropertyID
GROUP BY E . EmployeeID , E . ExperienceLevel ,
P . PropertyID , P . City , P . State , P . Zip ;
I. Create the SQL view you wrote to answer part H in your GG-DW database.
This is self-explanatory.
J. Create the Microsoft Excel 2010 workbook named GG-DW-BI-Exercises.xlsx.
This is self-explanatory. See file: DBC-e06-GG-DW-BI-Exercises.xlsx .
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.)
The query data and Pivot Table are shown below.
A Guide to Sql 9th Edition Chapter 8 Problem 9scg
Source: https://scholaron.com/textbook-solutions/solutions-for-database-concepts-6th-edition-chapter-8-2-68124-9780132742924