기말고사 대비 개념정리

데이터베이스실무(변시우) 기말고사 대비 개념정리

데이터베이스의 개념

전통적 파일 정보 시스템의 문제점

1. 데이터 종속성
2. 데이터 중복성

데이터베이스 시스템(위 문제를 해결)

1. 데이터 독립성
2. 제어된 중복

데이터베이스의 정의

1. 공용 데이터의 저장소
   여러 사용자가 공동으로 소유하고 유지
2. 통합 데이터의 저장소
   중복을 최소화하여 통합
3. 운영 데이터의 저장소
   조직의 고유 업무를 위해 사용
4. 저장 데이터의 저장소
   컴퓨터가 접근 가능한 매체에 저장

데이터베이스의 특성

1. 실시간 접근(realtime accessibility):
   수많은 사용자가 접근해도 즉시 응답
2. 끊임없는 변화(continuous evolution):
   현실 세계와 같이 계속해서 변화하여 현실 세계를 반영
3. 동시 공용(concurrent sharing):
   여러 사용자가 동시에 같은 데이터 이용 가능
4. 내용 기반 참조(content based referencing):
   주소(위치)가 아니라 값(내용)으로 데이터에 접근

데이터베이스 시스템

데이터베이스 사용자의 분류

1. 일반 사용자(최종 사용자)
   전문 지식 없이 데이터베이스에 접근하는 사용자
2. 응용 프로그래머
   프로그래밍 언어로 응용 프로그램을 개발하며, 데이터 언어나 DBMS 실행 명령어를 작성
3. 데이터베이스 관리자(DBA)
   DB를 구축하고 운영, 통제하는 슈퍼 사용자

데이터 언어(SQL: Structured Query Language)

1. 데이터 정의어(DDL: Data Definition Language)
   데이터베이스 구조를 정의하거나 변경
   CREATE, ALTER, DROP 등
2. 데이터 조작어(DML: Data Manipulation Language)
   데이터의 입력, 수정, 삭제, 검색 요청
   INSERT, UPDATE, DELETE, SELECT 등
3. 데이터 제어어(DCL: Data Control Language)
   데이터베이스를 제어하고 통제(보안, 무결성, 회복 등)
   GRANT, REVOKE, CREATE USER, COMMIT, ROLLBACK 등

DBMS의 필수 기능

1. 정의 기능:
   통합 데이터베이스 구조를 생성, 삭제, 변경
2. 조작 기능:
   데이터에 접근하여 입력, 수정, 삭제, 검색 처리
3. 제어 기능:
   데이터를 정확하고 안전하게 유지하도록 통제(보안, 권한, 무결성, 회복)

데이터 모델의 종류

1. 계층형(Hierarchical) 데이터 모델:
   트리 형태, 1:n 관계, 검색 속도 빠름
2. 네트워크형(Network) 데이터 모델:
   포인터 사용, m:n 표현 용이
3. 관계형(Relational) 데이터 모델:
   테이블 형태, 키(key)를 통해 관계 표현, 이해하기 쉬움
   MySQL, Oracle이 속함
4. 객체 지향형(Object Oriented) 데이터 모델:
   객체 단위, OID 사용, 상속/캡슐화 개념 도입
5. 객체 관계형(Object Relational) 데이터 모델:
   관계형을 기본으로 객체 개념 추가

3단계 데이터베이스 구조 (ANSI/SPARC) - 데이터 독립성 제공

• 외부 단계 (외부 스키마 / 서브 스키마):
  사용자 관점의 개별적 DB 구조.
  여러 외부 스키마 존재 가능 (예: 교수용 뷰, 학생용 뷰).
• 개념 단계 (개념 스키마):
  조직 전체 관점의 통합된 DB 구조.
  하나의 DB에는 하나의 개념 스키마만 존재.
• 내부 단계 (내부 스키마):
  저장 장치 관점의 물리적 저장 방식 명세.

데이터 사전 (Data Dictionary)

스키마와 사상 정보 등 다양한 DB 객체에 관한 모든 데이터(메타 데이터)를 저장 시스템 데이터베이스(System Database)의 일종 사용자는 검색만 가능하고 변경은 DBMS만 가능

데이터 독립성

1. 논리적 데이터 독립성:
   외부 스키마와 개념 스키마 사이의 사상(Mapping)
   개념 스키마가 변경되어도 외부 스키마에는 영향을 주지 않음
2. 물리적 데이터 독립성:
   개념 스키마와 내부 스키마 사이의 사상(Mapping)
   내부 저장 구조가 변경되어도 개념 스키마에는 영향을 주지 않음

관계형 데이터 모델

데이터 모델의 3요소

1. 데이터 구조(Data Structure):
   데이터를 어떤 형태로 저장하는지 표현 (예: 릴레이션).
2. 연산(Operation):
   데이터 구조 안의 데이터를 어떤 방식으로 처리하는지 표현 (예: 관계 대수).
   
3. 제약 조건(Constraint):
   데이터 저장 시의 구조적 제약과 연산 적용 시의 행위적 제약 사항.

릴레이션(Relation)의 주요 개념

• 속성(Attribute)
  테이블의 열(Column).
  더 이상 분해할 수 없는 원자 값(Atomic Value)만 사용.
• 투플(Tuple)
  테이블의 각 행(Row).
  현실 세계의 개체(Entity)를 표현.
• 도메인(Domain)
  각 속성이 취할 수 있는 모든 값들의 집합.
  (데이터의 유형, 크기, 범위 정의).
• 차수(Degree)
  릴레이션을 구성하는 전체 속성의 개수(정적 특성).
• 카디널리티(Cardinality)
  릴레이션 안의 전체 투플의 개수(동적 특성).

릴레이션의 4가지 특성

1. 투플의 유일성
   모든 투플은 서로 달라야 하며, 유일한 속성 값이 있어야 함.
2. 투플의 무순서성
   투플 사이의 순서는 의미가 없음(집합의 성질).
3. 속성의 무순서성
   속성 사이의 순서는 의미가 없으며, 이름으로 참조됨.
4. 속성의 원자성
   모든 속성 값은 더 이상 분해할 수 없는 하나의 원자 값만을 가짐(다중 값 허용 안 됨).

키(Key)의 종류

1. 슈퍼키(Super Key)
   투플을 유일하게 식별할 수 있는 속성 집합
   유일성은 만족하지만 최소성은 만족하지 않아도 됨
2. 후보키(Candidate Key)
   투플을 유일하게 식별할 수 있는 속성들의 최소 집합
   유일성과 최소성을 모두 만족해야 함
3. 기본키(Primary Key - PK)
   후보키 중에서 하나를 선택하여 지정한 키
   널(NULL) 값을 가질 수 없음
4. 대체키(Alternate Key)
   기본키로 선정되지 못한 나머지 후보키
5. 외래키(Foreign Key - FK)
   다른 릴레이션의 기본키를 참조하는 속성 집합
   릴레이션 간의 연관성을 표현

• 키의 포함 관계
  슈퍼키 ⊃ 후보키 ⊃ 기본키 = 대체키

무결성 제약 조건 (Integrity Constraint)

1. 개체 무결성(Entity Integrity)
   기본키로 지정한 속성은 NULL 값을 가질 수 없고 중복될 수 없음
2. 참조 무결성(Referential Integrity)
   외래키는 참조하는 릴레이션의 기본키 값과 일치하거나 NULL 값만을 가져야 함
3. 도메인 무결성
   속성 값이 정의된 도메인에 속한 값이어야 함
4. 유일성 제약 조건(Unique)
   키 속성 값이 중복되지 않아야 함 (대체키 관련)

관계 연산

• 관계 대수 (Relational Algebra):
  절차적 언어(Procedural)
  데이터를 획득하는 과정(How)을 명세
• 관계 해석 (Relational Calculus):
  비절차적 언어(Non-procedural)
  원하는 정보가 무엇(What)인지만 선언

관계 대수

관계 대수의 정의

• 릴레이션을 내부적으로 처리하기 위한 연산(operation)들의 집합
• 관계형 데이터 모델의 이론적 언어로써 SQL 언어의 이론적 토대를 제공
• 절차적(procedural) 표현 방법: 사용자가 필요로 하는 데이터를 획득하는 절차(순서)를 명세하며 ‘어떻게(HOW)’에 중점
  • (비교: 관계 해석은 결과만 선언하는 비절차적 표현이며 ‘무엇을(WHAT)’에 중점)

연산의 분류

A. 집합 연산 (Set Operation)

1. 합집합 (Union, $\cup$):
   두 릴레이션의 모든 투플을 반환 (중복 제거)
   결과 차수는 R1 또는 R2와 같음
2. 교집합 (Intersection, $\cap$):
   두 릴레이션의 공통 투플만 반환
3. 차집합 (Difference, $-$):
   R1에는 속하지만 R2에는 속하지 않는 투플 반환
4. 카티션 프로덕트 (Cartesian Product, $\times$):
   두 릴레이션의 모든 투플을 수평으로 결합(연결)
   결과 차수: R1 차수 + R2 차수
   결과 카디널리티: R1 카디널리티 $\times$ R2 카디널리티

B. 관계 연산 (Relational Operation)

1. 셀렉트 (Select, $\sigma$):
   조건을 만족하는 투플(행)을 선택
   릴레이션을 수평 분할(horizontal partitioning)하는 효과
   기호: $\sigma_{조건식}(R)$
   (SQL의 WHERE 절에 해당)
2. 프로젝트 (Project, $\pi$):
   특정 속성(열)만 추출
   릴레이션을 수직 분할(vertical partitioning)하는 효과
   기호: $\pi_{속성리스트}(R)$
   (SQL의 SELECT 절에 해당)
3. 조인 (Join, $\bowtie$):
   두 릴레이션의 공통 속성을 기준으로 투플을 결합
   동등 조인 (Equijoin): 비교 연산자로 ‘=’을 사용하는 가장 일반적인 조인
4. 디비전 (Division, $\div$):
   특정 값들을 모두 가지고 있는 투플을 찾는 연산
   R2의 모든 투플과 연관된 R1의 투플을 반환

연산의 성격에 따른 분류(기본 vs 복합)

관계 대수의 활용 (쿼리 예시)

• 삽입 : $R \cup { \text{투플} }$ (합집합 이용)
• 삭제 : $R - (\sigma_{조건}(R))$ (차집합 이용)
• 수정 : 삭제 후 삽입하는 방식으로 표현

SQL 기초

SELECT 문의 실행 순서(논리적 순서)

1. FROM (테이블 가져오기)
2. WHERE (행 필터링 - 집계 함수 사용 불가)
3. GROUP BY (그룹화)
4. HAVING (그룹 필터링 - 집계 함수 사용 가능)
5. SELECT (출력할 열 선택) - 별칭(AS) 정의
6. ORDER BY (정렬)

NULL 값의 함정(연산 및 집계)

• 비교 불가:
  NULL은 =, <, > 연산자로 비교하면 무조건 거짓(False)입니다. 반드시 IS NULL 또는 IS NOT NULL을 써야 합니다.
• 산술 연산:
  NULL이 포함된 사칙연산의 결과는 무조건 NULL입니다. (예: 100 + NULL = NULL)
• 집계 함수:
  COUNT(*): NULL을 포함한 모든 행의 개수.
  COUNT(속성명): NULL 값을 제외한 행의 개수.

와일드카드 (_ vs %)

LIKE 연산자 사용 시 정확한 의미 구분이 필요합니다.

• _ (언더바): 정확히 1글자 (예: LIKE ‘__’ → 딱 2글자인 이름만 검색).
• % (퍼센트): 0개 이상의 모든 문자열 (예: LIKE ‘김%’ → ‘김’으로 시작하는 모든 문자열).

집합 연산자의 MySQL 특징

• UNION: 합집합. 자동으로 중복 행 제거.
• 제약 사항:
  표준 SQL과 달리 MySQL은 INTERSECT(교집합)와 MINUS/EXCEPT(차집합)를 지원하지 않습니다.

서브쿼리 (Subquery) 핵심

• 실행 순서:
  괄호 안의 부 질의문(Subquery)이 먼저 실행되고, 그 결과를 바깥쪽 주 질의문(Main Query)이 사용합니다.
• IN 연산자:
  서브쿼리 결과 중 하나라도 일치하면 참(True).
• 상호연관 질의 (Correlated Query):
  안쪽 질의의 WHERE 절이 바깥쪽 질의의 테이블을 참조하는 경우입니다. 성능상 주의가 필요합니다.

조인 (JOIN) 심화

• 크로스 조인 (Cross Join):
  조인 조건이 없을 때 발생하며, 두 테이블의 모든 행을 곱한(Cartesian Product) 결과가 나옵니다 (의미 없는 데이터 대량 발생 주의).
• 셀프 조인 (Self Join):
  하나의 테이블에 별칭을 다르게 주어(S1, S2) 마치 두 개의 테이블인 것처럼 조인합니다.
  계층 구조나 같은 테이블 내 비교 시 사용합니다.
• 외부 조인 (Outer Join):
  • LEFT OUTER JOIN:
    왼쪽 테이블의 모든 데이터를 포함하고, 매칭되는 오른쪽 데이터가 없으면 NULL로 채움.
  • RIGHT OUTER JOIN:
    오른쪽 테이블의 모든 데이터를 포함하고, 매칭되는 왼쪽 데이터가 없으면 NULL로 채움.
  • FULL OUTER JOIN:
    왼쪽과 오른쪽 테이블의 모든 데이터를 포함하고, 매칭되는 각 데이터가 없으면 NULL로 채움.
  • (기출 변형: “수강생이 없는 과목을 찾으시오” -> 과목 LEFT OUTER JOIN 수강 ON … WHERE 수강.학번 IS NULL)

데이터 변경 (DML) 속 서브쿼리

• UPDATE + Subquery 예시: “수강 내역이 없는 학생의 소속학과를 NULL로 변경하라”

UPDATE 학생
SET 소속학과 = NULL
WHERE 학번 NOT IN (SELECT 학번 FROM 수강);

• DELETE + Subquery 예시: “수강자가 2명 미만인 과목을 삭제하라”

DELETE FROM 과목
WHERE 과목번호 IN
(SELECT 과목번호 FROM 수강 GROUP BY 과목번호 HAVING COUNT(*) < 2);

SQL 활용

데이터 정의어 (DDL)

• ALTER TABLE (테이블 수정):
  열 추가: ADD (예: ADD 등록날짜 DATETIME …)
  열 삭제: DROP COLUMN (예: DROP COLUMN 등록날짜)
  제약 조건 추가/삭제: ADD CONSTRAINT, DROP CONSTRAINT 구문을 사용합니다.
• DROP TABLE (테이블 삭제)의 함정:
  삭제 불가: 다른 테이블(자식 테이블)이 외래키로 참조하고 있는 부모 테이블은 삭제할 수 없습니다.
  해결책: 자식 테이블을 먼저 삭제하거나, 외래키 제약 조건을 끊어야 합니다.

데이터 제어어 (DCL)

• GRANT (권한 부여):
  기본 형식: GRANT 권한 ON 대상 TO 유저;
  WITH GRANT OPTION: 이 옵션이 붙으면, 권한을 받은 사용자가 다른 사용자에게도 그 권한을 부여할 수 있습니다.
• REVOKE (권한 회수):
  기본 형식: REVOKE 권한 ON 대상 FROM 유저;
  (주의: TO가 아니라 FROM을 씁니다.)

뷰 (View)

• 개념: 실제 데이터를 저장하지 않는 가상 테이블(Virtual Table)입니다. 물리적 저장 공간을 거의 차지하지 않습니다.
• 장점 (4가지 키워드): 편의성, 보안성, 재사용성, 독립성(논리적 데이터 독립성)
• ⚠️ 갱신(INSERT/UPDATE/DELETE)이 불가능한 경우:
  단순한 뷰는 수정이 가능하지만, 아래의 경우에는 조회(SELECT)만 가능하고 데이터 변경이 불가능합니다.
  1. 집계 함수(SUM, AVG 등)나 계산식을 포함한 경우.
  2. GROUP BY, DISTINCT를 사용한 경우.
  3. 조인(JOIN)으로 여러 테이블을 합쳐 만든 경우.
  4. 뷰에 포함되지 않은 원본 테이블의 열이 NOT NULL인 경우 (값을 넣을 방법이 없으므로).

인덱스 (Index)

• 자료구조:
  대부분의 DBMS는 B-트리(Balanced Tree) 구조를 사용합니다.
• 생성이 필요한 경우 (Good):
  기본키(PK), 외래키(FK).
  WHERE 절, JOIN 조건, ORDER BY에 자주 사용되는 열.
• 생성이 불필요하거나 안 좋은 경우 (Bad):
  데이터 변경(INSERT, UPDATE, DELETE)이 빈번한 열 (인덱스도 계속 수정해야 해서 성능 저하).
  성별(남/여)처럼 값의 종류가 적은(선택도가 높은) 열.
  테이블의 행 개수가 너무 적은 경우.

SQL 응용

내장 함수 (Built-in Function)

• 문자열 길이 차이:
  LENGTH(): 바이트(Byte) 수 반환 (한글은 3바이트 등으로 계산됨).
  CHAR_LENGTH(): 글자 수 반환 (한글도 1글자로 계산).
• 날짜 계산:
  DATE_ADD(날짜, INTERVAL 값 단위) / DATE_SUB(…): 날짜 더하기/빼기.
  (예: DATE_ADD(‘2023-01-01’, INTERVAL 1 MONTH))

절차형 SQL 3총사 비교

• 트리거의 핵심 키워드:
  OLD: 변경 전 데이터 (DELETE, UPDATE 시 사용).
  NEW: 변경 후 데이터 (INSERT, UPDATE 시 사용).
  (예: IF NEW.성별 = ‘남’ THEN …)

트랜잭션의 ACID 특성

1. 원자성 (Atomicity):
   전부 실행되거나, 아예 실행되지 않아야 함 (All or Nothing).
2. 일관성 (Consistency):
   트랜잭션 전후 데이터베이스 상태가 모순 없이 일관되어야 함.
3. 고립성 (Isolation):
   실행 중인 트랜잭션의 중간 결과에 다른 트랜잭션이 접근할 수 없음.
4. 지속성 (Durability):
   커밋된 결과는 장애가 발생해도 영구적으로 보존되어야 함.

동시성 제어와 락 (Lock)

• 공유 락 (Shared Lock, S-lock):
  읽기(SELECT)할 때 사용.
  다른 공유 락과는 호환되지만, 독점 락과는 호환 불가.
• 독점 락 (Exclusive Lock, X-lock):
  쓰기(INSERT, UPDATE 등)할 때 사용.
  다른 어떤 락과도 호환되지 않음 (완전 독점).

트랜잭션 고립 수준 (Isolation Level)

• 문제 유형 (Dirty, Non-repeatable, Phantom):

  • Dirty Read:
    커밋되지 않은(임시) 데이터를 읽는 오류.
  • Non-repeatable Read:
    같은 데이터를 다시 읽었는데 그 사이 값이 변경(UPDATE)되어 결과가 달라지는 오류.
  • Phantom Read:
    다시 읽었는데 없던 데이터가 생기거나(INSERT) 사라지는(DELETE) 오류.

• 4단계 고립 수준:

  1. READ UNCOMMITTED (수준 0):
     커밋 안 된 것도 읽음. (모든 문제 발생).
  2. READ COMMITTED (수준 1):
     커밋된 것만 읽음. (Dirty Read 방지, 가장 많이 사용).
  3. REPEATABLE READ (수준 2):
     트랜잭션 내에서 같은 값을 보장. (Non-repeatable Read 방지).
  4. SERIALIZABLE (수준 3):
     완벽한 직렬화. (Phantom Read까지 방지, 성능 가장 느림).

회복 (Recovery)

• REDO (재실행):
  커밋된 트랜잭션이지만 장애로 반영 안 됐을 때 -> 로그 보고 다시 실행 (Roll-forward).
• UNDO (취소):
  커밋되지 않았는데 장애 발생 -> 로그 보고 실행 취소 (Roll-back).
• 체크포인트 (Checkpoint):
  로그 내용을 실제 디스크에 저장하는 시점. 이 시점 이전의 로그는 회복 시 무시해도 됨.