인문정보학2020기말준비

phj
이동: 둘러보기, 검색
  • 데이터베이스란?다수의 응용 시스템들이 사용하기 위해 체계적으로 편성, 저장된 데이터의 집합.
  • DBMS란? 응용프로그램과 데이터의 중재자인 소프트웨어로 모든 응용 프로그램들이 데이터베이스에 접근하여 공동으로 활용할 수 있게끔 관리(정의, 조작, 제어)해 줌.
  • Xml enabled DBMS? XML 지원 데이터베이스는 XML 언어로 쓰여진 데이터를 담을 수 있는 기능을 지원해 주는 DBMS임.
  • 데이터베이스 기술의 활용이 인문학 연구에 어떠한 도움을 줄 수 있는가? 인문지식을 담고 있는 언어를 컴퓨터가 읽을 수 있는 ‘데이터’로 전환하여 더욱 의미있는 활용 가치를 창출해 낼 수 있음.
  • xml table 만들기
create table 테이블이름넣기 (
id nvarchar(20) not null,
xmltxt xml,
primary key(id)
)
  • 다른db에서 복사해서 내 테이블에 적제하기
insert into 새로만든 내 테이블
select * from 디비.dbo.테이블이름

테이블만들지 않고 그대로 복사할 경우는 아래
select * into xmltable3 from Class2020.dbo.xmltable3
  • 테이블을 만들어서 자료복사해서 넣기
create table 남한산성_xml(
xid nvarchar(8) not null,
xmltxt xml null,
primary key(xid)
)

create table 남한산성_geo(
gid nvarchar(40) not null,
gtype nvarchar(16) null,
address nvarchar(80) null,
longtitude nvarchar(8) null,
latitude nvarchar(8) null,
primary key(gid)
)

insert into 남한산성_xml
 select * from common.dbo.남한산성_xml

insert into 남한산성_geo
 select * from common.dbo.남한산성_geo

create view 남한산성_meta as
select 
xid,
	xmltxt.value('(/항목/항목명/한글항목명)[1]', 'nvarchar(80)') as title,
	xmltxt.value('(/항목/항목명/한자항목명)[1]', 'nvarchar(80)') as hanja, 
	xmltxt.value('(/항목/메타데이터/대표분야)[1]', 'nvarchar(20)') as category, 
	xmltxt.value('(/항목/메타데이터/대표유형)[1]', 'nvarchar(20)') as gtype, 
	xmltxt.value('(/항목/메타데이터/표준시대)[1]', 'nvarchar(20)') as era, 
	xmltxt.value('(/항목/메타데이터/위치/공간/@식별자)[1]', 'nvarchar(40)') as gid
from 남한산성_xml 


select 남한산성_meta.xid, 남한산성_meta.title, 남한산성_meta.gid, 
남한산성_meta.gtype, 남한산성_geo.longtitude, 남한산성_geo.latitude 
from 남한산성_meta, 남한산성_geo where 남한산성_meta.gid = 남한산성_geo.gid
  • 항목명/주소/경도/위도/항목/ xmltxt 뷰만들기
create view jeju_meta as
select id, 
  xmltxt.value('(//항목명)[1]','nvarchar(40)') as 항목명,
  xmltxt.value('(//메타데이터/주소)[1]','nvarchar(40)') as 주소,
  xmltxt.value('(//메타데이터/위도/경도)[1]','float') as 경도,
  xmltxt.value('(//메타데이터/위도/위도)[1]','float') as 위도,
  xmltxt.query('/항목') as xml
 from jeju_xml
  • xml data 넣기
insert into xmltable3 (xmltxt) values
(N' xml 원본넣기 ')
  • 트리거
create trigger xmltable3getid on xmltable3 instead of insert
as /*as 아래와같이 동작하라*/
	insert into xmltable3(id, xmltxt)
	select
		that.xmltxt.value('(//./@id)[1]', 'nvarchar(20)') as id,
		that.xmltxt as xmltxt	/* this, that 모두 상관없음 지금 입력되고 있는 그 테이블*/
	from inserted that
  • 업데이트
update xmltable3
set xmltxt=N'업데이트내용'
where  id='작가-강학태' /*바꾸고 싶은 곳 지정해주기*/
  • xml에서 필요한 자료 추출 및 view 만들기
/* 종합: XML 문서로부터 데이터 활용에 필요한 메타데이터 추출 */ 

select id,
	xmltxt.value('(//범주/프로젝트)[1]', 'nvarchar(40)') as projcet,
	xmltxt.value('(//범주/클래스)[1]', 'nvarchar(40)') as class,
	xmltxt.value('(//표제/이름[./@표기="국문"])[1]', 'nvarchar(40)') as hangeul,
	xmltxt.value('(//표제/이름[./@표기="한자"])[1]', 'nvarchar(40)') as hanja,
	xmltxt.value('(//표제/이름[./@표기="영문"])[1]', 'nvarchar(80)') as english,
	xmltxt.value('(//속성/@틀)[1]', 'nvarchar(20)') as template,
	xmltxt.value('(//속성/정의)[1]', 'nvarchar(256)') as definition
from xmltable3

/* 추출한 메타데이터를 가상 테이블(뷰, view)에 저장 */ 

create view xmlmeta as
select id,
	xmltxt.value('(//범주/프로젝트)[1]', 'nvarchar(40)') as projcet,
	xmltxt.value('(//범주/클래스)[1]', 'nvarchar(40)') as class,
	xmltxt.value('(//표제/이름[./@표기="국문"])[1]', 'nvarchar(40)') as hangeul,
	xmltxt.value('(//표제/이름[./@표기="한자"])[1]', 'nvarchar(40)') as hanja,
	xmltxt.value('(//표제/이름[./@표기="영문"])[1]', 'nvarchar(80)') as english,
	xmltxt.value('(//속성/@틀)[1]', 'nvarchar(20)') as template,
	xmltxt.value('(//속성/정의)[1]', 'nvarchar(256)') as definition
from xmltable3

select * from xmlmeta
  • join을 써서 보여주고싶은 데이터 보여주기
/*클라스가 사건인 데이타를 뽑아내고싶다*/
select * from xmlmeta
where class='사건'

/*클라스가 사건인 데이타를 뽑아내어 xmltable3도 같이 보여줘라, 다보고싶으면 where 빼고*/
select xmlmeta.*,xmltxt.query('//속성'), xmltxt.query('//개설') from xmlmeta
join xmltable3 on xmlmeta.id=xmltable3.id
where class='사건'
  • join을 써서 뷰를 만들기
CREATE VIEW jeju_meta_xml AS
SELECT jeju_meta.id, 항목명, 경도, 위도, xmltxt
FROM jeju_meta
JOIN jeju_xml ON jeju_meta.id = jeju_xml.id


  • 인덱스(CROSS APPLY)를 만들어서 VIEW 만들기
create view xmltable3_인명 as
	select id, node.value('.', 'nchar(80)' ) as 인명, node.value('./@id', 'nchar(80)' ) as 인명_id
	from xmltable3 CROSS APPLY xmltxt.nodes('//인명') as R(node) 

/*인덱스를 만들어서 xmltxt에 query값으로 넣기*/
insert into jeju_xml(xmltxt)
	select node.query('.') as 항목
	from jeju_bulk CROSS APPLY xmltxt.nodes('//항목') as R(node)
  • 빈도 찾아보기
select 용어, 용어_id, count(*) as 빈도
 from xmltable3_용어
 group by 용어, 용어_id
 order by 용어, 용어_id
  • 네트워크그래프 기본 포멧
/*항상 기본 포멧으로 쓰기,shell그래프노드를 표현가능, 샌드박스의 파이선 프로그램 쓰기*/
CREATE TABLE shellData (
	id nvarchar(40) NOT NULL,
	class nvarchar(40) NULL,
	groupName nvarchar(40) NULL,
	partName nvarchar(40) NULL,
	label nvarchar(80) NULL,
	hangeul nvarchar(80) NULL,
	hanja nvarchar(80) NULL,
	english nvarchar(160) NULL,
	infoUrl nvarchar(256) NULL,
	iconUrl nvarchar(256) NULL,
    PRIMARY KEY(id)
)
CREATE TABLE shellLinks (
	source nvarchar(40) NOT NULL,
	target nvarchar(40) NOT NULL,
	relation nvarchar(40) NOT NULL,
	attribute nvarchar(80) NULL,
	PRIMARY KEY(source, target, relation)
)
/*자료넣기*/
insert into friendsData (id, class, groupName, label )
values
('김현', 'Actor', '교수', '김현'),
('박현정', 'Actor', '학생', '박현정'),/*아이디가 박현정인 노드는 클래스가 액터이고 그룹네임이 학생이고 라벨이 박현정이다*/
('정송이', 'Actor', '학생', '정송이'),
('강훈혁', 'Actor', '학생', '강훈혁'),
('김수현', 'Actor', '학생', '김수현'),
('이정민', 'Actor', '학생', '이정민'),
('최원재', 'Actor', '학생', '최원재'),
('조원희', 'Actor', '교수', '조원희'),
('DH201', 'Event', '수업', '수업:인문정보 데이터베이스'),
('한중연', 'Actor', '기관', '한국학중앙연구원'),
('한국학대학원', 'Actor', '기관', '한국학대학원')
('디지털인문학연구소', 'Actor', '기관', '디지털인문학연구소')

insert into friendsLinks(source, target, relation)
values
('김현', '한국학대학원', '~의 교수이다'),
('조원희', '한국학대학원', '~의 교수이다'),
('박현정', '한국학대학원', '~의 학생이다'),
('정송이', '한국학대학원', '~의 학생이다'),
('강훈혁', '한국학대학원', '~의 학생이다'),
('김수현', '한국학대학원', '~의 학생이다'),
('이정민', '한국학대학원', '~의 학생이다'),
('최원재', '한국학대학원', '~의 학생이다'),
('한중연', '한국학대학원', '부속기구 ~가 있다'),
('한중연', '디지털인문학연구소', '부속기구 ~가 있다'),
('한국학대학원', 'DH201', '교과목 ~를 개설하다'),
('김현', 'DH201', '~를 강의하다'),
('조원희','DH201', '~를 수강하다'),
('박현정', 'DH201', '~를 수강하다'),
('정송이', 'DH201', '~를 수강하다'),
('강훈혁', 'DH201', '~를 수강하다'),
('김수현', 'DH201', '~를 수강하다'),
('이정민', 'DH201', '~를 수강하다'),
('최원재', 'DH201', '~를 수강하다'),
('조원희',  'DH201', '~를 수강하다'),
('김현',  '디지털인문학연구소', '~의 소장이다'),
('박현정', '디지털인문학연구소', '~의 연구원이다'),
('정송이', '디지털인문학연구소', '~의 연구원이다')

/*관행적으로 첫글자는 대문자로 쓰기, 파이선이 인식하려면 프로젝트명+Data(노드)와 프로젝트명+Link(링크)는 절대 바꾸면 안됨*/
select * from friendsData
select * from friendsLinks

/*네트워크 그래프 출력*/
http://dh.aks.ac.kr/~sandbox/cgi-bin/Story01.py?db='자기_데이터베이스'&project=friends&key=DH201
  • 테이블 만들기, 트리거, 뷰, 조인, 조인해서 인덱스, select문 만들기 등등
  1. 5개 작품의 한글제목, 작가이름, 소장기관을 보이시오.
  2. 작가는 각각 몇 점의 그림을 그렸나요? (※ count() 함수 사용)
  3. 5개 작품의 작품설명(‘설명’) 속에 나오는 모든 ‘지명’을 작품별로 보이시오.
  4. 5개 작품의 xml 문서에 포함된 모든 ‘인명’을 작품별로 보이시오.
  5. 5개 작품의 id, 한글제목, 한자제목, 작가이름, 작품설명(‘설명’)을 보이시오. (※ join 문 사용)
use s_phj

select * from paintings_xml

create table paintings_xml(
	id nvarchar(10), 
	xmltxt xml,
	primary key(id)
)

paintings_getid

create trigger paintings_getid on paintings_xml instead of insert
as 
	insert into paintings_xml(id, xmltxt)
	select
		that.xmltxt.value('(//./@id)[1]', 'nvarchar(20)') as id,
		that.xmltxt as xmltxt	
	from inserted that

insert into paintings_xml(xmltxt) values
(N'<개체 id="w05">
	<표제>
		<이름 표기="국문">김대성과 석굴암</이름>
		<이름 표기="한자">石窟庵 創建圖</이름>
	</표제>
	<범주>
		<프로젝트>민족기록화</프로젝트>
		<클래스>작품</클래스>
		<기여 역할="작성">박성희</기여>
	</범주>
	<속성 틀="작품정보">
		<작가>이종상</작가>
		<제작>1979년</제작>	
		<규격>290.9x197cm(300호)</규격>
		<소장처>한국학중앙연구원</소장처>
	</속성>
	<설명>
        <문단><시대>신라</시대> <인명 id="인물-김대성">김대성(金大城 700~774)</인명>의 설화를 묘사한 민족기록화.</문단> 
		<문단><서명 id="문헌-삼국유사">『삼국유사』</서명> 권5 효선(孝善)편에 "대성이 현생의 부모를 위해 <지명 id="공간-불국사">불국사</지명>를 창건하고 전세의 부모를 위해 <지명 id="공간-석굴암">석불사(石佛寺:석굴암)</지명>를 창건하여 <인명 id="인물-신림">신림(神琳)</인명> <인명 id="인물-표훈">표훈(表訓)</인명> 두 스님을 청해 각각 머물게 했다"고 적혀 있다.</문단>
	</설명>
	<시각>
		<삽도 url="w05m.jpg">김대성과 석굴암</삽도>
		<온라인참조 url="http://digerati.aks.ac.kr/Gallery/Exhibition2016-1/index.htm" >민족기록화 가상전시관</온라인참조>
	</시각>
	<연관>
		<관련항목 rel="~이 그렸다" id="작가-이종상" />
		<관련항목 rel="~을 담았다" id="인물-김대성"/>
		<관련항목 rel="~을 담았다" id="인물-신림"/>
		<관련항목 rel="~을 담았다" id="인물-표훈"/>
		<관련항목 rel="~을 담았다" id="공간-석굴암"/>
		<관련항목 rel="~에 있다" id="기관-한국학중앙연구원"/>
		<관련항목 rel="~와 관련있다" id="공간-불국사"/>
		<관련항목 rel="~와 관련있다" id="문헌-삼국유사"/>
	</연관>
</개체>')

create view paintings_tbl as
select 
 id,
	xmltxt.value('(/개체/표제/이름)[1]', 'nvarchar(80)') as 한글제목,
	xmltxt.value('(/개체/표제/이름)[2]', 'nvarchar(80)') as 한자제목, 
	xmltxt.value('(/개체/속성/작가)[1]', 'nvarchar(20)') as 작가이름, 
	xmltxt.value('(/개체/속성/제작)[1]', 'nvarchar(20)') as 제작년도, 
	xmltxt.value('(/개체/속성/규격)[1]', 'nvarchar(20)') as 작품규격, 
	xmltxt.value('(/개체/속성/소장처)[1]', 'nvarchar(20)') as 소장기관
from paintings_xml 

select * from paintings_tbl 

select 한글제목, 작가이름, 소장기관 from paintings_tbl

select 작가이름, count(*) as 빈도 from paintings_tbl
group by 작가이름



select paintings_tbl.한글제목 ,paintings_xml.xmltxt.query('//지명') from paintings_xml 
join paintings_tbl on paintings_xml.id=paintings_tbl.id

select paintings_tbl.한글제목 ,paintings_xml.xmltxt.query('//인명') from paintings_xml 
join paintings_tbl on paintings_xml.id=paintings_tbl.id

select paintings_tbl.한글제목, node.value('.', 'nchar(80)' ) as 지명
	from paintings_xml CROSS APPLY xmltxt.nodes('//지명') as R(node) 
	join paintings_tbl on paintings_xml.id=paintings_tbl.id

select paintings_tbl.한글제목, node.value('.', 'nchar(80)' ) as 인명
	from paintings_xml CROSS APPLY xmltxt.nodes('//인명') as R(node) 
	join paintings_tbl on paintings_xml.id=paintings_tbl.id

select paintings_xml.id, paintings_tbl.한글제목, paintings_tbl.작가이름, paintings_xml.xmltxt.value('(//설명)[1]','nvarchar(256)') as 작품설명
from paintings_xml 
join paintings_tbl on paintings_xml.id=paintings_tbl.id
  • 최종복습
use s_phj

select * from jeju_xml
drop table jeju_xml

select * from tutor2019.dbo.jeju_xml

insert into 새로만든 내 테이블
select * from 디비.dbo.테이블이름

create table jeju_xml(
    id nvarchar(10), 
	xmltxt xml
    primary key(id)
)

insert into jeju_xml select * from tutor2019.dbo.jeju_xml

select * into jeju_xml from tutor2019.dbo.jeju_xml

/*3.
   - 뷰 이름: jeju_meta
   - 뷰 컬럼: id
             항목명: nvarchar(40)
	    주소: nvarchar(40)
             경도: float
	     위도: float
	     xmltxt xml
*/

drop view jeju_meta_xml
create view jeju_meta as(
	select id, 
     xmltxt.value('(/항목/항목명)[1]', 'nvarchar(40)') as 항목명,
	 xmltxt.value('(/항목/메타데이터/주소)[1]', 'nvarchar(40)') as 주소, 
	 xmltxt.value('(/항목/메타데이터/위치/경도)[1]', 'float') as 경도, 
	 xmltxt.value('(/항목/메타데이터/위치/위도)[1]', 'float') as 위도, 
	 xmltxt.value('(//항목)[1]','nvarchar(256)') as xml
   from jeju_xml 
)
select id, 
  xmltxt.value('(//항목명)[1]','nvarchar(40)') as 항목명,
  xmltxt.value('(//메타데이터/주소)[1]','nvarchar(40)') as 주소,
  xmltxt.value('(//메타데이터/위도/경도)[1]','float') as 경도,
  xmltxt.value('(//메타데이터/위도/위도)[1]','float') as 위도,
  xmltxt.query('/항목') as xml
 from jeju_xml


 select * from jeju_metaMxml 

 /*4. 앞에서 생성한 뷰 jeju_meta와 테이블 jeju_xml을 연결(join)하여 다음과 같은 구조의 가상 테이블 – 뷰를 만드시오.

	- 뷰 이름: jeju_meta_xml
	- 뷰 칼럼: id
		  항목명: nvarchar(40)
   		  경도: float
		  위도: float
	           xmltxt xml
*/
create view jeju_metaMxml 
as(
 select jeju_xml.id, 
	jeju_meta.항목명,
    jeju_meta.경도,
    jeju_meta.위도
 from jeju_meta
 join jeju_xml on jeju_meta.id=jeju_xml.id
)

CREATE VIEW jeju_meta_xml AS
SELECT jeju_meta.id, 항목명, 경도, 위도, xmltxt
FROM jeju_meta
JOIN jeju_xml ON jeju_meta.id = jeju_xml.id

/*xml 데이터 속에서 모든 ‘지명’ 요소를 추출하여 지명 목록을 만들고자 한다. 이를 위해 작성한 다음의 SQL 문을 완성하고, 그 결과를 가상 테이블(뷰)로 만드시오.

	- 뷰 이름: jeju_place
	- 뷰 컬럼: id, 
		  지명(nvarchar(20))*/
select * from jeju_place
drop view jeju_place
create view jeju_place as
	select id, node.value('.', 'nchar(20)' ) as 지명
	from jeju_xml CROSS APPLY xmltxt.nodes('//지명') as R(node) 

select 지명, count(*) as 빈도 from jeju_place
group by 지명
order by 지명

select * from 직조공예_xml

  - 데이터베이스 이름: AKS##
    - 테이블 이름: 직조공예_xml
    - 테이블 컬럼: id: nvarchar(10),  xmltxt: xml

create table 직조공예_xml (
	id nvarchar(10),
	xmltxt xml)


insert into 직조공예_xml(xmltxt) values
(N'
<항목 ID="무형0014">
<명칭>한산모시짜기(韓山모시짜기)</명칭>
<지정사항>
	<종목>중요무형문화재</종목>
	<지정번호>제14호</지정번호>
	<분야>공예</분야>
	<지정명칭>한산모시짜기</지정명칭>
	<지정일자>1967. 1.16.</지정일자>
	<현보유자>문정옥(文貞玉)</현보유자>
	<전승지역>
		<지역>충청남도 서천군 한산면</지역>
		<위도>36.0870</위도>
		<경도>126.8020</경도>
	</전승지역>
</지정사항>
<설명>
  모시는 기호 지방에서도 짜는 곳이 여러 곳이나, 예로부터 한산 지방의 모시가 이름이 있었다.
  한산 지방의 부녀자들은 대개가 모시를 짤 수 있는 기능을 지녔고 그와 같은 기술 집단 가운데서 문정옥의 솜씨가 두드러진다.
  ......
	<삽도 PID="p140601">한산모시짜기 / 모시 톱기</삽도>
	<삽도 PID="p140602">한산모시짜기 / 모시올 쪼개기</삽도>
	........
</설명>
</항목>
')



select * from 직조공예_xml
drop table 직조공예_xml

create trigger 직조공예_getid on 직조공예_xml instead of insert
as 
	insert into 직조공예_xml(id, xmltxt)
	select
		that.xmltxt.value('(/항목/@ID)[1]', 'nvarchar(20)') as id,
		that.xmltxt as xmltxt	
	from inserted that
	/*   - 뷰 이름: 직조공예_meta
    - 뷰 컬럼: xml_id 
		명칭: nvarchar(80)
		분야: nvarchar(10)
		지정일자: nvarchar(20)
		전승지역: nvarchar(40)	*/

CREATE VIEW 직조공예_meta AS
SELECT id as xml_id, 
	xmltxt.value('(/항목/명칭)[1]', 'nvarchar(80)') AS 명칭,
	xmltxt.value('(/항목/지정사항/분야)[1]', 'nvarchar(10)') AS 분야,
	xmltxt.value('(/항목/지정사항/지정일자)[1]', 'nvarchar(20)') AS 지정일자, 
	xmltxt.value('(/항목/지정사항/전승지역)[1]', 'nvarchar(40)') AS 전승지역
FROM 직조공예_xml	

select * from 직조공예_meta
/*뷰 2: 위치 정보
    - 뷰 이름: 직조공예_gis
    - 뷰 컬럼: xml_id 
		지역명: nvarchar(40)		※ 전승지역/지역
		경도: real
		위도: real
*/
CREATE VIEW 직조공예_gis AS
SELECT id as xml_id, 
    xmltxt.value('(/항목/지정사항/전승지역)[1]', 'nvarchar(40)') AS 지역,
	xmltxt.value('(/항목/지정사항/전승지역/경도)[1]', 'real') AS 경도,
	xmltxt.value('(/항목/지정사항/전승지역/위도)[1]', 'real') AS 위도
FROM 직조공예_xml

/*   ※ 뷰 3: 시각 자료
    - 뷰 이름: 직조공예_photo
    - 뷰 컬럼: xml_id 
		파일: nvarchar(40)
		캡션: nvarchar(100)
*/
CREATE VIEW 직조공예_photo AS
SELECT id as xml_id, 
    xmltxt.value('(/항목/설명/삽도/@PID)[1]', 'nvarchar(40)') AS 파일,
	xmltxt.value('(/항목/설명/삽도)[1]', 'nvarchar(100)') AS 캡션
FROM 직조공예_xml


/*  1) id, 항목명, 전승지역, 경도, 위도
  2) 파일, 캡션, 항목명, 전승지역, 경도, 위도*/

 select * from 직조공예_xml
 select * from 직조공예_meta
 select * from 직조공예_gis
 select * from 직조공예_photo

 select 직조공예_photo.파일, 직조공예_photo.캡션, 직조공예_meta.명칭 as 항목명, 직조공예_meta.전승지역, 직조공예_gis.경도, 직조공예_gis.위도 
 from 직조공예_meta
 join 직조공예_gis on 직조공예_meta.xml_id=직조공예_gis.xml_id
 join 직조공예_photo on 직조공예_meta.xml_id=직조공예_photo.xml_id
원본 주소 "http://dh.aks.ac.kr/~phj/wiki/index.php?title=인문정보학2020기말준비&oldid=757"