Sql Program to Create a Charity Database System Assignment Solution.

Instructions

Objective

Write a sql program to create a charity database system in sql.

Requirements and Specifications

Project Overview

You are tasked with finishing development of a section of an existing database system (Champlain Charity LLC). The system is for a local charity and includes multiple schemas. Having been developed over time, the existing data may have some inconsistencies and existing stored procedures and functions may have been developed using older or varying techniques to accomplish similar tasks. This is normal in any production database. In fact, most production databases have some functionality that is no longer used but is left in place due to the difficulty of altering RDBMS (Relational Database Management System).

The contractor that was hired to create a volunteer management schema for Champlain Charities, LLC was not able to complete the SQL assignment. You were hired to finish the task. The project will require the use of two schemas: Person and Volunteer. Your work is limited to the database and does not include designing an interface. The volunteer functionality will be used to track volunteers for several organizations within the charity, though only the food shelf uses the database directly.

The Volunteers work for:

Food Shelf
Administration
Housing
Outreach
Fund Raising

Job functions by Organization:

Food Shelf: Intake Worker, Warehouse worker, Inventory, Vendor Management
Administration: Administrative Assistant, Financial Assistant
Housing: Coordinator, Client Outreach
Outreach: Media Relations, Marketing Assistant
Fundraising: Charity Outreach, Fundraiser, Event Coordinator, Event Volunteer

The contract requires that you develop stored procedures, functions and tables, as needed to support basic data insert, update and delete functionality as well as basic reporting needs. The insert, update and delete functionality shall use transaction processing and error handling. Tables with names beginning with LU are lookup tables and they do not need stored procedures, though you will need to insert data for the client.

As part of your task, you will need to review existing volunteer tables and any relevant tables and stored procedures in Person to determine whether modifications need to be done and whether there is any overlap in functionality that can be combined, or removed because it is not needed.

The client wants volunteer contact information and emergency contact information to be separate from information in the Person Schema due to concerns with privacy regulations.

The volunteer functionality relies heavily on the development of a scheduling system for volunteers. The functionality shall include information about when a volunteer is scheduled and where. A volunteer is not required to work every week, but may work more than once a week. Additionally, each volunteer may work for more than one organization and in more than one position.

The Database contains the following Schemas:

DBO (Database Owner): This is a default schema that is automatically used on table creation, if no other schema is specified. It can also be used if the database only needs one schema. It has no special functionality over other schema names. In our database, the dbo schema is used as a catchall for tables that do not fit other schemas. You will not use this schema in your project.
Person: The Person schema contains information about people that either work for the charity, volunteer, or are clients of the charity. You will utilize part of this schema to hold demographic information about individual volunteers.
Food shelf: This schema manages clients of the charity’s food shelf program. It is the heart of the client database for the organization. You will not use this schema, but the structure of the tables, functions and stored procedures in this schema might be useful as examples to review in developing your code.
Maintenance: Tracks database maintenance and is not relevant to your project.
Message: Is for message journaling and is not relevant to your project.
Signature: Holds tables that store encrypted client signatures captured by signature pads. This is not relevant to your project.
SSN: This schema holds only one table to secure client Social Security Numbers and is not relevant to your project.
Volunteer: This schema is the heart of your project and contains some tables. You may decide to use, modify, or replace the existing tables.

Source code

CREATE TYPE [dbo].[BatchProcessPersonID] AS TABLE(
 [RowID] [int] NOT NULL,
 [PersonID] [int] NULL,
 [ActionToTake] [nvarchar](50) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[LUTableChanges] Script Date: 1/6/2018 9:07:41 PM ******/
CREATE TYPE [dbo].[LUTableChanges] AS TABLE(
 [TableRows] [int] IDENTITY(1,1) NOT NULL,
 [TableName] [nvarchar](50) NULL,
 [ActionTaken] [nvarchar](50) NULL,
 [Oldvalue] [nvarchar](max) NULL,
 [OldLUTableID] [int] NULL,
 [NewValue] [nvarchar](max) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[LUTableChangesa] Script Date: 1/6/2018 9:07:41 PM ******/
CREATE TYPE [dbo].[LUTableChangesa] AS TABLE(
 [TableRows] [int] IDENTITY(1,1) NOT NULL,
 [TableName] [nvarchar](50) NULL,
 [ActionTaken] [nvarchar](50) NULL,
 [Oldvalue] [nvarchar](max) NULL,
 [OldLUTableID] [int] NULL,
 [NewValue] [nvarchar](max) NULL
)
GO
/****** Object: UserDefinedFunction [dbo].[ActionToTake] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Gary Savard
-- Create date: 28 April 11
-- Description: This function validates the actions to be taken when a household member becomes the client, etc.
-- If the input value is valid, then the function returns 1, if not, 0.
-- =============================================
CREATE FUNCTION [dbo].[ActionToTake]
(
 @Source NVarchar(8)
)
RETURNS int
AS
BEGIN
 DECLARE
 @Result int,
 @Temp NVarchar(8)
 SET @Result =0;
 SET @Temp ='';
 SELECT @Temp = LTRIM(RTRIM(@Source));--Clean any spaces out
 IF @Temp = 'DEMOTE'
  SET @Result = 1;
 ELSE IF @Temp ='DELETE'
  SET @Result = 1;
 ELSE IF @Temp ='NEWHOUSEHOLD'
  SET @Result = 1;
 ELSE
  SET @Result =0;
 RETURN @Result;
END
GO
/****** Object: UserDefinedFunction [dbo].[CheckRecertifyDate] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Gary Savard
-- Create date: 26 Oct 10
-- Description: This function determines whether a customer must recertify based on the 1 year criteria
-- The PersonID input is optional and, if it is zero then we just work based on the LastCertificationDate input
-- If the PersonID is >0 then we get the last certification date from the foodshelf.foodshelfcertification table and ignore
--the input date.
-- =============================================
CREATE FUNCTION [dbo].[CheckRecertifyDate]
(
 @PersonID int =0, --We can just do the calculation based on input date time, or by getting from the Foodshelf.FoodshelfCertification table
 @LastCertificationDate DateTime
)
RETURNS bit
AS
BEGIN
 DECLARE
 @Result BIT,
 @TempDate DATETIME
 SET @Result =0; --Could debate whether 0 or 1 is better for default.
 SET @TempDate =SYSDATETIME();
 IF @PersonID =0
 BEGIN
  IF (DATEDIFF(DD,@LastCertificationDate,SYSDATETIME()) >= 365)
   BEGIN
    SET @Result =1;
    RETURN @RESULT;
   END
  ELSE
   BEGIN
    SET @Result =0;
    RETURN @Result;
   END
 END
 ELSE
  BEGIN
   SELECT @TempDate = MAX(LastCertificationDate)
   FROM Foodshelf.FoodShelfCertification Where PersonID = @PersonID
   IF (DATEDIFF(DD,@TempDate,SYSDATETIME()) >= 365)
   BEGIN
    SET @Result =1;
    RETURN @RESULT;
   END
  ELSE
   BEGIN
    SET @Result =0;
    RETURN @Result;
   END
  END
 RETURN @Result;
END
GO
/****** Object: UserDefinedFunction [dbo].[ValidateDataSource] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Gary Savard
-- Create date: 26 Oct 10
-- Description: This function takes in a set of possible values for origin of a new record: FOOD,WX,MICRO,TENANT,COMMACT
-- If the input value is valid, then the function returns 1, if not, 0.
-- =============================================
CREATE FUNCTION [dbo].[ValidateDataSource]
(
 @Source NVarchar(8)
)
RETURNS int
AS
BEGIN
 DECLARE
 @Result int,
 @Temp NVarchar(8)
 SET @Result =0;
 SET @Temp ='';
 SELECT @Temp = LTRIM(RTRIM(@Source));--Clean any spaces out
 IF @Temp = 'FOOD'
  SET @Result = 1;
 ELSE IF @Temp ='WX'
  SET @Result = 1;
 ELSE IF @Temp ='MICRO'
  SET @Result = 1;
 ELSE IF @Temp ='TENANT'
  SET @Result = 1;
 ELSE IF @Temp ='COMMACT'
  SET @Result = 1;
 ELSE IF @Temp ='ADMIN'
  SET @Result =1;
 ELSE
  SET @Result =0;
 RETURN @Result;
END
GO
/****** Object: Table [Foodshelf].[FoodshelfRules] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Foodshelf].[FoodshelfRules](
 [FoodshelfRulesID] [int] IDENTITY(1,1) NOT NULL,
 [FoodshelfRules] [nvarchar](max) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: View [dbo].[vGetFoodshelfRules] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vGetFoodshelfRules]
AS
SELECT FoodshelfRules
FROM Foodshelf.FoodshelfRules
WHERE (ModifiedDate =
                          (SELECT MAX(ModifiedDate) AS Expr1
                            FROM Foodshelf.FoodshelfRules AS FoodshelfRules_1))
GO
/****** Object: Table [Foodshelf].[FoodTypeGiven] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Foodshelf].[FoodTypeGiven](
 [PersonID] [int] NOT NULL,
 [FoodTypeGivenID] [int] IDENTITY(1,1) NOT NULL,
 [ReceivedDate] [datetime] NOT NULL,
 [FoodGiven] [Foodshelf].[GroceryOrBreadProduce] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_FoodTypeGiven] PRIMARY KEY CLUSTERED
(
 [PersonID] ASC,
 [FoodTypeGivenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[GetGroceryHistory] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Gary Savard
-- Create date: 14 April, 2011
-- Description: Returns the number of times a client received groceries from the foodshelf in the past 6 months.
-- =============================================
CREATE FUNCTION [dbo].[GetGroceryHistory]
(@PersonID int)
RETURNS TABLE
AS
RETURN
  select ReceivedDate from foodshelf.FoodTypeGiven where PersonID =@PersonID
  and ReceivedDate between DATEADD(m,-6,(CONVERT(DATE,GETDATE()))) and CONVERT(DATE,GETDATE()) and FoodGiven =0;
GO
/****** Object: Table [dbo].[GuidelinesByProgram] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GuidelinesByProgram](
 [GuidelinesID] [int] IDENTITY(1,1) NOT NULL,
 [ProgramID] [int] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_GuidelinesByProgram] PRIMARY KEY CLUSTERED
(
 [GuidelinesID] ASC,
 [ProgramID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[HouseholdTransferRequest] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HouseholdTransferRequest](
 [ClientMaintenanceID] [int] IDENTITY(1,1) NOT NULL,
 [OriginalClientID] [int] NULL,
 [HouseholdMemberToMoveID] [int] NOT NULL,
 [NewClientID] [int] NOT NULL,
 [ActionRequested] [int] NOT NULL,
 [Notes] [nvarchar](max) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[JournalClientFamilyChanges] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JournalClientFamilyChanges](
 [JournalClientFamilyChangesID] [int] IDENTITY(1,1) NOT NULL,
 [HouseholdMembersID] [int] NOT NULL,
 [PreviousClientsID] [int] NULL,
 [NewClientsID] [int] NULL,
 [ActionTaken] [nvarchar](50) NULL,
 [Notes] [nvarchar](max) NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[JournalLookupTableChanges] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JournalLookupTableChanges](
 [JournalLookupTableID] [int] IDENTITY(1,1) NOT NULL,
 [TableName] [nvarchar](50) NULL,
 [ActionTaken] [nvarchar](50) NOT NULL,
 [OldValue] [nvarchar](max) NULL,
 [OldLUTableID] [int] NULL,
 [NewValue] [nvarchar](max) NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[LUHouseholdTransferOptions] Script Date: 1/6/2018 9:07:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LUHouseholdTransferOptions](
 [HouseholdTransferActionID] [int] IDENTITY(1,1) NOT NULL,
 [ActionToTake] [nvarchar](50) NOT NULL,
 [ActionToTakeDescription] [nvarchar](max) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[LUStateFederalProgram] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LUStateFederalProgram](
 [StateFederalProgramID] [int] IDENTITY(1,1) NOT NULL,
 [IsFederal] [bit] NOT NULL,
 [ProgramName] [nvarchar](50) NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_LUStateFederalProgram] PRIMARY KEY CLUSTERED
(
 [StateFederalProgramID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Organization] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Organization](
 [OrganizationID] [int] NOT NULL,
 [OrganizationName] [nvarchar](50) NOT NULL,
 [Phone] [nchar](10) NULL,
 [Address] [nvarchar](50) NULL,
 [Note] [nvarchar](max) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED
(
 [OrganizationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProgramGuidelines] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProgramGuidelines](
 [ProgramGuidelinesID] [int] IDENTITY(1,1) NOT NULL,
 [GuidelinesID] [int] NOT NULL,
 [Guideline1] [nvarchar](max) NOT NULL,
 [Guideline2] [nvarchar](max) NULL,
 [Guideline3] [nvarchar](max) NULL,
 [Guideline4] [nvarchar](max) NULL,
 CONSTRAINT [PK_ProgramGuidelines] PRIMARY KEY CLUSTERED
(
 [ProgramGuidelinesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Foodshelf].[FoodShelfCertification] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Foodshelf].[FoodShelfCertification](
 [FoodShelfCertificationID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [CertificationTextID] [int] NOT NULL,
 [LastCertificationDate] [datetime] NOT NULL,
 [IsPaperCertification] [bit] NULL,
 [PaperCertificationDate] [datetime] NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_FoodShelfCertification_1] PRIMARY KEY CLUSTERED
(
 [FoodShelfCertificationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Foodshelf].[HouseholdNotes] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Foodshelf].[HouseholdNotes](
 [HouseholdNotesID] [int] IDENTITY(1,1) NOT NULL,
 [ClientPersonID] [int] NOT NULL,
 [HouseholdMemberPersonID] [int] NULL,
 [Notes] [nvarchar](max) NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
 [HouseholdNotesID] ASC,
 [ClientPersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Foodshelf].[LUCertificationText] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Foodshelf].[LUCertificationText](
 [CertificationTextID] [int] IDENTITY(1,1) NOT NULL,
 [CertificationText] [nvarchar](max) NOT NULL,
 [GuidelinesText] [nvarchar](max) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Foodshelf].[Signature] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Foodshelf].[Signature](
 [SignatureID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [FoodShelfCertificationID] [int] NOT NULL,
 [Signature] [varchar](max) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Signature] PRIMARY KEY CLUSTERED
(
 [SignatureID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Maintenance].[UserModifiableTables] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Maintenance].[UserModifiableTables](
 [UserModifiableTablesID] [int] IDENTITY(1,1) NOT NULL,
 [FromSchema] [nvarchar](30) NOT NULL,
 [FromTable] [nchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [Message].[Msg] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Message].[Msg](
 [MessageID] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [dbo].[Name] NULL,
 [ErrMsg] [varchar](50) NULL,
 [ErrNo] [varchar](50) NULL,
 [StoredProcedure] [varchar](100) NULL,
 [inputValues] [varchar](max) NULL,
 [ErrDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Person].[Address] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Address](
 [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [PersonID] [int] NOT NULL,
 [AddressLine1] [nvarchar](60) NOT NULL,
 [AddressLine2] [nvarchar](60) NULL,
 [City] [nvarchar](30) NOT NULL,
 [County] [nvarchar](30) NULL,
 [State] [nvarchar](2) NOT NULL,
 [PostalCode] [nvarchar](10) NULL,
 [AddressTypeID] [int] NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
 [AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[Demographics] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Demographics](
 [DemographicsID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [EducationCategoryID] [int] NULL,
 [HousingStatus] [nvarchar](50) NULL,
 [NumberInHousehold] [int] NULL,
 [Gender] [nvarchar](2) NULL,
 [Race] [nvarchar](20) NULL,
 [CountryOfOrigin] [nvarchar](70) NULL,
 [Ethnicity] [nvarchar](20) NULL,
 [Disability] [dbo].[NoYesMaybe] NOT NULL,
 [CSFP] [dbo].[NoYesMaybe] NOT NULL,
 [Veteran] [dbo].[NoYesMaybe] NOT NULL,
 [HealthInsurance] [dbo].[NoYesMaybe] NOT NULL,
 [FamilyStatusType] [nvarchar](50) NULL,
 [InsuranceType] [nvarchar](50) NULL,
 [FoodStamps] [dbo].[NoYesMaybe] NOT NULL,
 [HomeboundDelivery] [dbo].[NoYesMaybe] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK__Deomogra__E81ABA19151B244E] PRIMARY KEY CLUSTERED
(
 [DemographicsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[FamilyRelationships] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[FamilyRelationships](
 [FamilyRelationshipID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [ClientPersonID] [int] NOT NULL,
 [RelationshipID] [int] NOT NULL,
 [RelationDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_FamilyRelationships] PRIMARY KEY CLUSTERED
(
 [FamilyRelationshipID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[Income] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Income](
 [IncomeSourceRowID] [int] IDENTITY(1,1) NOT NULL,
 [IncomeSourceID] [int] NOT NULL,
 [PersonID] [int] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Income] PRIMARY KEY CLUSTERED
(
 [IncomeSourceRowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LanguagesSpoken] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LanguagesSpoken](
 [LanguageID] [int] NOT NULL,
 [PersonID] [int] NOT NULL,
 [IsPrimaryLanguage] [bit] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [rowid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
 [LanguagesSpokenID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_LanguagesSpoken] PRIMARY KEY CLUSTERED
(
 [LanguageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUAddressType] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUAddressType](
 [AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
 [AddressType] [nvarchar](10) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
(
 [AddressTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUCity] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUCity](
 [CityID] [int] IDENTITY(1,1) NOT NULL,
 [StateID] [int] NOT NULL,
 [CountyID] [int] NOT NULL,
 [PostalCodeID] [int] NOT NULL,
 [CityName] [nvarchar](30) NOT NULL,
 [CityAbbreviation] [nvarchar](30) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_LUCity] PRIMARY KEY CLUSTERED
(
 [CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUContactType] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUContactType](
 [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY CLUSTERED
(
 [ContactTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUCountryOfOrigin] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUCountryOfOrigin](
 [CountryID] [int] IDENTITY(1,1) NOT NULL,
 [CountryAbbreviation] [nvarchar](50) NULL,
 [CountryName] [nvarchar](70) NOT NULL,
 [FIPSCode] [nvarchar](2) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUCounty] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUCounty](
 [CountyID] [int] IDENTITY(1,1) NOT NULL,
 [CityID] [int] NOT NULL,
 [StateID] [int] NOT NULL,
 [CountyName] [nvarchar](20) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_LUCounty] PRIMARY KEY CLUSTERED
(
 [CountyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUEducationCategory] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUEducationCategory](
 [EducationCategoryID] [int] IDENTITY(1,1) NOT NULL,
 [EducationLevel] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUEthnicity] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUEthnicity](
 [EthnicityID] [int] IDENTITY(1,1) NOT NULL,
 [EthnicityAbbreviation] [nvarchar](20) NOT NULL,
 [EthnicityDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUFamilyRelationship] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUFamilyRelationship](
 [FamilyRelationshipID] [int] IDENTITY(1,1) NOT NULL,
 [RelationshipCode] [nvarchar](30) NOT NULL,
 [RelationshipDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUFamilyStatusType] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUFamilyStatusType](
 [FamilyStatusTypeID] [int] IDENTITY(1,1) NOT NULL,
 [FamilyStatusType] [nvarchar](50) NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUHousingStatus] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUHousingStatus](
 [HousingStatusID] [int] IDENTITY(1,1) NOT NULL,
 [HousingStatus] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUIncomeSource] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUIncomeSource](
 [IncomeSourceID] [int] IDENTITY(1,1) NOT NULL,
 [IncomeSourceAbbreviation] [nvarchar](20) NOT NULL,
 [IncomeSourceDescription] [nvarchar](50) NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUInsuranceType] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUInsuranceType](
 [InsuranceTypeID] [int] IDENTITY(1,1) NOT NULL,
 [InsuranceType] [nvarchar](50) NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LULanguage] Script Date: 1/6/2018 9:07:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LULanguage](
 [LanguageID] [int] IDENTITY(1,1) NOT NULL,
 [LanguageName] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUPersonType] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUPersonType](
 [PersonTypeID] [int] IDENTITY(1,1) NOT NULL,
 [PersonType] [nvarchar](20) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUPhoneType] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUPhoneType](
 [PhoneTypeID] [int] IDENTITY(1,1) NOT NULL,
 [PhoneType] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUPostalCode] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUPostalCode](
 [PostalCodeID] [int] IDENTITY(1,1) NOT NULL,
 [CityID] [int] NOT NULL,
 [PostalCode] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_LUPostalCode] PRIMARY KEY CLUSTERED
(
 [PostalCodeID] ASC,
 [CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUProgramServiceType] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUProgramServiceType](
 [ProgramServiceTypeID] [int] IDENTITY(1,1) NOT NULL,
 [ServiceCode] [nvarchar](20) NOT NULL,
 [ServiceDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUProgramType] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUProgramType](
 [ProgramTypeID] [int] IDENTITY(1,1) NOT NULL,
 [ProgramName] [nvarchar](20) NOT NULL,
 [ProgramDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LURace] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LURace](
 [RaceID] [int] IDENTITY(1,1) NOT NULL,
 [RaceAbbreviation] [nvarchar](20) NOT NULL,
 [RaceDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Person].[LUState] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[LUState](
 [StateID] [int] IDENTITY(1,1) NOT NULL,
 [StateCode] [nvarchar](2) NOT NULL,
 [Name] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_State_StateID] PRIMARY KEY CLUSTERED
(
 [StateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[Notes] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Notes](
 [HouseholdNotesID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [Notes] [nvarchar](max) NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
 [HouseholdNotesID] ASC,
 [PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Person].[Person] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Person](
 [PersonID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [DateOfBirth] [datetime] NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [EmailContactPreference] [dbo].[NoYes] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [dbo].[NoYes] NULL,
 CONSTRAINT [PK_Person_PersonID] PRIMARY KEY CLUSTERED
(
 [PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[PersonAddresses] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[PersonAddresses](
 [AddressID] [int] NOT NULL,
 [PersonID] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_PersonAddresses] PRIMARY KEY CLUSTERED
(
 [AddressID] ASC,
 [PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[Phone] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Phone](
 [PhoneID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [PhoneNumber] [dbo].[Phone] NOT NULL,
 [PhoneExtension] [nvarchar](8) NULL,
 [PhoneType] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED
(
 [PhoneID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[Programs] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Programs](
 [ProgramsID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [ProgramID] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Programs] PRIMARY KEY CLUSTERED
(
 [PersonID] ASC,
 [ProgramID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Person].[ProgramServicesUsed] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[ProgramServicesUsed](
 [ServiceID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [ProgramID] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [ProgramServiceID] [int] NOT NULL,
 CONSTRAINT [PK_ProgramServicesUsed_1] PRIMARY KEY CLUSTERED
(
 [ServiceID] ASC,
 [PersonID] ASC,
 [ProgramID] ASC,
 [ProgramServiceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Signature].[Signature] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Signature].[Signature](
 [SignatureID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [Signature] [varchar](max) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Signature] PRIMARY KEY CLUSTERED
(
 [SignatureID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Signature].[Signatures] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Signature].[Signatures](
 [SignaturesID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [SignatureID] [int] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_Signatures] PRIMARY KEY CLUSTERED
(
 [SignaturesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [SSN].[SSN] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [SSN].[SSN](
 [SSNID] [int] IDENTITY(1,1) NOT NULL,
 [PersonID] [int] NOT NULL,
 [SSN] [nvarchar](9) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_SSN] PRIMARY KEY CLUSTERED
(
 [SSNID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[LUVolunteerJobType] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[LUVolunteerJobType](
 [VolunteerJobTypeID] [int] IDENTITY(1,1) NOT NULL,
 [JobType] [varchar](50) NOT NULL,
 [JobDescription] [varchar](100) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[LUVolunteerType] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[LUVolunteerType](
 [VolunteerTypeID] [int] IDENTITY(1,1) NOT NULL,
 [VolunteerType] [nvarchar](20) NOT NULL,
 [VolunteerTypeDescription] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 [IsDeleted] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[VolunteerContactInformation] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[VolunteerContactInformation](
 [ContactInformationID] [int] IDENTITY(1,1) NOT NULL,
 [VolunteerPersonID] [int] NOT NULL,
 [ContactPersonID] [int] NOT NULL,
 [OrganizationID] [int] NULL,
 [ContactFirstName] [dbo].[Name] NOT NULL,
 [ContactLastName] [dbo].[Name] NOT NULL,
 [ContactDOB] [datetime] NULL,
 [ContactAddress1] [nvarchar](max) NULL,
 [ContactAddress2] [nvarchar](max) NULL,
 [ContactHomePhone] [dbo].[Phone] NULL,
 [ContactWorkPhone] [dbo].[Phone] NULL,
 [ContactCellPhone] [dbo].[Phone] NULL,
 [ContactPager] [nvarchar](20) NULL,
 [ContactEmail] [nvarchar](50) NULL,
 [VolunteerTypeID] [int] NULL,
 [IsActive] [bit] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL,
 CONSTRAINT [PK_VolunteerContactInformation] PRIMARY KEY CLUSTERED
(
 [VolunteerPersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[VolunteerEmergencyContact] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[VolunteerEmergencyContact](
 [VolunteerEmergencyContactID] [int] IDENTITY(1,1) NOT NULL,
 [VolunteerPersonID] [int] NOT NULL,
 [ContactInformation] [nvarchar](max) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[VolunteerJobs] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[VolunteerJobs](
 [VolunteerJobsID] [int] IDENTITY(1,1) NOT NULL,
 [VolunteerPersonID] [int] NOT NULL,
 [Job] [nvarchar](50) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[VolunteerNotes] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[VolunteerNotes](
 [VolunteerNotesID] [int] IDENTITY(1,1) NOT NULL,
 [VolunteerPersonID] [int] NOT NULL,
 [Note] [varchar](max) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Volunteer].[VolunteerScheduleDayTime] Script Date: 1/6/2018 9:07:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Volunteer].[VolunteerScheduleDayTime](
 [VolunteerScheduleDayTimeID] [int] IDENTITY(1,1) NOT NULL,
 [VolunteerScheduleWeekID] [int] NOT NULL,
 [DayOfWeek] [int] NOT NULL,
 [AvailableBeginTime] [time](7) NOT NULL,
 [AvailableEndTime] [time](7) NOT NULL,
 [IsNoShow] [bit] NOT NULL,
 [ActualHoursTotal] [numeric](18, 0) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [ModifiedBy] [dbo].[Name] NOT NULL
) ON [PRIMARY]
GO