Achievement Goals
- 데이터 간의 다양한 관계를 알아보기
- 데이터 간 관계를 기술하는 언어(SQL) 익히기
- 합리적이고 효율적인 방법으로 데이터베이스를 구성하는 방법을 이해하기
- 데이터베이스에서 관련 정보를 찾기 위해 SQL 쿼리 작성하는 방법 알아보기
관계형 데이터베이스
구조화된 데이터는 하나의 테이블로 표현할 수 있다. 사전에 정의된 테이블을 relation 이라고도 부르기 때문에, 테이블을 사용하는 데이터베이스를 관계형 데이터베이스(Relational database)라고 한다.
관계형 데이터베이스를 학습하면서, 반드시 알고 있어야 하는 키워드들이 있다.
- 데이터(data): 각 항목에 저장되는 값
- 테이블(table; 또는 relation) : 사전에 정의된 열(column)의 데이터 타입대로 작성된 데이터가 행(row)으로 축적된다.
- 칼럼(column; 또는 field) : 테이블의 한 열(column)
- 레코드(record; 또는 tuple) : 테이블의 한 행(row)에 저장된 데이터
- 키(key) : 테이블의 각 레코드를 구분할 수 있는 값. 각 레코드마다 고유한 값을 가짐. 기본키(primary key)와 외래키(foreign key) 등이 있음.
관계 종류
테이블과 테이블 사이의 관계는 다음과 같다.
- 1:1 관계
- 1:N 관계
- N:N 관계
테이블 스스로 관계를 가질 수도 있다.
- self referencing 관계
1:1 관계
하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우다. 다음과 같이 User 테이블과 Phonebook 테이블이 있다고 가정해보자.
User 테이블은 user_id, name, phone_id를 갖고 있다. 이 중 phone_id는 foreign key(외래 키)로써, Phonebook 테이블의 phone_id와 연결되어 있다. Phonebook 테이블은 phone_id와 phone_number를 갖고 있다.
각 전화번호가 단 한 명의 유저와 연결되어 있고, 그 반대도 동일하다면, User 테이블과 Phonebook 테이블은 1:1 관계(One-to-one relationship)이다.
그러나 1:1 관계는 자주 사용하지 않는다. 1:1로 나타낼 수 있는 관계라면 User 테이블에 phone_id를 대신해 phone_number를 직접 저장하는 게 나을 수 있다.
1:N 관계
하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우이다. User 테이블과 Phonebook 테이블의 관계를 다음과 같이 가정해보자.
이 구조에서는 한 명의 유저가 여러 전화번호를 가질 수 있다. 그러나 여러 명의 유저가 하나의 전화번호를 가질 수는 없다. 이런 1:N(일대다) 관계는 관계형 데이터베이스에서 가장 많이 사용된다.
N:N 관계
여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우이다. N:N(다대다) 관계를 위해 스키마를 디자인 할 때에는 Join 테이블을 만들어 관리한다. 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있다.
아래와 같은 여행 상품을 관리하는 테이블이 있다고 가정해보자. 여러 개의 여행 상품이 있고, 여러 명의 고객이 있다. 고객 한 명은 여러 개의 여행 상품을 구매할 수 있고, 여행 상품 하나는 여러 명의 고객이 구매할 수 있다.
이렇게 Customer 테이블과 Package table이 따로 존재한다면, N:N(다대다) 관계를 어떻게 표현할 수 있을까? 다대다 관계는 두 개의 일대다 관계와 모양이 같다. 두 개의 테이블과 1:N(일대다) 관계를 형성하는 새로운 테이블로 N:N(다대다) 관계를 나타낼 수 있다.
이렇게 다대다 관계를 위한 테이블을 조인 테이블이라고 한다. N:N(다대다) 관계를 그림으로 나타내면 아래와 같다.
customer_package 테이블에서는 고객 한 명이 여러 개의 여행 상품을 가질 수 있고, 여행 상품 하나를 여러 명의 고객이 가질 수 있다.
customer_package 테이블은 customer_id와 package_id를 묶어주는 역할이다. 이 테이블을 통해 어떤 고객이 몇 개의 여행 상품을 구매했는지? 또는 어떤 여행 상품이 몇 명의 고객을 갖고 있는지? 등을 확인할 수 있다. 이렇게 조인 테이블을 생성하더라도, 조인 테이블을 위한 기본키(cp_id)는 반드시 있어야 한다.
- 만약 foreign key를 리스트 형식으로 관리하는 필드가 있다면, 어떤 문제가 발생할 수 있을까?
자기참조 관계(Self Referencing Relationship)
때로는 테이블 내에서도 관계가 필요하다. 예를 들어 추천인이 누구인지 파악하기 위해 사용할 수 있다.
아래와 같이 유저 테이블이 있다면, user_id는 기본키(primary key), name은 사용자의 이름, 그리고 recommend_id는 추천인 아이디이다.
User 테이블의 recommend_id는 User 테이블의 user_id와 연결되어 있다. 한 명의 유저(user_id)는 한 명의 추천인(recoomend_id)를 가질 수 있다. 그러나 여러 명이 한 명의 유저를 추천인으로 등록할 수 있다. 이 관계는 1:N(일대다) 관계와 유사하다고 볼 수 있다. 그러나 일반적으로 일대다 관계는 서로 다른 테이블의 관계를 나타낼 때 표현하는 방법이다.
Schema
스키마(schema) 는 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티(Entity) 간의 관계에 대한 설명이다. 즉 데이터베이스의 청사진과 같다.
먼저 이것에 대해 이해하기 전에 한 가지 상황을 떠올려 보자.
우리가 수강신청이라는 시스템을 하나 만든다고 생각하면 제일 먼저 뭐가 떠오를까?
라이브러리는 뭘 사용할지? 서버 언어를 뭘 사용할지? 이런 생각을 할 수도 있겠지만, 핵심은 어떤 데이터가 어떻게 표현되느냐에 관련된 것이다. 결국에 중요한 것은 데이터가 어떻게 표현되느냐가 제일 중요하다! 그렇다면 데이터를 표현하기 위해서는 뭐가 필요할까? 먼저 데이터가 정의 되어야 한다. 데이터를 정의하는 방법 그리고 데이터 간의 관계를 구성하는 방법을 떠올렸을 때 필요한 것이 바로 Schema다.
Schema 구성해보기
학교에서 사용되는 데이터베이스를 구축한다고 상상해보자. 만약 교사(Teachers), 수업(Classes) 그리고 학생(Students)과 같은 데이터를 저장해야 한다고 했을 때, 간단하게 테이블 형식으로 나타내자면 아래의 테이블같은 모습일 것이다.
이처럼 테이블에 담긴 정보를 표현할 때 쓰이는 용어 세 가지가 있는데, 아래 예시 테이블을 분석해 보면서 어떤 의미를 갖고 있는지 알아보자.
교사
| Teachers |
|---|
| Name |
| Department |
| Classes |
수업
| Classes |
|---|
| Name |
| Room Number |
| Teacher |
| Students |
학생
| Students |
|---|
| Name |
| Classes |
엔티티(Entity)
여기서 교사, 수업, 학생 각각의 테이블을 엔티티(Entity) 라고 부른다. 갑작스럽게 등장한 이 용어는... 그냥 객체같은 형식을 가진 고유한 정보의 단위를 테이블로 나타낸 것이라고 생각하는 게 좋겠다.
이 예시에서는 Teachers, Classes, Students 세 개의 엔티티가 있다.
필드(Field)
각 엔티티는 해당 엔티티의 특성을 설명하는 필드(Field) 가 존재하는데, 행렬이 있다면 필드는 열(column)에 해당된다. 예를 들어, 교사의 정보로 이름, 부서 그리고 맡고 있는 수업 목록 이있다면, 교사라는 엔티티 안에 엔티티의 특성을 설명하는 필드로 교사의 정보가 들어가는 것이다. => Teacher(Name, Department, Classes)
레코드(record)
레코드(record)는 테이블에 저장된 항목이다. 행렬이 있다면 행(row)에 해당된다. Teachers 테이블의 하나의 레코드(행)를 예로 들면, Music Theory, Brass Methods 수업을 담당하는 음대의 Brad 선생님을 위와 같이 표현할 수 있다.
- Entity : 엔티티는 데이터베이스에서 테이블로 표시할 수 있다.
- Field : 엔티티의 세부 데이터, 행렬의 열(column)
- Record : 테이블에 저장된 항목, 행렬의 행(row)
보통 학교에서는, 각 교사가 여러가지 수업을 진행할 수 있다. 이는 1:N(one-to-many, 일대 다) 라는 용어로 이 관계를 설명할 수 있다. 그렇다면 스키마에서 이 관계를 어떻게 정의할 수 있을까?
테이블로 표현해보자.
1:N
Teachers
| ID | Name | Department | Classes |
|---|---|---|---|
| 1 | Morgan | Psy | ?? |
| 2 | Nancy | CS | ?? |
| 3 | Kelly | CS | ?? |
Classes
| ID | Name | Room Num. | Teacher |
|---|---|---|---|
| 1 | CS 101 | 114 | ?? |
| 2 | CS 102 | 114 | ?? |
| 3 | CS 151 | 222 | ?? |
| 4 | CS 245 | 118 | ?? |
| 5 | CS 330 | 220 | ?? |
| 6 | CS 101 | 318 | ?? |
| 7 | PSY 201 | 318 | ?? |
특정 필드 외의 각 엔티티에는 ID 필드도 포함된다. 위의 Teachers와 Classes 사이 관계를 담기 위해 어떤 형태로 정보가 저장되어야 할까?
이 관계를 위해 가장 좋은 방법은, Teachers 테이블을 Classes 테이블에 저장하는 것이다.
- 첫 번째 방법 : Teachers 테이블에 Classes 컬럼의 내용 채우기
- 두 번째 방법 : Classes 테이블에 Teachers 컬럼의 내용 채우기
N:N
이번에는 Classes 테이블을 Students 테이블과 연결해보자.
각 수업에는 여러 명의 학생들로 구성되어 있고, 각 학생 또한 여러 개의 수업을 듣는다고 생각해보자. 이러한 형태의 관계를 N:N (many to many 다대 다) 라고 표현할 수 있다.
Classes
| ID | Name | TeacherID | Students |
|---|---|---|---|
| 1 | CS 101 | 2 | ?? |
| 2 | CS 102 | 2 | ?? |
| 3 | CS 151 | 3 | ?? |
| 4 | CS 245 | 3 | ?? |
| 5 | CS 330 | 3 | ?? |
| 6 | CS 101 | 1 | ?? |
| 7 | PSY 201 | 1 | ?? |
Students
| ID | Name | Classes |
|---|---|---|
| 1 | Jae | ?? |
| 2 | Rebekah | ?? |
| 3 | Brandon | ?? |
| 4 | Alice | ?? |
| 5 | Kiran | ?? |
| 6 | Miso | ?? |
| 7 | Mike | ?? |
위의 관계를 스키마에서 어떻게 정의할 수 있을까?
아래처럼 테이블을 작성해서 ClassID와 StudentID들이 교차하는 좌표들을 하나의 테이블에 저장하면 된다. 이와 같은 테이블을 Join 테이블이라고 부른다.
Classes/Students
| ClassID | StudentID |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 3 |
| 4 | 5 |
| ... | ... |
