[ SQL ] 정규화와 JOIN (등가조인)

 

하루입니다.

 

---

 

정규화는 데이터의 중복을 제거하여 보다 안정적인 자료구조를 가지게 하기 위한 단계이다. 제1정규형부터 제5정규형 + BCNF의 단계를 거치며, 일반적으로 제3정규형까지의 단계를 거치면 적절하고 중복이 없는 데이터를 얻을 수 있다.

 

제1정규화

• 하나의 컬럼은 하나의 값만 가지고 있어야 한다.

 

여기 학생정보 테이블이 있다. 홍길동이 수학, 영어라는 두 가지 값을 가지고 있다. 한 속성이 하나의 값만 가질 수 있도록 바꾸겠다.

 

제1정규화 완료! 더이상 나눠지지 않는 원자값으로 만들었다!

 

 

 

제2정규화

• 테이블의 모든 컬럼이 완전 함수적 종속을 만족해야 한다.(부분종속이 없어야 한다.)
• 부분종속이 없어야 한다? 예를 들어 학과장은 학과에도 속하고 이름에도 속할 수 있겠지만 이미 학과에 속하는 상황에서 이름에 부분종속될 필요가 없다고 이해했다. 꼭 있어야 할 필요가 없다. 중복된다.
• 학생(학번(유일한 값) - 이름 - 소속학과), 수강정보(이름 - 수강과목), 학과(학과 - 학과장)으로 나눈다.
• 아 ... 그러니까 찾는다 라는 개념으로 이해해도 되려나? 101 홍길동이 있다면 학과를 찾을 수 있다. 학과명이 있다면 학과장을 찾을 수 있다. 이름이 있다면 그 이름이 듣는 수강과목을 찾을 수 잇다. 만약 학번 - 이름 - 학과명 - 학과장으로 있다면 학생 이름으로도, 학과명으로도 학과장을 찾을 수 있기에 학과라는 테이블로 분리시킨다 (부분종속을 없앤다)라고 이해하고 있다.
• 이름은 기본키인 학번에 완전히 종속되어야 한다. 학과장은 학과에 종속되어야 한다. 종속관계가 아닌 것이 한 테이블에 있으면 안 된다. 김교수는 수학과의 김교수지 101 홍길동의 김교수가 아니다. 

 

 

 

제3정규화

• 이행적 종속을 없애도록 테이블을 분해해야 한다. 위에서는 이미 다 분해됐지만 ...
• 예를 들어 이름 - 학과 - 학교가 있을 때 학과는 이름에 종속되고, 학교는 학과에 종속된다. A -> B, B -> C 형태의 이행적 종속이 존재한다. 이를 이름 - 학과, 학과 - 학교로 나눠야 한다는 것이다.

 

 

 

JOIN (중요!!!)

• 문제점이 생겼다. 정규화 후 데이터들이 흩어져서 정보가 한 눈에 들어오지 않는다. 홍길동의 학과장을 알고 싶다면 어떻게 해야 할까? 이럴 때 조인을 사용한다. 학생 테이블과 학과 테이블을 조인한다.

SELECT *
FROM STUDENT, DEPT;

 

• 문제점이 또 생겼다. 조인하면 행이 두 테이블을 곱한것만큼 생긴다는 것이다. 학생*학과의 경우는 3*2로 6개의 행이 생긴다. 아래처럼.

• 여기서 제대로 된 행만을 찾아야 한다. 제대로 된 행이란 여기서는 학과명이 서로 같은 테이블이다. 왜냐면 홍길동의 학과장을 찾는다는 건 홍길동의 학과명을 찾는다 (학생 테이블) X 학과명의 학과장을 찾는다 (학과 테이블) 이렇게 이어지기 때문이다.
• 그래서 불필요한 행들을 어떻게 없앨 것이냐? WHERE로 학생.학과명 = 학과.학과명인 것을 찾으면 된다. 이것을 JOIN조건이라고 한다. JOIN은 조인조건 없이 거의 사용하지 않는다.

SELECT STUDENT.학번, STUDENT.이름, DEPT.학과장
FROM STUDENT, DEPT				-- 조인
WHERE STUDENT.학과명 = DEPT.학과명;		-- 조인조건

 

• 이때 테이블명이 너무 길다면 별칭을 줄 수 잇다. FROM에서 준다.
• 왜 얘는 WHERE에서 별칭을 사용할 수 있을까? 아마 SQL의 실행순서 때문인 듯. 저번에 실행 순서가 FROM WHERE SELECT ORDER BY라고 배웠다. FROM에서 별칭을 지정했기에 WHERE에서 사용할 수 있는 듯하다.

SELECT S.학번, S.이름, D.학과장
FROM STUDENT S, DEPT D			-- 조인
WHERE S.학과명 = D.학과명;		-- 조인조건

 

 

 

---

 

 

JOIN문

• JOIN문은 어떤 테이블에 어떤 값이 있는지, 어떤 값이 같은지를 알아야 사용할 수 있다.

 

관계테이블

- EMPLOYEES의 JOB_ID는 JOBS의 JOB_ID를 참조한다. 

- 직원들은 하나의 직업아이디를 가지지만, 직업아이디에는 여러명의 직원이 소속되어 있다.

 

- EMPLOYEES의 DEPARTMENT_ID는 DEPARTMENTS의 DEPARTMENT_ID를 참조한다. 

- 직원들은 하나의 부서아이디를 가지지만, 부서아이디에는 여러명의 직원이 소속되어 있다.

 

- EMPLOYEES의 MANAGER_ID는 EMPLOYEES의 EMPLOYEES_ID를 참조한다.

- 직원 중 매니저들은 하나의 직원아이디를 가지지만, 직원아이디에는 여러명의 매니저가 소속되어 있다

 

- DEPARTMENTS의 MANAGER_ID는 EMPLOYEES의 EMPLOYEES_ID를 참조한다.

- 부서 담당자들은 하나의 직원아이디를 가지지만, 직원아이디에는 여러명의 부서 담당자가 소속되어 있다.

 

- DEPARTMENTS의 LOCATION_ID는 LOCATIONS의 LOCATION_ID를 참조한다.

- 부서의 로케이션 아이디는 하나의 로케이션 아이디를 가지지만, 로케이션 아이디에는 여러개의 부서 로케이션이 소속되어 있다.

 

- F-P 연결된다. P가 PRIMARY KEY, F는 FOREGIN KEY로 외래키라고 한다.

 

 

 

 

JOIN문 실습

• 조인이란 2개 이상의 테이블을 연결해서 데이터를 조회하는 것이다. 
• FROM절에는 연결할 테이블들을, WHERE절에는 조인조건을 지정한다.
• 조인조건의 최소 갯수는 연결한 테이블 수 - 1이다.

-- 테이블 2개 합친다
SELECT 별칭1.컬럼명, 별칭1.컬럼명, 별칭2.컬럼명, 별칭2.컬럼명
FROM 테이블1 별칭1, 테이블2 별칭2
WHERE 별칭1.컬럼명 = 별칭2.컬럼명

-- 테이블 3개 (이상) 합친다
SELECT 별칭1.컬럼명, 별칭1.컬럼명, 별칭2.컬럼명, 별칭2.컬럼명, 별칭3.컬럼명, 별칭3.컬럼명
FROM 테이블1 별칭1, 테이블2 별칭2, 테이블3 별칭3
WHERE 별칭1.컬럼명 = 별칭2.컬럼명
AND 별칭1.컬럼명 = 별칭3.컬럼명

• 역시 ... 이렇게만 보면 잘 모르겠으니 ... 빨리 실습하러 갑시다 ... 

 

 

등가조인

• 조인하는 테이블의 특정 컬럼값이 서로 같은 것끼리 조인해서 원하는 데이터를 조회하는 것

 

• 직원아이디, 직원이름, 소속부서아이디, 소속부서명 조회하기
• 직원아이디, 직원이름, 소속부서아이디는 EMPLOYEES
• 소속부서아이디, 소속부서명은 DEPARTMENTS에 있다.
• 소속부서아이디가 같은 것을 WHERE로 건다.

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DAPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY EMPLOYEE_ID;

 

• 부서아이디, 부서명, 부서 소재지 도시명, 부서 소재지 주소 조회하기
• 부서아이디, 부서명은 DEPARTMENTS
• 부서 소재지 도시명, 부서 소재지 주소는 LOCATIONS에 있다.
• 두 테이블에서 같은 값은 LOCATION_ID다.

SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, L.STREET_ADDRESS, L.LOCATION_ID
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID
ORDER BY DEPARTMENT_ID;

 

• 부서 관리자의 아이디가 null이 아닌 부서의 부서아이디, 부서명, 관리자아이디(직원아이디), 관리자이름(직원이름)을 조회하기
• 부서 아이디, 부서명은 DEPARTMENTS
• 관리자아이디, 관리자이름은 EMPLOYEES에 있다.
• 왜 d.manager_id = e.employee_id인가?
• 일단 직원 매니저와 부서 매니저는 전혀 상관 없다 어쩌다 이름 겹친 거 ... 직원 매니저는 해당 직원들을 담당하는 상관, 부서의 매니저는 부장이다.
• 부서의 매니저는 결국 직원이기 때문이다 = 부서 매니저의 아이디는 직원 아이디에 있어야만 한다 = 겹치는 것이 올바른 데이터이다. 

SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANEGER_ID, E.FIRST_NAME
FROM DEPARTMENT D, EMPLOYEES E
WHERE D.MANEGER_ID IS NOT NULL
AND D.MANEGER_ID = E.EMPLOYEE_ID
ORDER BY D.DEPARTMENT_ID;

 

 

---

 

 

잘못된 정보에 대한 지적은 언제나 환영입니다. 

 

복습 끝!