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
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.
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.
Allot at least 10 hours to cover all materials and complete this assignment.
Lecture - Lookup Tables Part 1
I will post common questions and answers from the bulletin board here.
Demonstration of creating the correct query for both queries (must have both) is worth 10 points.