[MSSQL] 2.Data Modeling

2.Data Modeling:  파일시스템(중복허용)처럼 사용하는법을 지양하는 분석론의 과정이 데이터베이스 모델링 과정이다.

 

SDLC(Software Development Life Cycle)과 데이터베이스 개발과정:



SDLC방법론을 데이터베이스 개발과정에 적용하면 다음의 그림과 같이 적용할 수 있습니다.



물리적 설계는 개발에 사용되는 DBMS에서 제공하는 구조나 기능들과 논리적 설계를 같이 고려하여야 하며, 이를 통해서 좀 더 효율적인 데이터베이스가 탄생되기 때문에 물리적 설계 역시 데이터베이스 개발과정에서 중요한 부분을 차지합니다.

논리적 데이터 모델링: 기업의 데이터 구조를 명확하게 표현하기 위한 방법으로 기업 내에 존재하는 데이터에 대해 프로세스와는 독립적으로 데이터요구를 인식하여 문서화 하는 효율적인 기법을말한다.



기업 내에 산발적으로 또는 중복되어있는 데이터를 분석하여 무결성,공유도,융통성이 높은 데이터베이스 설게의 Frame형성하는 작업.

 

최적의 데이터 모델링을 위해 지켜야할 원칙:

구조의 확증성(일관성), 단순성,비중복성,공유성(다수에의해사용),무결성(모순x),확장성

 

주요성공요소:

사용자와공동작업,데이터 중심 접근방법(프로세스와 독립접근),개념화,정규화기법채택, 도형사용, 자료사전사용




체계도: 데이터모형토대구축: 기업내 존재하는 데이터를 아주 간략하게 나타내는 단계.

 

※ 데이터 모형 토대 구축: 실체와 관계를 정의하고 이를 실체관계도로 표현함으로써 논리적 데이터 모형의 토대를 구축한다.

실체정의: 기업내의 업무 중에서 필요한 정보를 얻기위한 기본 데이터를 정의하고

관계정의: 업무규칙으로부터 얻어지는 데이터간의 관계를 정의한다.

 

 

실체: 실체는 의미있는 유용한정보를 제공하기위해 기록,관리하는 데이터로 사람,사물,장소,개념,또는 사건을 명칭한다. 기업에서 정보로 관리할 대상이 되는 개체를 정의하는 것.

 

1)실체(Entities 정의 단계:실체추출-> 실체검증 -> 실체정의표작성

Entity: 정보로 관리할 대상이 되는 개체

Entities: 동일한 성격을 지닌 실체들의 집합

 

핵심실체: 물리적실체 -그 Entity Type이 현실세계에 물리적존재하는경우(고객,제품,종업원)

특성실체: 논리적인개념을 표현한 실체 - 현실세계에서는 존재하지않지만 사고의 간편화를 위한 정의(업무,관리)

관련실체: 실체간의 거래, 사건으로부터 파생된 실체,사건 및 행위개념(매입,매출)

 

2)실체 추출

우선 시나리오에 나타난 명사들을 정리하여 의미나 특성상 비슷한 것들을 묶어서 다른 것들에 대해 독립적이거나 종속되지 않는 성격의 명사로 정리합니다.

▣ 실체명명규칙

• 단수명사
• 현업용어사용
• 약어 사용 배재
• 필요 시 수식어 사용

▣ 추출된 실체들

• 회원
• 테이프
• 임대
• 점포관리자
• 고객
• 임대전표
• 임대거래
• 재고품목
• 임대연체

고려사항

유용성: 업무적으로 의미있는 정보인가?

중복성: 혹시 같은의미의 실체가 있지 않은가?

속성존재: 실체를 구성하는 다른의미의 단어가 있는지에대한 검증

하나 이상의 다른실체와의 관계존재: 추출된실체들과 관계 검증

 

3)실체 정의표



1)관계 정의

관계는 관리하고자 하는 대상인 실체간에 존재하는 연관성을 나타내는 것입니다.

관계추출 -> 관계정의 -> 관계검증 순서입니다.

 

▣ 기수정의

기수정의는 관계에 참여하는 각 실체를 기준으로 나타나는 실체연결비율을 말합니다. 즉 실체의 관계를 숫자로 표현하는 것을 말합니다.

▶ 1:1 - 하나

두 개의 실체 간의 기수가 1인 경우만 존재한다는 것 ex) pc <->모니터

 

▶ 1:다 - 하나 또는 여러

한쪽의 실체는 하나가, 또 다른 실체는 하나 이상인 경우 ex) 고객 -> 여러 개의 임대정보

 

▶ M:N관계

양쪽 실체 모두 하나이상씩 발생되며 이러한 관계는 많이 발생 ex) 교수 <-> 과목

 

▣ 선택성 정의

관련되는 실체 존재 조건으로 관계연결여부가 미치는 영향을 표현하는 것. 실체간의 관계가 성립될수 있는 조건이

반드시 있는지, 선택적으로 있는지 결정하는것.

▶필수: 반드시 ex) 사원은 반드시 부서에 소속되야하는 선택적 관계

▶선택적: 가끔 경우에따라 ex) 사원-병역정보  면제되는경우도 있기때문에.

 

2)관계 추출:

관계추출은

• 비즈니스에서 사용되는 동사
• 장표와 장부에서 함께 나타나는 실체들간의 의미 있는 정보

에서 추출합니다.

 

3)관계 정의:

 

• 관계의 방향성을 고려한 관계명칭부여
• 관계가 예상되는 두 실체유형을 각각 주어와 술어로 사용하는 문장을 구성



1. 실체 관계도 : 알기 쉬운 형식으로 그리는 일

 

실체와 이들 간의 관계를 도형으로 표기하는 것을 말합니다.

실체관계도는

• 실체의 표기
• 실체의 배열
• 관계의 연결
• 관계의 관계명 표기
• 관계의 기수표기
• 관계의 선택성 표기

의 순서를 거쳐서 완성됩니다.

 

▣ 실체의 표기

• 사각형의 도형 안에 실체명을 기록하고

▣ 실체의 배열

• 핵심, 특성, 관계실체의 순으로 배열
• 실체관계도의 외곽으로부터 중심부로 전개
• 관련실체는 사건 또는 프로세스의 순서를 고려하여 좌에서 우로, 위에서 밑으로 배열

▣ 관계의 연결

• 관계가 존재하는 실체 사이를 실선으로 연결
• 가능한 관계 선의 겹침(Cross)을 회피
• 관계의 중복을 피할 것
• 다대다 관계는 관련실체를 추가하여 2개의 1대다 관계로 분할 및 단순화

▣ 관계명 표기

• 관계명을 관계선의 좌우 또는 상하에 기록
• 관계명 기록시 시계방향 기준으로 표기

▣ 관계의 기수표기

• 1(One)
• M(One or More)

▣ 관계의 선택성 표기

• 해당실체의 한 건에 대한 상대실체의 선택성을 상대실체 쪽에 표기

이렇게 함으로써 실체관계도가 완성되었고, 논리적 데이터모형의 뼈대를 만들었습니다. 완성된 실체관계도는 다음과 같습니다.



실체관계도

 

1)데이터모형 식별자 정의:

앞서 추출한 실체에 대하여 실체를 대표할 수 있는 ID와도 같은 존재를 추출하는 단계

 

2)주/부식별자 정의

주식별자란? 실체가 어떠한 특성을 가지고 있는지를 단하나의 속성으로 알려줄수 있는 주 식별자.

Ex) 박상욱을 대표하는것 == 이름., 주민등록번호, 회사명, 직위 등등 후보키중 가장 영향력있는것.

▣ 식별자 규칙

• 실체 유일성(Uniqueness)보장
• 실체는 최소한 하나 이상의 식별자를 보유
• 식별자를 구성하는 속성은 필수요소(Mandatory)임
• 식별자 값은 한번 설정되면 변화하지 않아야 함

실체	주식별자	부식별자
고객	고객번호	주민등록번호, 전화번호
임대	임대번호 + 테이프일련번호
테이프	테이프일련번호

주/부식별자표

 

3)외부식별자 정의 : 두 실체간의 관계를 결정하여 주는 속성으로 관계에 의해서 자식실체에 위치하며 부모실체의 주식별자와 같은 값을 가진다.

 

실체	Primary Key	Foreign Key
고객	고객번호
임대	임대번호 + 테이프일련번호	테이프일련번호, 고객번호
테이프	테이프일련번호

외부식별자표

 

4)식별자 업무 규칙 정의 : 참조관계의 무결성을 강조한 개념으로 한실체가 입력, 삭제되거나 외부식별자의 변경에 따른 영향을 관리하는 것.

 

시나리오를 가지고 3개의 실체를 정의하였습니다. 여기서 "19800304002"라는 테이프 일련번호를 가진 테이프 하나가 폐기되었습니다. 이런 경우에는 해당 테이프에 대한 기록을 테이프 실체에서 삭제해야 합니다. 이런 경우 임대정보에 해당 일련번호의 기록이 있는 경우에는 어떻게 처리해야 하는가에 대한 규칙을 정의하는 것을 식별자 업무규칙정의라고 합니다. 

 

가. 입력규칙

입력규칙에는 6개의 기능을 제공하고 있습니다.

• Dependent : 대응되는 부모실체가 있는 경우에만 자식실체의 입력을 허용
• Automatic : 부모실체의 입력을 언제나 허용하며, 대응되는 부모실체가 없는 경우에는 이를 생성
• Nullify : 대응되는 부모실체가 없는 경우 자식실체의 외부식별자를 Null로 설정
• Default : 대응되는 부모 실체가 없는 경우에는 자식실체의 외부식별자를 Default 값으로 설정
• Customized : 특정한 검증조건이 만족되는 경우에만 자식 실체의 입력을 허용
• No Effect : 자식실체의 입력을 언제나 허용

나. 삭제규칙

• Restrict : 대응되는 자식실체가 없는 경우에만 주 실체의 삭제를 허용
• Cascade : 부모실체와 대응되는 자식실체를 모두 삭제
• Nullify : 부모실체의 삭제를 언제나 허용하며 대응되는 자식실체의 외부식별자를 Null로 설정
• Default : 부모실체의 삭제를 언제나 허용하며 대응되는 자식실체의 외부식별자를 Default값으로 설정
• No Effect : 부모실체의 삭제를 언제나 허용

다. 수정규칙

• Dependent : 대응되는 부모실체가 없는 경우에만 주 실체의 수정을 허용
• Cascade : 주 실체의 수정을 항상 허용하고 동시에 대응되는 자식실체의 외부식별자를 자동수정
• Nullify : 부모실체를 항상 허용하고 동시에 대응되는 자식실체의 외부식별자를 Null로 수정
• Default : 부모실체의 수정을 항상 허용하고 동시에 대응되는 자식실체의 외부식별자를 지정된 초기값으로 수정
• Customized : 특정한 검증조건이 만족되는 경우에만 부모실체의 수정을 허용
• No Effect : 부모실체의 수정을 조건없이 허용

이를 기준으로 업무규칙 정의표를 작성하면 다음과 같습니다.

 

부모실체	관계	자식실체	외부키	업무규칙
				입력	삭제	수정
고객		임대	고객번호	Dependent	Cascade	Cascade
테이프		임대	테이프일련번호	Dependent	Restrict	Cascade

외부키 업무 규칙 정의표

 

 

1)데이터의 모형 상세화:

실체에 대해서 더 자세하게 알아보는 단계

속성확정 -> 하부유형정의 -> 정규화검증 -> Domain정의 -> 속성별 업무규칙 정의

 

2)속성확정 : 실체에 관한 정보를 구성하는, 더 이상 분리될수 없는 최소 단위의 데이터.

 

3)하부유형정의:

실체의 속성 중에서 '허용되는 값의 범위'가 틀릴 경우 실체를 상부유형과 하부유형으로 분류하여 실체간에 새로운 실체를 생성하여 Supertype Entity와 Subtype Entity로 정의하는 것 

 

예를들어 신용카드로 임대를 하였을 경우에는 카드번호라든지, 분할횟수 등의 속성이 필요할 것입니다.

이런 경우 임대에 대한 정보 중에서 현금 임대와 신용카드 임대의 속성이 조금씩 다르기 때문에 임대라는 상부유형 밑에 현금 임대와 신용카드 임대라는 하부유형으로 실체를 추가하여 세분화할 수 있습니다.

 

4)*정규화 검증 :  정규화란 데이터의 논리적 구조를 결정하는 것입니다.

▣ 정규화 목적

• 데이터모형의 단순화
• 데이터모형의 일관성 유지
• 속성의 배열 검증
• 실체, 하부유형, 속성의 누락 여부 검증
• 데이터구조 안정성의 최대화

 

▣ 이상현상

• 입력이상 : 원하는 데이터를 입력하기 위해서 원하지 않는 데이터를 함께 입력해야 되는 현상을 말합니다. 예를 들어 앞서 속성확정표에서 임대 실체에서 임대에 대한 정보를 입력할 때, 회원에 대한 정보와 연체에 대한 정보도 입력을 해야만 합니다. 이런 경우를 입력이상이라고 합니다.
• 삭제이상 : 한 튜플을 삭제함으로써 유지해야 할 다른 정보를 삭제해야 되는 연쇄작용을 말합니다. 앞선 임대 실체에서 임대에 대한 정보를 삭제할 때, 회원의 개인정보도 삭제가 되게 됩니다. 이런 연쇄작용을 삭제이상이라고 합니다.
• 갱신이상 : 갱신이상은 하나의 튜플의 속성 값을 갱신함으로써 정보의 모순성이 생기는 현상을 말합니다.

이러한 문제들을 해결하기 위해 특정 식별자에 종속하는 속성들을 분리해내는 정규화 작업을 거쳐야 합니다. 

 

 

 함수적 종속성:

예를 들면 고객에 대한 주식별자는 고객번호입니다. 여기서 고객명은 고객번호에 의해서 식별이 될 수 있습니다. 즉 고객번호를 알고 있으면 고객의 이름도 같이 알 수 있다는 것입니다.

즉 고객명은 중복이 가능합니다. 예를 들어 '이현중'이라는 동명이인이 존재할 수도 있기 때문입니다. 이에 반해서는 고객번호는 유일하기 때문에 고객에 대한 중복이 발생되지 않습니다. 이때 성명은 고객번호에 종속적이다고 할 수 있습니다.

 

정규화단계 (1~5차정규화, bcnf까지 6차까지있지만 현업에서는 보통 3차정규화까지만 적용!)

 

가)1차 정규화:

반복적이거나 여러 개의 값을 지닐 수 있는 속성의 제거하는 것



1차 정규화

 

나) 2차 정규화:

2차 정규화는 식별자에 완전히 종속되지 않는 속성을 제거합니다.

즉 기본키가 하나가 아닌 여러 개의 속성으로 구성되어 있는 복합키에 부분적으로만 의존하는 속성을 제거하는 과정을 말합니다. 이 역시 예제를 통해서 설명을 드리도록 하겠습니다.



 



2차 정규화

중복저장을 피하기 때문에 저장공간이 낭비되는 것을 막을 수 있습니다. 

 

다) 3차 정규화:

비식별자 속성에 종속되는 속성을 제거하는 것입니다. 2차 정규화와는 비슷하지만, 그 주체가 식별자인지 비식별자인지에 따라서 2,3차 정규화로 나뉘어집니다.

 









3차 정규화 - 정규화 이전

위의 그림에서 보면 주소는 우편번호에 의존합니다. 이는 기본키도 아닌 비식별자 속성이다. 이를 3차 정규화 하면 다음과 같이 할 수 있습니다.









 

 

라) BCNF(Boyce/Codd Normal Form)

식별자가 유일한 결정자인 형태 를 말합니다.

 

마) 4차 정규화

4차 정규화는 속성값의 존재 여부에 종속되는 속성을 제거하는 정규화 모델 입니다.

 

바) 5차 정규화

5차 정규형은 Join Normal Form 이라고도 합니다. 

3개 이상의 무손실 분해된 실체를 말합니다.

 

 

역으로 전개하는 과정을 비정규화(Denormalization) 

지금까지 정규화 과정을 거친 비디오 대여점의 실체들은 다음과 같이 정규화 할 수 있습니다. 

 

고객	고객번호, 성명, 주민번호, 전화번호1, 전화번호2, 메모, 우편번호, 점수
임대정보	임대번호, 고객번호, 임대일자, 총계, 지급수단
임대품목	임대번호, 테이프일련번호, 임대일수, 가격, 연체일수, 연체금액
우편번호	우편번호, 주소
테이프	테이프일련번호, 제목, 출시일, 감독, 메모, 대여여부, 반납일

 

 

5. 도메인(Domain) 정의

Domain이란 각 속성이 가질 수 있는 값에 대한 일련의 특성을 말합니다. 즉, 각 속성이 가질 수 있는 값에 대한 업무적 제약조건으로부터 파악된 일련의 특성이라고 할 수 있습니다. 이는 Domain추출 -> Domain 정의 -> Domain 검증 의 절차를 거칩니다.

 






6. 속성업무규칙 정의

입력, 삭제, 수정 또는 조회 등의 작업이 동일 실체나 또는 다른 실체의 속성에 미치는 연쇄작용에 관련된 업무규칙으로 데이터 무결성의 마지막 형태입니다.

사용자규칙	사건	실체명	속성명	조건	연쇄작용
고객등록은 첫 임대거래 시에 이루어 진다	INSERT	고객정보	N/A	임대발생	첫 임대발생 => 고객정보생성
고객의 마지막 임대거래일로부터 1년이 경과하면 고객정보에서 삭제한다	DELETE	고객정보	N/A	최종 임대거래일 > 1년	신고객 = 구고객 ? 1년이 넘은 고객
고객의 점수가 1000점 이상인 경우에는 10%의 임대비용을 할인하여 준다	INSERT	임대정보	N/A	점수 > 1000	총계 = 총계 * 0.9

속성업무규칙정의표