Schema and Query Design

Objectives of this chapter
데이터 간의 다양한 관계를 알아봅니다.
데이터 간 관계를 기술하는 언어(SQL)를 익힙니다.
합리적이고, 효율적인 방법으로 데이터베이스를 구성하는 방법을 이해합니다.
데이터베이스에서 관련정보를 찾기 위해 SQL 쿼리를 작성하는 방법을 알아봅니다.

What is a schema?
스키마(schema)는 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티 간의 관계에 대한 설명입니다. 즉, 데이터베이스 청사진과 같습니다.
엔티티(Entity)는 고유한 정보의 단위입니다. 세 개의 엔티티 Teachers, Classes, Students가 있다. 엔티티는 데이터베이스에서 테이블로 표시할 수 있습니다.
각 엔티티에는 해당 엔티티의 특성을 설명하는 필드(Field)가 있습니다. 행렬이라면 열(column)에 해당되겠죠. 예를 들어, 교사에게는 이름, 부서, 그리고 맡고 있는 수업 목록이 있을 수 있습니다. 테이블에 저장된 모든 항목에는 해당 필드가 포함됩니다.
레코드(record)는 테이블에 저장된 항목입니다. 행렬에서의 행(row)이라고 볼 수도 있습니다. Teachers 테이블의 하나의 레코드(행)를 예로 들면, Music Theory, Brass Methods 수업을 가르치는 음대의 Cynthia 선생님을 위와 같이 표현할 수 있겠지요.
보통 학교에서는, 각 교사가 여러가지의 수업을 진행할 수 있습니다. 이는 1:N(one-to-many, "일대 다"라고 보통 읽습니다)라는 용어로 이 관계를 설명할 수 있습니다. 그렇다면 스키마에서 이 관계를 어떻게 정의할 수 있을까요?
Teachers와 Classes 사이 관계를 담기 위해 어떤 형태로 정보가 저장되어야 할까요?
일단, Teachers 테이블에서 어떤 식으로 수업(Classes)를 식별할 것인지부터 확실하게 정해야 합니다.
각 테이블에는 고유한 ID라는 필드가 있습니다. 이느 각 테이블의 레코드 하나를 가리키는 숫자로, 자동적으로 그 값이 증가합니다. (auto increments) 이 ID 필드는 해당 테이블의 기본 키 (Primary Key) 역할을 합니다.
다른 테이블에서 테이블의 기본 키(primary key)를 참조할 때 해당 값을 외래 키(foreign key)라고 합니다. 이 예시에서 ClassID라는 필드는 Classes 테이블에서 특정 레코드를 고유하게 식별하는 외래 키입니다.
한 열에 여러 값을 저장하면, 상수 시간(constent-time)에 대한 검색 손실이 발생합니다. 한 명의 교사의 수업에서 학생을 검색하기 위해, 해당 교사의 ClassID 열의 값을 반복해야만 할 것입니다.
이 관계를 위해 가장 좋은 방법은, Teachers 테이블을 Classes 테이블에 저장하는 것입니다. 우리는 여전히 교사가 가르치는 모든 수업을 찾아낼 수 있고, 앞서 살펴본 모든 문제를 피할 수 있을것 입니다.
각 수업은 여러 명의 학생들로 구성되어 있습니다. 그리고 각 학생 또한 여러 개의 수업을 듣고 있습니다. 이러한 형태의 관계를 N:N (many to many 다대 다)이라고 표현할 수 있습니다.
하나의 열에는 여러 값을 저장할 수 없다. 즉, Classes 테이블에 여러 개의 Student ID 들을 저장할 수가 없습니다. 그 이유는 수업당 최대 학생 수를 늘리면 문제가 발생하기 때문이다.
위와 같은 차트로 관계를 시각화 할 수는 있지만 이처럼 큰 차트를 데이터베이스에 저장하는 것은 무리입니다. 차트에서 ClassID와 StudentID들이 교차하는 좌표들을 하나의 테이블에 저장 해보겠습니다.
새롭게 만든 테이블은 SQL의 원칙들을 위반하지 않고서 스키마에서 수업 대 학생 (Classes-to-Students) 관계를 잘 보여줍니다. 이와 같은 테이블을 조인(join) 테이블이라고 부릅니다.
하나의 수업은 Classes/Students 테이블에서 여러 번 등장하기 때문에 일대 다(one-to-many) 관계입니다. 마찬가지로, 한 학생이 조인 테이블에서 여러 번 등장하기 때문에 Students 테이블과 Classes/Students 테이블 또한 일대 다(one-to-many)관계라고 볼 수 있습니다. 새롭게 추가된 조인 테이블이 기존의 다대 다 관계를 두 일대 다 관계로 나눈 것을 볼 수 있습니다.
Describe 명령어를 이용하면 특정 테이블이나 열을 살펴볼 수 있습니다.
쿼리문에서 where 라는 단어는 특정조건을 만족하는 레코드만 조회할 수 있도록 해주는 제한 사항 (constraint)입니다.
SQL에서는 쿼리를 작성할 때 테이블 이름을 가명 (alias)로 표기할 수 있습니다. 이전에 주어진 교사 ID 번호를 이용해 수업 일정을 조회한 쿼리문을 이번에는 classes 테이블을 c라는 가명으로만 바꿔서 다시 진행해 보겠습니다.
select c.name, c.room from classes c where c.teacher_id=2;
SQL에서는 다양한 방법으로 해당 검색을 실행할 수 있습니다. 제일 먼저 해 볼 것은 서브쿼리 (subquery)입니다. Classes 테이블에 실행하는 쿼리 안에서 Sebastian의 ID번호를 찾기 위해 Teachers 테이블에 쿼리를 작성하겠습니다.
select c.name, c.room from classes c where c.teacher_id=(select id from teachers where name="Sebastian");
select c.name, c.room from classes c inner join teachers t on (c.teacher_id=t.id) where t.id=2;
이번에는 조인 (join)을 이용해 접근해 보겠습니다. 조인은 다수의 테이블에서 특정 기준에 해당하는 레코드들을 합칩니다. 위 예시는 Classes 테이블의 teacher_id와 Teachers 테이블의 id 필드들 간에 공통된 데이터를 찾습니다. 다양한 종류의 조인트 (joint)들이 존재합니다. 가장 자주 보게될 조인은 두 테이블 간에 동일한 record들을 돌려주는 이너 조인(inner join)입니다.
이너 조인 문법이 다른 두 방법보다 더 선호된다. 이너 조인은 Teachers 테이블에 있는 다른 데이터에도 접근할 수 있게 해줍니다. 예를 들어, 교사 이름을 직접적으로 찾아볼 수 있습니다.
select c.name, c.room from classes c inner join teachers t on (c.teacher_id=t.id) where t.name="Sebastian";
또한 조인을 활용하면 두 개의 테이블에서부터 온 데이터를 하나의 쿼리 결과로 보여줄 수 있습니다.
select c.name, c.room, t.name from classes c inner join teachers t on (c.teacher_id=t.id) where t.name="Sebastian";
특정 학생의 수업 일정을 보여주는 방법
Classes 테이블과 Classes/Students 테이블을 이너 조인하는 쿼리:
select c.name from classes c inner join classes_students cs on (c.id=cs.class_id)
여기에 Students 테이블을 연결하는 방법 : 또 다른 이너 조인을 이용하면 된다.
select c.name, s.name from classes c inner join classes_students cs on (c.id=cs.class_id) inner join students s on (cs.student_id=s.id) where s.name="Jayo";
이번에는 Student ID를 이용해 원하는 정보에 접근할 수 있습니다. 더 나아가 Students 테이블을 쿼리하고 있어니 테이블에 있는 다른 정보도 쿼리 결과에 표시할 수 있습니다.
CS 수업의 학생명단
select s.name, s.email from students s inner join classes_students cs on (s.id=cs.student_id) inner join classes c on (cs.class_id=c.id) where c.name="CS 101";
교사 중 한명이 아파서 당일 수업을 취소한다는 이메일을 해당 수업 학생들에게 보내야 하는 시나리오를 생각해 보자. 쿼리를 어떻게 작성해야 할까요?
select s.email from students s inner join classes_students cs on (s.id=cs.student_id) inner join classes c on (cs.class_id=c.id) inner join teachers t on (t.id=c.teacher_id) where t.name="Sebastian";
특정 학생이 수강하고 있는 특정 교사의 수업들을 보고 싶을 경우:
select c.name from classes c inner join classes_students cs on (c.id=cs.class_id) inner join students s on (cs.student_id=s.id) inner join teachers t on (t.id=c.teacher_id) where s.name="Rebekah" and t.name="Kelly";
제한 사항이 두 개: 학생 이름과 교사 이름