[Oracle] #5-1 모델링(테이블 시나리오, 테이블 정의서) 개념 정리

* NCS 학습모듈 - 데이터 입출력 구현 참조

 

1. 관계형 DB 모델링 연습

 

 - 모델링을 위해서 업무 분석을 한다.

 - ER-WIN : 관계형 DB 모델링을 도식화할 수 있는 프로그램

 

 

2. 학사관리 업무분석

 

 학사관리를 하기 위해선 어떤 데이터들이 필요할까?

 - 학생이 수강신청을 하고, 수강신청 과목의 목록, 수강신청 과목의 총 학점

 - 해당 과목을 수강신청한 학생목록

 

 이 중에서 우리는 [학생, 과목, 수강신청] 의 데이터를 추출해서 시나리오를 작성하려한다.

 

 

3. 테이블 시나리오

 

1) 좋은 테이블 설계하기

 홍길동, 무궁화, 개나리 라는 학생이 수강신청을 한 것을 테이블 시나리오로 작성해보았다.

 

 위의 테이블은 잘못 설계되었다.

 왜냐하면 Null 값이 많이 발생되며 개나리의 수강과목이 추가될수록 칼럼이 계속해서 추가되기 때문이다.

 그리고 서로 다른 주제가 같이 있다.

 

 과목으로 중심으로 설계한 테이블도 봐보자.

 위의 테이블도 학생이 늘어날 때마다 계속 칼럼이 추가되는 잘못 설계된 테이블이다.

 

 

 결국, 테이블을 분리해야한다.

 학생 테이블과 과목 테이블을 만들어보자.

 

 여기에 새로운 학생이 추가되었다.

아래로 늘어났다!

 

 테이블이 옆으로 늘어나는 것이 아니라(칼럼이 새로 만들어지는 것이 아니라) 아래로 늘어난다.

 설계가 잘 된 테이블이다.

 

 

2) 데이터 정의하기

 테이블마다 PK(고유기본값)이 정해져야한다.

 어디에 PK값을 주어야할까?

 학생마다 고유로 갖게 되는(겹치지 않는) 학번이다.

이메일도 각기 고유의 값이기에 unique를 줄 수 있다.

 

 

 과목테이블은 어떻게 줄 수 있을까?

 PK값을 누구를 줘야할까?

 과목 학점은 겹치는 값이 너무나 많고, 과목명은 한글이 존재한다.

 그렇기 때문에 두 칼럼 모두 PK로 지정하기엔 어려움이 있다.

 이럴 땐 아래와 같이 개발자가 PK값을 주기 위한 칼럼을 새로 만들기도 한다.

 

코드의 이름을 붙이는 방법도 개발자의 센스

 

 

3. 관계형 데이터베이스

 

 1) 개념

 데이터베이스내의 테이블은 최소한의 의미(주제)를 가지는 테이블로 분리해서 구성하되, 그 테이블에 있는 컬럼을 연결한 것이다.

     부모테이블:Primary Key 기본키

     자식테이블:Foreign Key 외래키

 형태로 연결된다.

 

 분리된 테이블은 관계가 형성되는데 총3가지 유형이 있다.

 

 2) 3가지 유형

  ① 1:1 관계 : 두테이블간 어떤 테이블의 PK라도 다른 테이블에 전이되어도 상관없다.

 

  ② 1:N 관계

      두 테이블간의 관계를 위해서 교차테이블 만들 필요 없다.

      단, 1관계에 있는 PK를 N관계에 있는 테이블에 컬럼으로  전이시켜 관계를 맺는다.

       예) 부서 ---- 사원

 

  ③ N:M 관계

      두 테이블간의 관계를 위해서 교차테이블을 반드시 만든다.

       예) 학생-수강-과목

            회원-예매-영화

            회원-구매-물품

 

3) 다:다(N : N) 관계

 위의 결과를 살펴보며 다시 생각해보자.

 왜 이렇게 테이블이 잘못 생성이 될까?

 

 학생과 과목이 다:다 관계이기 때문이다.

 다:다 관계의 의미는 1명의 학생이 다양한 과목을 선택할 수 있는(다수) 것과 동시에 한 과목이 여러 명의 학생을 수강시킬 수 있는(다수) 관계라는 뜻이다.

 한 명의 학생이 딱 하나의 관계만을 맺을 수 있다면 테이블이 잘못 생성이 될 일이 없다는 뜻이다.

 

 이런 관계에는 반드시 교차테이블이 필요하다.

 이 교차테이블이 여기선 바로 수강신청이 된다.

 

 

4) 교차테이블

 

 교차테이블인 수강테이블을 만들어보았다.

 각 학번마다의 과목코드를 짝지어주었다.

 그리고 PK값으로 지정할만한 칼럼이 없어서 일련번호로 순서를 부여해서 사용한다(많이 사용된다).

 

 이 때!

 학번은 학생테이블을 참조해야한다.

 학생테이블에 없는 학번은 의미가 없다.

 과목코드 또한 그러하다.

 

 이런 관계를 부모-자식 관계라고 불린다.

 * Foreign Key로 두 테이블간의 데이터 무결성(결점이 없는)을 강제할 수 있으며 Join작업으로 원하는 작업을 처리한다.

 

 

 5) 개체 무결성

  - 기본키에 속해 있는 속성은 NULL값을 가지지 못한다.

  - 기본키는 레코드간에 유일한 식별자이므로 물리적으로 NULL값이 올 수 없다.

 

 

 6) 참조 무결성(FK제약조건)

  - 테이블은 참조할 수 있는 외래키 값을 가질 수 있다

  - 컬럼에 적용되는 3가지 제약조건

     not null

     no change->함부로 변하면 안됨

     no duplicate

 

  - foreign key 제약조건
  - 동일한 테이블 또는 다른 테이블에서 기본키 또는 고유키를 참조하는 제약 조건
  - on delete cascade  부모테이블의 행이 삭제되는 경우 자식테이블의 종속행을 삭제
  - on delete set null 부모테이블의 행이 삭제되는 경우 종속 외래키 값을 null로 변환
  - 부모 : Primary Key 기본키 <--> 자식 : Foreign Key 외래키

 

	부모테이블	자식테이블
입력	상관없음	반드시 부모 테이블에 있는 데이터가 입력된다
수정	수정하려는 데이터가 자식테이블에서 참조하고있는지 검증	부모테이블에 존재하는 다른 데이터로 변경 가능
삭제	삭제하려는 데이터가 자식 테이블에서 참조하고 있는지 검증	제약이 없다

 

 

4. 정규화(Normalization)

 

 1) 개념

   정규화(Normalization)의 기본 목표는 테이블 간에 중복된 데이타를 허용하지 않는다는 것이다.

   중복된 데이터를 허용하지 않음으로써 무결성(Integrity)를 유지할 수 있으며, DB의 저장 용량 역시 줄일 수 있다.

 

 2) 정규화의 목적

    데이터베이스의 변경시 이상 현상 제거

    데이터베이스 구조 확장시 재 디자인 최소화

    사용자에게 데이터 모델을 더욱 의미있게

    다양한 질의 지원

 

3) 정규화의 종류

    제1정규화 : 모든 속성은 반드시 하나의 값을 가져야 함 (원자값)

    제2정규화 : 모든 속성은 반드시 기본키 전부에 종속되어야 한다(기본키 일부에만 종속되어서는 안됨)

    제3정규화 : 기본키가 아닌 모든 속성 간에는 서로 종속될 수 없다(속성 간 종속성 배제)

     * 정규화 : 분리할 수 없는 값

 

 

 ① 제1정규화

   제1정규화란 테이블의 컬럼이 원자값(Atomic Value, 하나의 값)을 갖도록 테이블을 분해하는 것이다.

 

   예를 들어 아래와 같은 테이블이 있다고 해보자.

   여기서 무궁화는 제1정규화가 되지 않았다.

   제1정규화를 시켜보면 아래와 같이 된다.

 

 

  ② 제2정규화

   제2 정규화란 제1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것이다.

   여기서 완전 함수 종속이라는 것은 기본키의 부분집합이 결정자가 되어선 안된다는 것을 의미한다.

 

   수강테이블의 학번과 과목코드는 반드시 학생테이블과 과목테이블에 존재해야 한다.

 

 

  ③ 제3정규화

   제3정규화란 제2 정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분해하는 것이다.

   아래의 테이블을 보자.

 

   여기서 이름과 과목, 그리고 과목과 수강료는 서로 영향을 끼치고 있다.

   이 때 홍길동이 만약 과목을 Oracle DB로 바꾼다면?

   물론 수강료도 수정해줄 수 있겠지만 이럴 때는 이름-과목 | 과목-수강료 로 테이블을 나누어주는 것이 더 편리하다.

 

   이 과정을 제3정규화라고 한다.

 

 

 

5. 테이블 정의서

테이블 정의서의 예시

 

 테이블 정의서는 위의 모습과 같다.