데이터베이스 설계

seongmin·2022년 10월 6일
dbschemasql
0

Java

목록 보기
27/30

스키마(Schema) 는 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티 간의 관계에 대한 설명이다.

즉, "데이터베이스의 청사진"과 같다.

엔티티는 데이터베이스에서 테이블로 표시할 수 있다.

각 엔티티에는 해당 엔티티의 특성을 설명하는 필드(Field) 가 있다.

레코드(record) 는 테이블에 저장된 항목이다.

TeachersClassesStudents
NameNameName
DepartmentRoom NumberEmail
ClassesTeacherClasses

엔티티 : Teachers, Classes, Students
Teachers의 필드 : Name, Department, Classes
Teachers 테이블의 하나의 레코드를 예로 들면,

NameDepartmentClasses
CynthiaMusicMusic Theory

Cynthia, Music, Music Theory 가 레코드에 해당된다.

관계형 데이터베이스

구조화된 데이터는 하나의 테이블로 표현할 수 있습니다. 사전에 정의된 테이블을 relation 이라고도 부르기 때문에, 테이블을 사용하는 데이터베이스를 관계형 데이터베이스(Relational database)라고 합니다.

관계형 데이터베이스 관련 키워드 정리

  • 데이터(data): 각 항목에 저장되는 값이다.
  • 테이블(table; 또는 relation) : 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적된다.
  • 칼럼(column; 또는 field) : 테이블의 한 열을 가리킨다.
  • 레코드(record; 또는 tuple) : 테이블의 한 행에 저장된 데이터다.
  • 키(key) : 테이블의 각 레코드를 구분할 수 있는 값이다. 각 레코드마다 고유한 값을 가진다. 기본키(primary key)와 외래키(foreign key) 등이 있다.

관계 종류

1:1 관계

하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우이다. 다음과 같이 User 테이블과 Phonebook 테이블이 있다고 가정해본다.

User 테이블은 user_id, name, phone_id 를 가지고 있다. 이 중 phone_id 는 외래키(foreign key) 로써, Phonebook 테이블의 phone_id 와 연결되어 있다. Phonebook 테이블은 phone_id 와 phone_number 를 가지고 있다.

각 전화번호가 단 한 명의 유저와 연결되어 있고, 그 반대도 동일하다면, User 테이블과 Phonebook 테이블은 1:1 관계(One-to-one relationship) 다.

그러나 1:1 관계는 자주 사용하지 않는다. 1:1로 나타낼 수 있는 관계라면 User 테이블에 phone_id를 대신해 phone_number를 직접 저장하는 게 나을 수 있기 때문이다.

1:N 관계

하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우다. User 테이블과 Phonebook 테이블의 관계를 다음과 같이 가정해본다.

이 구조에서는 한 명의 유저가 여러 전화번호를 가질 수 있다. 그러나 여러명의 유저가 하나의 전화번호를 가질 수는 없다. 이런 1:N(일대다) 관계는 관계형 데이터베이스에서 가장 많이 사용한다.

N:M 관계

여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우다. N:M(다대다) 관계를 위해 스키마를 디자인할 때에는, Join 테이블을 만들어 관리한다. 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있다.

다음과 같이 여행 상품을 관리하는 테이블이 있다고 가정한다. 여러 개의 여행 상품이 있고, 여러 명의 고객이 있다. 고객 한 명은 여러 개의 여행 상품을 구매할 수 있고, 여행 상품 하나는 여러 명의 고객이 구매할 수 있다.

이렇게 Customer 테이블과 Package table이 따로 존재한다면, 두 개의 테이블과 1:N(일대다) 관계를 형성하는 새로운 테이블로 N:N(다대다) 관계를 나타낼 수 있다.

이렇게 다대다 관계를 위한 테이블을 조인 테이블 이라고 한다. N:N(다대다) 관계를 그림으로 나타내면 다음과 같다.

customer_package 테이블에서는 고객 한 명이 여러 개의 여행 상품을 가질 수 있고, 여행 상품 하나가 여러 개의 고객을 가질 수 있다.

customer_package 테이블은 customer_id와 package_id를 묶어주는 역할이다. 이 테이블을 통해 어떤 고객이 몇 개의 여행 상품을 구매했는지 또는, 어떤 여행 상품이 몇 명의 고객을 가지고 있는지 등을 확인할 수 있다. 이렇게 조인 테이블을 생성하더라도, 조인 테이블을 위한 기본키(여기서는 cp_id)는 반드시 있어야 한다.

자기참조 관계(Self Referencing Relationship)

때로는 테이블 내에서도 관계가 필요하다. 예를 들어 추천인이 누구인지 파악하기 위해 사용할 수 있다.

다음과 같이 유저 테이블이 있다. user_id는 기본 키(primary key), name은 사용자의 이름, 그리고 recommend_id는 추천인 아이디다.

SQL 내장함수

집합연산: 레코드를 조회하고 분류한 뒤, 특정 작업을 하는 연산

  • GROUP BY

데이터를 조회할 때 그룹으로 묶어서 조회한다. 

SELECT * FROM customers;

customers 테이블의 모든 레코드를 조회

이 쿼리를 주(state) 에 따라 그룹으로 묶어 표현할 수 있다.

SELECT * FROM customers
GROUP BY State;

customers 테이블의 모든 레코드를 State 에 따라 그룹화

  • HAVING

HAVING 은 GROUP BY 로 조회된 결과를 필터링할 수 있다. 

SELECT CustomerId, AVG(Total)
FROM invoices
GROUP BY CustomerId
HAVING AVG(Total) > 6.00

invoices 테이블을 CustomerId 로 그룹화하고 그 평균이 6을 초과한 결과를 조회

이 쿼리는 모든 고객의 주문서에서 가격의 평균을 구한 뒤에, 그 평균이 6.00을 넘는 결과만 조회한다.

이렇게 GROUP BY 로 그룹을 지은 결과에 필터를 적용할 때에는 HAVING 을 사용할 수 있다.

HAVING 은 WHERE 과는 적용하는 방식이 다르다. HAVING 은 그룹화한 결과에 대한 필터이고, WHERE 는 저장된 레코드를 필터링한다. 따라서 실제로 그룹화 전에 데이터를 필터해야 한다면, WHERE 을 사용한다.

  • COUNT()

COUNT 함수는 레코드의 갯수를 헤아릴 때 사용한다. COUNT 함수의 사용법은 다음과 같다.

SELECT *, COUNT(*) FROM customers
GROUP BY State;

모든 레코드에 대한 COUNT 함수 사용 예시

위 커맨드를 실제로 실행하면, 각 그룹의 첫번째 레코드와 각 그룹의 레코드 갯수를 집계하여 리턴한다.

다음과 같이 변경하면, 그룹으로 묶인 결과의 레코드 갯수를 확인할 수 있다.

SELECT State, COUNT(*) FROM customers
GROUP BY State;

각 State에 해당하는 레코드의 갯수를 확인하는 COUNT 함수 예시

  • SUM()

SUM 함수는 레코드의 합을 리턴한다. 

SELECT InvoiceId, SUM(UnitPrice)
FROM invoice_items
GROUP BY InvoiceId;
  • AVG()

AVG 함수는 레코드의 평균값을 계산하는 함수다. 

SELECT TrackId, AVG(UnitPrice)
FROM invoice_items
GROUP BY TrackId;
  • MAX(), MIN()

MAX 함수와 MIN 함수는 각각 레코드의 최대값과 최소값을 리턴한다. 

SELECT CustomerId, MIN(Total) // MAX(Total)
FROM invoices
GROUP BY CustomerId

SELECT 실행 순서

데이터를 조회하는 SELECT 문은 정해진 순서대로 동작한다.

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
SELECT CustomerId, AVG(Total)
FROM invoices
WHERE CustomerId >= 10
GROUP BY CustomerId
HAVING SUM(Total) >= 30
ORDER BY 2

위 쿼리문의 실행 순서는 다음과 같다.

  1. FROM invoices : invoices 테이블에 접근을 한다.
  2. WHERE CustomerId >= 10 : CustomerId 필드가 10 이상인 레코드들을 조회한다.
  3. GROUP BY CustomerId : CustomerId를 기준으로 그룹화한다.
  4. HAVING SUM(Total) >= 30 : Total 필드의 총합이 30 이상인 결과들만 필터링한다.
  5. SELECT CustomerId, AVG(Total) : 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구한다.
  6. ORDER BY 2 : AVG(Total) 필드를 기준으로 오름차순 정렬한 결과를 리턴한다.
profile
seongmin
이전 포스트

SQL, NoSQL

다음 포스트

데이터베이스 정규화

0개의 댓글