View difference between Paste ID: kuDmLwYq and AwdwCau8

-use Transitional;
+
 --
 --   Script by Lars Rönnbäck
 --   Based on the paper "Modeling Conflicting, Unreliable, and Varying Information"
---   Script by Lars Rönnbäck - latest version is always here: https://pastebin.com/AwdwCau8
+--   https://www.researchgate.net/publication/329352497_Modeling_Conflicting_Unreliable_and_Varying_Information
---
+
 --   This script creates a relationally modeled implementation of Transitional Modeling, with
 --   some limitiations due to missing features in SQL Server.
---   Full-text preprint available on ResearchGate: https://bit.ly/2A7J4Rb
+
 --   Version: 20181217.2   Better XML in the views.
 --
---   some limitiations due to missing features in SQL Server. Note that this is only intended
+--   TODO: Add stored procedure that takes similar XML input and automagically populates the
---   as a prototype in which concepts can be tested for educational purposes.
+--         underlying model.
 --
---   Version: 20181220.1   Added stored procedure that simplifies data insertion.
+
---						   Cleaned up examples and added posit types.
+
---            20181217.2   Better XML in the views (old version: https://pastebin.com/kuDmLwYq).
+
 drop table if exists [Assertion];
 drop table if exists [Posit];
-drop procedure if exists [Assert];
+
-drop view if exists [Check_for_Contradictions];
+
 drop table if exists [Appearance];
 drop table if exists [Role];
 drop table if exists [Thing];
 
 -- A table to store the unique identifiers of things.
 -- Where a thing is that which is sufficiently distinguishable
 -- from something else to be told apart.
 create table [Thing] (
 	[UID] uniqueidentifier not null default NEWSEQUENTIALID(),
 	-- Enforce uniqueness of the unique identifiers.
 	-- Note that primary keys enforce uniqueness as well as cluster
 	-- the underlying table for better performance, and is needed
 	-- in order to reference these using foreign key references.
 	constraint [unique_and_referenceable_UID] primary key clustered (
 		[UID]
 	)
 );
 
 create table [Role] (
 	[RoleUID] uniqueidentifier not null,
 	[Role] varchar(555) not null,
 	constraint [Role_is_Thing] foreign key (
 		[RoleUID]
 	) references [Thing]([UID]),
 	constraint [referenceable_RoleUID] primary key clustered (
 		[RoleUID]
 	),
 	constraint [unique_Role] unique nonclustered (
 		[Role]
 	)
 );
 
 /*
 ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
 def. of universal
 A body of information is said to be universal iff positors agree on all appearances.
 -----------------------------------------------------------------------------------------------
 
 In order to disagree you must first agree upon something upon which the difference in
 opinion lies. At the very least, positors must agree on appearances. In other words
 if two positors want to have an opinion about something, they must first come to the
 conclusion that this something boils down to the same unique identifier for both of them
 and that they mean the same when they talk about the roles it may appear in.
 
 We will assume that talk about "Archie's beard" by any positor means that it is the
 same Archie and the same property they are talking about.
 */
 create table [Appearance] (
 	[AppearanceUID] uniqueidentifier not null,
 	[ThingUID] uniqueidentifier not null,
 	[RoleUID] uniqueidentifier not null,
 	constraint [Appearance_is_Thing] foreign key (
 		[AppearanceUID]
 	) references [Thing]([UID]),
 	constraint [ensure_existing_Thing] foreign key (
 		[ThingUID]
 	) references [Thing]([UID]),
 	constraint [ensure_existing_Role] foreign key (
 		[RoleUID]
 	) references [Role]([RoleUID]),
 	constraint [referenceable_AppearanceUID] primary key clustered (
-	constraint [existing_Thing] foreign key (
+
 	),
 	constraint [unique_Appearance] unique nonclustered (
-	constraint [existing_Role] foreign key (
+
 		[RoleUID]
 	)
 );
 
 create table [DereferencingSet] (
 	[DereferencingSetUID] uniqueidentifier not null,
 	constraint [DereferencingSet_is_Thing] foreign key (
 		[DereferencingSetUID]
 	) references [Thing]([UID]),
 	constraint [unique_and_referenceable_DereferencingSetUID] primary key clustered (
 		[DereferencingSetUID]
 	)
 );
 
 create table [Dereference] (
 	[DereferencingSetUID] uniqueidentifier not null,
 	[AppearanceUID] uniqueidentifier not null,
 	constraint [reference_to_DereferencingSet] foreign key (
 		[DereferencingSetUID]
 	) references [DereferencingSet]([DereferencingSetUID]),
 	constraint [reference_to_Appearance] foreign key (
 		[AppearanceUID]
 	) references [Appearance]([AppearanceUID]),
 	constraint [unique_Dereference] primary key clustered (
 		[DereferencingSetUID],
 		[AppearanceUID]
 	)
 );
 
 /*
 ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
 def. of canonical
 A body of information is said to be canonical iff all assertions
 are made against posits without negated values.
 -----------------------------------------------------------------------------------------------
 
 In practice, the value of a posit may be of any data type, primitive or complex, but due to
 SQL Server lacking support for generics (looks like <T> in many programming languages) we
 have to limit the Value column to varchar(max). Appearance time may also be any time type,
 exact or fuzzy, in order to represent that the value appeared exactly since some specified
 time or inexactly within some period of time. Here the AppearanceTime column is limited
 to a datetime.
 
 Finally, in order to represent information, canonical form is used, which simply means that
 values are stored without negation, for example "red" is acceptable, and "not red" is not.
 Opposite opinions are instead handled using negative Reliability in assertions.
 */
 create table [Posit] (
 	[PositUID] uniqueidentifier not null,
 	[DereferencingSetUID] uniqueidentifier not null,
 	[Value] varchar(max) null,
 	[AppearanceTime] datetime null,
 	constraint [ensure_existing_DereferencingSet] foreign key (
 		[DereferencingSetUID]
 	) references [DereferencingSet]([DereferencingSetUID]),
 	constraint [Posit_is_Thing] foreign key (
 		[PositUID]
 	) references [Thing]([UID]),
-	[AppearanceTime] datetime not null,
+
-	constraint [existing_DereferencingSet] foreign key (
+
 	)
 );
 
 /*
 ----------- Excerpt from: Modeling Conflicting, Unreliable, and Varying Information -----------
 def. of a body of information
 A body of information is a set of true assertions.
 
 def. of exclusive
 A body of information is said to be exclusive iff no assertions
 in which only the reliability differs exist.
 -----------------------------------------------------------------------------------------------
 
 We will assume that any data inserted into this table represents true assertions. We will not
 constrain the Reliability column, but in practice it should be in the interval [-1, 1].
 The constraint [unique_Assertion] ensures that the stored assertions are exclusive, so that
 there is no ambiguity with respect to the assigned Reliability.
 */
 create table [Assertion] (
 	[AssertionUID] uniqueidentifier not null,
 	[PositorUID] uniqueidentifier not null,
 	[PositUID] uniqueidentifier not null,
 	[Reliability] decimal(3,2) not null,
 	[AssertionTime] datetime not null,
 	constraint [Assertion_is_Thing] foreign key (
 		[AssertionUID]
 	) references [Thing]([UID]),
 	constraint [ensure_existing_Positor] foreign key (
 		[PositorUID]
 	) references [Thing]([UID]),
 	constraint [ensure_existing_Posit] foreign key (
 		[PositUID]
 	) references [Posit]([PositUID]),
 	constraint [referenceable_AssertionUID] primary key nonclustered (
-	constraint [existing_Positor] foreign key (
+
 	),
 	constraint [unique_Assertion] unique clustered (
-	constraint [existing_Posit] foreign key (
+
 		[PositUID],
 		[AssertionTime]
 	)
 );
 
 go
 create or alter view [v_Posit]
 as
 select
 	p.[PositUID],
 	p.[DereferencingSetUID],
 	p.[Value],
 	p.[AppearanceTime],
 	(
 		select
 			p.[PositUID] as [@UID],
 			p.[DereferencingSetUID] as [DereferencingSet/@UID],
 			(
 				select
 					a.[ThingUID] as [@UID],
 					r.[Role] as [@Role]
 				from
-			p.[PositUID] as [@PositUID],
+
-			p.[AppearanceTime] as [@AppearanceTime],
+
-			p.[DereferencingSetUID] as [DereferencingSet/@DereferencingSetUID],
+
 				on
 					d.[DereferencingSetUID] = s.[DereferencingSetUID]
-					a.[AppearanceUID] as [@AppearanceUID],
+
-					a.[ThingUID] as [@ThingUID],
+
 				on
 					a.[AppearanceUID] = d.[AppearanceUID]
 				join
 					[Role] r
 				on
 					r.[RoleUID] = a.[RoleUID]
 				where
 					s.[DereferencingSetUID] = p.[DereferencingSetUID]
 				order by
 					r.[RoleUID] asc
 				for xml path('Appearance'), type
 			) as [DereferencingSet],
 			p.[Value],
 			p.[AppearanceTime]
 		for xml path('Posit'), type
 	) as [PositXML]
 from
 	[Posit] p
 
 go
 create or alter view [v_Assertion]
-			p.[Value]
+
 select
 	a.[AssertionUID],
 	a.[PositorUID],
 	a.[PositUID],
 	p.[DereferencingSetUID],
 	p.[Value],
 	p.[AppearanceTime],
 	a.[Reliability],
 	a.[AssertionTime],
 	(
 		select
 			a.[AssertionUID] as [@UID],
 			a.[PositorUID] as [Positor/@UID],
 			p.[PositXML].query('.'),
 			a.[Reliability] as [Reliability],
 			a.[AssertionTime] as [AssertionTime]
 		for xml path('Assertion'), type
 	) as [AssertionXML]
 from
-			a.[AssertionUID] as [@AssertionUID],
+
-			a.[PositorUID] as [@PositorUID],
+
-			a.[Reliability] as [@Reliability],
+
-			a.[AssertionTime] as [@AssertionTime],
+
-			p.[PositXML].query('.')
+
 go
 
 --------------------------------------- PROPERTIES ---------------------------------------
 
 -- we have to use tables since the OUTPUT clause only works for tables
 drop table if exists #A;
 drop table if exists #B;
 drop table if exists #M;
 drop table if exists #S;
 drop table if exists #D;
-/*
+create table #A([UID] uniqueidentifier not null primary key); -- Archie
-------------------------------------------- XML FORMAT -------------------------------------------
+create table #B([UID] uniqueidentifier not null primary key); -- Bella
-  Specify UID for already known things and some Label for unknown. Bracketed attributes
+create table #M([UID] uniqueidentifier not null primary key); -- Modeler
-  are optional. Labels will result in new things being created, one for each unique label.
+create table #S([UID] uniqueidentifier not null primary key); -- Script
 create table #D([UID] uniqueidentifier not null primary key); -- Disagreer
-	<Assertion PositorUID="<UID|Label>" Reliability="1.00" AssertionTime="2018-12-13T15:30:00">
+
-	  <Posit [PositUID="<UID>"] AppearanceTime="2018-12-01T12:00:00">
+-- create the Archie thing
-		<DereferencingSet [DereferencingSetUID="<UID>"]>
+insert into [Thing]([UID]) output inserted.[UID] into #A values (DEFAULT);
-		  <Appearance [AppearanceUID="<UID>"] ThingUID="<UID|Label>" Role="has beard" />
+-- create the Bella thing
-		</DereferencingSet>
+insert into [Thing]([UID]) output inserted.[UID] into #B values (DEFAULT);
-		<Value>fluffy red</Value>
+-- create the Modeler thing
-	  </Posit>
+insert into [Thing]([UID]) output inserted.[UID] into #M values (DEFAULT);
-	</Assertion>
+-- create the Script thing
 insert into [Thing]([UID]) output inserted.[UID] into #S values (DEFAULT);
-  Note that AssertionUID cannot be specified. Assertions can never be altered once they've been
+-- create the Disagreer thing
-  created.
+insert into [Thing]([UID]) output inserted.[UID] into #D values (DEFAULT);
---------------------------------------------------------------------------------------------------
+
 -- we now have five things
-create or alter procedure Assert (
+
-	@assertion xml OUTPUT
+
-)
+-- create a role thing and an appearance thing
 drop table if exists #Role1;
-begin
+drop table if exists #Appearance1;
-	set nocount on;
+create table #Role1([UID] uniqueidentifier not null primary key);
 create table #Appearance1([UID] uniqueidentifier not null primary key);
-	-- create a lookup table
+insert into [Thing]([UID]) output inserted.[UID] into #Role1 values (DEFAULT);
-	create table #Thing (
+insert into [Thing]([UID]) output inserted.[UID] into #Appearance1 values (DEFAULT);
-		[ThingUID] uniqueidentifier not null primary key,
+
-		[UID_or_Label] varchar(555) not null
+-- create a role
-	);
+insert into [Role]([RoleUID], [Role])
 select [UID], 'has beard'
-	-- is a posit set already referenced through a UID?
+from #Role1;
-	declare @PositUID uniqueidentifier = @assertion.value(
+
-		'/Assertion[1]/Posit[1]/@PositUID',
+-- check what the role looks like
-		'uniqueidentifier'
+select * from [Role];
-	);
+
 -- create an appearance
-	-- if not it needs to be built
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
-	if @PositUID is null
+select app.[UID], Archie.[UID], r.[RoleUID]
-	begin
+from [Role] r, #Appearance1 app, #A Archie
-		-- is a dereferencing set already referenced through a UID?
+where r.[Role] = 'has beard';
-		declare @DereferencingSetUID uniqueidentifier = @assertion.value(
+
-			'/Assertion[1]/Posit[1]/DereferencingSet[1]/@DereferencingSetUID',
+-- we now have one appearance
-			'uniqueidentifier'
+select * from [Appearance];
-		);
+
 -- create a dereferencing set thing
-		if @DereferencingSetUID is not null
+drop table if exists #DereferencingSet1;
-		begin
+create table #DereferencingSet1([UID] uniqueidentifier not null primary key);
-			if not exists (
+insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet1 values (DEFAULT);
-				select [DereferencingSetUID] from [DereferencingSet]
+
-				where [DereferencingSetUID] = @DereferencingSetUID
+-- create a dereferencing set
-			)
+insert into [DereferencingSet]([DereferencingSetUID])
-			raiserror('The referenced @DereferencingSetUID does not exist', 10, 1);
+select [UID]
-		end
+from #DereferencingSet1;
-		else
+
-		begin
+-- add the appearance to the dereferencing set
-			-- check if given AppareanceUID:s exist
+insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
-			if exists (
+select s.[UID], app.[UID]
-				select x.[AppearanceUID]
+from #DereferencingSet1 s, #Appearance1 app;
-				from (
+
-					select distinct
+-- create a posit thing
-						Appearance.value('@AppearanceUID', 'uniqueidentifier') as [AppearanceUID]
+drop table if exists #Posit1;
-					from @assertion.nodes(
+create table #Posit1([UID] uniqueidentifier not null primary key);
-						'/Assertion/Posit/DereferencingSet/Appearance[@AppearanceUID]'
+insert into [Thing]([UID]) output inserted.[UID] into #Posit1 values (DEFAULT);
-					) as T(Appearance)
+
-				) x
+-- create a posit
-				left join [Appearance] a
+insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
-				on a.[AppearanceUID] = x.[AppearanceUID]
+select p.[UID], s.[UID], 'fluffy red', '2018-12-01 12:00'
-				where a.[AppearanceUID] is null
+from #Posit1 p, #DereferencingSet1 s;
-			)
+
-			raiserror('The referenced @AppearanceUID does not exist', 10, 1);
+-- we now have one posit
 select * from [Posit];
-			-- create a table to hold appearances
+
-			create table #Appearance (
+-- now the posit is complete
-				AppearanceUID uniqueidentifier not null primary key,
+select * from [v_Posit];
-				ThingUID uniqueidentifier not null,
+
-				RoleUID uniqueidentifier not null
+-- let Bella assert this, so first create an assertion thing
-			);
+drop table if exists #Assertion1;
 create table #Assertion1([UID] uniqueidentifier not null primary key);
-			-- add existing and referenced appearances
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion1 values (DEFAULT);
-			insert into #Appearance
+
-			select a.*
+-- create an assertion
-			from (
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-				select distinct
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:30'
-					Appearance.value('@AppearanceUID', 'uniqueidentifier') as [AppearanceUID]
+from #Assertion1 a, #B Bella, #Posit1 p;
-				from @assertion.nodes(
+
-					'/Assertion/Posit/DereferencingSet/Appearance[@AppearanceUID]'
+-- now we have one assertion
-				) as T(Appearance)
+select * from [Assertion];
-			) x
+
-			join [Appearance] a
+-- now the assertion is complete
-			on a.[AppearanceUID] = x.[AppearanceUID];
+select * from [v_Assertion]
 order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
-			declare @matches int = 0;
+
-			-- create missing things and populate the lookup table
+-- create another posit thing
-			merge [Thing] t
+drop table if exists #Posit2;
-			using (
+create table #Posit2([UID] uniqueidentifier not null primary key);
-				select distinct
+insert into [Thing]([UID]) output inserted.[UID] into #Posit2 values (DEFAULT);
-					Appearance.value('@ThingUID', 'varchar(555)') as [UID_or_Label]
+
-				from @assertion.nodes(
+-- create that posit
-					'/Assertion/Posit/DereferencingSet/Appearance[not(@AppearanceUID)]'
+insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
-				) as T(Appearance)
+select p.[UID], s.[UID], 'shaved clean', '2018-12-01 12:00'
-			) i on i.[UID_or_Label] = cast(t.[UID] as varchar(555))
+from #Posit2 p, #DereferencingSet1 s;
-			when not matched then insert values(DEFAULT)
+
-			when matched then update set @matches = @matches + 1
+-- we now have two posits
-			output inserted.[UID], i.[UID_or_Label] into #Thing;
+select * from [Posit];
 
-			-- create a lookup table
+-- let the Disagreer assert this, so first create another assertion thing
-			create table #Role (
+drop table if exists #Assertion2;
-				[RoleUID] uniqueidentifier not null primary key,
+create table #Assertion2([UID] uniqueidentifier not null primary key);
-				[Role] varchar(555) not null
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion2 values (DEFAULT);
-			);
+
 -- create another assertion
-			-- lookup or create the roles
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-			merge [Thing] t
+select a.[UID], Disagreer.[UID], p.[UID], 1, '2018-12-13 15:30'
-			using (
+from #Assertion2 a, #D Disagreer, #Posit2 p;
 
-					r.[RoleUID],
+-- now that assertion is also complete
-					x.[Role]
+-- so Bella and the Disagreer have conflicting views of Archie's beard
-				from (
+select * from [v_Assertion]
-					select
+
-						Appearance.value('@Role', 'varchar(555)') as [Role]
+
-					from @assertion.nodes(
+-- let the Disagreer disagree on the posit Bella asserted
-						'/Assertion/Posit/DereferencingSet/Appearance[not(@AppearanceUID)]'
+drop table if exists #Assertion3;
-					) as T(Appearance)
+create table #Assertion3([UID] uniqueidentifier not null primary key);
-				) x
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion3 values (DEFAULT);
-				left join [Role] r
+
-				on x.[Role] = r.[Role]
+-- create another assertion
-			) i on i.[RoleUID] = t.[UID]
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-			when not matched then insert values(DEFAULT)
+select a.[UID], Disagreer.[UID], p.[UID], -1, '2018-12-13 15:35'
-			when matched then update set @matches = @matches + 1
+from #Assertion3 a, #D Disagreer, #Posit1 p;
-			output inserted.[UID], i.[Role] into #Role;
+
 -- so Bella and the Disagreer also disagree on the posit stating it is fluffy red
-			-- add missing roles to Role
+select * from [v_Assertion]
-			merge [Role] r
+
-			using #Role rlkp
+
-			on rlkp.[RoleUID] = r.[RoleUID]
+
-			when not matched then insert values (rlkp.[RoleUID], rlkp.[Role]);
+-- first create an assertion thing
 drop table if exists #Assertion4;
-			-- lookup or create the appearances
+create table #Assertion4([UID] uniqueidentifier not null primary key);
-			merge [Thing] t
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion4 values (DEFAULT);
-			using (
+
 -- create an assertion
-					a.[AppearanceUID],
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-					tlkp.[ThingUID],
+select a.[UID], Bella.[UID], p.[UID], 0.05, '2018-12-13 15:35'
-					rlkp.[RoleUID]
+from #Assertion4 a, #B Bella, #Posit2 p;
-				from (
+
-					select
+
-						Appearance.value('@ThingUID', 'varchar(555)') as [UID_or_Label],
+select * from [v_Assertion]
-						Appearance.value('@Role', 'varchar(555)') as [Role]
+
-					from @assertion.nodes(
+
-						'/Assertion/Posit/DereferencingSet/Appearance[not(@AppearanceUID)]'
+-- create another posit thing
-					) as T(Appearance)
+drop table if exists #Posit3;
-				) x
+create table #Posit3([UID] uniqueidentifier not null primary key);
-				join #Thing tlkp
+insert into [Thing]([UID]) output inserted.[UID] into #Posit3 values (DEFAULT);
-				on tlkp.[UID_or_Label] = x.[UID_or_Label]
+
-				join #Role rlkp
+-- create that posit
-				on rlkp.[Role] = x.[Role]
+insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
-				left join [Appearance] a
+select p.[UID], s.[UID], 'shaved clean', '2018-12-01 13:00'
-				on a.[ThingUID] = tlkp.[ThingUID] and a.[RoleUID] = rlkp.[RoleUID]
+from #Posit3 p, #DereferencingSet1 s;
-			) i on i.[AppearanceUID] = t.[UID]
+
-			when not matched then insert values(DEFAULT)
+-- we now have three posits
-			when matched then update set @matches = @matches + 1
+select * from [Posit];
-			output inserted.[UID], i.[ThingUID], i.[RoleUID] into #Appearance;
+
 -- create another assertion thing
-			-- add missing apperances to Appearance
+drop table if exists #Assertion5;
-			merge [Appearance] a
+create table #Assertion5([UID] uniqueidentifier not null primary key);
-			using #Appearance alkp
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion5 values (DEFAULT);
-			on alkp.[AppearanceUID] = a.[AppearanceUID]
+
-			when not matched then insert values (alkp.[AppearanceUID], alkp.[ThingUID], alkp.[RoleUID]);
+-- create an assertion
 insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-			declare @NumberOfAppearances int = (select count(*) from #Appearance);
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:35'
 from #Assertion5 a, #B Bella, #Posit3 p;
-			-- do these appearances already exist as a dereferencing set?
+
-			set @DereferencingSetUID = (
+
-				select d.DereferencingSetUID
+
-				  from [Dereference] d
+
-				left join #Appearance a on a.AppearanceUID = d.AppearanceUID
+
-				group by d.DereferencingSetUID
+
-				having COUNT(d.AppearanceUID) = @NumberOfAppearances
+
-				   and COUNT(a.AppearanceUID) = @NumberOfAppearances
+
-			);
+
 Let A be a body of information. The information in effect is a
-			-- if not it needs to be created
+
-			if @DereferencingSetUID is null
+
-			begin
+
-				create table #DereferencingSet (
+
-					[DereferencingSetUID] uniqueidentifier not null primary key
+
-				);
+
    t ≤ t@.
-				insert into [Thing]
+
-				output inserted.[UID] into #DereferencingSet
+
-				values (DEFAULT);
+
    assertion !(P, p, 0, T0) exists with T ≤ T0 ≤ T@.
-				set @DereferencingSetUID = (select top 1 [DereferencingSetUID] from #DereferencingSet);
+
    latest assertion time T for each combination of P, D, and v.
-				insert into [DereferencingSet] values (@DereferencingSetUID);
+
 
-				insert into [Dereference]
+
-				select @DereferencingSetUID, [AppearanceUID]
+
-				from #Appearance;
+
-			end
+
-		end -- end of building a dereference set
+
-		-- now we have a @DereferencingSetUID
+
 	@assertionTime datetime
-		declare @Value varchar(max);
+
-		declare @AppearanceTime datetime;
+
 with A1 as (
-		select
+
-			@Value = cast(Posit.query('Value/node()') as varchar(max)),
+		*
-			@AppearanceTime = Posit.value('@AppearanceTime', 'datetime')
+
-		from @assertion.nodes(
+		[v_Assertion]
-			'/Assertion[1]/Posit[1]'
+	where
-		) as T(Posit);
+		[AppearanceTime] <= @appearanceTime
 	and
-		-- check if it already exists
+		[AssertionTime] <= @assertionTime
-		set @PositUID = (
+
-			select [PositUID]
+
-			from [Posit]
+
-			where [DereferencingSetUID] = @DereferencingSetUID
+		a.*
-			  and [Value] = @Value
+
-			  and [AppearanceTime] = @AppearanceTime
+		A1 a
-		);
+	where
 		a.[Reliability] <> 0
-		if @PositUID is null
+	and
-		begin
+		a.[AppearanceTime] = (
-			-- create the posit
+			select
-			create table #Posit (
+				max(s.[AppearanceTime])
-				[PositUID] uniqueidentifier not null primary key
+			from
-			);
+				A1 s
 			where
-			insert into [Thing]
+				s.[PositorUID] = a.[PositorUID]
-			output inserted.[UID] into #Posit
+			and
-			values (DEFAULT);
+				s.[DereferencingSetUID] = a.[DereferencingSetUID]
 		)
-			set @PositUID = (select top 1 [PositUID] from #Posit);
+	and not exists (
 		select
-			insert into [Posit] (
+			x.[AssertionUID]
-				[PositUID],
+		from
-				[DereferencingSetUID],
+			A1 x
-				[Value],
+		where
-				[AppearanceTime]
+			x.[PositorUID] = a.[PositorUID]
-			)
+		and
-			values (
+			x.[PositUID] = a.[PositUID]
-				@PositUID,
+		and
-				@DereferencingSetUID,
+			x.[Reliability] = 0
-				@Value,
+		and
-				@AppearanceTime
+			x.[AssertionTime] between a.AssertionTime and @assertionTime
-			);
+
-		end -- new posit created
+),
-	end -- end of building posit
+A3 as (
-	-- now we have a @PositUID
+
 		a.*
-	declare @AssertionUID uniqueidentifier;
+
 		A2 a
-	-- create the assertion
+	where
-	create table #Assertion (
+		a.[AssertionTime] = (
-		[AssertionUID] uniqueidentifier not null primary key
+			select
-	);
+				max(s.[AssertionTime])
 			from
-	insert into [Thing]
+				A2 s
-	output inserted.[UID] into #Assertion
+			where
-	values (DEFAULT);
+				s.[PositorUID] = a.[PositorUID]
 			and
-	set @AssertionUID = (select top 1 [AssertionUID] from #Assertion);
+				s.[DereferencingSetUID] = a.[DereferencingSetUID]
 			and
-	-- A positor must be referenced through a UID or Label
+				s.[Value] = a.[Value]
-	declare @PositorUID_or_Label varchar(555) = @assertion.value(
+		)
-		'/Assertion[1]/@PositorUID',
+
-		'varchar(555)'
+
-	);
+	*
 from
-	if @PositorUID_or_Label is null
+	A3;
-	raiserror('No @PositorUID has been specified', 10, 1);
+
 
-	-- is must either be a UID or a Label (and if it is a Label it is already created)
+
-	declare @PositorUID uniqueidentifier = isnull(
+
-		(select [ThingUID] from #Thing where [UID_or_Label] = @PositorUID_or_Label),
+
-		(select [UID] from [Thing] where cast([UID] as varchar(555)) = @PositorUID_or_Label)
+
-	);
+
 
-	if @PositorUID is null
+
-	raiserror('The referenced @PositorUID does not exist', 10, 1);
+
 order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
-	-- now store the assertion
+
-	insert into [Assertion] (
+
-		[AssertionUID],
+
-		[PositorUID],
+
-		[PositUID],
+
-		[Reliability],
+
 A body of information is said to be symmetric iff the assertions
 !(P, p, −α, T) and !(P, ̄p, α, T) are equivalent for a posit p and its opposite ̄p.
-	select
+
-		@AssertionUID,
+
-		@PositorUID,
+
-		@PositUID,
+
-		Posit.value('@Reliability', 'decimal(3,2)') as [AppearanceTime],
+
-		Posit.value('@AssertionTime', 'datetime') as [AppearanceTime]
+
-	from @assertion.nodes(
+
-		'/Assertion[1]'
+
-	) as T(Posit)
+
 for the same dereferencing set satisfy the inequality:
-	-- and we are done, so finally set the output variable
+
-	set @assertion = (
+
-		select [AssertionXML] from [v_Assertion] where [AssertionUID] = @AssertionUID
+
-	);
+
-end
+
 The fact that we can talk about contradictions relies on the assumption that our body
 of information is symmetric. When a body of information is also bounded it is possible to
 transform all information into canonical form (negated values like "not red" are instead
--- first create a few things (and positors), and relate them to a name using "has name"
+
-declare @assertion1 xml = '
+
-	<Assertion PositorUID="A" Reliability="1.00" AssertionTime="2018-12-15 00:00">
+
-	  <Posit AppearanceTime="1972-08-20 15:30">
+
-		<DereferencingSet>
+
-		  <Appearance ThingUID="A" Role="has name" />
+
-		</DereferencingSet>
+
-		<Value>Archie</Value>
+
-	  </Posit>
+
-	</Assertion>
+		[AppearanceTime], [AssertionTime]
-';
+	from
-exec Assert @assertion1 OUTPUT;
+		(select distinct [AppearanceTime] from Posit) p
 	cross join
--- the stored assertion is returned (in case we need any of the UID:s for later)
+		(select distinct [AssertionTime] from Assertion) a
-select @assertion1;
+
--- but we can also look at it using the view
+
-select * from [v_Assertion];
+
 	case
-declare @assertion2 xml = '
+
-	<Assertion PositorUID="B" Reliability="1.00" AssertionTime="2018-12-15 00:00">
+
-	  <Posit AppearanceTime="1972-02-13 08:00">
+
-		<DereferencingSet>
+
-		  <Appearance ThingUID="B" Role="has name" />
+
-		</DereferencingSet>
+
-		<Value>Bella</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion2 OUTPUT;
+
 		bitemporalTimepoints t
-declare @assertion3 xml = '
+
-	<Assertion PositorUID="M" Reliability="1.00" AssertionTime="2018-12-15 00:00">
+
-	  <Posit AppearanceTime="1945-10-17 12:00">
+
-		<DereferencingSet>
+
-		  <Appearance ThingUID="M" Role="has name" />
+
-		</DereferencingSet>
+
-		<Value>Modeler</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion3 OUTPUT;
+
 select * from [Check_for_Contradictions];
-declare @assertion4 xml = '
+
-	<Assertion PositorUID="S" Reliability="1.00" AssertionTime="2018-12-15 00:00">
+-- which information is in effect at 12 and 13 given latest assertions?
-	  <Posit AppearanceTime="2018-12-15">
+select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
-		<DereferencingSet>
+
-		  <Appearance ThingUID="S" Role="has name" />
+select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
-		</DereferencingSet>
+
-		<Value>Script</Value>
+
-	  </Posit>
+-- Bella realizes she will remain forever contradictory about 12:00, so she makes another assertion
-	</Assertion>
+drop table if exists #Assertion6;
-';
+create table #Assertion6([UID] uniqueidentifier not null primary key);
-exec Assert @assertion4 OUTPUT;
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion6 values (DEFAULT);
 
-declare @assertion5 xml = '
+
-	<Assertion PositorUID="D" Reliability="1.00" AssertionTime="2018-12-15 00:00">
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	  <Posit AppearanceTime="2001-01-01 01:00">
+select a.[UID], Bella.[UID], p.[UID], 0.95, '2018-12-13 15:40'
-		<DereferencingSet>
+from #Assertion6 a, #B Bella, #Posit1 p;
-		  <Appearance ThingUID="D" Role="has name" />
+
-		</DereferencingSet>
+-- which information is in effect at 12 and 13 given latest assertions?
-		<Value>Disser</Value>
+select * from [Information_in_Effect]('2018-12-01 12:00', getdate())
-	  </Posit>
+
-	</Assertion>
+select * from [Information_in_Effect]('2018-12-01 13:00', getdate())
-';
+
-exec Assert @assertion5 OUTPUT;
+
 -- have contradictory assertions been made?
--- there should now be five assertions
+
-select * from [v_Assertion];
+
 -- the Disagreer is now changing it's mind and retracts the previous statement
--- first we will let Bella assert a few things about Archie
+drop table if exists #Assertion7;
-declare @ArchieUID6 varchar(555) = (
+create table #Assertion7([UID] uniqueidentifier not null primary key);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion7 values (DEFAULT);
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+
 -- a retraction has reliability = 0
-declare @BellaUID6 varchar(555) = (
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+select a.[UID], Disagreer.[UID], p.[UID], 0, '2018-12-13 15:40'
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+from #Assertion7 a, #D Disagreer, #Posit2 p;
 
-declare @assertion6 xml = '
+
-	<Assertion PositorUID="' + @BellaUID6 + '" Reliability="1.00" AssertionTime="2018-12-13 15:30">
+
-	  <Posit AppearanceTime="2018-12-01 12:00">
+
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID6 + '" Role="has beard" />
+
-		</DereferencingSet>
+
-		<Value>fluffy red</Value>
+
-	  </Posit>
+
-	</Assertion>
+drop table if exists #Posit4;
-';
+create table #Posit4([UID] uniqueidentifier not null primary key);
-exec Assert @assertion6 OUTPUT;
+insert into [Thing]([UID]) output inserted.[UID] into #Posit4 values (DEFAULT);
 
--- there should now be a new assertion
+insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
-select * from [v_Assertion] where [Value] = 'fluffy red';
+select p.[UID], s.[UID], 'shaved clean', '2018-12-01 14:00'
 from #Posit4 p, #DereferencingSet1 s;
--- let the Disser have a different opinion
+
-declare @ArchieUID7 varchar(555) = (
+drop table if exists #Assertion8;
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+create table #Assertion8([UID] uniqueidentifier not null primary key);
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion8 values (DEFAULT);
 
-declare @DisserUID7 varchar(555) = (
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:45'
-	cross apply AssertionXML.nodes('//*[Value = "Disser"]//*[@Role = "has name"]') as T(Appearance)
+from #Assertion8 a, #B Bella, #Posit4 p;
 
-declare @assertion7 xml = '
+
-	<Assertion PositorUID="' + @DisserUID7 + '" Reliability="1.00" AssertionTime="2018-12-13 15:30">
+
-	  <Posit AppearanceTime="2018-12-01 12:00">
+
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID7 + '" Role="has beard" />
+
-		</DereferencingSet>
+
-		<Value>shaved clean</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion7 OUTPUT;
+
 
--- there should now be two assertions
+
--- where Bella and the Disagreer have conflicting views of Archie's beard
+drop table if exists #Assertion9;
-select * from [v_Assertion] where [Value] in ('fluffy red', 'shaved clean');
+create table #Assertion9([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Assertion9 values (DEFAULT);
--- let the Disser further oppose to what Bella stated
+
-declare @ArchieUID8 varchar(555) = (
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:50'
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+from #Assertion9 a, #B Bella, #Posit4 p;
 
-declare @DisserUID8 varchar(555) = (
+
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Disser"]//*[@Role = "has name"]') as T(Appearance)
+
 
-declare @assertion8 xml = '
+
-	<Assertion PositorUID="' + @DisserUID8 + '" Reliability="-1.00" AssertionTime="2018-12-13 15:35">
+
-	  <Posit AppearanceTime="2018-12-01 12:00">
+
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID8 + '" Role="has beard" />
+
-		</DereferencingSet>
+
-		<Value>fluffy red</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion8 OUTPUT;
+
 -- The good thing about posits, assertions, and so on being "things" in themselves
--- so Bella and the Disser have opposing views of the posit stating it is fluffy red
+
-select * from [v_Assertion] where [Value] in ('fluffy red', 'shaved clean');
+
 
 -- let the Script positor assert when what we have done so far was recorded in
-declare @ArchieUID9 varchar(555) = (
+
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+-- we now have 21 things
 select * from [Thing];
-declare @BellaUID9 varchar(555) = (
+
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+-- create another role thing
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+drop table if exists #Role2;
 create table #Role2([UID] uniqueidentifier not null primary key);
-declare @assertion9 xml = '
+insert into [Thing]([UID]) output inserted.[UID] into #Role2 values (DEFAULT);
-	<Assertion PositorUID="' + @BellaUID9 + '" Reliability="0.05" AssertionTime="2018-12-13 15:35">
+
-	  <Posit AppearanceTime="2018-12-01 12:00">
+-- create a new role
-		<DereferencingSet>
+insert into [Role]([RoleUID], [Role])
-		  <Appearance ThingUID="' + @ArchieUID9 + '" Role="has beard" />
+select [UID], 'was recorded at'
-		</DereferencingSet>
+from #Role2;
-		<Value>shaved clean</Value>
+
-	  </Posit>
+-- check what the role looks like
-	</Assertion>
+select * from [Role];
-';
+
-exec Assert @assertion9 OUTPUT;
+-- create an appearance thing for every thing we have created
 drop table if exists #Appearance2;
 create table #Appearance2([UID] uniqueidentifier not null primary key);
-select * from [v_Assertion] where [Value] in ('fluffy red', 'shaved clean')
+merge [Thing] t
 using [Thing] src on 1 = 0 -- make sure everything is "not matched" below
 when not matched then insert ([UID]) values (DEFAULT)
--- she is sure Archie was shaved clean later though (a real change occured between 12 and 13)
+output inserted.[UID] into #Appearance2;
-declare @ArchieUID10 varchar(555) = (
+
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+-- create an equal number of appearances
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
 select app.[UID], t.[UID], r.[RoleUID]
-declare @BellaUID10 varchar(555) = (
+from (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+join (select [UID], row_number() over (order by [UID]) as _row from [Thing]) t
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+on t._row = app._row
 cross apply (select [RoleUID] from [Role] where [Role] = 'was recorded at') r;
-declare @assertion10 xml = '
+
-	<Assertion PositorUID="' + @BellaUID10 + '" Reliability="1.00" AssertionTime="2018-12-13 15:35">
+-- we now have 23 appearances
-	  <Posit AppearanceTime="2018-12-01 13:00">
+select * from [Appearance];
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID10 + '" Role="has beard" />
+-- create 22 dereferencing set things
-		</DereferencingSet>
+drop table if exists #DereferencingSet2;
-		<Value>shaved clean</Value>
+create table #DereferencingSet2([UID] uniqueidentifier not null primary key);
-	  </Posit>
+merge [Thing] t
-	</Assertion>
+using #Appearance2 src on 1 = 0 -- make sure everything is "not matched" below
-';
+when not matched then insert ([UID]) values (DEFAULT)
-exec Assert @assertion10 OUTPUT;
+output inserted.[UID] into #DereferencingSet2;
 
 -- create 22 dereferencing sets
-select * from [v_Assertion] where [Value] in ('fluffy red', 'shaved clean')
+insert into [DereferencingSet]([DereferencingSetUID])
 select [UID]
 from #DereferencingSet2;
 
 -- add the appearances to the dereferencing sets
 insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
 select s.[UID], app.[UID]
 from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
 join (select [UID], row_number() over (order by [UID]) as _row from #Appearance2) app
 on s._row = app._row;
 
 -- create 22 posit things
 drop table if exists #Posit5;
 create table #Posit5([UID] uniqueidentifier not null primary key);
 merge [Thing] t
 using #DereferencingSet2 src on 1 = 0 -- make sure everything is "not matched" below
 when not matched then insert ([UID]) values (DEFAULT)
 output inserted.[UID] into #Posit5;
 
 -- create 22 posits (with the limitation that the date needs to be converted to a string)
 insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
 select p.[UID], s.[UID], cast(getdate() as varchar(max)), getdate()
 from (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
 join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet2) s
 on p._row = s._row;
 
 -- we now have 26 posits, the last 22 of which intends to capture metadata
 select * from [v_Posit];
 
 -- finally let the Script positor assert these
 drop table if exists #Assertion10;
 create table #Assertion10([UID] uniqueidentifier not null primary key);
-	select *
+merge [Thing] t
-	  from [v_Assertion]
+using #Posit5 src on 1 = 0 -- make sure everything is "not matched" below
-	 where [AppearanceTime] <= @appearanceTime
+when not matched then insert ([UID]) values (DEFAULT)
-	   and [AssertionTime]  <= @assertionTime
+output inserted.[UID] into #Assertion10;
 
 insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	select a.*
+select a.[UID], Script.[UID], p.[UID], 1, getdate()
-	  from A1 a
+from (select [UID], row_number() over (order by [UID]) as _row from #Assertion10) a
-	 where a.[Reliability] <> 0
+join (select [UID], row_number() over (order by [UID]) as _row from #Posit5) p
-	   and a.[AppearanceTime] = (
+on a._row = p._row
-		   select max(s.[AppearanceTime])
+cross apply #S Script;
-			 from A1 s
+
-			where s.[PositorUID] = a.[PositorUID]
+-- there are now 31 assertions
-			  and s.[DereferencingSetUID] = a.[DereferencingSetUID]
+
-	   )
+
-	   and not exists (
+
-		   select x.[AssertionUID]
+
-		     from A1 x
+
-		    where x.[PositorUID] = a.[PositorUID]
+
-		      and x.[PositUID] = a.[PositUID]
+
-		      and x.[Reliability] = 0
+-- finding all (current) metadata can be done by a condition on our Script positor
-		      and x.[AssertionTime] between a.AssertionTime and @assertionTime
+
-	   )
+where [PositorUID] = (select top 1 [UID] from #S)
 order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
-select a.*
+
-  from A2 a
+
- where a.[AssertionTime] = (
+-- we will start by marrying Archie and Bella
-	   select max(s.[AssertionTime])
+
-		 from A2 s
+-- create a few new role things
-		where s.[PositorUID] = a.[PositorUID]
+drop table if exists #Role3;
-		  and s.[DereferencingSetUID] = a.[DereferencingSetUID]
+create table #Role3([UID] uniqueidentifier not null primary key);
-		  and s.[Value] = a.[Value]
+insert into [Thing]([UID]) output inserted.[UID] into #Role3 values (DEFAULT);
-)
+drop table if exists #Role4;
 create table #Role4([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Role4 values (DEFAULT);
 drop table if exists #Role5;
 create table #Role5([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Role5 values (DEFAULT);
 
 -- create the new roles
 insert into [Role]([RoleUID], [Role])
 select [UID], 'husband'
 from #Role3;
 insert into [Role]([RoleUID], [Role])
 select [UID], 'wife'
 from #Role4;
 insert into [Role]([RoleUID], [Role])
 select [UID], 'church'
 from #Role5;
 
 -- what roles have got now?
 select * from [Role];
 
 -- we will need a church thing
 drop table if exists #C;
 create table #C([UID] uniqueidentifier not null primary key); -- Church
 
 -- create the Church thing
 insert into [Thing]([UID]) output inserted.[UID] into #C values (DEFAULT);
 
 -- we also need three appearance things
 drop table if exists #Appearance3;
 create table #Appearance3([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Appearance3 values (DEFAULT);
 drop table if exists #Appearance4;
 create table #Appearance4([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Appearance4 values (DEFAULT);
 drop table if exists #Appearance5;
 create table #Appearance5([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Appearance5 values (DEFAULT);
 
 -- create three appearances
 insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
 select app.[UID], Archie.[UID], r.[RoleUID]
 from [Role] r, #Appearance3 app, #A Archie
 where r.[Role] = 'husband';
 insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
 select app.[UID], Bella.[UID], r.[RoleUID]
-	select [AppearanceTime], [AssertionTime]
+from [Role] r, #Appearance4 app, #B Bella
-	from (select distinct [AppearanceTime] from Posit) p
+where r.[Role] = 'wife';
-	cross join (select distinct [AssertionTime] from Assertion) a
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
 select app.[UID], Church.[UID], r.[RoleUID]
 from [Role] r, #Appearance5 app, #C Church
 where r.[Role] = 'church';
 
 -- create a dereferencing set thing
 drop table if exists #DereferencingSet3;
 create table #DereferencingSet3([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet3 values (DEFAULT);
 
 -- create a dereferencing set
 insert into [DereferencingSet]([DereferencingSetUID])
 select [UID]
 from #DereferencingSet3;
 
 -- add the created appearances to the (same) dereferencing set
 insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
 select s.[UID], app.[UID]
 from #DereferencingSet3 s, #Appearance3 app;
 insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
 select s.[UID], app.[UID]
 from #DereferencingSet3 s, #Appearance4 app;
 insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
 select s.[UID], app.[UID]
 from #DereferencingSet3 s, #Appearance5 app;
 
 -- create a posit thing
 drop table if exists #Posit6;
 create table #Posit6([UID] uniqueidentifier not null primary key);
--- Bella is contradictory for the appearance time between 12 and 13 for assertion time 15:35 and onwards
+insert into [Thing]([UID]) output inserted.[UID] into #Posit6 values (DEFAULT);
--- so with our current knowledge she has made contradictory statements
+
 -- create a posit
--- Bella realizes she will remain forever contradictory unless she makes another assertion
+insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
 select p.[UID], s.[UID], 'married', '2004-06-19 15:00'
-declare @ArchieUID11 varchar(555) = (
+from #Posit6 p, #DereferencingSet3 s;
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+-- now the posit is complete (note that the set has three members now)
 select * from [v_Posit]
-declare @BellaUID11 varchar(555) = (
+where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+-- let Bella assert this, so first create an assertion thing
 drop table if exists #Assertion11;
-declare @assertion11 xml = '
+create table #Assertion11([UID] uniqueidentifier not null primary key);
-	<Assertion PositorUID="' + @BellaUID11 + '" Reliability="0.95" AssertionTime="2018-12-13 15:40">
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion11 values (DEFAULT);
-	  <Posit AppearanceTime="2018-12-01 12:00">
+
-		<DereferencingSet>
+-- create an assertion
-		  <Appearance ThingUID="' + @ArchieUID11 + '" Role="has beard" />
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-		</DereferencingSet>
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
-		<Value>fluffy red</Value>
+from #Assertion11 a, #B Bella, #Posit6 p;
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion11 OUTPUT;
+where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
 order by [DereferencingSetUID], [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
--- which information is in effect at 12 and 13 given that assertion
+
 -- let them divorce a while later (using the same dereferencing set)
 -- create a posit thing
 drop table if exists #Posit7;
 create table #Posit7([UID] uniqueidentifier not null primary key);
--- now the situation looks better for Bella between 12 and 13
+insert into [Thing]([UID]) output inserted.[UID] into #Posit7 values (DEFAULT);
 
 -- create a posit
 insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
--- Bella is contradictory for the appearance time between 12 and 13 but now only for
+select p.[UID], s.[UID], 'divorced', '2010-01-31 10:00'
--- assertion time between 15:35 and 15:45
+from #Posit7 p, #DereferencingSet3 s;
 
 -- the state of this dereferencing set has transitioned from 'married' to 'divorced'
 select * from [v_Posit]
-declare @ArchieUID12 varchar(555) = (
+where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+-- let Bella assert this as well, so first create an assertion thing
 drop table if exists #Assertion12;
-declare @DisserUID12 varchar(555) = (
+create table #Assertion12([UID] uniqueidentifier not null primary key);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion12 values (DEFAULT);
-	cross apply AssertionXML.nodes('//*[Value = "Disser"]//*[@Role = "has name"]') as T(Appearance)
+
 -- create an assertion
-declare @assertion12 xml = '
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	<Assertion PositorUID="' + @DisserUID12 + '" Reliability="0" AssertionTime="2018-12-13 15:40">
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
-	  <Posit AppearanceTime="2018-12-01 12:00">
+from #Assertion12 a, #B Bella, #Posit7 p;
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID12 + '" Role="has beard" />
+-- now Bella has asserted the divorce
-		</DereferencingSet>
+
-		<Value>shaved clean</Value>
+where [DereferencingSetUID] = (select top 1 [UID] from #DereferencingSet3)
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion12 OUTPUT;
+
 
 -- first the Disagreer must appear as husband
 drop table if exists #Appearance6;
 create table #Appearance6([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Appearance6 values (DEFAULT);
 
--- the Disser is now only saying it's not fluffy red
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
 select app.[UID], Disagreer.[UID], r.[RoleUID]
 from [Role] r, #Appearance6 app, #D Disagreer
-declare @ArchieUID13 varchar(555) = (
+where r.[Role] = 'husband';
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+-- create a dereferencing set thing
 drop table if exists #DereferencingSet4;
-declare @BellaUID13 varchar(555) = (
+create table #DereferencingSet4([UID] uniqueidentifier not null primary key);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet4 values (DEFAULT);
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+
 -- create a dereferencing set
-declare @assertion13 xml = '
+insert into [DereferencingSet]([DereferencingSetUID])
-	<Assertion PositorUID="' + @BellaUID13 + '" Reliability="1.00" AssertionTime="2018-12-13 15:45">
+select [UID]
-	  <Posit AppearanceTime="2018-12-01 14:00">
+from #DereferencingSet4;
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID13 + '" Role="has beard" />
+-- add the created appearances to the dereferencing set (with only two members)
-		</DereferencingSet>
+insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
-		<Value>shaved clean</Value>
+select s.[UID], app.[UID]
-	  </Posit>
+from #DereferencingSet4 s, #Appearance6 app;
-	</Assertion>
+insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
-';
+select s.[UID], app.[UID]
-exec Assert @assertion13 OUTPUT;
+from #DereferencingSet4 s, #Appearance4 app;
 
 -- create a posit thing
-select * from [v_Assertion] where [Value] in ('fluffy red', 'shaved clean')
+drop table if exists #Posit8;
 create table #Posit8([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Posit8 values (DEFAULT);
 
 -- create a posit
 insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
 select p.[UID], s.[UID], 'married', '2011-11-11 11:11'
 from #Posit8 p, #DereferencingSet4 s;
 
 -- let Bella assert this as well, so first create an assertion thing
 drop table if exists #Assertion13;
 create table #Assertion13([UID] uniqueidentifier not null primary key);
-declare @ArchieUID14 varchar(555) = (
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion13 values (DEFAULT);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+-- create an assertion
 insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-declare @BellaUID14 varchar(555) = (
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 15:55'
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+from #Assertion13 a, #B Bella, #Posit8 p;
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+
 -- now Bella has asserted her second marriage
-declare @assertion14 xml = '
+-- we can find this from finding every assertion in which Bella has appeared in the wife role
-	<Assertion PositorUID="' + @BellaUID14 + '" Reliability="1.00" AssertionTime="2018-12-13 15:50">
+
-	  <Posit AppearanceTime="2018-12-01 14:00">
+-- note: it is also possible to achieve the same result using joins instead of the XML query
-		<DereferencingSet>
+select a.* from [v_Assertion] a cross apply #B Bella
-		  <Appearance ThingUID="' + @ArchieUID14 + '" Role="has beard" />
+where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
-		</DereferencingSet>
+
-		<Value>shaved clean</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+drop table if exists #Assertion14;
-exec Assert @assertion14 OUTPUT;
+create table #Assertion14([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Assertion14 values (DEFAULT);
 
-select * from [v_Assertion] where [Value] in ('fluffy red', 'shaved clean')
+-- create an assertion (that is a retraction since Reliability = 0)
 insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
 select a.[UID], Bella.[UID], p.[UID], 0, '2018-12-13 16:00'
 from #Assertion14 a, #B Bella, #Posit8 p;
 
 -- in the view of all assertions the retraction is now visible
 select a.* from [v_Assertion] a cross apply #B Bella
 where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
 order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
 
 -- which means that the latest information we have is that Bella is divorced
 select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
 where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
 order by [PositorUID], [AppearanceTime] desc, [AssertionTime] desc;
 
 -- so Bella needs to assert a different posit, with the correct appearance time
 drop table if exists #Posit9;
 create table #Posit9([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Posit9 values (DEFAULT);
 
 insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
--- we now have 42 things that we want to create metadata for
+select p.[UID], s.[UID], 'married', '2012-12-12 12:12'
 from #Posit9 p, #DereferencingSet4 s;
 
-declare @ScriptUID15 varchar(555) = (
+drop table if exists #Assertion15;
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+create table #Assertion15([UID] uniqueidentifier not null primary key);
-	cross apply AssertionXML.nodes('//*[Value = "Script"]//*[@Role = "has name"]') as T(Appearance)
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion15 values (DEFAULT);
 
 insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-declare thing cursor static for (select [UID] from [Thing]);
+select a.[UID], Bella.[UID], p.[UID], 1, '2018-12-13 16:00'
-open thing;
+from #Assertion15 a, #B Bella, #Posit9 p;
 
-declare @ThingUID15 varchar(555);
+
-fetch next from thing into @ThingUID15;
+select a.* from [v_Assertion] a cross apply #B Bella
 where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
-declare @now char(19) = CONVERT(varchar(19), getdate(), 121);
+
 
-while(@@FETCH_STATUS = 0)
+
-begin
+
-	declare @assertion15 xml = '
+select a.* from [Information_in_Effect](getdate(), getdate()) a cross apply #B Bella
-		<Assertion PositorUID="' + @ScriptUID15 + '" Reliability="1.00" AssertionTime="' + @now + '">
+where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@UID = sql:column("Bella.UID") and @Role = "wife"]') = 1
-		  <Posit AppearanceTime="' + @now + '">
+
-			<DereferencingSet>
+
-			  <Appearance ThingUID="' + @ThingUID15 + '" Role="was recorded at" />
+
-			</DereferencingSet>
+
-			<Value>' + @now + '</Value>
+
-		  </Posit>
+
-		</Assertion>
+
-	';
+
-	exec Assert @assertion15 OUTPUT;
+
 of a class through the string c and associates the unique
-	fetch next from thing into @ThingUID15;
+
-end
+
 pM = [{(i, thing),(C, class)}, v, t].
-close thing;
+
-deallocate thing;
+
 It is time for the Modeler to step in and tell us what some of our things are.
--- now there are 42 assertions from which we can tell when the data entered the database
+
-select * from [v_Assertion] where isdate([Value]) = 1
+-- we need three new roles in order to create a model
 drop table if exists #Role6;
 create table #Role6([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Role6 values (DEFAULT);
 drop table if exists #Role7;
 create table #Role7([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Role7 values (DEFAULT);
--- but it is in effect now
+drop table if exists #Role8;
-select * from [Information_in_Effect](getdate(), getdate())
+create table #Role8([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Role8 values (DEFAULT);
 
 -- create the new roles
--- we will start by marrying Archie and Bella in the Notre-Dame
+insert into [Role]([RoleUID], [Role])
--- so we first need the Notre-Dame thing
+select [UID], 'is class'
-declare @assertion16 xml = '
+from #Role6;
-	<Assertion PositorUID="NDame" Reliability="1.00" AssertionTime="2018-12-15 00:00">
+insert into [Role]([RoleUID], [Role])
-	  <Posit AppearanceTime="1753-01-01">
+select [UID], 'thing'
-		<DereferencingSet>
+from #Role7;
-		  <Appearance ThingUID="NDame" Role="has name" />
+insert into [Role]([RoleUID], [Role])
-		</DereferencingSet>
+select [UID], 'class'
-		<Value>Notre-Dame de Paris</Value>
+from #Role8;
-	  </Posit>
+
-	</Assertion>
+-- what roles have got now?
-';
+select * from [Role] order by RoleUID;
-exec Assert @assertion16 OUTPUT;
+
 -- create two class things
--- we need the UID:s
+drop table if exists #Class1;
-declare @ArchieUID17 varchar(555) = (
+create table #Class1([UID] uniqueidentifier not null primary key);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [Thing]([UID]) output inserted.[UID] into #Class1 values (DEFAULT);
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+drop table if exists #Class2;
 create table #Class2([UID] uniqueidentifier not null primary key);
-declare @BellaUID17 varchar(555) = (
+insert into [Thing]([UID]) output inserted.[UID] into #Class2 values (DEFAULT);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+-- the classes need names, so first they need to appear with the "is class" role
 drop table if exists #Appearance7;
-declare @NDameUID17 varchar(555) = (
+create table #Appearance7([UID] uniqueidentifier not null primary key);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [Thing]([UID]) output inserted.[UID] into #Appearance7 values (DEFAULT);
-	cross apply AssertionXML.nodes('//*[Value = "Notre-Dame de Paris"]//*[@Role = "has name"]') as T(Appearance)
+drop table if exists #Appearance8;
 create table #Appearance8([UID] uniqueidentifier not null primary key);
-declare @assertion17 xml = '
+insert into [Thing]([UID]) output inserted.[UID] into #Appearance8 values (DEFAULT);
-	<Assertion PositorUID="' + @BellaUID17 + '" Reliability="1.00" AssertionTime="2018-12-13 15:55">
+
-	  <Posit AppearanceTime="2004-06-19 15:00">
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
-		<DereferencingSet>
+select app.[UID], c.[UID], r.[RoleUID]
-		  <Appearance ThingUID="' + @ArchieUID17 + '" Role="husband" />
+from [Role] r, #Appearance7 app, #Class1 c
-		  <Appearance ThingUID="' + @BellaUID17 + '" Role="wife" />
+where r.[Role] = 'is class';
-		  <Appearance ThingUID="' + @NDameUID17 + '" Role="church" />
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
-		</DereferencingSet>
+select app.[UID], c.[UID], r.[RoleUID]
-		<Value>married</Value>
+from [Role] r, #Appearance8 app, #Class2 c
-	  </Posit>
+where r.[Role] = 'is class';
-	</Assertion>
+
-';
+-- create a dereferencing set thing
-exec Assert @assertion17 OUTPUT;
+drop table if exists #DereferencingSet5;
 create table #DereferencingSet5([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet5 values (DEFAULT);
-select * from [v_Assertion] where [Value] in ('married')
+drop table if exists #DereferencingSet6;
 create table #DereferencingSet6([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #DereferencingSet6 values (DEFAULT);
--- let them divorce a while later (using the same dereferencing set, so it is a real change)
+
-declare @ArchieUID18 varchar(555) = (
+-- create dereferencing sets
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [DereferencingSet]([DereferencingSetUID])
-	cross apply AssertionXML.nodes('//*[Value = "Archie"]//*[@Role = "has name"]') as T(Appearance)
+select [UID]
 from #DereferencingSet5;
-declare @BellaUID18 varchar(555) = (
+insert into [DereferencingSet]([DereferencingSetUID])
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+select [UID]
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+from #DereferencingSet6;
 
-declare @NDameUID18 varchar(555) = (
+-- add the created appearances
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
-	cross apply AssertionXML.nodes('//*[Value = "Notre-Dame de Paris"]//*[@Role = "has name"]') as T(Appearance)
+select s.[UID], app.[UID]
 from #DereferencingSet5 s, #Appearance7 app;
-declare @assertion18 xml = '
+insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
-	<Assertion PositorUID="' + @BellaUID18 + '" Reliability="1.00" AssertionTime="2018-12-13 15:55">
+select s.[UID], app.[UID]
-	  <Posit AppearanceTime="2010-01-31 10:00">
+from #DereferencingSet6 s, #Appearance8 app;
-		<DereferencingSet>
+
-		  <Appearance ThingUID="' + @ArchieUID18 + '" Role="husband" />
+-- two posit things
-		  <Appearance ThingUID="' + @BellaUID18 + '" Role="wife" />
+drop table if exists #Posit10;
-		  <Appearance ThingUID="' + @NDameUID18 + '" Role="church" />
+create table #Posit10([UID] uniqueidentifier not null primary key);
-		</DereferencingSet>
+insert into [Thing]([UID]) output inserted.[UID] into #Posit10 values (DEFAULT);
-		<Value>divorced</Value>
+drop table if exists #Posit11;
-	  </Posit>
+create table #Posit11([UID] uniqueidentifier not null primary key);
-	</Assertion>
+insert into [Thing]([UID]) output inserted.[UID] into #Posit11 values (DEFAULT);
-';
+
-exec Assert @assertion18 OUTPUT;
+-- two posits
 insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
--- now Bella has asserted the divorce as well
+select p.[UID], s.[UID], 'Person', '1901-01-01' -- will represent dawn of time
-select * from [v_Assertion] where [Value] in ('married', 'divorced')
+from #Posit10 p, #DereferencingSet5 s;
 insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
 select p.[UID], s.[UID], 'Cathedral', '1901-01-01' -- will represent dawn of time
 from #Posit11 p, #DereferencingSet6 s;
 
-declare @DisserUID19 varchar(555) = (
+-- now, the Modeler must assert these
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+drop table if exists #Assertion16;
-	cross apply AssertionXML.nodes('//*[Value = "Disser"]//*[@Role = "has name"]') as T(Appearance)
+create table #Assertion16([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Assertion16 values (DEFAULT);
-declare @BellaUID19 varchar(555) = (
+drop table if exists #Assertion17;
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+create table #Assertion17([UID] uniqueidentifier not null primary key);
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+insert into [Thing]([UID]) output inserted.[UID] into #Assertion17 values (DEFAULT);
 
-declare @assertion19 xml = '
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-	<Assertion PositorUID="' + @BellaUID19 + '" Reliability="1.00" AssertionTime="2018-12-13 15:55">
+select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
-	  <Posit AppearanceTime="2011-11-11 11:11">
+from #Assertion16 a, #M Modeler, #Posit10 p;
-		<DereferencingSet>
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-		  <Appearance ThingUID="' + @DisserUID19 + '" Role="husband" />
+select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
-		  <Appearance ThingUID="' + @BellaUID19 + '" Role="wife" />
+from #Assertion17 a, #M Modeler, #Posit11 p;
-		</DereferencingSet>
+
-		<Value>married</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion19 OUTPUT;
+
 -- lot's of work, but two classes are defined
--- now Bella has asserted her second marriage, but her second marriage took place without a church
+-- they have their own unique identifiers, so many more roles can be added to provide additional
-select * from [v_Assertion] where [Value] in ('married', 'divorced')
+-- information about the classes
 
 -- now it is time to associate some things with the classes
--- another way to find this is from finding every assertion in which Bella has appeared in the wife role
+-- every such thing needs an appearance with the 'thing' role
 drop table if exists #Appearance9;
-declare @BellaUID_Q1 varchar(555) = (
+create table #Appearance9([UID] uniqueidentifier not null primary key);
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+merge [Thing] t
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+using (
 	select [UID] from #A
 	union all
-where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@ThingUID = sql:variable("@BellaUID_Q1") and @Role = "wife"]') = 1
+	select [UID] from #B
 	union all
--- the above can be achieved through joins in the underlying model as well
+	select [UID] from #C
 	union all
 	select [UID] from #M
 	union all
-declare @DisserUID20 varchar(555) = (
+	select [UID] from #D
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+) src
-	cross apply AssertionXML.nodes('//*[Value = "Disser"]//*[@Role = "has name"]') as T(Appearance)
+on 1 = 0 -- make sure everything is "not matched" below
 when not matched then insert ([UID]) values (DEFAULT)
-declare @BellaUID20 varchar(555) = (
+output inserted.[UID] into #Appearance9;
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+-- create an equal number of appearances
 insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
-declare @assertion20 xml = '
+select app.[UID], t.[UID], r.[RoleUID]
-	<Assertion PositorUID="' + @BellaUID20 + '" Reliability="0" AssertionTime="2018-12-13 16:00">
+from (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
-	  <Posit AppearanceTime="2011-11-11 11:11">
+join (select [UID], row_number() over (order by [UID]) as _row from (
-		<DereferencingSet>
+	select [UID] from #A
-		  <Appearance ThingUID="' + @DisserUID20 + '" Role="husband" />
+	union all
-		  <Appearance ThingUID="' + @BellaUID20 + '" Role="wife" />
+	select [UID] from #B
-		</DereferencingSet>
+	union all
-		<Value>married</Value>
+	select [UID] from #C
-	  </Posit>
+	union all
-	</Assertion>
+	select [UID] from #M
-';
+	union all
-exec Assert @assertion20 OUTPUT;
+	select [UID] from #D
 ) things ) t
 on t._row = app._row
-declare @BellaUID_Q2 varchar(555) = (
+cross apply (select [RoleUID] from [Role] where [Role] = 'thing') r;
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+-- then two more appearances (one for each associated class)
 drop table if exists #Appearance10;
 create table #Appearance10([UID] uniqueidentifier not null primary key);
-where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@ThingUID = sql:variable("@BellaUID_Q2") and @Role = "wife"]') = 1
+insert into [Thing]([UID]) output inserted.[UID] into #Appearance10 values (DEFAULT);
 drop table if exists #Appearance11;
 create table #Appearance11([UID] uniqueidentifier not null primary key);
 insert into [Thing]([UID]) output inserted.[UID] into #Appearance11 values (DEFAULT);
 
-where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@ThingUID = sql:variable("@BellaUID_Q2") and @Role = "wife"]') = 1
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
 select app.[UID], c.[UID], r.[RoleUID]
 from [Role] r, #Appearance10 app, #Class1 c
 where r.[Role] = 'class';
-declare @DisserUID21 varchar(555) = (
+insert into [Appearance]([AppearanceUID], [ThingUID], [RoleUID])
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+select app.[UID], c.[UID], r.[RoleUID]
-	cross apply AssertionXML.nodes('//*[Value = "Disser"]//*[@Role = "has name"]') as T(Appearance)
+from [Role] r, #Appearance11 app, #Class2 c
 where r.[Role] = 'class';
-declare @BellaUID21 varchar(555) = (
+
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+-- create dereferencing set things (equal in number to the things we want to classify)
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+drop table if exists #DereferencingSet7;
 create table #DereferencingSet7([UID] uniqueidentifier not null primary key);
-declare @assertion21 xml = '
+merge [Thing] t
-	<Assertion PositorUID="' + @BellaUID21 + '" Reliability="1.00" AssertionTime="2018-12-13 16:00">
+using #Appearance9 src on 1 = 0 -- make sure everything is "not matched" below
-	  <Posit AppearanceTime="2012-12-12 12:12">
+when not matched then insert ([UID]) values (DEFAULT)
-		<DereferencingSet>
+output inserted.[UID] into #DereferencingSet7;
-		  <Appearance ThingUID="' + @DisserUID21 + '" Role="husband" />
+
-		  <Appearance ThingUID="' + @BellaUID21 + '" Role="wife" />
+-- create 22 dereferencing sets
-		</DereferencingSet>
+insert into [DereferencingSet]([DereferencingSetUID])
-		<Value>married</Value>
+select [UID]
-	  </Posit>
+from #DereferencingSet7;
-	</Assertion>
+
-';
+-- add the appearances to the dereferencing sets
-exec Assert @assertion21 OUTPUT;
+-- first the things
 insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
 select s.[UID], app.[UID]
-declare @BellaUID_Q3 varchar(555) = (
+from (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+join (select [UID], row_number() over (order by [UID]) as _row from #Appearance9) app
-	cross apply AssertionXML.nodes('//*[Value = "Bella"]//*[@Role = "has name"]') as T(Appearance)
+on s._row = app._row;
 -- then the classes
 insert into [Dereference]([DereferencingSetUID], [AppearanceUID])
-where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@ThingUID = sql:variable("@BellaUID_Q3") and @Role = "wife"]') = 1
+select
 	s.[UID],
 		case
 			when exists (
 					select top 1 d.[DereferencingSetUID]
 					from [Dereference] d
-where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@ThingUID = sql:variable("@BellaUID_Q3") and @Role = "wife"]') = 1
+					join [Appearance] a
 					  on a.[AppearanceUID] = d.[AppearanceUID]
--- there is currently no way for the system to know that these two relationships (with and without a church) both
+					join (
--- represent a marriage
+						select [UID] from #A
 						union all
 						select [UID] from #B
 						union all
 						select [UID] from #M
 						union all
 						select [UID] from #D
 					) persons
 					on persons.[UID] = a.[ThingUID]
 					where d.[DereferencingSetUID] = s.[UID]
 			) then Person.[UID]
 			else Cathedral.[UID]
 		end
 from #DereferencingSet7 s, #Appearance10 Person, #Appearance11 Cathedral;
 
 -- create posit things
 drop table if exists #Posit12;
 create table #Posit12([UID] uniqueidentifier not null primary key);
--- first create three classes: Person, Cathedral, and Church
+merge [Thing] t
-declare @ModelerUID22 varchar(555) = (
+using #DereferencingSet7 src on 1 = 0 -- make sure everything is "not matched" below
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+when not matched then insert ([UID]) values (DEFAULT)
-	cross apply AssertionXML.nodes('//*[Value = "Modeler"]//*[@Role = "has name"]') as T(Appearance)
+output inserted.[UID] into #Posit12;
 
-declare @assertion22A xml = '
+-- create posits
-	<Assertion PositorUID="' + @ModelerUID22 + '" Reliability="1.00" AssertionTime="2018-12-01 00:00">
+insert into [Posit]([PositUID], [DereferencingSetUID], [Value], [AppearanceTime])
-	  <Posit AppearanceTime="1998-12-31 23:59">
+select p.[UID], s.[UID], 'active', '1901-01-01'
-		<DereferencingSet>
+from (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
-		  <Appearance ThingUID="C" Role="is class" />
+join (select [UID], row_number() over (order by [UID]) as _row from #DereferencingSet7) s
-		</DereferencingSet>
+on p._row = s._row;
-		<Value>Person</Value>
+
-	  </Posit>
+-- finally let the Modeler positor assert these
-	</Assertion>
+drop table if exists #Assertion18;
-';
+create table #Assertion18([UID] uniqueidentifier not null primary key);
-exec Assert @assertion22A OUTPUT;
+merge [Thing] t
-declare @assertion22B xml = '
+using #Posit12 src on 1 = 0 -- make sure everything is "not matched" below
-	<Assertion PositorUID="' + @ModelerUID22 + '" Reliability="1.00" AssertionTime="2018-12-01 00:00">
+when not matched then insert ([UID]) values (DEFAULT)
-	  <Posit AppearanceTime="1998-12-31 23:59">
+output inserted.[UID] into #Assertion18;
-		<DereferencingSet>
+
-		  <Appearance ThingUID="C" Role="is class" />
+insert into [Assertion]([AssertionUID], [PositorUID], [PositUID], [Reliability], [AssertionTime])
-		</DereferencingSet>
+select a.[UID], Modeler.[UID], p.[UID], 1, '2018-12-01 00:00'
-		<Value>Cathedral</Value>
+from (select [UID], row_number() over (order by [UID]) as _row from #Assertion18) a
-	  </Posit>
+join (select [UID], row_number() over (order by [UID]) as _row from #Posit12) p
-	</Assertion>
+on a._row = p._row
-';
+cross apply #M Modeler;
-exec Assert @assertion22B OUTPUT;
+
-declare @assertion22C xml = '
+
-	<Assertion PositorUID="' + @ModelerUID22 + '" Reliability="1.00" AssertionTime="2018-12-01 00:00">
+-- as can be seen there are four things of class Person and one thing of class Cathedral
-	  <Posit AppearanceTime="1998-12-31 23:59">
+
-		<DereferencingSet>
+where AssertionXML.exist('/Assertion/Posit/DereferencingSet/Appearance[@Role = "is class" or @Role = "class"]') = 1
-		  <Appearance ThingUID="C" Role="is class" />
+
-		</DereferencingSet>
+
-		<Value>Church</Value>
+
-	  </Posit>
+
-	</Assertion>
+
-';
+
-exec Assert @assertion22C OUTPUT;
+
 posit p = [{(i1,r1), . . . ,(in,rn)}, v, t], is a structure constructed
 by replacing unique identifiers, ij with the unique identifiers of their
 class, Cj, the value, v, with its data type, τ(v), and the time point, t,
 with its data type, τ(t).
 -----------------------------------------------------------------------------------------------
 
--- now associate some things with the classes
+
-declare @ModelerUID23 varchar(555) = (
+values of type varchar(max), so it cannot hold such a structure. It could either
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+be expressed as serialized XML or JSON, or we could let the field contain a
-	cross apply AssertionXML.nodes('//*[Value = "Modeler"]//*[@Role = "has name"]') as T(Appearance)
+reference to an identifier in a separate table that replicates the structure.
 Since a posit type contains a set with variable number of members, it is
-declare @ArchieUID23 varchar(555) = (
+easier in this case to express it using XML.
-	select Appearance.value('@ThingUID', 'varchar(555)') from v_Assertion
+*/