• If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!


Spring 2103 COP4709 Assignment 8

Page history last edited by Dr. Ron Eaglin 11 years, 3 months ago

 Assignment 8 - Lookup Tables




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




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;




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



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 (
 [Description] NVARCHAR(200) 
 [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.




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))
 RETURN (SELECT TOP 1 [Codes].[Description]
         FROM Codes, Categories
         WHERE Codes.CategoryName = Categories.Name
         AND Categories.Name = @CategoryName
         AND Codes.Name = @CodeName)


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.



Comments (0)

You don't have permission to comment on this page.