Assignment 8 - Lookup Tables

Objectives

Learn to use lookup tables, and create a data structures for data lookup.

Assignment

This assignment uses the Case Study - Florida Housing Database

For this example we are going to use some data from the Census Bureau public use microdata samples (PUMS) which are described at http://www.census.gov/acs/www/data_documentation/public_use_microdata_sample/ from this site you are going to use the

1. Florida Housing Unit Records that are available at http://www.census.gov/acs/www/data_documentation/public_use_microdata_sample/ I have uploaded the data you need for this assignment;

FL_Housing_Data_2006.zip

2. Data Dictionary for the PUMS Data http://www.census.gov/acs/www/Downloads/data_documentation/pums/DataDict/PUMS_Data_Dictionary_2009-2011.pdf 

Steps

1. First locate the 2006-2008 3 years housing data from (1) and extract it. You will import this data (csv file) into a new database in SQL Server. You can call the database FloridaHousingDB. When importing use the Wizard and specify Flat File as the input source (no need to convert to Excel format).

2. Next you will create a set of Look-up tables for  REGION, TYPE, ACR, FS. You can name the lookup tables the same as the field name. For this part you will create a separate lookup table for each column above. You will need to enter the values in the lookup table.

3. This is duplicate information, however it will demonstrate various methods for lookup tables. Create the table structure below

CREATE TABLE Categories (

 [Name] NVARCHAR(50) PRIMARY KEY,

 [Description] NVARCHAR(200) 

)

CREATE TABLE Codes (

 [Name] NVARCHAR(50),

 [CategoryName] NVARCHAR(50) FOREIGN KEY REFERENCES Categories(Name),

 [Description] NVARCHAR(200) ) 

3. Create a stored procedure sp_insertCategory that takes 2 arguments Name and Description and creates a new row in category inserting the passed data.

4. Create a stored procedure sp_insertCode that takes 3 arguments Name, CategoryName, and Description - this stored procedure should create a new row in the table Codes. It should also check to ensure the Category exists in Categories and if not create it.

5. If you have done these 2 properly you should be able to run the following;

-- Add Categories

EXEC sp_InsertCategory 'REGION' , 'Region Code'

EXEC sp_InsertCategory 'TYPE', 'Type of Unit'

EXEC sp_InsertCategory 'ACR', 'Lot Size'

EXEC sp_InsertCategory 'FS', 'Yearly food stamp/Supplemental Nutrition Assistance Program recipiency'

-- Add codes
EXEC sp_InsertCode '1', 'REGION', 'Northeast'
EXEC sp_InsertCode '2', 'REGION', 'Midwest'
EXEC sp_InsertCode '3', 'REGION', 'South'
EXEC sp_InsertCode '4', 'REGION', 'West'
EXEC sp_InsertCode '9', 'REGION', 'Puerto Rico'

EXEC sp_InsertCode '1', 'TYPE', 'Housing Unit'
EXEC sp_InsertCode '2', 'TYPE', 'Institutional Group Quarters'
EXEC sp_InsertCode '1', 'TYPE', 'Noninstitutional Group Quarters'

EXEC sp_InsertCode 'b', 'ACR', 'N/A'
EXEC sp_InsertCode '1', 'ACR', 'House on less than one acre'
EXEC sp_InsertCode '2', 'ACR', 'House on one to less than ten acres'
EXEC sp_InsertCode '3', 'ACR', 'House on ten acres or more'

EXEC sp_InsertCode 'b', 'FS', 'N/A'
EXEC sp_InsertCode '1', 'FS', 'Yes'
EXEC sp_InsertCode '2', 'FS', 'No'

7. Now for the final step (2 queries) - using either lookup table or the category codes table you will write a query that returns the following shown below the example. This example is the count of the number of units for each of the code descriptions for ACR. This should be done with a SINGLE query.

SELECT   [Codes].[Description],  COUNT(*) As 'Number Units' 
FROM     RawData, Codes, Categories 
WHERE    RawData.ACR = Codes.Name
  AND    Codes.CategoryName = 'ACR'
  AND    Categories.Name = Codes.CategoryName
GROUP BY [Codes].[Description]

Description                        Number Units
House on less than one acre              170745
House on one to less than ten acres       20940
House on ten acres or more                 2586

You will Submit a query that returns the same thing breakdown for homes receiving food stamps and also homes not receiving food stamps (variable FS). The query must use the code description string values 'Yes' or 'No' as the query parameter - not the values 0,1, or b.  Here are the expected results;

Description                         Number Units
House on ten acres or more                7874
House on less than one acre               1078
House on one to less than ten acres         90

You will submit a screen capture of your query and the results of your query in SQL Server.  Submit as doc or pdf file.

Information

This may prove useful - here is a function to return a Description given a code name and a category name

CREATE FUNCTION func_GetDescription (

   @CodeName NVARCHAR(50),

   @CategoryName NVARCHAR(50))

 RETURNS NVARCHAR(200)

 AS

 BEGIN  

 RETURN (SELECT TOP 1 [Codes].[Description]

         FROM Codes, Categories

         WHERE Codes.CategoryName = Categories.Name

         AND Categories.Name = @CategoryName

         AND Codes.Name = @CodeName)

END 

For Example 

SELECT dbo.func_GetDescription('1','REGION')

will return Northeast.

Estimated Completion Time

Allot at least 10 hours to cover all materials and complete this assignment.

Supporting Lectures 

Lecture - Lookup Tables Part 1

Questions and Answers

I will post common questions and answers from the bulletin board here.

External Resources

Grading Criteria

Demonstration of creating the correct query for both queries (must have both) is worth 10 points.