Lookup Tables Part 1

Summary of Video

I demonstrate multiple methods of creating and using lookup tables. Examples are a single lookup table per field and using a 2 table lookup table system.

Prerequisites

The Data Dictionary used in this lecture is available at http://www.census.gov/acs/www/Downloads/data_documentation/pums/DataDict/PUMS_Data_Dictionary_2009-2011.pdf

The Data used in this lecture is uploaded here FL_Housing_Data_2006.zip

Video Link

Support Materials

Table Structure of using a 2 table design for handling look-up function.

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) )

The CatCodes View

CREATE VIEW CatCodes AS
SELECT    Codes.Name AS CodeName,
        Categories.Name AS CategoryName,
        [Codes].[Description] AS CodeDescription,
        [Categories].[Description] AS CategoryDescription
FROM    Categories, Codes
WHERE    Codes.CategoryName = Categories.Name

Queries used in the Video

SELECT DISTINCT(ACR) FROM RawData

SELECT COUNT(*) FROM RawData WHERE RawData.ACR = '1' 

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]   

SELECT   [ACR].[Description],  COUNT(*) As 'Number Units' 
FROM     RawData, ACR
WHERE    RawData.ACR = [ACR].[Code]
GROUP BY [ACR].[Description]

SELECT * FROM CatCodes
SELECT     [CatCodes].[CodeDescription], COUNT(*) As 'Number Units' 
FROM     RawData, CatCodes 
    WHERE     RawData.ACR = CatCodes.CodeName
    AND  CatCodes.CategoryName = 'ACR'
    GROUP BY [CatCodes].[CodeDescription]