Assignments for ACS-4904Feb 26, 2010

From the list below make 4 selections for your next 4 assignments.

Assignments

#3 and #4 due Mar 18, 2010

#5 and #6 due Apr 5, 2010

All SQL scripts and views must be commented to aid readability.

All queries used to display results must be coded as SQL views.

When describing an ETL process, consider using a UML activity diagram.

  1. Implement the employee hierarchy using a bridge table in your Northwind Dimensional database (for the reports to relationship). Develop queries to test your implementation.
  1. Implement an application to maintain the hierarchy bridge table. That is, an application that allows one to insert and delete nodes in the hierarchy. Ease of use by the end-user is important here.
  1. Implement the employee hierarchy using nested sets in your Northwind Dimensional database (for the reports to relationship). Develop queries to test your implementation.
  1. Implement an application to maintain the employee hierarchy where the implementation is based on nested sets. That is, an application that allows one to insert and delete nodes in the hierarchy. Ease of use by the end-user is important here.
  1. Implementation of value-banding (p 207++) in your Northwind dimensional database. You must generate meaningful queriesthat access the facts in different ways, with and without the banding table.
  1. Create the dimensional database shown in Figure 9.5 including the fact table, Date dimension, Account dimension and Transaction Type dimension. Load the dimension and fact tables with sample data. Create an SQL view that implements the code shown on page 209.
  1. Figure 8.7 shows an HR survey schema. Adapt this idea for the course evaluations that are done at the UofW. You need to provide sample data. You need to develop some queries to show how the data can be utilized.
  1. Using the same database as for Assignment 2, develop an SQL script to maintain a Type 3 dimension. Develop queries that can be used to test your implementation. You must include a description of the ETL process.
  1. Create the database required for Figure 8.5 on page 195 (skills group keyword dimension outrigger) and for Figure 8.6 on page 197 (delimited skills list dimension outrigger). Create views for each that allow us to list the employees with at least one of two skills. Insert sufficient data in your database for testing purposes. We should be able to find multiple employees with Unix or Linux skills.
  1. Implement the design in Figure 9.3. Provide reasonable test data. Provide a view that will list a customer and the January month ending balance of each account he/she is associated with.
  1. Implement ETL to update the fact table in your Assignment 2 database when new order detail lines have been added to the source database. A new fact must be related to the most recent type 2 dimension record for the pertinent entity. You capture pertinent source records using a technique from Assignment 2. You must include a description of the ETL process
  1. Implement ETL to update existing facts in your Assignment 2 database. A fact is updated when the shipped date is assigned a value (i.e. shipped date was null and is given a value). You capture pertinent source records using a technique from Assignment 2. You must include a description of the ETL process.
  1. Normalize two dimensions from Assignment 1:

a)The Date dimension by creating a Month dimension.

b)The Product dimension by creating a Category dimension.

You must show that your new database yields the same results for the queries that were part of Assignment 1.

  1. Create two mini-dimensions in the database you created for Assignment 1:

a)Category as a mini-dimension

b)Month as a mini-dimension

You must show that your new database yields the same results for the queries that were part of Assignment 1.

  1. Using your work from #12 or #13, create an aggregate fact table that holds metrics such as quantity and dollar value, and where the dimensions are OrderMonth, Category, Customer, Employee. We say that OrderDate is collapsed to OrderMonth, Product is collapsed to Category, and the dimensions of ShippedDate, Supplier, and Order are lost. Create two queries that answer the same question, but where one uses the base schema and the other uses the aggregate schema.