1. 설계 프로세스 적용
가. 프로세스 요약
1) 임무 명세 & 목표 정의
•
시스템 전체의 임무 명세 작성
•
작업별 임무 목표 작성
2) 필드 정제
•
1단계: 특징 검토 및 정제
•
2단계: 샘플 데이터 기반 정제
3) 일반 필드와 계산된 필드 구분
•
일반 필드 목록 생성
•
계산된 필드 목록 생성
4) 데이터 구조 생성
•
테이블 목록 생성
•
각 테이블 내 데이터 필드 할당
•
필드 정제
◦
이상적 필드 요소 사용
◦
다중 부분 필드 해소
◦
다중값 필드 해소
•
테이블 구조 정제
◦
이상적 테이블 요소 사용
◦
부분 집합 테이블 설정
◦
불필요한 이중 필드 해소
•
테이블에 사용될 키 설정
◦
Candidate Key 설정
◦
Primary Key 지정
◦
Alternate Key 지정
•
필드 명세 정의
◦
일반적 요소 정의
◦
물리적 요소 정의
◦
논리적 요소 정의
5) 테이블 관계 설정
•
테이블 간 관계 식별하여 관계도 생성
•
관계 특성 설정
◦
관계마다 삭제 규칙 지정
◦
관계마다 참여 종류 지정
◦
관계마다 참여 정도 지정
6) 업무 규칙 명세 정의
•
필드별 제약조건 정의 및 적용
•
관계별 제약조건 정의 및 적용
•
업무 규칙 명세서 작성
7) 데이터 무결성 검토
•
테이블 수준 검토
•
필드 수준 검토
•
관계 수준 검토
•
업무 규칙 검토
8) 설계 결과물
•
ERD
•
일반 필드 목록
•
계산된 필드 목록
•
테이블 목록
•
테이블 관계도
•
업무 규칙 명세서
나. 프로세스 적용
•
비밀 유지 서약에 따라 노출 금지
2. 프로세스별 주요내용
가. 임무 명세 & 목표
1) 임무 명세
•
정의: 설계하는 DB의 구체적인 목적
•
방법: 프로젝트 담당자와의 인터뷰를 통해 임무 명세 작성
•
주의할 점
◦
구체적인 작업은 명시하지 않는다
◦
간단 명료하게 작성한다
◦
DB의 구체적인 목적을 포함한다
2) 임무 목표
•
정의: DB를 통해 수행하는 작업의 목표
•
방법: 예상되는 사용자와 현업 관리자와의 인터뷰를 통해 임무 목표 작성
•
주의할 점
◦
하나의 작업만 명시한다
◦
작업의 목표를 뚜렷하게 표현한다
나. 기존 데이터베이스 분석
1) 일반 필드 목록 생성
•
1단계: 특징 검토와 정제
◦
대상과 특징 분류하고 대상은 제거
ex) 상품은 대상이고 상품의 제조연월은 특징임
◦
중복된 특징 제거
◦
이름은 중복이지만 쓰임새가 다른 특징 분류
ex) 이름 → 직원 이름, 이름 → 담당자 이름
◦
이름은 다르지만 같은 쓰임새인 특징 제거
ex) Product Number, Product No. → Product Number
•
2단계: 샘플 데이터 기반 정제
◦
1단계에서 정제된 특징 목록과 샘플 데이터를 비교하여 중복되지 않은 새로운 특징 확인
ex) 제품 제조연월이 샘플 데이터에 있지만 특징 목록에 없다면 새로 추가
2) 계산된 필드 목록 생성
•
계산된 필드 목록: 일반 필드 목록 내 특징을 활용하여 계산되는 특징 나열
ex) 합계, 평균 등등
•
일반 필드 목록에 계산된 특징이 있다면 제거하고 계산된 필드 목록으로 해당 특징 이동
다. 테이블 구조 설정
1) 테이블 목록 생성
•
1단계: 데이터 필드 목록에 맞춰 필요한 테이블 목록 생성
•
2단계: 1단계 테이블 목록을 주제에 따라 통폐합
•
3단계 : 2단계 테이블 목록을 임무 목표에 따라 통폐합
•
테이블 목록 생성 후 각 테이블에 데이터 필드 목록을 참고하여 데이터 필드 할당
2) 필드 정제
•
이상적인 필드
◦
정의: 테이블 내 잠재적으로 발생 가능한 문제요소(다중 부분 필드, 다중값 필드 등)를 제거하기 위한 지침
◦
테이블 주제(대상 또는 사건)의 고유한 특성일 것
◦
하나의 값만을 포함할 것
◦
더 작은 값으로 해체가 안될 것
◦
계산되거나 연결된 값을 포함하지 않을 것
◦
전체 DB에서 유일한 값일 것
•
다중 부분 필드 해소
◦
정의: 다중 부분 필드란 더 작은 값으로 해체가 될 수 있는 값으로 구성된 것
ex) 고객 주소(서울시 강남구 테헤란로 89-4)
→ 고객 거주 도시(서울시), 고객 거주 지역구(강남구), 고객 거주 거리(테헤란로), 고객 상세주소(89-4)
•
다중값 필드 해소
◦
정의: 다중값 필드란 하나의 필드에 복수의 값으로 구성되는 것
ex) '학생'이라는 테이블 내 '강의 과목들'(수학, 영어, 국어) → '첫번째 강의과목'(수학), '두번째 강의과목'(영어)
3) 테이블 구조 정제
•
이상적인 테이블 요소
◦
정의: 테이블 구조에 잠재적으로 발생 가능한 문제요소(데이터 중복 등)를 제거하기 위한 지침
◦
하나의 테이블은 하나의 대상 또는 하나의 사건을 주제로 가질 것
◦
불필요한 이중 필드를 포함하지 않을 것
◦
기본 키를 가질 것
◦
다중 부분 필드 또는 다중 다중값 필드를 허용하지 않을 것
◦
계산된 필드를 포함하지 않을 것
◦
최소한의 중복 데이터만 포함할 것
•
불필요한 이중 필드 해소
◦
정의: 이중 필드란 같은 내용을 가리키는 필드가 하나의 테이블 또는 복수의 테이블에 걸쳐 복수의 필드에 존재하는 것
ex) '학생'이라는 테이블 내 '첫번째 강의과목'(수학), '두번째 강의과목'(영어)
→ 강의라는 테이블을 새로 만들고 학생 이름(외래키), 강의과목으로 필드를 생성하여 이중필드 해소
◦
예외가 허용되는 이중 필드는 외래키가 유일함
•
부분집합 테이블 설정
◦
정의: 부분집합 테이블이란 특정 테이블의 주제를 일부 공유하고 해당 테이블에 종속되는 형태의 테이블
ex) 직원이라는 주제를 대표하는 employee 테이블을 생성하고 정직원과 계약직원의 특징을 각각 관리하는 부분집합 테이블 설정할 수 있음.
◦
주제를 대표하는 테이블과 주제에 종속되는 부분집합 테이블 구분
ex) 대표: employee, 종속: pull_time_employee, part_time_employee
◦
주제를 대표하는 테이블의 해당 주제의 공통 필드 할당
ex) employee(name, date_hired)
◦
부분집합 테이블의 필드 할당
ex) pull_time_employee(salary_amount, position), part_time_employee(hourly_rate, skill_level)
4) 키 설정
키 설정 목적: 테이블 내 필드를 배타적으로 식별함으로써 테이블의 구조적 무결성을 보장하기 위함
•
Candidate Key 지정
◦
자연적인 Candidate Key 지정
→ 테이블의 필드 중에서 후보키의 조건에 맞는 경우 해당 필드를 지정
◦
인공적인 Candidate Key 지정
→ 테이블의 필드 중에서 후보키의 조건에 맞지 않거나 가 자연적인 Candidate Key 보다 적합할 경우
•
Primary Key 지정: Candidate Key 중에서 아래의 조건에 따라 적합한 것을 선택한다
◦
단일 Primary Key > 복합 Primary Key
◦
필드 이름에 테이블의 이름이 일부 포함하는 Primary Key
ex) sales_invoices라는 테이블의 sales_invoice_number
•
Alternate Key 지정
◦
Candidate Key 중에서 Primary Key로 지정되지 못한 것을 AK(Alternate Key) 또는 CAK(Composite Alternate Key)로 지정한다
•
Alternate Key 지정에 따른 이점(자세히 알아보자)
◦
식별 대체수단: 식별 대체수단이 필요한 경우는?
◦
성능 상의 이점: 대체키도 보조인덱스로 활용될 수 있어서 보조 인덱스 검색에 따라 데이터를 검색하므로 성능 상 이점이 있지 않을까?
5) 필드 명세 정의
필드 명세 목적: DB 내 데이터의 특성과 목적을 이해함으로써 필드 수준의 무결성을 달성하기 위함
•
일반적 요소
◦
필드 이름, 부모 테이블, 레이블
◦
명세 종류, 설명
•
물리적 요소
◦
데이터 종류, 길이, 문자지원
◦
입력 마스크, 디스플레이 형식
•
논리적 요소
◦
키 종류, 키 구조, 유일성, 널 지원
◦
값 입력자, 필요값, 기본값
라. 테이블 관계 설정
1) 다중값 필드 해결
•
1:N 관계로 수정
2) N:M 관계
•
연결 테이블 활용
3) 삭제 규칙 지정
삭제 규칙 지정 목적: 자식 테이블 내 레코드 중 부모테이블의 어떤 레코드와도 관계를 갖지 못하는 고아 레코드 발생 방지하기 위함
•
거부(Deny): 부모 테이블의 레코드 삭제 거부(레코드 유지)와 해당 레코드에 대한 비활성화
•
제약(Restrict): 부모 테이블의 레코드 삭제하기 위해서는 자식 테이블 내 연관 레코드 모두 삭제 선행
•
연속(Cascade): 부모 테이블의 레코드 삭제에 따라 자식 테이블 내 연관 레코드 모두 자동 삭제
•
널화(Nullify): 부모 테이블의 레코드 삭제 후 자식 테이블 내 연관 외래키 값을 NULL로 변경. 본 규칙을 사용하기 위해 해당 외래키 필드에 대해 Nulls Allowed 설정 선행
•
기본값 설정(Set Default): 부모 테이블의 레코드 삭제 후 자식 테이블 내 연관 외래키 값을 기본값으로 갱신함. 본 규칙을 사용하기 위해 외래키 필드에 대해 기본값 설정 선행
4) 참여 종류 지정
•
의무적 참여: 특정 테이블에 레코드가 있어야만 연관 테이블에 레코드 입력 가능
•
선택적 참여: 특정 테이블에 레코드가 없어도 연관 테이블에 레코드 입력 가능
예시) 직원과 고객의 관계
◦
업무 규칙 상 한 명의 직원은 최소 한 명 이상의 고객을 전담한다고 할 때, 직원과 고객의 관계는 1:N이다.
◦
직원 테이블에 레코드가 있어야만 고객 테이블에 레코드를 입력할 수 있으므로 직원 테이블에 의무적 참여를 지정한다.
◦
고객 테이블에 레코드가 없어도 직원 테이블에 레코드를 입력할 수 있으므로 고객 테이블에 선택적 참여를 지정한다.
5) 참여 정도 지정
•
참여 정도: 특정 테이블이 관계가 있는 테이블의 하나의 레코드와 연결되어야 하는 최소, 최대의 수
예시) 회사 업무 규칙 상 한 명의 직원은 최대 15명의 고객을 전담할 수 있다
◦
직원 테이블 (1, 1) → 고객 테이블: 직원 테이블은 고객 테이블에 대한 의무적 참여가 지정되어 있으므로 최소 참여는 1. 한 명의 고객에게는 최대 한 명의 직원을 전담하므로(1:N 관계) 최대 참여는 1.
◦
고객 테이블 (0, 15) → 직원 테이블: 고객 테이블은 직원 테이블에 대한 선택적 참여가 지정되어 있으므로 최소 참여는 0, 한 명의 직원에게는 최대 15명의 고객을 할당할 수 있음로 최대 참여는 15
마. 업무 규칙 명세 정의
1) 업무 규칙
•
정의: 서비스의 운영 방법(또는 조직의 데이터 인식 방법)에 따라 데이터 자체나 데이터 처리 프로세스에 제약사항을 설정하는 것
2) 데이터베이스 지향 업무 규칙
•
필드별 제약조건 정의 및 적용
◦
필드와 연관된 업무 규칙 식별 및 제약사항으로 적용
◦
필드 명세에 제약사항 내용 추가
◦
식별된 업무 규칙을 적절하게 테스트할 수 있는 프로세스 식별
→ 해당 필드의 레코드에 대해 CRUD 처리 중 어떤 것이 위의 제약사항을 테스트할 수 있는가?
ex) '한국 고객을 위한 우편번호를 저장할 수 있다'
우편번호 필드의 데이터 타입을 int로 설정한다
우편번호 필드 조회 시 해당 데이터 타입이 int로 설정되어 있는지 확인 가능
•
관계별 제약조건 정의 및 적용
◦
관계와 연관된 업무 규칙 식별 및 제약사항으로 적용
◦
식별된 업무 규칙을 적절하게 테스트할 수 있는 프로세스 식별
ex) '한 명의 개발자는 최소 하나의 서비스를 담당하지만 세가지 이상의 서비스를 담당할 수 없다'
개발자 테이블과 연결 테이블(개발자_서비스)의 참여 수준 및 정도
개발자 테이블→연결 테이블의 참여수준: 의무, 참여 정도: 개발자 테이블 (1, 1) → 연결 테이블
연결테이블→개발자 테이블의 참여수준: 의무, 참여 정도: 연결테이블 (1, 8) → 개발자 테이블
3) 응용프로그램 지향 업무 규칙
•
DB의 설계 단계에서 설정할 수 없는 업무 규칙에 대해 응용프로그램 구현 단계에서 적용한다
예시) '지역민에 해당하는 고객은 모든 상품에 대해 5%의 할인혜택을 받는다'
→ 할인혜택과 같은 계산 필드는 DB 내 필드로 관리되지 않으므로 DB 설계 단계에서 해당 업무 규칙을 적용할 수 없다.
4) 업무 규칙 명세서 작성
•
명세, 제약조건, 종류(DB 지향, 응용프로그램 지향), 범주(필드 또는 관계)
바. 데이터 무결성 검토
1) 테이블 수준 무결성 검토
•
테이블 내 이중 필드 존재여부 확인
•
테이블 내 계산된 필드 존재여부 확인
•
테이블 내 다중값 필드 존재여부 확인
•
테이블 내 다중 부분 필드 존재여부 확인
•
테이블 내 이중 레코드 존재여부 확인
•
테이블 내 모든 레코드가 Primary Key에 의해 식별되는지 확인
2) 필드 수준 무결성 검토
•
각 필드가 이상적 필드의 조건 충족여부 확인
•
각 필드를 위한 필드 명세 정의여부 확인
3) 관계 수준 무결성 검토
•
테이블 간 관계의 적절여부 확인
•
테이블 간 삭제 규칙 정의 확인
•
테이블 간 참여 종류 정의 확인
•
테이블 간 참여 수준 정의 확인
4) 업무 규칙 검토
•
각 규칙에 따라 적절한 제약조건의 부과여부 확인
•
각 규칙의 올바른 정의여부 확인
•
각 규칙을 위한 업무 규칙 명세 작성여부 확인
Reference
•
Michael J. Hernandez, Database Design for Mere Mortals 3rd Edition(번역명: 파워 오브 데이터베이스)