LookupTables_Documentation
docx
keyboard_arrow_up
School
University of Notre Dame *
*We aren’t endorsed by this school
Course
799
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
3
Uploaded by MasterWater6219
Lookup Tables Documentation
The assignment begins with the setup of a new database named 'FloridaHousingDB' in SQL Server. The housing data is imported into this database using the .csv file provided by Professor Eaglin using the Flat File Import wizard (I found it easier to deal with than the normal import wizard for this assignment).
Four lookup tables are created to store categorical data from the housing dataset: 'REGION', 'TYPE', 'ACR', and
'FS'. Each lookup table consists of two columns - 'Code' and 'Description', where 'Code' represents the unique identifier for a category, and 'Description' provides a human-readable label for the category.
Two stored procedures are developed to facilitate the insertion of data into the lookup tables. The first stored procedure, 'sp_InsertCategory', takes two arguments - 'Name' and 'Description' - and creates a new row in the 'Categories' table. The second stored procedure, 'sp_InsertCode', takes three arguments - 'Name', 'CategoryName', and 'Description' - and inserts a new row into the 'Codes' table. This stored procedure also ensures that the corresponding category exists in the 'Categories' table before inserting the code.
Several queries are executed to demonstrate the functionality of lookup tables and stored procedures in retrieving and analyzing data from the housing dataset.
sp_InsertCategory:
o
Parameters:
@Name: Represents the name of the category to be inserted.
@Description: Provides a description for the category.
o
Functionality:
Checks if the specified category name already exists in the 'Categories' table using the `IF
Query 2: Breakdown of Housing Units by Food Stamp Recipiency
This query provides a breakdown of housing units based on whether they receive food stamps ('Yes') or not ('No'). It utilizes the 'FS' lookup table to match the code descriptions with their respective categories.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help