데이터베이스 뽀개버리기!!

데이터 모델링과 국어사전

이재학 — Tue, 30 Oct 2007 05:08:41 GMT

데이터 모델링을 잘 하려면 국어사전을 많이 보는 것이 도움이 된다. 왜냐하면 우리가 그냥 무심코 지나쳤던 단어들에 대한 깊은 생각을 할 수 있기 때문이다. 예를 들어, 게임계에서 아주 잘 사용하는 ‘유저(user, 이용자)’라는 단어를 생각해보면 우리가 얼마나 그냥 지나쳤었는지 알 수 있다. 네이버 국어 사전에는 ‘이용자’의 뜻이 다음과 같이 나와 있다.

[명사]어떤물건이나 시설, 서비스 따위를 이용하는 사람.

Ø 휴대폰이용자

Ø 백화점이용자

Ø 컴퓨터통신의 이용자가 급격히 늘고 있다.

단어의 뜻을 잘 살펴보면 ‘이용한다’는 뜻과 ‘사람’이라는 뜻이 합쳐진 것을 볼 수 있다. ‘이용한다’는 동사이고, ‘사람’은 명사이다. 이것만 보아도 ‘이용자’는 개체집합이 아닌 관계라는 것을 알 수 있다. 만약 사람이 게임을 이용한다면 아마도 다음과 같이 ‘사람’은 ‘고객’이 될 것이고, ‘이용자’는 관계가 될 것이다. 게임 말고도 게시판 등의 제공되는 서비스를 이용하는 사람도 있다면 다음과 같은 모델이 될 것이다.

그럼 ‘이용자’는 관계라는 것을 알았다. 관계는 실제로 다음과 같이 나타낼 수 있다. (개체만 집합이 아니다. 관계도 집합이다.)

일반적으로 설계된 것을 보면 테이블의 이름이 ‘User’ 또는 ‘Member’라는 단어가 들어가 있는 것을 볼 수 있다. Member는 ‘어떤 단체를 구성하는 일원’이다. 즉, ‘Member’는 그 의미의 범위가 매우 좁다. 그러므로 ‘Member’라는 단어는 고객이 어떤 동호회에 가입되었을 때의 관계가 Member가 되어야 하는 것이 옳다고 볼 수 있다.

여기서 말하는 ‘회원’이 많은 곳에서 볼 수 있는 ‘가입자’라고 보아도 무관하다. 하지만 동호회의 ‘가입자’보다는 ‘회원’이 의미 전달이 더욱 좋다. (모델은 의사소통의 수단이다.) 우리가 알고 있던 ‘UserID’와 ‘MemberID’가 어떻게 해서 탄생되어야 한다는 것을 아는 것은 매우 중요하다. 왜냐하면 ‘고객’은 모델러에게는 가장 중요한 개체집합이고, 기업 경영의 의사결정에 있어서 가장 중심이 되는 중요한 요소이기 때문이다.

tag : DB모델링, DB설계, 국어사전, 단어

Bottom-Up 방식으로 모델링 해보기

이재학 — Fri, 26 Oct 2007 07:21:49 GMT

예제를 찾기 힘들기 때문에 데브피아에 올라온 질문을 토대로 설명을 해보도록 하겠다. (질문 원본)

질문의 내용은 업체별 제품별 제품규격별 가격을 변동을 관리해야하는데 어떻게 해야 좋은 모델을 만들 수 있을까이다. 질문에서 보면 핵심이 되는 엔터티 집합은 ‘업체’와 ‘제품’이다. 제품규격은 코드성 엔터티 집합으로 만약 업계표준 같은 것이 정해져 있다면 코드 엔터티 집합이라는 것은 명확해진다. 여기서 ‘업체’는 질문에서 보면 ‘제조업체’로 볼 수 있다. ‘제조업체’는 현재로써 특별히 관리해야 할 건덕지가 보이지 않으므로 이것도 코드 엔터티 집합으로 볼 수 있다. 그러므로 다음의 엔터티 집합이 본래의 모습이다.

‘제조업체’, ‘제품규격’을 코드화 해서 대충 다음과 같은 모델이 만들어 질 것이다.

문제는 제품규격에 대한 가격변동에 따른 이력을 관리해야 하는 것에 있다. 즉, ‘제품’과 ‘제품규격’ 사이의 관계가 ‘제품가격변동이력’으로 변경되며, 1:다의 관계에서 다:다의 관계가 된다.

이제 다:다 관계를 풀어 이력 엔터티 집합으로 만들면 된다. 여기서 한 가지 결정을 해야 하는 것은 이력 관리시 가격이 변동되었다는 이벤트까지 관리를 할 것인지를 결정해야 한다. 실제 데이터는 다음과 같이 들어갈 것이다.

--이벤트까지관리

시작일 종료일

-------- --------

20010107 20010502

20010502 20010707

20010707 20011107

20011107 20011207

--이력만관리

시작일 종료일

-------- --------

20010107 20010501

20010502 20010706

20010707 20011106

20011107 20011206

어찌되었건 최종적으로 다음과 같은 모델이 될 것이다.

그렇다면 해당 제품의 현재 가격을 어떻게 쿼리해서 가져올까? 아마도 다음과 같이 될 것이다.

select

a.제조업체명

, b.제품명

, d.제품규격

, c.가격

from 제조업체 a inner join 제품 b

on a.제조업체번호 = b.제조업체번호

inner join 가격변동이력 c

on a.제품번호 = c.제품번호

inner join 제품규격 d

on c.제품규격번호 = d.제품규격번호

where '20071026' between c.시작일 and c.종료일

요구사항에 따라서 제품규격을 알기 위해서는 항상 ‘가격변동이력’과 조인해야 함이 부담스럽다면 다음과 같은 비정규화도 고려해 볼 수 있다.

tag : DB모델링, DB설계, Bottom-Up, 상향식

Service Broker 무조건 따라해 보기

이재학 — Mon, 08 Oct 2007 09:31:07 GMT

* 참고: 글자수 제한으로 소스1,2,3는 파일로 올린다.

도움말의 Service Broker의 역할을 보면 다음과 같은 그림이 나온다.

요걸 구현해 보것다. 편의상 왼쪽의 편지를 보내는 사람을 ‘Sender’라고 하고, 오른쪽의 편지를 받는 사람을 ‘Receiver’라고 하것다.

무조건 아래의 코드를 입력해보자. (복사보다는 직접 타자를 쳐보는 것이 더 좋겠지?)

편지를 보내고, 받는 사람, 우체국, 우편차, 어떻게 편지를 전달하는지에 대한 것을 구현한다.

소스1: Source1.doc

이제 메시지를 보내보자. 2~3회 실행해보자.

소스2: Source2.doc

메시지는 Queue에 쌓이게 되고, 메시지를 받아서 Msg테이블에 잘 저장해보자.

소스3: Source3.doc

오~ 메시지가 잘 도착했다. ㅎㅎ

그러면 이제 Person테이블이 없는 사람에게 메시지를 보내보자.

메시지 보내기 부분에서 다음과 같이 Receiver를 Person테이블에 ‘없는놈’에게 메시지를 보내보고(소스2 수정하여 실행),

위에서 실행했던 메시지를 받아보자.(소스3 실행)

--메세지보내기(소스2를 고친다.)

DECLARE

@handle uniqueidentifier

, @Sender varchar(20)

, @Receiver varchar(20)

, @Msg nvarchar(max);

SET @Sender = 'yasi';

SET @Receiver = '없는놈'; --이부분을 고친다.)

SET @Msg = N'하이~ 방가방가(^^)/';

<소스4>

이제 ‘수취인불명’ 메시지를 받아보자. 아래 소스는 <소스3>과 비스무리하다.

--수취인불명메세지

DECLARE

@handle uniqueidentifier

, @message_body xml

, @Sender varchar(20)

, @Receiver varchar(20)

, @Msg nvarchar(max)

, @PersonName varchar(20);

BEGIN TRY

BEGIN TRAN;

WHILE(1=1)

BEGIN

--보낸편지를받는다.

RECEIVE TOP(1)

@handle = conversation_handle

, @message_body = CONVERT(nvarchar(max), message_body)

FROM ReceivedMsgQueue;

IF @@ROWCOUNT = 1

BEGIN

SELECT

@Sender = A.Sender

, @Receiver = A.Receiver

, @Msg = A.Msg

FROM (

SELECT

x.item.value('Sender[1]', 'varchar(20)') AS Sender

, x.item.value('Receiver[1]', 'varchar(20)') AS Receiver

, x.item.value('Message[1]', 'nvarchar(max)') AS Msg

FROM @message_body.nodes('/Letter') AS x(item)) A LEFT OUTER JOIN Person B

ON A.Receiver = B.PersonName;

IF @Sender IS NOT NULL

BEGIN

INSERT Msg(Sender, Receiver, Msg, ReceiveDT)

VALUES(@Sender, @Receiver, @Msg, GETDATE());

END ELSE

BEGIN

END CONVERSATION @handle;

END

END ELSE

BEGIN

END CONVERSATION @handle;

BREAK;

END

COMMIT;

END TRY

BEGIN CATCH

IF (XACT_STATE()) = -1 ROLLBACK;

ELSE IF (XACT_STATE()) = 1 COMMIT;

END CATCH;

<소스5>

자~ 이제 또 한번 한 숨 돌리고(5초 가량 기둘린다.)메시지가 잘 도착했는데 살펴보자.

--메세지가잘도착했나?

SELECT * FROM Msg

역시 잘 된다. 보낸놈과 받는놈이 바뀐 것을 볼 수 있다.

예제만 보면 ‘아~ 조낸 복잡하네~!! 무어여 이게~~ 간단한 것을 이따시로 복잡하게 해논기여~~’라고 말하는게 눈에 훤하다.

하지만 비동기 통신, 안정적인 메시지 처리, 보내진 순서대로 처리하는 등의 내부적인 것을 구현하려면

이 정도 복잡함은 진짜 새 발의 피다.

중요한 것은 Service Broker가 SOA의 구현이라는 것이다.

또한 프로시저화 한다면 이러한 복잡함은 숨겨진다.

프로시저화 한다면 인터페이스를 단순해져 불러다가 쓰는 입장에서는 복잡함을 느끼지는 못할 것이다.

다시 한번 말하지만 이것은 절대 복잡한 것이 아니다. 복잡한 것은 이미 SQL Server 2005에 구현되어 있다.

아주 무궁무진한 가능성을 지닌 기능임에는 틀림없다. 허허..

<강좌후기>

아..띠부럴..

취침시간을 1시간 43분이나 넘겨버렸다. 24시 정각에 잠들었어야 하는데..

내일 또 하루죙일 골골 대겠다..떱..

허허 간만에 문서 만들었당~

tag : SQL2005, service, broker, ServiceBroker

Reporting Service와 Microsoft SQL Server Management Studio

이재학 — Mon, 08 Oct 2007 09:20:42 GMT

오늘 테이블정의서를 뽑는 툴을 찾다가 짜증이 나서 담배를 피고 있는데..

리포팅 서비스를 이용해보면 어떨까 해서리..

대충 잘 되는지 해보았다. 잘된다..

잘 이용하면 .rdl 파일만 들고 다니면서 연결문자열 정도만 수정하면 DBA에게는 매우 좋은 툴이 되리라

생각해다. .. 안 좋나?? 허허

1. [시작]->[프로그램]->[SQL Server 2005]->[ SQL Server Business Intelligence Development Studio] 선택

2. [파일]->[새로만들기]->[프로젝트]->[보고서 서버 프로젝트 마법사]

마법사가 시작되는데 걍 취소한다.

3. 솔루션 탐색기에서 [보고서] 디렉토리에서 마우스 오른키 후 [새 보고서 추가] 선택. 그럼 마법사가 뜬다.

4. [다음] 클릭

5. 데이터 원본을 편집한다.

6. 적당히 편집 후 확인 버튼 클릭

7. 연결문자열 확인 후 [다음] 클릭

8. 다음의 sql script를 쿼리 문자열에 넣는다. 그리고 [다음]클릭

--테이블

select

a.name schema_name

, isnull(

case

when c.column_id = 1 then b.name

when c.column_id = 2 then d.table_name

else '' end, '') table_name

, c.name column_name

, isnull(d.column_name, '') column_desc1

, isnull('(PK, ' + convert(varchar, e.type_desc) + ') ', '') + isnull('(FK,' + object_name(f.referenced_object_id), '') column_desc2

, g.name date_type

, case when g.name IN ('nchar', 'nvarchar') AND c.max_length <> -1 then c.max_length/2 else c.max_length end column_length

, c.precision

, c.scale

, case when c.is_nullable = 1 then 'NULL' else 'NOT NULL' end is_nullable

, case

when c.is_identity = 1 then

(select '(' + convert(varchar, seed_value) + ',' + convert(varchar, increment_value) + ')'

from sys.identity_columns where b.object_id = object_id)

else ''

end is_identity

--, isnull(h.name, '') default_constraints

, isnull(h.definition, '') default_value

from sys.schemas a inner join sys.objects b

on a.schema_id = b.schema_id inner join sys.all_columns c

on b.object_id = c.object_id

and b.type = 'U' left join (

select

a.major_id object_id

, b.minor_id column_id

, a.value table_name

, b.value column_name

from sys.extended_properties a inner join sys.extended_properties b

on a.major_id = b.major_id

and b.minor_id > 0

and a.minor_id = 0) d

on c.object_id = d.object_id

and c.column_id = d.column_id left join (

select

a.object_id

, a.column_id

, b.type_desc

from sys.index_columns a inner join sys.indexes b

on a.object_id = b.object_id

and a.index_id = b.index_id

and b.is_primary_key = 1

) e

on b.object_id = e.object_id

and c.column_id = e.column_id left join sys.foreign_key_columns f

on b.object_id = f.parent_object_id

and c.column_id = f.parent_column_id inner join sys.types g

on c.system_type_id = g.system_type_id

and c.user_type_id = g.user_type_id left join sys.default_constraints h

on c.default_object_id = h.object_id

where b.name <> 'sysdiagrams'

order by b.name , b.object_id, c.column_id

9. 나머지는 귀찮으니까 걍 [마침]누르자.

10. ‘테이블정의서’라고 이름을 넣고 마치자.

11. 적당히 리포트 레이아웃을 꾸민다. 머..간격조절 정도? 나머지는 홈페이지의 (요기) 참고

12. 이런식으로 다음의 SQL을 이용하여 ‘인덱스보고서’를 만든다.

--인덱스

select

c.name table_name

, b.name index_name

, b.type_desc index_type

, b.is_primary_key

, b.is_unique

, b.fill_factor

, b.allow_row_locks

, b.allow_page_locks

from sys.index_columns a inner join sys.indexes b

on a.object_id = b.object_id

and a.index_id = b.index_id inner join sys.objects c

on a.object_id = c.object_id

and c.type = 'U'

where c.name <> 'sysdiagrams'

13. 저장하고 프로젝트를 닫아버리자. 그리고 프로젝트 파일들이 모인 디렉토리에서

- 테이블정의서.rdl

- 인덱스정의서.rdl

을 바탕화면에 복사한다.

14. Microsoft SQL Server Management Studio로 가서 다음과 같이 사용자 지정보고서를 선택한다.

15. 아까 만들었던 ‘테이블정의서.rdl'을 선택한다.

16. 다음과 같은 경고창이 뜬다. 무시하고 [실행]한다.

17. 오오~ 잘나온다. ㅋㅋ. 인덱스 정의서도 이런식으로 만들어보면 좋겠당.

tag : SQL2005, ReportingService, RDL, 테이블정의서, 레이아웃

SQL Server 2005 데이터베이스 메일 보내기

이재학 — Mon, 08 Oct 2007 08:55:47 GMT

1. 설정하기

use master

sp_configure 'show advanced options',1

reconfigure with override

sp_configure 'Database Mail XPs',1

--go

--sp_configure 'SQL Mail XPs',0

reconfigure

2. 노출영역 구성

3. 예제 스크립트

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'MailServiceAccount'

, @description = 'DataBaser.Net Service Mail'

, @email_address = 'admin@databaser.net'

, @display_name = 'Databaser.Net'

, @username='admin@databaser.net'

, @password='패스워드'

, @mailserver_name = 'mail.databaser.net'

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MailServiceProfile'

, @description = 'Profile used for database mail';

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MailServiceProfile'

, @account_name = 'MailServiceAccount'

, @sequence_number = 1

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MailServiceProfile'

, @principal_name = 'public'

, @is_default = 1 ;

--참고: 지우기

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp

@profile_name = 'MailServiceProfile'

, @account_name = 'MailServiceAccount'

EXECUTE msdb.dbo.sysmail_delete_profile_sp

@profile_name = 'MailServiceProfile' ;

EXECUTE msdb.dbo.sysmail_delete_account_sp

@account_name = 'MailServiceAccount' ;

--exec msdb..sysmail_help_principalprofile_sp

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp

@principal_name = 'guest',

@profile_name = 'MailServiceProfile' ;

--메일보내기

declare @body1 varchar(100)

set @body1 = 'Server :'+@@servername+ ' My First Database Email '

EXEC msdb.dbo.sp_send_dbmail

@recipients='admin@databaser.net'

, @subject = 'My Mail Test'

, @body = @body1

, @body_format = 'HTML' ;

메일을 확인해보면 잘 갔을 것이다.

tag : SQL2005, 데이터베이스메일

CRM 정리자료

이재학 — Fri, 05 Oct 2007 05:02:43 GMT

1-1 고객의 재밝견: CRM_1_1.ppt
1-2 로열티 시대: CRM_1_2.ppt
1-3 새로운패러타임(CRM): CRM_1_3.ppt

tag : CRM

지각동사, 사역동사

이재학 — Wed, 19 Sep 2007 14:30:26 GMT

지각동사
see
fell
hear
smell

사역동사
make
have
let

tag : 지각동사, 사역동사

이곳은? dbstroy.com

이재학 — Tue, 18 Sep 2007 17:03:57 GMT

이곳의 URL은 2개다.
- dbstroy.com
- dbstroy.egools.com

stroy는 지금은 쓰지 않는 영어 단어로 '파괴'의 뜻을 가진 옛날말이다.
다행이었다.
존재하는 단어여서..

원래는 dbstory.com (DB이야기)을 구입하려고 했는데..
오타로 dbstroy.com 이 되어 버렸다.
그덕에 구입된 도메인다.

어찌되었건..
이곳의 내용은 쓰레기가 아닌 것으로만 채울 것이다.

'to'의 종류

이재학 — Tue, 18 Sep 2007 13:01:25 GMT

to부정사: '~는 것'으로 해석
I want to call you.
나는 니가 전화하는 것을 원한다. (강조효과, TO 다음에 어떤 동사가 올지 모르므로 '부정사'라는 이름이 붙음)

I want calling you.
나는 니가 전화하는 것을 원한다. (동명사, 짧으므로 더 많이 쓰임, 전치사 뒤에 전치사를 쓸 수 없으므로 TO부정사를 대신함)

in order to: '~하기 위하여'로 해석하며, 'in order'를 생략가능, 생략하지 않으면 정중, 강조의 의미
I went to tell you.
나는 너에게 말하기 위하여 갔다.

전치사: '~에', '~로' 해석
I went to church.
나는 교회에 갔다.

가주어(To부정사, 동명사로 시작하는 주어가 조낸 길때)
To save some money for the future is important.
= Saving some money for the future is important.
= It is important to save some money for the future. (가주어 ~ to부정사: 상식적인 상황)
= It is important saving some money for the future. (가주어 ~ 동명사: 특이하거나 특별한 상황)

정리: (1)명사 + (2)동사 + (3)명사 / (4)전치사 + (5)명사
- TO부정사를 쓸 수 있는 곳: (1), (3)
- (5)에는 TO부정사를 쓸 수 없다. 왜냐하면 '전치사 + to'로 전치사 뒤에 전치사를 쓸 수 없다. 그러므로 동명사 사용
- TO부정사와 동명사, It 가주어는 Package 문법(Famlly Group)이다.

tag : To부정사, 전치사To, 동명사, 가주어

2진수의 변환화 재귀쿼리

이재학 — Fri, 14 Sep 2007 05:07:31 GMT

재귀쿼리를 이용해서 2진수의 변환을 해보자. 87이란 정수가 있다. 이를 2진수로 변환하기 위해서는 다음과 같은 계산을 해야 한다.

87/2 = 43, 나머지 = 1

43/2 = 21, 나머지 = 1

21/2 = 10, 나머지 = 1

10/2 = 5, 나머지 = 0

5/2 = 2, 나머지 = 1

2/2 = 1 나머지 = 0

결과는 1010111 = 2⁶ + 2⁴ + 2² + 2¹ + 2⁰ = 64 + 16 + 4 + 2 + 1 = 87₁₀이 되어야 한다. 요걸 한방에 어떻게 해결할 수 없을까? 제목에서 나타나 있듯이 재귀쿼리를 이용하면 된다. 다음의 소스를 실행하면 원하는 결과를 얻을 것이다.

declare

@int int

, @bin varchar(100);

set @int = 87;

set @bin = '';

with dumy(no, mod, bin)

(

select 1, @int, convert(varchar, @int%2)

union all

select no + 1, @int/power(2, no), convert(varchar, (@int/power(2, no))%2)

from dumy

where no + 1 <=30

)

select

@bin = @bin + bin

from dumy

where mod + bin > 0

order by no desc

select @bin bin

/* result

bin

----------------------------------------------------------------------------------------------------

1010111

한 가지 고려해야 할 점은 2진수를 표현할 수 있는 데이터형이 없다는 것이다. 그래서 2진수를 표현하려면 문자열로 표현을 해야 한다. int형을 표현하기 위해서는 31자리의 문자가 필요하다. 트래이드 오프이긴 하지만.. 위의 스크립트를 함수로 만들거나 10진수와 2진수의 매칭 테이블을 만들어서 관리하는 방법도 있겠다. 참고로 매칭 테이블은 다음과 같이 만들면 된다.

select colbin

into #bin

from (select '0' colbin union all select '1') t

select (b1.colbin + b2.colbin + b3.colbin) as bin_number

from #bin b1 cross join #bin b2 cross join #bin b3

order by 1;

/* result

bin_number

----------

000

001

010

011

100

101

110

111

tag : SQL2005, SQL, T-SQL, 2진수