실기 SQL, 정규화 등 정리

<SQL -DDL>

[CREATE]

1. 테이블 만들기

CREATE TABLE 학생

(이름, VARCHAR(15) NOT NULL,

학번 CHAR(8),

전공 CHAR(5),

성별 SEX,

생년월일 DATE,

PK(학번),

FK(전공) REFERENCES 학과(학과코드)

ON DELETE/UPDATE SET NULL(4가지 옵션): 학과 튜플 삭제시 관련된 전공 값 => NULL

     CASCADE: 학과코드 변경시 => 같은 값

NO ACTION: '' => 변화 X

SET DEFAULT: '' => DEFAULT로

CONSTRAINT 생년월일제약

CHEACK(생년월일>= '1980-01-01'));

*성별(SEX) 도메인 정의

CREATE DOMAIN SEX CHAR(1)

DEFAULT '남'

CONSTRAINT 성별제약 CHECK(VALUE IN('남', '여'));

2. 뷰 만들기(보기 쉽도록)

CREATE VIEW 국문전공(이름, 전공)

AS SELECT 이름, 전공

FROM 학생

WHERE 전공 = '국문학과';

3. INDEX 만들기(DB 검색 성능 향상 위해)

CREATE UNIQUE INDEX 고객번호_idx

ON 고객(고객번호 DESC);

4. 트리거 만들기(이벤트 발생시마다 자동적으로 수행하는 내용)

문제) 새로운 레코드 삽입 전, 전공 정보 없는 것은 '전공미등록'으로 치환하라.

CREATE TRIGGER 전공미등록_tri BEFORE INSERT ON 학생

REFERENCING NEW TABLE AS new_table(새로 추가된 튜플의 집합)

FOR EACH ROW

WHEN new_table.학년=''

BEGIN(본문 시작)

SET new_table.전공 = '전공미등록'

END(본문 끝)

[ALTER]

문제1: 3글자 짜리 학년이란 '속성'을 학생 TABLE에 추가하라.

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

문제2: 전공 속성의 기본값을 '전공미등록'으로 바꿔라.

ALTER TABLE 학생 ALTER 전공 SET DEFAULT '미등록';

문제3: 전공이라는 속성 제거

ALTER TABLE 학생 DROP 전공;

*CASCADE(참조 중인 다른 개체도 연쇄적으로 제거) <-> RESTRICT(참조중일 경우 제거 취소)

<SQL -DML>

1. INSERT, UPDATE

문제1) 황진이를 '프로필' 테이블에 등록

INSERT INTO 프로필(이름, 신장, 컵) VALUES('황진이', '170cm', 'D')

문제2) 황진이를 'D컵모델' 테이블에 등록

INSERT INTO D컵모델(이름, 신장)

SELECT 이름, 신장 FROM 프로필 WHERE 컵='D';

문제3) 황진이에게 100만원 보너스 주기

UPDATE 프로필 SET BONUS=BONUS+100만원 WHERE 이름='황진이';

<SQL -DCL>

예제1) 김정은에게 인민에 대한 모든 것을 위임+다른 이에게 이 권한 부여할 수도 있게

GRANT ALL ON 인민 TO 김정은 WITH GRANT OPTION;

예제2) 김정은의 모든 권한 취소

REVOKE ALL ON 인민 FROM 김정은;

예제3) 권한 부여하는 권한까지 취소+이미 부여한 권한들 모두 취소

REVOKE GRANT OPTION FOR ALL ON 인민 FROM 김정은 CASCADE;

<정규화>

1. 이상(Anomaly)

: 속성의 종속으로 인한 Data 중복으로 발생

종류 3가지

ㄱ. 삽입: 원치 않는 값 때문에 삽입 불가

ㄴ. 삭제: 원치 않는 것도 삭제됨

ㄷ. 갱신: 일부 튜플 정보만 갱신돼 Inconsistency(불일치성) 발생

2. 부분 함수적 종속

PK: 학번, 과목번호일 때

학번에만 종속될 경우 => 부분 함수적 종속

둘 다에 종속될 경우 => 완전 함수적 종속

*학번이 전공 결정할 경우

전공은 학번에 '종속'된다(학번 -> 전공)

3. 정규화 과정

비정규형

|-------------> 도메인에서 원자값 아닌 속성 분리

1NF(제1정규형)

|-------------> 부분 함수적 종속 제거

2NF(제2정규형)

|-------------> 이행적 함수적 종속 제거

3NF(제3정규형)

|-------------> 결정자가 후보키가 아닌 함수 제거

BCNF

      |-------------> 다치 종속성 제거(A ->>B): 복합 속성 {A,C}에 대응하는 B가 A에만 종속될 경우 

4NF(제4정규형)

*이행적 함수적 종속: A->B, B->C 일 때 A->C 만족하면

------------------------------------------------------------------------

<트랜잭션> 논리적 기능 작업 단위

1. 트랜잭션 특성 4가지 -ACID

Atomicity(원자성): 하나라도 오류 있으면 전부 Rollback해야

Consistency(일관성): A, B 계좌 합이 1만이면, A->B 이체해도 1만으로 같아야

Isolation(독립성): 갱신 중 데이터를 다른 트랜잭션이 접근 못 하도록

Durability(영속성): 성공적으로 완료된 트랜잭션 결과는 영구적으로 반영되야

2. 병행 제어 기법

1) Locking

:트랜잭션은 접근 전 잠금 허락 받아야함(잠금->실행->해제 순으로)

2) 2가지 종류

2단계 잠금 규약	타임 스탬프
확장(growing:잠금 수행) ->  축소(shrinking:잠금 해제)	트랜잭션 간 실행 순서 미리 결정
장점: 직렬성 보장	장점: 교착 상태 발생 X
단점: 교착 예방 불가

<암호화 기법>

	DES(Data Encryption Standard)	RSA(사람들 이름)
특징	개인키 기법  ㄴ동일한 키(암호/복호)	공개키 기법  ㄴ공개키/비밀키(관리자가 관리):비대칭적
장점	암호/복호 속도 빠름 알고리즘 단순	관리할 키 적다
단점	관리할 키 많다 (사용자 수 만큼)	암호/복호 속도 느림 알고리즘 복잡

<투명성> 4가지

위치: DB 실제 위치 몰라도 논리적 이름만으로 접근 가능

중복: 데이터 중복돼도 하나의 데이터만 존재하는 것처럼 사용

병행: 트랜잭션 병행돼도 서로 영향 X

장애: 장애 상관없이 트랜잭션 잘 작동

<고급DB>

1) DW(Data Warehouse) : 여러 계층 사용자 위한 의사결정용 DB

2) DM(Data Mart): 소규모, 단일 주제의 DW

3) Data Mining: DW에서 정보 캐내기 위한 기법

4) OLAP(Online Analytical Processing): DW의 Data를 전략적 정보로 변환

*3가지 종류(Relational, Multi-dimension, Hybrid OLAP)

*OLAP의 연산기능

Roll-up	상세 데이터 => 요약 데이터 순으로 접근
Drill-down	vise versa.
Drill-through	DW나 OLTP에 존재하는 상세 데이터에 접근
Drill-across	다른 데이터 큐브의 데이터에 접근
Pivoting	보고서의 행,열, 차원을 바꾸어 볼 수 있는 기능
Slicing	다차원 데이터를 다양한 각도에서 조회&비교
Dicing	Slicing을 더 쪼개는 기능

5) OLTP(Online Transaction P-): 실시간 처리 위한 시스템(빠른 응답 시간 요구)

6) ODBC(Open DB Connectivity)

:DB 접근 위한 API임. DB 종류 관계 없이 이용 가능하단 장점

<회복> 장애시 DB 손상 이전으로 돌아감

회복 Manager: log 이용해 모든 변화를 취소 or 재작업

취소(Undo)	재작업(Redo)
Start 有 Commit X	Start 有 Commit 有

<로그 내용>

<T0 start> <T0, 점수, 45, 100> <T0 commit>

*start, commit 모두 있으므로 Redo해야함.