Integrated Curation Data Model

Database Schema

Factual Data

Node Tabel Schema: xxxData

create table concertData (
	id nvarchar(40) not null,
	class nvarchar(16) null,
	groupName nvarchar(16) null,
	partName nvarchar(40) null,
	label nvarchar(80) null,
	infoUrl nvarchar(256) null,
	iconUrl nvarchar(256) null,
	primary key(id)
)

Edge Table Schema: xxxLinks

create table concertLinks (
	source  nvarchar(40)  not null,
	target  nvarchar(40) not null,
	relation nvarchar(40) not null,
	attribute nvarchar(40) null,
	primary key (source, target, relation )
)

Category Data

※ 분류 색인 서비스를 위한 Category Data는 Factual Data Node의 속성 정보에서 기계적으로 추출합니다.

Node Table Schema: xxxSchema

create table xxxSchema (
	id nvarchar(40) not null,
	class nvarchar(16) null,
	groupName nvarchar(16) null,
	partName nvarchar(40) null,
	label nvarchar(80) null,
	infoUrl nvarchar(256) null,
	iconUrl nvarchar(256) null,
	primary key(id)
)

/* Virtual View: xxxNodes */

create view xxxNodes as
select * from concertData
union
select * from concertSchema

Edge Table Schema: xxxCategory

create table xxxCategory (
	source  nvarchar(40)  not null,
	target  nvarchar(40) not null,
	relation nvarchar(40) not null,
	attribute nvarchar(40) null,
	primary key (source, target, relation )
)

Object Properties

* hasCategory
* hasMember

Storyline Data

※ Storyline Data는 Factcual Data와 다른 이름을 갖는 독립적 Data Set입니다. Storyline Data의 Edge Table은 Node Table로부터 자동 변환합니다.

Node Table Schema

create table yyyData (
	id nvarchar(40) not null,
	class nvarchar(16) null,
	upperNode nvarchar(40) null,
	nextNode nvarchar(40) null,
	label nvarchar(80) null,
	infoUrl nvarchar(256) null,
	iconUrl nvarchar(256) null,
	primary key(id)
)

Edge Table Schema

create table yyyLinks (
	source  nvarchar(40)  not null,
	target  nvarchar(40) not null,
	relation nvarchar(40) not null,
	attribute nvarchar(40) null,
	primary key (source, target, relation )
)

Object Properties

* hasTopic
* hasEpisode
* hasFactualComponent
* isPreviousInSequence
* isNextInSequence

SQL Script

Factual Data 처리

/* insert node data */

insert into xxxData (id, class, groupName, partName, label, infoUrl, iconUrl )
select id, class, groupName, partName, label, infoUrl, iconUrl  
	from xxxData$

/* insert link data */

insert into xxxLinks (source, target, relation)
select source, target, relation from xxxLinks$

Category Data 처리

/* xxxSchema 데이터 처리 자동화 Script */

insert into xxxSchema (id, class, groupName, label)
values ('Thing', 'Category', 'Top', 'Thing' )

insert into xxxSchema (id, class, groupName, label)
select class as id, 'Category' as class, 'Thing' groupName, class as label
from concertData
group by class

insert into xxxSchema (id, class, groupName, label)
select groupName as id, 'Category' as class, class as groupName, groupName as label
from concertData
where groupName is not NULL
group by class, groupName

insert into xxxSchema (id, class, groupName, label)
select partName as id, 'Category' as class, groupName, partName as label
from concertData
where partName is not NULL
group by groupName, partName

/* xxxCategory 데이터 처리 자동화 Script */

-- hasCategory (top->1st)
insert into concertCategory (source, target, relation )
select 'Thing' as source, class as target, 'hasCategory' as 'relation' from concertData
group by class

-- hasCategory (1st->2nd)
insert into concertCategory (source, target, relation )
select class as source, groupName as target, 'hasCategory' as 'relation' from concertData
where groupName is not NULL
group by class, groupName

-- hasCategory (2nd->3rd)
insert into concertCategory (source, target, relation )
select groupName as source, partName as target, 'hasCategory' as 'relation' from concertData
where partName is not NULL
group by class, groupName, partName

-- hasMember (Major Category)
insert into concertCategory (source, target, relation )
select class as source, id as target, 'hasMember' as 'relation' from concertData
where groupName is NULL
order by class, id

-- hasMember  (2nd Category)
insert into concertCategory (source, target, relation )
select groupName as source, id as target, 'hasMember' as 'relation' from concertData
where groupName is not NULL and partName is NULL
order by groupName, id

-- hasMember  (3rd Category)
insert into concertCategory (source, target, relation )
select partName as source, id as target, 'hasMember' as 'relation' from concertData
where partName is not NULL
order by partName, id

Storyline Data 처리

/* insert node data */

insert into yyyData (id, class, upperNode, nextNode, label, infoUrl, iconUrl )
select id, class, upperNode, nextNode, label, infoUrl, iconUrl from yyyData$

/* generate link data */

-- hasTopic
insert into yyyLinks (source, target, relation )
select upperNode, id, 'hasTopic' from narrativeData
where class='Topic' and upperNode is Not Null 

-- hasEpisode
insert into yyyLinks (source, target, relation )
select upperNode, id, 'hasEpisode' from narrativeData
where class='Episode' and upperNode is Not Null

-- isPeviousInSequence
insert into yyyLinks (source, target, relation )
select id, nextNode, 'isPreviousInSequence' from narrativeData
where nextNode is Not Null