DDBC convert sql

DH 교육용 위키
Hiblue (토론 | 기여) 사용자의 2018년 8월 10일 (금) 14:37 판

이동: 둘러보기, 검색
use hiblue
select * from t_month order by last
--dhtime JDDate 
--ddbc Month
	
	--month time:after month
	select distinct '<http://authority.dlia.edu.tw/time#mon'+a.id+'>' as source, '<http://www.w3.org/2006/time#after>' as relation,  '<http://authority.dlia.edu.tw/time#mon'+b.id+'>' as target from t_month a, t_month b where a.last+1=b.first order by source

	--month time:before month
	select distinct '<http://authority.dlia.edu.tw/time#mon'+a.id+'>' as source, '<http://www.w3.org/2006/time#after>' as relation,  '<http://authority.dlia.edu.tw/time#mon'+b.id+'>' as target from t_month a, t_month b where a.last+1=b.first order by source

	--month label 
	select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+month_name+'"'+'@hanja' from t_month --질문!! 한자가 맞는가?
	select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+month+'"^^xsd:nonNegativeInteger' from t_month

	--month hasBeginning day
	select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+first+'>' from t_month
	--month hasEnd day
	select distinct '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+last+'>' from t_month

	-- month inside day (inverse of 'month') (예상)
	select distinct '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+t_day.id+'>' from t_month, t_day where t_month.first <= t_day.id and t_month.last >= t_day.id
		--select * from t_month where id in (10005, 10009, 10010, 10011, 10012, 10013)
	
	-- month days (월일수)
	select '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>', '<http://www.w3.org/2006/time#days>','"'+cast(cast(last as int)-cast(first as int) as nvarchar)+'"^^xsd:nonNegativeInteger' from t_month
	-- month leap (윤달)
	select '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#leap>','"'+cast(leap_month as nvarchar)+'"^^xsd:nonNegativeInteger' from t_month

--ddbc Year
   	--create t_year
	create table t_year (id INT IDENTITY(1,1) not null, eraNumber int, era_id int, ganzhi nvarchar(20), first int, last int)
	insert into t_year select year as eraNumber, era_id, ganzhi, min(first) as first, max(last) as last from t_month group by year, era_id, ganzhi order by first
	--connect year month
	update t_month set year_id=a.year_id from (select t_month.id as month_id, t_year.id as year_id from t_year,t_month where t_month.year=t_year.eraNumber and t_month.era_id=t_year.era_id and t_month.ganzhi=t_year.ganzhi) a where a.month_id=t_month.id
		--select year, era_id, ganzhi, first, last, cast(last as int) - cast(first as int) from t_year 
		--select * from t_month where id in (83069, 83070, 83071, 83068, 83067)
	--year hasBeginning day 	  
		select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(t_year.first as nvarchar)+'>' from t_year 
	--year hasEnd day
		select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(t_year.last as nvarchar)+'>' from t_year 
    --year inside day(inverse of 'year') (예상)
		select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+t_day.id+'>' from t_year, t_day where t_day.id<=t_year.last and t_day.id>=t_year.first 

		select '<http://authority.dlia.edu.tw/time#day'+t_day.id+'>',  '<http://dh.aks.ac.kr/ontologies/dhtime#year>',  '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>' from t_year, t_day where t_day.id<=t_year.last and t_day.id>=t_year.first 
	--year days (연일수)
		select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(t_year.last-t_year.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from t_year

	--year hasBeginning month
		select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>' from t_year, t_month where t_year.first=t_month.first
	--year hasEnd month
		select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>' from t_year, t_month where t_year.last=t_month.last
	--year-inside-month  (inverse of year)
		select '<http://authority.dlia.edu.tw/time#year'+cast(t_year.id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#mon'+t_month.id+'>' from t_year, t_month where t_month.year_id=t_year.id
	--year before year
		select '<http://authority.dlia.edu.tw/time#year'+cast(a.id as nvarchar)+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from  t_year a, t_year b where a.first-1=b.last
	--year-after-year
		select '<http://authority.dlia.edu.tw/time#year'+cast(a.id as nvarchar)+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from  t_year a, t_year b where a.last+1=b.first
	--year - era Era
		select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#era>','<http://authority.dlia.edu.tw/time#era'+cast(era_id as nvarchar)+'>' from t_year
    --year - eraNumber
		select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#eraNum>','"'+cast(eraNumber as nvarchar)+'"^^xsd:nonNegativeInteger' from t_year
    --year ganzhi
	select '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#ganzhi>','"'+ganzhi+'"^^xsd:string@hanja' from t_year

--ddbc era
	update t_era_names set name=ltrim(rtrim(name))
	--era-label
	select '<http://authority.dlia.edu.tw/time#era'+cast(era_id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+name+'"@'+short_name_eng from t_era_names, c_languages where t_era_names.language_id=c_languages.id
	--왕년/시연호 = 연호를 쓰지 않는 경우! 그대로 둘것인가 삭제?? 고민여부 -  번역원은 값을 넣지 않음
	--select distinct eraName, emperor.name from era, emperor where eraName like '%(%' and era.emperor_id=emperor.emperor_id
	--era- hasBeginning day
	    select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>' as source, '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(a.first as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a order by source
	--era hasEnd day
	    select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>' as source, '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(a.last as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a order by source	
	--era inside day  (inverse of era)
		select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>' as source, '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+cast(t_day.id as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_day where t_era.first <= t_day.id and t_era.last >= t_day.id
	--era days (연호일수)
		select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(a.last-a.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, 
	--era hasBeginning year
	select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.first=b.first and a.era_id=b.era_id
	--era hasEnd year
    select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#year'+cast(b.id as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.last=b.last and a.era_id=b.era_id
    --era inside year  (inverse of era)
	select '<http://authority.dlia.edu.tw/time#era'+cast(era_id as nvarchar)+'>', '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#year'+cast(id as nvarchar)+'>' from t_year
		
	--era Beginning month
	select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(b.id as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.first=b.first and a.era_id=b.era_id
	--era hasEnd month
	select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(b.id as nvarchar)+'>' from
		(select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, t_year b where a.first=b.first and a.era_id=b.era_id
    --era inside month (inverse of era)
	select '<http://authority.dlia.edu.tw/time#era'+t_era.id+'>', '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from
		t_month,t_year,t_era where t_month.year_id=t_year.id and t_year.era_id=t_era.id
	
	--era before era
	select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#era'+b.era_id+'>' from (select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, (select era_id, min(first) as first, max(last) as last from t_month group by era_id) b where a.first-1=b.last
	--era after era 
	select '<http://authority.dlia.edu.tw/time#era'+a.era_id+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#era'+b.era_id+'>' from (select era_id, min(first) as first, max(last) as last from t_month group by era_id) a, (select era_id, min(first) as first, max(last) as last from t_month group by era_id) b where a.last=b.first+1
				

--emporor
	--emperor label
	select '<http://authority.dlia.edu.tw/time#emperor'+cast(emperor_id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+name+'"@'+short_name_eng from t_emperor_names, c_languages where t_emperor_names.language_id=c_languages.id

	--emperor hasbeginning day
	 select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(a.first as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a --685
		--select emperor_id from t_year,t_era where t_year.era_id=t_era.id group by emperor_id 매칭갯수확인 685
	--emperor hasEnd day
	 select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(a.last as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a 
	
	--emperor inside day (inverse of emperor)
	select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+cast(t_day.id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a , t_day where t_day.id<=a.last and t_day.id>=a.first 
	--emperor days(재위일수)
	select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(a.last-a.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a

	--emperor hasbeginning month
	 select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.first = t_month.first and t_month.era_id=t_era.id and t_era.emperor_id=a.id
	--emperor hasEnd month
	select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.last = t_month.last and t_month.era_id=t_era.id and t_era.emperor_id=a.id
	--emporor inside month (inverse of emperor)
	select '<http://authority.dlia.edu.tw/time#emperor'+t_era.emperor_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#mon'+cast(t_month.id as nvarchar)+'>' from t_era, t_month where t_month.era_id=t_era.id
	
	--emperor hasBeginning year
	select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#year'+cast(t_month.year_id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.first = t_month.first and t_month.era_id=t_era.id and t_era.emperor_id=a.id
	--emporor hasEnd year
	select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#year'+cast(t_month.year_id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.last = t_month.last and t_month.era_id=t_era.id and t_era.emperor_id=a.id
	--emperor inside year (inverse of emperor)
	select '<http://authority.dlia.edu.tw/time#emperor'+t_era.emperor_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#year'+cast(t_month.year_id as nvarchar)+'>' from t_era, t_month where t_month.era_id=t_era.id

	--emperor hasBeginning era
	 select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#era'+cast(t_era.id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.first = t_month.first and t_month.era_id=t_era.id and t_era.emperor_id=a.id
	--emperor hasEnd era		
		select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#era'+cast(t_era.id as nvarchar)+'>' from
		(select t_emperor.id , min(first) as first, max(last) as last from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id) a, t_month, t_era where a.last = t_month.last and t_month.era_id=t_era.id and t_era.emperor_id=a.id
	--emperor inside era (inverse of emperor)
	select '<http://authority.dlia.edu.tw/time#emperor'+t_era.emperor_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#era'+cast(t_era.id as nvarchar)+'>' from t_era

	--emperor before emperor
	select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#emperor'+b.id+'>' from (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) a, (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id  from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) b where a.first-1+1=b.last and a.dynasty_id=b.dynasty_id
	--emperor after emperor
		select '<http://authority.dlia.edu.tw/time#emperor'+a.id+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#emperor'+b.id+'>' from (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) a, (select t_emperor.id , min(first) as first, max(last) as last, dynasty_id  from t_year, t_emperor, t_era where t_year.era_id=t_era.id and t_era.emperor_id=t_emperor.id group by t_emperor.id, dynasty_id) b where a.last+1=b.first and a.dynasty_id=b.dynasty_id	

--dynasty
	--dynasty label
	select '<http://authority.dlia.edu.tw/time#dyansty'+cast(dynasty_id as nvarchar)+'>', '<http://www.w3.org/2000/01/rdf-schema#label>', '"'+name+'"@'+short_name_eng from t_dynasty_names, c_languages where t_dynasty_names.language_id=c_languages.id
	--dynasty nation
	select '<http://authority.dlia.edu.tw/time#dyansty'+cast(id as nvarchar)+'>', '<http://dh.aks.ac.kr/ontologies/dhtime#nation>', '"'+type+'"^^xsd:string' from t_dynasty
	
	--dynasty hasbeginning day
		--create view t_rel as select distinct t_month.id as month_id,t_year.id as year_id, emperor_id, dynasty_id, t_month.first, t_month.last from t_year,t_era,t_emperor,t_month where t_year.era_id=t_era.id and t_emperor.id=t_era.emperor_id and t_month.year_id=t_year.id
	 select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#day'+cast(min(first) as nvarchar)+'>' from t_rel group by dynasty_id
	--dynasty hasEnd day
	select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#day'+cast(max(last) as nvarchar)+'>' from t_rel group by dynasty_id
	--dynasty inside day
	select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#day'+cast(t_day.id as nvarchar)+'>' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_day where t_day.id<=a.last and t_day.id>=a.first 
	--dynasty days
	select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>', '<http://www.w3.org/2006/time#days>', '"'+cast(a.last-a.first+1 as nvarchar)+'"^^xsd:nonNegativeInteger' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a
	
	--dynasty hasBeginning month
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#month'+cast(b.month_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
	--dynasty hasEnd month
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#month'+cast(b.month_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
	--dyansty insdie month
	select '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#month'+cast(month_id as nvarchar)+'>' from t_rel 

	--dynasty hasBeginning year
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#year'+cast(b.year_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
	--dynasty hasEnd year
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#year'+cast(b.year_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
	--dyansty inside year
	select distinct '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#year'+cast(year_id as varchar)+'>' from t_rel
	
	--dynasty hasBeginning era
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#era'+cast(b.era_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
	--dynasty hasEnd era
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#era'+cast(b.era_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
	--dyansty inside era
	select distinct '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#era'+cast(era_id as varchar)+'>' from t_rel

	--dynasty hasBeginning emperor
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasBeginning>', '<http://authority.dlia.edu.tw/time#era'+cast(b.emperor_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.first=b.first and a.dynasty_id=b.dynasty_id
	--dynasty hasEnd emperor
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>' , '<http://www.w3.org/2006/time#hasEnd>', '<http://authority.dlia.edu.tw/time#era'+cast(b.emperor_id as nvarchar)+'>' from 
	 (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a,	t_rel b where a.last=b.last and a.dynasty_id=b.dynasty_id
	--dyansty inside emperor
	select distinct '<http://authority.dlia.edu.tw/time#dynasty'+dynasty_id+'>' , '<http://www.w3.org/2006/time#inside>', '<http://authority.dlia.edu.tw/time#era'+cast(emperor_id as varchar)+'>' from t_rel

	--dynasty before dynasty
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>', '<http://www.w3.org/2006/time#before>', '<http://authority.dlia.edu.tw/time#dyansty'+b.dynasty_id+'>' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) b where a.first-1=b.last 

	--dynasty after dynasty
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.dynasty_id+'>', '<http://www.w3.org/2006/time#after>', '<http://authority.dlia.edu.tw/time#dyansty'+b.dynasty_id+'>' from (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) a, (select dynasty_id, min(first) as first, max(last) as last from t_rel group by dynasty_id) b where a.last+1=b.first 

	--따로 노는 왕조들;;;
	--select * from t_dynasty_names where dynasty_id in (0,1,2,3,4,5,6,7,8,9,20,21,23,24,25,26,27,30,33,38,77,80,84,94,95,97,98,99,100,101,102,107,120)
	
--class정의
	--day class Day
	select '<http://authority.dlia.edu.tw/time#day'+id+'>', '<http://www.w3.org/2002/07/owl#class>', '<http://authority.dlia.edu.tw/time#Day>' from t_day
	--month class Month
	select '<http://authority.dlia.edu.tw/time#mon'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Month>' from t_month
	--year class Year
	select '<http://authority.dlia.edu.tw/time#year'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Year>' from t_year
	--emperor class Emperor
	select '<http://authority.dlia.edu.tw/time#emperor'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Emperor>' from t_emperor
	--era class Era
	select '<http://authority.dlia.edu.tw/time#era'+id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#Era>' from t_era
	--dynasty class Dynasty
	select '<http://authority.dlia.edu.tw/time#dynasty'+a.id+'>', '<http://www.w3.org/2002/07/owl#Class>', '<http://authority.dlia.edu.tw/time#month>' from t_dynasty