🐢 7. Database

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

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

🫧 SQL 문

• 1. 학생 등록
  • 다른 질의문으로 감싸기

  SELECT StudentName, Students.StudentID, Cnt
  FROM {
    SELECT Students.StudentID, count(StudentCourse.CourseID) as [CNT]
    FROM Students LEFT JOIN StudentCourses
    ON Students.StudentID = StudentCOurse.StudentID
    GROUP BY Students.StudentID
  } T INNER JOIN Students on T.studentID = Students.StudentID
  

  • StudentName 을 GROUP BY 절에 추가하기

  SELECT Students.StudentID, count(StudentCourse.CourseID) as [CNT]
  FROM Students LEFT JOIN StudentCourses
  ON Students.StudentID = StudentCOurse.StudentID
  GROUP BY Students.StudentID, Students.StudentID
  

  • 집합 함수로 감싸기

  SELECT max(StudnetName) as [StudentName], Students.StudentID,
      count(StudentCourse.CourseID) as [CNT]
    FROM Students LEFT JOIN StudentCourses
    ON Students.StudentID = StudentCOurse.StudentID
    GROUP BY Students.StudentID
  

• 1. 수강생 수 구하기

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

🫧 소규모 데이터베이스 설계

• 1단계: 모호성 처리
  • 정확히 무엇을 설계해야 하는지 이해하기
• 2단계: 핵심 객체 정의
  • 핵심 객체 하나당 보통 하나의 테이블 사용함
• 3단계: 관계 분석
  • 테이블끼리 어떤 관계가 있는지 분석하기
• 4단계: 행위 조사
  • 수행될 작업들을 살펴보고, 관련된 데이터를 어떻게 저장하고 가져올 것인지 이해하기

🫧 대규모 데이터베이스 설계

• join 연산은 일반적으로 아주 느리다고 간주하기
• 데이터를 비정규화 해야 함

🫧 면접 문제

• 14.1 하나 이상의 집: 하나 이상의 집을 대여한 모든 거주자의 목록을 구하는 SQL 질의문

SELECT TenantName
FROM Tenants
INNER JOIN
(SELECT TenantID FROM AptTenants GROUP BY TenantID HAVING count(*) > 1) C
ON Tenants.TenantID = C.TenantID

• 14.2 Open 상태인 Request: 모든 건물 목록과 Status 가 Open 인 모든 Request 의 개수 구하기

SELECT BuildingName, ISNULL(Count, 0) as 'Count'
FROM Buildings
WHERE Requests.Status = 'Open'

• 14.3 Request 를 Close로 바꾸기: 11번 빌딩에서 대규모 리모델링 공사 진행중임. 이 건물에 있는 모든 집에 대한 모든 Request 의 Status 를 Close 로 변경해주는 질의문 작성하라

UPDATE Requests
SET Status = 'Close'
WHERE AptID IN (SELECT AptID FROM Apartments WHERE BuildingID = 11)

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

• Inner JOIN: 서로 매칭되는 것만 조회한다. (교집합)

• Outer JOIN: 매칭되는 데이터를 기준으로 좌측 또는 우측의 데이터도 같이 조회한다.

  • Left Outer JOIN : LEFT OUTER JOIN B 할 경우 A, B 테이블간 조인 조건이 맞지 않는 A와B 테이블 중 A 테이블은 무조건 결과값에 포함되어 나오는 것이다. 만약 B의 값이 없는 경우는 그 속성을 NULL로 반환한다.

  • Right Outer JOIN : A와B 테이블 중 B 테이블은 무조건 결과값에 포함되어 나오는 것이다. 만약 A의 값이 없는 경우는 그 속성을 NULL로 반환한다.

  • Full Outer JOIN : 좌측과 우측의 모든 데이터를 읽어 JOIN한다.

• Natural JOIN: 두 테이블을 JOIN할때 컬럼명, 타입이 같은 모든 컬럼을 JOIN 조건으로 사용한다.

• Self JOIN: 자기 자신의 테이블을 JOIN하는 것이다.- employees 테이블이 사원번호, 상사번호를 가지고 있다고 가정해보자. 특정 사원의 상사를 구하고 싶다면 Self JOIN을 사용해야한다.

• 14.5 비정규화: 비정규화란 무엇인가? 그 장단점을 설명해라

  • 비정규화
    • 의도적으로 정규화 원칙을 위배하는 행위
    • 데이터베이스의 성능 향상을 위해, 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법
    • 조회 속도를 향상 시키지만, 데이터 모델의 유연성은 낮아짐
    • 조회 성능을 향상 시키기 위해 데이터 중복을 허용하거나 데이터를 그룹핑 하는 과정
    • 정규화가 끝난 후 성능 이슈가 있을 때 가지는 과정이며, 정규화처럼 일정한 규칙 존재
    • 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정

  • 반정규화 대상

    • 수행 속도가 많이 느린 경우
    • 테이블의 조인 연산을 지나치게 사용하여 데이터를 조회하는 것이 기술적으로 어려운 경우
    • 테이블에 많은 데이터가 있고, 다량의 범위 혹은 특정 범위를 자주 처리해야 하는 경우
  • 반정규화의 장점과 단점
    • 장점
      • 데이터를 빠르게 조회할 수 있음
      • 조인을 제거하기 때문에 검색 시간이 최적화
    • 단점
      • 데이터의 삽입, 삭제, 수정 등 갱신 시 비용이 높아짐
      • 데이터간의 일관성이 깨질 수 있음 (서로 다른 데이터가 저장될 수 있음)
      • 많은 저장 공간이 필요함

• 14.6 개체-관계 다이어그램: 회사, 사람, 직원으로 구성된 데이터베이스의 ER 다이어그램을 그려라

companyID        int
companyName      varchar(10)

peopleID         int
peopleName       varchar(10)

employeeID       int
employeeName     varchar(10)

• 14.7 성적 데이터베이스 설계: 학생들의 성적을 저장하는 간단한 데이터베이스를 생각해보자. 이 데이터베이스를 설계하고, 성적이 우수한 학생(상위 10%) 목록을 반환하는 sql 질의문을 작성해라. 단 학생 목록은 평균 성적에 따라 내림차순으로 정렬되어야 한다

SELECT 학생, avg(성적) FROM 성적표 ORDER BY avg(성적) DESC;