🦊 7. Database

7. Database

SQL 문접과 그 변종들

• 명시적 JOIN

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

• 묵시적 JOIN

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

이 둘은 동등하다. 어느 쪽을 사용할 것이냐 하는 것은 개인 취향의 문제이다.

비정규화 vs. 정규화 데이터베이스

• 정규화 데이터베이스 : 중복을 최소화하도록 설계됨
• 비정규화 데이터베이스 : 읽는 시간을 최적화하도록 설계됨

Courses 나 Teachers 와 같은 자료를 포함하는 전형적인 정규화 DB는 외래키를 갖은 컬럼이 있다.

이 설계의 장점은 교사 정보를 DB에 한 번만 저장해도 된다는 것이다.

반면 상당수의 일상적 질의를 처리하기 위해 JOIN을 많이 하게 되는 단점이 있다.

비정규화 DB에서는 데이터를 중복해서 저장할 수 있다.

같은 질의를 자주 반복해야 한다는 사실을 미리 알고 있으면, 교사의 이름 정보를 Courses 테이블에 중복해 저장할 수도 있다.

높은 규모 확장성을 실현하기 위해 자주 사용되는 기법이다.

SQL 문

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

질의 #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 Sudents on T.studentID = Students.StudentID

• 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 TeacherName, isnull(StudentSize.Number, 0)
FROM Teachers LEFT JOIN
	(SELECT TeacherID, count(StudentCourses.CourseID) AS [Number]
		FROM Courses INNER JOIN StudentCourses
		ON Courses.CourseID = StudnetCourses
		GROUP BY Courses.TeacherID) StudentSize
ON Teachers.TeacherID = StudentSize.TeacherID
ORDER BY StudentSize.Number DESC

소규모 데이터베이스 설계

1단계: 모호성 처리

설계를 시작하기 전에 정확히 무엇을 설계해야 하는지 이해해야 한다.

2단계: 핵심 객체 정의

그 다음으로, 이 시스템의 핵심 객체가 무엇인지 살펴본다.

핵심 객체 하나당 보통 하나의 테이블을 사용한다.

3단계: 관계 분석

핵심 객체의 윤곽을 잡고 나면 테이블끼리 어떤 관계가 있을지 살펴본다.

4단계: 행위 조사

흔하게 수행될 작업들을 살펴보고 관련된 데이터를 어떻게 저장하고 가져올 것인지 이해해야 한다.

대규모 데이터베이스 설계

대규모의 규모 확장성이 높은 DB를 설계할 때에 JOIN 연산은 일반적으로 아주 느리다고 간주해야 한다.

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

면접 문제

14.1 하나 이상의 집

하나 이상의 집을 대여한 모든 거주자의 목록을 구하는 SQL 질의문을 작성하라.

SELECT *
FROM Tenants
INNER JOIN TenantApartments
ON Tenants.TenantID = TenantApartments
GROUP BY Tenents.TenentID

14.2 Open 상태인 Request

모든 건물 목록과 Status 가 Open 인 모든 Request 개수를 구하라.

SELECT Buildings.BuildingName, count(*) as [cnt]
FROM Buildings
INNER JOIN Requests
ON Buildings.BuildingID = Requests.BuildingID
	and Requests.status = 'Open'
GROUP BY Building.BuildingID

14.3 Request 를 Close로 바꾸기

11번 빌딩에서 대규모 리모델링 공사를 진행 중이다.

이 건물에 있는 모든 집에 대한 모든 Request 의 Status 를 Close 로 변경해주는 질의문을 작성하라.

UPDATE Requests
SET Status = 'Close'
WHERE Requests.BuildingID = 11

14.4 JOIN

서로 다른 종류의 JOIN 은 어떤 것들이 있는가? 각각이 어떻게 다르고, 어떤 상황에서 어떤 JOIN 과 어울리는지 설명하라.

• LEFT JOIN : 기준 테이블에서 매핑되는 데이터를 붙임, 존재하지 않는 데이터는 NULL 처리
• RIGHT JOIN : 상대 테이블에서 매핑되는 데이터를 가져옴. 존재하지 않는 기준 데이터는 NULL 처리
• INNER JOIN : 교집합, 서로 매핑되는 것만 조회, 나머지는 사라짐
• FULL JOIN : 합집합, 두 데이터를 모두 가져옴

14.5 비정규화

비정규화란 무엇인가? 그 장단점을 설명하라.

DB 에서의 중복으로 존재하는 데이터를 허용함

데이터의 중복으로 일관성이 없어질 수 있지만, JOIN 을 하지 않아 읽기 성능이 향상됨.

14.6 개체-관계 다이어그램

회사, 사람, 직원으로 구성된 데이터베이스의 ER 다이어그램을 그려라.

companyID   BIGINT
companyName VARCHAR(50)

personID    BIGINT
personeName VARCHAR(50)

employee    BIGINT
employeeName VARCHAR(50)