[정보처리기사] - [3과목] 2023 (01)

[제 3과목 데이터베이스 구축]

- 2023년도 1회차 (01)

CREATE TABLE

▶ 개념

: 테이블을 정의하는 명령문이다.

 

▶ 표기 형식

CREATE TABLE 테이블명 ( 속성명 데이터_타입 [ DEFAULT 기본값 ] [ NOT NULL ],
...
[ , PRIMARY KEY ( 기본키_속성명,  ...) ]
[ , UNIQUE (대체키_속성명, ...) ]
[ , FOREIGN KEY (외래키_속성명, ...) ]
[ REFERENCES 참조테이블 (기본키_속성명, ...) ]
[ ON DELETE 옵션 ]
[ ON UPDATE 옵션 ]
[ . CONSTRAINT 제약조견명 ] [ CHECK (조건식) ] );

: 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정한다.

 

▶ 포함되는 기능

1. 속성 타입 변경 불가

2. 속성의 NOT NULL 여부 지정

3. 기본키를 구성하는 속성 지정

4. CHECK 제약조건의 정의

 

▷ PRIMARY KEY

: 기본키로 사용할 속성 또는 속성의 집합을 지정한다.

 

▷ UNIQUE

: 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것으로 UNIQUE로 지정한 속성은 중복된 값을 가질 수 없다.

 

DROP 옵션

1. CASCADE

: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다. 즉 주 테이블의 데이터 제거 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용된다.

2. RESTRICTED 

: 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소한다.

 

관계 해석 관련 기호

기호	구성요소	설명
∨	OR 연산	두 식을 '또는' 이라는 관계로 연결
∧	AND 연산	두 식을 '그리고' 라는 관계로 연결
ㄱ	NOT 연산	식에 대한 부정
∀	전칭 정량자	가능한 모든 튜플에 대하여 (='For All)
∃	존재 전량자	하나라도 일치하는 튜플이 있음

 

개념적, 논리적, 물리적 설계

▶ 개념적 설계 (정보 모델링, 개념화) 개념

: 정보의 구조를 얻기 위하여 현실 세계의 무한성과 계속성을 이해하고, 다른 사람과 통신하기 위하여 현실 세계에 대한 인식을 추상적 개념으로 표현하는 과정이다.

: 개념적 설계 단계에서는 개념 스키마 모델링과 트랜잭션 모델링을 병행 수행한다.

: 개념적 설계 단계에서는 요구 분석 단계에서 나온 결과인 요구 조건 명세를 DBMS에 독립적인 E-R 다이어그램으로 작성한다.

→ 산출물로 E-R Diagram을 만들 수 있다.

: DBMS에 독립적인 개념 스키마를 설계한다.

: 논리적 설계 단계의 앞 단계에서 수행된다.

 

▶ 논리적 설계 (데이터 모델링)

: 현실 세계에서 발생하는 자료를 컴퓨터가 이해하고 처리할 수 있는 물리적 저장장치에 저장할 수 있도록 변환하기 위해 특정 DBMS가 지원하는 논리적 자료 구조로 변환시키는 과정이다.

: 특정 DBMS가 지원하는 논리적 자료 구조로 변환시키는 과정이다.

: 개념 세계의 데이터를 필드로 기술된 데이터 타입과 이 데이터 타입들 간의 관계로 표현되는 논리적 구조의 데이터로 모델화한다.

: 개념적 설계가 개념 스키마를 설계하는 단계라면 논리적 설계에서는 개념 스키마를 평가 및 정제하고 DBMS에 따라 서로 다른 논리적 스키마를 설계하는 단계이다. 

: 트랜잭션의 인터페이스를 설계한다.

: 관계형 데이터베이스라면 테이블을 설계하는 단계이다.

 

▶ 물리적 설계 (데이터 구조화)

: 논리적 설계 단계에서 논리적 구조로 표현된 데이터를 디스크 등의 물리적 저장장치에 저장할 수 있는 물리적 구조의 데이터로 변환하는 과정이다.

: 물리적 설계 단계에서는 다양한 데이터베이스 응용에 대해 처리 성능을 얻기 위해 데이터베이스 파일의 저장 구조 및 액세스 경로를 결정한다.

: 저장 레코드의 형식, 순서, 접근 경로와 같은 정보를 사용하여 데이터가 컴퓨터에 저장되는 방법을 묘사한다.

 

집합 연산자

▶ 종류

집합 연산자	설명	집합 종류
UNION	- 두 SELECT문의 조회 결과를 통합하여 모두 출력한다. - 중복된 행은 한 번만 출력한다	합집합
UNION ALL	- 두 SELECT 문의 조회 결과를 통합하여 모두 출력한다. - 중복된 행도 그대로 출력한다.	합집합
INTERSECT	- 두 SELECT 문의 조회 결과 중 공통된 행만 출력한다.	교집합
EXCEPT	- 첫 번째 SELECT 문의 조회 결과에서 두 번째 SELECT 문의 조회 결과를 제외한 행을 출력한다.	차집합

 

정규화 과정

[ 비정규 릴레이션 ]

↓  : 도메인이 원자값

[ 1NF ]

↓  :  부분적 함수 종속 제거

[ 2NF ]

↓  : 이행적 함수 종속 제거

[ 3NF ]

↓  : 결정자이면서 후보키가 아닌 것을 제거

[ BCNF ]

↓  : 다치 종속 제거

[ 4NF ]

↓  : 조인 종속성 이용

[ 5NF ]

 

◎ 질문 예시

Q. 제 3 정규형( 3NF )에서 BCNF 가 되기 위한 조건은?

1. 결정자가 후보키가 아닌 함수 종속 제거 (3NF → BCNF)

2. 이행적 함수 종속 제거 (2NF  →  3NF)

3. 부분적 함수 종속 제거 (1NF  →  2NF)

4. 원자값이 아닌 도메인 분해 (비정규 릴레이션 → 1NF)

 

※ 정규화 과정 및 조건 순서 암기

 

관계 데이터 모델의 무결성 제약 조건

▶ 종류

1. 개체 무결성

: 릴레이션에서 기본키를 구성하는 속성은 널(NULL) 값이나 중복 값을 가질 수 없다.

2. 참조 무결성

: 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 한다.

: 릴레이션은 참조할 수 없는 외래 키 값을 가질 수 없다.

3. 널 무결성

: 릴레이션의 특정 속성 값이 NULL이 될 수 없도록 하는 규정

4. 고유 무결성

: 릴레이션의 특정 속성에 대해서 각 튜플이 갖는 값들이 서로 달라야 한다는 규정

5. 도메인 무결성

: 특정 속성의 값이 그 속성이 정의된 도메인에 속한 값이어야 한다는 규정

6. 키 무결성

: 하나의 릴레이션에는 적어도 하나의 키가 존재해야 한다는 규정

7. 관계 무결성

: 릴레이션에 어느 한 튜플의 삽입 가능 여부 또는 한 릴레이션과 다른 릴레이션의 튜플들 사이의 관계에 대한 적절성 여부를 지정한 규정

 

로킹 단위 (Locking Granularity)

: 로킹 단위는 병행제어에서 한꺼번에 로킹할 수 있는 객체의 크기를 의미한다.

→ 한꺼번에 로킹할 수 있는 단위를 로킹 단위라고 한다.

: 데이터베이스, 파일, 레코드, 필드 등은 로킹 단위가 될 수 있다.

: 로킹 단위가 크면 로크 수가 작아 관리하기 쉽지만 병행성 수준이 낮아지고 데이터베이스 공유도가 저하된다.

: 로킹 단위가 작으면 로크 수가 많아 관리하기 복잡해 오버헤드가 증가하지만 병행성 수준이 높아지고, 데이터베이스 공유도가 증가한다.

→ 로킹 단위가 작아지면 로킹 오버헤드가 증가한다.

 

트랜잭션의 상태

1. 활동 (Actice)

: 트랜잭션이 실행 중인 상태

2. 실패 (Failed)

: 트랜잭션 실행 중 오류가 발생하여 중단된 상태

3. 철회 (Aborted)

: 트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태

4. 부분 완료 (Partially Committed)

: 트랜잭션의 마지막 연산까지 완료했지만, Commit 연산이 실행되기 직전의 상태

→ 트랜잭션의 상태 중 트랜잭션의 마지막 연산이 실행된 직후의 상태로, 모든 연산의 처리는 끝났지만 트랜잭션이 수행한 최종 결과를 데이터베이스에 반영하지 않은 상태

5. 완료 (Committed)

: 트랜잭션이 성공적으로 종료되어 Commit 연산까지 수행한 상태

 

삭제문 (DELETE FROM ~)

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

※ DROP : 테이블 삭제 / DELETE : 레코드 삭제

: 테이블의 행을 삭제할 때 사용한다.

: SQL을 사용 용도에 따라 분류할 경우 DML에 해당한다.

 

▶ 기본 사용 형식

DELETE
FROM 테이블명
[WHERE 조건] :

 

▶ 형식

: 모든 레코드를 삭제할 때는 WHERE 절을 생략한다.

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

 

관계 대수식 

π 이름 ( ∂학과 = '교육' (학생) )

 

▶ SQL 질의로 표현한 것

: SELECT 이름 FROM 학생 WHERE 학과 = '교육';

 

- π 이름 : '이름' 필드를 표시

- ∂학과 = '교육' : '학과'가 "교육"인 자료만을 대상으로 한다.

- (학생) : <학생> 테이블의 자료를 검색한다.

≫ 교육과 학생의 '이름'을 검색하는 식

 

분산 데이터베이스

▶ 개념

: 논리적으로는 같은 시스템에 속하지만 물리적으로는 컴퓨터 네트워크를 통해 분산되어 있는 데이터베이스

 

▶ 목표

1. 위치 투명성 : 접근하려는 데이터베이스의 실제 위치를 알 필요 없이 단지 데이터베이스의 논리적인 명칭만으로 접근할 수 있다.

2. 중복 투명성 : 동일한 데이터가 여러 곳에 중복되어 있더라도 사용자는 마치 하나의 데이터만 존재하는 것처럼 사용할 수 있고, 시스템은 자동으로 여러 데이터에 대한 작업을 수행한다.

3. 병행 투명성 : 분산 데이터베이스와 관련된 다수의 트랜잭션들이 동시에 실행되더라도 그 트랜잭션들의 수행 결과는 서로 영향을 받지 않는다.

4. 장애 투명성 : 트랜잭션, DBMS, 네트워크, 컴퓨터 장애에도 불구하고 트랜잭션은 정확하게 수행된다.

 

▶ 구성 요소

1. 분산 처리기 : 자체적으로 처리 능력을 가지며, 지리적으로 분산되어 있는 컴퓨터 시스템

2. 분산 데이터베이스 : 지리적으로 분산되어 있는 데이터베이스로서 해당 지역의 특성에 맞게 데이터베이스가 구성

3. 통신 네트워크 : 분산 처리기들을 통신망으로 연결하여 논리적으로 하나의 시스템처럼 작동할 수 있도록 하는 통신 네트워크를 의미

 

▶ 장점

: 지역 자치성 ↑

: 자료의 공유성 향상

: 분산 제어 가능

: 시스템 성능 향상

: 중앙 컴퓨터의 장애가 전체 시스템에 영향을 끼치지 않음

: 효용성과 융통성이 높음

: 신뢰성 및 가용성이 높음

: 점진적 시스템 용량 확장이 용이함

 

▶ 단점

: 분산 처리기들을 통신망으로 연결하여 논리적으로 하나의 시스템처럼 작동할 수 있도록 하는 통신 네트워크를 의미

 

릴레이션, 튜플, 속성

▶ 릴레이션 (Relation)

: 데이터들을 표(Table)의 형태로 표현한 것으로 구조를 나타내는 릴레이션 스키마와 실제 값들인 릴레이션 인스턴스로 구성된다,.

 

▶ 튜플 (Tuple)

: 튜플은 릴레이션을 구성하는 각각의 행을 말한다.

: 튜플은 속성의 모임으로 구성된다.

: 파일 구조에서 레코드와 같은 의미이다.

: 튜플의 수를 카디널리티 또는 기수, 대응수라고 한다.

 

▶ 속성 (Attribute)

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

: 파일 구조상의 데이터 항목 또는 데이터 필드에 해당된다.

: 개체의 특성을 기술한다.

: 속성의 수를 디그리(Degree) 또는 차수라고 한다.

 

데이터 정의어 ( DDL )

: CREATE, ALTER, DROP

 

데이터 조작어 ( DML )

:  SELECT, INSESRT, UPDATE, DELETE

 

데이터 제어어 ( DCL )

: COMMIT, ROLLBACK, GRANT, REVOKE

 

이상 (Anomaly)

: 데이터의 중복으로 인하여 관계 연산을 처리할 때 예기치 못한 곤란한 현상이 발생하는 것

 

▶ 종류 

1. 삽입 이상 (Insertion Anomaly)

: 릴레이션에 데이터를 삽입할 때 의도와는 상관없이 원하지 않는 값들도 함께 삽입되는 현상

 

2. 삭제 이상 (Deletion Anomaly)

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

 

3. 갱신 이상 (Update Anomaly)

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

 

릴레이션

▶ 특징

: 한 릴레이션에 포함된 튜플들은 모두 상이하다.

→ 모든 튜플은 서로 다른 값을 갖는다.

ex. <학생> 릴레이션을 구성하는 홍길동 레코드는 홍길동에 대한 학적사항을 나타내는 것으로 <학생> 릴레이션 내에서는 유일하다.

: 한 릴레이션에 포함된 튜플 사이에는 순서가 없다.

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

ex. <학생> 릴레이션에 새로운 학생의 레코드를 삽입하거나, 기존 학생에 대한 레코드를 삭제함으로써 테이블은 내용 면에서나 크기 면에서 변하게 된다.

: 속성의 유일한 식별을 위해 속성의 명치은 유일해야 하지만, 속성을 구성하는 값은 동일한 값이 있을 수 있다.

→ 각 속성은 릴레이션 내에서 유일한 이름을 가진다.

ex. 각 학생의 학년을 기술하는 속성인 '학년'은 다른 속성명들과 구분되어 유일해야 하지만 '학년' 속성에는 2, 1, 2, 4 등이 입력된 것처럼 동일한 값이 있을 수 있다.

: 속성은 더 이상 쪼갤 수 없는 원자 값만을 저장한다.

→ 모든 속성 값은 원자 값이다.

ex. '학년'에 저장된 1, 2, 4 등은 더 이상 세분화할 수 없다.

: 릴레이션을 구성하는 튜플을 유일하게 식별하기 위해 속성들의 부분집합을 키(Key) 로 설정한다.

 

뷰 (View)

 : 뷰는 기본 테이블로부터 유도된 테이블

: 기본 테이블과 같은 형태의 구조 사용 / 조작도 깁노 테이블과 거의 유사

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

: 데이터의 논리적 독립성이 어느 정도 제공된다.

→ 논리적 독립성을 제공한다.

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

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

: 하나의 뷰를 삭제하면 그 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제된다.

→ 뷰가 정의된 기본 테이블이 삭제되면 뷰는 자동적으로 삭제된다.

: 뷰는 독자적인 인덱스를 가질 수 없다.

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

→ 뷰로 구성된 내용에 대한 삽입, 갱신, 삭제 연산에는 제약이 따른다.