7강. 관계에 대하여 #1

액세스를 사용해서 뭔가를 할 수 있겠다 싶을 때 보통 위와 같은 경고 메시지를 많이 경험하게 됩니다. 이런 경우 그저 답답하기만 하죠…… 이번 강좌를 통해서 이러한 경고메시지를 받게 되는 이유에 대한 내용입니다. 

지난 강좌를 통해서 필드와 테이블에 관련된 여러 개념들에 대해 익숙해지셨으리라고 믿습니다. 각 주제별로 만들어 놓은 테이블을 자유자재로 활용해서 원하는 정보를 얻고 또 그 자료를 유지하는 과정에서 자료의 유관성이 손상되지 않도록 하는 첫 번째 단계가 바로 테이블 사이의 관계를 정의하는 것입니다. 

관계를 정의하는 이유

테이블간의 관계를 정의한 다음에는 쿼리, 폼, 보고서를 만들어 여러 테이블의 정보를 한 번에 표시할 수 있습니다. 
     1) 테이블의 자료를 함부로 추가하거나 수정하거나, 삭제하지 못하게 할 수 있습니다.
         참조 무결성을 설정합니다.
         참조무결성은 데이터베이스 전체에 영향을 미칩니다. 오직 관계설정 창에서만 정의할 수 있습니다. 

     2) 관계로 연결된 테이블에서 자료를 수집하는 방법을 정할 수 있습니다.
         조인 옵션을 설정합니다.
        조인은 질의나 폼/보고서 등에서 설정할 기본 조인방법을 미리 설정해 둔 것으로 얼마든지 해당 질의
            테이블이나 폼/보고서에서 상황(작업)에 따라 재설정이 가능합니다.

관계형 룰 - 개체무결성과 참조 무결성

관계형 데이터베이스는 수많은 관련 정보를 주제별로 여기저기 테이블에 나누어서 관리합니다. 따라서 흩어져 있는 자료들이 서로 연관성을 유지하도록 어떠한 원칙을 마련해야 할 겁니다. 이것을 '관계형 룰' 이라고 합니다.

여기에는 다음 원칙이 있습니다.

1. 개체 무결성 룰(Entity Integrity)
   이미 설명한 기본 키의 개념은 개체무결성 룰(Entity Integrity)을 달성하기 위한 것입니다. 여기서 개체란 테이블을 의미할 겁니다. 다시 말해 테이블에 들어있는 레코드들은 각각을 구별할 수 있어야 한다는 겁니다. 따라서 기본키를 설정하고 이 기본키는 고유해야 하며 Null 값을 포함해서는 안 된다는 겁니다.
   
2. 참조 무결성 룰(Referential Integrity)
   레코드를 입력하거나 삭제할 때 테이블 간에 정의된 관계를 유지하기 위해 따르는 규칙입니다. 참조 무결성을 강화할 경우, 기본 테이블에 관련 레코드가 없으면 관련 테이블에 레코드를 추가하거나 관련 테이블에 고아 레코드를 초래할 수 있는 기본 테이블의 값을 변경할 수 없으며, 관련 테이블에 일치하는 관련 레코드가 존재하면 기본 테이블의 레코드를 삭제할 수 없게 됩니다.

'테이블'사원목록'에서 관련 레코드가 필요하기 때문에 레코드를 추가하거나 변경할 수 없습니다.' 라는 경고 메시지가 뜨는 이유가 바로 이 때문입니다.
관계를 설정할 때 사원목록이 마스터 테이블로 아마도 사원의 인적사항 등 기초 정보를 입력해 둔 모양입니다. 그리고 세부항목 테이블은 아마도 월별 사원의 판매실적을 기록하는 테이블인 모양입니다. 하여간 마스터 테이블인 사원목록에 해당 사원의 정보가 없는 상황에서 세부항목 테이블에 자료를 입력하려고 하면 부모 없는 자식이 되는 상황으로 정보로서의 가치가 거의 없고 나중에 자료 처리시 문제를 일으키는 자료가 되므로 미리 자료의 추가를 막게 됩니다.

관계의 종류

Microsoft Access에서 만들어지는 관계의 종류는 관련된 필드가 정의되는 방법, 다시 말하면 기본키/고유키 여부에 따라 달라집니다. 

•  일대다 관계(one-to-many 1:m) 
  관련된 필드 중 한 개만 기본 키이거나 고유 인덱스가 있는 경우에 만들어집니다. 일반적으로 가장 많은 형태입니다.
  연결된 상대 테이블의 필드(외래키)가 기본키 또는 고유키면 당연히 일대일의 관계로 변합니다.
  
• 일대일 관계 (one-to-one 1:1)
  관련된 필드 모두 기본 키이거나 고유 인덱스가 있는 경우에 만들어집니다. 이러한 테이블은 하나로 합쳐질 수 있습니다. 따라서 아래와 같은 경우를 제외하고는 자주 사용되지는 않습니다. 
  
• 다대다 관계(many-to-many, m:m)
  실제로 두 테이블의 외래 키가 기본 키로 구성된 제3의 테이블(병합테이블 Junction Table)에 대한 두 개의 일대다 관계로 만들어집니다. 
  데이터베이스 디자인 단계에서 업무분석을 통해 주제 테이블들을 구성하고 두 개의 주제 테이블이 다대다의 관계로 분석이 되면 반드시 일대다의 관계가 만들어지도록 새로운 병합테이블을 만들도록 합니다.

액세스의 관계 도식도(Diagram)

액세스의 관계의 도식(다이어그램)에서는 일대일, 일대다, 다대다 등 관계의 종류와 참조무결성이 설정여부를 한눈에 알아 볼 수 있습니다. 참조 무결성은 매우 중요합니다. 따라서 참조무결성이 설정되어 있어야 관계 도식도에서 관계의 종류를 알려주는 기호 표시를 나타내 줍니다.

우선 액세스의 예제 파일인 Northwind.mdb 파일을 열고 관계 도식도를 보도록 합시다.

관계 설정 창을 열려면... 
1) < F11 > 키를 눌러 데이타베이스 창을 불러낸 다음....
2) 메뉴에서 도구-관계를 클릭합니다..( 단축키는 Alt-TR )
또는 데이타베이스 창에서 오른쪽 마우스 버튼을 눌러 단축메뉴를 불러내고 단축 메뉴에서 관계 메뉴를 클릭합니다.

• 일대일 관계는 관련된 필드 모두 기본 키이거나 고유 인덱스가 있는 경우에 만들어집니다.
• 일대다 관계는 관련된 필드 중 한 개만 기본 키이거나 고유 인덱스가 있는 경우에 만들어집니다.
  
• 다대다 관계는 실제로 두 테이블의 외래 키가 기본 키로 구성된 제3 테이블에 대한 두 개의 일대다 관계로 만들어집니다. 
  
• 　액세스의 샘플 파일인 Northwind.mdb의 각 테이블간 관계 설정 내용입니다.

다대다 관계 설정하기

다대다 관계의 두 테이블을 직접적으로 관계설정을 할 수는 없습니다. 따라서 이런 관계의 두 테이블을 분해하여 3개의 테이블로 만든 다음 2개의 일대다 관계로 해소시켜야 합니다. 다대다 관계를 일대다 관계로 해소키는 일은 데이터베이스 디자인에 있어서 매우 중요한 작업 중의 하나입니다.

액세스의 샘플 파일인 Northwind.mdb의 각 테이블간 관계 설정을 보면 일대다의 관계는 여기 저기서 보입니다. 정말로 보입니까? 대표적인 다대다의 관계를 하나 찾아봅시다. 주문(Orders 테이블)과 제품(Products 테이블)간의 관계가 그렇습니다. 하나의 제품은 여러 주문에 반복되어 포함될 겁니다. 또 한번 주문할 때 여러 제품을 동시에 주문하겠지요? 다대다의 관계입니다. 따라서 다대다의 관계를 해소하려면 두 테이블을 연결할 테이블(접합 테이블 Junction Table)을 만든 다음 다른 두 테이블의 기본키 필드와 같은 형식/크기를 갖는 새 필드(외래키)를 테이블에 추가하여 한편으로는 제품 테이블과 또 한편으로는 주문 테이블과 일대다의 관계를 맺어줄 수 있도록 합니다. 이 두 필드는 고유키 또는 기본키로 만들고 관계를 설정해 주면 됩니다. 기타 필요한 필드가 있으면 다른 테이블과 마찬가지로 얼마든지 필드를 추가할 수 있습니다. 

참고로 이 예에서 접합 테이블의 기능은 Orders 테이블에서 떨어져 나온 제품의 주문에 대한 세부 정보이므로 이름을 [OrderDetails]라고 지었습니다. Master-Detail이란 의미를 테이블 이름에 부여한 것으로 이와 같이 주제가 이름에 잘 나타나도록 짓는 것도 요령입니다.

액세스에서 관계를 정의하는 방법

액세스에서 이러한 관계를 정의할 수 있는 방법은 다양합니다.

1) 관계설정 창을 이용하여 설정
관계설정 창을 이용 하여 정의하는 경우는 조인의 3가지 형식과 참조무결성을 동시에 설정할 수 있습니다.
여기에서 정의된 조인의 형식은 테이블이나 폼에서 만들 조인의 기본값으로만 사용됩니다. 얼마든지 여러 질의테이블이나 폼에서 재 설정이 가능합니다.
여기에서 정의한 참조무결성은 데이터베이스 전체에 영향을 미칩니다.

2) 질의디자인 창에서 설정
질의디자인 창 에서 관계를 설정하여 조인의 3가지 형식 중 하나를 지정할 수 있지만 참조 무결성을 강화되지 않습니다.
여기서 정의한 관계의 조인형식은 정의된 질의테이블에서만 영향을 줍니다. 다른 테이블간의 연결에는 전혀 영향을 주지 않습니다.

3) 하위폼/하위보고서에서 필드연결 속성의 설정
폼에서는 테이블을 폼의 데이터 원본으로 매여 놓은 바운드 폼들을 상위폼과 하위폼의 형식으로 포함시켰을 때 하위폼의 속성에서 하위필드연결, 기본필드 연결 속성을 이용한 연결을 설정을 할 수 있지만 오직 하나의 지정된 연결(조인) 형식만 있습니다.

4) 하위데이타시트를 삽입하면서 관계 만들기
액세스2000 버전부터는 하위데이타시트를 지원합니다. 이 하위데이타시트를 삽입하는 과정에서 두 테이블간의 관계가 설정되어 있지 않으면 관계를 만들 수 있습니다. 여기서도 역시 참조 무결성은 강화할 수 없습니다. 따라서 참조 무결성을 강화하려면 관계정의 창을 직접 열고 설정해주어야 합니다. 

- 먼저 테이블을 열고 메뉴에서 [삽입-하위데이타시트]를 실행합니다.
옵션설정 창이 뜨면 하위테이블로 삽입할 테이블을 선택합니다. 이때 선택한 테이블과 이미 관계를 만들어 둔 경우는 하위필드연결/기본필드연결 입력란에 마스터 테이블의 기본키와 삽입할 하위테이블의 외래키가 바로 설정이 되지만 그렇지 않은 경우는 직접 선택해야 합니다(기본키와 외래키에 대한 개념을 아직 터득하지 못하신 분들은 지난 강좌를 참고하십시오).

이제 [확인] 버튼을 누릅니다. 이때 두 테이블간에 관계설정을 만든 적이 없으면 관계를 만들겠다고 메시지 창이 뜰 겁니다.
설정을 마치면 다음 그림과 같이 왼쪽의 레코드 선택기 우측으로 더하기(+) 기호가 나타납니다. 이것을 누르면 연결된 하위테이블의 레코드를 복 수 있습니다.

  목차 | 이전 | 다음