[MySQL][자바/Java] 조인 / 유스케이스 다이어그램 / DB 모델링

조인 (Join)

• 클래스 간의 연관을 해징. -> 필드에 선언하고 setter와 생성자를 통해 주입.
• 테이블 간의 관계. 테이블 간의 연관. Foreign 키를 가진 게 자식(의존). Foreign이 없으면 아무런 관계가 없음.
• 조인 : 보통 두 개 이상의 테이블인데 실제로는 하나 이상의 테이블에서 쿼리문 걸어줄 때 사용(셀프 조인이라는 게 있음)
• 쿼리문 잘 짜는 사람은 step by step으로 풀어내는 사람.

-- JOIN %%%%%%%%%%%%%%%%%%%

SELECT * FROM emp;
SELECT DISTINCT deptno FROM emp; -- 40번 부서의 직원이 없는데
SELECT * FROM dept; -- 40번 부서는 있음.
-- 이런 것을 Multiplicity라고 함.

-- 특정 사원인 소속된 부서의 정보를 검색(사원의 정보 + 부서의 정보)
/*
Cartesian Product 카르테션 프로덕트
단순 데이터의 곱으로 결과를 산출하는 방식
14 x 4 = 56

Cartesian Product가 도출되는 경우
1) 조인 조건을 안 줬거나
2) 조언 조건을 잘못 줬거나 
-> 결론 : 조인 조건을 잘 줘라.

JOIN 조건(두 개의 테이블을 연결시키는 조건) 부여하는 방법
1) WHERE절을 이용해서 각각 테이블의 공통의 컬럼을 명시한다
2) WHERE emp.deptno = dept.deptno
*/
SELECT * FROM emp, dept; -- Cartesian Product

-- 1) 컬럼명을 일일이 명시해야 한다... 불필요한 컬럼이 출력되는 것을 방지한다.
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
-- 오류는 없지만 안 좋은 쿼리. 현업에서 * 쓰면 안 됨. 
-- 내가 만들어놓은 것을 재사용할 것이므로. 
-- 프로젝션 : 내가 나열하고자 하는 컬럼명만 명시해야 함. 
-- 셀렉션 : 내가 원하는 행만 디스플레이하겠다.

-- 2) deptno 컬럼이 어느 테이블에 있는지 모호... 컬럼명 앞에 table alias를 부여한다
-- 질의를 하는 데 있어서 굉장한 서버 장애를 가져옴.
-- 에러가 뜨는데 이럴 때는 에러가 나는 게 좋음. 
-- emp 테이블 뒤져서 empno 있고 dept 테이블 뒤져서 없으면 디스플레이.
-- 뒤져서 두 테이블 다 있으면 모호해지는 거임.
SELECT empno, ename, sal, deptno, dname, loc FROM emp, dept 
WHERE emp.deptno = dept.deptno;

-- 3) 컬럼명 앞에 테이블명을 일일이 다 붙이는 게 너무 번거롭다.
-- 성능 장애를 막기 위해 table alias는 다 붙여준다.
-- 그런데 번거로움.
SELECT emp.empno, emp.ename, emp.sal, emp.deptno, dept.dname, dept.loc 
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

-- 4)
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

-- 사원의 이름, 급여, 부서번호, 부서명을 검색. 단 급여가 2000 이상이고 30번 부서에 한해서만.
-- WHERE 절에서 조인 조건과 함께 비조인 조건이 쓰인다.
SELECT e.ename, e.sal, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno 
AND e.sal >= 2000 
AND d.deptno = 30;

-- SELF JOIN %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
SELECT empno, ename, mgr FROM emp WHERE ename = 'BLAKE'; -- 알리야스 e
SELECT empno, ename FROM emp WHERE empno = 7839; -- 알리야스 m
-- emp 테이블만 건드림. 한 테이블에서 셀프 조인.

-- BLAKE라는 사원의 상사의 이름을 검색.. 사원번호, 사원이름, 상사번호, 상사이름
-- 특정 사원의 상사의 이름을 검색
-- 1) 
SELECT empno, ename, mgr FROM emp; -- 해당 사원의 상사번호를 검색
SELECT empno, ename FROM emp; -- 위의 상사번호가 사원번호인 사람을 검색

-- 2) FROM 절 뒤의 서브쿼리를 씀. 조인 조건을 안 줘서 Cartesian Product
SELECT * FROM (SELECT empno, ename, mgr FROM emp) e,
(SELECT empno, ename FROM emp) m;

-- 3)
SELECT *
FROM (SELECT empno, ename, mgr FROM emp) e,
(SELECT empno, ename FROM emp) m
WHERE e.mgr = m.empno; 
-- KING은 상사가 null이라서 안 나옴. 그래서 14줄 나와야 하는데 13줄 나옴.
-- KING도 나오게 하고 싶으면 아래 OUTER JOIN으로 해결 가능.

-- 4)
SELECT e.empno 사원번호, e.ename 사원이름, m.empno 상사번호, m.ename 상사이름
FROM (SELECT empno, ename, mgr FROM emp) e,
(SELECT empno, ename FROM emp) m
WHERE e.mgr = m.empno;

-- 5)
SELECT e.empno 사원번호, e.ename 사원이름, m.empno 상사번호, m.ename 상사이름
FROM (SELECT empno, ename, mgr FROM emp) e,
(SELECT empno, ename FROM emp) m
WHERE e.mgr = m.empno 
AND e.ename = 'BLAKE';

-- OUTER JOIN %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
/*
	A,B 테이블을 조인할 경우, 조건에 맞지 않는 데이터는 디스플레이되지 않는데...
    이 경우도 디스플레이 하고 싶을 때 outer join을 사용한다.
    
    OUTER JOIN의 종류
    1) LEFT OUTER JOIN   2) RIGHT OUTER JOIN  3) FULL OUTER JOIN
    (데이터가 어느 쪽에 있는가에 따라서 종류의 이름이 정해짐)
    -- FULL OUTER JOIN은 mysql의 종류인데 안 먹음. 그럴 때 어떻게 해야 하는지 다룰 것.
*/

-- 사원의 이름, 부서번호, 부서이름을 검색...
SELECT e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno; 
-- 40번 부서에 emp에 없어서 표시 안 됨. 
-- 하지만 표시하고 싶을 수 있는데 그 때 OUTER JOIN으로 해결
-- 40번 부서는 dept에는 있는데 emp에는 없음. FROM절에서 오른쪽에만 있음.

-- 1) 위 경우에 RIGHT OUTER JOIN을 사용
-- 조인수행시 우측 테이블(DEPT)이 기준이 되어서 결과를 생산하도록 해준다.
SELECT e.ename, d.deptno, d.dname
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

-- LEFT로도 변경 가능.
SELECT e.ename, d.deptno, d.dname
FROM dept d LEFT OUTER JOIN emp e ON e.deptno = d.deptno;

-- 특정 사원의 상사의 이름을 검색... 13줄 검색..
-- CONCAT은 문자열 결합
/*
SELF 조인의 결과 KING의 사원정보가 빠져있다.
KING은 MGR이 NULL..이라서
*/
SELECT CONCAT(e.ename, '의 매니저는 ', m.ename, '입니다') Info
FROM emp e, emp m
WHERE e.mgr = m.empno;

SELECT CONCAT(e.ename, '의 매니저는 ', m.ename, '입니다') Info
FROM emp e LEFT OUTER JOIN  emp m ON e.mgr = m.empno;
-- emp의 mgr이 없으므로 LEFT

-- A  10, 20, 30   FULL OUTER JOIN    B  10, 20, 40 
-- 10,20,30,40 도출됨. 중복을 제외하고 합침. 합집합과 같다.
-- FULL OUTER JOIN이 실행되지 않음으로 UNION 연산자를 mysql에서 사용해야 한다.
/*
FULL OUTER JOIN을 실습할 수 있는 간단한 테이블을 만들고 예제를 풀어보도록 한다.
*/
CREATE TABLE outer1(sawonid int);
CREATE TABLE outer2(sawonid int);

INSERT INTO outer1 VALUES(10);
INSERT INTO outer1 VALUES(20);
INSERT INTO outer1 VALUES(40);

INSERT INTO outer2 VALUES(10);
INSERT INTO outer2 VALUES(20);
INSERT INTO outer2 VALUES(30);

SELECT * FROM outer2;

-- FULL OUTER JOIN 사용
/*
SELECT sawonid FROM outer1
FULL OUTER JOIN
SELECT sawonid FROM outer2;
이거 에러뜸
*/

SELECT sawonid FROM outer1
UNION
SELECT sawonid FROM outer2;
-- outer1을 먼저 디스플레이하고 중복을 제외하고 outer2를 디스플레이함
-- 그래서 정렬이 필요

-- 정리 
-- LEFT OUTER JOIN : 왼쪽 테이블 다 가져옴
-- RIGHT OUTER JOIN : 오른쪽 테이블 다 가져옴
-- FULL OUTER JOIN : 중복을 제외하고 양 테이블 다 가지고 옴

---

UseCase Diagram & DB Modeling

• 기술면접에서 미들서버에서 예외처리 어떻게 했는지 물어봄.
• db에 id가 있는지 없는지 확인
• 특정 사람 삭제할 때 레코드 있는지 먼저 확인. db에 있는 데이터를 핸들링(CRUD)할 때에는 반드시 존재 유무를 확인해야 한다. 가장 중요한 기본.
• 예외를 너무 디테일하게 하면 예외 때문에 프로그래밍을 못함. 프로그래밍할 때 예외를 어느 정도까지 할지 어느 정도 합의를 해야 함.

 

< 주식 프로그램 >

• 상장된 주식을 DBA(Database Administrator)가 DB 테이블에 넣음. 그 테이블에 들어있는 데이터 주식 정보를 긁어옴. 코드를 짤 때 10초에 한번씩 긁어오도록 전광판에 주식에 대한 정보를 뿌림. 주식 CRUD는 프로그램의 몫이 아님. DBA가 넣음. 불러올 뿐. 고객 CRUD가 프로그램이 함.
• UseCase Diagram 완성되면 엔터티 추출. 고객, 주식 추출. 고객 => 주식을 보유한 고객과 보유하지 않은 고객

• DB 모델링을 한다.

• Customer와 Stock은 다대다 관계(한 고객이 여러 개의 주식을 보유함, 주식 하나가 여러 고객들에게 보유됨) -> 현실 세계에서는 다대다 관계가 많이 있지만 프로그래밍에서는 다대다 관계를 해소해야 함 -> 중간에 Association Entity로 Shares를 하나 둔다. 그러면 1대다, 1대다로 됨.
• 고객 - 주식 중간에 있는 Shares에서 고객, 주식의 외래키를 가지고 고객이 보유하고 있는 주식 수량을 속성으로 가짐.
• shares는 양쪽의 외래키와 함께 자신만의 일반 속성 quantity를 다 통해서만 이해될 수 있음. 이 모든 정보를 다 조합해야 함. 기본키 없음.

• Customer, Stock, Shares 테이블 생성(broker.sql 파일에서 가져옴)

 

---

작업순서

1. 사용자적 입장에서 기능을 분석 —> UseCase Diagram // 이 부분이 DB Modeling

2. 엔터티 추출, 기본키 지정 —> 개념 설계 과정

3. 일반 속성 지정, 엔터티 간의 관계를 설정

   이때 다대다 관계는 Association Entity로 해소시켜줘야 한다

   —> 논리적 설계 —> 물리적 설계(테이블의 실질적인 구성)

   가장 중요한 과정이 논리 설계라고 할 수 있다.

UI 설계

1. VO클래스 작성

2. Business Logic Template 작성

• 다대다 관계일 때는 부모, 자식 그런 거 없음.
• 다대다 관계 중간에 있는 ORDER
• ORDER_CODE 이렇게 PK를 줄 수도 있음.
• PK를 넣고 안 넣고의 차이 → PK를 지정하게 되면 PK 하나만 가지고 모든 정보를 뽑아낼 수 있음. association에 PK가 없으면 양쪽 정보를 다 취합해야 한다.

 

• 예외(사용자 정의 예외)
• - broker.twotier.exception
• - DuplicateSSNException (ex : 이미 등록된 회원일 때)
• - RecordNotFoundException (ex : 삭제할 회원이 없을 때)
• - InvalidTransactionException (ex : 고객이 100주 가지고 있는데 150주 팔려고 할 때)

 

• DB 접속할 때마다 Connection! Connection 몇 개까지 커버리지 할 수 있는가? -> 여기에 따라 서버의 성능이 좋다, 나쁘다가 결정됨.