데이터 관련 직종에 입문하기를 원하는 사람들은 기본적으로 데이터베이스를 다룰 줄 알아야 합니다. 데이터베이스를 설계할 수 있는 능력까지 갖춘다면 금상첨화일 것 입니다. 개발하고자 하는 시스템의 규모가 커지면 커질수록 데이터베이스를 효율적으로 설계하는 일이 중요해집니다. 데이터 베이스의 설계는 문제의 분석부터 시작하여 설계 및 구축에 이르는 전 과정을 포함하고 있기 때문에 훌륭한 공학 설계 과목이 될 수 있습니다.
2005년 12월
지은이 오세종
1. 설계를 위한 준비
(1) 데이터 베이스의 개요
연습문제
- 동일 데이터가 중복하여 존재할 때, 발생할 수 있는 문제가 무엇인지 설명하시오.
저장공간의 낭비 (그만큼 관리비 많이 소요됨)
데이터 불일치 (정확도 떨어지는 만큼 신뢰성 하락)
보안이 어려움
- 데이터베이스와 DBMS의 차이점을 설명하시오.
database
데이터베이스는 "관리하기 쉽게 모아진 데이터의 집합"
database management system
DBMS는 "관리하기 쉽게 모아진 데이터의 집합" 즉, database를 처리하는 "관리 시스템"
DBMS를 통해서만 DB에 접근 및 관리/처리 할 수 있다.
- 릴레이션, 컬럼, 튜플에 대해서 설명하시오.
릴레이션 = 하나의 파일 = 하나의 테이블
속성 = Attribute = 필드 = 하나의 열 = Column = 한 줄의 가로데이터
튜플 = record = 하나의 행 = Row = 한 줄의 세로데이터
- 다음의 테이블에서 기본키와 외래키는 무엇인지 찾으시오.
학생데이터
|------------------------------|
| 학번 | 학생명 | 전공 | 지도교수 id |
|------------------------------|
이하 생략
교수데이터
|-------------------|
| 지도교수 id | 교수명 |
|-------------------|
이하 생략
기본키 (Primary Key)는 주민등록번호 처럼 서로 중복되지 못하는, 개별성 (고유성)이 있는, 식별이 가능한 Column 속성값이다. 1개 이상이며 데이터 길이가 짧고 자주 이용되는 값으로 선택한다.
외래키 (Foreign Key)는 다른 테이블과의 연결고리 역할을 하며, 적어도 두 테이블이 갖고 있는 동일한 Column 속성값이다.
기본키 : 학번
외래키 : 지도교수 id
학생테이블에서의 PK : 학번
교수테이블에서의 FK : 지도교수 id
- 뷰 View 의 필요성에 대해 설명하시오.
뷰는 일반 테이블에 대해서 일부만 가져온 테이블이다.
하는 이유 : 보안을 위해 감추고자 하는 정보가 있거나, 좀 더 간편하게 필요한 정보만 보길 원할 때
학생데이터
|------------------------------|
| 학번 | 학생명 | 전공 | 지도교수 id |
|------------------------------|
이하 생략
CREATE VIEW view02
AS SELECT 학번, 학생명
FROM 학생데이터view02
|-------------|
| 학번 | 학생명 |
|-------------|
이하 생략
(2) 시스템 구축 & DB 설계
a. 데이터베이스 설계란
현실세계를 분석하여 이를 개념적 모델로 표현하고, 이를 바탕으로 데이터베이스를 구축하는 과정을 말한다.
(1) 데이터베이스 안에 어떤 테이블들이 있어야 하고,
(2) 각 테이블들은 어떤 컬럼이 있어야 하며,
(3) 기본키와 외래키는 어떤 것인지를 정하고,
(4) 응용프로그램에서 필요로 하는 뷰와 인덱스를 생성하는 일련의 과정
이 있어야 한다.
b. 종류
데이터설계에는 일반적으로
(1) 논리적 데이터베이스 설계 (logical database design)
: 현실세계를 관찰/분석하여 ERD 라고 불리는 개념적 모델을 만드는 과정 = 데이터 모델링
(2) 물리적 데이터베이스 설계 (physical database design)
: 논리적 설계를 바탕으로 실제 데이터베이스를 구축하기 위해 테이블, 인덱스, 뷰 등을 설계하고 반정규화를 시행하는 과정
으로 구분된다.
c. 정보시스템 구축 절차
(1) 정보전략 계획 수립 ISP
(2) 업무 분석
해당 기업의 전략 목표 , 현행 업무절차 평가 (문제점 / 개선방향 ) , 현행 정보 시스템 평가 등
업무 흐름도로 정리 등
(3) 데이터 모델링
현실세계를 데이터 관점에서 파악하여 개념적인 모델로 표현하는 단계 = 논리적 데이터베이스 설계 = ERD
(4) 프로세스 모델링
현실세계를 업무의 처리 절차나 흐름의 관점에서 파악하여 개념적 모델로 표현하는 단계
- 업무가 어떻게 구성되어 있는지
- 업무의 처리 절차와 방법이 어떻게 되는지를 파악
기능분해도, 프로세스 흐름도 등
(5) 상관 모델링
데이터 모델과 프로세스 모델을 비교 검토하여 서로 간에 잘 맞는지를 평가하는 과정이다.
데이터모델과 프로세스모델은 동일한 현실세계의 서로 다른 면을 표현한 것이기 때문에 상호 조화를 이루어야 한다.
따라서,
(1) 업무의 과정이 명확하게 파악되었는지 (프로세스 모델)
(2) 업무 과정과 관련된 데이터나 정보가 올바로 파악되었는지 (데이터 모델)
를 평가하는 것은 매우 중요하다.
CRUD
대표적으로 상관모델을 위하여 CRUD 매트릭스가 많이 이용된다.
C reate
R ead
U pdate
D elete
(6) 산출물 기반 설계 단계
물리적 DB설계
실제 데이터베이스 구축을 위한 테이블, 뷰, 인덱스, 용량 등을 설계한다.
여기서부터는 특정 DBMS 제품을 염두에 두고 작업을 진행한다.
논리적 설계와는 달리
구축될 데이터베이스의 용량, 성능, 보안요소 등도 고려하게 된다.
테이블 기술서 등이 있다.
프로그램 설계
(7) 데이터베이스 구축
물리적 DB 설계 내용을 가지고 실제 DBMS 안에 테이블, 인덱스, 뷰 등을 생성하는 과정을 말한다.
이렇게 구축된 데이터베이스를 가지고 프로그램을 개발하고 테스트한다.
(8) 데이터베이스 튜닝
데이터베이스가 일정한 성능을 유지할 수 있도록 비효율적인 요소를 제거하고 성능 개선을 위하여 SQL 문장을 포함, 데이터베이스의 여러 요소를 조정하는 작업을 말한다.
데이터베이스 내에 데이터 양이 증가하고 사용자 수가 증가하면 자연히 데이터베이스의 응답 속도 및 처리 속도가 저하되기 마련이다.
따라서 데이터베이스 튜닝을 통하여 일정한 성능을 유지시키는 것이 중요하다.
(9) 유지보수
필요에 따라 기존 테이블에 새로운 컬럼 추가하거나, 삭제 및 수정하는 작업을 뜻한다.
(3) DB모델링의 주요 개념
데이터 모델링이란,
논리적 데이터베이스 설계 이다.
현실세계의 업무를 분석하여 이를 약속된 표기법을 사용하는 개념적 모델 ERD 를 표현하는 과정이다.
엔티티, 속성 attribute , 관계 등을 이용한다.
ERD를 그릴 줄도 알아야 하지만, 반대로 ERD를 보고 현실세계의 업무를 추론할 수도 있어야 한다.
데이터베이스를 이루는 테이블, 컬럼, 행, 기본키, 외래키 에 대해선
모델링 ERD 상에서는 각자 다른 용어로 지칭된다.
테이블 Table = 엔티티
컬럼/열 Column = 속성
튜플/행 Row = 인스턴스
기본키 PK = 주 식별자
외래키 FK = 외래 식별자
a. 엔티티 (테이블)
업무의 관심대상이 되는 정보를 갖고 있거나, 그에 대한 정보를 관리할 필요가 있는 유형 및 무형의 사물(개체)를 말합니다.
데이터베이스 용어로는 테이블 table 에 해당됩니다.
엔티티는 박스로 표현되며, 두 개 이상의 인스턴스를 보유해야 한다.
인스턴스는 하나의 row 를 의미합니다.
b. 속성 Attribute (컬럼)
엔티티(테이블)에서 관리해야 할 최소 단위의 정보 항목을 말하며, 엔티티는 하나 이상의 속성을 포함한다.
c. 관계 Relationship 와 카디널리티
관계 Relationship 이란,
두 엔티티 (table) 사이의 관련성을 나타내는 용어이다.
ERD 에서는 두 엔티티가 관계가 있다는 사실을 나타내기 위해, 두 엔티티를 선으로 연결한다.
이때, 두 엔티티가 관계가 있다 라는 의미는 상호 공유 하는 속성이 있다 라는 의미이다.
카디널리티 Cardinality 란,
두 엔티티 간의 관계를 보다 구체적으로 표현하는 방법의 하나이다.
각 엔티티 속에 속해있는 인스턴스 (row) 간에 수적으로 어떤 관계에 있는지를 나타낸다.
d. 부모/자식 엔티티
공통속성의 값이 먼저 생성되는 쪽이 부모엔티티, 나중에 생성되어 가져다 쓰는 쪽이 자식 엔티티이다.
e. 주식별자와 외래식별자
주식별자 Primary identifier = 기본키 Primary Key
외래식별자 Foreign identifier = 외래키 Foreign Key
모델링과 데이터베이스 구축
사실상 여기까지는 개념적 설명이기 때문에, 적용을 앞두고 이해가 되지 않습니다.
순서대로 그림으로 표현해봅니다.
위에 그려진 개념적모델에 대해서 하나씩 해부해보자.
이제 PK와 FK 를 표시해보자.
이제 학교 도서관 현실상황에서의
현실세계 -> 개념적 모델 -> 데이터베이스 까지 알아보자.
연습문제
6번
다른 사람으로부터 받은 명함을 데이터베이스로 관리하려고 한다. 다음의 명함을 보고 명함 엔티티를 설계하시오. (주식별자는 명함번호로 한다.)
9번
다음의 두 테이블과 저장된 튜플들을 보고 ERD를 작성하시오. (카디낼러티와 참여도도 표시한다. 각 환자는 하나의 질병만 있는 것으로 가정한다.)
환자테이블과 질병테이블간에는 "질병코드" 가 중복되어 있다.
환자테이블에서는 "질병코드"가 각 row를 대표하는 고유성이 있는 컬럼이 아니므로 PK가 아니다.
질병테이블에서는 "질병코드"가 각 row를 대표하는 고유성이 있는 컬럼이므로 PK이다.
즉, 환자테이블의 "질병코드"가 FK 이다. 자녀엔티티 라고 볼 수 있다.
즉, 질병테이블의 "질병코드"가 PK 이다. 부모엔티티 라고 볼 수 있다. (필수적)
두 테이블간 예시를 상상하여 만들어보았다.
콜레라 질병을 앓고 있어 설사를 하는 환자는 없을 수도 있으니,
질병 데이터는 필수적이고, 환자 데이터는 선택적이다.
1: N 관계를 갖는다.
(4) 모델링 도구의 사용
데이터베이스 설계 도구로는
Toad 사의 Data Modeler, Erwin 사의 Data Modeler , ORacle 사의 SQL Developer 등이 있다.
2. 업무분석
(1) 현실 업무의 자료 수집
- 고유 업무를 위한 문서들
- 업무메뉴얼
- 다른 업무 주체와의 업무 과정에서 발생하거나 주고받는 문서들
- 거래명세서 등
를 바탕으로 "조직도" , "데이터 흐름도" , "기능" , "프로세스" 등을 토대로 진행할 수 있다.
3. 논리적 DB설계
(1) 엔티티 정의
(2) 식별자/관계의 정의
a. 주 식별자
b. 관계
c. 외래식별자
d. 업무규칙
(3) 상세화
a. 세부속성 확정
b. 정규화
c. 도메인/용어사정 정의
(4) 통합 및 검증
a. ERD 통합
b. 엔티티 검증
c. 관계 검증
d. 속성 검증
