IT CookBook, SQL Server 로배우는데이터베이스개론과실습 [ 강의교안이용안내 ] 본강의교안의저작권은한빛아카데미 에있습니다. 이자료를무단으로전제하거나배포할경우저작권법 136 조에의거하여최고 5 년이하의징역또는 5 천만원이하의벌금에처할수있고이를병과 ( 倂科 ) 할수도있습니다.
Chapter2. 관계데이터모델 SQL Server 로배우는데이터베이스개론과실습
1. 관계데이터모델의개념 2. 무결성제약조건 3. 관계대수
관계데이터모델의개념을이해한다. 관계데이터모델의제약조건을알아본다. 관계데이터모델의연산인관계대수의종류와작성법을알아본다.
01. 관계데이터모델의개념 릴레이션 릴레이션스키마와인스턴스 릴레이션의특징 관계데이터모델
1.1 릴레이션 릴레이션 (relation) : 행과열로구성된테이블 표 2-1 relation 과관련된한글용어 용어한글용어비고 relation 릴레이션, 테이블 관계 라고하지않음 relational data model relational database relational algebra relationship 관계데이터모델관계데이터베이스관계대수관계
1.1 릴레이션 릴레이션이란? 도서 1, 축구의역사, 굿스포츠, 7000 도서 2, 축구아는여자, 나무수, 13000 도서 3, 축구의이해, 대한미디어, 22000 도서 4, 골프바이블, 대한미디어, 35000 도서 5, 피겨교본, 굿스포츠, 8000 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 그림 2-1 데이터와테이블 ( 릴레이션 ) 도서번호 = {1,2,3,4,5} 도서이름 = { 축구의역사, 축구아는여자, 축구의이해, 골프바이블, 피겨교본 } 출판사 = { 굿스포츠, 나무수, 대한미디어 } 가격 = {7000, 13000, 22000, 35000, 8000} 첫번째행 (1, 축구의역사, 굿스포츠, 7000) 의경우네개의집합에서각각원소한개씩선택하여만들어진 것으로이원소들이관계 (relationship) 를맺고있다.
1.1 릴레이션 관계 (relationship) 릴레이션내에서생성되는관계 : 릴레이션내데이터들의관계 릴레이션간에생성되는관계 : 릴레이션간의관계 도서 ( 도서번호, 도서이름, 출판사, 가격 ) 주문 ( 도서번호, 고객번호, 판매가격, 주문일자 ) 고객 ( 고객번호, 이름, 주민번호, 주소, 핸드폰 ) 그림 2-2 릴레이션간의관계
1.2 릴레이션스키마와인스턴스 투플 (tuple), 행 (row) 이라고도부름 ( 카디널리티 =5) 도서 속성 ( 애트리뷰트 ), 열 (column) 이라고도부름 ( 차수 =4) 도서번호도서이름출판사가격 1 축구의역사굿스포츠 7000 2 축구아는여자나무수 13000 3 축구의이해대한미디어 22000 4 골프바이블대한미디어 35000 5 피겨교본굿스포츠 8000 스키마 ( 내포 ) Schema 인스턴스 ( 외연 ) Instance 그림 2-3 도서릴레이션
1.2.1 릴레이션스키마 스키마의요소 속성 (attribute) : 릴레이션스키마의열 도메인 (domain) : 속성이가질수있는값의집합 차수 (degree) : 속성의개수 스키마의표현 릴레이션이름 ( 속성 1 : 도메인 1, 속성 2 : 도메인 2, 속성 3 : 도메인 3 ) EX) 도서 ( 도서번호, 도서이름, 출판사, 가격 )
1.2.2 릴레이션인스턴스 인스턴스요소 투플 (tuple) : 릴레이션의행 카디날리티 (cardinality) : 투플의수 투플이가지는속성의개수는릴레이션스키마의차수와동일하고, 릴레이션내의모든투플들은서로중복되지않아야한다. 표 2-2 릴레이션구조와관련된용어 릴레이션용어같은의미로통용되는용어파일시스템용어 릴레이션 (relation) 테이블 (table) 파일 (file) 스키마 (schema) 내포 (intension) 헤더 (header) 인스턴스 (instance) 외연 (extension) 데이터 (data) 투플 (tuple) 행 (row) 레코드 (record) 속성 (attribute) 열 (column) 필드 (field)
1.3 릴레이션의특징 속성은단일값을가진다각속성의값은도메인에정의된값만을가지며그값은모두단일값이여야한다. 속성은서로다른이름을가진다속성은한릴레이션에서서로다른이름을가져야만한다. 한속성의값은모두같은도메인값을가진다한속성에속한열은모두그속성에서정의한도메인값만가질수있다. 속성의순서는상관없다속성의순서가달라도릴레이션스키마는같다. 예를들어릴레이션스키마에서 ( 이름, 주소 ) 순으로속성을표시하거나 ( 주소, 이름 ) 순으로표시하여도상관없다. 릴레이션내의중복된투플은허용하지않는다하나의릴레이션인스턴스내에서는서로중복된값을가질수없다. 즉모든투플은서로값이달라야한다. 투플의순서는상관없다투플의순서가달라도같은릴레이션이다. 관계데이터모델의투플은실제적인값을가지고있으며이값은시간이지남에따라데이터의삭제, 수정, 삽입에따라순서가바뀔수있다.
1.3 릴레이션의특징 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본굿스포츠 8000 5 피겨교본굿스포츠 8000 동일한튜플이중복되면안됨 6 피겨교본, 피겨기초굿스포츠 8000 속성의값은단일값이어야함 그림 2-4 릴레이션의특징에위배된경우
1.4 관계데이터모델 관계데이터모델은데이터를 2 차원테이블형태인릴레이션으로표현하며, 릴레이션에대한제약조건 (constraints) 과관계연산을위한관계대수 (relational algebra) 를정의한다. 관계데이터모델 릴레이션제약조건관계대수 컴퓨터시스템에구현 관계데이터베이스시스템 릴레이션 (SQL 로생성및관리 ) 제약조건 (SQL 로제약선언 ) 관계대수 (SQL 로연산 ) 그림 2-5 관계데이터베이스시스템
02. 무결성제약조건 키 무결성제약조건 무결성제약조건의수행
2.1 키 특정투플을식별할때사용하는속성혹은속성의집합이다. 릴레이션은중복된투플을허용하지않기때문에각각의투플에포함된속성들중어느하나 ( 혹은하나이상 ) 는값이달라야한다. 즉키가되는속성 ( 혹은속성의집합 ) 은반드시값이달라서투플들을서로구별할수있어야한다. 키는릴레이션간의관계를맺는데도사용된다. 그림 2-6 Customer 테이블과키
2.1 키 고객 고객번호 이름 주민번호 주소 핸드폰 1 박지성 810101-1111111 영국맨체스타 000-5000-0001 2 김연아 900101-2222222 대한민국서울 000-6000-0001 3 장미란 830101-2333333 대한민국강원도 000-7000-0001 4 추신수 820101-1444444 미국클리블랜드 000-8000-0001 도서 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 주문 고객번호 도서번호 판매가격 주문일자 1 1 7000 2013-07-01 1 2 13000 2013-07-03 2 5 8000 2013-07-03 3 2 13000 2013-07-04 4 4 35000 2013-07-05 1 3 22000 2013-07-07 4 3 22000 2013-07-07 그림 2-7 마당서점데이터베이스
2.1.1 슈퍼키 투플을유일하게식별할수있는하나의속성혹은속성의집합을말한다. 투플을유 일하게식별할수있는값이면모두슈퍼키가될수있다. 고객번호 : 고객별로유일한값이부여되어있기때문에투플을식별할수있다. 이름 : 동명이인이있을경우투플을유일하게식별할수없다. 주민번호 : 개인별로유일한값이부여되어있기때문에투플을식별할수있다. 주소 : 가족끼리는같은정보를사용하므로투플을식별할수없다. 핸드폰 : 한사람이여러개의핸드폰을사용할수있고반대로핸드폰을사용하지않는사람이있을수있기때문에투플을식별할수없다. 고객릴레이션의경우고객번호와주민번호를포함한모든속성의집합이슈퍼키가된다. EX) ( 주민번호 ), ( 주민번호, 이름 ), ( 주민번호, 이름, 주소 ), ( 주민번호, 이름, 핸드폰 ), ( 고객번호 ), ( 고객번호, 이름, 주소 ), ( 고객번호, 이름, 주민번호, 주소, 핸드폰 ) 등
2.1.2 후보키 투플을유일하게식별할수있는속성의최소집합이다. 고객번호 : 한명의고객이여러권의도서를구입할수있으므로후보키가될수없다. 고객번호가 1인박지성고객의경우세번의주문기록이있으므로투플을유일하게식별할수없다. 도서번호 : 도서번호가 2인 축구아는여자 의경우두번의주문기록이있으므로투플을유일하게식별할수있다. 주문릴레이션의후보키는 2 개의속성을합한 ( 고객번호, 도서번호 ) 가된다. 참고로 이렇게 2 개이상의속성으로이루어진키를복합키 (composite key) 라고한다.
2.1.3 기본키 여러후보키중하나를선정하여대표로삼는키를말한다. 후보키가하나뿐이라면 그후보키를기본키로사용하면되고여러개라면릴레이션의특성을반영하여하 나를선택하면된다. 기본키선정시고려사항 릴레이션내투플을식별할수있는고유한값을가져야한다. NULL 값은허용하지않는다. 키값의변동이일어나지않아야한다. 최대한적은수의속성을가진것이라야한다. 향후키를사용하는데있어서문제발생소지가없어야한다. 릴레이션스키마를표현할때기본키는아래와같이밑줄을그어표시한다. 릴레이션이름 ( 속성 1, 속성 2,. 속성 N) EX) 고객 ( 고객번호, 이름, 주민번호, 주소, 핸드폰 ) 도서 ( 도서번호, 도서이름, 출판사, 가격 )
2.1.4 대리키 기본키가보안을요하거나, 여러개의속성으로구성되어복잡하거나, 마땅한기본키가없을때는일련번호같은가상의속성을만들어기본키로삼는경우가있다. 이러한키를대리키 (surrogate key) 혹은인조키 (artificial key) 라고한다. 대리키는 DBMS나관련소프트웨어에서임의로생성하는값으로사용자가직관적으로그값의의미를알수없다. 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 1 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 3 2 13000 2013-07-04 5 4 4 35000 2013-07-05 6 1 3 22000 2013-07-07 7 4 3 22000 2013-07-07 그림 2-8 대리키를사용하도록변경된주문릴레이션
2.1.5 대체키 대체키 (alternate key) 는기본키로선정되지않은후보키를말한다. 고객릴레이션의경우고객번호와주민번호중고객번호를기본키로정하면주민 번호가대체키가된다.
2.1.6 외래키 다른릴레이션의기본키를참조하는속성을말한다. 다른릴레이션의기본키를참 조하여관계데이터모델의특징인릴레이션간의관계 (relationship) 를표현한다. 외래키의특징 관계데이터모델의릴레이션간의관계를표현한다. 다른릴레이션의기본키를참조하는속성이다. 참조하고 ( 외래키 ) 참조되는 ( 기본키 ) 양쪽릴레이션의도메인은서로같아야한다. 참조되는 ( 기본키 ) 값이변경되면참조하는 ( 외래키 ) 값도변경된다. NULL 값과중복값등이허용된다. 자기자신의기본키를참조하는외래키도가능하다. 외래키가기본키의일부가될수있다.
2.1.6 외래키 고객 고객번호 이름 주민번호 주소 핸드폰 1 박지성 810101-1111111 영국맨체스타 000-5000-0001 2 김연아 900101-2222222 대한민국서울 000-6000-0001 3 장미란 830101-2333333 대한민국강원도 000-7000-0001 4 추신수 820101-1444444 미국클리블랜드 000-8000-0001 기본키 도서 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 기본키 참조 주문 외래키 참조 주문번호 고객번호 도서번호 판매가격 주문일자 1 1 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 3 2 13000 2013-07-04 5 4 4 35000 2013-07-05 6 1 3 22000 2013-07-07 7 4 3 22000 2013-07-07 기본키 그림 2-9 릴레이션간의참조관계
2.1.6 외래키 외래키사용시참조하는릴레이션과참조되는릴레이션이꼭다른릴레이션일필 요는없다. 즉자기자신의기본키를참조할수도있다. 참조 기본키 외래키 선수번호 이름 주소 멘토번호 1 박지성 영국맨체스타 NULL 2 김연아 대한민국서울 3 3 장미란 대한민국강원도 4 4 추신수 미국클리블랜드 NULL 그림 2-10 멘토릴레이션
2.1 키 내용요약 기본키 대체키 슈퍼키 후보키중선정된키 기본키로선정되지않은후보키 릴레이션내투플을식별할수있는속성의집합 후보키 ( 투플을식별할수있는속성의최소집합 ) 그림 2-11 키의포함관계
2.2 무결성제약조건 데이터무결성 (integrity, 無缺性 ) 은데이터베이스에저장된데이터의일관성과정확 성을지키는것을말한다. 도메인무결성제약조건 도메인제약 (domain constraint) 이라고도하며, 릴레이션내의투플들이각속성의도메인에지정된값만을가져야한다는조건이다. SQL 문에서데이터형식 (type), 널 (null/not null), 기본값 (default), 체크 (check) 등을사용하여지정할수있다. 개체무결성제약조건 기본키제약 (primary key constraint) 이라고도한다. 릴레이션은기본키를지정하고그에따른무결성원칙즉, 기 본키는 NULL 값을가져서는안되며릴레이션내에오직하나의값만존재해야한다는조건이다. 참조무결성제약조건 외래키제약 (foreign key constraint) 이라고도하며, 릴레이션간의참조관계를선언하는제약조건이다. 자식릴레이션의외래키는부모릴레이션의기본키와도메인이동일해야하며, 자식릴레이션의값이변경될때부모릴레이션의제약을받는다는것이다.
2.2 무결성제약조건 표 2-3 제약조건의정리 구분 도메인 도메인무결성제약조건개체무결성제약조건참조무결성제약조건 키 제약대상속성투플속성과투플 같은용어 도메인제약 (Domain Constraint) 기본키제약 (Primary Key Constraint) 외래키제약 (Foreign Key Constraint) 해당되는키 - 기본키외래키 NULL 값허용여부허용불가허용 릴레이션내 제약조건의개수 속성의개수와동일 1 개 0~ 여러개 투플삽입 / 수정시제약사항 기타 투플삽입, 수정시제약 사항우선확인 투플삽입 / 수정시제약 사항우선확인 우선확인 부모릴레이션의투플수정 / 삭 제시제약사항우선확인
2.3.1 개체무결성제약조건 삽입 : 기본키값이같으면삽입이금지된다. 수정 : 기본키값이같거나 NULL 로도수정이금지된다. 삭제 : 특별한확인이필요하지않으며즉시수행한다. 학번 이름 학과코드 501 박지성 1001 401 김연아 2001 402 장미란 2001 502 추신수 1001 그림 2-12 학생릴레이션 (5-1, 남슬찬, 1001) (NULL, 남슬찬, 1001) 삽입거부 학번 이름 학과코드 501 박지성 1001 401 김연아 2001 402 장미란 2001 502 추신수 1001 삽입거부 학번 이름 학과코드 501 박지성 1001 401 김연아 2001 402 장미란 2001 502 추신수 1001 그림 2-13 개체무결성제약조건의수행예 ( 기본키충돌및 NULL 값삽입 )
2.3.2 참조무결성제약조건 삽입 학과 ( 부모릴레이션 ) : 투플삽입한후수행하면정상적으로진행된다. 학생 ( 자식릴레이션 ) : 참조받는테이블에외래키값이없으므로삽입이금지된다. 학생 ( 자식릴레이션 ) 학과 ( 부모릴레이션 ) 학번 이름 학과코드 501 박지성 1001 401 김연아 2001 402 장미란 2001 502 추신수 1001 학과코드 학과명 1001 컴퓨터학과 2001 체육학과 참조 그림 2-14 학생관리데이터베이스
2.3.2 참조무결성제약조건 삭제 학과 ( 부모릴레이션 ) : 참조하는테이블을같이삭제할수있기때문에금지하거나다른추가작업이필요하다. 학생 ( 자식릴레이션 ) : 바로삭제가능하다. 부모릴레이션에서투플을삭제할경우참조무결성조건을수행하기위한고려사항 즉시작업을중지 자식릴레이션의관련투플을삭제 초기에설정된다른어떤값으로변경 NULL 값으로설정 수정 삭제와삽입명령이연속해서수행된다. 부모릴레이션의수정이일어날경우삭제옵션에따라처리된후문제가없으면다시삽입제약조건에따라처리된다.
2.3.2 참조무결성제약조건 표 2-4 참조무결성제약조건의옵션 ( 부모릴레이션에서투플을삭제할경우 ) 명령어의미예 RESTRICTED CASCADE DEFAULT NULL 자식릴레이션에서참조하고있을경우부모릴레이션의삭제작업을거부함자식릴레이션의관련투플을같이삭제처리함자식릴레이션의관련투플을미리설정해둔값으로변경함자식릴레이션의관련투플을 NULL 값으로설정함 (NULL 값을허가한경우 ) 학과릴레이션의투플삭제거부학생릴레이션의관련투플을삭제학생릴레이션의학과가다른학과로자동배정학과릴레이션의학과가 NULL 값으로변경
2.3.2 참조무결성제약조건 학생 학번이름학과코드 501 박지성 1001 401 김연아 2001 402 장미란 2001 502 추신수 1001 2 참조확인 학과 (2001, 체육학과 ) 학과코드 학과명 1001 컴퓨터학과 2001 체육학과 1 삭제요청 3 옵션선택 1 RESTRICTED : 요청한삭제작업중지 ( 에러처리 ) 2 CASCADE : 학생릴레이션의해당투플을같이연쇄적으로삭제 (CASCADE) 3 기본값으로변경 ( 미리설정한값, DEFAULT) 4 NULL 값으로설정 그림 2-15 참조무결성제약조건에서부모릴레이션의투플을삭제할경우
03. 관계대수 관계대수 셀렉션과프로젝션 집합연산 조인 디비전 관계대수예제
3.1 관계대수 관계대수 (relational algebra, 關係代數 ) 란 릴레이션에서원하는결과를얻기위해수학의대수와같은연산을이용하여질의하는방법을 기술하는언어다. 관계대수와관계해석 관계대수 : 어떤데이터를어떻게찾는지에대한처리절차를명시하는절차적인언어이며, DBMS 내부의처리언어로사용된다. 관계해석 : 어떤데이터를찾는지만명시하는선언적인언어로관계대수와함께관계 DBMS의표준언어인 SQL의이론적인기반을제공한다. 관계대수와관계해석은모두관계데이터모델의중요한언어이며실제동일한표현능력을 가지고있다.
3.1.1 관계의수학적의미 릴레이션 (relation) 의수학적개념 예 ) A {2, 4}, B {1, 3, 5} 일때 A B {(2,1), (2,3), (2,5), (4,1), (4,3), (4,5)} 릴레이션 R 은카티전프로덕트의부분집합으로정의 예 ) R1 {(2,1), (4,1)}, R2={(2, 1), (2, 3), (2,5)}, R3={(2, 3), (2, 5), (4, 3), (4, 5)} 원소개수가 n 인집합 S 의부분집합의개수는 2ⁿ 이므로, 카티전프로덕트 A B 의 부분집합의개수는이다. 카티전프로덕트의기초집합 A, B 각각이가질수있는값의범위를도메인 (domain) 이라고한다. 즉집합 A 의도메인은 {2, 4} 이다. 릴레이션역시집합이므로집합에서집합에서가능한연산은합집합 ( ), 교집합 ( ), 카티전프로덕트 ( ) 등이있다. R1 R2 = {(2, 1), (4, 1), (2, 3), (2, 5)} R1 R2 = {(2, 1)}
3.1.1 관계의수학적의미 릴레이션 (relation) 의현실세계적용 예 ) 학번 ={2, 4}, 과목 ={ 데이터베이스, 자료구조, 프로그래밍 } 일때두집합의카티전프로덕트학번 과목은학번원소와과목원소의순서쌍의집합이다. 즉, 학번 과목 ={(2, 데이터베이스 ), (2, 자료구조 ), (2, 프로그래밍 ), (4, 데이터베이스 ), (4, 자료구조 ), (4, 프로그래밍 )} 을말한다. 학번 과목의각원소는학생이과목을수강할수있는모든경우를나열한것이다. 수강 ={(2, 데이터베이스 ), (2, 자료구조 ), (4, 프로그래밍 )} 은카티전프로덕트학번 과목의부분집합으로하나의릴레이션인스턴스다. 수강릴레이션의투플은위에서나열한여섯개원소중하나로, 아래수강테이블을데이터베이스에서는릴레이션 (relation) 이라고부른다. 수강학번과목 2 데이터베이스 2 자료구조 4 프로그래밍 그림 2-16 수강릴레이션
3.1.2 관계대수연산자 표 2-5 관계대수연산자 구분 분류 대상 연산자이름 기호 설명 순수 기본 단항 셀렉션 릴레이션에서조건에만족하는투플을선택 순수 기본 단항 프로젝션 릴레이션의속성을선택 순수 추가 단항 개명 릴레이션이나속성의이름을변경 순수유도이항디비전 부모릴레이션에포함된투플의값을모두갖고있는투플을분자릴레이션에서추출 일반집합 일반집합 일반집합 일반집합 기본 이항 합집합 두릴레이션의합집합 기본 이항 차집합 두릴레이션의차집합 유도 이항 교집합 두릴레이션의교집합 기본 이항 카디전프로덕트 두릴레이션에속한모든투플의집합 순수유도이항조인 세타동등자연 left 세미 right left 외부 right full 두릴레이션간의비교조건에만족하는집합 두릴레이션간의같은값을가진집합 동등조인에서중복속성을제거 자연조인후오른쪽속성을제거 자연조인후왼쪽속성을제거 자연조인후각각왼쪽 (left), 오른쪽 (right), 양쪽 (full) 의모든값을결과로추출 조인이실패 ( 또는값이없을경우 ) 한쪽의값을 NULL 로채움
3.1.3 관계대수식 관계대수식관계대수는릴레이션간연산을통해결과릴레이션을찾는절차를기술한언어로, 이연산을수행하기위한식을관계대수식 (relational algebra expression) 이라고한다. 관계대수식은대상이되는릴레이션과연산자로구성되며, 결과는릴레이션으로반환된다. 반환된릴레이션은릴레이션의모든특징을따른다. 단항연산자 : 연산자 < 조건 > 릴레이션 이항연산자 : 릴레이션 1 연산자 < 조건 > 릴레이션 2 R1 A B C a1 b1 c1 a2 b3 c3 a3 b4 c2 R2 A B C a1 b1 c1 a2 b3 c3 a3 b4 c2 그림 2-17 관계대수식을이해하기위한예제데이터
3.1.3 관계대수식 표 2-6 관계대수식의사용예 주요연산자사용예결과설명 셀렉션 (σ) σ (R1) A B C a1 b1 c1 a2 b3 c3 R1 에서조건에맞는투플을추출한다. A B 프로젝션 (π) π (R2) a1 a2 b1 b3 R2 에서조건에맞는속성만을추출한다. a3 b3 A B C a1 b1 c1 합집합 ( ) R1 R2 R1 과 R2 의합집합을구한다. a2 b3 c3 a3 b4 c2 a3 b3 c1 A B C 차집합 (-) R1 - R2 R1 과 R2 의차집합을구한다. a3 b4 c2 R1.A R1.B R1.C R2.A R2.B R2.C 조인 R1 R2 a1 b1 c1 a1 b1 c1 a1 b1 c1 a3 b3 c1 a2 b3 c3 a2 b3 c3 R1 과 R2 의카티전프로덕트를구하여조건에맞는투플을추출한다.
3.2.1 셀렉션 (selection) 릴레이션의투플을추출하기위한연산이다. 하나의릴레이션을대상으로하는단항연산자며, 찾고자하는투플의조건 (predicate) 을명시하고그조건에만족하는투플을반환한다. 형식 : σ< 조건 > (R) (R 은릴레이션 ) 질의 2-1 마당서점에서판매하는도서중 8,000 원이하인도서를검색하시오. σ 가격 <=8000 ( 도서 ) 도서 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 σ 가격 <=8000 ( 도서 ) 도서번호도서이름출판사가격 1 축구의역사굿스포츠 7000 5 피겨교본굿스포츠 8000 그림 2-18 셀렉션의예
3.2.2 프로젝션 (projection) 릴레이션의속성을추출하기위한연산으로단항연산자다. 형식 : π< 속성리스트 > (R) 질의 2-2 신간도서안내를위해카달로그발송주소록을만드시오. π 이름, 주소, 핸드폰 ( 고객 ) 고객 고객번호 이름 주민번호 주소 핸드폰 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 김연아 900905-2222222 대한민국서울 000-6000-0001 3 장미란 831009-2333333 대한민국강원도 000-7000-0001 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 π 이름, 주소, 핸드폰 ( 고객 ) 이름주소핸드폰 박지성영국맨체스타 000-5000-0001 김연아대한민국서울 000-6000-0001 장미란대한민국강원도 000-7000-0001 추신수미국클리블랜드 000-8000-0001 그림 2-19 프로젝션의예
3.3.1 합집합 두개의릴레이션을합하여하나의릴레이션을반환한다. 이때두개의릴레이션은서로같은 속성순서와도메인을가져야한다. 형식 : R S 질의 2-3 마당서점은지점 A 와지점 B 가있다. 두지점의도서는각지점에서관리하며릴 레이션이름은각각도서 A, 도서 B 이다. 마당서점의도서를하나의릴레이션으로보이시오. 도서 A 도서 B 도서 A 도서번호도서이름출판사가격 1 축구의역사굿스포츠 7000 도서 B 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 4 골프바이블대한미디어 35000 5 피겨교본굿스포츠 8000 그림 2-20 합집합의예
3.3.2 교집합 합병가능한두릴레이션을대상으로하며, 두릴레이션이공통으로가지고있는투 플을반환한다. 형식 : R S 질의 2-4 마당서점의두지점에서동일하게보유하고있는도서목록을보이시오. 도서 A 도서 B 도서 A 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 도서 B 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 도서번호도서이름출판사가격 1 축구의역사굿스포츠 7000 그림 2-21 교집합의예
3.3.3 차집합 첫번째릴레이션에는속하고두번째릴레이션에는속하지않는투플을반환한다. 형식 : R - S 질의 2-5 마당서점의두지점에서동일하게보유하고있는도서목록을보이시오. 도서 A - 도서 B 도서 A 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 도서 B 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 - 도서번호도서이름출판사가격 2 축구아는여자나무수 13000 3 축구의이해대한미디어 22000 그림 2-22 차집합의예
3.3.4 카티전프로덕트 (cartesian product) 두릴레이션을연결시켜하나로합칠때사용한다. 결과릴레이션은첫번째릴레이션의오른쪽에두번째릴레이션의모든투플을순서대로배열하여반환한다. 결과릴레이션의차수는두릴레이션의차수의합이며, 카디날리티는두릴레이션의카디날리티의곱이다. 형식 : R S 질의 2-6 도서릴레이션과고객릴레이션의카티전프로덕트를구하시오 ( 결과가많으므 로투플을일부삭제한릴레이션을사용한다 ). 고객 주문
3.3.4 카티전프로덕트 (cartesian product) 고객 고객번호 이름 주소 핸드폰 1 박지성 영국맨체스타 000-5000-0001 2 김연아 대한민국서울 000-6000-0001 3 장미란 대한민국강원도 000-7000-0001 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 2 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 1 2 13000 2013-07-04 고객번호 이름 주소 핸드폰 주문번호 고객번호 도서번호 판매가격 주문일자 1 박지성 영국맨체스타 000-5000-0001 1 2 1 7000 2013-07-01 1 박지성 영국맨체스타 000-5000-0001 2 1 2 13000 2013-07-03 1 박지성 영국맨체스타 000-5000-0001 3 2 5 8000 2013-07-03 1 박지성 영국맨체스타 000-5000-0001 4 1 2 13000 2013-07-04 2 김연아 대한민국서울 000-6000-0001 1 2 1 7000 2013-07-01 2 김연아 대한민국서울 000-6000-0001 2 1 2 13000 2013-07-03 2 김연아 대한민국서울 000-6000-0001 3 2 5 8000 2013-07-03 2 김연아 대한민국서울 000-6000-0001 4 1 2 13000 2013-07-04 3 장미란 대한민국강원도 000-7000-0001 1 2 1 7000 2013-07-01 3 장미란 대한민국강원도 000-7000-0001 2 1 2 13000 2013-07-03 3 장미란 대한민국강원도 000-7000-0001 3 2 5 8000 2013-07-03 3 장미란 대한민국강원도 000-7000-0001 4 1 2 13000 2013-07-04 그림 2-23 카티전프로덕트의예
3.4 조인 (join) 두릴레이션의공통속성을기준으로속성값이같은투플을수평으로결합하는연산이다. 조인을수행하기위해서는두릴레이션의조인에참여하는속성이서로동일한도메인으로구성되어야한다. 조인연산의결과는공통속성의속성값이동일한투플만을반환한다. 형식 : R C S = σc (R S) (R과 S는릴레이션, C는조인 ) 조인연산의구분 기본연산 : 세타조인 ( ), 동등조인 ( ), 자연조인 ( ) 확장된조인연산 : 세미조인 ( ), 외부조인 ( )
3.4.1 세타조인과동등조인 세타조인 (theta join, θ) 조인에참여하는두릴레이션의속성값을비교하여조건을만족하는투플만반환한다. 세타조인의조건은 {=,,,, <, >} 중하나가된다. 형식 : R (r 조건 s) S (R과 S는릴레이션이며 r은 R의속성, s는 S의속성 ) 동등조인 (equi join) 세타조인에서 = 연산자를사용한조인을말한다. 보통조인연산이라고하면동등조인을지칭한다. 형식 : R (r = s) S 질의 2-7 고객과고객의주문사항을모두보이시오. 고객 고객. 고객번호 = 주문. 고객번호주문
3.4.1 세타조인과동등조인 고객 고객번호 이름 주소 핸드폰 1 박지성 영국맨체스타 000-5000-0001 2 김연아 대한민국서울 000-6000-0001 3 장미란 대한민국강원도 000-7000-0001 4 추신수 미국클리블랜드 000-8000-0001 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 2 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 1 2 13000 2013-07-04 5 4 4 35000 2013-07-05 6 5 3 22000 2013-07-07 7 4 3 22000 2013-07-07 고객 고객. 고객번호 = 주문. 고객번호주문 고객번호 이름 주소 핸드폰 주문번호 고객번호 도서번호 판매가격 주문일자 1 박지성 영국맨체스타 000-5000-0001 2 1 2 13000 2013-07-03 1 박지성 영국맨체스타 000-5000-0001 4 1 2 13000 2013-07-04 2 김연아 대한민국서울 000-6000-0001 1 2 1 7000 2013-07-01 2 김연아 대한민국서울 000-6000-0001 3 2 5 8000 2013-07-03 4 추신수 미국클리블랜드 000-8000-0001 5 4 4 35000 2013-07-05 4 추신수 미국클리블랜드 000-8000-0001 7 4 3 22000 2013-07-07 그림 2-24 동등조인의예
3.4.2 자연조인 (natural join) 동등조인에서조인에참여한속성이두번나오지않도록두번째속성을제거한 결과를반환한다. 형식 : R N(r, s) S 질의 2-8 고객과고객의주문사항을모두보이시오. 고객 N( 고객. 고객번호 = 주문. 고객번호 ) 주문
3.4.2 자연조인 (natural join) 고객 고객번호 이름 주소 핸드폰 1 박지성 영국맨체스타 000-5000-0001 2 김연아 대한민국서울 000-6000-0001 3 장미란 대한민국강원도 000-7000-0001 4 추신수 미국클리블랜드 000-8000-0001 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 2 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 1 2 13000 2013-07-04 5 4 4 35000 2013-07-05 6 5 3 22000 2013-07-07 7 4 3 22000 2013-07-07 고객 N( 고객. 고객번호 = 주문. 고객번호 ) 주문 고객번호 이름 주소 핸드폰 주문번호 도서번호 판매가격 주문일자 1 박지성 영국맨체스타 000-5000-0001 2 2 13000 2013-07-03 1 박지성 영국맨체스타 000-5000-0001 4 2 13000 2013-07-04 2 김연아 대한민국서울 000-6000-0001 1 1 7000 2013-07-01 2 김연아 대한민국서울 000-6000-0001 3 5 8000 2013-07-03 4 추신수 미국클리블랜드 000-8000-0001 5 4 35000 2013-07-05 4 추신수 미국클리블랜드 000-8000-0001 7 3 22000 2013-07-07 그림 2-25 자연조인의예
3.4.3 외부조인과세미조인 외부조인 (outer join) 자연조인시조인에실패한투플을모두보여주되값이없는대응속성에는 NULL 값을채워서반환한다. 모든속성을보여주는기준릴레이션의위치에따라왼쪽 (left) 외부조인, 오른쪽 (right) 외부조인, 완전 (full) 외부조인으로나뉜다. 형식 : 왼쪽 (left) 외부조인 R 완전 (full) 외부조인 R (r, s) S (r, s) S 오른쪽 (right) 외부조인 - R (r, s) S R1 R2 A B B C aa 1 1 dd cc 2 3 ee cc 3 4 ff R1 (R1.B, R2.B) R2 A B C aa 1 dd cc 2 NULL cc 3 ee 그림 2-26 왼쪽외부조인의예
3.4.3 외부조인과세미조인 질의 2-9 마당서점의고객과고객의주문내역을보이시오. 1 고객기준으로주문내역이없는고객도모두보이시오. 2 주문내역이없는고객과, 고객릴레이션에고객번호가없는주문을모두보이시오. 3 주문내역기준으로고객릴레이션에고객번호가없는주문도모두보이시오. 1 고객 ( 고객. 고객번호, 주문. 고객번호 ) 주문 2 고객 ( 고객. 고객번호, 주문. 고객번호 ) 주문 3 고객 ( 고객. 고객번호, 주문. 고객번호 ) 주문
3.4.3 외부조인과세미조인 고객 고객번호 이름 주문 주문번호고객번호판매가격 1 박지성 2 김연아 3 장미란 4 추신수 1 2 7000 2 1 13000 3 2 8000 4 1 13000 5 4 35000 6 5 22000 7 4 22000 고객고객. 고객번호 = 주문. 고객번호주문고객고객. 고객번호 = 주문. 고객번호주문고객고객. 고객번호 = 주문. 고객번호주문 고객번호 이름 주문번호 판매가격 1 박지성 2 13000 1 박지성 4 13000 2 김연아 1 7000 2 김연아 3 8000 3 장미란 NULL NULL 4 추신수 5 35000 4 추신수 7 22000 고객번호 이름 주문번호 판매가격 1 박지성 2 13000 1 박지성 4 13000 2 김연아 1 7000 2 김연아 3 8000 3 장미란 NULL NULL 4 추신수 5 35000 4 추신수 7 22000 5 NULL 6 22000 이름 주문번호 고객번호 판매가격 김연아 1 2 7000 박지성 2 1 13000 김연아 3 2 8000 박지성 4 1 13000 추신수 5 4 35000 NULL 6 5 22000 추신수 7 4 22000 1 왼쪽외부조인 2 완전외부조인 3 오른쪽외부조인 그림 2-27 외부조인의예
3.4.3 외부조인과세미조인 세미조인 (semi join) 자연조인을한후두릴레이션중한쪽릴레이션의결과만반환하며, 기호에서닫힌쪽릴레이 션의투플만반환한다. 형식 : R (r, s) S 질의 2-10 마당서점의고객중주문내역이있는고객의고객정보를보이시오. 고객 고객. 고객번호, 주문. 고객번호주문
3.4.3 외부조인과세미조인 고객 고객번호 이름 주소 핸드폰 1 박지성 영국맨체스타 000-5000-0001 2 김연아 대한민국서울 000-6000-0001 3 장미란 대한민국강원도 000-7000-0001 4 추신수 미국클리블랜드 000-8000-0001 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 2 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 1 2 13000 2013-07-04 5 4 4 35000 2013-07-05 6 5 3 22000 2013-07-07 7 4 3 22000 2013-07-07 고객 ( 고객. 고객번호, 주문. 고객번호 ) 주문 고객번호 이름 주소 핸드폰 1 박지성 영국맨체스타 000-5000-0001 2 김연아 대한민국서울 000-6000-0001 4 추신수 미국클리블랜드 000-8000-0001 그림 2-28 세미조인 ( 왼쪽이닫힌경우 ) 의예
3.5 디비전 (division) 릴레이션의속성값의집합으로연산을수행한다. 형식 : R S R S1 결과 A B R S1 B A s1 b1 b1 s1 s1 b2 s2 s1 b3 s3 s1 b4 s4 s2 b1 s2 b2 S2 s2 b4 R S2 B A s3 b1 b1 s1 s3 b2 b3 s4 b4 s4 b1 s4 b2 S3 R S3 B A b1 s1 b2 s2 b4 그림 2-29 디비전연산의예
3.6.1 셀렉션, 프로젝션, 집합연산의복합사용 질의 2-11 마당서점의도서중가격이 8,000 원이하인도서이름과출판사를보이시오. 마당서점의지점이하나인경우 도서 π 도서이름, 출판사 (σ 가격 <=8000 도서 ) 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 σ 가격 <=8000 ( 도서 ) 도서번호도서이름출판사가격 도서이름 출판사 1 축구의역사굿스포츠 7000 축구의역사 굿스포츠 5 피겨교본굿스포츠 8000 π 도서이름, 출판사 피겨교본 굿스포츠 그림 2-30 단일릴레이션에서셀렉션, 프로젝션연산의복합사용
3.6.1 셀렉션, 프로젝션, 집합연산의복합사용 마당서점의지점이둘이상인경우 π 도서이름, 출판사 ((σ 가격 <=8000 도서 A) (σ 가격 <=8000 도서 B)) 도서 A 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 2 축구아는여자 나무수 13000 3 축구의이해 대한미디어 22000 도서 B 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 4 골프바이블 대한미디어 35000 5 피겨교본 굿스포츠 8000 σ 가격 <=8000 ( 도서 A) σ 가격 <=8000 ( 도서 B) 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 도서번호 도서이름 출판사 가격 1 축구의역사 굿스포츠 7000 5 피겨교본 굿스포츠 8000 도서번호도서이름출판사가격 1 축구의역사굿스포츠 7000 5 피겨교본굿스포츠 8000 도서이름 축구의역사 피겨교본 π도서이름, 출판사출판사굿스포츠굿스포츠 그림 2-31 두개이상의릴레이션에서셀렉션, 프로젝션, 합집합연산의복합사용
3.6.2 카티전프로덕트를사용한연산과조인을사용한연산 질의 2-12 마당서점의박지성고객의거래내역중주문번호, 이름, 가격을보이시오. 카티전프로덕트를사용한연산 π 주문. 주문번호, 고객. 이름, 주문. 판매가격 (σ 고객. 고객번호 = 주문. 고객번호 AND 고객. 이름 = 박지성 ( 고객 주문 ))
고객 고객번호 이름 주민번호 주소 핸드폰 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 김연아 900905-2222222 대한민국서울 000-6000-0001 3 장미란 831009-2333333 대한민국강원도 000-7000-0001 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 1 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 3 2 13000 2013-07-04 5 4 4 35000 2013-07-05 6 1 3 22000 2013-07-07 고객 주문 7 4 3 22000 2013-07-07 고객번호이름주민번호주소핸드폰주문번호고객번호도서번호판매가격주문일자 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 1 1 1 7000 2013-07-01 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 1 2 13000 2013-07-03 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 3 2 5 8000 2013-07-03 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 4 3 2 13000 2013-07-04 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 5 4 4 35000 2013-07-05 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 6 1 3 22000 2013-07-07 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 7 4 3 22000 2013-07-07 2 김연아 900905-2222222 대한민국서울 000-6000-0001 1 1 1 7000 2013-07-01 결과생략 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 5 4 4 35000 2013-07-05 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 6 1 3 22000 2013-07-07 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 7 4 3 22000 2013-07-07 σ 고객. 고객번호 = 주문. 고객번호 AND 고객. 이름 = 박지성 고객번호이름주민번호주소핸드폰주문번호고객번호도서번호판매가격주문일자 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 1 1 1 7000 2013-07-01 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 1 2 13000 2013-07-03 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 6 1 3 22000 2013-07-07 π 주문. 주문번호, 고객. 이름, 주문. 판매가격 주문번호이름판매가격 1 박지성 7000 그림 2-32 카티전프로덕트를사용한연산 2 박지성 13000 6 박지성 22000
3.6.2 카티전프로덕트를사용한연산과조인을사용한연산 조인을사용한연산 π 주문번호, 이름, 판매가격 (σ 이름 = 박지성 ( 고객고객. 고객번호 = 주문. 고객번호주문 ))
고객 고객번호 이름 주민번호 주소 핸드폰 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 김연아 900905-2222222 대한민국서울 000-6000-0001 3 장미란 831009-2333333 대한민국강원도 000-7000-0001 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 주문 주문번호 고객번호 도서번호 판매가격 주문일자 1 1 1 7000 2013-07-01 2 1 2 13000 2013-07-03 3 2 5 8000 2013-07-03 4 3 2 13000 2013-07-04 고객 고객. 고객번호 = 주문. 고객번호주문 5 4 4 35000 2013-07-05 6 1 3 22000 2013-07-07 7 4 3 22000 2013-07-07 고객번호 이름 주민번호 주소 핸드폰 주문번호 고객번호 도서번호 판매가격 주문일자 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 1 1 1 7000 2013-07-01 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 1 2 13000 2013-07-03 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 6 1 3 22000 2013-07-07 2 김연아 900905-2222222 대한민국서울 000-6000-0001 3 2 5 8000 2013-07-03 3 장미란 831009-2333333 대한민국강원도 000-7000-0001 4 3 2 13000 2013-07-04 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 5 4 4 35000 2013-07-05 4 추신수 820713-1444444 미국클리블랜드 000-8000-0001 7 4 3 22000 2013-07-07 σ 이름 = 박지성 고객번호이름주민번호주소핸드폰주문번호고객번호도서번호판매가격주문일자 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 1 1 1 7000 2013-07-01 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 2 1 2 13000 2013-07-03 1 박지성 810225-1111111 영국맨체스타 000-5000-0001 6 1 3 22000 2013-07-07 π 주문번호, 이름, 판매가격 주문번호 이름 판매가격 1 박지성 7000 2 박지성 13000 6 박지성 22000 그림 2-33 조인을사용한연산
요약 1. 릴레이션 2. 릴레이션스키마 3. 릴레이션인스턴스 4. 관계데이터베이스시스템 5. 키 6. 무결성제약조건 7. 참조무결성제약조건의옵션 8. 관계대수 9. 셀렉션 10. 프로젝션 11. 집합연산 12. 조인 13. 디비전
SQL Server 로배우는데이터베이스개론과실습