Database Fundamentals

In this assignment you will start with an existing database and develop a data warehouse from it. You will take the existing data and de-normalize it, such that it is more powerful for analytical uses.

1. In the Week 7 Books and Resources area, locate the Microsoft Access database file named WPC Database. You will use this database as the starting point.
2. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>You need about twenty PROPERTY_SERVICE transactions in the database. Write the needed SQL statements for any needed additional PROPERTY_SERVICE transactions to the PROPERTY_SERVICE table and insert the data into your database (just make up reasonable data values on your own). Assuming you are using Microsoft Access, you may also enter these new records directly into the table.
3. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Design a data warehouse star schema for a dimensional database named GG-DW. The fact table measure will be HoursWorked.
4. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Create the GG-DW database in Access, using the menus: File, New, Blank Desktop Database.
5. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>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. You will include this information in the final report for the assignment.
6. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Write the complete set of SQL statements necessary to load the transformed data into the GG-DW database.
7. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Populate the GG-DW database, using the SQL statements you wrote to answer part F.
8. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Write an SQL query similar to the one shown in the text on page 456 that uses the hours worked per day as the measure.
9. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Write the SQL view equivalent of the SQL query you wrote to answer part H.
10. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Create the SQL view you wrote to answer part I in your GG-DW database.
11. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 0pt;’=””>Create a Microsoft Excel 2013 workbook named GG-DW-BI-Exercises.xlsx.
12. <p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 12pt;’=””>Using either the results of your SQL query from part H (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 J (create a Microsoft Excel data connection to the view), create an OLAP report similar to the OLAP report shown in Figure 8-17. (Hint: If you need help with the needed Microsoft Excel actions, search in the Microsoft Excel help system for more information.)

<p “times=”” new=”” roman”,”serif”;=”” 12pt;=”” normal;=”” white;=”” rgb(0,=”” 0,=”” 0);=”” 0in;=”” 12pt;’=””>Present your results from parts A through L in an illustrated narrative report form, in a single Microsoft Word document. You should discuss the overall process you went through, and provide a summary of your results. The report should include the SQL statements for each of the queries you created, and screen shots of the results you obtained from them.

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.