Move Fast

  데이터베이스 설계

• 개념적 데이터베이스 설계와 물리적 데이터베이스 설계로 구분한다.
• 개념적 데이터베이스 설계 : 실제로 데이터베이스를 구현하는 방법과는 독립적으로 정보 사용의 모델을 개발하는 과정
• 물리적 데이터베이스 설계 : 물리적인 저장 장치(Disk)와 접근 방식(인덱스의 생성)을 다룬다.
• 개념적 데이터베이스 설계 과정에서 조직체(실세계)의 엔티티, 관계, 프로세스, 무결성 제약조건 등을 나타내는 추상화 모델(ER 모델)을 구축한다.
• 엔티티 : 서로 구분이 되면서 조직체에서 데이터베이스에 나타내려는 객체(사람, 장소, 사물 등)을 의미한다.
• 관계 : 두 개 이상의 엔티티들 간의 연관을 나타낸다.
• 프로세스 : 관련된 활동들을 나타낸다.
• 무결성 제약조건 : 데이터의 정확성과 비즈니스 규칙을 의미한다.

  개념적 수준의 모델

• 특정 데이터 모델과 독립적으로 응용 세계를 모델링할 수 있도록 한다.
• 데이터베이스 구조나 스키마를 하향식으로 개발할 수 있기 위한 틀(framework)을 제공한다.
• 인기 있는 개념적 수준의 모델은 엔티티-관계(Entity-Relationship, ER) 모델
• ER 모델과 같은 개념적 데이터 모델이 사상될 수 있는 다수의 구현 데이터 모델(implementation data model)이 존재한다.
• 구현 단계에서 사용되는 세 가지 데이터 모델

- 관계 데이터 모델

- 계층 데이터 모델

- 네트워크 데이터 모델

• 개념적 DB 설계 : 실세계 정보->ER모델
• 논리적 DB 설계 : 개념적 데이터 모델(ER모델)->구현적 데이터 모델(릴레이션)
• 프로세스 모델링 : 어떤 프로세스가 어떤 릴레이션에 접근하는가를 모델링한 것

  데이터베이스 설계의 개요

• 한 조직체의 운영과 목적을 지원하기 위해 데이터베이스를 생성하는 과정
• 목적 : 모든 주요 응용과 사용자들이 요구하는 데이터, 데이터 간의 관계를 표현하는 것
• 데이터베이스 개발은 일반적인 프로젝트 라이프 사이클 과정을 따른다.
• 훌륭한 데이터베이스 설계 : 시간의 흐름에 따른 데이터의 모든 측면을 나타내고, 데이터 항목의 “중복을 최소화”하고, 데이터베이스에 대한 “효율적인 접근”을 제공하고, 데이터베이스의 “무결성을 제공”하고, 이해하기 쉬워야 한다.

  데이터베이스 설계의 주요 단계

• 요구사항 수집과 분석 → 개념적 설계 → DBMS 선정 → 논리적 설계 → 스키마 정제(정규화) → 물리적 설계( → 트랜잭션 설계) → 보안 설계 → 구현 단계(DB 구축 및 튜닝)
• 일반적으로 데이터베이스 설계의 완성도를 높이기 위해서 위의 과정에서 앞뒤로 왔다갔다할 필요가 있다.
• 요구사항 수집과 분석

- 흔히 기존의 문서를 조사하고, 인터뷰(가장 흔함)나 설문 조사 등이 시행된다.

- 설문 조사는 자유롭게 의견을 적어내도록 하는 방식과 주어진 질문에 대해서만 답을 하는 방식으로 구분된다.

- 요구사항에 관한 지식을 기반으로 관련 있는 엔티티들과 이들의 애트리뷰트들이 무엇인가, 엔티티들 간의 관계가 무엇인가 등을 파악한다.

- 데이터 처리에 관한 요구사항에 대하여 전형적인 연산들은 무엇인가, 연산들의 의미, 접근하는 데이터의 양 등을 분석한다.

- 결과물 : 데이터베이스 요구사항

• 개념적 설계

- 모든 물리적인 사항과 독립적으로, 한 조직체에서 사용되는 정보의 모델을 구축하는 과정

- 사용자들의 요구사항 명세로부터 개념적 스키마가 만들어진다.

- 높은 추상화 수준의 데이터 모델을 기반으로 정형적인 언어로 데이터 구조를 명시한다.

- 대표적인 데이터 모델 : ER모델

- 개념적 설계의 단계에서는 엔티티 타입, 관계 타입, 애트리뷰트들을 식별하고, 애트리뷰트들의 도메인을 결정하고, 후보 키와 기본 키 애트리뷰트들을 결정한다.

- 완성된 개념적 스키마(ER 스키마)는 ER다이어그램으로 표현된다.

- 결과물 : 개념적 스키마(ER 스키마)

• DBMS 선정

- 여러 가지 요인들을 검토한 후 DBMS를 선정한다.

- 기술적 요인 : DBMS가 제공하는 데이터 모델, 저장 구조, 인터페이스, 질의어, 도구, 제공되는 서비스 등

- 정치적 요인 : 고수준의 전략적인 결정 등

- 경제적 요인 : DBMS 구입 비용, 하드웨어 구입 비용, 유지 보수(서비스) 비용, 기존의 시스템을 새로운 DBMS에 맞게 변환하는데 소요되는 비용, 인건비, 교육비 등

• 논리적 설계

- 데이터베이스 관리를 위해 선택한 DBMS의 데이터 모델을 사용하여 논리적 스키마(외부 스키마 포함)를 생성한다.

- 개념적 스키마에 알고리즘(ER 다이얼그램)을 적용하여 논리적 스키마(릴레이션)를 생성한다.

- 논리적 스키마를 나타내기 위해 관계 데이터 모델을 사용하는 경우에는 ER 모델로 표현된 개념적 스키마를 관계 데이터베이스 스키마로 사상한다.

- 관계 데이터베이스 스키마를 향상시키기 위해 정규화 과정을 적용한다.

- 데이터베이스 설계자가 요구사항 수집과 분석 후에 바로 논리적 설계 단계로 가는 경우 흔히 좋은 관계 데이터베이스 스키마가 생성되지 않는다.

- 결과물 : 논리적 스키마(관계 데이터베이스 스키마)

• 물리적 설계

- 처리 요구사항들을 만족시키기 위해 저장 구조와 접근 경로 등을 결정한다.

- 성능상의 주요 기준은 응답 시간, 트랜잭션 처리율, 보고서 생성 시간으로 구분할 수 있다.

- 응답 시간 : 질의와 갱신이 평균적으로 또는 피크 시간 때 얼마나 오래 걸릴 것인가

- 트랜잭션 처리율(Transactions Per Seconds, TPS) : 1초당 얼마나 많은 트랜잭션들이 평균적으로 또는 피크 시간 때 처리될 수 있는가

- 전체 데이터베이스에 대한 보고서를 생성하는데 얼마나 오래 걸릴 것인가

• 트랜잭션 설계

- 요구사항 수집과 분석 후에 데이터베이스 설계 과정과 별도로 트랜잭션 설계를 진행할 수 있다.

- 트랜잭션 : 완성될 데이터베이스에서 동작할 응용 프로그램

- 데이터베이스 스키마는 트랜잭션에서 요구하는 모든 정보를 포함해야 한다.

- 검색, 갱신, 혼합 등 세 가지 유형으로 구분하여 입력과 출력, 동작 등을 식별한다.

  ER 모델

• 데이터베이스 설계를 용이하게 하기 위해 P.P Chen이 1976년에 제안한 개념적 데이터 모델
• 현재는 EER(Enhanced Entity Relationship) 모델이 데이터베이스 설계 과정에 널리 사용되고 있다.
• 개념적 설계를 위한 인기 있는 모델로서, 많은 Computer Aided Software Engineering (CASE)도구들에서 지원된다.
• 실세계를 엔티티, 애트리뷰트, 엔티티들 간의 관계로 표현한다.
• 쉽게 관계 데이터 모델로 사상된다.
• 기본적인 용어로는 엔티티, 관계, 애트리뷰트가 있고, 기타 용어로는 카디날리티 비율(관계 비율), 참여 제약조건 등이 있다.
• 적은 노력으로 쉽게 배울 수 있고, 전문가가 아니어도 이해하기 쉬우며, 자연어보다는 좀더 정형적이고, 구현에 독립적이어서 데이터베이스 설계자들이 최종 사용자들과 의사 소통을 하는데 적합하다.
• ER 모델을 기반으로 만들어진 다수의 CASE 도구들이 존재한다.
• CASE 도구들은 ER 설계를 자동적으로 SQL Server, 오라클 등의 DBMS에서 제공하는 데이터 정의어로 변환하고, 어떤 도구는 XML로 변환한다.
• 현재는 데이터베이스 설계를 위한 다소 구형 그래픽 표기법을 사용한다.
• 엔티티(Entity)

- 하나의 엔티티는 사람, 장소, 사물, 사건 등과 같이 독립적으로 존재하면서 고유하게 식별이 가능한 실세계의 객체이다. - 릴레이션의 레코드(투플)과 유사한 개념.

- 사물처럼 실체가 있는 것도 있지만 생각이나 개념과 같이 추상적인 것도 존재한다.

  엔티티 타입

• 엔티티들은 엔티티 타입(또는 엔티티 집합)들로 분류된다.
• 포함관계 : 엔티티  엔티티 집합
• 엔티티 타입 : 동일한 애트리뷰트들을 가진 엔티티들의 틀(내포)
• 엔티티 집합 : 동일한 애트리뷰트들을 가진 엔티티들의 모임(외연)
• 하나의 엔티티는 한 개 이상의 엔티티 집합에 속할 수 있다.
• 엔티티 집합과 엔티티 타입을 엄격하게 구분할 필요는 없다.
• ER 다이어그램에서 엔티티 타입은 직사각형으로 나타낸다.

• 강한 엔티티 타입(정규 엔티티 타입)

- 독자적으로 존재한다.

- 엔티티 타입 내에서 자신의 키 애트리뷰트를 사용하여 고유하게 엔티티들을 식별할 수 있는 엔티티 타입

• 약한 엔티티 타입

- 키를 형성하기에 충분한 애트리뷰트를 갖지 못한 엔티티 타입

- 이 엔티티 타입이 존재하려면 소유 엔티티 타입(Owner Entity Type)이 있어야 한다.

- 소유 엔티티 타입 또는 식변 엔티티 타입(Identifying Entity Type) : 약한 엔티티 타입에게 키 애트리뷰트를 제공하는 엔티티 타입

- 소유 엔티티 타입의 키 애트리뷰트를 결합해야만 고유하게 약한 엔티티들을 식별할 수 있다.

- 소유 엔티티 타입의 기본 키가 되는 애트리뷰트는 약한 엔티티 타입에서 외래 키이면서 동시에 기본 키의 구성요소가 된다.

- ER 다이어그램에서 경계가 이중선 직사각형으로 표현한다.

- ER 다이어 그램에서 약한 엔티티 타입의 부분 키는 점선 밑줄을 그어 표시한다.

- 부분 키(partial key) : 부양 가족의 이름처럼 한 사원에 속한 부양가족 내에서는 서로 다르지만 회사 전체 사원들의 부양가족들 전체에서는 같은 경우가 생길 수 있는 애트리뷰트. 즉, 부분적(사원 하나)으로는 식별역할을 하나, 전체(사원 전체)로 봤을 때 식별 역할을 못하는 키

- ER 다이어그램에서 약한 엔티티 타입과 소유 엔티티 타입의 관계는 이중실선 다이아몬드로 표현된다.

  애트리뷰트(Attribute)

• 하나의 엔티티는 연관된 애트리뷰트들의 집합
• 한 애트리뷰트의 도메인은 그 애트리뷰트가 가질 수 있는 모든 가능한 값들의 집합
• 여러 애트리뷰트가 동일한 도메인을 공유할 수 있다. ex) 네 자리 정수
• 키 애트리뷰트는 한 애트리뷰트 또는 애트리뷰트들의 모임으로서 한 엔티티 타입 내에서 각 엔티티를 고유하게 식별한다.
• ER 다이어그램에서 기본 키에 속하는 애트리뷰트는 밑줄을 그어 표시한다.
• 요구사항 명세에서 “명사”나 “형용사”로 표현된다.
• 엔티티는 독립적인 의미를 갖는 반면, 애트리뷰트는 독립적인 의미를 갖지 않는다.
• ER 다이어그램에서 타원형으로 나타낸다.
• ER 다이어그램에서 애트리뷰트와 엔티티 타입은 실선으로 연결한다.
• 단순 애트리뷰트(Simple Attribute)

- 더 이상 다른 애트리뷰트로 나눌 수 없는 애트리뷰트

- ER 다이어그램에서 실선 타원으로 표현된다.

- ER 다이어그램에서 대부분의 애트리뷰트는 단순 애트리뷰트이다.

• 복합 애트리뷰트(Composite Attribute)

- 두 개 이상의 애트리뷰트로 이루어진 애트리뷰트

- 동일한 엔티티 타입이나 관계 타입에 속하는 애트리뷰트들 중에서 밀접하게 연관된 것을 모아놓은 것

• 단일 값 애트리뷰트(Single-Valued Attribute)

- 각 엔티티마다 정확하게 하나의 값을 갖는 애트리뷰트

- ER 다이어그램에서 단순 애트리뷰트와 동일하게 표현된다.

- ER 다이어그램에서 대부분의 애트리뷰트는 단일 값 애트리뷰트

- Ex. 사원의 사원번호 애트리뷰트는 어떤 사원도 두 개 이상의 사원번호를 갖지 않으므로 단일 값 애트리뷰트

• 다치 애트리뷰트(Multi-Valued Attribute)

- 각 엔티티마다 여러 개의 값을 가질 수 있는 애트리뷰트

- ER 다이어그램에서 이중선 타원으로 표현한다.

- ER 모델에서만 허용되는 개념으로, 관계 데이터 모델(구현 데이터 모델의 유형)에서는 알고리즘을 통해 이 문제를 해결한다.

• 저장된 애트리뷰트(Stored Attribute)

- 다른 애트리뷰트와 독립적으로 존재하는 애트리뷰트

- ER 다이어그램에서 단순 애트리뷰트와 동일하게 표현된다.

- ER 다이어그램에서 대부분의 애트리뷰트는 저장된 애트리뷰트

- Ex. 사원 엔티티 타입에서 사원이름, 급여는 다른 애트리뷰트와 독립적으로 존재한다.

• 유도된 애트리뷰트(Derived Attribute)

- 다른 애트리뷰트의 값으로부터 얻어진 애트리뷰트

- 관계 데이터베이스에서 릴레이션의 애트리뷰트로 포함시키지 않는 것이 좋다. 계속 다른 컬럼으로부터 연산하여 값을 얻을 수 있기 때문에 포함시키면 매우 비효율적이다.

- ER 다이어그램에서 점선 타원으로 표현한다.

  관계와 관계 타입

• 관계는 엔티티들 사이에 존재하는 연관이나 연결로서 두 개 이상의 엔티티 타입들 사이의 사상으로 생각할 수 있다.
• 관계 집합은 동질(성질이 같음)의 관계들의 집합
• 관계 타입은 동질의 관계들의 틀
• 관계 집합과 관계 타입을 엄격하게 구분할 필요는 없다.
• 요구사항 명세에서 흔히 “동사”는 ER 다이어그램에서 관계로 표현된다.
• ER 다이어그램에서 관계 타입은 다이아몬드로 표현된다.
• ER 다이어그램에서 관계 타입과 서로 연관되는 엔티티 타입들은 관계 타입에 실선으로 연결한다.

“EMPLOYEE 의 각 엔티티와 DEPARTMENT 의 각 엔티티는 WORKS_FOR 관계에 참여한다”고 표현한다.

[ 엔티티와 엔티티 간의 관계의 예 ]

• 관계의 애트리뷰트

- 관계 타입은 관계의 특징을 기술하는 애트리뷰트들을 가질 수 있다.

- 관계 타입은 키 애트리뷰트를 갖지 않는다.

- 관계의 특징이란 상황에 맞게 유동적으로 변하는 데이터를 의미한다.

- Ex. 공급자와 부품간의 Supplies 관계 타입이 생성되었을 때, 관계의 특징으로 Quantity (수량)애트리뷰트가 관계 타입에 존재한다고 가정하자. 여기서 Quantity가 공급자(Supplier) 엔티티의 애트리뷰트로 존재한다면, 공급자는 부품을 항상 고정된 수량으로 생산한다는 의미를 갖게 된다. 따라서 Quantity 애트리뷰트는 관계 타입(Supplies) 엔티티에 애트리뷰트로 있으면서 유동적인 수량값을 의미하는 것이다.

  차수(Degree)

• 관계로 연결된 엔티티 타입들의 개수
• 하나의 관계 타입에 몇 개의 엔티티 타입이 연관되는지를 나타낸다.
• 실세계에서 가장 흔한 관계는 두 개의 엔티티 타입을 연결하는 2진 관계이다.
• 유형으로 1진 관계, 2진 관계, 3진 관계, N진 관계가 있다.
• 3진(또는 N진) 관계는 2진(또는 3진) 관계로 표현될 수 없다.

  카디날리티(Cardinality)

• 카디날리티 비율 : 한 엔티티가 참여할 수 있는 관계의 수
• 관계 타입에 참여하는 엔티티들의 가능한 조합을 제한한다.
• 관계를 흔히 1:1, 1:N, M:N으로 구분한다.
• ER 다이어그램에서 카디날리티에 관한 정보는 간선 위에 나타낸다.

(E1, E2 : 엔티티 타입)

• 1:1 관계

- E1의 각 엔티티가 정확하게 E2의 한 엔티티와 연관되고, E2의 각 엔티티가 정확하게 E1의 한 엔티티와 연관되는 관계

- Ex. 각 사원에 대해 최대한 한 개의 PC가 있고, 각 PC에 대해 최대한 한 명의 사원이 이용한다면 사원과 PC의 관계는 1:1 관계이다.

• 1:N 관계 (또는 N:1 관계)

- E1의 각 엔티티가 E2의 임의의 개수의 엔티티와 연관되고, E2의 각 엔티티는 정확하게 E1의 한 엔티티와 연관되는 관계

- 실세계에서 가장 흔히 나타나는 관계 (학과:학생 → 1:N)

- Ex. 각 사원에 대해 최대한 한 대의 PC가 있고, 각 PC에 대해 여러 명의 사원들이 이용한다면 사원과 PC의 관계는 N:1 관계이다.

- Ex. 각 사원에 대해 여러 대의 PC가 있고, 각 PC에 대해 최대한 한명의 사원이 이용한다면 사원과 PC의 관계는 1:N 관계이다.

• M:N 관계

- 한 엔티티 타입에 속하는 임의의 개수의 엔티티가 다른 엔티티 타입에 속하는 임의의 개수의 엔티티와 연관된다.

- Ex. 각 사원에 대해 여러 대의 PC가 있고, 각 PC에 대해 여러 명의 사원들이 이용한다면 사원과 PC의 관계는 M:N 관계이다.

  카디날리티 비율의 최소값과 최대값

• ER 다이어그램에서 관계 타입과 엔티티 타입을 연결하는 실선 위에 (min, max) 형태로 표기한다.
• 어떤 관계 타입에 참여하는 각 엔티티 타입에 대하여 min은 이 엔티티 타입 내의 각 엔티티는 적어도 min 번 관계에 참여함을 의미한다.
• 어떤 관계 타입에 참여하는 각 엔티티 타입에 대하여 max는 이 엔티티 타입 내의 각 엔티티는 최대 max 번 관계에 참여함을 의미한다.
• min=0 : 어떤 엔티티가 반드시 관계에 참여해야 할 필요는 없음을 의미한다. (선택적 참여O)
• max=* : 어떤 엔티티가 관계에 임의의 수만큼 참여할 수 있음을 의미한다. (상한이 존재X)
• 관계에 참여하는 엔티티의 숫자가 아니라, 하나의 엔티티가 몇 번 관계에 참여하는 가를 나타낸다.

  다중 관계와 순환적 관계

• 다중 관계 : 두 엔티티 타입 사이에 두 개 이상의 관계 타입이 존재할 수 있다.
• 순환적 관계 : 하나의 엔티티 타입이 동일한 관계 타입에 두 번 이상 참여하는 것

  역할(Role)

• 관계 타입의 의미를 명확하게 하기 위해 사용된다.
• 특히 하나의 관계 타입에 하나의 엔티티 타입이 여러 번 나타나는 경우(Ex. 1진 관계)에는 반드시 역할을 표기해야 한다. (역할을 생략해선 안된다.)
• ER 다이어그램에서 관계 타입의 간선 위에 표시한다.
  

  전체 참여와 부분 참여

• 전체 참여 : 어떤 관계에 엔티티 타입 E1의 모든 엔티티들이 관계 타입 R에 의해서 어떤 엔티티 타입 E2의 어떤 엔티티와 연관되는 것을 의미한다.
• 부분 참여 : 엔티티 타입의 일부 엔티티만 관계에 참여하는 것
• 약한 엔티티 타입은 항상 관계에 전체 참여한다.
• 카디날리티 비율과 함께 “참여 제약조건”은 관계에 대한 중요한 제약조건이다.
• 두 엔티티 타입 E1, E2의 엔티티 비율이 100:10 이고, E1이 부분 참여, E2가 전체 참여일 때 부분 참여하는 E1의 엔티티 수는 최대 10이다.
• ER 다이어그램에서 전체 참여는 이중 실선으로 표시한다.

  ER 스키마를 작성하기 위한 지침

• 엔티티는 키 애트리뷰트 이외에 설명 정보를 추가로 가진다. 설명정보가 없으면 애트리뷰트이다.
• 다치 애트리뷰트는 엔티티로 분류해야 한다.
• 애트리뷰트들이 직접적으로 설명하는 엔티티에 애트리뷰트들을 붙인다.
• 가능한 한 복합 식별자를 피해야 한다.
• 관계는 일반적으로 독자적으로 존재할 수 없지만 엔티티 타입과 관계 타입을 절대적으로 구분하는 것은 어렵다.
  

  데이터베이스 설계 과정

• 응용의 요구사항을 수집하여 기술
• 응용과 연관이 있는 엔티티 타입들을 식별
• 응용과 연관이 있는 관계 타입들을 식별
• 관계의 카디날리티 비율이 1:1, 1:N, M:N 중에서 어느 것에 해당하는지 결정
• 엔티티 타입과 관계 타입에 필요한 애트리뷰트들을 식별하고, 각 애트리뷰트가 가질 수 있는 값들의 집합(도메인)을 식별
• 엔티티 타입들을 위한 기본 키를 식별
• 응용을 위한 ER 스키마 다이어그램을 그린다.
• ER 스키마 다이어그램이 응용에 대한 요구사항과 부합되는지 검사
• ER 스키마 다이어그램을 DBMS에서 사용되는 데이터베이스 모델로 변환

  ER 모델의 또 다른 표기법

• ERWin 등의 CASE 도구들에서는 “새발(crow-feet) 표기법”이 흔히 사용된다.
• 새발 표기법에도 여러 가지 변형들이 존재한다.

• 새발 표기법에서 엔티티 타입과 애트리뷰트의 표현

• Ex. 기업에서 흔히 볼 수 있는 작은 세계에 관한 요구사항

강한 엔티티 타입 약한 엔티티 타입 애트리뷰트 다치 애트리뷰트 관계 타입

  ER 스키마를 관계 모델의 릴레이션으로 사상

• 논리적 설계 단계에서는 ER 스키마를 관계 데이터 모델의 릴레이션들로 사상한다.
• ER 스키마에는 엔티티 타입과 관계 타입이 존재하지만 관계 데이터베이스에서는 엔티티 타입과 관계 타입을 구분하지 않고 릴레이션들만 있다.
• 릴레이션으로 사상할 대상이 ER 스키마에서 엔티티 타입인지 또는 관계 타입인지, 엔티티 타입이라면 정규(강한) 엔티티 타입인지 또는 약한 엔티티 타입인지, 관계 타입이라면 2진 관계 타입인지 3진 이상의 관계 타입인지, 애트리뷰트가 단일 값 애트리뷰트인지 또는 다치 애트리뷰트인지 등에 따라 사상하는 방법이 달라진다.
• ER 모델을 릴레이션들로 사상하는 7개의 단계로 이루어진 알고리즘

  ER-관계 사상 알고리즘

• 1단계 : 정규 엔티티 타입과 단일 값 애트리뷰트

- ER 스키마의 각 정규 엔티티 타입 E에 대해 하나의 릴레이션 R을 생성한다.

- E에 있던 단순 애트리뷰트들을 릴레이션 R에 모두 포함시킨다.

- E에서 복합 애트리뷰트는 그 복합 애트리뷰트를 구성하는 단순 애트리뷰트들만 릴레이션 R에 포함시킨다. (복합 애트리뷰트 자체는 포함되지 않는다.)

- E의 기본 애트리뷰트가 릴레이션 R의 기본 키가 된다.

• 2단계 : 약한 엔티티 타입과 단일 값 애트리뷰트

- ER 스키마에서 소유 엔티티 타입 E를 갖는 각 약한 엔티티 타입 W에 대하여 릴레이션 R을 생성한다.

- W에 있던 모든 단순 애트리뷰트들을 릴레이션 R에 포함시킨다.

- 소유 엔티티 타입에 해당하는 릴레이션의 기본 키를 약한 엔티티 타입에 해당하는 릴레이션에 외래 키로 포함시킨다.

- 약한 엔티티 타입에 해당하는 릴레이션 R의 기본 키는 약한 엔티티 타입의 부분 키와 소유 엔티티 타입에 해당하는 릴레이션을 참조하는 외래 키의 조합으로 이루어진다.

• 3단계 : 2진 1:1 관계 타입

- ER 스키마의 각 2진 1:1 관계 타입 R에 대하여, R에 참여하는 엔티티 타입에 대응되는 릴레이션 S와 T를 찾는다.

- 관계 타입에 전체 참여하는 엔티티 타입을 릴레이션 S로 생성한다.

- 관계 타입에 부분 참여하는 엔티티 타입을 릴레이션 T로 생성한다.

- 릴레이션 T의 기본 키를 릴레이션 S의 외래 키로 포함시킨다.

- 위의 경우가 아니라면, S와 T 중에서 한 릴레이션을 선택하여, 만일 S를 선택했다면 T의 기본 키를 S에 외래 키로 포함시킨다.

- 관계 타입 R이 가지고 있는 모든 단순 애트리뷰트(복합 애트리뷰트를 갖고 있는 경우에는 복합 애트리뷰트를 구성하는 단순 애트리뷰트)들을 릴레이션 S에 포함시킨다.

- 두 엔티티 타입이 관계 타입 R에 전체 참여할 때는 두 엔티티 타입과 관계 타입을 하나의 릴레이션으로 합치는 방법도 가능하다.

- 관계 타입을 하나의 릴레이션으로 생성하여 관계에 참여하는 각 엔티티 타입의 기본 키를 외래 키로 포함시키는 방법(외래 키들은 모두 기본 키의 구성요소)도 있으나, 조인의 횟수가 많으므로 권장되지 않는다.

• 4단계 : 정규 2진 1:N 관계 타입

- 관계 타입 R에 참여하는 N측의 엔티티 타입에 대응되는 릴레이션 S를 찾는다.

- 관계 타입 R에 참여하는 1측의 엔티티 타입에 대응되는 릴레이션 T를 찾는다.

- 릴레이션 T의 기본 키를 릴레이션 S에 외래 키로 포함시킨다.

- Ex. 하나의 부서에는 다수의 사원들이 있으므로 부서:사원 -> 1:N 관계이다. 부서 릴레이션은 T, 사원 릴레이션은 S에 해당하므로, DEPARTMENT[부서]의 기본 키를 EMPLOYEE[사원]의 외래 키로 포함시킨다.

- 반면, N측의 릴레이션 S의 기본 키를 1측의 릴레이션 T에 외래 키로 포함시키면 애트리뷰트에 값들의 집합이 들어가거나 정보의 중복이 많이 발생한다.

- Ex. EMPLOYEE[사원]의 기본 키를 DEPARTMENT[부서]의 외래 키로 포함시키면, 부서 릴레이션에서 외래 키를 제외한 나머지 데이터들이 모두 중복된 데이터이므로 비효율적이다.

- 관계 타입 R이 가지고 있는 모든 단순 애트리뷰트(복합 애트리뷰트를 갖고 있는 경우에는 복합 애트리뷰트를 구성하는 단순 애트리뷰트)들을 릴레이션 S에 포함시킨다.

- S와 T에 대응하는 엔티티 타입이 동일할 경우(순환적 관계) 하나의 릴레이션에 기본 키를 참조하는 외래 키를 포함시킨다. (자기 참조 릴레이션 생성)

• 5단계 : 2진 M:N 관계 타입

- 2진 M:N 관계 타입 R에 대해서는 릴레이션 R을 생성한다.

- 참여 엔티티 타입에 해당하는 릴레이션들의 기본 키를 릴레이션 R에 외래 키로 포함시키고, 이들의 조합은 릴레이션 R의 기본 키가 된다. (각 외래 키는 기본 키의 구성요소)

- 관계 타입 R이 가지고 있는 모든 단순 애트리뷰트(복합 애트리뷰트를 갖고 있는 경우에는 복합 애트리뷰트를 구성하는 단순 애트리뷰트)들을 릴레이션 R에 포함시킨다.

• 6단계 : 3진 이상의 관계 타입

- 3진 이상의 각 관계 타입 R에 대하여 릴레이션 R을 생성한다.

- 관계 타입 R에 참여하는 모든 엔티티 타입에 대응되는 릴레이션들의 기본 키를 릴레이션 R에 외래 키로 포함시킨다.

- 관계 타입 R이 가지고 있는 모든 단순 애트리뷰트(복합 애트리뷰트를 갖고 있는 경우에는 복합 애트리뷰트를 구성하는 단순 애트리뷰트)들을 릴레이션 R에 포함시킨다.

- 일반적으로 외래 키들의 조합이 릴레이션 R의 기본 키가 된다.

- 관계 타입 R에 참여하는 엔티티 타입들의 카디날리티(하나의 엔티티가 참여하는 관계 횟수)가 1:N:N이면 카디날리티가 1인 릴레이션의 기본 키를 참조하는 외래 키를 제외한 나머지 외래 키들의 조합(N측의 기본키를 참조하는 외래키들)이 릴레이션 R의 기본 키가 된다.

• 7단계 : 다치 애트리뷰트

- 각 다치 애트리뷰트(여러 값을 갖는 애트리뷰트)에 대하여 릴레이션 R을 생성한다.

- 다치 애트리뷰트에 해당하는 애트리뷰트를 릴레이션 R에 포함시키고, 다치 애트리뷰트를 소유한 엔티티 타입이나 관계 타입에 해당하는 릴레이션의 기본 키를 릴레이션 R에 외래 키로 포함시킨다.

- 다치 애트리뷰트의 값을 반복해서 단일값으로 삽입한다.

- 릴레이션 R의 기본 키는 다치 애트리뷰트와 외래 키의 조합이다.

- Ex. 한 사원이 여러 취미를 가지는 경우, hobby 애트리뷰트는 다치 애트리뷰트이므로 (사원번호, 취미)를 기본 키로 갖는 릴레이션으로 설계되어야 한다.

1단계 : 정규 엔티티 타입과 단일 값 애트리뷰트

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary)

PROJECT(Projno, Projname, Budget)

DEPARTMENT(Deptno, Deptname, Floor)

SUPPLIER(Suppno, Suppname, Credit)

PART(Partno, Partname, Price)

2단계 : 약한 엔티티 타입과 단일 값 애트리뷰트

DEPENDENT(Empno, Depname, Sex)

3단계 : 2진 1:1 관계 타입

PROJECT(Projno, Projname, Budget, StartDate, Manager)

[Manager는 EMPLOYEE의 기본 키인 Empno을 참조하는 외래 키]

4단계 : 정규 2진 1:N 관계 타입

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary, Dno)

[Dno는 DEPARTMENT의 기본 키인 Deptno을 참조하는 외래 키]

PART(Partno, Partname, Price, Subpartno)

[Subpartno은 PART의 기본 키인 Partno을 참조하는 외래 키]

5단계 : 2진 M:N 관계 타입

WORKS_FOR(Empno, Projno, Duration, Responsibility)

6단계 : 3진 이상의 관계 타입

SUPPLY(Suppno, Projno, Partno, Quantity)

7단계 : 다치 애트리뷰트

PROJ_LOC(Projno, Location)

  내포된 SQL (Embeded SQL)

• 호스트 언어에 포함되는 SQL문
• SQL이 호스트 언어(고급 프로그래밍 언어)의 완전한 표현력을 갖고 있지 않기 때문에 모든 질의를 SQL로 표현할 수는 없다.
• SQL은 호스트 언어가 갖고 있는 조건문, 반복문, 입출력 등과 같은 동작, 사용자와의 상호 작용, 질의 결과를 GUI로 보내는 등의 기능을 갖고 있지 않다.
• C, C++, 자바 등의 언어로 작성하는 프로그램에 SQL 문을 삽입하여, 데이터베이스를 접근하는 부분에 대해 SQL에 없는 기능은 호스트 언어로 작성할 필요가 있다.
• 데이터 구조가 불일치하는 문제(Impedance Mismatch)가 발생
• ESQL/C : MSSQL SERVER 2005에서 C언어에 SQL문을 내포시키는 환경
• 호스트 언어로 작성 중인 프로그램에 SQL문을 내포시킬 때 해당 호스트 언어의 컴파일러가 호스트 언어의 구문과 SQL문을 구별하는 방법 : 호스트 언어로 작성 중인 프로그램에 포함된 모든 SQL문에는 반드시 문장의 앞부분에 EXEC SQL을 붙인다.
• EXEC SQL은 EXEC SQL부터 세미콜론(;) 사이에 들어 있는 문장이 내포된 SQL문임을 전컴파일러에게 알린다.
• 전컴파일러(precompiler) : 코드에서 SQL문만 찾아서 번역하는 컴파일러
• ESQL/C 전컴파일러는 내포된 SQL문을 C 컴파일러에서 허용되는 함수 호출로 변환한다.
• 내포된 SQL문의 컴파일 과정

편집기 → 호스트 언어 + 내포된 SQL → 전컴파일러 → 호스트 언어 + 함수 호출문 → 호스트 언어 컴파일러

→ 오브젝트 프로그램 → 링커(DBMS가 제공하는 SQL라이브러리) → 실행 프로그램

  ESQL/C

• 정적인 SQL문과 동적인 SQL문 모드를 지원한다.
• 정적인 SQL문은 C 프로그램에 내포된 완전한 Transact-SQL문
• 동적인 SQL문은 불완전한 Transact-SQL문으로서 일부 또는 전부를 질의가 수행될 때 입력가능하다.
• 정적인 SQL문은 입력값과 출력 데이터를 위해서 C 프로그램의 변수들을 포함할 수 있다.
• 호스트 변수(Host Variable) : SQL문에 포함된 C 프로그램의 변수
• 호스트 변수를 사용할 때는 앞에 항상 콜론(:)이 붙는다.

  불일치 문제와 커서

• 호스트 언어는 단일 변수/레코드 위주의 처리(투플 위주의 방식)를 지원하는 반면에 SQL은 데이터 레코드들의 처리(집합 위주의 방식)를 지원하기 때문에 데이터 개수의 불일치 문제가 발생한다.
• 커서(Cursor) : 불일치 문제의 해결책으로, 한 번에 한 투플씩 가져오는 수단
• DECLARE CURSOR문을 사용하여 커서를 정의한다.
• OPEN cursor 문은 질의를 수행하고, 질의 수행 결과의 첫 번째 투플 이전을 커서가 가리키도록 한다. 커서가 가리키는 투플이 현재 투플이다.
• 그 다음에 FETCH문은 커서를 다음 투플로 이동하고, 그 투플의 애트리뷰트 값들을 FETCH문에 명시된 호스트 변수들에 복사한다.
• CLOSE cursor문은 커서를 닫는다.
• 정적인 커서 예시

• 만일 갱신할 투플들에 대해 커서를 정의할 때는 커서 선언부에 FOR UPDATE OF절을 반드시 포함시키고, 수정할 애트리뷰트들을 함께 명시해야 한다.

  SQL 통신 영역 (SQL Communication Area, SQLCA)

• C 프로그램에 내포된 SQL문에 발생하는 에러들을 사용자에게 알려준다.
• 사용자는 SQLCA 데이터 구조의 에러 필드와 상태 표시자를 검사하여 내포된 SQL문이 성공적으로 수행되었는지의 여부를 파악할 수 있다.
• SQLCA 데이터 구조 중에서 가장 중요하고 많이 사용되는 필드는 SQLCODE 변수이다.
• SQLCODE 의 값이 0이면 마지막에 내포된 SQL문이 성공적으로 끝났음을 의미한다.

  데이터 조작어(Data Manipulation Language, DML)

• 데이터베이스 내의 원하는 데이터를 검색, 수정, 삽입, 삭제한다. 대부분의 데이터 조작어는 SUM, COUNT, AVG와 같은 내장 함수들을 갖고 있다. 일반적으로 프로그램에 내포(코드에 포함)되어 사용된다.
• 데이터 검색 : SELECT [col1], [col2], ... FROM [tablename] [option1] [option2], ...;

ex) SELECT * FROM [tablename]; -> 전체 데이터 조회

• 데이터 수정 : UPDATE [tablename] SET [col1=new_data1], [col2=new_data2], ... WHERE [target_col]=[value];
• 데이터 삭제 : DELETE FROM [tablename] WHERE [target_col]=[value];

ex) DELETE FROM [tablename]; -> 전체 데이터 삭제

• 데이터 삽입 : INSERT INTO [tablename] ([col1], [col2], ...) VALUES ([data1], [data2], ...);

- 컬럼명을 지정하지 않을 경우, 스키마 구조의 순서에 맞게 데이터를 삽입해야 한다.

- 컬럼명을 지정할 경우, 지정한 컬럼명의 순서에 맞게 데이터를 삽입해야 한다.

* 아래의 모든 예제는 MSSQL을 기반으로 작성됩니다.

  SELECT 문

• 관계 데이터베이스에서 정보를 검색하는 SQL문
• 관계 대수의 실렉션(Selection) 연산자와는 의미가 완전히 다르다.
• 관계 대수의 실렉션(Selection), 프로젝션(Projection), 조인(Join), 카티션 곱(Cartisian Product) 등을 결합한 것
• 관계 데이터베이스에서 가장 자주 사용된다.

빨간색은 필수적인 절이고, 나머지는 선택 사항

선택 사항은 모두 포함할 필요는 없으나 순서는 매우 중요

• 별칭(Alias) : 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

[tablename1.col] FROM [tablename1] AS [alias1], [tablename2] AS [alias2];

 ex) EMPLOYEE.DNO FROM EMPLOYEE AS E, DEPARTMENT AS D;

이후 E.DNO 또는 D.DNO 로 애트리뷰트를 구분할 수 있다.

이 질의는 좋은 질의가 아닌데, 그 이유는 컬럼 추가 시 응용 프로그램 입장에서 오류가 발생할 수 있기 때문이다.

따라서 아래와 같이 조회할 컬럼을 지정하는 것이 적절하다.

* 빨간색 물결 밑줄은 스키마(ex. KIM.DEPARTMENT)를 명시하지 않아서 뜨는 겁니다.

DISTINCT 절은 중복을 없애주는데, 해당 쿼리는 성능이 저하되므로 불필요한 사용은 자제해야 한다.

WHERE 절은 조건문에 해당하며, 주로 기본 키와 함께 사용된다.

문자열 탐색 시 MSSQL 내장 함수 SUBSTR을 사용해도 상관없지만, DBMS 공용 함수인 LIKE를 사용하였다.

LIKE 뒤에 %의 위치에 따라 검색하고자 하는 문자열의 위치를 정할 수 있다.

... LIKE '김%' : 김으로 시작하는 문자열 검색

... LIKE '%김%' : 김을 포함하는 문자열 검색

... LIKE '%김' : 김으로 끝나는 문자열 검색

<>는 부정 연산자이다.

DNO IN (1, 3) : DNO=1 OR DNO=3 의 간결한 표현

AS [new_col] 절이 없을 경우 빈 컬럼명이 나타나므로, 수식 사용 시 별칭(Alias)이 필수적으로 요구된다.

  NULL 값

• 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널값이다.
• COUNT(*)을 제외한 집단 함수(AVG, MAX, MIN ..., etc)들은 널값을 무시한다.
• 어떤 애트리뷰트에 들어 있는 값이 NULL인가를 비교하려면 아래와 같은 구문을 사용해야 한다.

  ORDER BY 절

• 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬할 때 사용한다.
• 해당 절을 사용하지 않을 경우 릴레이션에 투플들이 삽입된 순서대로 보여진다.
• SELECT 문에서 가장 마지막에 사용되는 절이다.
• 디폴트 정렬 순서는 오름차순(ASC)이고, DESC를 지정하여 내림차순으로 지정할 수 있다.
• 널값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 먼저 나타난다.
• SELECT 절에서 조회할 애트리뷰트들을 사용해서 정렬해야 한다.

  집단 함수

• 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수
• 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일값을 반환한다.
• SELECT 절과 HAVING 절에만 나타날 수 있다.
• COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에 COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구한다.
• COUNT(*)를 제외하고는 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구한다.
• SQL문에서 키워드 DISTINCT가 집단 함수와 함께 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거한다.
• COUNT : 투플이나 값들의 개수
• SUM : 값들의 합
• SVG : 값들의 평균값
• MAX : 값들의 최댓값
• MIN : 값들의 최솟값

  GROUP BY 절

• 그룹화를 위해 사용된 애트리뷰트에 동일한 값을 갖는 투플들을 하나의 그룹으로 묶는다.
• 이 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 한다.
• 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성된다.
• SELECT 절에는 각 그룹마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있다.
• 아래의 질의는 그룹화를 하지 않고 EMPLOYEE 릴레이션의 모든 투플에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못된 것이다.

[ 질의 결과 ]

올바른 질의는 아래의 경우다.

     [ 질의 결과 ]

  HAVING 절

• 그룹화된 애트리뷰트에 대하여 그룹이 만족해야 하는 조건을 명시한다.
• 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용하기 사용된다.
• HAVING 절에 나타나는 애트리뷰트는 반드시 GROUP BY 절에 나타나거나 집단 함수에 포함되어야 한다.

  집합 연산

• 집한 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 한다.
• UNION : 합집합
• EXCEPT : 차집합
• INTERSECT : 교집합
• UNION ALL : 중복된 결과를 포함한 합집합
• EXCEPT ALL : 중복된 결과를 포함한 차집합
• INTERSECT ALL : 중복된 결과를 포함한 교집합

아래는 다른 예시이다.

  조인(Join)

• 조인은 두 개 이상의 릴레이션으로부터 연관된 투플들을 결합한다.
• 조인의 일반적인 형식

• 조인 조건 : 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것
• 가장 흔히 사용되는 비교 연산자 : =
• 조인 조건 생략 및 잘못된 표현을 할 경우 카티션 곱이 적용된다.
• 조인 질의가 수행되는 과정

조인 조건을 만족하는 투플 검색

↓

검색한 투플들 중 SELECT 절에 명시된 애트리뷰트들만 프로젝트

↓

DISTINCT 키워드 사용 시 중복 배제

• 조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직하다. 특히, 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 한다. 그렇지 않으면 오류 발생

-> [tablename] AS [tuple_variable]

• 결과 릴레이션의 투플 수는 외래 키의 투플 수와 동일하거나 외래 키에 널값이 있으면 나타나지 않으므로 외래 키의 투플 수보다 작을 수 있다.

EMPLOYEE의 투플 변수는 E, DEPARTMENT의 투플 변수는 D이다.

위의 릴레이션은 아래의 결과 릴레이션에서 EMPNAME과 DEPTNAME을 프로젝션한 결과이다.

• 자체 조인(Self Join)

- 한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것

- 자기 자신을 참조하는 릴레이션처럼 포함 관계나 순환 관계가 있을 때만 사용한다.

- 실제로는 한 릴레이션이 접근되지만 FROM 절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭(Alias)을 두 개 지정해야 한다.

  중첩 질의(Nested Query)

• 외부 질의의 WHERE 절에 다시 SELECT ... FROM ... WHERE 형태로 포함된 SELECT 문
• 부질의(Subquery)라고도 한다.
• INSERT, DELETE, UPDATE문에도 사용될 수 있다.
• 중첩 질의의 결과로 한 개의 단일값, 한 개의 애트리뷰트로 이루어진 릴레이션, 여러 애트리뷰트로 이루어진 릴레이션이 반환될 수 있다.
• 한 개의 단일값을 가진 결과 릴레이션은 비교 연산자의 우항으로 사용될 수 있다.
• 중첩 질의의 형식

• 한 개의 단일값이 반환되는 경우

- 위의 중첩 질의(괄호 안의 질의)로부터 ‘과장’ 이라는 단일값이 반환된다.

- 단일값을 반환하는 SELECT문의 조건(WHERE)에는 중복이 있는 “컬럼=값” 형태가 오면 안된다. 따라서 실제로 위의 질의는 부적절하다고 볼 수 있다. 박영창이라는 이름은 중복될 수 있기 때문이다.

- 조건에 중복이 있으면 안되는 이유 : 비교 연산자 사용이 불가능하기 때문이다.

• 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우

- 중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환 될 수 있다.

- 외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS 와 같은 연산자를 사용해야 한다.

위의 질의를 중첩 질의를 사용하지 않은 다음과 같은 조인 질의로 나타낼 수 있다.

실제로, 중첩 질의를 사용하여 표현된 대부분의 질의를 중첩 질의가 없는 조인 질의로 표현할 수 있다.

• 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우

- EXISTS 연산자를 사용하여 중첩 질의의 결과가 빈 릴레이션인지 여부를 검사한다.

- 중첩 질의의 결과가 빈 릴레이션이 아니면 참이 되고, 그렇지 않으면 거짓이 된다.

- 외부 질의의 대상이 되는 테이블(위의 경우 EMPLOYEE)의 총 투플 수 만큼 중첩 질의가 실행된다. 이를 상관 중첩 질의라고 한다.

• 상관 중첩 질의(Correlated Nested Query)

- 중첩 질의의 WHERE절에 있는 프레디키트(조건)에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의

- 중첩 질의의 수행 결과가 단일 값이든, 하나 이상의 애트리뷰트로 이루어진 릴레이션이든 외부 질의로 여러 번 결과를 반환한다.

- 상관 중첩 질의에서는 외부 질의를 만족하는 각 투플이 구해진 후에 중첩 질의가 수행되므로 상관 중첩 질의는 외부 질의를 만족하는 투플 수만큼 여러 번 수행될 수 있다. -> 성능 저하 야기

- 외부 질의에서 첫 번째 투플에 해당하는 사원이 속한 부서(E.DNO)의 평균 급여와 그 사원의 급여를 비교(SALARY > AVG)한다.

- 이후 외부 질의에 선언된 릴레이션의 다른 투플에 대해서도 처음과 같은 방식으로 비교 결과가 참인지 거짓인지 반환한다.

- 총 투플 수만큼 중첩 질의가 실행된다.

- 마지막으로 만족하는 투플들의 집합(=결과 릴레이션)을 외부 질의가 반환한다.

  INSERT 문

• 기존의 릴레이션에 투플을 삽입하는 데이터 조작어
• 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있다.
• 릴레이션에 한 번에 한 투플씩 삽입하는 것과 한 번에 여러 개의 투플을 삽입할 수 있는 것으로 구분한다.
• 구문

INSERT INTO [tablename](애트리뷰트1, ..., 애트리뷰트n) VALUES (값1, ..., 값n);

• (애트리뷰트)와 (값)의 순서는 동일해야 한다.
• 애트리뷰트를 제시하지 않을 경우, 릴레이션 스키마에 명시된 애트리뷰트의 순서에 맞추어 값을 삽입해야 한다.

INSERT INTO [tablename] VALUES (값1, ..., 값n);

• 릴레이션에 한 번에 한 투플씩 삽입하는 INSERT문

형광색 펜이 삐뚤삐뚤하네요.. 죄송합니다...  : (

• 릴레이션에 한 번에 여러 개의 투플을 삽입하는 INSERT문

위의 질의를 위한 문장 구조는 아래와 같다.

  DELETE 문

• 한 릴레이션으로부터 한 개 이상의 투플들을 삭제하는 데이터 조작어
• 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않는다. -> INSERT문과 반대
• 구문

DELETE FROM [tablename] WHERE [조건];

• 전체 투플 삭제 : DELETE FROM [tablename];

  UPDATE 문

• 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정하는 데이터 조작어
• 기본키나 외래키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있다.
• 구문

[ 질의 실행 전 EMPLOYEE ]

[ 질의 실행 후 EMPLOYEE ]

  트리거(Trigger)

• 명시된 이벤트(데이터베이스의 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는 쿼리문 또는 절차
• 데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구
• 트리거를 명시하려면 트리거를 활성화 시키는 사건인 이벤트(삽입, 삭제 등)와 트리거가 활성화되었을 때 수행되는 테스트인 조건, 트리거가 활성화되고 조건이 참일 때 수행되는 쿼리문(절차)인 동작을 표현해야 한다.
• 트리거를 이벤트-조건-동작(ECA) 규칙이라고도 부른다. - Event, Condition, Action
• 구문

• MSSQL에서 트리거를 정의하는 문장은 SQL3의 트리거 문장과 동일하지는 않다.
• 이벤트의 가능한 예로는 테이블에 투플 삽입, 삭제, 수정 등이 있다.
• 조건은 임의의 형태의 프레디키트
• 동작은 데이터베이스에 대한 임의의 갱신(INSERT, DELETE, UPDATE 쿼리문)
• 어떤 이벤트가 발생했을 때 조건이 참이 되면 트리거와 연관된 동작이 수행되고, 거짓이면 아무 동작도 수행되지 않는다.
• 삽입, 삭제, 수정 등이 일어나기 전에 동작하는 트리거와 일어난 후에 동작하는 트리거로 구분한다.

 이벤트 동작 조건

• 트리거는 연쇄적으로 활성화될 수 있다. 그 과정은, 하나의 트리거가 활성화되어 이 트리거 내의 한 SQL문이 수행되고 그 결과로 다른 트리거를 활성화하여 그 트리거 내의 SQL문이 수행되는 것이다.

  SQL 개요

• SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인 중 하나이다.
• SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어이다.
• 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여
• 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있다.
• MSSQL에서는 추가기능이 존재한다.
• SQL은 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없다.
• 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정(질의 처리 과정)을 담당한다.
• 자연어에 가까운 구문을 사용하여 질의를 표현할 수 있다.
• 대화식 SQL(interactive SQL)과 내포된 SQL(embedded SQL) 두 가지 인터페이스를 지원한다. 내포된 SQL이란 고급 프로그래밍 언어(C, C++, Java 등)에 내포되어 사용되는 SQL이다.

  SQL의 구성요소

• 데이터 정의어 (Data Definition Language, DDL)

• 데이터 조작어 (Data Manipulation Language, DML)
• 데이터 제어어 (Data Control Language, DCL)

  데이터 정의어와 무결성 제약조건

• 스키마의 생성과 제거 : SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약 조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원한다.

• 데이터 타입

• 릴레이션 정의

• 릴레이션 제거 : DROP TABLE TABLENAME;

ex) DROP TABLE DEPARTMENT;

• 릴레이션 스키마 변경 : ALTER TABLE TABLENAME [ADD] COLNAME NEW_DATATYPE;

ex) ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

• 인덱스 생성 : CREATE INDEX INDEXNAME ON TABLENAME(COLNAME);

ex) CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);

* 외래 키의 경우 조인 참여가 잦기 때문에 인덱스 사용률이 높은 편이다.

• 도메인 생성 : CREATE DOMAIN COLNAME DATATYPE DEFAULT ‘value’;

ex) CREATE DOMAIN DEPTNAME CHAR(10) DEFAULT ‘개발’;

• 제약 조건 예시

- 자기 자신을 참조하는 경우

FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),

-> MANAGER(외래 키) 컬럼이 EMPNO(기본 키) 컬럼을 참조한다.

- 다른 릴레이션을 참조하는 경우

FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO)

ON DELETE SET DEFAULT ON UPDATE CASCADE

-> EMPLOYEE의 DNO(외래 키) 컬럼이 DEPARTMENT의 DEPTNO(기본 키) 컬럼을 참조한다.

=> 참조되는 릴레이션(DEPARTMENT)에서 DELETE가 발생하면 삭제된 기본 키를 참조하던 외래 키(DNO)는 디폴트값(1)을 가진다.

=> 참조되는 릴레이션(DEPARTMENT)에서 UPDATE가 발생하면 갱신된 기본 키를 참조하던 외래 키(DNO)는 갱신된 값을 상속 받는다.

- 또 다른 예시 : 조건 걸기

-> 매니저의 월급이 기본 사원의 월급보다 높은 투플(레코드)만 삽입을 허용함.

• 참조 무결성 제약 조건 유지하기 위한 SQL

... ON DELETE NO ACTION

... ON DELETE CASCADE : 기본 키 삭제 시 연쇄적으로 외래 키도 삭제된다.

... ON DELETE SET NULL

... ON DELETE SET DEFAULT

... ON UPDATE NO ACTION

... ON UPDATE CASCADE : 기본키 수정 시 연쇄적으로 외래키도 수정된다.

... ON UPDATE SET NULL

... ON UPDATE SET DEFAULT

- ON INSERT 가 없는 이유 : 삽입의 경우 참조 무결성 제약조건을 위배하지 않으므로 반복적으로 확인하는 것은 오버헤드를 키우기 때문이다. (성능 저하)

- CASCADE 할 때, 외래키에는 반드시 인덱스가 있어야 한다.

• 무결성 제약조건의 추가 및 삭제

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK

PRIMARY KEY(STNO);

ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK;