[정리] 데이터베이스 개론과 실습

---

이 포스팅은 2015년 10월 18일에 작성된 예전 블로그 글을 새로이 옮긴 것입니다. 참고바랍니다.

---

 

참고도서
박우창 외 2인. 『오라클로 배우는 데이터베이스 개론과 실습』. 한빛아카데미. 2014

 

데이터베이스 시스템의 개념

 

1장. 데이터베이스 시스템

 

1. 데이터베이스

• 데이터: 관찰의 결과로 나타난 정량적 혹은 정성적인 실제 값을 말한다.
• 데이터베이스: 조직에 필요한 정보를 얻기 위하여 논리적으로 연관된 데이터를 모아 구조적으로 통합해 놓은 것이다.

 

2. 데이터베이스의 개념

데이터에 대한 통합된 데이터(integrated data), 저장된 데이터(stored data), 운영 데이터(operational data), 공용 데이터(shared data)를 말한다.

 

3. 데이터베이스의 특징

실시간 접근성(real time accessibility), 계속적인 변화(continuous change), 동시 공유(concurrent sharing), 내용에 따른 참조(reference by content) 등이 있다.

 

4. 데이터베이스 시스템의 구성

• 데이터베이스 관리 시스템(DBMS): 사용자와 데이터베이스를 연결시켜주는 소프트웨어.
• 데이터베이스: 데이터를 모아둔 토대.
• 데이터 모델: 데이터가 저장되는 기법에 관한 내용.

 

5. 정보 시스템의 발전

1. 파일 시스템: DBMS가 없는 시스템으로, 데이터는 파일 단위로 저장되며 파일을 다루는 파일 서버가 있다.
2. 데이터베이스 시스템: DBMS를 도입하여 데이터를 통합 관리하는 시스템이다.
3. 웹 데이터베이스 시스템: 데이터베이스를 웹 브라우저에서 사용하도록 제공하는 시스템이다. 웹 서버와 JSP, PHP, 웹 데이터베이스 연동 언어들을 사용한다.
4. 분산 데이터베이스 시스템: 여러 곳에 분산된 DBMS 서버를 연결하여 운영하는 시스템으로 대규모 응용 시스템에 사용된다.

 

6. DBMS의 장점

데이터를 공유하여 중복 가능성을 최소화하고 이를 통해 데이터의 일관성을 유지한다. 또 데이터 구조가 변경되더라도 프로그램을 수정할 필요가 없어 데이터 독립성을 유지할 수 있다.

 

7. SQL

데이터베이스 시스템에 사용하는 전용 언어로 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL)로 구성된다.

• 데이터 정의어: SQL의 CREATE, ALTER, DROP 문과 같이 DBMS에 저장된 테이블의 구조를 정의한다.
• 데이터 조작어: SQL의 SELECT, INSERT, DELETE, UPDATE 문과 같이 데이터를 삽입, 삭제, 수정하는데 사용한다.
• 데이터 제어어: SQL의 GRANT, REVOKE 문과 같이 데이터의 사용 권한을 관리한다.

 

8. DBMS의 기능

• 데이터 정의(Definition): 데이터의 구조를 정의하고 데이터 구조에 대한 삭제 및 변경 기능을 수행함.
• 데이터 조작(Manipulation): 데이터를 조작하는 소프트웨어(응용 프로그램)가 요청하는 데이터의 삽입, 수정, 삭제, 작업을 지원함.
• 데이터 추출(Retrieval): 사용자가 조회하는 데이터 혹은 응용 프로그램의 데이터를 추출함.
• 데이터 제어(Control): 데이터베이스 사용자를 생성하고 모니터링하며 접근을 제어함.

 

9. 데이터 모델

데이터베이스 시스템에서 데이터를 저장하는 이론적인 방법에 관한 것으로, 데이터베이스에 데이터가 어떻게 구조화되어 저장되는지를 결정한다.

 

10. 3단계 데이터베이스 구조

외부 단계, 개념 단계, 내부 단계로 나누어지며 각 단계는 외부 스키마, 개념 스키마, 내부 스키마로 구성된다. 스키마(schema)는 그리스어에서 유래된 단어로 데이터베이스의 조직이나 구조를 의미한다.

• 외부 스키마: 서브 스키마(sub schema)라고도 하며, 뷰(view)의 개념이다. 개념 스키마 중 사용자에게 필요한 부분 스키마를 의미한다.
• 개념 스키마: 전체 데이터베이스의 정의를 말하는 것으로 통합 조직 별로 하나만 존재한다. 저장 장치에 독립적으로 기술되며, 데이터와 관계(relationship), 제약사항, 무결성에 대한 내용이 포함된다.
• 내부 스키마: 물리적 저장 장치에서 데이터베이스가 실제로 저장되는 방법의 표현이다. 인덱스, 데이터 레코드의 배치 방법, 데이터 압축 등에 관한 사항이 포함된다.

 

11. 데이터 독립성

3단계 데이터베이스 구조에서 하위 단계의 내용을 추상화하여 상위 단계에 그 세부 사항을 숨김으로써 한 단계 내의 변경에 대해서 다른 단계와 상호 간섭이 없도록 하는 것이다.

• 논리적 데이터 독립성: 외부 단계와 개념 단계 사이의 독립성으로, 개념 스키마가 변경되어도 외부 스키마에는 영향을 미치지 않도록 지원한다.
• 물리적 데이터 독립성: 개념 단계와 내부 단계 사이의 독립성으로, 저장 장치 구조 변경과 같이 내부 스키마가 변경되어도 개념 스키마에 영향을 미치지 않도록 지원한다.

 

2장. 관계 데이터 모델

 

1. 릴레이션

관계 데이터 모델의 핵심적인 개념으로 행과 열로 구성된 테이블을 말한다.

 

2. 릴레이션 스키마

관계 데이터베이스의 릴레이션이 어떻게 구성되는지 어떤 정보를 담고 있는지에 대한 기본적인 구조를 정의한다.

• 속성(attribute): 릴레이션 스키마의 열
• 도메인(domain): 속성이 가질 수 있는 값의 집합
• 차수(degree): 속성의 개수

 

3. 릴레이션 인스턴스

릴레이션 스키마에 실제로 저장되는 데이터의 집합을 말한다.

• 투플(tuple): 릴레이션의 행
• 카디날리티(cardinality): 투플의 수

 

4. 관계 데이터베이스 시스템

관계 데이터 모델을 컴퓨터 시스템에 구현한 것이다.

 

5. 키

• 슈퍼키: 투플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합이다.
• 후보키: 투플을 유일하게 식별할 수 있는 하나의 속성 혹은 속성의 집합으로, 속성의 최소 집합이다.
• 기본키: 여러 후보키 중 하나를 선정하여 대표로 삼는 키를 말한다.
• 대리키: 일련번호 같은 가상의 속성을 만들어 기본키로 삼는 것을 말한다.
• 외래키: 다른 릴레이션의 기본키를 참조하는 속성을 말한다.

 

6. 릴레이션의 특징

• 속성은 단일 값을 가진다.
• 속성은 서로 다른 이름을 가진다.
• 한 속성의 값은 모두 같은 도메인 값을 가진다.
• 속성의 순서는 상관없다.
• 릴레이션 내의 중복된 투플은 허용하지 않는다.
• 투플의 순서는 상관없다.

 

7. 무결성 제약조건

• 도메인 무결성 제약조건: 모든 투플은 릴레이션 스키마에 정의된 각 속성의 도메인에 지정된 값만을 가져야 한다.
• 개체 무결성 제약조건: 기본키는 NULL 값을 가져서는 안 되며, 릴레이션 내에 오직 하나의 값만 존재해야 한다.
• 참조 무결성 제약조건: 자식 릴레이션의 외래키는 부모 릴레이션의 기본키와 속성의 도메인이 동일해야 하며, 자식 릴레이션의 값 변경 시 부모 릴레이션의 값에 제약을 받는다.

 

8. 참조 무결성 제약조건의 옵션

• RESTRICTED: 자식 릴레이션에서 참조하고 있을 경우 부모 릴레이션의 삭제 작업을 거부함.
• CASCADE: 자식 릴레이션의 관련 투플을 같이 삭제함.
• DEFAULT: 자식 릴레이션의 관련 투플을 미리 설정해둔 값으로 변경함.
• NULL: 자식 릴레이션의 관련 투플을 NULL 값으로 설정함(NULL 값을 허가한 경우).

 

9. 관계대수

릴레이션에서 원하는 결과를 얻기 위해 수학의 대수와 같은 연산을 이용하여 질의하는 방법을 기술하는 언어이다.

 

10. 셀렉션

릴레이션의 투플을 추출하기 위한 연산으로 기호는 ‘σ(sigma)’를 사용한다.

 

11. 프로젝션

릴레이션의 속성을 추출하기 위한 연산으로 기호는 ‘π(pi)’를 사용한다.

 

12. 집합연산

수학의 집합연산과 동일한 연산으로 합집합, 교집합, 차집합, 카티전 프로덕트 등이 있다.

 

13. 조인

두 릴레이션의 공통 속성을 기준으로 속성 값이 같은 투플을 수평으로 결합하는 연산이다.

• 세타조인: 두 릴레이션의 속성 값을 비교해서 조건(=, ≠, ≤, ≥, <, >)에 만족하는 투플을 반환한다.
• 동등조인: 세타조인에서 = 연산자를 사용한 조인을 말한다.
• 자연조인: 조인에 참여한 속성이 두 번 나오지 않도록 중복된 속성을 제거한 결과를 반환한다.
• 세미조인: 자연조인을 한 후 두 릴레이션 중 한쪽 릴레이션의 결과만 반환한다.
• 외부조인: 자연조인 시 조인에 실패한 투플을 모두 보여주되, 값이 없는 대응 속성은 NULL 값을 채워서 반환한다.

 

14. 디비전

속성 A와 B로 이루어진 릴레이션 R과 속성 B로 이루어진 릴레이션 S의 디비전 연산은, 릴레이션 S의 속성 B 값과 동일하게 대응하는 릴레이션 R의 속성 A의 투플들을 반환한다.

---

 

데이터베이스 프로그래머

 

3장. SQL 기초

 

1. SQL

SQL(Structured Query Language)은 1970년대 후반 IBM이 SEQUEL(Structured English QUEry Language)이라는 이름으로 개발한 관계형 데이터베이스 언어이다.

 

2. 데이터 정의어(DDL)

테이블이나 관계의 구조를 생성하는 데 사용하여 CREATE, ALTER, DROP 문 등이 있다.

CREATE TABLE 테이블이름
    ( {속성이름 데이터타입
       [NULL | NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
      }
      [PRIMARY KEY 속성이름(들)]
      {[FOREIGN KEY 속성이름 REFERENCES 테이블이름(속성이름)]
       [ON DELETE {CASCADE | SET NULL}
      }
    (
ALTER TABLE 테이블이름
    [ADD 속성이름 데이터타입]
    [DROP COLUMN 속성이름]
    [MODIFY 속성이름 데이터타입]
    [MODIFY 속성이름 데이터타입 [NULL | NOT NULL]
    [ADD PRIMARY KEY(속성이름)]
    [[ADD | DROP] 제약이름]
DROP TABLE 테이블이름

 

3. 데이터 조작어(DML)

테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용하여 SELECT, INSERT, DELETE, UPDATE 문 등이 있다. 여기서 SELECT 문은 데이터를 조회하는 명령어라 하여 특별히 질의어(query)라고 한다.

SELECT
    [ALL | DISTINCT]
    [테이블이름.]{ * | 속성이름 [[AS] 속성이름별칭]}
[FROM
    {테이블이름 [AS 테이블이름별칭]}
    [INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN
    {테이블 이름 [ON 검색조건]}
    | FULL [OUTER] JOIN {테이블이름}]]
[WHERE 검색조건(들)]
[GROUP BY {속성이름, [ …, n]}]
[HAVING 검색조건(들)]
[질의 UNION 질의 | 질의 UNION ALL 질의]
[ORDER BY {속성이름 [ASC | DESC], { …, n}}]
INSERT INTO 테이블이름[(속성리스트)]
    VALUES (값리스트);
UPDATE 테이블이름
SET 속성이름1 = 값1[, 속성이름2 = 값2, …]
[WHERE <검색조건>];
DELETE FROM 테이블이름
[WHERE 검색조건];

 

4. WHERE 조건

WHERE 절 다음에 나올 수 있는 조건으로 사용할 수 있는 술어(predicate)는 비교, 범위, 집합, 패턴, NULL 등이 있다.

 

5. 집계 함수

테이블의 각 열에 대해 계산을 하는 함수로 SUM, AVG, MIN, MAX, COUNT의 다섯 가지가 있다.

 

6. GROUP BY

속성의 공통 값에 따라 그룹을 만드는 데 사용하는 명령이다.

 

7. HAVING

GROUP BY 절의 결과 나타나는 그룹을 제한하는 역할을 한다.

 

8. 조인

한 테이블의 행을 다른 테이블의 행에 연결함으로써 두 개 이상의 테이블을 결합하는 연산이다.

 

9. 동등조인(내부조인)

동등조건에 의하여 테이블을 조인하는 것을 동등조인(equi join)이라고 한다. 조인이라고 하면 대부분 동등조인을 말한다. 동등조인은 내부조인(inner join)이라고도 한다.

 

10. 부속질의

SELECT 문의 WHERE 절에 또 다른 테이블 결과를 이용하기 위해 다시 SELECT 문을 괄호로 묶는 것을 부속질의(subquery)라고 한다. 부속질의는 질의가 중첩되어 있다는 의미에서 중첩질의(nested query)라고도 한다.

 

11. 상관 부속질의

상관 부속질의(correlated subquery)는 상위 부속질의와 하의 부속질의가 독립적이지 않고 상위 부속질의의 투플을 이용하여 하위 부속질의를 계산하는 질의를 말한다.

 

12. 투플 변수

FROM 절의 테이블 이름 뒤에 테이블의 다른 이름을 붙여주는 것은 투플 변수라고 한다.

 

13. 집합연산

SQL에서 집합연산은 합집합을 UNION, 차집합을 MINUS, 교집합을 INTERSECT로 나타낸다.

 

14. EXISTS

EXISTS는 부속질의문의 어떤 행이 조건에 만족하면 참이다. 반면 NOT EXISTS는 부속질의문의 모든 행이 조건에 만족하지 않을 때만 참이다.

 

15. CREATE

테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본키 및 외래키를 정의한다.

 

16. ALTER

생성된 테이블의 속성과 속성에 관한 제약 그리고 기본키 및 외래키를 변경한다.

 

17. DROP

테이블의 구조와 데이터를 삭제한다.

 

18. INSERT

테이블에 새로운 투플을 삽입한다.

 

19. UPDATE

기존 투플에 있는 특정 속성 값을 수정한다.

 

20. DELETE

테이블에 있는 기존 투플을 삭제한다.

 

4장. SQL 고급

 

1. 내장 함수

사용자가 만든 함수에 대비되는 용어로 DBMS에서 제공하는 함수다. 상수나 열 이름을 입력 값으로 받아 호출되며, 단일 값을 결과로 반환한다.

 

숫자 함수

• ABS(숫자): 숫자의 절댓값을 계산.

ABS(-4.5)=4.5

• CEILING(숫자): 숫자보다 크거나 같은 최소의 정수.

CEILING(4.1)=5

• FLOOR(숫자): 숫자보다 작거나 같은 최소의 정수.

FLOOR(4.1)=4

• ROUND(숫자, m): 숫자의 반올림, m은 반올림 기준 자릿수.

ROUND(5.36, 1)=5.40

• LOG(n, 숫자): 숫자의 자연로그 값을 반환.

LOG(10)=2.30359

• POWER(숫자, n): 숫자의 n제곱 값을 계산.

POWER(2,3)=8

• SQRT(숫자): 숫자의 제곱근 값을 계산(숫자는 양수).

SQRT(9.0)=3.0

• SIGN(숫자): 숫자가 음수면 -1, 0이면 0, 양수면 1.

SIGN(3.45)=1

 

문자 함수(문자 값 반환 함수 / s: 문자열, c: 문자, n: 정수, k: 정수)

• CHR(k): 정수 아스키 코드를 문자로 반환.

CHR(68)=’D’

• CONCAT(s1, s2): 두 문자열을 연결.

CONCAT(‘마당’,’ 서점’)=’마당 서점’

• INITCAP(s): 문자열의 첫 번째 알파벳을 대문자로 변환.

INITCAP(‘the soap’)=’The Soap’

• LOWER(s): 대상 문자열을 모두 소문자로 변환.

LOWER(‘MR.SCOTT’)=’mr.scott’

• LPAD(s, n, c): 대상 문자열의 왼쪽부터 지정한 자리 수까지 지정한 문자로 채움.

LPAD(‘Page 1’, 10, ‘*’)=’****Page 1’

• LTRIM(s1, s2): 대상 문자열의 왼쪽부터 지정한 문자들을 제거.

LTRIM(‘<==>BROWNING<==>’,’<>=’)=’BROWNING<==>’

• REPLACE(s1, s2, s3): 대상 문자열의 지정한 문자를 원하는 문자로 변경.

REPLCAE(‘JACK and JUE’, ‘J’, ‘BL’)=’BLACK and BLUE’

• RPAD(s, n, c): 대상 문자열의 오른쪽부터 지정한 자리 수까지 지정한 문자로 채움.

RPAD(‘AbC’, 5, ‘*’)=’AbC**’

• RTRIM(s1, s2): 대상 문자열의 오른쪽부터 지정한 문자들을 제거.

RTRIM(‘<==>BROWNING<==>’, ’<>=’)=’<==>BROWNINIG’

• SUBSTR(s, n, k): 대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 변환.

SUBSTR(‘ABCDEFG’, 3, 4)=’CDEF’

• TRIM(c FROM s): 대상 문자열의 양쪽에서 지정된 문자를 삭제(문자열만 넣으면 기본값으로 공백 제거).

TRIM(‘=’ FROM ‘==>BROWNING<==’)=’>BROWNING<’

• UPPER(s): 대상 문자열을 모두 대문자로 변환.

UPPER(‘mr.scott’)=’MR.SCOTT’

 

문자 함수(숫자 값 반환 함수)

• ASCII(c): 대상 알파벳 문자의 아스키 코드 값을 반환.

ASCII(‘D’)=68

• INSTR(s1, s2, n, k): 문자열 중 n번째 문자부터 시작하여 찾고자 하는 문자열 s2가 k번째 나타나는 문자열 위치 반환.

INSTR(‘CORPORATE FLOOR’, ‘OR’, 3, 2)=14

• LENGTH(s): 대상 문자열의 글자 수를 반환.

LENGTH(‘CANDIDE’)=7

 

날짜, 시간 함수

• TO_DATE(char, datetime): 문자형(CHAR) 데이터를 날짜형(DATE)으로 반환.

TO_DATE(‘2014-02-14’, ‘yyyy-mm-dd’)=2014-02-14

• TO_CHAR(date, datetime): 날짜형(DATE) 데이터를 문자형(VARCHAR2)로 반환.

TO_CHAR(TO_DATE(‘2014-02-14’, ‘yyyy-mm-dd’), ‘yyyymmdd’)=’20140214’

• ADD_MONTHS(date, 숫자): date 형의 날짜에서 지정한 달만큼 더함(1: 다음달, -1: 이전달).

ADD_MONTHS(TO_DATE(‘2014-02-14’, ‘yyyy-mm-dd’), 12)=2015-02-14

• LAST_DAY(date): date 형의 날짜에서 달의 마지막 날을 반환.

LAST_DAY(TO_DATE(‘2014-02-14’, ‘yyyy-mm-dd’))=2014-02-28

• SYSDATE: DBMS 시스템상의 오늘 날짜를 반환하는 인자 없는 함수.

SYSDATE=14/04/20

 

2. 부속질의

하나의 SQL 문 안에 다른 SQL 문이 중첩된(nested) 형태로 표현되는 질의를 말한다. 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공하는 데 사용한다.

• 스칼라 부속질의: SELECT 절에서 사용되는 부속질의로 단일 행, 단일 열의 스칼라 값을 반환한다.
• 인라인 뷰: FROM 절에서 사용되는 부속질의로 결과를 뷰(view) 형태로 반환한다.
• 중첩질의: WHERE 절에서 사용되는 부속질의로 주질의에 사용된 자료 집합의 조건을 서술한다.

 

3. 뷰

하나 이상의 테이블을 합하여 사용하기 편리하게 만든 가상의 테이블이다.

• 편리성: 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있다.
• 재사용성: 자주 사용되는 질의를 뷰로 미리 정의해 놓을 수 있다.
• 보안성: 각 사용자별로 필요한 데이터만 선별하여 보여줄 수 있다.

CREATE VIEW 뷰이름 [(열이름 [ ,… n ])]
AS SELECT 문
CREATE OR REPLACE VIEW 뷰이름 [(열이름 [ ,… n ])]
AS SELECT 문
DROP VIEW 뷰이름 [ ,… n ];

 

4. DBMS와 데이터 파일

 

5. 인덱스

도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조다.

 

6. B-tree

루트 노드(root node), 내부 노드(internal node), 리프 노드(leaf node)로 구성된 트리 자료구조로, 리프 노드가 모두 같은 레벨에 존재하는 균형(balanced) 트리다.

 

7. 오라클 인덱스의 종류

• B-tree 인덱스: B-tree 구조를 이용한 인덱스로 트리의 리프 노드에 실제 데이터 값 대신 해당되는 행의 위치 값인 rowid를 가지고 있다.
• IOT(Index-Organized Table): 연석된 키 값의 레코드를 묶어서 같은 블록에 저장하는 방법으로, 인덱스 구조의 리프 노드에 rowed 대신 테이블의 투플 자체가 저장된다.
• Bitmap: 컬럼 값들의 유무를 이진의 비트값으로 나열한 인덱스다.
• FBI(Function-Based Index): 함수를 사용하여 특정 컬럼을 가공하여 만든 인덱스다.

CREATE [REVERSE] [UNIQUE] INDEX 인덱스이름
[ON 테이블이름 (컬럼 [ASC | DESC] [{, 컬럼 [ASC | DESC]} …])];
ALTER [REVERSE] [UNIQUE] INDEX 인덱스 이름
[ON {ONLY} 테이블이름 (컬럼이름 [{, 컬럼이름 } …])] REBUILD];
DROP INDEX 인덱스이름;

 

5장. 데이터베이스 프로그래밍

 

1. 데이터베이스 프로그래밍

DBMS에 데이터를 정의하고 시스템에 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정을 말한다. 일반 프로그래밍과는 데이터베이스 언어인 SQL을 포함한다는 점이 다르다.

 

2. 삽입 프로그래밍

데이터베이스 프로그래밍 방법 중 하나로 SQL을 자바, C와 같은 범용 프로그래밍 언어에 삽입하여 프로그래밍하는 것을 말한다. SQL 문이 삽입된 자바, C와 같은 프로그래밍 언어를 호스트 언어라고 한다.

 

3. PL/SQL(Procedural Language/Structured Query Language)

SQL 언어를 확장하여 데이터베이스 전용 응용 프로그램을 작성할 때 사용하는 오라클사의 SQL 전용 언어다.

 

4. 저장 프로시저

PL/SQL에서 사용하는 기능으로, 일반 프로그래밍 언어의 함수 대신 사용하는 명칭이다. 프로시저를 정의하여 DBMS에 저장한다.

 

5. 커서

실행 결과 테이블을 한 번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로 가리키는 데 사용하는 포인터를 말한다. 커서와 관련된 키워드로는 CURSOR, OPEN, FETCH, CLOSE 등이 있다.

 

6. 트리거

데이터의 변경(삽입, 삭제, 수정)문이 실행될 때 자동으로 실행되는 프로시저다. 보통 데이터 변경문이 처리되는 세 가지 시점. 즉 실행 전(BEFORE), 대신하여(INSTEAD OF), 실행 후(AFTER)에 동작한다. 오라클은 기본적으로 BEFORE와 AFTER 트리거를 지원한다.

 

7. 연동

어느 한 부분이 움직이면 다른 부분도 같이 움직인다는 의미로, 데이터베이스 응용에서는 일반 프로그램을 수행하여 DBMS를 동작시킨다는 의미이다. 연동은 자바 프로그램 혹은 웹 프로그램을 이용.

 

8. JDBC(Java DataBase Connectivity)

자바는 객체지향 언어이기 때문에 객체를 호출하여 데이터베이스에 접속한다. 데이터베이스에 접속하는 API(Application Programming Interface)를 java.sql.*에서 제공한다. java.sql에 정의된 API는 각 DBMS 제조사에게 자신의 제품에 맞게 구현해서 제공하는데, 이를 JDBC(Java DataBase Connectivity) 드라이버라고 한다.

---

 

데이터베이스 설계자

 

6장. 데이터 모델링

 

1. 데이터베이스 생명주기

데이터베이스 생성과 운영에 관련된 ‘요구사항 수집 및 분석 – 설계 – 구현 – 운영 – 감시 및 개선’ 사이클을 말한다.

 

2. 개념적 모델링

개체(entity)를 추출하고 각 개체 간의 관계(relationship)를 정의하여 ER 다이어그램을 만드는 과정이다.

 

3. 논리적 모델링

개념적 모델링 단계에서 만들어진 ER 다이어그램을 사용하고자 하는 DBMS에 맞게 매핑하여 실제 데이터베이스로 구현하기 위한 모델을 만드는 과정이다.

 

4. 물리적 모델링

작성된 논리적 모델을 실제 컴퓨터의 저장 장치에 저장하기 위한 물리적 구조를 정의하여 구현하는 과정이다.

 

5. ER 모델

데이터 모델링 과정 중 개념적 모델링 단계에 사용하는 모델로 1976년 피터 첸(Peter Chen)이 제안하였다. ER 모델의 핵심 개념은 세상의 사물을 개체(entity)와 개체 간의 관계(relationship)로 표현하는 것이다.

 

6. 개체와 개체 타입

• 개체: 사람, 사물, 장소, 개념 또는 사건과 같이 유무형의 정보를 가지고 있는 독립적인 실체를 말한다.
• 개체 타입: 개체 집합은 공통된 속성을 가진 개체들의 모임이며 개체 타입은 개체 집합의 이러한 동일한 특징을 나타내는 용어다.

 

7. 개체 타입의 종류

• 강한 개체 타입: 다른 개체의 도움 없이 독자적으로 존재할 수 있는 개체를 말한다.
• 약한 개체 타입: 독자적으로는 존재할 수 없고 반드시 상위 개체 타입을 가져야 하는 개체를 말한다.

 

8. 속성

개체가 가진 성질을 의미한다.

 

9. 속성의 종류

• 단순 속성과 복합 속성
  • 단순 속성: 더 이상 분해가 불가능한 속성이다.
  • 복합 속성: 독립적인 의미를 가진 속성으로 분해할 수 있는 속성이다.
• 단일값 속성과 다중값 속성
  • 단일값 속성: 특정 개체 타입에 대해 하나의 값만 가지는 속성이다.
  • 다중값 속성: 여러 개의 값을 가질 수 있는 속성이다.
• 저장 속성과 유도 속성
  • 저장 속성: 다른 속성의 영향 없이 단독으로 저장되는 속성이다.
  • 유도 속성: 다른 저장 속성으로부터 유도된(계산되어진) 속성이다.

10. 관계와 관계 타입

• 관계: 개체 사이의 연관성을 나타내는 개념이다.
• 관계 타입: 개체 타입과 개체 타입 간의 연결 가능한 관계를 정의한 것이다.

 

11. 대응 수

두 개체 타입의 관계에 실제로 참여하는 개별 개체들의 수를 말한다. 대응수는 하나의 개체가 하나의 개체에 대응하는 1:1 관계, 하나의 개체가 여러 개체에 대응하는 1:N 관계, 여러 개체가 하나의 개체에 대응하는 N:1 관계, 여러 개체가 여러 개쳬에 대응하는 N:M 관계로 나눌 수 있다.

 

12. ISA(수퍼클래스와 서브클래스) 관계

상위 개체의 특성에 따라 하위 개체 타입이 결정되는 형태를 말한다. 이때 상위 개체 타입을 수퍼클래스, 하위 개체 타입을 서브클래스라고 한다.

 

13. 전체 참여와 부분 참여

관계에 참여하는 개체 집합의 참여 형태에 따른 제약 조건으로, 개체 집합의 모든 개체들이 관계에 참여하는 조건을 전체 참여라고 한다. 전체 참여는 개체 타입과 관계 사이를 두 줄 실선으로 표시한다. 일부만 참여하는 부분 참여의 경우는 일반적인 관계의 표현과 동일하게 단일 실선으로 표시한다.

 

14. 순환적 관계

하나의 개체 타입이 동일한 개체와 순환적인 관계를 가지는 형태다. 순환적 관계는 관계선을 양쪽 모두에 그린다.

 

15. 식별자

약한 개체 타입에서 개별 개체들을 구분할 수 있는 속성을 말한다. 식별자는 강한 개체 타입의 키와 달리 약한 개체 타입을 구분하는 역할을 한다.

 

16. IE 표기법(Information Engineering Notation)

ER 다이어그램을 그리는 대표 프로그램인 ERWin에서 사용하는 표기법 중 하다. IE 표기법은 관계 대응 수의 표현에 새발 같은 모양의 기호를 사용하여 새발 표기법(crow-feet)이라고도 한다.

 

17. 사상(mapping, 매핑)

ER 모델이 완성되면 실제 데이터베이스를 구축하기 위해 논리적 모델링을 수행하여야 한다. 이때 ER 모델과 사용할 데이터베이스 모델 간의 매핑을 말한다.

 

7장. 정규화

 

1. 이상현상

잘못 설계된 테이블로 삽입, 삭제, 수정 같은 작업을 했을 때 생기는 현상으로 데이터베이스의 일관성을 훼손하여 데이터의 무결성을 깨뜨린다.

• 삭제이상: 투플 삭제 시 하나의 정보를 삭제함으로써 같이 저장된 다른 정보가 연쇄적으로 삭제되는 현상이다.
• 삽입이상: 투플 삽입 시 제공되지 못하는 속성 값을 NULL 값으로 입력해야 하는 현상이다.
• 수정이상: 투플 수정 시 중복된 데이터의 일부만 수정되어 데이터의 불일치가 일어나는 현상이다.

 

2. 함수 종속성

릴레이션 R과 R에 속하는 속성의 집합 X, Y가 있을 때, X 각각의 값이 Y의 값 한 개와 대응될 때 ‘X는 Y를 함수적으로 결정한다’라고 하고 X → Y로 표기한다.

 

3. 정규화

릴레이션을 분해하여 이상현상을 제거하는 과정이다.

• 제 1정규형(1NF): 어떤 릴레이션 R의 모든 속성 값이 원자값을 가지면 제 1정규형을 만족한다.
• 제 2 정규형(2NF): 어떤 릴레이션 R이 제 1정규형이고 기본키가 아닌 속성이 기본키에 완전 함수 종속일 때 제 2정규형이라고 한다.
  • 완전 함수 종속(full functional dependency): A와 B가 릴레이션 R의 속성이고 A → B 종속성이 성립할 때, B가 A의 속성 전체에 함수 종속하고 부분 집합 속성에 함수 종속하지 않을 경우 완전 함수 종속이라고 한다.
• 제 3정규형(3NF): 어떤 릴레이션 R이 제 2정규형이고 기본키가 아닌 속성이 기본키에 비이행적(non-transitive)으로 종속할 때(직접 종속) 제 3정규형이라고 한다.
• BCNF(Boyce Codd Normal Form): 함수 종속성 X → Y가 성립할 때 모든 결정자 X가 후보키면 BCNF이다.

 

4. 무손실 분해

릴레이션 R을 릴레이션 R1과 R2로 분해했을 때, R1 ⋈ R2 = R이면 무손실(lossless-join) 분해라고 한다. 무손실 분해를 위한 조건은 R1 ∩ R2 → R1 이거나 R1 ∩ R2 → R2 중 하나를 만족하면 된다.

 

8장. 트랜잭션, 동시성 제어, 회복

1. 트랜잭션의 상태도

 

2. 트랜잭션의 성질

• 원자성: 트랜잭션과 관련된 작업들이 전부 수행되던지 아니면 전부 수행되지 않아야(all or nothing) 한다.
• 일관성: 트랜잭션을 수행하기 전이나 수행한 후나 데이터베이스는 항상 일관성 있는 상태를 유지해야 한다.
• 고립성: 트랜잭션 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장해야 한다.
• 지속성: 성공적으로 수행을 완료한 트랜잭션은 변경한 데이터를 데이터베이스에 영구히 저장해야 한다.

 

3. 동시성 제어

트랜잭션이 동시에 실행될 때 데이터베이스의 일관성을 해치지 않도록 트랜잭션의 데이터 접근을 제어하는 DBMS의 기능이다.

 

4. 갱신손실

동시성 제어를 하지 않는 트랜잭션들이 발생시키는 문제로, 한 트랜잭션의 갱신이 다른 트랜잭션에 의하여 분실되는 현상이다.

 

5. 락

트랜잭션이 데이터를 읽거나 변경할 때 데이터에 표시하는 잠금 장치다.

• 공유락(LS, shared lock): 읽기를 할 때 사용하는 잠금 장치다.
• 배타락(LX, exclusive lock): 쓰기를 할 때 사용하는 잠금 장치다.

 

6. 2단계 락킹

트랜잭션이 락을 걸고 해제하는 시점을 2단계로 나누어 시행하는 락킹 기법이다. 확장단계와 수축단계가 있다.

 

7. 데드락

두 개 이상의 트랜잭션이 각각 자신의 데이터에 대하여 락을 획득하고 상대방 데이터에 대하여 락을 요청하여 무한 대기 상태에 빠지는 현상을 말한다.

 

8. 트랜잭션 동시 실행 문제

• 오손 읽기: 다른 트랜잭션이 COMMIT하지 않는 데이터를 읽은 후 다른 트랜잭션이 철회(ROLLBACK)하면서 발생하는 현상이다.
• 반복불가능 읽기: 트랜잭션 중간에 다른 트랜잭션이 변경한 데이터를 읽으면서 발생하는 현상이다.
• 유령데이터 읽기: 트랜잭션 중간에 다른 트랜잭션이 삽입한 데이터를 읽으면서 발생하는 현상이다.

 

9. 트랜잭션 고립 수준 명령어

트랜잭션의 읽기/쓰기에 대한 고립 수준을 결정하는 명령이다. READ UNCOMMITTED, READ COMMITTED REPEATABLE READ, SERIALIZABLE이 있다.

 

10. 로그 파일을 이용한 회복

• 재실행(REDO): 로그 파일에 기록된 로그를 이용하여 트랜잭션이 수행한 결과를 다시 반영하는 연산이다.
• 취소(UNDO): 로그 파일에 기록된 로그를 이용하여 트랜잭션이 수행한 결과를 취소하는 연산이다.

 

11. 회복을 위한 로그 기록 방법

• 즉시 갱신: 트랜잭션은 ‘갱신 데이터 → 로그’, ‘버퍼 → 데이터베이스’ 작업이 부분완료 전에 동시에 진행될 수 있으며 REDO, UNDO 연산을 이용하여 복구한다.
• 지연 갱신: 트랜잭션은 ‘갱신 데이터 → 로그’가 끝난 후 부분완료를 하고 ‘버퍼 → 데이터베이스’ 작업은 부분완료 후에 진행한다. REDO 연산을 이용하여 복구한다.

 

12. 체크포인트

데이터베이스와 트랜잭션 로그 파일을 동기화한 후, 동기화한 시점을 로그 파일에 기록해두는데 이를 체크포인트라고 한다.

 

9장. 데이터베이스 보안과 관리

 

1. 테이블스페이스 생성하기

테이블스페이스(TABLESPACE)는 오라클에서 데이터를 저장할 때 사용하는 논리적인 저장공간을 의미한다.

• 시스템 테이블스페이스: 데이터베이스 생성 시 자동 생성.
• 사용자 테이블스페이스: 사용자가 필요에 따라 만들어 사용.

CREATE TABLESPACE 테이블스페이스이름
    DATAFILE ‘저장될 경로 및 사용할 파일명’
    SIZE 저장공간

DROP TABLESPACE 테이블스페이스이름
    [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]];

 

2. 신규 사용자 계정 생성하기

CREATE USER 사용자이름
    IDENTIFIED BY 비밀번호
    DEFAULT TABLESPACE 테이블스페이스
ALTER USER 사용자이름
    IDENTIFIED BY 비밀번호
DROP USER 사용자이름 CASCADE;

 

3. 권한 관리

• GRANT 문: 객체를 생성한 소유자가 대상 객체에 대한 권한을 다른 사용자에게 허가하는 명령이다.
• REVOKE 문: GRANT 문으로 허가한 권한을 취소, 회수하는 명령이다.

 

주요 객체별 지정 가능한 권한(객체 권한)

• ALTER: 테이블
• DELETE: 테이블, 뷰
• EXECUTE: 프로시저/함수
• INDEX: 테이블
• INSERT: 테이블, 뷰
• REFERENCES: 테이블, 뷰
• SELECT: 테이블, 뷰
• UPDATE: 테이블, 뷰

 

주요 시스템 권한

• CREATE TABLE: 테이블을 생성할 수 있음.
• CREATE VIEW: 뷰를 생성할 수 있음.
• CREATE USER: 새로운 사용자를 생성할 수 있음.
• CREATE TABLESPACE: 테이블스페이스를 생성할 수 있음.
• CREATE SESSION: 데이터베이스에 접속할 수 있음.
• DELETE ANY TABLE: 다른 사용자 테이블의 투플을 삭제(DELETE)할 수 있음.
• ALTER ANY TABLE: 다른 사용자의 테이블을 수정(ALTER)할 수 있음.
• DROP ANY TABLE: 다른 사용자의 테이블을 삭제(DROP)할 수 있다.
• CREATE ANY TABLE: 다른 사용자의 테이블을 생성(CREATE)할 수 있음.
• SELECT ANY TABLE: 다른 사용자의 테이블을 조회(SELECT)할 수 있음.
• CREATE ANY VIEW: 다른 사용자의 뷰를 생성(CREATE)할 수 있음.
• ALTER ANY VIEW: 다른 사용자의 뷰를 수정(ALTER)할 수 있음.
• DROP ANY INDEX: 다른 사용자의 테이블의 인덱스를 삭제(DROP)할 수 있음.
• CREATE ANY INDEX: 다른 사용자 테이블의 인덱스를 생성(CREATE)할 수 있음.
• ALTER ANY INDEX: 다른 사용자 테이블의 인덱스를 수정(ALTER)할 수 있음.

GRANT 권한 [(컬럼[ , … n ])] [ , … n ]
    [ON 객체] TO { 사용자 | 롤 | PUBLIC [ , … n ]}
    [WITH GRANT OPTION]
REVOKE 권한 [(컬럼[ , … n ])] [ , … n ]
    [ON 객체] FROM { 사용자 | 롤 | PUBLIC [ , … n]}

 

4. WITH GRANT OPTION

허가 받은 권한을 다른 사용자에게 다시 부여할 때 사용하는 옵션이다.

 

5. 역할(ROLE, 롤)

데이터베이스 객체(OBJECT)에 대한 권한을 모아둔 집합이다.

CREATE ROLE 역할 이름
DROP ROLE 역할 이름
GRANT 권한 [ON 객체] TO 역할이름
REVOKE 권한 [ON 객체] FROM 역할이름
GRANT 역할이름 TO 사용자

 

6. 백업

예상하지 못한 장애에 대비하여 데이터베이스를 복제하여 보관하는 작업이다.

 

7. 복원

장애가 발생하여 운영 중인 데이터가 손상을 입었을 때 기존에 복사해 둔 백업 파일을 사용하여 복구하는 작업이다.

 

8. 백업의 종류

• 전체 백업: 데이터베이스 개체, 시스템 테이블, 데이터 등 데이터베이스 전체를 백업한다.
• 차등 백업: 마지막으로 수행한 전체 백업 이후에 변경된 데이터만 백업한다.
• 증분 백업: 데이터베이스에서 수행한 작업을 기록하고 있는 로그 파일을 이용하여 백업한다.