CREATE TABLE dbo.tblSurveys
(	sPkId int IDENTITY(1,1) NOT NULL,
	surveryName varchar(64) NULL,
	surveyAuthor varchar(32) NULL,
	surveyCount int NULL,
	surveyStart datetime NULL,
	surveyEnd datetime NULL,
	CONSTRAINT PK_tblSurveys PRIMARY KEY CLUSTERED (sPkId ASC)
) 
GO


CREATE TABLE dbo.tblSurveyGroup
(	sgPkID int IDENTITY(1,1) NOT NULL,
	sPkID int NULL,
	systemName varchar(5) NULL,
	status varchar(64) NULL,
	splan varchar(64) NULL,
	CONSTRAINT PK_tblSurveyGroup PRIMARY KEY CLUSTERED (sgPkID ASC)
) 
GO
ALTER TABLE dbo.tblSurveyGroup  WITH CHECK ADD  CONSTRAINT FK_tblSurveyGroup_tblSurveys FOREIGN KEY(sPkID)
	REFERENCES dbo.tblSurveys (sPkId)
GO
ALTER TABLE dbo.tblSurveyGroup CHECK CONSTRAINT FK_tblSurveyGroup_tblSurveys
GO


CREATE TABLE dbo.tblSurveyQuestions
(	qPkId int IDENTITY(1,1) NOT NULL,
	sPkID int NOT NULL,
	q01 varchar(512) NULL,
	q02 varchar(512) NULL,
	q03 varchar(512) NULL,
	q04 varchar(512) NULL,
	q05 varchar(512) NULL,
	q06 varchar(512) NULL,
	q07 varchar(512) NULL,
	q08 varchar(512) NULL,
	q09 varchar(512) NULL,
	q10 varchar(512) NULL,
	q11 varchar(512) NULL,
	q12 varchar(512) NULL,
	CONSTRAINT PK_tblQuestions PRIMARY KEY CLUSTERED (qPkId ASC)
) 
GO
ALTER TABLE dbo.tblSurveyQuestions  WITH CHECK ADD  CONSTRAINT FK_tblSurveyQuestions_tblSurveys FOREIGN KEY(sPkID)
	REFERENCES dbo.tblSurveys (sPkId)
GO
ALTER TABLE dbo.tblSurveyQuestions CHECK CONSTRAINT FK_tblSurveyQuestions_tblSurveys
GO


CREATE TABLE dbo.tblSurveyAnswers
(	aPkId int IDENTITY(1,1) NOT NULL,
	sPkID int NOT NULL,
	qID int NULL,
	aID int NULL,
	aValue varchar(512) NULL,
	CONSTRAINT PK_tblSurveyAnswers PRIMARY KEY CLUSTERED (aPkId ASC)
) 
GO
ALTER TABLE dbo.tblSurveyAnswers  WITH CHECK ADD  CONSTRAINT FK_tblSurveyAnswers_tblSurveys FOREIGN KEY(sPkID)
	REFERENCES dbo.tblSurveys (sPkId)
GO
ALTER TABLE dbo.tblSurveyAnswers CHECK CONSTRAINT FK_tblSurveyAnswers_tblSurveys
GO


CREATE TABLE dbo.tblSurveyResults
(	rPkId int IDENTITY(1,1) NOT NULL,
	sPkID int NOT NULL,
	sid int NULL,
	r01 int NULL,
	r02 int NULL,
	r03 int NULL,
	r04 int NULL,
	r05 int NULL,
	r06 int NULL,
	r07 int NULL,
	r08 int NULL,
	r09 int NULL,
	r10 int NULL,
	r11 int NULL,
	r12 varchar(32) NULL,
	r13 varchar(255) NULL,
	rComments varchar(1664) NULL,
	userID varchar(32) NULL,
	entryDate datetime NOT NULL,
	CONSTRAINT PK_tblSurveyResults PRIMARY KEY CLUSTERED (rPkId ASC),
	CONSTRAINT CK_tblSurveyResults UNIQUE NONCLUSTERED (sid ASC)
) 
GO
ALTER TABLE dbo.tblSurveyResults  WITH CHECK ADD  CONSTRAINT FK_tblSurveyResults_tblSurveys FOREIGN KEY(sPkID)
	REFERENCES dbo.tblSurveys (sPkId)
GO
ALTER TABLE dbo.tblSurveyResults CHECK CONSTRAINT FK_tblSurveyResults_tblSurveys
GO