3장 관계 데이터베이스 모델과 언어

정보처리기사 필기 - 1과목 데이터베이스

3장 관계 데이터베이스 모델과 언어 

013 관계형 데이터베이스의 구조

① 관계형 데이터베이스의 개요

· 관계형 데이터베이스를 구성하는 개체(Entity)나 관계(Relationship)를 모두 릴레이션(Relation)이라는 표(Table)로 표현

· 릴레이션은 개체를 표현하는 개체 릴레이션, 관계를 나타내는 관계 릴레이션으로 구분 가능

· 장점 : 간결하고 보기 편리하며, 다른 데이터베이스로의 변환이 용이

· 단점 : 성능이 다소 떨어짐

② 관계형 데이터베이스의 Relation 구조

[그림 1] 릴레이션 구조

튜플(Tuple)

· 릴레이션을 구성하는 각각의 행

· 속성의 모임으로 구성

· 파일 구조에서 레코드와 같은 의미

· 튜플의 수 : 카디널리티(Cardinality) 또는 기수, 대응수

속성(Attribute)

· 데이터베이스를 구성하는 가장 작은 논리적 단위

· 파일 구조상의 데이터 항목 또는 데이터 필드에 해당

· 개체의 특성 기술

· 속성의 수 : 디그리(Degree) 또는 차수

도메인(Domain)

· 하나의 속성이 취할 수 있는 같은 타입의 원자(Atomic)값들의 집합

· 실제 속성 값이 나타날 때 그 값의 합법 여부를 시스템이 검사하는데 이용됨

③ 릴레이션의 특징

· 한 릴레이션에는 똑같은 튜플이 포함될 수 없음 → 튜플들은 모두 상이

· 튜플 사이에 순서 없음

· 튜플들의 삽입, 삭제 등의 작업으로 인해 릴레이션은 시간에 따라 변함

· 릴레이션 스키마를 구성하는 속성들 간의 순서는 중요하지 않음

· 속성의 명칭은 유일해야 하지만, 속성을 구성하는 값은 동일해도 됨

· 튜플을 유일하게 식별하기 위해 속성들의 부분집합을 키(Key)로 설정

· 속성의 값은 논리적으로 더 이상 쪼갤 수 없는 원자값만을 저장

014 관계형 데이터베이스의 제약 조건

제약 조건이란 데이터베이스에 저장되는 데이터의 정확성을 보장하기 위하여 키를 이용하여 입력되는 데이터에 제한을 주는 것으로 개체 무결성 제약, 참조 무결성 제약 등이 해당됨

① 키(Key)의 개념 및 종류

키 : 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 튜플들을 서로 구분할 수 있는 기준이 되는 속성

후보키(Candidate Key)

· 릴레이션을 구성하는 속성들 중 튜플을 유일하게 식별하기 위해 사용하는 속성들의 부분집합, 즉 기본키로 사용할 수 있는 속성들

· 하나의 릴레이션에는 중복된 튜플들이 없으므로 모든 릴레이션에는 반드시 하나 이상의 후보키가 존재

· 후보키는 릴레이션에 있는 모든 튜플에 대해서 유일성과 최소성을 만족시켜야 함

- 유일성(Unique) : 하나의 키 값으로 하나의 튜플만을 유일하게 식별할 수 있어야 함

- 최소성(Minimality) : 모든 레코드들을 유일하게 식별하는데 꼭 필요한 속성으로만 구성되어야 함

기본키(Primary Key)

· 기본키 : 후보키 중에서 선택한 주키(Main Key)

· 한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성

· Null값을 가질 수 없음

· 기본키로 정의된 속성에는 동일한 값이 중복되어 저장될 수 없음

대체키(Alternate Key)

· 후보키가 둘 이상일 때 기본키를 제외한 나머지 후보키들

· 보조키라고도 부름

슈퍼키(Super Key)

· 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플들 중 슈퍼키로 구성된 속성의 집합과 동일한 값은 나타나지 않음

· 릴레이션을 구성하는 모든 튜플에 대해 유일성은 만족시키지만, 최소성은 만족시키지 못함

외래키(Foreign Key)

· 관계를 맺고 있는 릴레이션 R1, R2에서 릴레이션 R1이 참조하고 있는 릴레이션 R2의 기본키와 같으면 R1 릴레이션의 속성을 외래키라고 함

· 참조되는 릴레이션의 기본키와 대응되어 릴레이션 간에 참조 관계를 표현하는 중요한 도구

· 외래키로 지정되면 참조 릴레이션의 기본키에 없는 값은 입력할 수 없음

② 무결성

개체 무결성 : 릴레이션에서 기본키를 구성하는 속성은 널값이나 중복값을 가질 수 없음

참조 무결성

· 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 함. 즉 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없음

· 외래키와 참조하려는 테이블의 기본키는 도메인과 속성 개수가 같아야 함 

015 관계대수 및 관계해석

① 관계대수의 개요

· 관계형 데이터베이스에서 원하는 정보와 그 정보를 검색하기 위해서 어떻게 유도하는 가를 기술하는 절차적인 언어

· 릴레이션을 처리하기 위해 연산자와 연산 규칙을 제공하는 언어로 피연산자가 릴레이션이고, 결과도 릴레이션

· 질의에 대한 해를 구하기 위해 수행해야 할 연산의 순서를 명시

· 순수 관계 연산자 : Select, Project, Join, Division

· 일반 집합 연산자 : UNION(합집합), INTERSECTION(교집합), DIFFERENCE(차집합), CARTESIAN PRODUCT(교차곱)

② 순수 관계 연산자

순수 관계 연산자란 관계 데이터베이스에 적용할 수 있도록 특별히 개발한 관계 연산자

Select

· 릴레이션에 존재하는 튜플 중에서 선택 조건을 만족하는 튜플의 부분집합을 구하여 새로운 릴레이션 만듦

· 릴레이션의 행(가로)에 해당하는 튜플을 구하는 것이므로 수평 연산이라고도 함

· 연산자의 기호는 그리스 문자 시그마(σ) 이용

· 표기 형식 : σ<조건>(R) 단, R은 릴레이션 이름

예)  σ<ave≥90>(성적) : <성적> 릴레이션에서 평균(Ave)이 90점 이상인 튜플들을 추출

Project

· 주어진 릴레이션에서 속성 List에 제시된 속성만을 추출하는 연산

· 릴레이션의 열(세로)에 해당하는 속성을 추출하는 것이므로 수직 연산이라고도 함

· 연산자의 기호는 그리스 문자 파이(π) 이용

· 표기 형식 : π<속성리스트>(R) 단, R은 릴레이션 이름

예) πname ave(성적) : <성적> 릴레이션에서 'Name' 과 'Ave' 속성을 추출

Join

· 공통 속성을 중심으로 두 개의 릴레이션을 하나로 합쳐서 새로운 릴레이션을 만드는 연산

· 연산자의 기호는 ⋈ 이용

· 표기 형식 : R ⋈ 키속성r=키속성s S 단, 키 속성 r은 릴레이션 R의 속성이고, 키 속성 s는 릴레이션 S의 속성

예) 성적 ⋈ No=No 학적부 : <성적> 릴레이션과 <학적부> 릴레이션을 'no' 속성을 기준으로 합쳐라

Division

· X⊃Y인 두 개의 릴레이션 R(X)와 S(Y)가 있을 때, R의 속성이 S의 속성값을 모두 가진 튜플에서 S가 가진 속성을 제외한 속성만을 구하는 연산

· 연산자의 기호는 ÷ 이용

· 표기 형식 : R[속성r ÷ 속성s] S 단, 속성r은 릴레이션 R의 속성이고 속성s는 릴레이션 S의 속성이며, 속성r과 속성s는 동일 속성값을 가지는 속성이어야 함

③ 일반 집합 연산자

일반 집합 연산자는 수학적 집합 이론에서 사용하는 연산자로서 릴레이션 연산에도 그대로 적용할 수 있음 

· 일반 집합 연산자 중 합집합, 교집합, 차집합은 합병 조건이 가능해야 함

· 합병 조건 : 합병하려는 두 릴레이션 간에 속성의 수가 같고, 각 속성이 취할 수 있는 도메인의 범위가 같아야 함

· 합병 가능한 두 릴레이션 R과 S가 있을 때 각 연산의 특징을 요약하면 다음과 같음

연산자	기능 및 수학적 표현	카디널리티
합집합 UNION ∪	· 두 릴레이션에 존재하는 튜플의 합집합을 구하되, 결과로 생성된 릴레이션에서 중복된 튜플은 제거됨  · R ∪ S = {t | t ∈ R ∨ t ∈ S}  ※ t는 릴레이션 R 또는 S에 존재하는 튜플	· |R∪S| ≤ |R| + |S|  · 합집합의 카디널리티는 두 릴레이션 커디널리티의 합보다 크지 않음
교집합 INTERSECTION ∩	· 두 릴레이션에 존재하는 튜플의 교집합을 구하는 연산  · R ∩ S = {t | t ∈ R ∧ t ∈ S}  ※ t는 릴레이션 R 그리고 S에 동시에 존재하는 튜플	· |R∩S| ≤ MIN{|R|, |S|}  · 교집합의 카디널리티는 두 릴레이션 중 카디널리티가 적은 릴레이션의 카디널리티보다 크지 않음
차집합 DIFFERENCE -	· 두 릴레이션에 존재하는 튜플의 차집합을 구하는 연산  · R - S = {t | t ∈ R ∧ t ∉ S}  ※ t는 릴레이션 R에는 존재하고 S에는 없는 튜플	· |R-S| ≤ |R|  · 차집합의 카디널리티는 릴레이션 R의 카디널리티보다 크지 않음
교차곱 CARTESIAN PRODUCT ×	· 두 릴레이션에 존재하는 튜플의 순서쌍을 구하는 연산  · R × S = {r·s | r ∈ R ∧ s ∈ S}  ※ r는 R에 존재하는 튜플이고, s는 S에 존재하는 튜플	· |R×S| = |R| × |S|  · 교차곱은 두 릴레이션의 카디널리티를 곱한 것과 같음

④ 관계해석

· 관계 데이터 모델의 제안자인 코드가 수학의 Predicate Calculus에 기반을 두고 관계 데이터베이스를 위해 제안

· 관계해석은 관계 데이터의 연산을 표현하는 방법으로, 원하는 정보를 정의할 때는 계산 수식을 사용

· 관계해석은 원하는 정보가 무엇이라는 것만 정의하는 비절차적 특성을 지님

· 튜플 관계해석과 도메인 관계해석이 있음

· 기본적으로 관계해석과 관계대수는 관계 데이터베이스를 처리하는 기능과 능력 면에서 동등하며, 관계대수로 표현한 식은 관계해석으로 표현 가능

· 질의어로 표현

016 정규화(Normalization)

① 정규화의 개요

· 함수적 종속성 등의 종속성 이론을 이용해 잘못 설계된 관계형 스키마를 더 작은 속성의 세트로 쪼개어 바람직한 스키마로 만드는 과정

· 분해해가는 과정

· 정규형에는 제1정규형, 제2정규형, 제3정규형, BCNF형, 제4정규형, 제5정규형이 있으며, 차수가 높아질수록 만족시켜야 할 제약조건 늘어남

· 데이터베이스의 논리적 설계 단계에서 수행

② 정규화의 목적

· 데이터 구조의 안정성을 최대화

· 어떠한 릴레이션이라도 데이터베이스 내에서 표현 가능하도록 만듦

· 효과적인 검색 알고리즘 생성 가능

· 중복을 배제하여 삽입, 삭제, 갱신 이상의 발생을 방지

· 데이터 삽입 시 릴레이션을 재구성할 필요성 감소

③ Anomaly(이상)의 개념 및 종류

정규화를 거치지 않으면 데이터베이스 내에 데이터들이 불필요하게 중복되어 릴레이션 조작 시 예기치 못한 곤란한 현상 발생 → 이상

· 삽입 이상(Insertion Anomaly) : 릴레이션에 데이터를 삽입할 떄 의도와는 상관없이 원하지 않은 값들도 함께 삽입되는 현상

· 삭제 이상(Deletion Anomaly) : 릴레이션에서 한 튜플을 삭제할 떄 의도와는 상관없는 값들도 함께 삭제되는 연쇄 삭제 현상이 일어나는 현상

· 갱신 이상(Update Anomaly) : 릴레이션에서 튜플에 있는 속성값을 갱신할 때 일부 튜플의 정보만 갱신되어 정보에 모순이 생기는 현상

④ 정규화의 원칙

· 정보의 무손실 표현, 즉 하나의 스키마를 다른 스키마로 변환할 때 정보의 손실이 있어서는 안 됨

· 분리의 원칙, 즉 하나의 독립된 관계성은 하나의 독립된 릴레이션으로 분리시켜 표현해야 함

· 데이터의 중복성이 감소되어야 함

⑤ 정규화 과정

1NF(제1정규형)

1NF는 릴레이션에 속한 모든 도메인이 원자값으로만 되어 있는 릴레이션

· 릴레이션의 모든 속성이 단순 영역에서 정의됨

2NF(제2정규형)

2NF는 릴레이션  R이 1NF이고, 키가 아닌 모든 속성이 기본키에 대하여 완전 함수적 종속 관계를 만족함

3NF(제3정규형)

릴레이션 R이 2NF이고, 키가 아닌 모든 속성이 기본키에 대해 이행적 종속 관계를 이루지 않도록 제한한 관계형

(이행적 종속 관계 : A→B이고 B→C일 떄 A→C를 만족하는 관계)

· 무손실 조인 또는 종속성 보존을 저해하지 않고도 항상 3NF 설계를 얻을 수 있음

BCNF(Boyce-Codd 정규형)

· 릴레이션 R에서 결정자가 모두 후보키인 관계형

(결정자 : '학번'에 따라 '이름'이 결정되는 '학번 → 이름'일 때 '학번'을 결정자라하고, '이름'을 종속자라고 함)

· 3NF에서 후보키가 많고 서로 중첩되는 경우에 적용하는 강한 제3정규형이라고도 함

· 모든 BCNF가 종속성을 보장하는 것은 아님

· BCNF의 제약 조건

- 키가 아닌 모든 속성은 각 키에 대하여 완전 종속해야 함

- 키가 아닌 모든 속성은 그 자신이 부분적으로 들어가있지 않은 모든 키에 대하여 완전 종속해야 함

- 어떤 속성도 키가 아닌 속성에 대해서는 완전 종속할 수 없음

4NF(제4정규형)

릴레이션 R에 A→→B가 성립하는 경우 R의 모든 속성이 A에 함수적 종속이면 이 릴레이션 R은 제4정규형에 속함

5NF(제5정규형, PJ/NF)

릴레이션 R의 모든 조인 종속성(JD)의 만족이 R의 후보키를 통해서만 만족될 때 그 릴레이션 R은 제5정규형 또는 PJ/NF에 속함

※ 다치 종속(MVD; Multi Valued Dependency) A, B, C 세 개의 속성을 가진 릴레이션 R에서 어떤 복합 속성 (A,C)에 대응하는 B 값의 집합이 A 값에만 종속되고 C 값에는 무관할 때 다치 종속 R · A →→ R · B가 존재함 ※ 조인 종속(JD; Join Dependency) 어떤 릴레이션 R이 자신의 Projection(X,Y,…,Z)에 대한 조인의 결과가 자신과 같을 때 조인 종속(JD) (X,Y,…,Z)은 R의 속성 집합의 부분집합 ※ 정규화 과정 정리 도메인이 원자값(비정규 릴레이션 → 1NF) 부분적 함수 종속 제거(1NF → 2NF) 이행적 함수 종속 제거(2NF → 3NF) 결정자이면서 후보키가 아닌 것 제거(3NF → BCNF) 다치 종속 제거(BCNF → 4NF) 조인 종속성 이용(4NF → 5NF)

017 SQL의 개념

① SQL(Structed Query Language)의 개요

· 1974년 IBM에서 개발한 SEQUEL에서 유래

· 국제표준 데이터베이스 언어, 관계형 데이터베이스(RDB)를 지원하는 언어로 채택

· 관계대수와 관계해석을 기초로 한 혼합 데이터 언어

· 질의어지만 질의 기능만 있는 것이 아니라 데이터 구조의 정의, 데이터 조작, 데이터 제어 기능을 모두 갖추고 있음

② SQL의 분류

DDL(데이터 정의어)

· DDL(Data Definition Language)은 SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어

· 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의 

· 데이터베이스 관리자나 데이터베이스 설계자가 사용

· 데이터 정의어(DDL)의 세 가지 유형

명령어	기능
CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
ALTER	TABLE에 대한 정의를 변경하는 데 사용
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

DML(데이터 조작어)

· DML(Data Manipulation Language)은 데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용되는 언어

· 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스 제공

· 데이터 조작어(DML)의 네 가지 유형

명령어	기능
SELECT	테이블에서 조건에 맞는 튜플을 검색
INSERT	테이블에서 새로운 튜플을 삽임
DELETE	테이블에서 조건에 맞는 튜플을 삭제
UPDATE	테이블에서 조건에 맞는 튜플의 내용을 변경

DCL(데이터 제어어)

· DCL(Data Control Language)은 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용되는 언어

· 데이터베이스 관리자가 데이터 관리를 목적으로 사용

· 데이터 제어어(DCL)의 종류

명령어	기능
COMMIT	명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
GRANT	데이트베이스 사용자에게 사용 권한 부여
REVOKE	데이터베이스 사용자의 사용 권한 취소

018 DDL

· DDL(Data Define Language, 데이터 정의 언어)은 SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 명령문

· DDL로 정의된 내용은 Meta-data가 되며, 시스템 카탈로그에 저장

  (메타 데이터 : 데이터 관리를 위한, 즉 데이터를 위한 데이터

  시스템 카탈로그 : 시스템 그 자체에 관련이 있는 다양한 객체들에 관한 정보를 포함하는 시스템 데이터베이스 테이블)

※ 데이터 정의문에서 사용하는 테이블 종류

기본 테이블	이름을 갖고 있으며 독자적으로 존재
뷰 테이블	독자적으로 존재하지 못하고, 기본 테이블로부터 유도된 이름을 가진 가상 테이블
임시 테이블	질의문 처리 결과로 만들어진 테이블로서, 이름을 가지지 않음

① CREATE SCHEMA

· 스키마를 정의하는 명령문

· 스키마의 식별을 위해 스키마 이름과 소유권자나 허가권자를 정의

※ 표기 형식

CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자_id;

예) 소유권자의 사용자 ID가 홍길동인 스키마 '대학교'의 정의문 - CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

② CREATE DOMAIN

· 도메인을 정의하는 명령문

· 임의의 속성에서 취할 수 있는 원자값의 범위가 SQL에서 지원하는 data_type에 포함되는 전체 값이 아니고 일부분일 때 사용자가 그 값의 범위를 사용자 정의 data_type으로 정의

※ 표기 형식

CREATE DOMAIN 도메인_이름 data_type [DEFAULT 묵시값_정의] [CONSTRAINT VALID-도메인_이름 CHECK (범위값)];

· data_type : SQL에서 지원하는 data_type

· 묵시값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 기본값

· 정의된 도메인_이름은 일반적인 data_type처럼 사용

예) '남', '여' 또는 '?' 중의 한 문자를 취할 수 있는 도메인 SEX의 정의문

CREATE DOMAIN SEX CHAR(1) DEFAULT '여' CONSTRAINT VALID-SEX CHECK(VALUE IN('남', '여', '?'));

정의된 도메인은 이름이 'SEX'이며, 문자형이고 크기는 1자  자료형으로 SEX를 지정한 속성의 기본값 : '여'  자료형으로 SEX를 지정한 속성은 '남', '여', '?' 중 하나의 값만 취함

※ SQL에서 지원하는 기본 data_type

정수(Integer)	INT(4Byte 정수), SMALLINT(2Byte 정수)
실수(Float)	FLOAT, REAL, DOUBLE PRECISION
형식화된 숫자	DEC(i, j) 단, i : 전체 자릿수, j : 소수부 자릿수
고정길이 문자	CHAR(n) 단, n : 문자수
가변길이 문자	VARCHAR(n) 단, n : 최대 문자수
고정길이 비트열(Bit String)	BIT(n)
가변길이 비트열	VARBIT(n)
날짜	DATE, 날짜 데이터는 YYY-MM-DD의 10자리로 표기
시간	TIME, 시간 데이터는 HH : MM: SS의 6자리로 표기

③ CREATE TABLE

CREATE TABLE은 기본 테이블을 정의하는 명령문 

※ 표기 형식

CREATE TABLE 기본테이블_이름 (속성명 data_type [NOT NULL], …, PRIMARY KEY(기본키_속성명), UNIQUE(대체키_속성명, …), FOREIGN KEY(외래키_속성명, …) REFERENCES 참조테이블(기본키_속성명), CONSTRAINT 제약조건명 CHECK(조건식) );

· 속성명 : 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 data_type, NOT NULL 여부를 지정

· PRIMARY KEY : 기본키 속성 지정

· UNIQUE : 대체키로 사용할 속성명들을 지정

· FOREIGN KEY ~ REFERENCES ~

- 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정

- 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용됨

· CHECK : 제약 조건을 정의

예) 이름, 학번, 전공, 성별, 생년월일로 구성된 '학생' 테이블을 정의하라.

CREATE TABLE 학생 (이름 VARCHAR(15) NOT NULL, 학번 VARCHAR(15) NOT NULL, 전공 VARCHAR(20) NOT NULL, 성별 SEX, 생년월일 DATE, PRIMARY KEY(학번), FOREIGN KEY(전공) REFERENCES 학과(학과코드), CONSTRAINT 성별제약 CHECK(성별 = '남') );

④ CREATE INDEX

CREATE INDEX는 인덱스를 정의하는 명령문 

※ 표기 형식

CREATE  [UNIQUE] INDEX 인덱스_이름 ON 기본테이블_이름({속성_이름 [ASC | DESC],}) [CLUSTER];

· UNIQUE 옵션

- 사용하는 경우 : 기본키나 대체키 같은 중복되는 값이 없는 속성으로 인덱스를 생성할 때

- 생략하는 경우 : 중복값을 허용하는 속성으로 인덱스를 생성할 때

· 정렬 여부 지정

- ASC : 오름차순 정렬, DESC : 내림차순 정렬

- 생략하면 오름차순으로 정렬됨

· CLUSTER 옵션 : 동일 인덱스 값을 갖는 튜플들을 그룹으로 묶을 때 사용

예) '고객' 테이블의 기본키인 '고객번호' 속성에 대해 오름차순 정렬하여 '고객번호_INX'라는 이름으로 인덱스를 구성하라.

CREATE UNIQUE INDEX 고객번호_INX  ON 고객(고객번호 ASC);

⑤ ALTER TABLE

ALTER TABLE은 테이블에 대한 정의를 변경하는 명령문

※ 표기 형식

ALTER TABLE 기본테이블_이름 ADD 속성_이름 data_type [DEFAULT '기본값'];  ALTER TABLE 기본테이블_이름 ALTER 속성_이름 [SET DEFAULT '기본값'];  ALTER TABLE 기본테이블_이름 DROP 속성_이름 [CASCADE];

· ADD : 새로운 속성(열)을 추가할 때 사용

· ALTER : 특정 속성의 Default 값을 변경할 때 사용

· DROP : 특정 속성을 삭제할 때 사용

예) '학생' 테이블에 최대 3문자로 구성되는 '학년' 속성 추가

ALTER TABLE 학생 ADD 학년 VARCHAR(3);

⑥ DROP

DROP은 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스 등을 삭제하는 명령문

※ 표기 형식

DROP SCHEMA 스키마_이름[CASCADE | RESTRICTED];  DROP DOMAIN 도메인_이름[CASCADE | RESTRICTED];  DROP TABLE 테이블_이름[CASCADE | RESTRICTED];  DROP VIEW 뷰_이름[CASCADE | RESTRICTED];  DROP INDEX 인덱스_이름;

· DROP SCHEMA : 스키마 삭제

· DROP DOMAIN : 도메인 삭제

· DROP TABLE : 기본 테이블 삭제

· DROP VIEW : 뷰 테이블 삭제

· DROP INDEX : 인덱스 삭제

· CASCADE 옵션 : 삭제할 요소를 참조하는 다른 모든 개체를 함께 삭제. 즉, Main Table의 데이터 삭제 시 각 외래키에 대해 부합되는 모든 데이터를 삭제하는 참조 무결성의 법칙을 설정

· RESTRICTED 옵션 : 삭제할 요소를 다른 개체가 참조중일 때는 삭제를 취소

예) '학생' 테이블을 삭제하되, '학생' 테이블을 참조하는 모든 테이블을 함께 삭제한다.

DROP TABLE 학생 CASCADE;

019 DML - SELECT

 SELECT문은 테이블을 구성하는 튜플(행)들 중에서 전체 또는 조건을 만족하는 튜플(행)을 검색하여 주기억장치 상에 임시 테이블로 구성하는 명령문

① 일반형식

SELECT Predicate [테이블명.]속성명1, [테이블명.]속성명2, …  FROM 테이블명1, 테이블명2, …  [WHERE 조건]  [GROUP BY 속성명1, 속성명2, …]  [HAVING 조건]  [ORDER BY 속성명 [ASC | DESC]];

· SELECT절

- 속성명 : 검색하여 불러올 속성(열) 또는 속성을 이용한 수식 지정

▶ 기본 테이블을 구성하는 모든 속성을 지정할 때는 '*'를 기술

▶ 두 개 이상의 테이블을 대상으로 검색할 때는 '테이블명.속성명'으로 표현

· Predicate : 불러올 튜플 수를 제한할 명령어를 기술

※ Predicate옵션

· ALL : 모든 튜플을 검색할 떄 지정하는 것으로, 주로 생략

· DISTINCT : 중복된 튜플이 있으면 그 중 첫 번째 한 개만 검색

· DISTINCTROW : 중복된 튜플을 제거하고 한 개만 검색하지만 선택된 속성의 값이 아닌, 튜플 전체를 대상으로 함

·  FROM절 : 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술

· WHERE절 : 검색할 조건을 기술

※ 조건 연산자

·  비교 연산자 : ＝, ＜＞, ＞, ＞＝, IN

·  논리 연산자 : NOT, AND, OR

·  LIKE : 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플만 검색

· GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 그룹화할 속성을 지정

· 일반적으로 GROUP BY절은 그룹 함수와 함께 사용됨

※ 그룹 함수의 종류

· COUNT(속성명) : 그룹별 튜플 수를 구하는 함수

· MAX(속성명) : 그룹별 최대값을 구하는 함수

· MIN(속성명) : 그룹별 최소값을 구하는 함수

· SUM(속성명) : 그룹별 합계를 구하는 함수

· AVG(속성명) : 그룹별 평균을 구하는 함수

· HAVING절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정

· ORDER BY절 : 특정 속성을 기준으로 정렬하여 검색할 때 사용

- 속성명 : 정렬의 기준이 되는 속성명을 기술

- [ASC | DESC] : 정렬 방식으로서 'ASC'는 오름차순, 'DESC'는 내림차순. 생략하면 오름차순으로 지정

② 기본검색

· SELECT * FROM 사원;

· 중복 제거 : DISTINCT

③ 조건 지정 검색

SELECT *  FROM 사원  WHERE 부서 = '기획';

SELECT *  FROM 사원  WHERE 부서 = '기획' AND  주소 = '후평동';

SELECT *  FROM 사원  WHERE 부서 = '기획' OR 부서 = '인터넷';

SELECT *  FROM 사원  WHERE 이름 LIKE "김%";

SELECT *  FROM 사원  WHERE 생일 Between #01/09/69# And #10/22/73#

·  SELECT *

 FROM 사원

 WHERE 주소 IS NULL;

(NULL이 아닌 값을 검색할 떄는 IS NOT NULL 사용 → WHERE 주소 IS NOT NULL;)

④ 정렬 검색

ASC : 오름차순 / DESC : 내림차순

⑤ 그룹 지정 검색

※ GROUP BY절 : 그룹을 지정. 그룹에 대한 조건을 지정할 때는 WHERE가 아닌 HAVING 사용

   Avg(기본급) As 평균 : '기본급' 속성에 있는 값들의 평균을 구하되 '평균'이라는 속성명으로 표시

SELECT 부서, Avg(기본급) As 평균  FROM 사원  Group By 부서;

SELECT 부서, COUNT(*) As 사원수  FROM 사원  Group By 부서;

SELECT 부서, COUNT(*) As 사원수  FROM 사원  WHERE 기본급 ≥ 100  Group By 부서  HAVING COUNT(*) ≥ 2;

⑥ 하위 질의

Select 이름, 주소

From 사원

Where 이름=(Select 이름 From 여가활동 Where 취미='나이트댄스')

- 하위 질의는 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용

  "Select 이름 From 여가활동 Where 취미 = '나이트댄스'"를 수행하여 <여가활동> 테이블에서 '성춘향'을 찾음

  그런 다음 하위 질의에 해당하는 피연산자의 자리에 '성춘향'을 대입하면 질문은 "Select 이름, 주소 From 사원 Where 이름='성춘향'"과 같음

Select *

From 사원

Where 이름 Not In(Select 이름 From 여가활동)

- Not In()

  Not In()은 포함되지 않는 데이터를 의미

  <사원> 테이브레서 모든 자료를 검색하는데, <여가활동> 테이블에 이름이 있는 자료는 제외하고 검색

⑦ 복수 테이블 검색

예) 경력이 10년 이상인 사원의 이름, 부서, 취미, 경력을 검색하시오.

Select 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력

From 사원, 여가활동

Where 여가활동.경력≥10 And 사원.이름 = 여가활동.이름

⑧ 통합(UNION) 질의

※ 두 테이블을 합치는 통합(UNION) 질의는 두 테이블에 모두 속해 있는 자료는 한 개만 표시

예) 사원들의 명단이 <사원> 테이블과 <직원>테이블에 저장되어 있다. 두 테이블을 통합하는 질의문을 작성하시오. 단, 같은 레코드가 두 번 나오지 않게 하시오.

Select *

From 사원

Union

Select *

From 직원

020 DML - INSERT, DELETE, UPDATE

① 삽입문(INSERT INTO~)

삽입문은 기본 테이블에 새로운 튜플을 삽입할 때 사용

※ 일반형식

INSERT INTO 테이블명(속성명1, 속성명2, …)  VALUES (데이터1, 데이터2, …)

· 대응하는 속성과 데이터는 개수와 data_type이 일치해야함

· 기본 테이블의 모든 속성을 사용할 때는 속성명 생략 가능

· SELECT문을 사용하여 다른 테이블의 검색 결과 삽입 가능

예1) <사원> 테이블에 (이름-홍승현, 부서-인터넷)을 삽입하시오

 → INSERT INTO 사원(이름, 부서) VALUES('홍승현', '인터넷');

예2)  <사원> 테이블에 (장보고, 기획, 05/03/73, 석사동, 90)을 삽입하시오

 → INSERT INTO 사원 VALUES('장보고', '기획', #05/03/73#, '석사동', '90')

예3)  <사원> 테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블에 삽입하시오

 → INSERT INTO 편집부원(이름, 생일, 주소, 기본급)

     SELECT 이름, 생일, 주소, 기본급

     FROM 사원

     WHERE 부서 = '편집';

② 삭제문(DELETE FROM~)

삭제문은 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용

※ 일반형식

DELETE  FROM 테이블명  WHERE 조건;

· 모든 레코드를 삭제할 떄는 WHERE절을 생략

· 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP과는 다름

예1) <사원> 테이블에서 임꺽정에 대한 튜플을 삭제하시오

 → DELETE FROM 사원 WHERE 이름='임꺽정';

예2)  <사원> 테이블에서 '인터넷' 부서에 대한 모든 튜플을 삭제하시오

 → DELETE FROM 사원 WHERE 부서 = '인터넷';

예3)  <사원> 테이블의 모든 레코드를 삭제하시오

 → DELETE FROM 사원;

③ 갱신문(UPDATE~ SET~)

갱신문은 기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 사용(UPDATE~ SET~ WHERE~)

※ 일반형식

UPDATE 테이블명  SET 속성명 = 데이터  WHERE 조건;

예1) <사원> 테이블에서 홍길동의 주소를 퇴계동으로 수정하시오

 → UPDATE 사원 SET 주소 = '퇴계동' WHERE 이름 = '홍길동';

예2)  <사원> 테이블에서 황진이의 부서를 기획부로 변경하고 기본급을 5만 원 인상시키시오

 → UPDATE 사원 SET 부서 = '기획', 기본급 = 기본급 + 5 WHERE 이름 = '황진이';

※ 데이터 조작문의 네 가지 유형 · SELECT(검색) : SELECT~ FROM~ WHERE~ · INSERT(삽입) : INSERT INTO~ VALUES~ · DELETE(삭제) : DELETE FROM~ WHERE~ · UPDATE(변경) : UPDATE~ SET~ WHERE~

021 내장 SQL

① 내장 SQL(Embedded SQL)의 정의

내장 SQL은 응용 프로그램 내에 데이터베이스에서 사용하는 데이터를 정의하거나 질의하는 SQL 문장을 내포하여 프로그램이 실행될 때 함께 실행되도록 호스트 프로그램 언어로 만든 프로그램에 삽입된 SQL

② 내장 SQL의 특징

· 내장 SQL 실행문은 호스트 언어에서 실행문이 나타날 수 있는 곳이면 프로그램의 어느 곳에서나 사용 가능

· 일반 SQL문은 수행 결과로 여러 개의 튜플을 반환하는 반면, 내장 SQL은 단 하나의 튜플만을 반환

· 내장 SQL문에 의해 반환되는 튜플은 일반 변수를 사용하여 저장 가능

· Host Program의 컴파일 시 내장 SQL문은 선행처리기에 의해 분리되어 컴파일됨. 호스트 변수와 데이터베이스 필드의 이름은 같아도 됨.

· 내장 SQL문에 사용된 호스트 변수의 데이터 타입은 이에 대응하는 데이터베이스 필드의 SQL 데이터 타입과 일치하여야 함

· 내장 SQL문이 실행되면 SQL 실행의 상태가 SQL 상태 변수에 전달됨

※ SQL 상태변수 : 삽입 SQL문 실행 후 SQLCODE라는 묵시적 변수에 성공, 실패, 오류 등의 결과를 정수값으로 전달함

 0:성공 / 100:NOT FOUND / 양수:경고 / 음수:에러 (SQL2에서는 SQLSTATE라는 변수 사용 → 00000:성공 / 02000:NOT FOUND)

③ 호스트 언어의 실행문과 구분시키는 방법 

프로그램에서 호스트 실행문과 내장 SQL문을 구분하기 위한 벙법

명령문의 구분

· C/C++에서 내장 SQL문은 $와 세미콜론(;) 문자 사이에 기술

· Visual BASIC에서는 내장 SQL문 앞에 'EXEC SQL'을 기술

변수의 구분

· 내장 SQL에서 사용하는 호스트 변수는 앞에 콜론(:) 문자를 붙임

④ 커서(Cursor)

· 커서는 내장 SQL문의 수행 결과로 반환될 수 있는 복수의 튜플들을 액세스할 수 있도록 해주는 개념

· 질의 수행 결과로 반환되는 첫 번째 튜플에 대한 포인터

· 커서를 사용하여 질의 결과로 반환될 수 있는 튜플들을 한 번에 하나식 차례로 처리 가능

커서 관련 명령어

· DECLARE : 커서를 정의하는 등 커서에 관련한 선언을 하는 명령

· OPEN : 커서가 질의 결과의 첫 번째 튜플을 포인트하도록 설정하는 명령

· FETCH : 질의 결과의 튜플들 중 현재의 다음 튜플로 커서를 이동시키는 명령

· CLOSE : 질의 수행 결과에 대한 처리 종료시 커서를 닫기 위해 사용하는 명령

022 뷰(View)

① 뷰(View)의 개요

· 뷰는 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된, 이름을 가지는 가상 테이블

· 저장장치 내에 물리적으로 존재하지 않지만, 사용자에게는 있는 것처럼 간주됨

· 데이터 보정작업, 처리과정 시험 등 임시적인 작업을 위한 용도로 활용됨

· 조인문의 사용 최소화로 사용상의 편의성을 최대화함

② 뷰(View)의 특징

· 뷰는 기본 테이블로부터 유도된 테이블이기 때문에 기본 테이블과 같은 형태의 구조를 사용하며, 조작도 기본 테이블과 거의 같음

· 가상 테이블이기 때문에 물리적으로 구현되어 있지 않음

· 데이터의 논리적 독립성 제공

· 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해짐

· 뷰를 통해서만 데이터에 접근하게 되면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있음

· 기본 테이블의 기본키를 포함한 속성(열) 집합으로 뷰를 구성해야만 삽입, 삭제 갱신 연산이 가능함

· 일단 정의된 뷰는 다른 뷰의 정의에 기초가 될 수 있음

· 뷰가 정의된 기본 테이블이나 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제됨

③ 뷰(View)의 장·단점

장점

· 논리적 데이터 독립성 제공

· 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원해줌

· 사용자의 데이터 관리를 간단하게 해줌

· 접근 데아를 통한 자동 보안 제공

단점

· 독립적인 인덱스를 가질 수 없음

· ALTER VIEW문을 사용할 수 없음. 즉 뷰의 정의를 변경할 수 없음

· 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신 연산에 제약이 따름

④ 뷰(View) 정의문

※ 일반 형식

CREATE VIEW 뷰이름[(속성이름)]  AS SELECT문;

· SELECT문을 부질의로 사용하여 SELECT문의 결과로서 뷰를 생성

· 부질의로서의 SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없음

· 속성 이름을 기술하지 않으면 SELECT문의 속성 이름이 자동으로 사용됨

예) 고객 테이블에서 주소가 춘천시인 고객들의 성명과 전화번호를 '춘천고객'이라는 뷰로 정의하시오

  → CREATE VIEW 춘천고객

AS SELECT 성명, 전화번호

FROM 고객

WHERE 주소 = '춘천시';

⑤ 뷰 삭제문

뷰는 ALTER문을 사용하여 변경할 수 없으므로 필요한 경우는 삭제한 후 재생성함

※ 일반형식

DROP VIEW 뷰이름 {RESTRICT | CASCADE};

· RESTRICT : 뷰를 다른 곳에서 참조하고 있으면 삭제가 취소됨

· CASCADE : 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 삭제됨

예) 뷰 '춘천고객'을 삭제하시오. 단 다른 곳에서 참조하고 있으면 제거되지 않게 하시오

  → DROP VIEW 춘천고객 RESTRICT;

023 시스템 카탈로그

① 시스템 카탈로그(System Catalog)의 의미

· 시스템 카탈로그는 시스템 그 자체에 관련이 있는 다양한 객체에 관한 정보를 포함하는 시스템 데이터베이스

· 시스템 카탈로그 내의 각 테이블은 사용자를 포함하여 DBMS에서 지원하는 모든 데이터 객체에 대한 정의나 명세에 관한 정보를 유지 관리하는 시스템 테이블

· 데이터 정의어의 결과로 구성되는 기본 테이블, 뷰, 인덱스, 패키지, 접근 권한 등의 데이터베이스 구조 및 통계 정보를 저장

· 카탈로그들이 생성되면 자료 사전에 저장되기 때문에 좁은 의미로는 카탈로그를 자료 사전이라고도 함

· 카탈로그에 저장된 정보를 메타 데이터라고 함

② 카탈로그의 특징

· 카탈로그 자체도 시스템 테이블로 구성되어 있어 일반 이용자도 SQL을 이용하여 내용 검색 가능

· INSERT, DELETE, UPDATE문으로 카탈로그를 갱신하는 것은 허용되지 않음

· 데이터베이스 시스템에 따라 상이한 구조를 가짐

· 카탈로그는 DBMS가 스스로 생성하고 유지함

· 카탈로그의 갱신 : 사용자가 SQL문을 실행시켜 기본 테이블, 뷰, 인덱스 등에 변화를 주면 시스템이 자동으로 갱신함

· 분산 시스템에서의 카탈로그 ; 보통의 릴레이션, 인덱스, 사용자 등의 정보를 포함할 뿐 아니라 위치 투명성 및 중복 투명성을 제공하기 위해 필요한 모든 제어 정보를 가져야 함

③ 시스템 카탈로그의 종류

· SYSTABLES : 기본 테이블 및 뷰 테이블의 정보를 저장하는 테이블

· SYSCOLUMNS : 모든 테이블에 대한 정보를 열(속성) 중심으로 저장하는 테이블

· SYSINDEXES : 인덱스 테이블에 대한 정보를 저장하는 테이블

· SYSVIEW : 뷰에 대한 정보를 저장하는 테이블

· SYSTABAUTH : 테이블에 설정된 권한 사항들을 저장하는 테이블

· SYSCOLAUTH : 각 속성에 설정된 권한 사항들을 저장하는 테이블

· SYSDEPEND : 기본 테이블과 뷰 사이의 종속 관계를 저장하는 테이블

· SYSUSERS : 사용자의 권한 등급 정보를 저장하는 테이블

④ 카탈로그/데이터 사전을 참조하기 위한 DBMS 내의 모듈 시스템

· 데이터 정의어 번역기(DDL Compiler) : DDL을 메타 데이터를 갖는 테이블(카탈로그)로 변환하여 데이터 사전에 저장시킴

· 데이터 조작어 번역기(DML Compiler) : 응용 프로그램에 삽입된 DML문을 주 언어로 표현한 프로시저 호출로 변환하여 질의 처리기와 상호 통신함

· Data Directory

- 데이터 사전에 수록된 데이터를 실제로 접근하는 데 필요한 정보를 관리 유지하는 시스템

- 시스템 카탈로그는 사용자와 시스템 모두 접근할 수 있지만 데이터 디렉터리는 시스템만 접근 가능

· 질의 최적화기 : 사용자의 요구를 효율적인 형태로 변환하고 질의를 처리하는 좋은 전략을 모색

· 트랜잭션 처리기 : 복수 사용자 환경에서 평행으로 동시에 일어나는 트랜잭션 문제를 해결하여, 각각의 사용자가 데이터베이스 자원을 배타적으로 이용할 수 있도록 함

출처 : 2017 시나공 정보처리기사 필기