Database/Video Box example

Video Box 사 모델링 예제

Soul-Learner 2008. 3. 11. 13:28

복합키 생성 예제

CREATE TABLE myTable (
 OrderID     number NOT NULL,
 ModelID    number NOT NULL,
 Description VARCHAR2(40),
 PRIMARY KEY (OrderID, ModelID)
);

CREATE TABLE myTable (
 OrderID     number NOT NULL,
 ModelID    number NOT NULL,
 Description VARCHAR2(40),
 constraint pk_myTable PRIMARY KEY (OrderID, ModelID)
);


혹은 다음처럼....

CREATE TABLE myTable (
 OrderID     number NOT NULL,
 ModelID    number NOT NULL,
 Description VARCHAR2(40)
);
alter table myTable add constraints myTable_PK primary key (OrderID, ModelID);

//아래처럼 constraints에서 s 를 제외해도 된다.
alter table myTable add constraint myTable_PK primary key (OrderID, ModelID);


키의 이름을 찾기 위해서는 Oracle 에서 제공하는 View 인 ALL_CONSTRAINTS 에서 질의하면 된다.

select constraint_name from all_constraints where table_name='MYTABLE';


키를 삭제하기 위해서는 다음과 같이....

alter table mytable drop constraint pk_mytable;


개념적 데이터베이스 설계

▶Video Box 사의 업무 기술서◀

Video Box 사는 각 제인점이 모두 network로 연결되어 있는  Video Shop 체인을 운영하며 각 체인점의 데이터를 관리해 준다. Video Box 사는 각 체인점을 주소, 전화번호 등 그 체인점에 대한 일반적인 정보와 고유권한을 가지고 있다. 각 체인점은 독립적으로 비디오를 구입, 대여, 판매, 분실, 폐기하고, 대여기간과 대여가격 등의 가격체계도 체인점의 상황에 따라 자유롭게 조정할 수 있다. 본사는 각 대여점의 구입, 대여, 판매, 분실, 폐기 등에 대한 정보는 관리하나 실제 업무에는 전혀 관여하지 않는다.

체인점에는 한명의 체인점주와 여러 명의 아르바이트생이 일하고 있으며, 체인점주와 아르바이트생들은 개인 ID와 PASSWORD 를 이용해 login 하고 , 체인점주는 그 체인점의 일주/월 매출액, 현재 보유 비디오 정보, 현재 대여 중인 비디오 정보등 그 체인점에 대한 모든 데이터에 접근할 권한이 있으나, 아르바이트생들은 단지 대여에 관계된 데이터와 일일 매출액에만 접근할 수 있다.

Video Box 사는 비디오를 장르별, 시청가능 연령대별 및 주연배우, 감독 등에 대한 다양한 정보를 관리하며, 각 비디오에 대하여는 그 비디오테이프에 대한 고유번호의 일반적인 정보들과 대여 및 연체 여부와 그리고 그 비디오테이프가 어느 지점의 테이프인지를 구분할 수 있어야 하며, 그 테이프를 통해 그 지점에서 얼마의 매출을 올렸는지 일, 주, 월별 정보를 알 수 있어야 한다.

Video Box 사는 각 체인점의 정보 통계를 알고 현시점(일, 주, 월별)의 대여 인기순위 등의 전체 통계에 대한 정보를 실시간으로 체인점에 제공하며, 각 체인점에서는 타 체인점에 대한 정보를 알 수 없다.

Video Box 사는 회원에 대한 일반적인 정보 외에 회원이 가입한 지점에 대해여 알고 있어야 한다. 어떤 사람이 Video Box 사의 체인점에서 비디오를 대여하려면 반드시 회원가입을 하여야 하고, 어느 체인점에서든 한번 회원가입을 한 사람은 다른 체인점에서 또 회원가입을 할 필요가 없다.

회원은 지급할 금액을 포인트와 현금으로 결제할 수 있다. 한 회원은 모든 체인점에서 대여 금액을  현금으로 지급할 떠ㅐ 결제 금액의 10%를 포인트로 적립받고, 연체료를 지급할 때는 현금으로 지급해도 포인트가 적립되지 않으며, 포인트로 대여로를 지급할 때는 포인트가 누적되지 않는다. 대여료를 포인트로 지불시에는 100포인트=100원으로 계산한다. 회원은 회원 가입 시 1000포인트를 적립 받으며, 포인트를 구매함으로 선금입금이 가능하고, 포인트 선 구매 시에는  5000원당 6000포인트를 적립하며, 포인트는 최소 1300포인트 이상 적립되었을 때에만 사용할 수 있다.




논리적 데이터 설계

▶엔티티 타입(Entity Type)정의◀

엔티티: 데이터로 관리되는 대상 개체

기본 엔티티: 기본적으로 존재하는 엔티티
중심 엔티티: 업무의 중심이 되는 엔티티
행위 엔티티: 두개 이상의 엔티티들로부터 발생하는 엔티티


Video Box 사의 Entity

회원, 체인점, 비디오



▶엔티티 타입 정의서◀


Entity Type 설 명 동의어 Entity Type 구분
체인점 Video Box에 소속된 체인점의 정보      기본
회원 회원의 기본정보 및 누적 포인트 고객 기본
비디오 회원에게 대여되는 비디오 정보   기본


▶관계(Relation)의 정의◀


체인점------보유한다------비디오
회원---------가입한다-------------체인점
회원--------대여한다--------------비디오

업무정의서와 위의 관계를 관찰해 보면 회원에게 비디오를 대여할 때 대여기간, 대여가격 속성을 가져야할 또 하나의 엔테티를 정의할 필요가 있다.


▶Membership 설정◀ (두 엔티티타입 사이의 참여자 수에 대한 표현)

1:1, 1:M, M:M



▶관계 정의서◀

관계의 정의 및 Membership 정의를 한개의 표로 정의한다.

Entity Type 관계 및 Membership 설 명 필수/선택 대상 Entity Type
체인점 각 체인점은 여러 개의 비디오를 보유할 수 있다 선택 비디오
각 비디오 테입은 하나의 체인점에 진열된다 필수
체인점 각 체인점은 여러 명의 회원을 가질 수 있다 선택 회원
각 회원은 한 체인점에 가입된다 필수
회원 각 회원은 여러개의 비디오 테입을 대여할 수 있다 선택 비디오
각 비디오는 한 회원에게 대여된다 필수



▶Entity Relationship Matrix◀ (관계 정의서를 한개의 표료 간결하게 표현함)

필수: o  선택: x  1개: 1  다수개: M

  체인점 회 원 비디오
체인점   M x M x
회 원 1 o   M x
비디오 1 o 1 o  


▶해석요령◀

세로로 나열된 엔티티를 주어로 해석한다.
가로로 나열된 엔티티를 목적어로 해석한다.
주어 앞에는 '한개의' 를 붙여서 해석한다. 예를 들어, 한개의 체인점은 ~
목적어 앞에는 주어, 목적어가 교차하는 셀에 입력된 수를 붙여서 해석한다. 예를 들어, 한개의 체인점은 다수(M)의 회원을 가질 수 있다.

해석 예 (체인점과 회원의 관계를 예로 들어보면...)

1 개의 체인점은 다수(M)의 회원을 가진다 (체인점-------M 회원)
(회원 왼쪽에 있는 M은 관계에 참여하는 회원의 수이다)

1 명의 회원은 한개(1)의 체인점에 가입해야 한다. (회원-----1 체인점)
(체인점 왼쪽에 있는 1 은 관계에 참여하는 체인점의 수이다)

위의 양방향의 관계를 한개의 그림으로 표시하면 다음과 같이된다.
체인점 1 --------- M 회원

위의 관계를 해석할 때는, 위의 관계는 양방향을 의미하고 있으므로 단방향 관계로 풀어서 다음과 같은 요령으로 해석할 수 있다.

체인점을 주어로 할 경우, 한개의 체인점은 다수의 회원을 가질 수 있다.
회원을 주어로 할 경우, 한명의 회원은 1개의 체인점에 회원으로 등록해야 한다.


▶ERWin7.1 을 이용한 엔티티간의 관계표현◀


▶식별자 정의◀

모든 엔티티는 반드시 한개 이상의 식별자(Identifier)를 가져야 한다.


▶식별자 선정시 고려사항◀

실제 업무에서 자주 사용하는 속성
명칭, 부서이름 등과같이 이름으로 기술되는 속성들은 식별자로 적합하지 않다.
주 식별자로 선정되는 속성의 수는 많아도 7~8개를 넘지 않아야 한다.


예를 들어 회원 엔티티의 주 식별자로 '회원번호' 를 생각해 볼 수 있다. ERWin7.1 에서 회원 엔티티의 비어 있는 주 식별자 자리에 주 식별자를 지정해 보자. 다음과 같이 엔티티의 관계만 설정된 상태에서 회원이라는 엔티티타입을 클릭하여 선택한다.
까맣게 선택된 '회 원' 문자열을 한번더 클릭하면 다음과 같이 편집 가능한 상태로 전환된다.
위의 상태에서 '회 원' 문자열에 커서가 들어가 있어서 엔티티 이름을 편집할 수 있는 상태인데, 우리가 하려고 하는 작업은 주 식별자를 추가하는 내용이기 때문에 위의 상태에서 그냥 <엔터> 키를 치면 주 식별자의 위치로 커서가 이동하여 주 식별자를 추가할 수 있게 된다. 아래의 그림에서 파랗게 Attribute0라는 나와 있는 곳에 '회원번호' 라고 입력한다.
'회원번호'를 입력한 후에 <엔터>를 치면 다음과 같은 상태가 된다.



위의 그림과 같이 회원 엔티티의 주 식별자를 회원번호로 입력하면 관계로 연결된 엔티티의 주 식별자도 자동으로 회원 엔티티의 주 식별자와 동일하게 정의되는 것을 확인할 수 있다.  이런 경우의 관계를 식별관계(Identifying Relationship) 이라고 하며 연결선은 실선으로 표시된다. 즉, 연관되어 있는 2개의 엔티티의 주 식별자가 동일한 관계를 말한다. 연관되어 있는 2개의 엔티티가 서로 다른 주 식별자를 정의하고 있을 경우에는 이들 관계를 비식별관계(Non-Identifying Relationship)라고 하며 연결선은 Dash line 으로 표현된다.

위의 그림에서 '대여' 엔티티를 다시 보면, 주 식별자로 회원번호가 설정되어 있는데, 한명의 회원이 여러번 대여할 수도 있기 때문에 대여 엔티티에서 '회원번호'는 주 식별자로 적합하지 않다는 것을 알 수가 있다. 그래서 다음과 같이 다른 속성을 복합적으로 적용하여 복합키를 적용할 필요성도 있게 된다.

'대여' 엔티티를 선택한 후 주 식별자를 클릭하여 편집가능한 상태로 전환한 다음, <엔터>키를 치면 다음 속성을 주 식별자로 정의할 수 있게 된다.

최종적으로 아래의 그림과 같이 '대여'엔티티의 주 식별자가 복합 식별자로 설정되었다.


여기서 한가지 생각해야 할 점은 '대여' 엔티티를 참조하는 자식 엔티티는 '대여' 엔티티의 주 식별자를 외부 식별자로 포함할 때 지나치게 속성의 수가 증가하게 된다. 이럴 경우, 설계속성으로 '대여번호' 라는 속성을 이용하여 대여가 발생할 때마다 그 수를 증가시켜 나가면 '대여' 엔티티를 참조하는 자식 엔티티들은 '대여번호' 만 참조하면 되므로 속성의 수가 많아지지 않게 된다. 아래의 그림과 같이 설계속성인 '대여번호'를 추가해 본다.

먼저 양 엔티티 사이에 있는 관계선을 선택하고 'Delete' 키를 눌러 삭제한다. 양 엔티티의 주 식별자가 동일하지 않기 때문에 삭제한 후에 '대여' 엔티티를 변경해야 순조롭게 진행된다. 관계선을 삭제한 후에 다음 그림과 같이 '대여' 엔티티의 주 식별자와 그 외 속성들을 설정해 준다.


'대여' 엔티티의 '회원번호' 속성은 대여정보가 기록될 때마다 반드시 '회원번호' 존재해야 하므로 'NOT NULL' 속성으로 설정해 줄 필요가 있다. '대여' 엔티티 위에서 마우스 우측을 눌러 'Attributes....' 를 선택하고 다음과 같이 설정해 준다.




위의 그림과 같은 상태에서 관계선을 설정해 줘야 한다. 화면 우측 위에 있는 툴바에서 Non-identifying relationship 을 선택한 후에 '회원' 엔티티와 '대여' 엔티티를 각각 클릭한다.


다음 그림과 같이 '대여' 엔티티에 이미 등록된 속성 '회원번호'를 외부식별자로 변경할 것인지 묻는 화면에서 'OK'를 눌러준다.


설정된 관계선은 다음과 같다. 선의 모양도 변경되어 Non-Identifying Relationship 의미를 표현하고 있다.



▶속성 정의서 작성 예◀


엔티티 타입 이름

속성 이름

상세 설명

속성 분류

체인점

체인점 코드

각 체인점에 부여된 고유 코드

설계

체인점 주소

체인점의 주소

기본

체인점 전화번호

체인점의 전화번호

기본

….

….

….

회 원

회원번호

가입 시 부여된 번호

설계

체인점 코드

각 체인점에 부여된 코드

설계

회원 이름

회원의 이름

기본

회원 주소

회원의 거주 주소

기본

비디오

비디오번호

비디오 고유번호

설계

비디오이름

비디오의 이름

기본

제작사

비디오를 제작한 회사의 이름

기본

상영시간

비디오의 총 상영시간

기본

 


▶도메인(Domain) 정의◀
속성에 대한 데이터 타입과 크기, 제약사항을 일정한 규칙에 따라 지정하는 것

도메인(Domain)정의서 예

 

엔티티

속성 이름

도메인 이름

데이터 타입

크기

제약 조건

체인점

체인점번호

CHAIN_NO

NUMBER

 

PK

체인점 이름

CHAIN_NAME

VARCHAR2

20

NOT NULL

주소

CHAIN_ADDRESS

VARCHAR2

50

NOT NULL

전화번호

CHAIN_PHONE

VARCHAR2

20

NOT NULL

위치

CHAIN_LOC

VARCHAR2

20

 

사 원

아이디

ID

VARCHAR2

20

PK

패스워드

PASSWORD

VARCHAR2

20

NOT NULL

구분

EMP_CHK

CHAR

1

NOT NULL

이름

EMP_NAME

VARCHAR2

20

 

주소

EMP_ADDRESS

VARCHAR2

50

 

전화번호

EMP_PHONE

VARCHAR2

20

 

 


▶정규화(Normalization)◀
종속성 이론을 이용하여 잘못 설계된 스키마를 더 작은 속성의 집합으로 쪼개어 바람직한 스키마로 만들어가는 과정이다.
다양한 유형의 검사를 통해 데이터 모델을 좀 더 구조화하고 개선해 나가는 절차에 관련된 이론이며, 구조화하고 개선해 나가는 일관된 원칙은 하나의 테이블에 중복된 데이터가 없도록 한다는 것이다.

다음의 한 예를 통해 정규화를 적용하여 검증하지 않은 경우에 발생하는 이상현상을 이해하여 보자. 학원에서 한개의 반을 관리하기 위해서 다음과 같은 구성을 가지는 테이블이 있다고 한다.

반 이름

과정명

강사이름

강사전화

학생이름

학생주소

학생전화

우리반

Java Web

박강사

011-259-6987

홍길동

서소문동

010-258-5987


만약, 학생 홍길동이 유일한 학생일 경우에 홍길동이 학원을 그만 두었다고 한다면 홍길동을 삭제해야 하는데, 홍길동이 포함된 레코드를 삭제하면 해당 강사의 정보 및 과정에 대한 정보까지 함께 삭제된다. 이런 경우를 삭제이상이라고 하며, 이와 같이 정규화를 통해 검증하지 않은 엔티티에서는 삽입, 갱신, 삭제이상이 발생할 가능성이 있기 때문에 정규화라는 절차에 따라 검사해 볼 필요가 있다.



지금까지 작업을 통해서 다음과 같은 엔티티 타입과 속성들이 추출되었다고 한다면 이런 구조가 문제는 없는지 정규화 과정을 통해서 검사해 볼 순서이다.

현재까지 추출된 엔티티 타입 및 속성

 

체인점

체인점 번호(PK)

체인점 이름

1

서소문

 

 

회 원

회원 번호

회원 이름

회원 주소

1

홍길동

서소문동

2

김인철

합정동

 

 

비디오

비디오 번호(PK)

비디오 이름

1

괴물

2

가문의 영광

3

다빈치 코드

4

흐르는 강물처럼

 

 

대 여

체인점번호(PK)

회원번호(PK)

비디오번호(PK)

대여일시

대여기간

대여료

1

1

1

2008-03-02

2

1000

1

1

2

2008-03-12

2

1500

1

2

3

 

1

1500

1

2

4

 

2

1000

1

2

1

 

2

1000

 

 


▶제1 정규화◀

속성값 중에서 원자성을 갖지 않은 속성을 분리해서 별도의 엔티티를 구성한다.

원자성을 갖지않는 속성의 분리


 


회원번호

암호

전화

주소

취미

1

1234

010-8745-589

중구 서소문동

영화, 운동, 독서


위의 테이블에서 취미 속성은 원자성을 갖지않는 속성이다. 이런 속성을 분리해서 별도의 엔티티를 구성한다.

회원

회원번호(PK)

암호

전화

주소

1

1234

010-8745-589

중구 서소문동

 

취미

취미번호

회원번호(FK)

취미

1

1

영화

2

1

운동

3

1

독서




▶제 2 정규화◀

복합키로 구성된 테이블에만 적용되는 정규화이며, 부분적 함수적 종속성을 제거하는 과정이다. 위에서 추출한 '대여' 엔티티는 복합 식별자를 갖는 엔티티이므로 제 2 정규화의 대상이 된다. 다음 내용을 보면서 확인해 본다.

 

대 여

체인점번호(PK)

회원번호(PK)

비디오번호(PK)

대여일시

대여기간

대여료

1

1

1

2008-03-02

2

1000

1

1

2

2008-03-12

2

1500

1

2

3

 

1

1500

1

2

4

 

2

1000

1

2

1

 

2

1000

 

대여기간, 대여료는 체인점마다 다르게 책정되기 때문에 주 식별자를 구성하는 체인점번호, 비디오번호에 종속적이라고 할 수 있다. 그러므로 이들 2개의 속성을 분리하여 별도의 엔티티(대여비디오 상세정보)를 구성해야 한다.

대여비디오 상세정보

체인점번호(PK)

비디오번호(PK)

대여기간

대여료

1

1

2

1000

1

2

2

1500

1

3

1

1500

1

4

2

1000

1

1

2

1000

 

 

대여

체인점번호(PK)

회원번호(PK)

비디오번호(PK)

대여일시

1

1

1

2007-12-03

1

1

2

2008-02-11

1

2

3

2008-03-02

1

2

4

2008-03-05

1

2

1

2008-03-12

 


▶제 3 정규화◀

이행적 함수적 종속성을 찾고 분리하는 절차

주 식별자에 종속적인 속성들 중에서 속성끼리 종속관계가 성립하는 경우에는 이들을 분리해서 별도의 엔티티로 구성한다.

제 3 정규화의 예

이행적 함수적 종속성을 갖는 경우

학번(PK)

학과명

학과전화번호

1234

물리학과

952-65875

 
위의 테이블은 이행적 함수적 종석성을 갖는다. 학번(PK)->학과명,  학과명->학과전화번호의 관계가 성립되기 때문에 이행적 함수적 종속성을 갖는다.


위의 테이블은 다음과 같이 2개의 테이블로 분리되어야 한다.

학번

학과명

 1234

 물리학과

 

 

학과명

학과전화번호

 물리학과

 

952-65875