[오라클]서브쿼리, 무결성, 트랜잭션, DDL, 정규화

▶ PC를 킨 후, 오라클이 실행되지 않을 때 서비스 프로그램 킨 후 콘솔 프로그램 상태가 시작됨인지 확인.

오라클 서비스 프로그램

SUBQUERY를 활용하여 데이터 UPDATE

--주소가 제주도인 고객의 월급을 500원 인상
--해당 데이터 조회
SELECT * FROM EXCOMPANY
WHERE USERID IN(SELECT USERID FROM EXCUSTOM WHERE ADDR1 = '제주도');

--해당 데이터 변경
UPDATE EXCOMPANY SET PAY=PAY+500
WHERE USERID IN(SELECT USERID FROM EXCUSTOM WHERE ADDR1 = '제주도');

--UPDATE 
SELECT * FROM PERSONNEL WHERE PNO = 1111;
UPDATE PERSONNEL SET DNO=30 WHERE PNO = 1111;

SELECT * FROM SAWON;
UPDATE SAWON SET DNO=20; 

SELECT * FROM PERSONNEL;

UPDATE PERSONNEL SET DNO=10,PNAME='SUZI',JOB='SINGER',MANAGER=0000,PAY=5000,STARTDATE=SYSDATE
WHERE PNO = 1111;

--DELETE (FROM은 생략 가능)
SELECT * FROM PERSONNEL WHERE PNO=1112;
DELETE FROM PERSONNEL WHERE PNO=1112;

SELECT * FROM BUSEO;
DELETE BUSEO;

ROLLBACK; --실행취소

제약조건 위반으로 에러가 발생하는 경우(CONSTRAINTS : 제약조건)

--제약조건 조회
SELECT * FROM USER_CONSTRAINTS;

--INSERT에러
SELECT * FROM PERSONNEL;
INSERT INTO PERSONNEL (PNO,PNAME,DNO) VALUES(7711,'SONG',99); --X
--(PERSONNEL_PNO_PK) : PRIMARY KEY 이므로 중복허용안함

--UPDATE 에러
SELECT * FROM DIVISION;
SELECT * FROM PERSONNEL;
UPDATE PERSONNEL SET DNO=55 WHERE DNO=20; -- X
UPDATE PERSONNEL SET DNO=50 WHERE DNO=20; -- O, DIVISION테이블에 50이 있으므로 사용 가능
--여기서 DIVISION 테이블에 DNO=50을 지우게 되면 PERSONNEL 테이블에 DNO=50인 데이터들은 고아데이터가 됨 (무결성이 깨짐)
--무결성 제약조건(KIM.PERSONNEL_DNO_FK)이 위배되었습니다- 부모 키가 없습니다
--PERSONNEL_DNO_FK 참조키(REFERENCE KEY)이므로 수정 안됨.

 

 무결성 제약조건

1. PRIMARY KEY : 중복,NULL 허용 안함
2. UNIQUE KEY : 중복값 허용 안함. NULL은 MS에서는 1번 허용, 오라클에서는 계속
3. CHECK KEY : 나이>=20 제약조건이 있으면, 무결성 제약조건을 체크할 때  20이상인 경우만 입력가능
4. NOT NULL KEY : NULL이 오면 안됨
5. FOREIGN KEY : 외래키. 외부참조키. 참조하는 컬럼은 참조되는 컬럼과 데이터형이 일치해야 함.
6. DEFAULT KEY : 초기값을 남,여 세팅해놓는 키

- A계좌에서 B계좌로 500원 송금 시 프로세스

1. A계좌 : 1000 , B계좌 : 500
2. A계좌확인
3. A계좌 잔액 확인 (1000)
4. A계좌에서 500원 차감 (500)
5. B계좌 확인
6. 500원 송금
7. B계좌 잔액 확인 (500)
8. B계좌 잔액 증가 (1000)
9. 완료

▶ 트랜잭션의 기능(크게 두가지)

① 내가 하고자 하는 작업은 하나지만 이루어지는 프로세스 단계는 여러개. 진행중 하나라도 실행이 되지 않는다면 해당 작업이 이루어지지 않음(진행되었던 작업들을 취소시킴. - ROLLBACK). 송금은 하루에도 수십만건이 발생. 오라클은 이 일거수일투족을 LOG파일에 저장함. LOG파일은 일종의 장부와 같은 역할(항상, 선기록 후조치 = 기록 후 실행함) 복구할 때 사용하므로 굉장히 중요.

트랜잭션 로그파일이라고도 부름

② LOCK  : 비행기 티켓. 구매 진행중일 때 다른 사람이 구매할 수 없도록 LOCK(잠금) 하는 것

 

- TRANSACTION

1. DML문장 시작시 트랜잭션 자동으로 시작

① INSERT, UPDATE, DELETE

② COMMIT(저장),ROLLBACK(취소) : 트랜잭션 발생시 반드시 둘 중 하나를 결정해줘야 함

③ 웹,자바,언어에서는 INSERT,UPDATE,DELETE의 경우 자동커밋

2. 오토커밋인 경우

① CREATE, ALTER, DROP

② EXIT

이미 DNO = 55로 데이터를 삽입했는데, 다른 사용자가 똑같은 내용으로 데이터를 삽입 하려하면 LOCK이 걸림

입력한 사용자가 COMMIT 이나 ROLLBACK 을 결정하지 않았으므로 나중에 실행한 사용자가 LOCK 상태가 됨.

위 콘솔에서

• COMMIT 이 이뤄지게 되면 LOCK상태에서 무결성 제약조건에 위배된다고 조회
• ROLLBACK 이 이뤄지게 되면 LOCK상태에서 INSERT문이 성공적으로 실행.

 

[DDL(CREATE, ALTER, DROP) 활용 기초]

1. DATA TYPE

- CHAR :  문자(고정길이)

- VARCHAR2 :  문자(가변길이)

- NUMBER(P,S) :  숫자(가변길이) EX) NUMBER(6,2) :  0000.00

- NUMBER만쓰면 내가 원하는 길이의 숫자를 모두 쓸 수 있다.

- DATE : 날짜(고정길이,7byte)

- LONG : 문자(가변길이,2GB

 

2. CHAR(5) [ / / / / ] , VARCHAR2 (5) [ / / / / ]

- 공통점 : 5글자를 저장할 수 있는 공간. 영대문자, 영소문자, 숫자, 특수문자 저장가능

- 차이점 : CHAR는 빈공간이더라도 반드시 5개의 공간을 가진다.. VARCHAR는 빈공간이 있을 경우 OS에 전달.

- CHAR > 저장공간의 낭비가 심하다

- 200byte의 댓글을 작성하는 메모리가 있다면. ‘메롱 ‘ 만 적혀있으면 196byte가 낭비됨

- CHAR > 속도가 굉장히 빠르다. 사이즈가 정해져 있는 경우는 CHAR사용. (이름, 학번, 주민,ZIP)

- WHERE절의 컬럼으로 자주 쓰는 변수는 CHAR로 설정함 (속도가 빠른것을 이용. 모델링시  다 따져야함)

- VARCHAR가 속도가 느린 이유는 메모리를 읽을때마다 Header를 읽어야 하기 때문. (해당 메모리에 용량이 얼마인지(몇 byte인지) 읽은 후. 해당 메모리에 대해서 읽는 것이기 때문에.)

- VARCHAR의 단점 : OS에서는 단편화 .DB에서는  CHAINING 현상. 같은 공간에서 읽어오는게 아니기 때문에 당연히 속도가 떨어짐. CHAINING 현상은 관리자의 관리가 필요함.

- 하드디스크안에서 오라클 DB가 설치되는 것.. 단편화되어 데이터베이스가 저장이되면 하드디스크에서 단편화 작업이 이뤄지면 파일의 저장위치가 변경되어 초창기에는 DB가 꺠졌었음

 

3. 테이블 이름

- 영문자로 시작

- 영문자, 숫자 사용 가능

- 특수문자는 _ , $, #만 가능

- 이름은 중복 허용 안함

- 컬럼명과 달라야 함.

- 예약어 사용불가(SELECT, FROM, VARCHAR, VALUES 등 )

- 사용가능예시 : Sawon, p_no, Div10

- 사용불가예시 : 10Div, $sal, p-no, alter

 

4. 정규화 작업 : 이상과 함수적 종속으로 인해 생기는 문제점을 해결하기 위해 릴레이션을 분해하는 과정

- 제1정규형: 주키(PRIMARY KEY)가 존재

- 제2정규형: 모든키가 아닌 컬럼들은 주키에 의존적

- 제3정규형: 키가 아닌 컬럼끼리 의존관계가 존재해선 안된다.

제1정규형	도메인 중복제거/ 원자값
제2정규형	부분함수종속제거
제3정규형	이행적함수종속제거
BCNF형	결정자간의 함수종속제거
제4정규형	다치종속제거
제5정규형	조인종속제거

 

5. 역정규화 : 일부 쓰기 성능의 손실을 감수하고 데이터를 묶거나 데이터의 복제 사본을 추가함으로써 데이터베이스의 읽기 성능을 개선하려고 시도하는 과정

▼ 제약조건 삭제할 경우 CONSTRAINT_NAME을 이용해서 삭제.

▼ 테이블_컬럼명_PK 이런식으로 회사마다 제약조건명 규칙이 있기 마련. 순서는 상관 없음