🐹 7. Database

14. 데이터베이스

[ SQL 문법과 그 변종들 ]

• 명시적(explicit) JOIN

SELECT CourseName, TeacherName
FROM Course INNER JOIN Teachers
ON Courses.TeacherID = Teachers.TeacherID

• 묵시적(implicit) JOIN

SELECT CourseName, TeacherName
FROM Courses, Teachers
WHERE Courses.TeacherID = Teachers.TeacherID

[ 비정규화 vs 정규화 데이터베이스 ]

• 정규화 데이터베이스

  : 중복을 최소화하도록 설계된 데이터베이스

  • Courses에는 TeacherID처럼 Teachers에 대한 외래키를 갖는 컬럼이 있을 것이다.
  • 장점: 교사 정보(이름, 주소 등등)를 데이터베이스에 한 번만 저장해도 된다.
  • 단점: 상당수의 일상적 질의를 처리하기 위해 JOIN을 많이 하게 된다.

• 비정규화 데이터베이스

  : 읽는 시간을 최적화하도록 설계된 데이터베이스

  • 데이터를 중복해서 저장할 수 있다.
  • 만약 같은 질의를 자주 반복해야 한다는 사실을 미리 알고 있으면, 교사의 이름 정보를 Courses 테이블에 중복해 저장할 수도 있다.
  • 높은 규모 확장성을 실현하기 위해 자주 사용된다.

[ SQL문 ]

위 예제의 데이터베이스 구조는 다음과 같다.

Courses: CourseID*, CourseName, TeacherID
Teachers: TeacherID*, TeacherName
Students: StudentID*, StudentName
StudentCourses: CourseID*, StudentID*

위 테이블을 사용해 질의문을 구현해보자.

질의 #1: 학생 등록

• 모든 학생의 목록을 뽑고 각 학생이 얼마나 많은 강의를 수강하고 있는지 확인하는 질의문

/* 잘못된 코드 */
SELECT Students.StudentName, count(*)
FROM Students INNER JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID

이 코드에는 3가지 문제가 있다.

1. 강의를 아예 수강하지 않는 학생은 목록에 포함되지 않는다. 왜냐하면 Student Courses가 수강 신청한 학생만을 포함하기 때문이다.

   → LEFT JOIN을 사용하도록 변경해야 한다.

2. LEFT JOIN을 하도록 변경해도 문제가 있다. count(*)는 StudentID로 만들어진 그룹 내에 존재하는 아이템의 개수를 센다. 따라서 강의를 아예 수강하지 않는 학생의 경우에도 1로 계산되는 문제가 있다.

   → count(StudentsCourses.CourseID)와 같이 그룹 내의 CourseID 수를 세도록 변경해야 한다.

3. Students.StudentID를 사용해 그룹을 만들었지만, 여전히 한 그룹 안에 여러 개의 StudentName이 존재한다. 데이터베이스가 어떤 StudentName을 반환해야 하는지 알 수 없다. 모두 같은 값을 가진다 해도 데이터베이스는 그 사실을 이해하지 못할 것이다.

   → first(Students.StudentName)과 같은 집계 함수(aggregate function)를 사용할 필요가 있다.

위 문제를 고쳐 다시 만든 질의문이다.

/* 첫 번째 해법: 다른 질의문으로 감싸기 */
SELECT StudentName, Student.StudentID, Cnt
FROM (
		SELECT Students.StudentID, count(StudentCourses.CourseID) as [Cnt]
		FROM Students LEFT JOIN StudentCourses
		ON Students.StudeentID = StudentCourses.StudentID
		GROUP BY Students.StudentID
) T INNER JOIN Students on T.StudentID = Students.StudentID

위 코드 3번째 줄에서 그냥 학생 이름을 SELECT하면 3~6번 줄에서 처럼 다른 질의문으로 감쌀 필요가 없지 않나라고 생각할 수 있다.

아래 코드는 다시 만들어본 잘못된 해결책 코드이다.

/* 잘못된 코드 */
SELECT StudentName, Students.StudentID, count(StudentCourses.CourseID) as [Cnt]
FROM Students LEFT JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID

이 코드는 이전 코드와 정확히 같은 결과를 낼 수 없다. SELECT가 가능한 것은 집계 함수나 GROUP BY 절에 포함된 값 뿐이다.

다음과 같은 방법을 써야한다.

/* 두 번째 해법: StudentName을 GROUP BY 절에 추가하기 */
SELECT StudentName, Students.StudentID, count(StudentCourses.CourseID) as [Cnt]
FROM Students LEFT JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID, Students.StudentName

또 다른 해법은 다음과 같다.

/* 세 번째 해법: 집합 함수로 감싸기 */
SELECT max(StudentName) as [StudentName], Students.StudentID, 
							count(StudentCourses.CourseID) as [Count]
FROM Students LEFT JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID

질의 #2: 수강생 수 구하기

• 모든 교사 목록과 각 교사가 가르치는 학생 수를 구하는 질의문

만약 한 교사가 동일한 학생을 여러 강의에서 가르치는 경우, 그 각각을 다른 학생으로 간주해서 가르치는 학생 수에 합산한다. 교사 리스트는 각 교사가 가르치는 학생 수를 기준으로 내림차순 정렬하면 된다.

SELECT TeacherID, count(StudentCourses.CourseID) AS [Number]
FROM Courses INNER JOIN StudentCourses
ON Courses.CourseID = StudentCourses.CourseID
GROUP BY Courses.TeacherID

INNER JOIN을하게 되면 강의를 하지 않는 교사는 목록에 포함되지 않는다. 다음과 같이 LEFT JOIN을 하면 모든 교사 목록을 얻을 수 있다.

SELECT TeacherName, isnull(StudentSize.Number, 0)
FROM Teachers LEFT JOIN
		(SELECT TeacherID, count(StudentCourses.CourseID) AS [Number]
		FROM Courses INNER JOIN StudentCourses
		ON Courses.CourseID = StudentCourses.CourseID
		GROUP BY Courses.TeacherID) StudentSize
ON Teachers.TeacherID = StudentSize.TeacherID
ORDER BY StudentSize.Number DESC

NULL값을 0으로 변환하기 위해 SELECT 문에서 isnull을 사용한다.

• isnull(a, b): NULL이 아닐 때 값 a, NULL일 때 값 b

[ 소규모 데이터베이스 설계 ]

면접장에서 데이터베이스를 설계해보라는 요청을 받았을 때! 접근법!

객체 지향 설계와 비슷한 점이 많다.

1단계: 모호성 처리

데이터베이스에 관련된 문제에는 모호한 부분이 내포되어 있다. 정확히 무엇을 설계해야 하는지 이해해야 한다.

[ 아파트 임대 대행업체가 사용할 시스템을 설계 ]

• 이 업체의 대리점이 여러 개인지 아니면 하나만 있는지 알고 있어야 한다.
• 얼마나 일반적으로 설계해야 하는지 면접관과 논의해야 한다.

• ex) 어떤 사람이 같은 빌딩에 있는 집을 두 개 빌리는 일은 굉장히 드물다. 하지만 그렇다고 해서 그런 경우를 처리할 수 없어야 한다는 것은 아니다. 그럴 수도 있고, 아닐 수도 있다.

  → 드물게 생기는 일은 별도의 방식을 사용해서, 즉 데이터베이스에 보관된 그 사람의 개인 정보를 복사한다거나 해서 해결할 수도 있다.

2단계: 핵심 객체 정의

그 다음으로는, 이 시스템의 핵심 객체(core object)가 무엇인지 살펴봐야 한다. 보통 핵심 객체 하나당 하나의 테이블을 사용한다.

아파트 임대 대행업체의 경우 Property, Building, Apartment, Tenant, Manager 등이 핵심 객체가 될 수 있다.

3단계: 관계 분석

핵심 객체의 윤곽을 잡고나면 어떻게 테이블을 설계해야 할지 감을 잡을 수 있을 것이다. 테이블끼리 어떤 관계가 있을 지(다대다, 일대다 등) 생각해야 한다.

Buildings와 Apartments의 관계가 일-대-다 관계라면, 다음과 같이 표현할 수 있다.

Apartments	s
ApartmentID	int
ApartmentAddress	varchar(100)
BuildingID	int

Buildings	s
BuildingID	int
BuildingName	varchar(100)
BuildidngAddress	varchar(500)

Apartments 테이블과 Buildings 테이블은 BuildingID를 통해 연결된다.

한 사람이 하나 이상의 아파트를 임대할 수 있도록 하고 싶다면, 다음과 같이 다-대-다 관계를 구현하면 된다.

TenantApartments	s
TenantID	int
ApartmentID	int

Apartments	s
ApartmentID	int
ApartmentAddress	varchar(100)
BuildingID	int

Tenants	s
TenantID	int
TenantName	varchar(100)
TenantAddress	varchar(500)

TenantApartments 테이블은 Tenants와 Apartments 사이의 관계를 저장하고 있다.

4단계: 행위 조사

마지막으로 세부적인 부분을 채워 넣어야 한다. 흔히 수행된 작업들을 살펴보고, 관련된 데이터를 어떻게 저장하고 가져올 것인지 이해해야 한다.

임대와 관계된 용어들 퇴거(moving out)나 임대비(rent payments)와 같은 것들을 처리해야 한다. 이런 작업들을 처리하려면 새로운 테이블이 필요하다.

[ 대규모 데이터베이스 설계 ]

대규모의 규모 확장성이 높은 데이터베이스를 설계할 때에 JOIN 연산(앞선 예제)은 일반적으로 아주 느리다고 간주해야 한다.

따라서 데이터를 비정규화(denormalize)해야 한다.