2018.03.22

“대규모 데이터 정리의 시작” 엑셀에서 관계형 데이터베이스 만들기

JD Sartain | PCWorld

엑셀은 놀라울 정도의 데이터베이스 파워를 지니고 있다. 관계형 데이터베이스를 생성하기 위해서는 하위 테이블과 연결되는 마스터 테이블부터 시작해야 한다. 마스터 테이블은 슬레이브(Slave), 차일드(Child), 그리고 디테일(Detail) 테이블로 불린다.

보다 구체적인 내용을 알아보기 전에, 관계형 데이터베이스 생성을 위한 각 단계별 설명과 연습을 해 볼 수 있는 통합문서를 준비했다. 화살표를 클릭하거나, 아래의 링크를 누르면 다운로드 받을 수 있다.

예제 통합 문서 다운로드 하기 

관계형 데이터베이스란?
엑셀은 구조부터 데이터베이스 생성에 적합하다. 데이터베이스란 기본적으로 연결된 아이템들(스프레드시트)의 조합인데 이들이 연합하여 다수의 레코드(테이블) 그룹 내에 단일 레코드(열)를 생성하게 된다.

스프레트시트 한 장이라도 그 자체로 데이터베이스라고 부를 수는 있지만, 관계형 데이터베이스는 아니다. 관계형 데이터베이스는 마스터 스프레드시트 테이블과 그 하위 슬레이브 테이블 또는 슬레이브 스프레드시트 전부를 포괄하는 개념이다.

예를 들어 운전면허가 차량국 컴퓨터에 저장된 플랫 파일 데이터베이스 상의 하나의 레코드라고 해 보자. 면허증에는 나의 이름, 성별, 신장, 체중, 눈동자 색, 머리 색, 주소, 생년월일, 면허증 발급 및 만료일 등의 정보가 담겨 있다.

그렇지만 이름, 주소, 성별이 같은 사람은 여러 명이 있을 수 있기 때문에, 면허증에는 항상 면허 번호가 함께 기재 돼 있다. 한 사람당 하나씩만 부여 되는 이 고유 번호는 키 필드(Key Field)라 불리며 플랫 파일 데이터베이스를 다른 관련 데이터베이스 들(관계형 데이터베이스 들)과 연결시키는 역할을 한다.

기본적인 플랫 파일 스프레드시트 데이터베이스

(이 예시에서 사용된) 이러한 마스터 데이터베이스(MasterDB 탭)는 운전 면허증에 담긴 모든 정보를 포함하고 있다. 슬레이브, 차일드, 그리고 디테일 데이터베이스 등으로 불리는 하나 이상의 관계형 데이터베이스들은 운전자 개인에게 관련된 부가적인 정보를 담고 있다(그리고 이러한 정보를 식별하는 것은 키 필드인 면허 번호이다). 예를 들어, 한 슬레이브 데이터베이스에는 그 동안 저질렀던 모든 교통법규 위반 사항에 관한 정보가 담겨있고, 또 다른 슬레이브 데이터베이스는 그 동안 내가 이사 다녔던 주소들에 대한 정보를 담고 있을 지도 모른다. 이런 식의 관계형 데이터베이스를 가리켜 ‘1대 다수의 관계’라 부른다. 한 개인이 다수의 위반 사실, 또는 과거 주소를 가질 수는 있어도, 하나의 위반 사실이 여러 명의 운전자를 동반할 수는 없기 때문이다.

그 밖에도 1대 1, 다수 대 다수 관계도 물론 존재한다. 제품을 구매하는 고객들 중 특정 고객들에게 할인 혜택(면세, 도매가 등)을 제공하는 세일즈 데이터베이스는 할인률 데이터베이스가 필요할 것이다. 이러한 데이터베이스는 고객 데이터베이스와 1:1의 관계를 갖는다. 반대로 한 고객이 다양한 상품을 구매할 수 있고, 하나의 상품이 다수의 고객에게 구매될 수 있는 상황이라면 다수 대 다수의 관계가 형성 되었다고 할 것이다.

관계형 데이터베이스 마스터와 슬레이브

관계형 데이터베이스는 어떨 때 필요할까?
관계형 데이터베이스를 만드는 이유는 다양하다. 가장 대표적인 것은 모든 스프레드시트에서 똑같은 데이터를 반복하지 않기 위해서일 것이다. 예를 들어 마스터 데이터베이스에 있는 모든 운전자의 이름과 생년월일 정보를 모든 하위 슬레이브 데이터베이스에 그대로 다시 입력하는 것은 불필요하고, 시간만 소요되며, 자원을 낭비하는 행위일 것이다.

설령 정보를 복사해서 붙여 넣는다 해도 여전히 쓸 데 없이 디스크 공간과 메모리를 잡아먹어 컴퓨터를 느리게 만드는 주범이 된다. 또한 이름, 생년월일 정보 등은 중복될 수 있다. 예컨대 전국에 수천 명의 잭 테일러, 존 스미스가 있을 텐데 이들 중 생년월일이 겹치는 이들이 한두 명쯤은 반드시 있을 것이기 때문이다. 반대로, 이러한 정보 없이 면허 번호만을 사용한다면 운전자의 신상을 파악할 수 없기 때문에 결국 개인 정보와 면허 번호 정보 모두를 사용해야만 정확한 신원 파악이 가능해 진다.

관계형 데이터베이스를 사용하는 가장 중요한 이유는 특정 정보만을 추출하여 리포트를 생성할 수 있는 쿼리와 필터를 생성하기 위해서다. 예를 들어 상사가 플로리다 주 산타 로사 카운티 주민들 중 교통 법규를 위반하여 300달러 이상의 벌금을 납부한 기록이 있는 모든 이들의 신상을 담은 리포트를 요구했다고 하자. 또는, 12월에 운전 면허 갱신을 앞둔 팬사콜라 운전자 수가 궁금할 수도 있다. 관계형 데이터베이스의 도움이 없다면 서너 개의 스프레드시트를 오가며 모든 정보를 일일이 복사, 붙여넣기 해야 할 것이다. 나중에 가서 상사가 “아니, 산타 로사 카운티가 아니라 사라소타 카운티를 말 한 거였는데,” 라고 말을 바꾸지 않기만을 바라면서 말이다.

 교통 벌금 관련 관계형 데이터베이스

관계형 데이터베이스 만들기
그럼 이제, 다수의 테이블 및 스프레드시트를 포함한 관계형 데이터베이스를 만들어 보자.

마스터 테이블 생성하기
1. 위의 첫 번째 스크린샷에 나온 것과 같은 필드를 사용하여(“기본 플랫 파일 스프레드시트 데이터베이스") 12-15 레코드를 자신의 데이터로 채운다(우리가 제공한 것을 사용해도 좋다). 이들 필드는 면허 번호, 이름, 주소, 시, 도, 우편번호, 생년월일, 면허증 만료일, 그리고 성별 정보를 포함하고 있다. 

2. 이 작업이 끝나면, 헤더를 포함한 전체 테이블을 하이라이트 한다. 즉, A1에서 I15(A1:I15)까지 전부다. 커서를 테이블 범위 안에 놓고 CTRL+A를 누르면 손쉽게 전체 선택이 가능하다. 

3. 탭을 선택 후 삽입 메뉴에서 ‘표’를 클릭한다.. 

4. 열에 하이라이트 되어 있다면(반드시 하이라이트가 되어 있어야 한다), ‘표 만들기 대화상자 속 ‘표에 사용할 데이터를 지정하십시오’에 열 주소가 나타날 것이다. ‘머리글 포함’ 박스를 찾아 체크하고, 확인을 클릭한다. 
 



5. 이 대화 상자를 닫고 난 후 반드시 ‘표 이름’ 필드 박스(좌측 상단 코너에 위치)에 이름을 기록해 두길 바란다. 표 이름 필드 박스는 ‘속성’ 그룹에서 찾을 수 있다. MASTER 를 입력하고 엔터를 누른다. 

6. 이렇게 하면 이제 표에 머리글에 아래 화살표가 생겼을 것이다. 이들 중 하나를 클릭하면 대화 메뉴 상자가 뜬다. 이 상자에는 필터 기능을 포함한 분류 옵션이 있는데, 이 기능을 사용하면 필터에 따라 특정 정보만을 걸러서 보여준다. 예를 들어 ‘모두 선택’으로 되어 있는 것을 해제하고 ‘Miami’와 ‘Destin’ 에만 체크를 하면 이 두 도시를 제외한 다른 모든 도시 정보를 필터를 통해 걸러 준다. 

주의: 다른 기록들이 모두 사라지더라도 놀라지 말 것. 이들은 완전히 사라진 것이 아니라, 잠깐 감춰진 것뿐이다. 도시 필드 옆의 아래 화살표를 클릭하고, ‘모두 선택’ 버튼을 다시 누르면 다시 사라진 기록들이 나타날 것이다. 

팁: 필터 메뉴에서 텍스트 필터 옵션을 클릭하면 팝업 리스트에서 필터링 옵션을 선택할 수 있다(체크박스 리스트에서 필드를 매칭하지 않아도 된다). 같음, 같지 않음, 시작문자, 끝 문자, 포함, 포함하지 않음, 사용자 지정 필터 등의 옵션이 존재한다. 

7. 스프레드시트 하단의 탭(sheet 1이라고 되어 있다)을 ‘MasterDB’ 등으로 바꾸는 것도 잊지 말자. 탭을 더블 클릭 하면 더 빠르게 이름을 바꿀 수 있다.
 



2018.03.22

“대규모 데이터 정리의 시작” 엑셀에서 관계형 데이터베이스 만들기

JD Sartain | PCWorld

엑셀은 놀라울 정도의 데이터베이스 파워를 지니고 있다. 관계형 데이터베이스를 생성하기 위해서는 하위 테이블과 연결되는 마스터 테이블부터 시작해야 한다. 마스터 테이블은 슬레이브(Slave), 차일드(Child), 그리고 디테일(Detail) 테이블로 불린다.

보다 구체적인 내용을 알아보기 전에, 관계형 데이터베이스 생성을 위한 각 단계별 설명과 연습을 해 볼 수 있는 통합문서를 준비했다. 화살표를 클릭하거나, 아래의 링크를 누르면 다운로드 받을 수 있다.

예제 통합 문서 다운로드 하기 

관계형 데이터베이스란?
엑셀은 구조부터 데이터베이스 생성에 적합하다. 데이터베이스란 기본적으로 연결된 아이템들(스프레드시트)의 조합인데 이들이 연합하여 다수의 레코드(테이블) 그룹 내에 단일 레코드(열)를 생성하게 된다.

스프레트시트 한 장이라도 그 자체로 데이터베이스라고 부를 수는 있지만, 관계형 데이터베이스는 아니다. 관계형 데이터베이스는 마스터 스프레드시트 테이블과 그 하위 슬레이브 테이블 또는 슬레이브 스프레드시트 전부를 포괄하는 개념이다.

예를 들어 운전면허가 차량국 컴퓨터에 저장된 플랫 파일 데이터베이스 상의 하나의 레코드라고 해 보자. 면허증에는 나의 이름, 성별, 신장, 체중, 눈동자 색, 머리 색, 주소, 생년월일, 면허증 발급 및 만료일 등의 정보가 담겨 있다.

그렇지만 이름, 주소, 성별이 같은 사람은 여러 명이 있을 수 있기 때문에, 면허증에는 항상 면허 번호가 함께 기재 돼 있다. 한 사람당 하나씩만 부여 되는 이 고유 번호는 키 필드(Key Field)라 불리며 플랫 파일 데이터베이스를 다른 관련 데이터베이스 들(관계형 데이터베이스 들)과 연결시키는 역할을 한다.

기본적인 플랫 파일 스프레드시트 데이터베이스

(이 예시에서 사용된) 이러한 마스터 데이터베이스(MasterDB 탭)는 운전 면허증에 담긴 모든 정보를 포함하고 있다. 슬레이브, 차일드, 그리고 디테일 데이터베이스 등으로 불리는 하나 이상의 관계형 데이터베이스들은 운전자 개인에게 관련된 부가적인 정보를 담고 있다(그리고 이러한 정보를 식별하는 것은 키 필드인 면허 번호이다). 예를 들어, 한 슬레이브 데이터베이스에는 그 동안 저질렀던 모든 교통법규 위반 사항에 관한 정보가 담겨있고, 또 다른 슬레이브 데이터베이스는 그 동안 내가 이사 다녔던 주소들에 대한 정보를 담고 있을 지도 모른다. 이런 식의 관계형 데이터베이스를 가리켜 ‘1대 다수의 관계’라 부른다. 한 개인이 다수의 위반 사실, 또는 과거 주소를 가질 수는 있어도, 하나의 위반 사실이 여러 명의 운전자를 동반할 수는 없기 때문이다.

그 밖에도 1대 1, 다수 대 다수 관계도 물론 존재한다. 제품을 구매하는 고객들 중 특정 고객들에게 할인 혜택(면세, 도매가 등)을 제공하는 세일즈 데이터베이스는 할인률 데이터베이스가 필요할 것이다. 이러한 데이터베이스는 고객 데이터베이스와 1:1의 관계를 갖는다. 반대로 한 고객이 다양한 상품을 구매할 수 있고, 하나의 상품이 다수의 고객에게 구매될 수 있는 상황이라면 다수 대 다수의 관계가 형성 되었다고 할 것이다.

관계형 데이터베이스 마스터와 슬레이브

관계형 데이터베이스는 어떨 때 필요할까?
관계형 데이터베이스를 만드는 이유는 다양하다. 가장 대표적인 것은 모든 스프레드시트에서 똑같은 데이터를 반복하지 않기 위해서일 것이다. 예를 들어 마스터 데이터베이스에 있는 모든 운전자의 이름과 생년월일 정보를 모든 하위 슬레이브 데이터베이스에 그대로 다시 입력하는 것은 불필요하고, 시간만 소요되며, 자원을 낭비하는 행위일 것이다.

설령 정보를 복사해서 붙여 넣는다 해도 여전히 쓸 데 없이 디스크 공간과 메모리를 잡아먹어 컴퓨터를 느리게 만드는 주범이 된다. 또한 이름, 생년월일 정보 등은 중복될 수 있다. 예컨대 전국에 수천 명의 잭 테일러, 존 스미스가 있을 텐데 이들 중 생년월일이 겹치는 이들이 한두 명쯤은 반드시 있을 것이기 때문이다. 반대로, 이러한 정보 없이 면허 번호만을 사용한다면 운전자의 신상을 파악할 수 없기 때문에 결국 개인 정보와 면허 번호 정보 모두를 사용해야만 정확한 신원 파악이 가능해 진다.

관계형 데이터베이스를 사용하는 가장 중요한 이유는 특정 정보만을 추출하여 리포트를 생성할 수 있는 쿼리와 필터를 생성하기 위해서다. 예를 들어 상사가 플로리다 주 산타 로사 카운티 주민들 중 교통 법규를 위반하여 300달러 이상의 벌금을 납부한 기록이 있는 모든 이들의 신상을 담은 리포트를 요구했다고 하자. 또는, 12월에 운전 면허 갱신을 앞둔 팬사콜라 운전자 수가 궁금할 수도 있다. 관계형 데이터베이스의 도움이 없다면 서너 개의 스프레드시트를 오가며 모든 정보를 일일이 복사, 붙여넣기 해야 할 것이다. 나중에 가서 상사가 “아니, 산타 로사 카운티가 아니라 사라소타 카운티를 말 한 거였는데,” 라고 말을 바꾸지 않기만을 바라면서 말이다.

 교통 벌금 관련 관계형 데이터베이스

관계형 데이터베이스 만들기
그럼 이제, 다수의 테이블 및 스프레드시트를 포함한 관계형 데이터베이스를 만들어 보자.

마스터 테이블 생성하기
1. 위의 첫 번째 스크린샷에 나온 것과 같은 필드를 사용하여(“기본 플랫 파일 스프레드시트 데이터베이스") 12-15 레코드를 자신의 데이터로 채운다(우리가 제공한 것을 사용해도 좋다). 이들 필드는 면허 번호, 이름, 주소, 시, 도, 우편번호, 생년월일, 면허증 만료일, 그리고 성별 정보를 포함하고 있다. 

2. 이 작업이 끝나면, 헤더를 포함한 전체 테이블을 하이라이트 한다. 즉, A1에서 I15(A1:I15)까지 전부다. 커서를 테이블 범위 안에 놓고 CTRL+A를 누르면 손쉽게 전체 선택이 가능하다. 

3. 탭을 선택 후 삽입 메뉴에서 ‘표’를 클릭한다.. 

4. 열에 하이라이트 되어 있다면(반드시 하이라이트가 되어 있어야 한다), ‘표 만들기 대화상자 속 ‘표에 사용할 데이터를 지정하십시오’에 열 주소가 나타날 것이다. ‘머리글 포함’ 박스를 찾아 체크하고, 확인을 클릭한다. 
 



5. 이 대화 상자를 닫고 난 후 반드시 ‘표 이름’ 필드 박스(좌측 상단 코너에 위치)에 이름을 기록해 두길 바란다. 표 이름 필드 박스는 ‘속성’ 그룹에서 찾을 수 있다. MASTER 를 입력하고 엔터를 누른다. 

6. 이렇게 하면 이제 표에 머리글에 아래 화살표가 생겼을 것이다. 이들 중 하나를 클릭하면 대화 메뉴 상자가 뜬다. 이 상자에는 필터 기능을 포함한 분류 옵션이 있는데, 이 기능을 사용하면 필터에 따라 특정 정보만을 걸러서 보여준다. 예를 들어 ‘모두 선택’으로 되어 있는 것을 해제하고 ‘Miami’와 ‘Destin’ 에만 체크를 하면 이 두 도시를 제외한 다른 모든 도시 정보를 필터를 통해 걸러 준다. 

주의: 다른 기록들이 모두 사라지더라도 놀라지 말 것. 이들은 완전히 사라진 것이 아니라, 잠깐 감춰진 것뿐이다. 도시 필드 옆의 아래 화살표를 클릭하고, ‘모두 선택’ 버튼을 다시 누르면 다시 사라진 기록들이 나타날 것이다. 

팁: 필터 메뉴에서 텍스트 필터 옵션을 클릭하면 팝업 리스트에서 필터링 옵션을 선택할 수 있다(체크박스 리스트에서 필드를 매칭하지 않아도 된다). 같음, 같지 않음, 시작문자, 끝 문자, 포함, 포함하지 않음, 사용자 지정 필터 등의 옵션이 존재한다. 

7. 스프레드시트 하단의 탭(sheet 1이라고 되어 있다)을 ‘MasterDB’ 등으로 바꾸는 것도 잊지 말자. 탭을 더블 클릭 하면 더 빠르게 이름을 바꿀 수 있다.
 



X