웹개발_DB 5

정규화
삽입/수정/삭제의 이상 현상을 제거하기 위한 작업.

1차 정규화
같은 성격과 내용의 컬럼이 연속적으로 나타날 경우,
하나의 컬럼에 여러 값이 연속적으로 나타날 경우

1차정규화 대상 테이블↓

상품명(콤마로 구분)
바지1	상의2	신발3

,

상품명1	상품명2	상품명3
바지1	상의2	신발3

* 조회가 너무 힘들다
↓

▶ 1차 정규화 진행

상품명

바지1

상의2

신발3

---

2차 정규화
조합키(복합키)로 구성되었을 경우 조합키의 일부분에만 종속되는 속성이 있을 경우.
부분 함수 종속(Partial Functional Dependency).

 

 

1차정규화 대상 테이블↓

이름	색상	꽃말	과
해바라기	노란색	행운	국화
장미	빨간색	사랑	장미

꽃말은 이름 색상을 참조하지만
과는 이름만 참조하는 때

* 이름에만 과가 종속된다.

▶ 2차 정규화 진행

FLOWER  TABLE

이름PK	과
해바라기	국화
장미	장미

이름:슈퍼키

 

FLOWER_LANGUAGE TABLE

이름FK(PK)	색상PK	꽃말
해바라기	노란색	행운
장미	빨간색	사랑

이름: 서브키

조합키 : 이름,색상

---

3차 정규화
이행 함수 종속(Transitive Dependency).
PK가 아닌 컬럼이 다른 컬럼을 결정하는 경우.

회원번호	이름	시	구	동	우편번호
1	송태섭	서울시	중구	소공동	11111
2	채치수	서울시	관악구	삼성동	22222
3	정대만	서울시	강남구	역삼동	33333
4	서태웅	서울시	강서구	가양동	44444
5	강백호	서울시	영등포구	당산동	55555

* 회원번호가 우편번호를, 우편번호가 시구동을 결정짓기 때문에 3차 정규화 대상이다.

▶ 3차 정규화 진행

회원번호	이름	우편번호
3	정대만	33333

 

우편번호	시	구	동
33333	서울시	강남구	역삼동

 

---

데이터베이스에서 정규화가 필요한 이유

데이터베이스를 잘못 설계하면 불필요한 데이터 중복으로 인해 공간이 낭비된다.
이런 현상을 이상(Anomaly)현상이라고 한다.

※ 회원번호와 프로젝트코드 두 컬럼의 조합키로 설정되어 있는 테이블이고 한 사람은 하나의 부서만 가질 수 있다.

회원번호 이름 부서 프로젝트코드 급여 부서별 명수

회원번호	이름	부서	프로젝트코드	급여	부서별 명수
11111	송태섭	인사팀	ABC0001	3000	4
11111	송태섭	인사팀	CBA0001	2000	4
11111	송태섭	인사팀	BCA0001	4000	4
22222	정대만	개발팀	FEW1233	5000	2
33333	채치수	기획팀	ERR4433	6000	3

이상 현상의 종류
- 삽입 이상
 새로운 데이터를 삽입하기 위해 불필요한 데이터도 삽입해야하는 문제

담당 프로젝트 코드가 정해지지 않은 신입 사원이 있다면,
프로젝트 코드에 NOT NULL 제약조건이 있다면, 이 사원은 테이블에 추가될 수 없다.
따라서 '미정'이라는 프로젝트 코드를 따로 만들어서 삽입해야 한다.

- 갱신 이상
 중복 행 중에서 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제,
 한 명의 사원은 반드시 하나의 부서에만 속할 수 있다.
 만약 "송태섭"이 보안팀으로 부서를 옮길 시 3개 모두 갱신해주지 않는다면,
 인사팀인지 보안팀인지 알 수 없다.

- 삭제 이상
 행을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 문제
"채치수"이 담당한 프로젝트를 박살내서 드랍된다면, "채치수" 행을 모두 삭제하게 된다.
따라서 프로젝트에서 드랍되면 회사에서도 드랍된다.


▶ 2차 정규화 진행

회원번호	이름	부서	부서별 명수
11111	송태섭	인사팀	4
22222	정대만	개발팀	2
33333	채치수	기획팀	3

 

회원번호	프로젝트 코드	급여
11111	ABC0001	3000
11111	CBA0001	2000
11111	BCA0001	4000
22222	FEW1233	5000
33333	ERR4433	6000

 

▶ 3차 정규화 진행

 

회원번호	이름	부서	부서별 명수
11111	송태섭	인사팀	4
22222	정대만	개발팀	2
33333	채치수	기획팀	3

회원번호	이름	부서
11111	송태섭	인사팀
22222	정대만	개발팀
33333	채치수	기획팀

 

부서	부서별 명수
인사팀	4
개발팀	2
기획팀	3

 

---

JOIN
  여러 테이블에 흩어져 있는 정보 중
  사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들고 결과를 보여주는 것.
  정규화를 통해 조회 테이블이 너무 많이 쪼개져 있으면 작업이 불편하기 때문에
  입력, 수정, 삭제의 성능을 향상시키기 위해서 JOIN을 통해 합친 후 사용한다.

 

※실습

/*EMP 테이블 사원번호로 DEPT 테이블의 지역 검색*/

SELECT * FROM EMP;
SELECT * FROM DEPT;

SELECT ENAME, LOC 
FROM DEPT D JOIN EMP E
ON D.DEPTNO = E.DEPTNO;

출력결과

---

/*SQL 실행 순서*/
/*FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY*/

/*PLAYER 테이블에서 송종국 선수가 속한 팀의 전화번호 검색하기*/
SELECT PLAYER_NAME, P.TEAM_ID, TEL 
FROM TEAM T JOIN PLAYER P
ON T.TEAM_ID = P.TEAM_ID AND PLAYER_NAME = '송종국';

출력결과

---

/*JOBS 테이블에서 JOB_ID로 직원들의 JOB_TITLE, EMAIL, 성, 이름 검색*/
SELECT * FROM JOBS J;
SELECT * FROM EMPLOYEES e ;

SELECT JOB_TITLE , EMAIL ,FIRST_NAME, LAST_NAME 
FROM JOBS J JOIN EMPLOYEES E
ON  J.JOB_ID = E.JOB_ID ;

출력결과

---

/*EMPLOYEES 테이블에서 HIREDATE가 2003~2005년까지인 사원의 정보와 부서명 검색*/
SELECT * FROM EMPLOYEES e ;
SELECT * FROM  DEPARTMENTS d; 

SELECT HIRE_DATE, DEPARTMENT_NAME,LAST_NAME,
FIRST_NAME
FROM DEPARTMENTS d JOIN EMPLOYEES e 
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID AND HIRE_DATE BETWEEN '2003-01-01' AND '2005-12-31' 
ORDER BY HIRE_DATE ;
/*TO_DATE를 사용할 때 년도만 설정하면 월은 SYDATE의 월, 일은 01로 자동설정됨*/

출력결과

---

/*JOB_TITLE 중 'Manager'라는 문자열이 포함된 직업들의 평균 연봉을 JOB_TITLE별로 검색*/
SELECT * FROM JOBS;
SELECT * FROM EMPLOYEES;

SELECT JOB_TITLE, AVG(SALARY)
FROM JOBS J JOIN EMPLOYEES E 
ON J.JOB_ID = E.JOB_ID AND JOB_TITLE LIKE'%Manager%' 
GROUP BY JOB_TITLE 
ORDER BY AVG(SALARY);

출력결과

---

/*EMP 테이블에서 ENAME에 L이 있는 사원들의 DNAME과 LOC 검색*/
SELECT * FROM EMP;
SELECT * FROM DEPT;

SELECT ENAME, DNAME, LOC
FROM DEPT D JOIN EMP E
ON D.DEPTNO = E.DEPTNO AND ENAME LIKE '%L%';

 

출력결과

---

/*축구 선수들 중에서 각 팀별로 키가 가장 큰 선수들 전체 정보 검색*/
SELECT * FROM  PLAYER p ;
SELECT TEAM_ID,HEIGHT FROM PLAYER p 
WHERE (TEAM_ID, HEIGHT) IN (
	SELECT TEAM_ID ,MAX(HEIGHT)
	FROM PLAYER
	GROUP BY TEAM_ID )
	ORDER BY HEIGHT DESC 
	;

출력결과

---

/*EMP 테이블에서 사원의 이름과 매니저 이름을 검색*/
/*셀프 조인(EMPNO 와 MGR 값이 같으면 메니저)*/
SELECT * FROM EMP;

SELECT E1.ENAME 사원, E2.ENAME 매니저
FROM EMP E1 JOIN EMP E2
ON E1.MGR = E2.EMPNO;

출력결과