데이터 모델링을 잘 하려면 국어사전을 많이 보는 것이 도움이 된다. 왜냐하면 우리가 그냥 무심코 지나쳤던 단어들에 대한 깊은 생각을 할 수 있기 때문이다. 예를 들어, 게임계에서 아주 잘 사용하는 ‘유저(user, 이용자)’라는 단어를 생각해보면 우리가 얼마나 그냥 지나쳤었는지 알 수 있다. 네이버 국어 사전에는 ‘이용자’의 뜻이 다음과 같이 나와 있다.
[명사]어떤물건이나 시설, 서비스 따위를 이용하는 사람.
단어의 뜻을 잘 살펴보면 ‘이용한다’는 뜻과 ‘사람’이라는 뜻이 합쳐진 것을 볼 수 있다. ‘이용한다’는 동사이고, ‘사람’은 명사이다. 이것만 보아도 ‘이용자’는 개체집합이 아닌 관계라는 것을 알 수 있다. 만약 사람이 게임을 이용한다면 아마도 다음과 같이 ‘사람’은 ‘고객’이 될 것이고, ‘이용자’는 관계가 될 것이다. 게임 말고도 게시판 등의 제공되는 서비스를 이용하는 사람도 있다면 다음과 같은 모델이 될 것이다.
그럼 ‘이용자’는 관계라는 것을 알았다. 관계는 실제로 다음과 같이 나타낼 수 있다. (개체만 집합이 아니다. 관계도 집합이다.)
일반적으로 설계된 것을 보면 테이블의 이름이 ‘User’ 또는 ‘Member’라는 단어가 들어가 있는 것을 볼 수 있다. Member는 ‘어떤 단체를 구성하는 일원’이다. 즉, ‘Member’는 그 의미의 범위가 매우 좁다. 그러므로 ‘Member’라는 단어는 고객이 어떤 동호회에 가입되었을 때의 관계가 Member가 되어야 하는 것이 옳다고 볼 수 있다.
여기서 말하는 ‘회원’이 많은 곳에서 볼 수 있는 ‘가입자’라고 보아도 무관하다. 하지만 동호회의 ‘가입자’보다는 ‘회원’이 의미 전달이 더욱 좋다. (모델은 의사소통의 수단이다.) 우리가 알고 있던 ‘UserID’와 ‘MemberID’가 어떻게 해서 탄생되어야 한다는 것을 아는 것은 매우 중요하다. 왜냐하면 ‘고객’은 모델러에게는 가장 중요한 개체집합이고, 기업 경영의 의사결정에 있어서 가장 중심이 되는 중요한 요소이기 때문이다.
예제를 찾기 힘들기 때문에 데브피아에 올라온 질문을 토대로 설명을 해보도록 하겠다. (질문 원본)
질문의 내용은 업체별 제품별 제품규격별 가격을 변동을 관리해야하는데 어떻게 해야 좋은 모델을 만들 수 있을까이다. 질문에서 보면 핵심이 되는 엔터티 집합은 ‘업체’와 ‘제품’이다. 제품규격은 코드성 엔터티 집합으로 만약 업계표준 같은 것이 정해져 있다면 코드 엔터티 집합이라는 것은 명확해진다. 여기서 ‘업체’는 질문에서 보면 ‘제조업체’로 볼 수 있다. ‘제조업체’는 현재로써 특별히 관리해야 할 건덕지가 보이지 않으므로 이것도 코드 엔터티 집합으로 볼 수 있다. 그러므로 다음의 엔터티 집합이 본래의 모습이다.
‘제조업체’, ‘제품규격’을 코드화 해서 대충 다음과 같은 모델이 만들어 질 것이다.
문제는 제품규격에 대한 가격변동에 따른 이력을 관리해야 하는 것에 있다. 즉, ‘제품’과 ‘제품규격’ 사이의 관계가 ‘제품가격변동이력’으로 변경되며, 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.종료일
요구사항에 따라서 제품규격을 알기 위해서는 항상 ‘가격변동이력’과 조인해야 함이 부담스럽다면 다음과 같은 비정규화도 고려해 볼 수 있다.
* 참고: 글자수 제한으로 소스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 END COMMIT; END TRY BEGIN CATCH IF (XACT_STATE()) = -1 ROLLBACK; ELSE IF (XACT_STATE()) = 1 COMMIT; GO |
<소스5>
자~ 이제 또 한번 한 숨 돌리고(5초 가량 기둘린다.)메시지가 잘 도착했는데 살펴보자.
--메세지가잘도착했나? SELECT * FROM Msg
|
역시 잘 된다. 보낸놈과 받는놈이 바뀐 것을 볼 수 있다.
예제만 보면 ‘아~ 조낸 복잡하네~!! 무어여 이게~~ 간단한 것을 이따시로 복잡하게 해논기여~~’라고 말하는게 눈에 훤하다.
하지만 비동기 통신, 안정적인 메시지 처리, 보내진 순서대로 처리하는 등의 내부적인 것을 구현하려면
이 정도 복잡함은 진짜 새 발의 피다.
중요한 것은 Service Broker가 SOA의 구현이라는 것이다.
또한 프로시저화 한다면 이러한 복잡함은 숨겨진다.
프로시저화 한다면 인터페이스를 단순해져 불러다가 쓰는 입장에서는 복잡함을 느끼지는 못할 것이다.
다시 한번 말하지만 이것은 절대 복잡한 것이 아니다. 복잡한 것은 이미 SQL Server 2005에 구현되어 있다.
아주 무궁무진한 가능성을 지닌 기능임에는 틀림없다. 허허..
<강좌후기>
아..띠부럴..
취침시간을 1시간 43분이나 넘겨버렸다.
내일 또 하루죙일 골골 대겠다..떱..
허허 간만에 문서 만들었당~
오늘 테이블정의서를 뽑는 툴을 찾다가 짜증이 나서 담배를 피고 있는데..
리포팅 서비스를 이용해보면 어떨까 해서리..
대충 잘 되는지 해보았다. 잘된다..
잘 이용하면 .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. 오오~ 잘나온다. ㅋㅋ. 인덱스 정의서도 이런식으로 만들어보면 좋겠당.
1. 설정하기
use master go sp_configure 'show advanced options',1 go reconfigure with override go sp_configure 'Database Mail XPs',1 --go --sp_configure 'SQL Mail XPs',0 go reconfigure go |
2. 노출영역 구성
3. 예제 스크립트
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'MailServiceAccount' , @description = 'DataBaser.Net Service Mail' , @email_address = ' , @display_name = 'Databaser.Net' , @username=' , @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=' , @subject = 'My Mail Test' , @body = @body1 , @body_format = 'HTML' ; |
메일을 확인해보면 잘 갔을 것이다.
| < 이전페이지 | 다음페이지 > |
by 이재학
