CREATE TABLE [dbo].[agecol](
	[age] [char](1) NOT NULL,
	[description] [varchar](25) NULL,
PRIMARY KEY CLUSTERED 
(
	[age] 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



CREATE TABLE [dbo].[familychars](
	[familyid] [char](5) NOT NULL,
	[size] [numeric](1, 0) NULL,
	[adults] [numeric](1, 0) NULL,
	[children] [numeric](1, 0) NULL,
	[workers] [numeric](2, 1) NULL,
	[oppgenchild] [numeric](1, 0) NULL,
	[adult1age] [char](1) NULL,
	[adult2age] [char](1) NULL,
	[child1age] [char](1) NULL,
	[child2age] [char](1) NULL,
	[child3age] [char](1) NULL,
	[child4age] [char](1) NULL,
PRIMARY KEY CLUSTERED 
(
	[familyid] 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


ALTER TABLE [dbo].[familychars]  WITH CHECK ADD  CONSTRAINT [FK_FAMILYCHARS_AGECOLA1] FOREIGN KEY([adult1age])
REFERENCES [dbo].[agecol] ([age])
GO

ALTER TABLE [dbo].[familychars] CHECK CONSTRAINT [FK_FAMILYCHARS_AGECOLA1]
GO

ALTER TABLE [dbo].[familychars]  WITH CHECK ADD  CONSTRAINT [FK_FAMILYCHARS_AGECOLA2] FOREIGN KEY([adult2age])
REFERENCES [dbo].[agecol] ([age])
GO

ALTER TABLE [dbo].[familychars] CHECK CONSTRAINT [FK_FAMILYCHARS_AGECOLA2]
GO

ALTER TABLE [dbo].[familychars]  WITH CHECK ADD  CONSTRAINT [FK_FAMILYCHARS_AGECOLC1] FOREIGN KEY([child1age])
REFERENCES [dbo].[agecol] ([age])
GO

ALTER TABLE [dbo].[familychars] CHECK CONSTRAINT [FK_FAMILYCHARS_AGECOLC1]
GO

ALTER TABLE [dbo].[familychars]  WITH CHECK ADD  CONSTRAINT [FK_FAMILYCHARS_AGECOLC2] FOREIGN KEY([child2age])
REFERENCES [dbo].[agecol] ([age])
GO

ALTER TABLE [dbo].[familychars] CHECK CONSTRAINT [FK_FAMILYCHARS_AGECOLC2]
GO

ALTER TABLE [dbo].[familychars]  WITH CHECK ADD  CONSTRAINT [FK_FAMILYCHARS_AGECOLC3] FOREIGN KEY([child3age])
REFERENCES [dbo].[agecol] ([age])
GO

ALTER TABLE [dbo].[familychars] CHECK CONSTRAINT [FK_FAMILYCHARS_AGECOLC3]
GO

ALTER TABLE [dbo].[familychars]  WITH CHECK ADD  CONSTRAINT [FK_FAMILYCHARS_AGECOLC4] FOREIGN KEY([child4age])
REFERENCES [dbo].[agecol] ([age])
GO

ALTER TABLE [dbo].[familychars] CHECK CONSTRAINT [FK_FAMILYCHARS_AGECOLC4]
GO


CREATE TABLE [dbo].[costofliving](
	[stfips] [char](2) NOT NULL,
	[areatype] [char](2) NOT NULL,
	[area] [char](6) NOT NULL,
	[periodyear] [char](4) NOT NULL,
	[periodtype] [char](2) NOT NULL,
	[period] [char](2) NOT NULL,
	[familyid] [char](5) NOT NULL,
	[annfamcost] [numeric](7, 0) NULL,
	[annwagewkr] [numeric](7, 0) NULL,
	[hrlywagewkr] [numeric](5, 2) NULL,
	[food] [numeric](5, 0) NULL,
	[housing] [numeric](5, 0) NULL,
	[healthcare] [numeric](5, 0) NULL,
	[transport] [numeric](5, 0) NULL,
	[childcare] [numeric](5, 0) NULL,
	[othercosts] [numeric](5, 0) NULL,
	[taxes] [numeric](5, 0) NULL,
	[natlcostindex] [numeric](5, 3) NULL,
 CONSTRAINT [PK_COSTOFLIVING] PRIMARY KEY CLUSTERED 
(
	[stfips] ASC,
	[areatype] ASC,
	[area] ASC,
	[periodyear] ASC,
	[periodtype] ASC,
	[period] ASC,
	[familyid] 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



ALTER TABLE [dbo].[costofliving]  WITH CHECK ADD  CONSTRAINT [FK_COSTOFLIVING_FAMILYchars] FOREIGN KEY([familyid])
REFERENCES [dbo].[familychars] ([familyid])
GO

ALTER TABLE [dbo].[costofliving] CHECK CONSTRAINT [FK_COSTOFLIVING_FAMILYchars]
GO

ALTER TABLE [dbo].[costofliving]  WITH CHECK ADD  CONSTRAINT [FK_COSTOFLIVING_GEOG] FOREIGN KEY([stfips], [areatype], [area])
REFERENCES [dbo].[geog] ([stfips], [areatype], [area])
GO

ALTER TABLE [dbo].[costofliving] CHECK CONSTRAINT [FK_COSTOFLIVING_GEOG]
GO

ALTER TABLE [dbo].[costofliving]  WITH CHECK ADD  CONSTRAINT [FK_COSTOFLIVING_PERIOD] FOREIGN KEY([periodyear], [periodtype], [period])
REFERENCES [dbo].[period] ([periodyear], [periodtype], [period])
GO

ALTER TABLE [dbo].[costofliving] CHECK CONSTRAINT [FK_COSTOFLIVING_PERIOD]
GO