[DB][데이터베이스 설계]

데이터베이스 설계

관계형 데이터베이스 구조화된 데이터는 하나의 테이블로 표현할 수 잇다. 사전에 정의된 테이블을 relation이라고 부르기 때문에 테이블을 사용하는 데이터베이스를 관계형 데이터베이스라고한다.

-데이터 : 각 항목에 저장되는 값

-테이블(table;또는 relation) : 사전에 정의된 열의 데이터 타입대로 작성된 데이터가 행으로 축적된다.

-칼럼(column;field) : 테이블의 한 열을 가리킨다

-키(Key) : 테이블의 각 레코드를 구분할 수 있는 값. 각 레코드마다 고유한 값을 지닌다. 기본키(primary key)와 외래키(foreign key)등 있다.

 

관계 종류

테이블과 테이블 사이의 관계

-1:1 관계

하나의 레코드가 다른 테이블의 레코드 한개와 연결된 경우를 뜻하며 잘 사용안함. 1:1로 나타낼 수 있는 관계면 직접 저장하는게 나을 수 있음.

 

-1:N 관계 (많이사용)

하나의 레코드가 서로 다른 여러개의 레코드와 연결된 경우 user테이블과 phonebook테이블의 관계로 설명하자면,

User table과 Phonebook tabledml 1:N 관계

이 구조에서는 한명의 유저가 여러 전화번호를 가질 수 있다 그러나 여러명의 유저가 하나의 전화번호를 가질 수 없다.

 

-N:N 관계

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

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

이렇게 Customer테이블과 Package 테이블이 따로 존재하면, N:N 관계를 설정하기 복잡하지만 두개의 테이블 사이에 테이블을 하나 더 놓고 1:N 관계를 형성하게하면 다음과 같다.

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

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

 

-자기참조 관계(Self Referencing Relationship)

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

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

 

User테이블의 recommend_id는 User테이블의 user_id와 연결되어있다. 한명의 유저는 한명의 추천인을 가질 수 있따 그러나 여러명이 한명의 유저를 추천인으로 등록할 수 있따 이 관계는 1:N관계와 유사하다 생각할 수 있지만 일반적으로 일대다 관계는 서로 다른 테이블의 관계를 나타낼 때 표현하는 방법이다.

 

SQL More

 

SQL 내장함수

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

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

HAVING : GROUP BY에서 조회된 결과를 필터링 할 수 있다.

COUNT() : 레코드의 갯수를 헤아릴 때 사용한다.

SUM() : 레코드의 합을 리턴

AVG() : 레코드의 평균값을 리턴

MAX(),MIN() : 최대값과 최소값을 리턴

 

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

[커맨드] SELCT 문의 실행 순서 예시

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) 필드를 기준으로 오름차순 정렬한 결과를 리턴한다.