Oracle - 데이터베이스 모델링

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

                          

데이터베이스 모델링

                                                                  

 

 

                   업무파악   "우리가 할 일이 무엇이지?"

                   개념적 데이터 모델링   "내가 하고자 하는 일에는 어떤 개념이 있고, 각각 개념들은 어떻게 상호작용 하는가?"

                   논리적 데이터 모델링   "관계형 데이터베이스에 맞게끔 표로 전환하는 작업"

                   물리적 데이터 모델링   "데이터 베이스를 선택하고, 실제 표를 생성"

 

 

 

 

---

 

업무파악

   

 화면 정의 기획서
     

앞으로 우리가 무엇을 만들 것인가?
어떤 화면이 필요한가?

 

 

 

 

 

개념적 데이터 모델링

 

화면에 필요한 데이터가 무엇인가?

 

 

 

 

 

과연 이런 데이터가 있다면?

 

 

문제가 발생한다!

게시글 작성자와 댓글 작성자가 중복되기 때문이다

 

 

그렇기 때문에 주제별로 분리해서 데이터를 만들어줘야 한다

게시글 / 회원 / 댓글

 

 

 

이렇게 분리하면 조인을 통해 원하는 데이터를 추출할 수 있다

 

                                           SELECT 아이디, 아이디정보, 댓글내용, 댓글작성일

                                                      FROM  회원  LEFT OUTER JOIN  댓글  ON  회원.아이디 = 댓글.글쓴이아이디

                                                      WHERE  id = kkk123;

 

 

 

---

 

 

 

 

 

Entity와 Attribute, Relation을

모두 작성한 그림을 그려보면

다음과 같은 그림으로 나타낼 수 있다

 

하지만 엔티티간의 중요한 관계 표시가 빠져 있다

 

 

 

---

 

 

Cardinality(관계 대응수)

엔티티와 엔티티의 관계대수

 

 

 

사원증 - 직원 ( 1 대 1 )

직원에게 사원증은 하나이다

사원증은 한 직원에게만 주어진다

 

부서 - 직원 ( 1 대 N )

직원에게 부서는 하나이다

부서는 직원을 여러 명 둘 수 있다

 

자격증 - 직원 ( N 대 N )

직원에게 자격증은 여러 개일 수 있다

자격증은 한 직원이 아닌 여러 직원들이 갖고 있을 수 있다

 

 

---

 

Optionality

엔티티와 엔티티의 필수, 옵션을 표기

 

 

부서 - 직원

직원은 부서가 필수이다

부서는 직원이 필수는 아니다(옵션)

 

 

 

 

1 : N 관계

 

 

 

 

 

 

1 : 1 관계

 

 

 

 

 

 

 

 

M : N 관계

 

 

 

 

 

하나의 열에 두 개의 데이터를 입력할 수 없다

그래서 새로운 테이블을 하나 만들어서

그 테이블과 관계를 맺도록 해야 한다

 

그것이 매핑 테이블이다

 

 

 

각 테이블의

PrimaryKey를 외래키(FK)로 참조 하고 있는

(회원의 아이디와 강의번호)

연결테이블(매핑테이블)을 사용해야 한다

 

 

 

 

 

강의신청이 회원과 강의를 연결하는 매핑 테이블이다

 

 

 

---

 

논리적 데이터 모델링

관계형 데이터 베이스에 맞게끔 표로 전환하는 작업

( ERD 프로그램 사용 )

 

 

---

 

1 : N 관계

회원의 id는 댓글의 글쓴이와 연결될 수 있다

회원의 id는 게시글의 글쓴이와 연결될 수 있다

 

 

 

 

1 : 1 관계

회원의 id와 관리자의 id는 연결된다

단, 회원테이블이 존재해야만

관리자도 존재하기 때문에

관리자의 id가 PK이자 FK가 동시에 된다

 

 

 

M : N 관계

회원의 id는 신청테이블의 mem_id와 연결된다

강의번호는 신청테이블의 lec_no와 연결된다

그리고 이 두 가지의 칼럼이 함께 PK가 된다(super key)

 

 

 

 

 

테이블 정규화

정규화란?

 

정제되지 않은 데이터를 관계형 데이터베이스처럼 만들어주는 방법이다

정규화는 1정규화부터 5정규화까지 있지만, 실무에서는 보통 1~3정규화까지의 과정만 거친다

 

---

 

제 1 정규화

속성 하나는 하나의 속성값만을 가져야 한다

 

 

제 2 정규화

기본키 중에 특정 컬럼에만 종속된 컬럼이 존재하면 안 된다

 

 

제 3정규화

기본키에 의존하지 않고 일반 열에 의존하는 열이 있다면 제거해야 한다

(이행 함수 종속을 제거해야 함)

 

 

---

 

다음의 예제를 통해 자세하게 알아보자

 

 

위의 폼을 1정규화부터 3정규화를 거쳐 정제된 데이터로 만들어보겠다

 

 

 

지금 이 데이터의 문제는 category 컬럼이 두 개의 데이터값을 갖고 있다는 것이다

그래서 이를 해결하기 위해서는  category의 데이터값이 하나만 갖도록 분리해야 한다

 

 

먼저 상품테이블과 카테고리 컬럼을 분리해서 어떠한 관계를 맺는지 확인할 필요가 있다

상품테이블과 카테고리 컬럼은  N : M 관계를 맺고 있다는 것을 확인할 수 있다

카테고리 컬럼과 상품 테이블을 연결하기 위해서는 category 또한 테이블로 만든다

(이때 카테고리를 구분할 수 있는 컬럼을 추가해서 만든다)

그런 다음 category 테이블과 상품 테이블을 연결할 수 있는 매핑 테이블을 만든다

 

매핑 테이블에는 각각의 테이블을 연결할 수 있는 key가 필요하다

(상품 테이블의 primary key, category 테이블의 primary key를 외래키로 참조)

이렇게 완성된 매핑테이블은 상품테이블과 카테고리테이블 각각과 1 : N 관계를 맺게 된다 

 

 

 

 

제 1 정규화를 거친 테이블을 보면 여전히 문제가 남아있다

 

title을 컬럼을 보면 "컴퓨터-LG"행이 중복으로 나타나고 있으며 구분하는 방법은 regdate컬럼이 유일하다

이렇게 되면 title이 pk(가정)인데 regdate라는 컬럼으로 또 다시 부분적으로 구분을 할 수 있기 때문에 문제가 된다

pk는 테이블 내에서 유일하게 존재해야 하며 pk가 모든 데이터의 값들을 구별할 수 있어야 한다(중복이 있어서는 안 됨)

이를 해결하기 위해서는 title부터 regdate까지를 따로 분리해서 하나의 키(=primary key) 역할을 하게 해야 한다

                                     (두 컬럼이 하나의 pk가 되어(=Super key) 테이블의 데이터를 구분할 수 있게 해준다)                               

                                         

                                                                               

 

 

그럼 이제는 완벽하게 정제된 데이터가 되었을까?

 

아니다! 여전히 문제는 남아있다

이번에는 한 테이블에 두 개의 pk가 존재하고 있다는 것이다

 

현재 num이 pk 역할을 하고 있는데 user_id는 num에 의해 구분되면서도

user_name부터  user_address까지를 구분하는 역할을 하고 있다

(product와 user는 상관관계도 없다)

이는 테이블을 분리함으로써 해결이 가능하다

 

 

 

 

물리적 데이터 모델링

ERD를 실제 구체화된 테이블로 변경하는 작업을 의미한다