관계형 데이터베이스 설계
1. 개념
- 구조화된 데이터는 하나의 테이블로 표현할 수 있음
- 사전에 정의된 테이블을 relation 이라고도 부름
핵심 키워드
- 데이터(data)
: 각 항목에 저장되는 값 - 테이블(table 또는 relation)
: 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적 - 칼럼(column 또는 field)
: 테이블의 한 열(Column)을 의미하며, 테이블에 저장된 모든 항복에는 해당 필드가 존재 - 레코드(record 또는 tuple)
: 테이블의 한 행(row)에 저장된 데이터 - 키(key)
: 테이블의 각 레코드를 구분할 수 있는 값으로 각 레코드마다 고유한 값을 가짐
: 기본 키(Primary key)는 다중 컬럼에 부여 가능
: 외래 키(foreign key)는 다른 테이블의 Primary key를 참조할 때 해당 값
:REFERENCES라는 SQL을 사용해서 FOREIGN KEY로 다른 테이블과 관계를 형성
- 데이터(data)
2. 관계의 종류
1) 1:1 관계
- 하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우
- 1:1 관계는 자주 사용하지 않음
1:1 관계(One-to-one relationship) 예시
- Phonebook 테이블 : phone_id와 phone_number를 가지고 있음
- User 테이블 : user_id, name, phone_id를 가지고 있음
*phone_id : 외래키(foreign key)로 Phonebook 테이블의 phone_id와 연결됨 - 각 전화번호가 단 한 명의 유저와 연결되어 있고 그 반대도 동일하다면, User 테이블과 Phonebook 테이블은 1:1 관계에 해당
- 1:1로 나타낼 수 있는 관계라면 User 테이블에 phone_id를 대신해 phone_number를 직접 저장하는 게 나을 수 있음
2) 1:N 관계
- 하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우
- 관계형 데이터베이스에서 가장 많이 사용
1:N 관계(One-to-many relationship) 예시
- 한 명의 유저가 여러 전화번호를 가질 수 있으나 여러명의 유저가 하나의 전화번호를 가질 수는 없음
- 한 명의 유저가 여러 전화번호를 가질 수 있으나 여러명의 유저가 하나의 전화번호를 가질 수는 없음
3) N:M 관계
- 여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우
- N:M(다대다) 관계를 위해 스키마를 디자인할 때에는, Join 테이블을 만들어 관리
- 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있음
N:M 관계(many-to-many relationship) 예시
- Package 테이블 : 여러 개의 여행 상품이 존재
- Customer 테이블 : 여러 명의 고객이 존재
- 고객 한 명은 여러 개의 여행 상품을 구매할 수 있고, 여행 상품 하나는 여러 명의 고객이 구매할 수 있어 N:M 관계에 해당
- 두 개의 테이블과 1:N(일대다) 관계를 형성하는 새로운 테이블로 N:M(다대다) 관계를 나타낼 수 있음
- customer_package 테이블 : 고객 한 명이 여러 개의 여행 상품을 가질 수 있고, 여행 상품 하나가 여러 개의 고객을 가질 수 있음
*customer_id와 package_id를 묶어주는 조인 테이블로, 조인 테이블을 위한 기본키(cp_id) 필요
4) self referencing 관계
- 테이블 내에서도 관계가 필요한 경우 존재
자기참조 관계(Self Referencing Relationship) 예시
- User 테이블 : user_id는 기본 키(primary key), name은 사용자의 이름, 그리고 recommend_id는 추천인 아이디
- recommend_id는 User 테이블의 user_id와 연결
- 한 명의 유저(user_id)는 한 명의 추천인(recommend_id)를 가질 수 있으나 여러 명이 한 명의 유저를 추천인으로 등록할 수 있음
*1:N(일대다) 관계와 유사하나 일반적으로 일대다 관계는 서로 다른 테이블의 관계를 표현
Schema
- 데이터베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티 간의 관계에 대한 설명으로, 데이터베이스의 청사진을 의미
*데이터를 정의하고, 데이터 간의 관계를 구성하기 위해 사용 - SQL을 사용하려면 형식이 고정된 스키마가 필요
entities(엔티티)
- 고유한 정보의 단위로 데이터베이스에서 테이블로 표시
- 각 엔티티에는 해당 엔티티의 특성을 설명하는 필드(field)가 존재
: 엔티티는 users, orders, items, orders_items
: users 엔티티의 필드는 id, username, email, password, nickname
SQL에서 사용되는 Query
// 위의 스키마 구성에서 박소민의 모든 주문내역중 주문 아이디와 주문액의 합계를 가져오는 쿼리문
select orders.id, orders.total_price from orders
inner join users on users.id = orders.user_id
where users.username = "박소민";1. 집합연산 관련 SQL 내장함수
- 집합연산 : 레코드를 조회하고 분류한 뒤, 특정 작업을 하는 연산
1) GROUP BY
SELECT * FROM customers
GROUP BY State;- customers 테이블의 모든 레코드를 State에 따라 그룹화
- 데이터베이스에서 데이터를 불러오는 과정에서 State에 따라 그룹을 지정했지만, 그룹 대한 작업없이 조회만 했기 때문에 쿼리의 결과로 나타나는 데이터는 각 그룹의 첫번째 데이터만 표현
2) HAVING
SELECT CustomerId, AVG(Total)
FROM invoices
GROUP BY CustomerId
HAVING AVG(Total) > 6.00
// invoices 테이블을 CustomerId로 그룹화하고 그 평균이 6을 초과한 결과를 조회- GROUP BY로 그룹을 지은 결과에 필터를 적용할 때에는 HAVING을 사용
- HAVING : 그룹화한 결과에 대한 필터
- WHERE : 저장된 레코드를 필터링
- HAVING은 WHERE과는 적용하는 방식이 달라 그룹화 전에 데이터를 필터해야 한다면 WHERE을 사용
3) COUNT()
SELECT *, COUNT(*) FROM customers
GROUP BY State;
// 모든 레코드에 대한 레코드 개수를 확인
// 각 그룹의 첫번째 레코드와 각 그룹의 레코드 개수를 집계하여 리턴
SELECT State, COUNT(*) FROM customers
GROUP BY State;
// 그룹으로 묶인 결과의 레코드 개수를 확인
// 각 State에 해당하는 레코드 개수를 확인- COUNT 함수 : 레코드의 개수를 헤아릴 때 사용
4) SUM()
SELECT InvoiceId, SUM(UnitPrice)
FROM invoice_items
GROUP BY InvoiceId;
// invoice_items 테이블에서 InvoiceId 필드를 기준으로 그룹하고, UnitPrice 필드 값의 합을 구함- SUM 함수 : 레코드의 합을 리턴
5) AVG()
SELECT TrackId, AVG(UnitPrice)
FROM invoice_items
GROUP BY TrackId;- AVG 함수 : 레코드의 평균값을 계산
6) MAX(), MIN()
SELECT CustomerId, MIN(Total)
FROM invoices
GROUP BY CustomerId;
// MIN을 MAX로 변경하면, 각 고객이 지불한 최대 금액을 리턴- 각각 레코드의 최대값과 최소값을 리턴
2. SELECT 실행 순서
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
SELECT CustomerId, AVG(Total) FROM invoices WHERE CustomerId >= 10 GROUP BY CustomerId HAVING SUM(Total) >= 30 ORDER BY 2
- FROM invoices : invoices 테이블에 접근
- WHERE CustomerId >= 10 : CustomerId 필드가 10 이상인 레코드들을 조회
- GROUP BY CustomerId : CustomerId를 기준으로 그룹화
- HAVING SUM(Total) >= 30 : Total 필드의 총합이 30 이상인 결과들만 필터링
- SELECT CustomerId, AVG(Total) : 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구함
- ORDER BY 2 : AVG(Total) 필드를 기준으로 오름차순 정렬한 결과를 리턴
- 데이터를 조회하는 SELECT 문은 정해진 순서대로 동작
✏️