231115 DB - ERD, JOIN 1

HR 스키마 - ERD 만들기

ERD(Entity-Relationship Diagram)

개체(테이블)와 개체 사이의 관계도

 

데이터 모델링

테이블들을 설계해서 생성하는 일련의 과정

 

데이터 모델링 순서

1. 요구사항 수집 및 분석
2. 개념 모델링
   주요 개체(테이블)와 관계를 결정하는 단계. 테이블, 컬럼, 테이블과 테이블의 관계 결정. 결과물 → ERD(뼈대)
3. 논리 모델링
   개념모델링의 결과를 구체화 시키는 단계. 제약조건, 데이터타입, 컬럼 사이즈 등 결정. 결과물 → 구체화된 ERD
4. 물리 모델링 : 논리모델링의 결과를 토대로 테이블 차트 및 생성. 작업을 하는 단계 결과물 → 테이블 차트, 테이블 생성

역모델링?

현재 운영되고 있는 테이블을 토대로 구체화된 ERD를 그리는 작업

메뉴 [Home] - [Models] - [Create EER Model from Database]

그림 1. HR 스키마 → ERD 1

그림 2. HR 스키마 → ERD 2

그림 3. HR 스키마 → ERD 3

---

Join

여러 테이블의 정보(컬럼)를 함께 출력하기 위해서 사용하는 구문

select 컬럼1,컬럼2,.... 
from 테이블 A join 테이블 B 
on 테이블A.컬럼=테이블B.컬럼 
[where 조건문] 
[order by 컬럼명];

select * 
from 첫 번째 테이블 이름 join 두 번째 테이블 B 
on 조건

select * 
from 첫번째 테이블 이름 별칭1 두번째 테이블 이름 별칭2 
where 조건

 

-- employees 테이블과 departments 테이블을 사용해서 직원 정보(employee_id, last_name, job_id, salary)와 직원이 소속된 부서 정보(department_id, department_name)를 함께 출력

select employee_id, last_name, job_id, salary, departments.department_id, department_name
from employees join departments
on employees.department_id = departments.department_id
order by employee_id;
-- 또는
select employees.employee_id, employees.last_name, employees.job_id, employees.salary, departments.department_id, departments.department_name
from employees join departments
on employees.department_id = departments.department_id
order by employees.employee_id;
-- 또는
select e.employee_id, e.last_name, e.job_id, e.salary, d.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id
order by e.employee_id;

그림 4. ex1 결과

-- departments 테이블과 locations 테이블을 사용해서 부서 정보(department_id, department_name, manager_id)와
부서의 위치 정보(location_id, city)를 함께 출력

select d.department_id, d.department_name, d.manager_id, l.location_id, l.city
from departments d join locations l
on d.location_id = l.location_id
order by d.department_id;

그림 5. ex2 결과

-- employees 테이블과 departments 테이블을 사용해서 부서 정보(department_id, department_name, manager_id)와 부서의 매니저 이름(last_name)을 함께 출력하시오.
select d.department_id, d.department_name, d.manager_id as "매니저 ID", e.last_name as "매니저 이름"
from departments d join employees e
on d.manager_id = e.employee_id;

그림 6. ex3 결과

-- employees 테이블과 jobs 테이블을 사용해서 직원 정보(employee_id, last_name, email, job_id)와 직원의 담당 업무명(job_title)을 함께 출력하시오.
select e.employee_id, e.last_name, e.email, e.job_id, j.job_title
from employees e join jobs j
on e.job_id = j.job_id
order by e.employee_id;

그림 7. ex4 결과

 

n개 테이블 조인하기

테이블수/조인건수

2 / 1

3 / 2

n / n-1

 

-- employees, departments, locations 테이블을 사용해서 직원 정보(employee_id, last_name, salary)와 직원이 소속된 부서 정보(department_id, department_name)와 부서의 위치 정보(location_id, city)를 함께 출력하시오.

select e.employee_id, e.last_name, e.salary, d.department_id, d.department_name, l.location_id, l.city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
order by e.employee_id;
-- 또는
select e.employee_id, e.last_name, e.salary, d.department_id, d.department_name, l.location_id, l.city
from employees e join departments d join locations l
on e.department_id = d.department_id and d.location_id = l.location_id
order by e.employee_id;

그림 8. ex5 결과

 

Self-join(자체조인)

자기 자신 테이블과 조인하는 유형,

하나의 테이블을 마치 다른 테이블인 듯 테이블 alias를 다르게 부여해서 조인하는 방법.

 

-- employees 테이블로부터 직원의 정보(employee_id, last_name, salary, manager_id)와 직원의 매니저 이름(last_name)을 함께 출력하시오.

select e1.employee_id, e1.last_name, e1.salary, e1.manager_id as "매니저 ID", e2.last_name as "매니저 이름"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
order by e1.employee_id;
-- 또는
select e1.employee_id, e1.last_name, e1.salary, e2.employee_id as "매니저 ID", e2.last_name as "매니저 이름"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
order by e1.employee_id;

그림 9. ex6 결과

-- employees 테이블과 departments 테이블을 사용해서 직원 정보(employee_id, last_name, salary, hire_date)와 직원이 소속된 부서 정보(department_name)를 함께 출력하되 급여가 8000이상인 사원만 출력하고, 사번을 기준으로 오름차순 정렬하시오.

select e.employee_id, e.last_name, e.salary, e.hire_date, d.department_name
from employees e join departments d
on e.department_id = d.department_id
where e.salary >= 8000
order by e.employee_id;

그림 10. ex7 결과

 

그림 11. 연습문제 1

select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;
-- 또는
select e.employee_id, e.last_name, e.salary, d.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

그림 12. 연습문제 1 결과

 

그림 13. 연습문제 2

select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e1.manager_id as "Mgr#"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;
-- 또는
select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e2.employee_id as "Mgr#"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;

그림 14. 연습문제2 결과

 

-- [오답] 사원 정보와 사원의 매니저 이름과 매니저의 매니저 ID가 출력됨!
select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e2.manager_id as "Mgr#"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;