------------------------------------------------------------------Table Creation Scripts-----------------------------------------------------------------------------
--Source Table
CREATE TABLE [Source](
SourceID INT NOT NULL,
SourceObject VARCHAR(50),
PRIMARY KEY (SourceID))
--Rules Table
CREATE TABLE [Rules](
RuleID INT NOT NULL,
ValueRHS VARCHAR(MAX),
Operator VARCHAR(2) NOT NULL,
Source INT NOT NULL,
CodeLHS VARCHAR(MAX),
RuleDescription VARCHAR(MAX) NOT NULL,
PRIMARY KEY (RuleID))
ALTER TABLE [Rules] ADD CONSTRAINT FK_Rules_Source FOREIGN KEY(Source)
REFERENCES [Source](SourceID)
--RulesGroup table
CREATE TABLE [RulesGroup](
RuleGroupID INT NOT NULL,
RuleGroupDescription VARCHAR(MAX) NOT NULL,
PRIMARY KEY (RuleGroupID))
--RulesGroupGrouping table
CREATE TABLE [RulesGroupGrouping](
RuleGroupGroupingID INT NOT NULL,
RuleGroupGroupingDescription VARCHAR(MAX) NOT NULL,
PRIMARY KEY (RuleGroupGroupingID))
--RulesGroupRelations table
CREATE TABLE [RulesGroupRelations](
RuleGroupID INT REFERENCES RulesGroup(RuleGroupID) NOT NULL,
RuleID INT NOT NULL,
RuleJoinOperator VARCHAR(5),
RuleSeqNum INT,
PRIMARY KEY(RuleGroupID,RuleID))
ALTER TABLE [RulesGroupRelations] ADD CONSTRAINT FK_Rules_RulesGroupRelations FOREIGN KEY(RuleID)
REFERENCES [Rules](RuleID)
ALTER TABLE [RulesGroupRelations] ADD CONSTRAINT FK_RulesGroup_RulesGroupRelations FOREIGN KEY(RuleGroupID)
REFERENCES [RulesGroup](RuleGroupID)
--RulesGroupGroupingRelations table
CREATE TABLE [RulesGroupGroupingRelations](
RuleGroupGroupingID INT NOT NULL,
RuleGroupID INT NOT NULL,
RuleGroupJoinOperator VARCHAR(5),
RuleGroupSeqNum INT,
PRIMARY KEY(RuleGroupGroupingID ,RuleGroupID))
ALTER TABLE [RulesGroupGroupingRelations] ADD CONSTRAINT FK_RulesGroup_RulesGroupGroupingRelations FOREIGN KEY(RuleGroupID)
REFERENCES [RulesGroup](RuleGroupID)
ALTER TABLE [RulesGroupGroupingRelations] ADD CONSTRAINT FK_RulesGroupGrouping_RulesGroupGropingRelations FOREIGN KEY(RuleGroupGroupingID)
REFERENCES [RulesGroupGrouping](RuleGroupGroupingID)
--CountryDetails table
CREATE TABLE [CountryDetails](
ScreenID INT NOT NULL,
RuleGroupGroupingID INT REFERENCES RulesGroupGrouping(RuleGroupGroupingID) NOT NULL,
ResultText VARCHAR(MAX))
ALTER TABLE [CountryDetails] ADD CONSTRAINT FK_CountryDetails_RulesGroupGroping FOREIGN KEY(RuleGroupGroupingID)
REFERENCES [RulesGroupGrouping](RuleGroupGroupingID)
----------------------------------------------------------------Populate Data into tables-----------------------------------------------------------------------
INSERT INTO [Source] VALUES(1,'Country')
INSERT INTO [Source] VALUES(2,'City')
INSERT INTO [Source] VALUES(3,'State')
INSERT INTO [Rules] VALUES(1,'USA','=',1,'CountryName','Check if Country is USA')
INSERT INTO [Rules] VALUES(2,'NY','=',2,'CityName','Check if City is NY')
INSERT INTO [Rules] VALUES(3,'20','<',2,'Temperature','Check if Temperature is less than 20')
INSERT INTO [Rules] VALUES(4,'LA','=',2,'CityName','Check if City is Los Angeles')
INSERT INTO [Rules] VALUES(5,'30','>',2,'Temperature','Check if Temperature is more than 30')
INSERT INTO [Rules] VALUES(6,'CA','=',3,'StateName','Check if State is California')
INSERT INTO [Rules] VALUES(7,'15','<',3,'Temperature','Check if Temperature is less than 15')
INSERT INTO [RulesGroup] VALUES(1,'Check if Country is USA')
INSERT INTO [RulesGroup] VALUES(2,'Check if City is NY AND Temperature is less than 20')
INSERT INTO [RulesGroup] VALUES(3,'Check if City is LA AND Temperature is more than 30')
INSERT INTO [RulesGroup] VALUES(4,'Check if State is CA AND Temperature is less than 15')
INSERT INTO [RulesGroupGrouping] VALUES(1,'Check if Country is USA')
INSERT INTO [RulesGroupGrouping] VALUES(2,'(Check if City is NY AND Temperature is less than 20)')
INSERT INTO [RulesGroupGrouping] VALUES(3,'(Check if City is LA AND Temperature is more than 30) OR (State is CA AND Temperature is less than 15)')
INSERT INTO [RulesGroupRelations] VALUES(1,1,NULL,NULL)
INSERT INTO [RulesGroupRelations] VALUES(2,2,'AND',1)
INSERT INTO [RulesGroupRelations] VALUES(2,3,NULL,2)
INSERT INTO [RulesGroupRelations] VALUES(3,4,'AND',1)
INSERT INTO [RulesGroupRelations] VALUES(3,5,NULL,2)
INSERT INTO [RulesGroupRelations] VALUES(4,6,'AND',1)
INSERT INTO [RulesGroupRelations] VALUES(4,7,NULL,2)
INSERT INTO [RulesGroupGroupingRelations] VALUES(1,1,NULL,NULL)
INSERT INTO [RulesGroupGroupingRelations] VALUES(2,2,NULL,NULL)
INSERT INTO [RulesGroupGroupingRelations] VALUES(3,3,'OR',1)
INSERT INTO [RulesGroupGroupingRelations] VALUES(3,4,NULL,2)
INSERT INTO [CountryDetails] VALUES(1,1,'Hi... Country is USA')
INSERT INTO [CountryDetails] VALUES(2,2,'Hi...City is NY and wow temperature is coool')
INSERT INTO [CountryDetails] VALUES(3,3,'Hi...City is LA and it is hot OR State is CA and it is cool')