+1 (315) 557-6473 

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