[데이터베이스] 주요 개념 정리

정규화된 데이터베이스와 비정규 데이터베이스

---

정규화된 데이터베이스는 중복이 최소가 되도록 설계된 데이터베이스입니다. 비정규 데이터베이스는 일기에 소요되는 시간을 줄이는 것을 목적으로 설계된 데이터 베이스입니다. Courses나 Teachers와 같은 자료를 표함하는 전형적 정규화 데이터베이스의 경우, Courses에는 TeacherID와 같은 열이 있을 것이고, 이 열은 Teachers에 대한 외래 키일 것입니다. 이렇게 데이터베이스를 설계하면 교사 정보(이름, 주소 등등)가 데이터베이스에 한 번만 저장된다는 장점이 있습니다. 단점은 상당수의 일상적 질의를 처리하기 위해 많은 JOIN을 하게 된다는 것입니다.

대신에 데이터 중복을 저장하여 비정규화할 수도 있습니다. 가령 같은 질의를 자주 반복해야 한다는 사실을 알고 있다면, 교사의 이름 정보를 Courses 테이블에 중복해 저장할 수도 있을 것입니다. 비정규화는 높은 규모확장성을 실현하기 위해 자주 사용되는 기법입니다.

SQL문

---

앞서 언급했던 데이터베이스를 예로 들어 기본적 SQL 문법을 훑어보도록 하겠습니다. 이 데이터베이스는 다음과 같은 단순한 구조를 갖습니다. (*는 기본 키, 즉 primary key이다.)

Courses: CourseID*, CourseName, TeacherID

Teachers: TeacherID*, TeacherName

Students: StudentID*, StudentName

StudnetCourses: CourseID*, StudentID*

질의1: 학생 등록

모든 학생의 목록을 뽑고 각 학생이 얼마나 많은 강의를 수강하고 있는지 알아보는 질의를 만들어보겠습니다. 다음과 같이 해볼 수 있을 것입니다.

SELECT Studnets.StudentName, count(*)

FROM Students INNER JOIN StudentCourses

ON Students.StudentID = StudentCourses.StudentID

GROUP BY Students.StudentID

여기에는 세가지 문제가 있습니다.

1. 어떤 강의도 수강하지 않는 학생은 목록에 포함되지 않습니다. Student Courses가 수강신청을 한 학생만 포함하기 때문입니다. 따라서 LEFT JOIN을 사용하도록 변경해야 합니다.

2. LEFT JOIN을 하도록 변경한다 해도 여전히 문제는 있습니다. COUNT(*)는 StudentID마다 만들어지는 그룹 내에 얼마나 많은 레코드가 있는지를 셉니다. 따라서 아무 강의도 수강하지 않는 학생의 경우에도 1로 계산되는 문제가 있습니다. 그러므로 count(StudnetCourses.CourseID)와 같이 그룹 내의 CourseID 수를 세도록 변경해야 합니다.

3. Students.StudentID를 사용해 그룹을 만들었는데, 한 그룹 안에 여전히 여러 개의 StudentNames가 존재합니다. 어떤 StudentName을 반환할지 데이터베이스가 데이터베이스가 결정할 수 있나? 그 전부가 같은 값을 가진다 해도 데이터베이스는 그 사실을 이해하지 못합니다. 따라서 first(Students.StudentName)과 같이 집합 함수(aggregate function)를 사용하여야 합니다.

이런 문제들을 교정하고 나면 다음과 같은 질의문이 만들어집니다.

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

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

/* 해법2: 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

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

질의2: 수강생 수 구하기

모든 교사 목록과 각 교사가 가르치는 학생 수를 구하는 질의를 작성해 보겠습니다. 교사 리스트는 각 교사가 가르치는 학생수를 기준으로 내림차순 정렬되어야 합니다. 단계적으로 이 질의문을 만들어 보겠습니다. 우선, TeacherID의 목록을 구한 다음에 각각의 TeacherID에 얼마나 많은 학생이 배정되어 있는지 구합니다. 앞서 살펴본 질의와 아주 비슷합니다.

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

INNER JOIN을 하면 맡은 강의가 없는 교사는 목록에 포함되지 않는다는 것에 유의합시다. 다음과 같이 모든 교사 목록에 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을 사용하고 있다는 점을 눈여겨보기를 바랍니다.

작은 데이터베이스 설계

---

데이터베이스 설계는 객체 지향 설계와 비슷한 점이 많습니다.

단계1: 모호성 처리

데이터베이스에 관계된 문제에는 의도된, 혹은 의도되지 않은 모호함이 숨겨져있습니다. 설꼐를 진행하기 전에 정확히 무엇을 설계해야 하는지 이해하도록 해야 합니다.

아파트 임대 대행업체를 위한 시스템을 설계한다고 해보겠습니다. 이 업체가 여러 곳에서 사업을 하는지 아니면 한곳에서만 하는지 알아야합니다. 또한 얼마나 일반적으로 설계해야 하는지 면접관과 의논해야 합니다. 가령, 어떤 사람이 같은 빌딩에 있는 집을 두 개 빌리는 일은 드뭅니다. 하지만 그렇다고 그런 경우를 처리할 수 없ㄷ자는 것은 아니지 않은가? 그럴 수도 있고, 아닐 수도 있습니다. 드물게 생기는 일은, 별도의 우회책을 사용하여 해결할 수도 있습니다. (데이터베이스에 보관된 그 사람의 연락 정보를 복제한다거나 해서)

단계2: 핵심 객체 정의

그 다음으로는, 시스템의 핵심 객체를 살펴봐야 합니다. 보통 핵심 객체 하나당 하나의 테이블을 사용합니다. 아파트 임대 대행업체의 경우, 필요한 핵심 객체로는 Property, Building, Apartment, Tenant, 그리고 Manager 등이 있을 수 있습니다.

단계3: 관계성 분석

핵심 객체를 식별해내고 나면 테이블을 어떻게 설계해야 하는지 감을 잡을 수 있습니다. 그런데 이 테이블 사이의 관계는 어떻게 정의해야 하겠는가? 일-대-다? 다-대-다

Buildings와 Apartments 사이에 일-대-다 관계가 있다면, 다음과 같이 표현해야 할 것입니다.

Buildings
BuildingID	BuildingName	BuildingAddress

Apartments
ApartmentID	ApartmentName	ApartmentAddress

Apartments 테이블은 BuildingID 열을 통해 Buildings에 연결됩니다. 한 사람이 집을 하나 이상 임대할 수 있도록 하고 싶다면, 다음과 같이 다-대-다 관계를 구현해야 합니다.

Tenants
BuildingID	TenantName	TenantAddress

Apartments
ApartmentID	ApartmentName	ApartmentAddress

Tenant Apartments
TenantID	ApartmentID

TenantApartments 테이블은 Tenants와 Apartments 사이의 관계를 저장합니다.

단계4: 행위 조사

마지막으로 세부적인 부분을 결정합니다. 빈번히 하게될 작업이 어떤 것들인지 살피고, 관련된 데이터를 어떻게 저장하고 가져올 것인지 이해합니다. 대여와 관계된 용어들, 그러니까 퇴거나 대여로 지불도 처리해야합니다. 이런 작업들을 처리하려면 새로운 테이블이 필요합니다.

대규모 데이터베이스 설계

---

대규모의 그리고 규모확장성이 높은 데이터베이스를 설계할 때 JOIN은 일반적으로 아주 느린 연산으로 간주된다는 점을 기억합시다. 따라서 데이터를 비정규화해야 합니다. 데이터가 어떻게 사용될지 깊이 생각하는것이 좋습니다. 데이터를 여러 테이블에 중복시켜야 할지도 모릅니다.