2013년 3월 10일 일요일

[DB] 데이터베이스 설계


데이터베이스 설계
- 모델링을 통해서 현실 세계의 업무적인 데이터 항목들과 프로세스를
추상화한 것을 실제적으로 구현하여서 물리적으로 데이터베이스화 시키는
과정

분석, 설계, 구현 과정이 필요하다.

- 현실 세계에서 데이터베이스화하려고 하는 대상을 설정
- 요구 사항 수집 및 분석 단계, 개념적 설계 단계, 논리적 설계 단계,
물리적 설계 단계를 거치게 된다.

1. 요구 사항 수집 및 분석 단계
- 데이터베이스를 궁극적으로 사용할 구성원들의 요구 사항과 기능적
요구 사항을 함께 명시해야한다.(관리자와 사용자의 요구분석)

2. 개념적 설계 단계(요구사항으로부터 ER다이어그램을 만드는 과정)
- 엔티티를 추출, 애트리뷰트와 관계를 정의, ER다이어그램 작성

3. 논리적 설계 단계(매핑)
- ER 다이어그램을 관계형 스키마와 같은 구현 데이터 모델로 변환

4. 물리적 설계 단계
- 데이터베이스 파일에 대한 내부 저장 구조, 인덱스, 접근 경로 등을
명시
- 트랜잭션을 고려, 질의에 대한 평균 응답 시간, 초당 트랜잭션 처리 수,
트랜잭션 처리량 등


요구사항 수집 및 분석
- 구축 대상이 되는 조직에 대한 분석, 부서 간의 업무 영역, 부수의 업무
내용 등을 파악하고, 제약 사항에는 뭐가 있는지를 알아낸다.
- 기존 시스템이 있다면 이를 분석하여 문제점이나 개선할 내용을 조사한다.(데이터베이스의 보완하는 방향으로 요즘은 많이 수행한다.)
- 조직이나 기관에 대한 일반 사항
- 기존 시스템에 대한 기초 조사
- 설문지 작성
- 담당자 면담
- 현장 답사

개념적 설계
- 상향식과 하향식으로 나눌 수 있다.
- 데이터베이스 관리 시스템의 종류에 상관 없으며, 개념적 데이터 모델인
ER 모델을 사용해서 설계한 산출물은 ER다이어그램이 된다.

논리적 설계
- 데이터베이스 관리 시스템을 무엇으로 할지 선정
- 데이터베이스 관리 시스템의 스키마를 만든다.

물리적 설계(튜닝)
- 주어진 응용 프로그램에 대한 성능을 향상시키기 위해서 데이터베이스
파일의 저장 구조와 접근 경로를 결정하는 작업을 수행(인덱스와 역정규화)

설계 지침
- 현실 세계를 데이터베이스로 구축할 때는 정확하게 모델링을 해야한다.
- 두 엔티티 간에 비슷한 애트리뷰트가 많이 있다면 둘을 하나의 엔티티로
통합해야한다.
- 애트리뷰트는 단순하고 명확하며 직관적인 의미를 갖도록 해야한다.
- 각각의 사실은 한 번씩만 표현해야 한다.
- 애트리뷰트는 널값을 가지면 안된다.
- 릴레이션을 작게 만들지 않으려고 애트리뷰트에 널값을 인정하기도 한다.
- 엔티티, 애트리뷰트, 관계 중 어느 것에 해당하는지를 정하는 것이 중요
- 일반적으로 엔티티와 애트리뷰트는 명사이고, 관계는 동사인 경우가 많다.
- 애트리뷰트의 이름은 짧아야 관리하기 쉽다.

[DB] 데이터모델링


데이터모델링
- 데이터베이스를 구축하고자 하는 대상이 되는 기관에서 사용되는 데이터를 분석하여 제약 조건을 체계적으로 정의하고 개념적인 도구를 이용해서 간결하고 이해하기 쉽게 표현하는 것
조감도라고 생각하면 쉽다.
만들고자 하는 제약조건들이 어떤지 알아보고 서로간에 약속에 의해
이해하기 쉽게 만들어진 청사진(나무가 아니라 숲을 보자는 방식)
실제로 필요한 데이터를 추상화해서 데이터 모델링을 하고 그것을
기반으로 데이터베이스를 구체화한다.

데이터 모델링의 중요성
- 데이터베이스 사용자 간에 의사소통을 쉽게 한다.
- 전체적인 조화와 균형을 유지할 수 있게 한다.

데이터 모델링의 종류
개념적 데이터 모델
- 고수준의 데이터 모델(사람에게 가까운 모델)
- 전체 시스템에 대한 개념적인 정보를 나타내는 데 사용
- 개체-관계 모델(ER Model)

논리적 데이터 모델
- 표현 데이터 모델 또는 구현 데이터 모델
- 구축할 시스템의 이미지

   관계형 데이터 모델
   - 데이터베이스의 구조를 이차원의 표를 이용하여 표현하는 모델
   - 이해하기가 쉽다.

물리적 데이터 모델
- 컴퓨터 내부에서 데이터들이 실제로 어떻게 저장되는가를 표현


ER 데이터 모델
- 개념적 데이터 모델
- 엔티티, 엔트리뷰트, 관계를 이용해서 실세계를 개념적으로 표현하는
기법

ER 모델의 구성요소
엔티티
- 모델의 관리 대상
- 사람과 물건, 장소 같은 실체가 있는 것이나 개념을 엔티티로 선택
- 시스템 구축 단계까지 진행되면 파일이나 데이터베이스의 테이블로 구현
- ER 다이어그램에서 사각형으로 표현
- 고유하게 식별 되어야만 한다.

엔티티 타입
- 엔티티의 집합


애트리뷰트
- 엔티티의 구성 요소
- 엔티티 또는 관계가 갖는 성질이나 특성
- 엔티티는 반드시 하나 이상의 키 애트리뷰트를 갖고 있어서 나머지
애트리뷰트를 유일하게 정의할 수 있다.
- ER 다이어그램에서 타원으로 표현

키 애트리뷰트 - 유일하게 구별할 수 있는 애트리뷰터
복합 애트리뷰트 - 두 개 이상의 애트리뷰트로 구성
다치 애트리뷰트 - 애트리뷰트 하나에 여러 값이 들어갈 수 있는
                          애트리뷰트(학과안에 여러개의 전화번호)
유도된 애트리뷰트 - 애트리뷰트에 실제 값이 저장되어 있는 것이
           아니라 저장된 값으로부터 계산해서 얻는
   값을 사용하는 애트리뷰트(나이 계산)
부분키 - 키와 비슷하지만 완벽하게 키라고 할 수 없고 약한
엔티티에서만 사용한다. 키 애트리뷰트에 반한여 부분키
라한다.
      - 점선으로 표현

관계
- 엔티티 간의 관계를 나타내는 것으로 1:1, 1:N, N:N관계를 표현
- ER 다이어그램에서 마름모로 표현

카디널리티 비율
1:1관계 : 하나의 엔티티에 대하여 하나의 엔티티만이 관계
1:N과계 : 하나의 엔티티에 여러개의 엔티티와 관계
N:N관계 : 여러 엔티티는 여러개의 엔티티와 관계

전체 참여와 부분 참여
- 전체 참여는 두 줄로 표현, 부분 참여는 한 줄로 표현

관계 타입의 차수
- 참여하고 있는 엔티티 타입의 개수
- 순환적 관계 : 차수가 1인 관계(사수)

[DB] DB란


데이터베이스(DB)
관련있는 데이터들의 집합, 보통 관계형 데이터베이스를 사용한다.

데이터베이스 관리 시스템(DBMS, Database Management System)
데이터베이스를 만들고 관리하기 위한 소프트웨어
예) Oracle, MS Sql, My Sql 등

데이터베이스 시스템(DBS, Database System)
사용자가 데이터베이스 관리 시스템을 통하여 물리적인 데이터베이스와
소통하는 형태의 시스템, 이것이 데이터베이스 전체를 의미한다.

DBMS의 장점
- 데이터의 중복성과 불일치성 감소
- 데이터보안
- 질의 처리에 효율적인 저장 구조
- 백업과 복구
- 다양한 인터페이스
- 일관된 데이터를 유지

DBMS의 단점
- 자원이 많고 비싸다

데이터베이스 스키마
-데이터베이스의 구조와 제약조건에 대해서 분명하고 자세하게 기술한 것
-구체적으로 데이터베이스를 구성하는 애트리뷰트와 관계 등의 집합

데이터베이스 언어
DDL, DML, DCL

DDL : 데이터간의 관계 정의 데이터베이스 구조 변경
DML : 데이터 검색, 삽입, 삭제, 변경
DCL : 데이터 보안, 무결성, 데이터 복구, 병행 수행, 권한 제어 등을 정의

필드(애트리뷰트, 속성, 열, 데이터, 항목) : 정보처리를 위한 최소 단위
레코드(튜플, 행) : 하나 이상의 필드가 모인 것
파일 : 레코드들이 모여서 구성된 관련 레코드들의 집합

데이터베이스 추상화 : 복잡한 부분을 은폐하고 필요한 부분만 부각
데이터 독립성 : 특정 스키마를 변경할 때 상위 단계의 스키마에 영향을 미치지 않는 성질
3계층 스키마 : 사용자의 응용 프로그램과 물리적 데이터베이스를 분리 시키는 것이 목적
외부단계(사용자), 개념단계, 내부단계(물리적 데이터 저장)

사용자는 내부단계의 스키마를 알지 못하더라도 원하는 데이터를 질의할
수 있으므로 추상화가 크다.

논리적 데이터 독립성 : 외부 스키마나 응용 프로그램을 변경하지 않고
개념 스키마를 변경할 수 있는 것

물리적 데이터 독립성 : 개념 스키마를 변경하지 않고 내부 스키마를
변경할 수 있는 능력

즉, 상호간의 독립성을 유지해야 좋은 데이터베이스이다.

데이터 모델
- 실세계를 데이터베이스화할 때 어떻게 진행할지 결정하는 기준
- 데이터베이스를 구성하는 데이터의 성격과 의미, 데이터 간의 관계,
그에 따른 제약조걱 등에 따라 데이터베이스화가 이루어지도록 하는 도구
- 물리적, 논리적, 개념적 데이터 모델로 구성(처음 개념을 설계하고
논리적으로 만든 데이터베이스를 물리적으로 구현)

물리적 데이터 모델
- 데이터가 어떻게 컴퓨터에 저장되는지에 관심
- 레코드의 형식, 레코드 순서, 접근 경로 같은 정보

개념적 데이터 모델
- 사용자가 데이터를 어떻게 인식하는지에 관심
- 엔티티-관계 데이터 모델

논리적 데이터 모델
- 컴퓨터와 사용자 둘 다를 고려하는 데이터 모델로 사용자가 이해할 수
있게 하면서 실제로 저장될 수 있는 구조를 갖게 한다.(DBMS를 이용해
데이터베이스를 구현하는 단계)
- 계층, 네트워크, 관계, 객체지향 데이터 모델

2013년 2월 5일 화요일

[DB] 서브쿼리 예제 및 TOP-N SQL

문제1) student 테이블과 department 테이블을 이용하여 이윤나 학생과 1전공(deptno1)이 동일한 학생들의 이름과 전공이름을 출력하라.
select name, dname from student s, department d where s.deptno1 = d.deptno and
s.deptno1 = (select deptno1 from student where name = '이윤나')

문제2) professor 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하라.
select p.name, p.hiredate, d.dname from professor p, department d where p.deptno = d.deptno and p.hiredate > (select hiredate from professor where name = '송도권')

* 다중 행 sub query
in => 같은 값을 가진 다중 행 출력
all => 해당하는 서브쿼리의 범위 최대, 최소 값을 반환
any => 해당하는 서브쿼리의 값들을 모두 반환

위의 설명만으로 이해하기 어렵다. 예제를 통해서 이해해보자
문제1) emp02 테이블을 사용하여 전체 직원중 과장 직급의 최소 연봉보다 연봉이 높은 이름과 직급, 연봉을 출력하시오.
select name, position, pay from emp02 where pay > any(select pay from emp02 where position = '과장')

문제2) student 테이블을 조회하여 각 학년별로 최대 키를 가진 학생들의 학년과 이름, 키를 출력하시오.
select grade, name, height from student where height, grade in (select max(height), grade from student group by grade)

* TOP-N SQL
select alias.*, rownum from
(select employee_id, last_name, hire_date from employees order by hire_date) alias
where rownum <= 5

select * from
(select temp.*, rownum r from
(select * from board order by seq) temp)
where r between 3 and 6


### INDEX ###
DB안에 데이터를 매번 full scan하여 찾는 것은 성능과 비용이 좋지 못하다.
따라서 너무 잦은 full scan을 줄이고 일정한 검색 속도를 보장하기 위해
인덱스를 사용한다.

인덱스를 만들면 각각의 인덱스에 따른 ROWID(주소값)가 부여되고 자동적으로
B-TREE구조로 데이터가 정렬된다.

인덱스의 사용순서
1. 인덱스를 생성하면서 ROWID를 부여(한번의 full scan)
2. B-TREE 구조 정렬
3. 검색
4. B-TREE에서 인덱스에 해당하는 ROWID반환
5. ROWID로 검색

일정한 속도가 보장된다.
그러나 insert 및 update의 요청으로 인한 인덱스 데이터가 누적되기
때문에 성능면에서 좋지 못하다.

### VIEW ###
실제 데이터값은 없으나 뷰를 통해서 원하는 데이터를 테이블처럼 다룰 수
있다.
사용이유 : 조인 횟수를 줄일 수 있고 원하는 데이터만 추려놓거나
보일 필요가 없는 데이터를 숨기기 위한 용도로 사용된다.
create view emp30 as
select e.employee_id, e.last_name, d.department_name from employees e
join departments d on e.department_id = d.department_id
where d.department_id = 30

### SEQUENECE ###
- 자동증가

create sequence 테이블명_컬럼명_seq
start with 1 //시작
increment by 2 //2씩 증가
maxvalue = 10000 //최대값
cycle //최대값을 찍고 처음으로 돌아감

create sequence emp07_empno_seq;

insert into emp07 values(emp07_empno_seq.nextval, 'kim', 'IT', 1234);
시퀀스는 한번 증가하면 되돌릴 수 없다.
글번호 카운팅
1. 시퀀스
2. 기존의 글 번호 + 1

2013년 2월 4일 월요일

[DB] 서브쿼리

하위 질의문으로써 전형적으로 where절에서 사용한다.
예를 들어보자.
예) 평균급여보다 많은 급여를 받는 직원의 이름과 급여를 출력하시오.
select avg(salary) from employees
를 하여 평균급여를 구한 뒤
select last_name, salary from employees where salary > 평균급여

이렇듯 하나의 질의문의 결과를 얻기 위해 두번의 질이과정을 거치고 있다. 이는 번거로운 작업니다. 이를 해결하기 위해 서브쿼리를 사용한다.

select last_name, salary from employees
where salary > (select avg(salary) from employees)
어떤 조건을 위해 where 절에 또다시 select하는 것이 서브쿼리이다.

문제) 직무별(job_id) 최대급여자의 사원내역을 출력하라.
select * from employees where (salary, job_id) in(select max(salary), job_id from employees group by job_id)
중요한 문제!
서브쿼리로 받는 값이 두개 이상이 될때는 in을 사용하여 값을 받아야한다. (=연산자로는 두개 이상의 값을 받을 수 없다.) 그리고 where절에서 salary 컬럼만으로 직무별 최대값을 구하면 같은 직무가 중복 출력되는 것을 볼 수 있다. 그 이유는 job_id를 salary의 최대값과 같이 처리해 주지 않았기 때문에 최대급여와 같은 다른 직무도 출력되는 것이다.

글만으로는 알아보기 어렵겠지만 명령문을 유심히 살펴보고 나름의 이해를 하도록 노력하자.

- update 문에서 서브쿼리 사용
문제) 20번 부서 이름을 30번 부서 이름으로 변경하여라.
update dept02 set department_name = (select department_name from dept02 where department_id = 30) where department_id = 20

[DB] DML

* INSERT 문
insert into 테이블명(컬럼명들) values(컬럼값들)
insert into dept01(deptno, dname) values(20, 'IT')

* UPDATE 문
update 테이블명 set 컬럼명 = 수정값, 컬럼명 = 수정값 where 수정할 대상

문제) emp00 테이블에서 salary가 3000불 이상 대상자는 salary를 10%인상하여라.

update emp00 set salary = salary * 1.1 where salary >= 3000


* delete 문(Rollback 가능, TRUNCATE는 Rollback 불가능)
delete from 테이블명 where 삭제대상
delete from emp01 where dname = 'IT'

* 트랙잭션
- 하나의 작업처리 단위
- COMMIT : 트랜잭션이 성공적으로 완료
- ROLLBACK : 트랜잭션 중 에러등으로 인해 실패했을 때 트랜잭션 단위의 작업전체를 취소
- DDL(create, truncate) => 자동 커밋
- DML(delete, update, insert) => 트랜잭션 관리 대상

JDBC에서 트랜잭션 수행
try{
 1번 작업(주문내역 추가)
 2번 작업(재고내역 감소)
 3번 작업(배송내역 추가)
 COMMIT();//트랜잭션 성공
1~3번 작업이 하나의 트랜잭션이다. 따라서 2번에서 에러가 발생 했을 때 commit()이 되지 못하고 catch문으로 간다.
}catch(Exception e){
 예외발생시 이곳에서 예외처리.
 ROLLBACK();//트랜잭션 되돌리기
}

2013년 2월 3일 일요일

[DB] JOIN

2개 이상의 테이블에서 원하는 데이터를 얻기위해 사용. 찾고자 하는 데이터가 다른 테이블에 존재할 때 기본 테이블의 주키를 이용하여 다른 테이블의 컬럼을 참고할 수 있다.

JOIN을 위해 주키와 외래키에 대해 잘 알아야 한다.

오라클에 연습용 테이블을 이용해 실습을 해보자.
문제> 'King'의 부서이름을 출력하시오.
King의 부서이름은 departments라는 테이블에 department_name으로 확인할 수 있다. 그러나 'King'이라는 이름은 employees테이블의 last_name을 통해 알 수 있으니 두 테이블의 조인을 통하여 원하는 값을 얻을 수 있다. 따라서 명령문은 다음과 같다.
select department_name from employees, departments
where employees.department_id = departments.department_id
and employees.last_name = 'King'

department_id를 서로 공유하는 컬럼이기 때문에 이를 통하여 서로의 값을 참조하는 조인테이블을 만들 수 있다.

테이블 명이 길 경우 이름을 변경하는 것도 가능하다. 위와 같은 결과 같을 나타내는 명령문이다.
select d.department_name from employees e, departments d
where e.department_id = d.department_id
and e.last_name = 'King'

JOIN의 또다른 방법이 존재한다. 명령어는 다음과 같다. 이 방법을 ANSI 조인이라 한다.
select d.department_name from employees
join departments on employees.department_id = departments.department_id
where employees.last_name = 'King'

* 조인 사용방법
1. 원하는 데이터는 무엇인지 명확히하라.
2. 원하는 데이터가 어느 테이블에 있는지 확인하라.
3. 원하는 데이터가 다른 테이블에 있다면 공통된 컬럼이 무엇인지 확인하라.(department_id)

mission> 'Sales'부서에 소속사원의 이름, 입사일과 부서이름을 출력하라.
select last_name, hire_date, department_name from employees
join departments on employees.department_id = departments.department_id
where departments.department_name = 'Sales'

* 3개 조인
사원이름, 부서번호, 부서이름, 직종번호, 직종이름, 도시이름
select e.last_name, e.department_id, d.department_name, j.job_id, j.job_title,
l.city from employees e, departments d, jobs j, locations l
where e.department_id = d.department_id and e.job_id = j.job_id
and d.location_id = l.location_id

* ANSI 조인
select e.last_name, e.department_id, d.department_name, j.job_id, j.job_title,
l.city from employees e
join departments d on e.department_id = d.department_id
join jobs j on e.job_id = j.job_id
join locations l on d.location_id = l.location_id

여기까지가 INNER 조인이다. INNER 조인은 두 개 이상의 테이블의 조인시 테이블에서 공통된 값을 출력한다. 그러나 공통이 되지 않은 데이터지만 확인이 필요한 데이터를 출력할 때가 있다. 이때 OUTER 조인이 필요하다.

* OUTER 조인
데이터가 존재하는 곳을 기준으로 공통되지 않는 데이터까지 출력한다.
즉, INNER JOIN에서 누락되는 데이터를 출력하도록 할 때 사용한다.
다음의 명령문을 보자.
select employee_id, last_name, hire_date from employees
where TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'

그러면 11개의 데이터가 출력된다. 여기서 부서이름까지 출력한다면 다음과 같다.

select employee_id, last_name, hire_date, department_name
from employees e, departments d
where e.department_id = d.department_id and
TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'

이렇게되면 10개의 데이터가 출력되면서 하나의 데이터가 빠진다. 그 이유는 department_id는 존재하나 department_name이 없는 경우 INNER JOIN에 의해 데이터가 제외되기 때문이다. 즉 공통된 데이터가 아니기 때문에 제외되는 것이다. 따라서 다음과 같이 OUTER 조인을 하면 모든 데이터를 확인 할 수 있다.

select employee_id, last_name, hire_date, department_name
from employees e, departments d
where e.department_id = d.department_id(+) and
TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'


또는

select employee_id, last_name, hire_date, department_name
from employees e left join departments d on e.department_id = d.department_id
where TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'

이렇게 누락되는 컬럼에 +를 시키던지 LEFT JOIN이라고 표현하여 오른쪽 테이블을 기준으로 왼쪽 테이블에 누락된 데이터를 JOIN시킴으로써 데이터를 누락시키지 않도록한다.

그러나 실무에서는 OUTER 조인은 조심스럽다. LEFT 조인을 하게되면 full 스캔을 하게된다.
따라서 성능면에서 비용이 발생한다.

* SELF 조인
자기자신을 참조하는 조인
select A.last_name, B.last_name from employees A, employees B
where A.manager_id = B.employee_id
A의 매니저는 B의 직원아이디를 뜻한다.
SELF 조인에서 INNER 조인을 하였다. 그렇기 때문에 두 테이블의 공통의 데이터만을 출력한다.