VO/DAO 패턴
VO(Value Object)
- 데이터 베이스의 한 레코드를 담는 객체
- 필드, 생성자, Getter/Setter 메소드 구성
- 레코드(행)의 개수만큼 생성해서 사용한다.
* VO객체, Domain객체, DTO객체, 자바 빈즈라고 부른다.
DAO(Database Access Object)
- Database Access 작업을 담당하는 객체
- 추가/삭제/변경/조회 작업과 관련된 메소드들로 구성
* CRUD 기능을 구현한다.
* Create, Retrieve(조회), Update, Delete
- 업무 로직이 존재하지 않는다.
* 단순한 JDBC 코딩만 존재한다.
* ibatis/mybatis/hibernate/jpa 등의 DB Access 작업을 단순화시켜주는 프레임워크를 활용해서 주로 개발된다.
Service
- 업무 로직을 관련된 작업을 담당하는 객체
- 사용자가 입력한 값을 전달받아서 적절한 업무로직을 수행하고 업무 로직의 수행 결과를 DAO를 활용해서 DB에 영구적으로 보관한다.
* 업무 로직은 보통 1번 이상의 DB Access 작업을 필요로 한다.
* Service 객체의 메소드들은 DAO에 구현된 DB Access 작업을 실행해서 업무 로직에 필요한 정보를 획득하거나, 업무 로직의 수행 결과를 DB에 반영한다.
무결성 제약 조건
- 테이블에 유효하지 않은 데이터가 저장되는 것을 방지한다.
- 테이블에 행이 추가, 갱신, 삭제될 때마다 제약 규칙이 적용된다.
- 제약 조건의 종류
not null 제약 조건
해당 컬럼이 null값을 포함하지 못하도록 제약한다(이름).
unique 제약 조건(고유키 제약 조건)
해당 컬럼의 값은 테이블의 모든 행에서 고유한 값을 가지는 컬럼 혹은 컬럼의 조합이어야 한다(이메일, 주민번호, 아이디)
primary key 제약 조건(기본키 제약 조건)
해당 컬럼이 행을 대표하는 컬럼이 되도록 한다.
* null 값을 허용하지 않는다.
* 모든 행에 대해서 같이 고유해야 한다.
foreign key 제약 조건(외래키 제약 조건)
해당 컬럼의 값이 동일한 테이블이나 다른 테이블의 기본키 혹은 고유키로 지정된 컬럼의 값을 참조하도록 한다
* 참조하는 컬럼의 값들만 저장되게 한다.
* not null 제약 조건이 지정되어 있지 않으면 null값도 허용한다.
check 제약 조건
* 해당 컬럼의 값이 제시된 조건을 만족하는 값만 저장되도록 지정한다.
0415
트랜잭션
- 데이터의 일관성을 보장하기 위해서 사용한다.
- insert, update, delete 구문만 트랜잭션 처리의 대상이다.
- 한 개 이상의 단일 작업(DB Access 작업)을 논리적 작업 그룹으로 묶어서 처리한다.
트랜잭션의 시작과 종료
- 트랜잭션의 시작
- 첫 번째 DML(INSERT, UPDATE, DELETE 구문) SQL이 실행될 때 자동으로 시작된다.
- 트랜잭션의 종료
- commit 또는 rollback 구문이 실행되면 자동으로 종료된다.
- 시스템이 고장난 경우
commit과 rollback
- commit은 해당 트랜잭션 내에서 실행된 dml 작업(데이터 변경 작업)을 영구 저장한다.
- rollback은 해당 트랜잭션 내에서 실행된 dml 작업의 db 반영을 전부 취소한다.
트랜잭션 특징(ACID)
- 원자성: 트랜잭션 내의 작업이 부분적으로 성공하거나 실패하는 것을 허용하지 않는 것. All or Nothing으로 동작
- 일관성: 트랜잭션 내의 작업이 성공적으로 완료되었다면 언제나 일관성 있는 데이터 상태로 유지되는 것.
무결성 제약 조건을 위반하는 (일관성 있는 데이터 상태가 아닌 것) 트랜잭션은 실행될 수 없다.
- 고립성: 트랜잭션 수행 시 다른 트랜잭션의 연산이 영향을 미치지 못하도록 하는 것
* 트랜잭션이 종료(완료)되기 전의 중간 상태의 데이터를 외부 트랜잭션이 볼 수 없음
- 지속성: 성공적으로 수행된 트랜잭션의 결과는 데이터베이스에 영구적으로 반영된다.
오라클 데이터베이스 객체
시퀀스: 일련번호 생성기
테이블: 데이터의 저장소
뷰: 가상의 테이블이다.
하나 이상의 테이블을 기반으로 하는 가상의 테이블이고, 자체적인 데이터를 포함하고 있지는 않지만, 테이블과 마찬가지로 뷰를 대상으로 CRUD가 가능하다.
* 대부분 뷰를 대상으로는 SELECT만 사용한다.
- 뷰를 사용하는 이유: 복잡한 SQL문을 쉽게 작성할 수 있다.
데이터 엑세스를 제한할 수 있다.
동일한 데이터로부터 다양한 결과를 얻을 수 있다.
인덱스: 데이터의 색인 정보를 가지고 있다.
인라인 뷰(Inline View)
select ~
from ~
where 컬럼 = (select ~
from ~
where ~) <-- 서브 쿼리
select ~
from (select ~
from ~
where ~) <-- 인라인 뷰
where ~
select ~, (select ~ from ~ where ~) <-- 스칼라 서브 쿼리
from ~
where ~
인라인 뷰
- 메인 쿼리의 from 절에 작성된 서브 쿼리를 말한다.
* select문의 실행 결과가 가상의 테이블로 취급된다.
* view는 오라클의 데이터베이스 객체이지만, 인라인 뷰는 오라클의 데이터베이스 객체가 아니다.
* 메인 쿼리가 실행되는 동안 잠깐 만들어지는 가상의 테이블이다.
- Top-N 분석 쿼리에 자주 사용된다.
- 인라인 뷰도 조인에 참여할 수 있다.
Top-N 분석 쿼리
- 테이블에서 조건에 맞는 최상위 데이터 n개 또는 최하위 데이터 n개를 조회할 때 사용된다.
- 회사에 급여를 가장 많이 받는 직원 3명
- 가장 최근에 입사한 직원 5명
- 한 달 동안 가장 많이 팔린 제품 10가지
- select 컬럼명, 컬럼명
from (select 컬럼명, 컬럼명
from 테이블
order by Top-N 분석 대상_컬럼명)
where rownum <= N
* rownum은 서브쿼리(인라인뷰)에서 반환되는 각 행에 1부터 시작하는 순번을 할당한다.
분석 함수
- 분석 대상 컬럼의 값을 기준으로 순번 혹은 순위를 부여하는 함수다.
* ROW_NUMBER(): 순번을 부여한다.
* RANK(): 순위를 부여한다.
- Top-N 분석을 수행할 수 있다.
- 특정 범위에 속한 행을 찾을 때 유리하다(6~10등).
- 페이징 처리에 유용하게 사용되는 함수다.
- 사용 방법: SELECT 분석함수() OVER (ODER BY 정렬대상컬럼명 정렬방향) 별칭, 컬럼명, 컬럼명
FROM 테이블명
* 정렬대상 컬럼의 값을 기준으로 행을 정렬한 다음 순번 혹은 순위를 부여한다.
0416
인덱스
- 오라클 데이터베이스 테이블에서 원하는 데이터를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조
- 테이블의 데이터에 대한 색인 정보를 보관하고 있는 객체다.
* 인덱스에 보관된 정보 = 데이터 + ROWID(데이터가 저장된 위치 정보)
- 인덱스의 생성
자동 생성 인덱스
Primary key, unique 제약 조건이 정의된 컬럼은 자동으로 인덱스가 생성된다.
수동 생성 인덱스
create index 인덱스명
on 테이블명(컬럼명, 컬럼명, ...)
- 인덱스의 삭제
drop index 인덱스명
- 인덱스가 필요한 경우
where절에 검색 조건으로 자주 사용되는 경우
조인 조건으로 자주 사용되는 경우
해당 컬럼에 null값이 많이 포함된 경우
해당 컬럼에 다양한 값이 분포되어 있는 경우
큰 테이블에서 대부분의 조회 조건으로 검색되는 행이 2%~4% 미만인 경우
- rowid
테이블에 저장된 데이터의 위치를 나타내는 주소값이다.
사용자가 임의로 변경할 수 없고, 조회만 할 수 있다.
예시: AAAEAW AAE AAAACt AAA
AAAEAW: 데이터베이스 식별을 위한 Data Object Number
AAE: 각각의 데이터파일에 할당된 File Number
AAAACt: 데이터 블록의 위치를 나타내는 Block Number
AAA: 블록 내에 데이터가 저장된 행의 번호 Row Number
사용자 및 권한 관리
사용자
- 데이터베이스 시스템에 접속하고, 데이터베이스 객체를 생성/삭제, 데이터 조작을 할 때 필요한 사용자 계정
- 시스템 권한을 가진 사용자만 사용자를 등록/삭제할 수 있다.
권한(privileges)
- 특정 SQL문을 실행할 수 있는 권리
- 시스템 권한
- 데이터베이스 시스템을 사용(액세스)할 수 있는 권한
- 시스템 권한을 가진 사용자만 부여/박탈이 가능하다.
- 객체 권한
- 데이터베이스 객체의 내용을 조작할 수 있는 권한
- 해당 객체의 주인(소유자)가 부여/박탈이 가능하다.
롤(role)
- 사용자에게 부여할 수 있는 권한들의 집합이다.
- 여러 개의 권한을 묶어서 하나의 롤을 정의할 수 있다.
* 권한의 부여/박탈이 쉬워진다.
- 주로 사용되는 롤
- Connect Role
- 오라클에 접속할 수 있는 세션을 생성할 수 있는 권한
- 일반적인 데이터베이스 객체를 생성하거나 조회할 수 있는 권한
- Resource Role
- PL/SQL을 사용할 수 있는 권한
* 일반 사용자에게는 Connect, Resource Role을 부여한다.
- DBA Role
- 모든 시스템 권한이 부여된 Role이다.
- 데이터베이스 관리자에게 부여된다.
0417
set 연산자
- 두 개 이상의 조회 결과를 하나로 결합한다.
- 연산자
- UNION
A UNION B 1 2 3 4 5 6 7
A의 조회 결과와 B의 조회 결과에 포함된 모든 행을 반환한다.
단 A와 B 둘 다에 포함되어 있는 행은 한 번만 선택된다.
(A와 B의 합집합을 반환한다. 단 중복된 행은 한 번만)
- UNION ALL
A UNION ALL B 1 2 3 4 3 4 5 6 7
A의 조회 결과와 B의 조회 결과에 포함된 모든 행을 반환한다.
A와 B 모두에 포함된 중복된 행도 모두 반환한다.
(A와 B의 합집합을 반환한다. 중복된 행 포함)
- INTERSECT
A INTERSECT B 3 4
A의 조회 결과와 B의 조회 결과에 공통으로 포함된 행만 반환한다.
(A와 B의 교집합을 반환한다.)
- MINUS
A MINUS B 1 2
A의 조회 결과에서 B의 조회 결과를 뺀 행을 반환한다.
(A와 B의 차집합을 반환한다.)
- 쿼리 작성 시 주의 사항
- A 쿼리문과 B 쿼리문의 선택된 컬럼의 개수가 동일해야 한다.
SELECT 컬럼 1, 컬럼 2
FROM A
UNION
SELECT 컬럼3, 컬럼7
FROM B
- A 쿼리문과 B 쿼리문의 선택된 컬럼의 데이터 타입이 동일해야 한다.
- A 쿼리문과 B 쿼리문에서 선택된 컬럼의 이름은 동일하지 않아도 된다.
- 정렬을 위한 ORDER BY 절은 맨 마지막 SELECT문에 붙인다.
다중컬럼 서브쿼리
- 서브쿼리가 두 개 이상의 컬럼을 반환하는 경우 사용된다.
- SELECT ~
FROM ~
WHERE (컬럼1, 컬럼2) IN (SELECT 컬럼1, 컬럼2
FROM ~
WHERE ~ )
* 컬럼 간의 실제 컬럼명은 다를 수 있다.
스칼라 서브쿼리
- 하나의 행에서 하나의 값(1행 1열)만 반환하는 서브쿼리다.
- 오직 하나의 값을 반환하는 서브 쿼리다.
- 주로 SELECT절에서 사용된다.
* DECODE 및 CASE의 조건 및 표현식 부분
* SELECT문의 모든 절에서 사용 가능하다(GROUP BY 절은 제외)
* UPDATE 문의 SET절
상호연관 서브쿼리
- SELECT COLUMN1, COLUMN2
(SELECT COLUMN1
FROM TABLE INNER
WHERE INNER.COLUMN3 = OUTER.COLUMN3)
FROM TABLE OUTER
- SELECT COLUMN1, COLUMN2, ...
FROM TABLE OUTER
WHERE COLUMN3 = (SELECT COLUMN1
FROM INNER
WHERE INNER.COLUMN4 = OUTER.COLUMN4)
- 상호연관 서브쿼리는 OUTER 쿼리의 행이 조회될 때마다 서브쿼리가 실행된다.
데이터 모델링
- 정보 시스템을 구축하기 위한 테이블 관점에서의 업무 분석 기법
- 약속된 표기법으로 표현하는 과정
- 데이터베이스 구축을 위한 분석/설계 과정
데이터 모델링의 모델 종류
- 개념적 데이터 모델: 핵심 엔티티를 선정하고, 앤티티간의 관계를 생성한다. 각 앤티티의 애트리뷰트를 선정한다.
- 논리적 데이터 모델: 구축하려는 정보 시스템에 맞게 기본키, 애트리뷰트를 정확하게 표현하는 것
- 물리적 데이터 모델: 실제 데이터베이스에 적용할 수 있도록 테이블, 컬럼, 데이터 타입 등을 고려해서 설계한 것
앤티티
- 업무에 필요한 정보를 저장하는 것
- 영구적으로 존재하는 데이터인 경우
- 누가 봐도 정확하게 구분되는 데이터 집합인 경우
앤티티의 종류
- Key entity, Main entity, Action entity, Child entity
속성(Attributes)
- 앤티티가 가지는 특징
- 앤티티를 구성하는 값
- 앤티티에서 관리하고자 하는, 더 이상 분리되지 않는 데이터의 최소 단위
관계(Relation)
- 두 개의 앤티티 사이의 논리적인 관계
- 관계는 분포도, 존재 유무, 방향으로 구성된다.
* 관계 = 분포도(O, <) + 존재유무(|) + 방향(─)
브릿지 테이블
0418
계층형 쿼리
- 테이블에 저장된 데이터가 상위 계층과 하위 계층의 관계를 가지고 있을 때 오라클에서는 start with와 connect by를 이용해서 상위->하위, 하위->상위로 데이터를 조회할 수 있다.
- 형식
select level, 컬럼명, 컬럼명, ...
from table
where 조건식
start with 계층의 시작점(시작행)을 지정합니다.
connect by 검색 방향을 지정
* connect by prior 부모행의 키 = 자식행의 키 <-- 부모에서 자식으로 트리 구성
* connect by prior 자식행의 키 = 부모행의 키 <-- 자식에서 부모로 트리 구성
* level은 계층형 쿼리에서 수행 결과의 depth를 표현하는 의사 컬럼이다.
- 계층형 쿼리의 실행 순서
select level, 컬럼명, 컬럼명, ... 5
from table 1
where 조건식 4
start with 계층의 시작점(시작행)을 지정합니다. 2
connect by 검색 방향 3