[DBMS] 데이터베이스 모델링

류재성

Nov 09, 2024

Contents

1. 데이터베이스 모델링 개념 2. 모델링 예제 3. SQL Server로 모델링 실습 4. 기존 테이블의 관계도 만들기

1. 데이터베이스 모델링 개념

💡

데이터베이스 모델링은 현 세계에서 사용되는 작업이나 사물들을 DBMS의 데이터베이스 개체로 옮기기 위한 과정이다. 쉽게 얘기하면 현실에서 쓰이는 것을 테이블로 변경하기 위한 작업이라고 생각하면 된다.

그림과 같이 현실 세계에서의 고객, 물건, 직원 등은 데이터베이스에서 각각의 테이블이라는 개체로 변환된다.

💡

주의할 점은 현실 세계의 실체가 없는 ‘물건을 산다’ 라는 행위도 테이블로 변환된다는 점이다.

2. 모델링 예제

2.1 쇼핑몰 방문 내역

고객 방문 테이블

고객 이름	출생년도	주소	연락처	구매한 물건	단가	수량
이승기	1987	서울	011-111-1111	ㅤ	ㅤ	ㅤ
김범수	1979	경남	011-222-2222	운동화	30	2
김범수	1979	경남	011-222-2222	노트북	1000	1
김경호	1971	전남	019-333-3333	ㅤ	ㅤ	ㅤ
조용필	1950	경기	011-444-4444	모니터	200	1
바비킴	1973	서울	010-000-0000	모니터	200	5
윤종신	1969	경남	없음	ㅤ	ㅤ	ㅤ
김범수	1979	경남	011-222-2222	청바지	50	3
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
바비킴	1973	서울	010-000-0000	메모리	80	10
성시경	1979	경남	없음	책	15	5
은지원	1978	경북	011-888-8888	책	15	2
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
은지원	1978	경북	011-888-8888	청바지	50	1
바비킴	1973	서울	010-000-0000	운동화	30	2
은지원	1978	경북	011-888-8888	ㅤ	ㅤ	ㅤ
은지원	1978	경북	011-888-8888	책	15	1
바비킴	1973	서울	010-000-0000	운동화	30	2
조관우	1965	경기	018-999-9999	ㅤ	ㅤ	ㅤ

고객의 방문은 위의 표와 같이 기록된다. 고객은 여러번 방문할 수도 있고, 아무것도 사지 않고 갈 수도 있다.

2.2 방문기록 정렬

표에서 구매한 이력이 없는 행을 위쪽으로 정렬해본다.

고객 이름	출생년도	주소	연락처	구매한 물건	단가	수량
이승기	1987	서울	011-111-1111	ㅤ	ㅤ	ㅤ
김경호	1971	전남	019-333-3333	ㅤ	ㅤ	ㅤ
윤종신	1969	경남	없음	ㅤ	ㅤ	ㅤ
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
은지원	1978	경북	011-888-8888	ㅤ	ㅤ	ㅤ
조관우	1965	경기	018-999-9999	ㅤ	ㅤ	ㅤ
김범수	1979	경남	011-222-2222	운동화	30	2
김범수	1979	경남	011-222-2222	노트북	1000	1
조용필	1950	경기	011-444-4444	모니터	200	1
바비킴	1973	서울	010-000-0000	모니터	200	5
김범수	1979	경남	011-222-2222	청바지	50	3
바비킴	1973	서울	010-000-0000	메모리	80	10
성시경	1979	경남	없음	책	15	5
은지원	1978	경북	011-888-8888	책	15	2
은지원	1978	경북	011-888-8888	청바지	50	1
바비킴	1973	서울	010-000-0000	운동화	30	2
은지원	1978	경북	011-888-8888	책	15	1
바비킴	1973	서울	010-000-0000	운동화	30	2

이렇게 정렬하면 전체 테이블이 L 자 모양의 테이블이 되었다. 이것을 L자형 테이블이라고 부른다.

L자형 테이블의 문제는 공간 낭비에 있다.

2.3 테이블 분리

L자형 테이블을 빈 칸이 있는 곳과 없는 곳으로 분리해보자. 그러면 다음 표와 같이 고객 방문 테이블이 고객 테이블과 구매 테이블로 분리가 된다.

고객 테이블

고객 이름	출생년도	주소	연락처
이승기	1987	서울	011-111-1111
김경호	1971	전남	019-333-3333
윤종신	1969	경남	없음
임재범	1963	서울	016-666-6666
임재범	1963	서울	016-666-6666
은지원	1978	경북	011-888-8888
조관우	1965	경기	018-999-9999
김범수	1979	경남	011-222-2222
김범수	1979	경남	011-222-2222
조용필	1950	경기	011-444-4444
바비킴	1973	서울	010-000-0000
김범수	1979	경남	011-222-2222
바비킴	1973	서울	010-000-0000
성시경	1979	경남	없음
은지원	1978	경북	011-888-8888
은지원	1978	경북	011-888-8888
바비킴	1973	서울	010-000-0000
은지원	1978	경북	011-888-8888
바비킴	1973	서울	010-000-0000

구매 테이블

구매한 물건	단가	수량
운동화	30	2
노트북	1000	1
모니터	200	1
모니터	200	5
청바지	50	3
메모리	80	10
책	15	5
책	15	2
청바지	50	1
운동화	30	2
책	15	1
운동화	30	2

테이블을 분리하면 빈 공간이 사라져 공간을 절약할 수 있다.

하지만 분리한 테이블은 2가지 문제가 있다. 하나는 고객의 정보가 중복되어 있다는 점이다. 다른 하나는 구매 테이블만 보면 누가 제품을 구매했는지 알 수가 없다. 이 두가지 문제를 해결해야 한다.

2.4 고객 테이블 중복 제거 (기본키 설정)

고객 테이블

고객 이름(PK)	출생년도	주소	연락처
이승기	1987	서울	011-111-1111
김경호	1971	전남	019-333-3333
윤종신	1969	경남	없음
임재범	1963	서울	016-666-6666
은지원	1978	경북	011-888-8888
조관우	1965	경기	018-999-9999
김범수	1979	경남	011-222-2222
조용필	1950	경기	011-444-4444
바비킴	1973	서울	010-000-0000
성시경	1979	경남	없음

고객 테이블의 중복을 제거했다. 고객 테이블에서 고객 이름은 기본키라고 한다.

💡

기본키(Primary Key)는 테이블에서 각 행을 고유하게 식별하는데 사용되는 컬럼이다. 기본키는 각 행을 식별하는 값이기 때문에 반드시 존재해야 한다.

2.5 구매 테이블 고객 이름 추가(외래키 설정)

고객 테이블과 구매 테이블을 연관시키기 위한 식별자가 필요하다. 두 테이블은 고객명을 통해 연결할 수 있다. 구매 테이블과 고객 테이블을 연결하기 위해 구매 테이블에 고객명을 넣는다.

구매 테이블

고객 이름(FK)	구매한 물건	단가	수량
김범수	운동화	30	2
김범수	노트북	1000	1
조용필	모니터	200	1
바비킴	모니터	200	5
김범수	청바지	50	3
바비킴	메모리	80	10
성시경	책	15	5
은지원	책	15	2
은지원	청바지	50	1
바비킴	운동화	30	2
은지원	책	15	1
바비킴	운동화	30	2

구매 테이블에 고객 이름을 넣어 누가 어떤 제품을 구매했는지 알 수 있게 되었다.

💡

구매 테이블은 고객 테이블처럼 중복되었다고 행을 삭제하면 안된다. 고객 테이블에서 고객이름은 각 행을 구분하는 기본키였지만 구매 테이블에서는 고객 테이블과 연관짓기 위한 컬럼이기 때문에 각 행을 식별하는 컬럼이 아니기 때문이다. 이런 경우 고객 이름은 외래키(Foreign Key) 라고 부른다. 외래키는 다른 테이블의 기본키를 참조해서 두 테이블간의 관계를 형성할 때 사용한다.

2.5 테이블간 관계(Relation)

💡

고객 테이블과 구매 테이블은 밀접한 관련이 있는 테이블이다. 즉, 그매 테이블만으로는 고객에세 배송할 수가 없다. 고객의 주소와 연락처는 고객 테이블에 있기 때문이다. 따라서 두 테이블의 업무적인 연관성을 맺어 줘야 한다. 이를 관계(Relation) 이라고 한다.

두 테이블 중엣 부모 테이블과 자식 테이블을 결정해보자. 부모와 자식을 구분하는 방법 중에서 주(Master)가 되는 쪽은 부모, 상세(Detail)가 되는 쪽을 자식으로 설정하는 방법이 있다.

그렇다면 고객과 구매 내역 중 어느 것이 주가 되는가?

“고객은 물건을 소유한다.” 또는 “물건은 고객을 소유한다.”

어느 것이 더 자연스러운가? 당연히 전자가 훨씬 자연스러운 표현이다. 그러므로 고객 테이블이 부모가 되고 구매 테이블이 자식 테이블이 된다.

또, 기준이 하나인 것과 하나의 기준이 여러 개의 기록을 남기는 것으로 나누어 부모 테이블과 자식 테이블을 구분할 수 있다.

“한 명의 고객은 여러 개의 구매 내역을 가질 수 있다.” , “하나의 구매 내역은 여러 고객이 가질 수 있다.“

역시 전자가 자연스럽다. 이런 관계를 1대 N 관계라고 한다.

💡

부모 테이블과 자식 테이블의 관계를 맺어주려면 기본키와 외래키를 설정한다. 1대N 의 관계에서 기본키는 1 , 외래키는 N 인 테이블로 설정한다. 고객 테이블에서 고객 이름을 찾으면 구매 테이블에서 구매 이력을 찾을 수 있다.

2.6 제약 조건 설정

이렇게 테이블간 관계가 설정된 후 제약 조건이라는 관계가 설정된다.

예를 들어 ‘존밴’ 이라는 사람이 모니터를 구매해야 한다고 가정해보자. 그러면 구매 테이블에는

‘존밴/모니터/200/1’ 이라는 행이 추가되어야 한다. 그런데 현재 ‘존밴’ 이라는 정보가 고객 테이블에 없기 때문에 구매 테이블에 행이 추가가 되면 안된다. 행이 추가되기 위해선 회원가입이 우선되어야 한다.

이처럼 테이블간 데이터의 무결성을 지키기 위해 사용되는 것이 제약 조건이다. 기본키(PK)와 외래키(FK) 도 제약조건의 일종이다.

3. SQL Server로 모델링 실습

데이터베이스 - 마우스 우클릭 - 새 데이터베이스를 선택한다.

ModelDB 라는 데이터베이스를 생성한다.

데이터베이스 - ModelDB - 마우스 우클릭 - 새 데이터베이스 다이어그램을 선택한다.

“지원 개체를 만들겠습니까?” 라는 메세지 박스가 뜨면 예를 선택한다.

그 다음 뜨는 테이블 추가 창은 닫기를 선택한다.

그리고 뜨는 새 쿼리창에서 마우스 우클릭 - 새 테이블을 선택한다.

테이블 명을 고객테이블로 설정한다.

테이블의 컬럼을 넣는다.

고객이름에 마우스 우클릭 - 기본키 설정을 선택한다.

동일하게 구매 테이블을 생성한다.

위의 사진처럼 구매 테이블의 고객 이름 컬럼을 선태 후 마우스 드래그로 고객 테이블의 고객이름 컬럼에 연결한다.

연결하면 사진과 같은 팝업이 뜬다. 테이블간 관계 이름을 설정 후 확인을 누른다.

관계가 형성되면 위의 사진과 같이 연결된다.

왼쪽 상단의 모두 저장을 누르면 데이터베이스 설정이 저장된다.

고객 테이블과 구매 테이블이 생성된 것을 확인할 수 있다.

4. 기존 테이블의 관계도 만들기

4.1 사용자 변경하기

[DBMS] SQL Server 샘플 데이터 설치 - {CODE-RYU};

DBMS

https://inblog.ai/coderyu1/dbms-sql-server-%EC%83%98%ED%94%8C-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%84%A4%EC%B9%98-33784?traffic_type=internal

샘플로 다운받은 AdventureWorks 를 사용해 다이어그램을 만들어보자.

만들기 전 소유자를 변경해야 한다.

데이터베이스 - AdventureWorks2022 마우스 우클릭 - 속성을 선택한다.

데이터베이스 속성 - 파일 - 소유자 더보기(…) 클릭 - 찾아보기 를 선택한다.

개체 찾아보기에서 소유자를 ‘NT AUTHORITY\SYSTEM’ 을 선택한다.

소유자가 바꾼 후 확인을 누른다.

💡

NT AUTHORITY\SYSTEM 는 윈도우의 ROOT 계정과 유사하며, 시스템 내부 작업 및 서비스를 수행할 수 있는 권한을 가진다.

4.2 다이어그램 만들기

데이터베이스 - AdventureWorks2022 마우스 우클릭 - 새 데이터베이스 다이어그램을 선택한다.

기존 테이블들이 뜬다. 이 중 몇개를 선택한 후 추가를 누른다.

그럼 자동으로 테이블의 관계도를 볼 수 있다.

DBMS

[DBMS] 데이터베이스 모델링

류재성

Nov 09, 2024

Contents

1. 데이터베이스 모델링 개념 2. 모델링 예제 3. SQL Server로 모델링 실습 4. 기존 테이블의 관계도 만들기

1. 데이터베이스 모델링 개념

💡

그림과 같이 현실 세계에서의 고객, 물건, 직원 등은 데이터베이스에서 각각의 테이블이라는 개체로 변환된다.

💡

주의할 점은 현실 세계의 실체가 없는 ‘물건을 산다’ 라는 행위도 테이블로 변환된다는 점이다.

2. 모델링 예제

2.1 쇼핑몰 방문 내역

고객 방문 테이블

고객 이름	출생년도	주소	연락처	구매한 물건	단가	수량
이승기	1987	서울	011-111-1111	ㅤ	ㅤ	ㅤ
김범수	1979	경남	011-222-2222	운동화	30	2
김범수	1979	경남	011-222-2222	노트북	1000	1
김경호	1971	전남	019-333-3333	ㅤ	ㅤ	ㅤ
조용필	1950	경기	011-444-4444	모니터	200	1
바비킴	1973	서울	010-000-0000	모니터	200	5
윤종신	1969	경남	없음	ㅤ	ㅤ	ㅤ
김범수	1979	경남	011-222-2222	청바지	50	3
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
바비킴	1973	서울	010-000-0000	메모리	80	10
성시경	1979	경남	없음	책	15	5
은지원	1978	경북	011-888-8888	책	15	2
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
은지원	1978	경북	011-888-8888	청바지	50	1
바비킴	1973	서울	010-000-0000	운동화	30	2
은지원	1978	경북	011-888-8888	ㅤ	ㅤ	ㅤ
은지원	1978	경북	011-888-8888	책	15	1
바비킴	1973	서울	010-000-0000	운동화	30	2
조관우	1965	경기	018-999-9999	ㅤ	ㅤ	ㅤ

고객의 방문은 위의 표와 같이 기록된다. 고객은 여러번 방문할 수도 있고, 아무것도 사지 않고 갈 수도 있다.

2.2 방문기록 정렬

표에서 구매한 이력이 없는 행을 위쪽으로 정렬해본다.

고객 이름	출생년도	주소	연락처	구매한 물건	단가	수량
이승기	1987	서울	011-111-1111	ㅤ	ㅤ	ㅤ
김경호	1971	전남	019-333-3333	ㅤ	ㅤ	ㅤ
윤종신	1969	경남	없음	ㅤ	ㅤ	ㅤ
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
임재범	1963	서울	016-666-6666	ㅤ	ㅤ	ㅤ
은지원	1978	경북	011-888-8888	ㅤ	ㅤ	ㅤ
조관우	1965	경기	018-999-9999	ㅤ	ㅤ	ㅤ
김범수	1979	경남	011-222-2222	운동화	30	2
김범수	1979	경남	011-222-2222	노트북	1000	1
조용필	1950	경기	011-444-4444	모니터	200	1
바비킴	1973	서울	010-000-0000	모니터	200	5
김범수	1979	경남	011-222-2222	청바지	50	3
바비킴	1973	서울	010-000-0000	메모리	80	10
성시경	1979	경남	없음	책	15	5
은지원	1978	경북	011-888-8888	책	15	2
은지원	1978	경북	011-888-8888	청바지	50	1
바비킴	1973	서울	010-000-0000	운동화	30	2
은지원	1978	경북	011-888-8888	책	15	1
바비킴	1973	서울	010-000-0000	운동화	30	2

이렇게 정렬하면 전체 테이블이 L 자 모양의 테이블이 되었다. 이것을 L자형 테이블이라고 부른다.

L자형 테이블의 문제는 공간 낭비에 있다.

2.3 테이블 분리

L자형 테이블을 빈 칸이 있는 곳과 없는 곳으로 분리해보자. 그러면 다음 표와 같이 고객 방문 테이블이 고객 테이블과 구매 테이블로 분리가 된다.

고객 테이블

고객 이름	출생년도	주소	연락처
이승기	1987	서울	011-111-1111
김경호	1971	전남	019-333-3333
윤종신	1969	경남	없음
임재범	1963	서울	016-666-6666
임재범	1963	서울	016-666-6666
은지원	1978	경북	011-888-8888
조관우	1965	경기	018-999-9999
김범수	1979	경남	011-222-2222
김범수	1979	경남	011-222-2222
조용필	1950	경기	011-444-4444
바비킴	1973	서울	010-000-0000
김범수	1979	경남	011-222-2222
바비킴	1973	서울	010-000-0000
성시경	1979	경남	없음
은지원	1978	경북	011-888-8888
은지원	1978	경북	011-888-8888
바비킴	1973	서울	010-000-0000
은지원	1978	경북	011-888-8888
바비킴	1973	서울	010-000-0000

구매 테이블

구매한 물건	단가	수량
운동화	30	2
노트북	1000	1
모니터	200	1
모니터	200	5
청바지	50	3
메모리	80	10
책	15	5
책	15	2
청바지	50	1
운동화	30	2
책	15	1
운동화	30	2

테이블을 분리하면 빈 공간이 사라져 공간을 절약할 수 있다.

2.4 고객 테이블 중복 제거 (기본키 설정)

고객 테이블

고객 이름(PK)	출생년도	주소	연락처
이승기	1987	서울	011-111-1111
김경호	1971	전남	019-333-3333
윤종신	1969	경남	없음
임재범	1963	서울	016-666-6666
은지원	1978	경북	011-888-8888
조관우	1965	경기	018-999-9999
김범수	1979	경남	011-222-2222
조용필	1950	경기	011-444-4444
바비킴	1973	서울	010-000-0000
성시경	1979	경남	없음

고객 테이블의 중복을 제거했다. 고객 테이블에서 고객 이름은 기본키라고 한다.

💡

기본키(Primary Key)는 테이블에서 각 행을 고유하게 식별하는데 사용되는 컬럼이다. 기본키는 각 행을 식별하는 값이기 때문에 반드시 존재해야 한다.

2.5 구매 테이블 고객 이름 추가(외래키 설정)

구매 테이블

고객 이름(FK)	구매한 물건	단가	수량
김범수	운동화	30	2
김범수	노트북	1000	1
조용필	모니터	200	1
바비킴	모니터	200	5
김범수	청바지	50	3
바비킴	메모리	80	10
성시경	책	15	5
은지원	책	15	2
은지원	청바지	50	1
바비킴	운동화	30	2
은지원	책	15	1
바비킴	운동화	30	2

구매 테이블에 고객 이름을 넣어 누가 어떤 제품을 구매했는지 알 수 있게 되었다.

💡

2.5 테이블간 관계(Relation)

💡

그렇다면 고객과 구매 내역 중 어느 것이 주가 되는가?

“고객은 물건을 소유한다.” 또는 “물건은 고객을 소유한다.”

어느 것이 더 자연스러운가? 당연히 전자가 훨씬 자연스러운 표현이다. 그러므로 고객 테이블이 부모가 되고 구매 테이블이 자식 테이블이 된다.

또, 기준이 하나인 것과 하나의 기준이 여러 개의 기록을 남기는 것으로 나누어 부모 테이블과 자식 테이블을 구분할 수 있다.

“한 명의 고객은 여러 개의 구매 내역을 가질 수 있다.” , “하나의 구매 내역은 여러 고객이 가질 수 있다.“

역시 전자가 자연스럽다. 이런 관계를 1대 N 관계라고 한다.

💡

2.6 제약 조건 설정

이렇게 테이블간 관계가 설정된 후 제약 조건이라는 관계가 설정된다.

예를 들어 ‘존밴’ 이라는 사람이 모니터를 구매해야 한다고 가정해보자. 그러면 구매 테이블에는

이처럼 테이블간 데이터의 무결성을 지키기 위해 사용되는 것이 제약 조건이다. 기본키(PK)와 외래키(FK) 도 제약조건의 일종이다.

3. SQL Server로 모델링 실습

데이터베이스 - 마우스 우클릭 - 새 데이터베이스를 선택한다.

ModelDB 라는 데이터베이스를 생성한다.

데이터베이스 - ModelDB - 마우스 우클릭 - 새 데이터베이스 다이어그램을 선택한다.

“지원 개체를 만들겠습니까?” 라는 메세지 박스가 뜨면 예를 선택한다.

그 다음 뜨는 테이블 추가 창은 닫기를 선택한다.

그리고 뜨는 새 쿼리창에서 마우스 우클릭 - 새 테이블을 선택한다.

테이블 명을 고객테이블로 설정한다.

테이블의 컬럼을 넣는다.

고객이름에 마우스 우클릭 - 기본키 설정을 선택한다.

동일하게 구매 테이블을 생성한다.

위의 사진처럼 구매 테이블의 고객 이름 컬럼을 선태 후 마우스 드래그로 고객 테이블의 고객이름 컬럼에 연결한다.

연결하면 사진과 같은 팝업이 뜬다. 테이블간 관계 이름을 설정 후 확인을 누른다.

관계가 형성되면 위의 사진과 같이 연결된다.

왼쪽 상단의 모두 저장을 누르면 데이터베이스 설정이 저장된다.

고객 테이블과 구매 테이블이 생성된 것을 확인할 수 있다.

4. 기존 테이블의 관계도 만들기

4.1 사용자 변경하기

[DBMS] SQL Server 샘플 데이터 설치 - {CODE-RYU};

DBMS

https://inblog.ai/coderyu1/dbms-sql-server-%EC%83%98%ED%94%8C-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%84%A4%EC%B9%98-33784?traffic_type=internal

샘플로 다운받은 AdventureWorks 를 사용해 다이어그램을 만들어보자.

만들기 전 소유자를 변경해야 한다.

데이터베이스 - AdventureWorks2022 마우스 우클릭 - 속성을 선택한다.

데이터베이스 속성 - 파일 - 소유자 더보기(…) 클릭 - 찾아보기 를 선택한다.

개체 찾아보기에서 소유자를 ‘NT AUTHORITY\SYSTEM’ 을 선택한다.

소유자가 바꾼 후 확인을 누른다.

💡

NT AUTHORITY\SYSTEM 는 윈도우의 ROOT 계정과 유사하며, 시스템 내부 작업 및 서비스를 수행할 수 있는 권한을 가진다.

4.2 다이어그램 만들기

데이터베이스 - AdventureWorks2022 마우스 우클릭 - 새 데이터베이스 다이어그램을 선택한다.

기존 테이블들이 뜬다. 이 중 몇개를 선택한 후 추가를 누른다.

그럼 자동으로 테이블의 관계도를 볼 수 있다.