[ Home | Freetime works | FreeBSD | KFUG ]

웹 캐시 서버 도전하기

FreeBSD와 웹 캐시

웹 캐시란?

인터넷상의 대부분의 트래픽을 차지하는 HTTP 프로토콜은 주로 웹 페이지 또는 파일이라는 객체 단위의 전송을 지원하는 프로토콜이라 생각하면 되며, 접근할 수 있는 각 객체에는 URL이라는 유일한 이름이 붙어 있다(여기에 쿠키나 CGI 인수 등이 추가되기도 한다). 일반적으로는 사용자의 웹 브라우저에서 웹 서버까지 직접 접속하여 URL로 지정된 웹 객체를 전송해 와서 적절한 MIME 타입에 따라 사용자 화면에 표시하는 구조로 되어 있다.

사람들이 많이 몰리는 웹 사이트의 경우 접속이 빈번해지면 웹 서버는 부하를 많이 받게 된다. 웹 서버의 부하를 낮추는 방법에는 여러가지가 있지만, 가장 편리한 방법 중의 하나는 사용자의 웹 브라우저와 웹 서버 사이에 웹 캐시를 중간에 두어 지나가는 내용을 캐싱하도록 하는 것이다. 웹 캐시는 HTTP 프로토콜의 서버와 클라이언트 양방향으로 동작하여 웹 브라우저 입장에서는 웹 서버로, 웹 서버 입장에서는 웹 클라이언트의 형태로 동작하며, 브라우저와 서버 사이의 URL 객체를 조건에 따라 캐시의 저장 공간에 저장하고 이용자의 요청이 웹 서버에게 불필요한 경우 캐시가 직접 처리하도록 되어 있다.

하지만 캐시라는 것은 여러 수준에서 존재할 수 있다. 웹에 관련된 것이라면, 일차적으로 웹 브라우저가 있는 PC에서 메모리와 디스크에 각 객체를 캐싱하고(인터넷 익스플로러나 넷스케이프 네비게이터, 모질라의 경우 각각 메모리와 디스크 캐시에 대한 설정을 찾아볼 수 있다) 있으므로 직접 연결시에도 전혀 캐시가 적용되지 않는 것은 아니다.

웹 캐시의 종류

웹 캐시에는 크게 두가지의 용법이 있다.

• 프록시 캐시(proxy cache)

  이 형태의 캐시는 일반적으로 사용자 측에 설치되어서, 사용자의 HTTP 프로토콜 접속을 캐싱하여 기본적으로 사용자측 - 서버측 구간의 트래픽을 줄여주는 역할을 한다. 사용자가 있는 랜에 직접 설치되기도 하고, ISP의 라우터 구간에 존재하기도 한다. 이 방법은 특정 서버의 부담을 크게 줄이지는 못하지만 여러 사람이 웹 접속을 많이 하는 경우 랜 구간의 트래픽을 줄일 수 있다. 가령 T1 회선(1.5Mbps)를 사용하는 일반 사무실의 경우 프록시 캐시를 도입하면 사내 트래픽을 많이 줄일 수 있다. 또한 방화벽이 있는 경우, 프록시 캐시는 일반 사용자의 웹 접속을 프록시 캐시만을 통하여 외부에 접속할 수 있도록 해 준다.

• 리버스 캐시(reverse cache)

  이 형태의 캐시는 서버 바로 앞에 붙어서 사용자의 HTTP 프로토콜 접속을 캐싱하여 기본적으로 서버측 구간의 트래픽을 줄이는 역할을 해 준다. 이는 서버의 증설 없이 웹 사이트의 성능을 향상시킬 수 있는 좋은 방법이기도 하다. 인버스 캐시, httpd 엑셀러레이터 등의 다른 이름으로 불리기도 한다.

결국 캐시라는 것은 사용자 - 웹 서버 간의 구간 어디엔가에 놓여서 트래픽을 줄이기 위한 목적으로 사용되는 것이지만, HTTP 프로토콜 자체가 모두 캐시를 통해 지나가는 특성상 이를 응용하여 여러가지 일이 가능하다. 예를 들면 다음과 같다.

• 웹 페이지 내의 광고 배너 삭제
• 음란물 등의 필터링
• 특정 웹사이트의 접속 제한
• 캐싱된 객체의 압축 전송

캐시 관련 포트

웹 캐시에 관련된 포트는 대부분 www 카테고리에 존재한다. 대부분의 포트는 웹 캐시 또는 캐시에 관련된 유틸리티(통계, 캐시 브라우저 등)이다.

squid (www/squid)

오픈 소스로 된 웹 캐시 중 가장 유명한 것은 squid(www/squid)이다. squid는 원래 Harvest라는 프로젝트의 일부였으나 Harvest 프로젝트가 종료된 뒤에 웹 캐시 부분이 따로 계속 발전하여 현재의 형태에 이르고 있다.

squid는 웹 캐시에 필요한 모든 기능을 제공하고 있는 완전한 웹 캐시 소프트웨어이다. 상용 웹 캐시에 비해 성능이 조금 떨어질지는 모르겠지만, 실제 중소규모의 네트워크에서 사용하기에는 조금도 부족함이 없는 수준이다. squid 프로젝트의 홈페이지는 아래 주소이다.

Squid Web Proxy Cache: http://www.squid-cache.org

# 캐시 서버의 포트를 지정한다. 보통 프록시 캐시로 쓰는 경우는# 3128번이고, 리버스 캐시로 사용한다면 80번으로 쓰는 경우가 많다.http_port 3128# 캐시용 메모리 크기를 지정한다.cache_mem 8 MB# 캐싱할 수 있는 최대 객체 크기를 정한다.maximum_object_size 4096 KB# 캐시 보관용의 디스크 영역을 지정한다.# 첫번째 인수인 ufs는 디스크 관리 방법(가장 보통인 것)을 나타내며,# aufs, diskd 등의 대체 키워드를 사용할 수 있다.# 두번째 인수는 캐시의 디렉토리이다. 용량이 충분한 디렉토리를# 지정한다.# 세번째 인수는 캐시의 최대 크기를 M바이트 단위로 지정한다.# 1500의 경우 1500M(1.5G) 를 나타낸다.# 네번째 인수와 다섯번째 인수는 캐시 디렉토리의 갯수를 나타내는데,# 캐시 디렉토리 아래 네번째 인수만큼의 디렉토리를 만들고, 그 아래# 다섯번째 인수만큼의 디렉토리를 만든다. 이를 통해 빠르게 캐시# 객체를 찾아갈 수 있다.cache_dir ufs /spool/cache/spool 1500 16 256# 캐시의 접근 로그를 저장한다. webalizer같은 로그 분석 도구로# 분석할 수 있다.cache_access_log /spool/cache/logs/access.log# 캐시에 대한 행동 로그 파일을 지정한다.cache_log none# 캐시 저장에 관련된 로그를 기록한다. 보통은 필요 없다.cache_store_log none# squid는 FTP 캐시도 가능한데, 이 경우 사용자 대신 실제 FTP 서버에# 익명 로그인할 경우의 id를 지정한다. 웹 캐시는 FTP 접속을 대신하는# 경우, 알맞은 인덱스 파일을 생성해 준다.ftp_user Squid@# 접근 제어 목록을 지정한다. 캐시에 아무나 접근할 수 있다면 불필요한# 외부 접속과 트래픽을 발생할 수 있다.# acl 다음은 그룹 이름, 그 다음에 포트, IP 주소 등을 지정할 수 있다.acl all src 0.0.0.0/0.0.0.0acl manager proto cache_objectacl localhost src 127.0.0.1/255.255.255.255acl SSL_ports port 443 563acl Safe_ports port 80          # httpacl Safe_ports port 21          # ftpacl Safe_ports port 443 563     # https, snews# http_access 뒤에는 allow/deny의 동작을 지정하고 acl 에서 지정했던# 그룹을 지정한다.# 아래의 경우 manager 그룹과 localhost 그룹은 접근을 허가,# 다음 행에 의해 localhost 이외의 manager 그룹은 접근이 안되는 것을# 알 수 있다.http_access allow manager localhosthttp_access deny manager# Safe_ports 이외는 거부(!은 not의 의미를 갖는다)http_access deny !Safe_ports# SSL_ports(443, 563 포트)가 아니면 CONNECT 그룹을 허가하지 않는다.# CONNECT 그룹은 acl에서 CONNECT 메소드를 나타낸다.http_access deny CONNECT !SSL_ports# 아래 예는 자신의 네트워크를 정의하고 여기에 대해 접속을 허가하는# 경우이다. 자신의 네트워크는 여기서 192.168.1.0/24 이다.acl myhome src 192.168.1.0/255.255.255.0http_access allow myhome# 가장 끝에는 명시적으로 allow all이나 deny all을 적어주기 바란다.# 아래와 같이 적어주면 위의 allow 규칙에 해당되지 못한 요청은 이 줄에# 의해서 모두 거부된다.http_accessde deny all

# mkdir -p /spool/cache/spool# chown -R nobody:nogroup /spool/cache/spool# squid -z

# /usr/local/etc/rc.d/squid.sh start

FETCH_ENV=     HTTP_PROXY=http://192.168.1.1:3128

  squid -k reconfigure|rotate|shutdown|interrupt|kill|debug|check|parse

# squid -k reconfigure

options         IPFIREWALL_FORWARD      #enable transparent proxy support

# 가상 호스트 기반의 리버스 캐시를 사용하도록 한다.httpd_accel_host virtualhttpd_accel_port 0# 프록시 캐시와 리버스 캐시의 기능을 모두 사용하도록 한다.httpd_accel_with_proxy on# Host: 헤더에 기반하여 서로 다른 호스트를 캐싱할 수 있도록 한다.httpd_accel_uses_host_header on

00050 divert 8668 ip from any to any via fxp000100 allow ip from any to any via lo000200 deny ip from any to 127.0.0.0/800300 deny ip from 127.0.0.0/8 to any65000 allow ip from any to any

# ipfw add 30 fwd 192.168.1.1,3128 tcp from 192.168.1.0/24 to any 80 via fxp0

# cd /usr/local/etc# cp wwwoffle.conf.install wwwoffle.conf

StartUp{ bind-ipv4         = 0.0.0.0#bind-ipv6         = :: bind-ipv6         = none http-port         = 8080 wwwoffle-port     = 8081 spool-dir         = /var/spool/wwwoffle run-uid           = nobody run-gid           = nogroup use-syslog        = yes password          = none max-servers       = 32 max-fetch-servers = 8}

# wwwoffle -online

# wwwoffle -autodial

# wwwoffle -offline

# wwwoffle -fetch

자료출처 : http://ngps.net/ngpiki/index.php?display=MySqlForCApi

간단하게나마 DB에서 가장 많이 사용되는 ( 게임용 DB 로도 가장 많이 사용 되는 ) MySQL For C API 에 대해서 알아 보고 최후로 DB 접근을 하기 위한 종단 서버 구성으로 마쳐 볼까 합니다 :) 이것 역시 언제 끝날지는 알 수 없습니다 시간 날 때마다 하겠습니다. 개인적으로 sql query문이 제가 약한 관계로 :) 제대로 된 글이 될 거라는 생각은 안 합니다.

휴 정말 간만에 들어오네요 :) 제가 너무 정신이 없어서 ( 논다고 바빳습니다. 기다리는 분은 전혀 없었겠지만 ) 2번에 나놔서 정리 할까 합니다.

첫번째로는 함수 나열과 정리를 하고 두번째는 실전 소스로 나눠서 정리할까 합니다.

실제 많은 api함수들이 있지만 자주 사용하는 것 몇가지만 알아보겠습니다.

아.. VS6에서는 컴파일을 위해서는 MYSQL/INCLUDE 폴더를 포함시켜 주시고 libmysql.lib를 포함 시키지 않으면 반가운 링크에러들이 뜰겁니다. 리눅스에서는 make 파일에 여러가지를 추가 시켜야겠죠.

정확한 include 파일 경로를 찾으려면,

# find / -name mysql.h

정확한 공유 라이브러리 파일 경로를 찾으려면,

# find / -name libmysqlclient.so

하시면 됩니다.

와 같이 하면 컴파일에 문제가 없을겁니다. 컴파일 얘기는 다음에 한번더 자세하게 makefile과 함께 하고 오늘은 자주 사용하는 함수만 정리 하겠습니다.

이제는 저 위에 함수들을 어떻게 사용 하는가에 대해서 간단한 예문을 통해서 알아 볼까 합니다. 지금 위키에서 바로 코딩을 하는 관계로 컴파일이 안됄수도 있습니다 (__) 그냥 어떻게 사용하는가만 알아 보자는 겁니다. 쿨럭;;

#define dDB_HOST  "아이피"#define dDB_PORT  3306#define dDB_ID    "아이디"#define dDB_PW    "db 패스워드"#define dDB_NAME  "DB 명 "#include <mysql.h>MYSQL* mysql;MYSQL *DBConnect( char * host , int port , char *id , char *pw , char *dbName ){        MYSQL *db = NULL;        db = mysql_init( (MYSQL*)NULL );                // 초기화 함수        if( db )        {                if( mysql_real_connect( db, host, id, pw, NULL, port, (char*)NULL, 0 ) ) // DB 접속                 {                        if( mysql_select_db( db, dbName ) != 0 ) // DB 선택                        {                                mysql_close( db );                                return NULL;                        }                }                else // connect error                {                        printf( "Error %d ( %s )\n", mysql_errno( db ), mysql_error( db ) );                        mysql_close( db );                        return NULL;                }        }        else                return NULL;        return db;}int main(){        mysql = DBConnect( dDB_HOST , dDB_PORT, dDB_ID , dDB_PW, dDB_DBNAME );        if( ! mysql )        {                return -1 ;        }        char Query[128];        sprintf( Query," select ......등등 필요한 쿼리들" );        if( !mysql_query ( mysql, Query)  )        {                MYSQL_RES *result = mysql_store_result( mysql );                if( result )                {                        MYSQL_ROW row;                        row = mysql_fetch_row( result );                            // 그리고 각 원하는 것들을 여기에다가 변수에 저장 한다.                            // id  = atoi ( row[0] ) ; 이런 식으로 ...                }                mysql_free_result( result );        }        mysql_close( mysql );        return 0;}

정말 간만에 수정 하는 군요 -_-+ 몇마디 더 추가 하고자 이렇게 글을 올리게 됐네요

이런식으로적으면 컴파일이 돼겠습니다요 이렇게 하면 mysql 이라는 게 생기겠죠 뭐 더 확실하게 알아 보고자 하면 연결 완료 나 종료에 대해서 printf 문등을 통해서 알아보던지 아니면 로그파일로 남기셔도 될듯하고요 여기서 주의 할점은 Mysql For C API를 사용할때는 -lmysqlclient 이 녀석입니다. 라이브러리를 연결 하는 녀석이죠 libmysqlclient.so 를 찾아서 링크 시켜 주는 역할을 합니다.

자 여기서 C API 의 자료형에 대해서 몇가지 알아 보겠습니다.

(에러 문구는 mysql for C API 문서에서 발견을 했는데 출처를 모르겠군요 )

이번에는 Mysql For C API 를 이용하면서 나올 에러들에 대해서 대처해보기 위해서 에러에 대해서 알아 보겠습니다.

1) "msyql.c:2: mysql.h: 그런 파일이나 디렉토리가 없음"

이 경우는 mysql.h의 경로를 찾을 수 없어서 나는 에러입니다. 다시 한번 mysql.h가 어디에 있는지 확인을 하고, -I옵션으로 그 경로를 지정해야 합니다. 이럴때 whereis 나 find 등을 잘써서 찾아 봅시다. 뭐 기본적으로 기본 경로에설치하면 아무 문제 없습니다.

2) "mysql.o(.text+0x11): undefined reference to `mysql_init'"

위와 같이 'undefined reference......' 라고 나오는 에러는 -lmysqlclient 옵션을 안줘서 생기는 에러 입니다.

3) "ld: cannot open -lmysqlclient: 그런 파일이나 디렉토리가 없음"

위의 에러는 -L옵션 뒤에 붙은 라이브러리의 경로가 잘못 되었을때 생깁니다. libmysqlclient.so 파일의 경로를 찾아서 그 경로 지정을 해주시면 됩니다.

4) "Can't connect to local MySQL server "

위의 에러는 MySQL의 서버에 연결을 할 수 없다는 메시지로서, MySQL 서버의 데몬이 실행 중이지 않을 때 나오는 메시지입니다.

5) "Access denied for user: 'root@localhost' (Using password: YES)"

사용자 아이디를 잘못 입력하거나, 암호를 잘못 입력하였을 때 나오는 메시지입니다. MySQL의 사용자는 모두 mysql database의 user 테이블에 있습니다.

6) "./sql: error in loading shared libraries libmysqlclient.so.6: cannot open shared object file: No such file or directory"

MySQL의 라이브러리를 열지 못한다는 메시지입니다. 컴파일 할 때 MySQL의라이브러리를 사용하는데, 동적 라이브러리이므로 실행시에도 라이브러리가 필요하게 된다. ibmysqlclient.so가 /usr/lib 혹은 /usr/lib/mysql 디렉터리에 존재 하지 않을 경우에 발생하는 문제입니다. /etc/ld.so.conf 파일에libmysqlclient.so가 있는 경로를 적어 준 후에 ldconfig 라는 명령을 프롬프트에서 실행하여 주면 됩니다.

mysql> use g_gaya;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 'XN__free'
Didn't find any fields in table 'XN__qna'
Database changed
mysql>

mysql> desc XN__free ;
ERROR 1016: Can't open file: 'XN__free.MYD'. (errno: 145)
mysql>

[10:24:12  /usr/local/mysql/data/g_gaya]# /usr/local/mysql/bin/myisamchk  XN__free.*
[10:24:51  /usr/local/mysql/data/g_gaya]# /usr/local/mysql/bin/myisamchk -o  XN__free.*

[10:25:57  /usr/local/mysql/data/g_gaya]# /usr/local/mysql/bin/myisamchk  XN__qna.*
[10:27:18  /usr/local/mysql/data/g_gaya]# /usr/local/mysql/bin/myisamchk -o   XN__qna.*

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
어느날 갑자기 정전이 되거나 하드디스크가 꽉차거나 하여
데이터가 깨지는 수가 있다.

복구방법

myisamchk 라는 것이 있다.

# myisamchk -r TEST.MYI

참고) man myisamchk

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
어느날 아침 로긴이 안되거나 혹은 게시판에서 데이터 베이스 엑세스를 할수 없습니다.. 이런 메세지를 볼수 있다. 난감하기 그지 없다... 어떻게 극복을 할것인가... ?
데이터 베이스는 항상 깨지기 마련이다. 1년은 운영한다면.. 적어도 1~2회 정도는 화일을 열수 없습니다.

에러 메세지 :
ERROR 1016: Can't open file: 'xxxxxx.MYD'. (errno: 145)
만약 /usr/local/mysql/var/데이터 베이스에 들어 가면 각각의   .......

--
myisamchk 복원 안되는게 몇가지 있는데, 두번째 mysqlcheck 는 100% 되더군요.
그외 비정상적으로 복원되는 경우는 DB수정에 의한 경우 밖에 없더군요..^^;
--
문제는 왜 이런 오류가 발생하는지가 궁금하네요.
글고 저같은 경우는 위 방법이 복잡하여...`ㅡ`;;
mysqlcheck -u아이디 -p비번 --auto-repair -B 복원할 DB명
을 사용합니다^^
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/usr/local/mysql/data/
   *.FRM : 테이블 정보
   *.MYI : 인덱스 정보
   *.MYD : 데이타 정보

#myisamchk 옵션을 보면
   # -r -o로 복구가 안되면다 -f 옵션을 이용한다.
   -f, --force Overwrite old temporary files.

   #복구
   -r, --recover Can fix almost anything except unique keys that aren't unique.

   #안전하게 복구
   -o, --safe-recover Uses old recovery method; Slower than '-r' but can
   handle a couple of cases where '-r' reports that it
   can't fix the data file.

   #데이터 정렬 및 속도 증가 -R1 저도 주면 될듯 싶네요
   -R, --sort-records=#
   Sort records according to an index. This makes your
   data much more localized and may speed up things

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
You should also check your tables regularly during normal system operation. At MySQL AB, we run a cron job to check all our important tables once a week, using a line like this in a `crontab' file:

35 0 * * 0 /path/to/myisamchk -s /path/to/datadir/*/*.MYI

This prints out information about crashed tables so we can examine and repair them when needed.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

myisamchk , 디비 복구에 관련된 url 들

http://www.mysql.com/doc/en/myisamchk_repair_options.html

http://database.sarang.net/database/mysql/doc/mysql-3.23.32/manual-split/manual_Maintenance.html

http://www.mysql.com/doc/en/Repair.html

http://linuxpro.pe.kr/document.files/man/mysql_3.23.28/manual_Table_types.html

http://www.phpschool.com/bbs/view.html?id=2590&code=tnt&start=0

http://www.mrtg.co.kr/

http://tunelinux.pe.kr/bbs/read.php?table=linuxinfo&no=18&o[at]=s&o[sc]=t&o[ss]=mrtg&o[st]=a

http://www.wowlinux.co.kr/download/specialview.html?id=55&view=1

http://tunelinux.pe.kr/bbs/read.php?table=linuxinfo&no=72
가장 나은 듯..

일단 snmpd 데몬이 실행되어야 함..
snmpd가 데이타를 만들고 mrtg는 순간의 데이타를 취합하여 통계를 내는 것..

mrtg설치시 gd가 필요하고 gd는 zlib랑 모가 필요하다는데 요즘 배포판에 모두 설치되어 있는 것들임.. 그냥 ./configure 하면 됨..

snmpd 떠 있으면 ./cfgmaker 가 mrtg.cfg 화일 제대로 만들어줌..

수작업으로 mrtg.cfg 화일 편집해야함(snmpd.conf도 함께.. 리부팅 필요)

ns2.myfolder.net 의 mrtg.cfg

### Interface 2 >> Descr: 'eth0' | Name: '' | Ip: '211.62.49.37' | Eth: '00-01-02-62-1e-b1' ###

################################################################################
# Traffic
################################################################################

Target[traffic]: 2:public@ns2.myfolder.net:
SetEnv[traffic]: MRTG_INT_IP="211.62.49.37" MRTG_INT_DESCR="eth0"
MaxBytes[traffic]: 5625000
Title[traffic]: Traffic
PageTop[traffic]:
Traffic

################################################################################
# HTTPD Daemon
################################################################################

Target[http]: `/usr/local/mrtg-2.9.10/web_count.sh`
WithPeak[http]: my
MaxBytes[http]: 512
Title[http]: HTTPD Daemon
Options[http]: gauge,integer,nopercent,growright
YLegend[http]: Number of HTTPD
LegendI[http]: httpd daemon:
LegendO[http]: httpd session:
Legend1[http]: Number of httpd
Legend2[http]: Number of session
Legend3[http]: 5 Min Max Number of httpd
Legend4[http]: 5 Min Max Number of httpd session
ShortLegend[http]: 개
PageTop[http]:
HTTPD Daemon

################################################################################
# MySQL Daemon
################################################################################

Target[mysql]: `/usr/local/mrtg-2.9.10/mysql_count.sh`
WithPeak[mysql]: my
MaxBytes[mysql]: 100
Title[mysql]: MySQL Daemon
Options[mysql]: gauge,integer,nopercent,growright
YLegend[mysql]: Number of MySQL
LegendI[mysql]: mysql daemon:
LegendO[mysql]: mysql session:
Legend1[mysql]: Number of mysql
Legend2[mysql]: Number of session
Legend3[mysql]: 5 Min Max Number of mysql
Legend4[mysql]: 5 Min Max Number of mysql session
ShortLegend[mysql]: 개
PageTop[mysql]:
MySQL Daemon

################################################################################
# LOAD AVG.
################################################################################

Target[load]: .1.3.6.1.4.1.2021.10.1.3.2&.1.3.6.1.4.1.2021.10.1.3.3:public@localhost * 100
WithPeak[load]: my
MaxBytes[load]: 10000
Title[load]: LOAD AVG.
Options[load]: gauge,integer,nopercent,growright
YLegend[load]: Load Average
LegendI[load]: 5 min:
LegendO[load]: 15 min:
Legend1[load]: Load average for 5 Min
Legend2[load]: Load average for 15 Min
Legend3[load]: Max Load average for 5 Min
Legend4[load]: Max Load average for 15 Min
YTicsFactor[load]: 0.01
PageTop[load]:
LOAD AVERAGE

################################################################################
# Memory Usage
################################################################################

Target[memory]: .1.3.6.1.4.1.2021.4.6.0&.1.3.6.1.4.1.2021.4.15.0:public@localhost * 1024
MaxBytes[memory]: 1073741824
Title[memory]: MEMORY USAGE
kilo[memory]: 1024
WithPeak[memory]: my
Options[memory]: gauge,growright
YLegend[memory]: Memory Usage
LegendI[memory]: Used:
LegendO[memory]: Cached:
Legend1[memory]: Used Memory Size
Legend2[memory]: Cached Memory Size
Legend3[memory]: 5min Max Used Memory Size
Legend4[memory]: 5min Max Cached Memory Size
ShortLegend[memory]: B
PageTop[memory]:
MEMORY USAGE

################################################################################
# Disk Usage1
################################################################################

Target[disk]: .1.3.6.1.4.1.2021.9.1.8.1&.1.3.6.1.4.1.2021.9.1.8.2:public@localhost
Title[disk]: Disk Usage1
kilo[disk]: 1024
Options[disk]: gauge,growright
LegendI[disk]: /:
LegendO[disk]: /boot:
Legend1[disk]: / Usage
Legend2[disk]: /boot Usage
MaxBytes1[disk]: 8000000
MaxBytes2[disk]: 23000
ShortLegend[disk]: B
kMG[disk]: K,M,G,T,P
PageTop[disk]:
Disk Usage1

################################################################################
# Disk Usage2
################################################################################

Target[disk2]: .1.3.6.1.4.1.2021.9.1.8.3&.1.3.6.1.4.1.2021.9.1.8.4:public@localhost
Title[disk2]: Disk Usage2
kilo[disk2]: 1024
Options[disk2]: gauge,growright
LegendI[disk2]: /data:
LegendO[disk2]: /disk_sdb1:
Legend1[disk2]: /data Usage
Legend2[disk2]: /disk_sdb1 Usage
MaxBytes1[disk2]: 56000000
MaxBytes2[disk2]: 17000000
ShortLegend[disk2]: B
kMG[disk2]: K,M,G,T,P
PageTop[disk2]:
Disk Usage2

indexmaker mrtg.cfg > index.html 하면 여러페이지를 하나의 인덱스 페이지로 만들어줌..

크론에 등록해야 함..

정규화

데이터베이스의 설계에서 가장 중요한 것은 현실을 제대로 반영하는 것이며, 이를 어떻게 논리적으로 구성하는가를 결정하는 것입니다. 특히 관계형 데이터 모델에서는 데이터 값들이 2차원의 평면 테이블 형태로 표현하므로 어떤 릴레이션들이 필요하고, 어떤 애트리뷰트가 필요한가를 결정하는 것이 중요합니다. 

이젠 앞서서 직관적으로 바라보았던 것들을 '정규화'라는 원리를 도입할 것입니다. 정규화는 관계형 데이터 모델에서 아주 중요한 역할을 하고 있습니다. 학자마다 정규화는 튜닝의 도구 또는 설계 검증의 도구이다라고 의견이 약간씩은 다르지만 결국은 같은 의미를 가지고 있습니다. 현실을 제대로 반영하는 것은 튜닝과 검증이라는 것을 모두 포함하고 있기 때문입니다.

데이터베이스 설계를 할 때 우리는 단계적인 사고방식을 가져야한다고 했습니다. 단계적인 사고 방식에서 윗 단계를 생각해 봅시다. 대부분은 설계의 초기단계에서 복잡한 생각을 하지 않는다고 나중에 누락되지는 않을까 하고 생각하는 분들이 많습니다. 그러나 이러한 것들은 정규화 과정이나 앞의 단계를 거치면서 데이터 모델링은 멋있게 틀을 잡아갑니다. 이렇게 틀을 잡아가는 것중 정규화는 그야말로 아주 큰 역할을 하는 것입니다. 앞에서 행했던 설계를 검증하고, 데이터의 중복을 없앤다 것 자체가 정보의 질을 높이고, 설계의 튜닝을 하는 것이니까요.

정규화란 속성이 제위치에 제대로 찾아가게끔 하는 것입니다. 정규화의 목적은 당연히 데이터의 중복의 최소화와 여러가지 이상(Anomaly)들을 제거함에 있습니다. 데이터가 중복되어 있으면 여러 문제를 일으킬 수 있습니다. 삽입, 삭제, 변경에서 나타는 이상들이 개발자를 괴롭히는 것입니다. 이것은 결국 속성이 제자리에 있지 않기 때문에 발생하는 중복의 문제점 때문이라고 할 수 있습니다.

정규화 과정은 속성간에 관계성, 데이터 종속성, 성능, 데이터베이스의일관성 유지등을 고려해야합니다. 정규화를 검증도구라고 하는 것도 설계가 잘못되면 일어날 수 있는 여러가지 문제점을 예방하는 차원이기 때문입니다. 또한 데이터의 중복을 없앤다는 자체가 엄청난 튜닝의 효과를 가지는 것입니다. 일단 데이터의 중복이 많은 설계는 먼가가 문제가 있는 설계입니다. 데이터베이스의 정의에서도 언급했듯이 데이터베이스는 중복의 최소화로 기존의 파일처리방식이나 수작업 방식에서 오는 정보의 질을 떨어뜨리는 문제점을 없애는 것입니다. 이러한 문제점을 좋은 정보의 질을 유지하기 위한 하나의 정형화된 도구 즉, 정규화를 통해 해결을 하게 되는 것입니다.

정규화는 데이터의 중복으로 인한 문제를 해결하기 위해서 속성들간에 종속성(Dependency)을 분석해서 기본적으로 하나의 릴레이션(테이블)에 표현하도록 분해를 하는 것입니다. 어떻게 보면 테이블을 무작정 쪼개는 것으로 보일 수도 있습니다. 그러나 테이블을 쪼개는 기준은 "함수적 종속"이란 개념으로 쪼개는 것입니다. 즉, 함수적 종속성을 파악한 다음 그 함수적 종속을 기본으로 해서 속성들을 하나의 테이블로 그룹짓는 것입니다.

함수적 종속

그럼 "함수적 종속" 이란 것이 무엇인가 살펴보도록 하겠습니다. 일단 함수라 하면 다음의 그림과 같이 나타낼 수 있습니다.

이것을 테이블로 표현한다면 다음과 같겠지요.

감이 약간 오시나요?? 일단 함수적 종속이란 말에서 함수라는 것에 대해서 살펴보도록 하겠습니다. 함수가 무엇인가요? 다름의 그림을 보고서 설명을 하도록 하겠습니다.

그림처럼 왼쪽은 2라는 원소가 두개가 들어 있습니다. 2를 어떤 함수에 집어 넣어야지 4가 될까요? 일반적으로 생각해 보면 2의 제곱을 구하는 함수에 값을 집어 넣는다면 4가 되겠지요. 그러나 역으로 4를 집어 넣는다면 16이 되버리는 것입니다. 즉, 2와 2는 4가 되기 위해서 함수적으로 종속되어 있는 것입니다. 결국 4는 2와 2가 4가 되기 위한 함수에 종속적이기 때문에 2의 제곱이라는 함수에 종속적이게 만드는 4는 "결정자"라고 부릅니다. 또한 2와 2를 "종속자"라고 합니다.

사실 이렇게 수학적으로 함수적 종속을 설명하였으나 정규화는 시스템을 구축하고자하는 조직내의 의미에 함수적 종속을 설명해야 합니다. 즉, 정규화는 관계형 모델에서 표현하고자 하는 주제가 동일한 속성들끼리 뭉쳐져 있는 것입니다. 그러므로 설계의 초기부터 나타내고 자하는 정보를 의미론적으로 묶는다면 정규화라는 과정의 설계의 검증도구가 되는 것입니다.

위의 예에서 2를 Y로 하고 4를 X라 한다면 함수적 종속의 표현은 "X  Y" 로 표기합니다. 이러한 표현을 실제의 예를 들어서 살펴보겠습니다.

학생 릴레이션에서 ...

학번  이름

학번  학과

학번  학년

위에서 보는 바와 같이 학번은 이름, 학과, 학년을 결정하고 있습니다. 즉, 학생 릴레이션에서 각각의 학생을 유일하게 구분지을 수 있는 속성은 학번으로 학번은 기본키의 역할을 하는 속성입니다. 즉, 이름만 가지고는 각각의 학생을 알 수 없다는 것입니다. 제가 옛날에 휴학을 하려고 했는데 시간이 없어서 조교님께 휴학 신청을 대신해달라고 한 적이 있었습니다. 그런데 저와 같은 이름을 가진 다른 사람이 있었는데 그 사람으로 휴학을 한적이 있어서 상당히 난감했던 적이 있습니다. 즉, 저의 이름인 "이재학" 만 가지고는 정확성이 있는 정보가 될 수 없던 것입니다. 만약 교수님께서 "이재학"을 불러오라 라고 한다면 이미 과에 "이재학"이란 이름을 가진 사람이 2명이라는 것을 아는 사람은 학번을 교수님께 되물을 것입니다. 이렇듯 이름은 학번에 종속되어 있다는 것입니다. 이름이 이재학이고, 학과가 정보통신공학과이고, 4학년인 학생은 저 말고도 한명이 더 있습니다. 즉, 이것들은 학번에 종속적이라는 것입니다.

테이블로 표현한 것을 가지고 좀더 살펴보도록 하겠습니다.

9555023, 이재학, 4학년, 정보통신공학과 라는 것은 한명의 학생에 대햔 데이터입니다. 학번, 이름, 학년, 학과중에 대표성을 띄고 있는 것은 학번이고, [9555023, 이재학], [9555023, 4학년], [9555023, 정보통신] 이라고 해야지만 정확한 정보를 표현할 수가 있는 것이죠. 앞에서 언급했듯이 [이재학, 4학년, 정보통신]이라고 해서 정확한 정보가 되는 것일까요? 당연히 학번이 9555023인 학생과 학번이 9839011인 학생을 구별하지 못하는데 문제가 있습니다.

이렇듯 학번은 이름, 학년, 학과를 결정하고, 이름, 학년, 학과는 학번에 종속적입니다. 이렇게 데이터에 대한 의미를 표현한 것을 함수적 종속이라고 합니다.

<참고>

함수 종속에 대한 증명된 추론 규칙 (R  릴레이션)

(데이터베이스시스템, 이석호, 정익사)

R1: (반사규칙) A  B 이면 A  B 이다.

R2: (첨가규칙) A  B 이면 AC  BC 이고 AC  B 이다.

R3: (이행규칙) A  B 이고 B  C 이면 A  C 이다.

R4: (분해규칙) A  BC 이면 A  B 이다.

R5: (결합규칙) A  B 이고 A  C 이면 A  BC 이다.

정규화는 이러한 "함수적 종속"을 기본 원칙으로 하나의 의미를 가지는 집합으로 나누는 작업입니다. 정규화는 1차 정규화, 2차 정규화, 3차 정규화, 보이스/코드 정규화, 4차 정규화, 5차 정규화, 도메인/키 정규화가 있습니다. 이 순서가 높아지는 단계의 정규화일수록 무결성은 강화되나 과도하게 테이블이 쪼개지므로 쓸 때 없는 부하가 걸릴 수 있습니다. 그러므로 현실을 감안해서 어느 정도 수준의 정규화까지 행해야 하는가를 결정해야 합니다. 보통 실무에서는 3차 정규화와 보이스/코드 정규화까지 합니다. 기본으로 3차 정규화까지는 해야 하며, 3차 정규화 과정을 마치고도 사용자의 요구사항에 의해서 여러가지 이상들이 발생할 요지가 있다면 더 높은 차원의 정규화를 해야 합니다. 이제 각 단계별 정규화에 대해서 설명하도록 하겠습니다.

1차 정규화

하나의 릴레이션은 어떤 도메인의 집합입니다. 각각의 속성은 해당 도메인에 속하는 단지 하나의 값을 가져야 합니다. 이것이 1차 정규화 이며, 실제로 1차 정규화도 거치지 않은 테이블이 많이 존재합니다. 예를 들면 다중값 속성들이 그대로 표현될 때 정규화된 테이블이 아닌 즉, 비정규화 테이블인 것입니다. 사원테이블에 사번, 이름, 보유기술, 월급의 속성이 있는데 보유기술은 여러 개를 가질 수 있다는 현실을 생각해 보도록 하겠습니다. 일반적으로 문서를 만들면 다음과 같은 문서가 나올 수 있습니다. 보유기술에서 하나의 속성값에 여러 개의 값이 들어간 것을 볼 수 있습니다. 이러한 표현은 비정규화 된 테이블입니다. 여기서 기본키는 사원번호입니다. 

그렇다고 다음과 같이 보유기술을 옆으로 펼친다고 해서 달라지는 것은 없습니다. 이도 역시 1차 정규화된 테이블이 아니라고 볼 수 있습니다. 의미상으로 보유기술1, 보유기술2, 보유기술3는 그냥 보유기술의 종류일 뿐입니다. 그러니 하나의 속성에 여러 개의 값을 다른 표현으로 한 것 뿐입니다.

어떤 독자분은 보유기술 속성에 속성값을 넣는데 콤마로 구분하면 어떻겠냐? 라는 생각을 가질 수도 있습니다. 만약 사원번호 1111 인 사원의 보유기술을 하나더 추가하려면 기본키가 "사원번호" 이므로 "사원번호"가 1111 인 사원이 추가되지 못하는 것을 알 수 있습니다.

즉, 다음과 같은 그림이 되어 삽입을 할 수 없는 형태가 되는 것입니다.

이제 본격적인 정규화에 대해서 알아보겠습니다. 아래의 테이블은 각각의 학생에 대해서 각각의 속성마다 단일값을 가지고 있으므로 1차 정규화된 테이블이 입니다. 여기서 독자분들은 함수적 종속관계를 찾아보아야 할 것입니다. 여러분은 찾아낸 함수적 종속성을 바탕으로 데이터가 많이 중복되어 있고, 그 데이터의 중복으로 인한 여러가지 문제점(이상)들을 찾아내어야 합니다.

이 테이블의 함수적 종속 다이어그램의 다음과 같습니다.

함수적 종속 다이어그램에서 보는 바와 같이 학번은 학생명과 학년을 결정하고, 학번은 학과를 결정합니다. 또한 수강코드는 담당교수와 과목명을 결정합니다. 각각의 학생은 자신이 수강신청한 과목의 성적등급을 알기 위해서 학번과 수강코드가 필요합니다. 담당교수는 학과를 결정합니다. 여기서 자칫 잘못하면 학과가 담당교수를 결정할 수 있다고 볼 수 있는데 하나의 학과에 소속된 교수는 여러명인 것이 보입니다. 즉, 지도교수가 학과를 결정하는 것이지 학과가 교수를 결정하는 것이 아닙니다.

이제 1차정규화된 테이블을 가지고 나타나는 문제점을 살펴보도록 하겠습니다. 이러한 문제점은 함수적 종속과 관련하여 찾아보아야 합니다.

이 테이블은 학번만 가지고는 어떤 과목의 성적의 등급이 얼마인지를 모릅니다. 그러므로 이 테이블의 기본키는 학번 + 수강코드입니다. 여기서 주의할 것은 수강코드라는 속성의 이름이 수강을 해야만 하는 코드가 아니라는 사실입니다. 즉, 수강할때 그 과목을 나타내는 과목의 고유번호응 나타내는 것입니다.

2차 정규화

앞서서 삽입, 삭제, 갱신 이상들이 일어 날 수 있다는 것을 보았습니다. 1차 정규화된 테이블에서 이러한 문제점이 일어나는 원인이 무엇일까요? 원인은 바로 기본키가 아닌 각각의 속성들이 기본키에 종속적이지 않고, 부분적으로 함수 종속이 되기 때문입니다. 즉, 기본키를 제외한 모든 속성이 기본키에 함수적 종속이 아니기 때문입니다. 이러한 문제를 해결하기 위해서는 기본키에 함수 종속을 시킨 것 끼리 따로 테이블을 만들어야 합니다. 2차 정규화된 테이블은 다음과 같습니다. ( 테이블 밑은 점(...)은 생각치 않도록 하겠습니다. )

2차 정규화의 결과로 위와 같은 3개의 테이블이 나왔습니다. 학생과 수강과목은 다:다의 관계를 맺고 있기 때문에 학생의 학번과 수강과목의 수강코드가 합쳐진 것을 기본키로 하여 학생 테이블과 수강과목 테이블을 연관지어 주고 있습니다. 사실 데이터 모델링의 초기단계부터 차근 차근 진행해 오면서 엔티티만 제대로 도출해 냈다면 이와 같은 2차 정규화 작업은 필요가 없습니다. 그러나 이와 같은 검증된 원리를 알고 있다면 애매모호 함이 없어지고,  좀더 정확한 정보를 산출하는 정보시스템이 만들어 질 수 있는 것입니다.

학생 테이블과 수강과목 테이블에 학과라는 속성이 중복되어 존재하는 것이 보입니다. 이것은 어떤 다른 엔티티 집합이 더 존재한다는 것을 의미합니다. 즉, "학과" 엔티티가 "학생"과 "수강과목"과 관계를 맺고 있다는 것입니다. 위의 테이블을 볼 때 학과와 관련된 엔티티와 학생, 수강과목은 1:다의 관계를 맺고 있음을 알 수 있습니다. 즉,

학과 : 학생  1 : 다

학과 : 수강과목  1 : 다

의 관계입니다. (사실 더많은 객관적인 업무규칙이 파악되어야 정확히 알수 있습니다.) 그러므로 속성의 이름은 학과보다는 "학과명"이라고 하는 것이 더 명확하겠지요. 그러나 이 단원에서는 다른 엔티티 집합은 생각치 않고, 이에 대한 고려는 다음 단원에서 하겠습니다.  단지 이것은 정규화를 위한 예제라는 것을 염두해 두시기 바랍니다.

이제는 어느정도 속성들이 자신이 있어야 할 곳에 배치된 것으로 보입니다. 그러나 이러한 2차 정규화 테이블에도 이상들이 존재합니다. 이러한이상들을 살펴보도록 하겠습니다.

2차 정규화된 테이블에서 이상이 일어나는 이유는 기본키가 아닌 다른 속성들 간에 함수적 종속이 일어나기 때문입니다. 이렇게기본키가 아닌 속성들끼리의 종속성을 가지는 것은 이행 종속(Transitive Function Dependency) 라고 합니다. 이러한 개념으로 위의 테이블에서 일어날 수 있는 문제점을 찾아보록 하겠습니다. 먼저 문제의 이행 종속이 일어나고 있는 테이블을 추려내야 합니다. 앞서서 그려본 함수적 종속 다이어그램에서와 같이 이행 종속이 일어나고 있는 테이블은 수강과목 테이블입니다.

삽입이상:

각 과목을 담당하는 교수가 특정 학과에 속한다는 사실을 삽입하려 할 때 과목이 존재하지 않는다면 이 사실을 삽입할 수 없습니다. 즉, 각각의 행을 구분할 수  있는 대표성을 지닌 속성(기본키)가 존재하지 않으므로 삽입이 불가능하다는 것입니다. 기본키는 수강과목 테이블에서 반드시 들어가야만 하는 것인데 이를 무시한채 다른 속성값을 삽입하려 한 것이 문제입니다.

삭제이상:

만약 해당 학과의 커리큘럼이 바뀌어 오상훈 교수가 담당하고 있는 과목인 "자료구조"라는 과목이 없어진다면 오상훈 교수가 정보통신공학과에 소속된다는 사실도 없어지게 됩니다. 오상훈 교수가 정보통신공학과에 소속된다는 사실은 이행종속이 일어나고 있다는 것이며, 역시 2차 정규화된 테이블에서 일어나는 삭제이상도 이행 종속때문이라는 것을 알 수 있습니다.

갱신이상:

만약 박덕규 교수의 소속 학과가 정보통신에서 다른 학과로 변경된다면 박덕규 교수에 해당되는 학과에 대한 속성값을 모두 변경시켜 주어야 합니다. 역시 이행종속이 일어나서 일어나는 이상현상입니다.

3차 정규화

2차 정규화된 테이블에서 이행 종속에 의해서 여러 가지 이상현상이 발생되는 것을 보았습니다. 이러한 여러 이상현상을 제거하려면 어떻게 해야 할까요? 당연히 이상현상의 원인이 되었던 이행종속을 없애면 됩니다. 어떻게 없애야 할까요? 당연히 이행종속을 일으키는 속성들을 묶어서 그룹지어 주면 문제는 해결됩니다.

이렇게 이행 종속성을 제거한 테이블은 3차 정규화된 테이블입니다. 만약 독자분들 중에 여기까지 대충 읽어 보신분들은 아마도 정규화란 것이 테이블을 쪼개는 것이구나 라고 생각하시는 분들도 계실겁니다. 그러나 테이블을 쪼갠다는 개념을 갖지 말고, 좀더 세부적으로 관련성이 많은 것끼리 새로운 그룹을 만드는 개념으로 정규화를 생각하셔야 합니다.

<쉬어가기>

자존심...

여러분은 느껴보셨는지요? 공대인이 마음이 닫혀있다는 것을... 얼마전 웹상에서 알게된 친구가 저에게 메신저로 호출을 하더군요. 그래서 왜 그러느냐고 했더니 지금 큰일났다고 하더군요. 다시 왜 그러냐고 물었더니 지금 자기가 하고 있는 프로젝트가 원래 D사 의 프로젝트인데 하청으로 받아서 하고 있는데, D사 사람이 와서 DB설계 개판(?)으로 했다고 하면서 엄청 깨졌다고 하더군요. 그러더니이거 DB 설계 다시해서 그쪽 사람과 협상해야 한다고 하소연을 했습니다. 그래서 한번 보자고 했습니다. ㅡㅡ;;

저는 그 DB설계 해논 것을 보고 이게 도대체 머냐? 그럴만 하다 라고 했습니다. 그랬더니 자기자신도 모르는 상태에서 했기 때문에 그럴만 하다라는 것을 인정하면서도 너무나도 화가 난다고 하는 것이 였습니다. 왜 화가 나는 것일까요? 자신이 해논 것에 대한 쓸 때 없는 자존심 때문일까요?

이상스럽게 이쪽 분야에서 일하는 사람이나 공부하는 사람들은 지는 것을 싫어합니다. 사실 게임도 아닌데 많은 사람들이 자신이 제시한 솔루션이 가장 옳다라고 우겨서(?) 이기고자하는 경향이 있습니다.(물론 필자도 그런 경향이 매우 짙게 나타납니다. 모르는 것도 전에 알던 지식을 이용해서 논리적으로 엮어서 아는 척하는 합니다. ㅡㅡ;;) 정보기술분야는 너무나도 빨리 발전하고 있습니다. 거의 대부분의 사람이 따라가기 바쁘지요. 물론 필자도 따라가기가 너무 힘듭니다. 그래서 매일 꾀죄죄한 모습으로 학교에서 중국음식에 길들여지면서 고생하는 이유일지도 모르겠습니다. 다음 그림을

여러분은 마음을 열고 다른 사람의 것을 받아들일 수 있어야합니다. 너무나도 빨리 발전하고 있는 기술을 따라가는데 가장 빠른 지름길은 사람과 사람이 나누는 Communication 일것입니다.

보이스/코드 정규화

3차 정규화도 여러가지 이상이 존재합니다. 그렇다면 이상이 발생하지 않는 정규화 과정은 어떤거냐고 의문을 가지는 분도 있을 겁니다. 이상이 발생하지 않는 정규화는 키/도메인 정규화입니다. 이것은 증명은 되었으나, 키/도메인 정규화 테이블을 만드는 구체적인 방법을 발견하지 못했기 때문에 실무에서 직관적으로 사용되는 방법이기도 합니다. 그러나  보통 실무에서는 3차 정규화과정이나 다음에 할 보이스/코드 정규화까지 합니다. 그 이유는 일반적으로 4차 정규화나 5차 정규화 과정을 거쳐야 하는 상황은 거의 발생하지 않기 때문입니다. 이 책에서는 보이스/코드 정규화 과정까지만 언급하겠습니다. 만약 보이스/코드 정규화 과정을 거쳤으나 사용자가 원하는 작업을 수행할 때 이상이 발생한다면 4차 정규화 과정을 거쳐야 할 것입니다. 4차, 5차 정규화는 다른 책을 참고하셔야 할 것입니다.

이제 위의 3차 정규화를 거친 테이블에 대한 이상현상이 발생하는 원인을 분석하고 보이스/코드 정규화에 대해서 언급하도록 하겠습니다.

3차 정규화 과정을 거치 테이블에서 이상현상을 발생시키는 원인은 후보키들이 중첩되어 있다는 것 때문입니다. 후보키는 기본키가 될 수 있는 자격이 있는 속성 또는 속성들입니다. 즉, 하나의 릴레이션에 여러 개의 후보키가 존재하는데 하나 또는 여러 개의 속성이 중첩되어서 후보키될 때 이상현상이 발생할 수 있다는 것입니다. 보이스/코드 정규화 과정은 바로 이러한 문제점을 해결하는 것입니다. 이러한 의미에서 볼 때 보이스/코드 정규형은 엄격한 3차 정규형이라고도 합니다.

보이스/코드 정규형은 릴레이션의 모든 결정자가 후보키이면 보이스/코드 정규형이라고 보는 것입니다. 결정자라는 개념은 어떤 속성을 함수적으로 완전히 종속시키는 속성을 의미합니다. 만약 다음의 업무 규칙이 존재하는 테이블이 있다고 가정 한다면

-. 하나의 과목을 여러 교수가 담당할 수 있다.

-. 각 교수는 하나의 과목만을 담당한다.

-. 각각의 학생은 같은 과목명을 가진 다른 과목을 수강하지 못한다.

앞서서 언급한 3차 정규화의 문제점인 후보키의 일부가 되는 속성인 "학번"이 중첩되어 있는 것이 보입니다. 즉, 수강_교수 릴레이션의 후보키는 "학번 + 과목명" , "학번 + 담당교수" 입니다. 이 후보키중 "학번 + 과목명"을 기본키라고 가정하겠습니다. 함수 종속 다이어그램에서 보는 바와 같이 "학번 + 과목명"은 "담당교수"를 결정하고, "담당교수"는 "과목명"을 결정합니다. 이런 구조를 가지고 있는 릴레이션의 문제점을 파악해 보도록 하겠습니다.

삽입이상:

만약 이현태 교수도 자료구조를 담당하게 되었다면 수강신청을 한 학생이 있어야만 이와 같은 사실을 입력할 수 있습니다. 만약 "담당교수"의 의마가 해당 과목을 담당하고, 또한 그 학생에 대한 생활지도 등의 "지도"를 할 수 있다면(여기서는 담당과목을 수강하지 않은 학생도 지도할 수 있다는 가정), 과목을 수강하지 않은 학생은 지도교수가 누구인지 결정을 할 수 없게 됩니다.

삭제이상:

학번이 "9655032" 인 학생이 자료구조의 수강 취소를 한다면 오용선 교수가 자료구조를 담당하고 있다는 사실도 함께 삭제됩니다. 이 뿐만 아니라 다른 과목들도 마찬가지로 수강하는 학생이 수강을 취소한다면 과목에 대한 담당교수도 같이 삭제되므로 이상현상이 일어납니다. 만약 다른 수강 신청자가 있다면 이와 같은 사실은 같이 삭제되지 않으나 현재 상황으로 볼 때 어떤 교수가 어떤 과목을 담당하고 있는지를 나타내는 것이 한 개의 투플(행)뿐이기 때문에 이러한 문제를 해결되어야 합니다.

갱신이상:

만약 이현태 교수가 "DB" 에서 "네트웍 프로그래밍"으로 담당과목이 바뀌었다면 3개의 투플(행)을 모두 변경해주어야 합니다.

이러한 문제점은 보이스/코드 정규화 과정을 거치면 해결되는 문제입니다. 즉, "모든 결정자가 후보키" 가 되게 하면 되는 것입니다. 다음은 보이스/코드 정규화의 결과입니다.

이제 여러분은 1차 정규화에서 3차 정규화 까지를 종합적으로 살펴볼 필요가 있습니다. 즉, 이러한 원리만 알고 있다면 바로 3차 정규화 또는 보이스/코드 정규화까지 직접 도출이 가능합니다.  직접 도출하는 예를 들어 보겠습니다. 다음과 같은 스키마가 존재하다고 가정하겠습니다.

대출 (대출번호, 고객명, 지점명, 지점위치, 자산, 대출합계)

이 스카마는 어떤 은행은 대출에 관련된 스키마입니다. 이 스키마를 가지고 함수적 종속만 파악한다면 나머지 보이스/코드 정규형을 도출하는 과정은 간단합니다. 다음은 이 스키마에 대한 함수적 종속을 나타내는 것입니다.

<함수적 종속>

지점명  자산

지정명  지점위치

대출번호  대출합계

대출번호  지점명

도출한 R1, R2, R3, R4, R5는 모두 보이스/코드 정규형을 만족합니다. 각각의릴레이션의 모든 결정자가 후보키입니다. 그러나 이렇게 너무 불필요한 정규화는 결과적으로 성능을 떨어뜨릴 수 있습니다. 그러므로 다음과 같은 통합작업을 거쳐야 합니다.

결과적으로 R1(지점명, 자산),  R2(지점명, 지점위치),  R3(대출번호, 대출합계),  R4(대출번호, 지점명), R5(대출번호, 고객명)으로 일단은 테이블을 최대한 분해하였습니다. 그러나 R1과 R2는 기본키가 같으므로 통합할 수 있습니다. 그러므로 R1_2 (지점명, 자산, 지점위치) 로 통합되고, R3와 R4, R5가 기본키가 같으나 R3, R4와 R5는 은행(R3, R4)과 고객(R5)으로 서로 다른 것을 나타내므로 R3와 R4는 통합되고, R5는 독립적으로 존재하게 됩니다. 즉, (R3, R4)와 R5는 표현하려는 정보가 틀리기 때문에 통합이 불가능합니다. 마지막에 나온 R5는 원래 정규화되기 전의 원래 테이블의 기본키가 됩니다. 결과적으로 다음과 같이 보이스/코드 정규화가 이루어졌습니다.

R1_2 (지점명, 자산, 지점위치)

R3_4 (대출번호, 지점명, 대출합계)

R5 (대출번호, 고객명)

결과적으로 정규화라는 과정은 함수적 종속이라는 하나의 원칙으로 관련성으로 속성들을 묶어서 데이터의 중복을 없애고, 데이터의 중복에 의한 여러가지 이상현상을 없애는 유용한 도구입니다. 데이터의 중복이 최소화되는 자체는 시스템이 가장 가벼운 데이터를 가지고 처리하기 때문에 전체적인 시스템의 성능이 높아지기도 하는 것입니다.

키/도메인 정규화

'정규화란것은 '함수적 종속'관계를 파악하는 것입니다. 이 종속관계를 파악하여 속성이 원래 갈 자리에 가게 하는 것입니다. 즉, 주제에 맞는 한 객체가 관련된 업무에 관한 속성들이 있어야 할 곳에 있게 하는 것입니다. 우리는 1차 정규화에서 보이스/코드 정규화까지 알아보았습니다. 보통 실무에서는 3차 정규화나 보이스/코드 정규화 때에 따라서는 아주 가끔씩 4차정규화를 행합니다.

그러나 검증은 되었으나 그 방법이 찾아지지 않은 키/도메인 정규화를 이 글에서 이야기하고자 합니다. 키(key)라는 것은 객체를 유일하게 구별할 수 있는 속성중에 가장 관련된 대표적인 것을 이야기 합니다. 이 키에 함수적으로 모두 종속되고, 속성의 도메인이 맞다면 즉, 모든 제약이 키와 도메인의 정의에 따른 논리적인 결과인 것은 모두 키/도메인 정규화인 것입니다.

이것은 완벽한 정규화입니다. 3차 정규형은 기본키에 모두 함수적 종속적인 것들로 테이블을 분리하고, 기본키가 아닌 속성들끼리의 종속성 즉, 이행종속을 일으키는 속성들을 다른 테이블로 옮기고 그 테이블에 기본키를 정의할 수 있으면 됩니다. 이와 같이 분리된 테이블은 기본키를 가지게 됩니다. 이러한 개념으로 테이블을 봤을때 데이타가 중복되어 나타나는 것들 잘 살펴보면, 왜 중복이 일어났는지 알수 있을 것입니다.

정규화의 해법들이 키/도메인 정규화 빼고는 모두 나와있습니다. 그러나 직관적으로 바라본다면 해법이 나와 있지는 않지만 키/도메인 정규화가 더 쉽습니다. 또한 초기에 엔티티를 선정할때 우리가 시스템화 하고자하는 관련된 것들끼리 모인 즉, 엔티티를 잘 선정한다면 직관적인 관점에서 3차정규화는 충분히 할 수 있으리라 생각합니다.

그렇다고 정규화 과정을 무시해서는 안됩니다. 이렇게 직관적으로 설계를 하면서 나갈때는 정규화는 검증도구가 되는 것입니다. 학자에 따라서 정규화는 검증도구다 또는 튜닝도구다라고 하는 의견들이 분분합니다. 그러나 정규화는 반드시 필요한 것이 틀림이 없습니다.

도메인/키 정규화에서 중요한 단어는 제약, 키, 도메인입니다. 한가지 주의할 것은 제약에 시간의 개념을 뺏다는 것입니다. 엑기스만 뽑는다면 키와 도메인에 대한 제약을 준수시켰을때 모든 제약이 준수되는 릴레이션은 키/도메인 정규형입니다.

다시 핵심단어 키, 도메인, 제약 이 세가지의 관점에서 살펴보겠습니다. 키라는 것은 객체들을 유일하게 구분지어 주는 속성입니다. 즉, 속성들중 대표하는 것을 말합니다. 이 속성들이 가질수 있는 값들의 범위를 정의한 것이 바로 도메인입니다. 독자들중에 아시는 분이 별로 없으시겠지만 푸리에변환 같은 것을 보면 시간 도메인에서 주파수도메인으로 주파수 도메인에서 시간 도메인으로 변환을 할 수 있습니다. 시간 도메인에서 본다면 이 값들은 절대로 변환과정을 거치지 않고는 시간이라는 단위밖에 가지지 못하는 것입니다. 실제로 주민번호를 본다면 생년월일 담에 오는 1이란 숫자는 남자밖에 가지지 못하는 숫자입니다. 이 도메인을 벗어난다면 현실에 맞지 않게 되는 것입니다. 누누히 얘기하지만 데이타베이스는 현실을 최대한 반영하는 것입니다.

정리하자면 키라는 것은 "unique + not null + 대표성" 입니다. 도메인은 앞에서 얘기한 것처럼 속성이 가질수 있는 값의 범위이고 가질수 있는 꼭 그것을 가져야만 하는 의미입니다.

이것은 현실의 제약이라고 볼수 있으며, 이러한 제약이 지켜진다면 이것은 완벽한 이상이 없는 정규형입니다.

정규화의 정리

이제 앞에서 살펴보았던 정규화에 대해서 의미로만 따져도록 하겠습니다. 필자의 경우는 정규화 과정은 검증도구로 사용하는 편입니다. 사실 함수적 종속이란 것이 조직의 범위내에서 통용되는 의미에 따라 틀려집니다. 그러므로 모델링을 하기전 단계에서부터 정보시스템을 구축하려는 조직에서 사용되는 정보의 의미를 파악하는 것이 더 중요하다고 합니다.

3차 정규화된 테이블을 보면 테이블마다 어떤 정보들을 나타내기 위해서 데이터 들이 뭉쳐있습니다. 즉, 각각의 속성들이 뭉쳐서 어떠한 하나의 정보(의미)를 만들어 내기 때문에 그 의미만 잘 파악한다면 앞에서 행했던 것처럼 바로 보이스/코드 정규화까지 직접 도출이 가능한 것입니다. 그냥 어떤 "의미"를 나타내기 위해서 그룹짓는 과정이라고 하기엔 너무 애매모호 합니다. 그래서 함수적 종속이란 개념을 도입하여 누구나 고개를 끄덕이게 만든 것이 정규화입니다.

독자가 파악해야 할 것은 사용자의 정보가 무엇인지 정확하게 판단하여야 할 것입니다. 만약 사용자의 요구사항이 정확하게 파악되면, 그 요구사항을 정확히 반영하기 위해서 엔티티를 도출하고, 각각의 속성들을 배치해야 합니다. 엔티티는 속성의 집합이기도 합니다. 하나의 엔티티가 다른 엔티티와 관계를 맺고, 어떠한 정보를 만들어 낼 수 있습니다.

일반적으로 데이터 모델링은 하향식(Top-Down)의 방식으로 설계를 하고, 정규화를 통한 하향식(Bottom-up) 방식으로 검증을 하는 방법론을 사용합니다. 이제 속성을 가지고 다음 그림을 살펴보도록 하겠습니다.

제일 먼저 파악해야 할 것은 "관련성" 입니다. 릴레이션이란 것이 속성들이 어떠한 관련성에 묶여서 있는 모습입니다. 그러니 관련성이라는 의미는 매우 중요한 것입니다. 이것은 기본키와의 관련성입니다. 하나의 개체( 학생으로 하였을 경우 학번이 9555023인 학생 하나는 인스턴스입니다.)를 대표하는 것이 기본키이기 때문입니다.

두번째는 파악해야 할 것은 속성의 도메인입니다. 각각의 속성은 가질 수 있는 값의 범위 즉, 도메인을 가지고 있습니다. 이 도메인에서 표현할 수 있는 속성값들을 대표할 수 있는 것들을 찾는 것입니다. 이 도메인도 관련된 엔티티의 범위를 가지기 때문에 관련성이라는 것은 매우 중요합니다.

의미상으로 볼 때 학번과 학생명이 같은 객체의 다른 표현인 것을 알 수 있습니다. 그러나 한 학년에 속하는 학생이 여러명인 것을 알 수 있지만, 학년자체는 독립적으로 존재할 수 있는 즉, 엔티티가 아니라 속성이라는 것입니다. 결과적으로 이 릴레이션은 "학생" 엔티티 집합이 포함된 것입니다. 

이러한 방식으로 속성값을 살펴보면 위의 그림이 나올 수 있습니다. 그림에서 학과명과 관련된 것을 살펴보면, 일단 학번이 이름과 학년을 결정하는 것은 앞에서 체크었으므로 이 두 속성은 제외하고 학번으로만 생각해보도록 하겠습니다. 학번이 학과명을 결정하나요? 이 부분은 상당한 혼돈의 여지가 있습니다. 그러나 좀더 원천적으로 생각하면 데이터베이스 시스템을 개발하려는 도메인이 무엇인가요? 바로 "학교"입니다. 즉, 학교에는 기본적으로 "학생"과 "학과"가 존재해야 "학교"가 존재할 수 있는 것입니다. 즉, 학과와 학생은 기본엔티티 집합인 것입니다. 이렇게 "닭이 먼저냐? 달걀이 먼저냐?" 라고 따지는 상황이 온다면 이것은 기본엔티티 집합입니다. 그러므로 학과명은 "학과" 엔티티 집합의 속성입니다. 그러므로 이것은 외부키인 것입니다. 그렇다면다른 속성은 어떨까요? 당연히 다른 속성들도 따져볼 것이 못되는 것입니다. 만약 관련이 있다면 그것은 외부키로 의 기능을 하는 속성입니다.

결과적으로 이 테이블에서 도출할 수 있는 엔티티 집합은 "학생", "수강(또는 과목)", "학과", "교수" 입니다. "학생" 과 "수강" 은 다:다의 관계를 맺고 있으므로 "수강코드, 학번, 등급" 은 이 다:다의 관계를 해소한 것이 되는 것입니다.

이렇게 엔티티와 속성과 관계를 도?하는 것은 데이터 모델리의 핵심입니다. 이와 같은 기본적인 것만 확실히 파악이 된다면 정규화는 데이터 모델링의 검증의 도구와 튜닝의 도구로 써 훌륭한 역할을 할 것입니다.

정규화(NORMALIZATION)
데이터베이스에서 테이블들을 설계시 여러 테이블들로 분할해서 데이터를 저장하고 있구나 하는 것을 알게 될 것이다. 그러면 왜 그런 형식으로 설계를 할까? 그것은 데이터를 논리적이고 비반복적인 형태로 분리하기 위함이다. 이것이 바로 정규화이다. 정규화는 관계형 데이터베이스의 개념과 같이 시작이 되었다.

테이블은 행(row)들과 열(column)들로 구성된 하나의 실체이며 데이터의 컬렉션인 엔티티(entity)이다. 하나의 테이블은 다른 테이블들과의 관계(relationship)를 맺을 수 있다. 이 관계 구성의 논리화가 바로 정규화로써 이루어지게 된다.
  정규화는 6차를 거쳐서 진행이 된다. 하지만 3차까지 만을 알아 보겠다. 왜냐하면 나머지 단계들은 실무에서는 거의 사용되지 않고 학계에서의 고려 대상이 되고 있기 때문이다. 정규화에서 중요한 것은 바로 기본 키가 된다. 모든 열들이 바로 기본 키에 대해서 존재의미를 갖는 3차 정규형식에 대해서 알아본다.

설계의 출발은 데이터 저장 매체인 엔티티(테이블)들을 구성하는 것이다. 일단은 서비스 흐름에서 찾아지는 기본적인 엔티티를 찾아서 그 엔티티로부터 정규화를 거치는 예를 들어 보도록 한다. 전자제품 대리점에서 고객이 물건을 주문하면 그 고객의 주문 정보가 생성이 된다. 바로 첫번째 엔티티가 생성이 되는 것이다. 그러면 ‘주문’이라는 엔티티에는 어떠한 항목들(필드 설정)이 들어가야하는지 다음의 테이블로 구현을 했다.

1차 정규형식(1NF, first normal form)
: 반복(중복)되는 데이터(그룹)를 분할하고 각 필드 값은 원소성(atomicity)이 되도록 한다.
  (여기서 분할의 설정이 고정 데이터인가, 가변적 데이터인가도 생각)

중복된 데이터를 찾아보기 위해 ‘주문’테이블에 다음처럼 발생 가능한 데이터를 입력해 보자. 이 테이블에는 하나의 주문에 대한 모든 정보가 다 들어 있다. 고로, 중복된 데이터가 있을수 있는 것이 된다.

이 테이블은 주문에 대한 자세한 정보가 들어가 있다고 했다. 그러면 왜 이테이블을 분할해야 하는가? 그 이유는 다음과 같다.

ㄱ. 데이터를 여러 번 저장하는 것은 공간 낭비

ㄴ. 반복적인 데이터가 존재한다는 것은 데이터의 이동량이 더 크게 된다. 따라서 데이터 버스나

    네트워크 대역폭에 더 많은 부담이 생긴다. 이는 전반적인 성능에 상당한 악영향을 미친다.

ㄷ. 반복 데이터들 간의 서로 모순된 데이터를 저장할 수 있다. 이것은 데이터 무결성이 깨져버린 

    것이다.

ㄹ. 결합된 데이터를 가진 열에 대해 어떠한 쿼리 명령을 내릴 경우  그 열 안에서 원하는 정보를

    분리시키는 과정을 거쳐야 하기에 속도가 떨어지게 된다.

1차 정규화를 시작하자. 이 테이블에서의 문제는 다음과 같다.
ㄱ. 고객 정보 부분은 데이터의 중복이 되어있다. 이 부분을 다른 테이블로 분할을 하게 된다.
ㄴ. 주문 상품 필드는 복합적인 데이터를 가지고 있다. 이 필드를 개별적인 필드들로 분할을 하게

    된다.

ㄱ.

고객 정보 부분을 다른 테이블로 분할을 할 때 생각을 해야할 것이 있다. 주문 테이블에서 떼어

   낸 후 연결을 할 매개체를 지정해야 하는 것이다. 그 연결은 ‘고객번호’필드로써 설정을 했다.

   그러므로 주문 테이블에서 고객번호 필드만을 남겨두고 고객 정보 모두를 제거 한다.

고객 정보는 다음과 같이 테이블을 구성한다.

이렇게 테이블을 분할함으로써 왕관의 데이터가 한번만 존재하게 되었다. 이것이 바로 중복 데이터의 제거이다. 이러므로 공간 절약과 중복 값들 사이의 모순을 방지할 수 있게 된 것이다.

ㄴ.
주문 테이블의 주문상품 필드가 갖는 복합적 데이터를 원소성이 유지되도록 분할을 해야 한다. 이 주문상품 필드엔 다음의 여러 정보가 들어 있음을 알수 있다.
1. 상품번호(모델번호)
2. 상품명(모델명)
3. 가격

이 항목들을 따로 존재하는 독립적인 정보로 만드는 것이 원소성을 유지하는 것이된다.
다음의 결과가 원소성을 위한 필드 분할 테이블이다.

결과 테이블을 보면 상품의 분할이 이루어져 원소성을 유지함을 알 수 있다. 그런데 기본 키 필드인 ‘주문번호’가 기본 키의 유일성이 깨져있다. 결과를 위한 각각의 행들은 고유한 데이터로써 분할은 했으나 기본 키 설정이 부적절하게 되어 데이터 식별이 어렵다. 이를 해결하기 위해서는 기본 키 추가 필드를 하나 더 만들도록 하자(다른 방법이 없는 것은 아니지만 지금으로써는 최선이라고 본다). 바로 복합 키를 설정하겠다는 뜻이다. 고객이 상품 주문 순서에 따른 필드를 하나 추가 하여 주문번호 필드와의 복합으로 기본 키 설정을 해 보면 다음의 테이블과 같이된다.

위 결과와 같이 각 주문에 대한 순서를 정하여 두개의 필드를 기본 키 설정으로 하게 되면 각 행들을 고유하게 식별할 수 있게된다.
이렇게 해서 1차 정규화를 거쳤다. 중복 되는 데이터도 없고 각각의 필드들은 원소성을 유지하고 있다.(가변적인 데이터는 중복 데이터가 아니다. 표현 그대로 선택시 마다 발생한 데이터일 뿐이다. 이것이 우연히 다른 데이터와 같을 수가 있는 것일 뿐이다.)

2차 정규형식(2NF)
: 2차 정규 형식은 다음의 규칙을 따르게 된다.
ㄱ. 1NF의 규칙을 만족해야 한다.
ㄴ. 모든 열은 반드시 키 전체에 의존 해야 한다. 키의 일부에만 의존한다면 정규화에 어긋나는 테이블이 되는 것이다.

그러면 위 1NF의 결과로 만들어진 ‘주문’ 테이블을 보자. 여기에서 복합키로 설정된 기본 키 전체에 의존하는 것이 아니라 하나의 필드에 의존하는 필드들이 있다. 바로 주문날짜와 고객번호이다. 이 필드들은 주문순서와는 상관이 없이 반복되며 오직 주문번호에만 의존하고 있다. 이것을 해결하려면 새로운 테이블로 분할을 해야한다.

주문 테이블을 2차 정규형식에 의해 분할을 한 결과이다.

3차 정규형식(3NF)
: 이제 정규화의 마무리 단계이다(이후의 형식들은 학계 이외에서는 별로 쓰이지 않는다). 3차 정규형식은 다음의 규칙을 따른다.

ㄱ. 2NF의 규칙 만족해야 한다.
ㄴ. 키가 아닌 필드에 의존하는 필드가 없어야 한다.
ㄷ. 필드 연산에 의한 결과값을 가지는 필드가 없어야 한다.

[주문상세] 테이블을 보면 기본 키가 아닌 상품번호에 의존하는 필드들이 있다. 바로 상품명과 단가 필드들인데 이 필드들은 다른 테이블로 분할을 해야 한다.

이렇게 해서 3차 정규형식중 ‘키가 아닌 필드에 의존하는 필드가 없어야 한다’를 만족 시켰고 나머지 하나인 필드연산에 의한 결과값을 가지는 필드는 아예 만들질 않았다. 그러면 결과값 필드 라는 것이 무엇일까? 다음의 예제 테이블에서 이것을 알아보자
(예제테이블)

위 예제 테이블에서 보면 바로 합계 필드가 필드들의 연산에 의한 결과값 저장 필드가 되는 것이다. 하지만 이경우는 데이터 검색 속도 향상을 위해 나누는 경우가 종종 있다.
이유는 WHERE 합계 > 5000 이 WHERE 수량 * 판매가격 > 5000 보다 빠르게 수행이 된다. 이런 경우를 ‘역정규화’라 한다. 실제로 실무에서는 이렇게 혼합된 방식을 사용하기도 한다. 이런 경우들이 존재하긴 해도, 원칙은 당연히 정규화를 거치는 것이다. 관계형 데이터베이스라는 것 자체가 정규화를 염두에 두고 설계된 것이기 때문이다. 정규화에 어딘가가 맞지 않는다는 것은 데이터 무결성에도 오류가 있을 수 있고 관계형 데이터 베이스 환경에서의 성능 면에서도 문제가 될 수 있는 것이다.

지금까지의 정규화로써 생성된 테이블들은 다음과 같다.
[주문]
[고객]
[주문상세]
[상품]

이 테이블들을 관계 형성을 하는 다이어그램으로 나타내 보자

실습을 위해서 EM에서 ‘shop’ 이라는 데이터베이스를 생성을 한다(3M).

다이어그램을 실행후 다음의 테이블들을 생성하고 관계설정을 한다.

[주문상세]
주문번호   int
주문순서   tinyint
상품번호   varchar(10)

[상품]
상품번호   varchar(10) PK
상품명      varchar(20)
단가         money
설명         varchar(50)