[TIL] 45일차 TIL(20230410) - DBMS 기초, SQL 데이터 탐색

 

 

[TIL] 45일차 TIL(20230410) - DBMS 기초, SQL 데이터 탐색

 

 

 

📗 데이터 분석 개요

 

✅ 데이터 분석은 질문에서 시작한다.

ex) 지난 달에 비해 신규 고객이 얼마나 많이 유입되었는가? 월별 판매 추이가 어떠한가?

ex) 이탈 고객과 충성 고객의 행동 패턴은 어떻게 다른가?

 

이러한 질문에 답하기 위한 분석 지표를 설정한다.

 

 

✅ 질문에 답하기 위한 데이터는 어디에서 나오고 어디에 서장되는가?

ETL(추출, 변환, 로드) > 데이터 웨어하우스(데이터베이스에 축적된 데이터를 공통의 형식으로 묶어서 관리, 하나로 통합)

 

 

✅ 데이터의 종류에 따른 특성

1. 업무 데이터

- 기업의 비즈니스 결과로 생성된 데이터

- 마스터 데이터(정보 데이터): 고객정보, 상품정보, 카테고리정보

- 트랜잭션 데이터(행동 데이터): 구매, 배송, 리뷰작성 등

- 갱신형, 정합성, 정확도가 높고, 정규화가 잘 되어있음

 

2. 로그 데이터

- 사용자의 접속시간, 클릭 이벤트, ip, 기기, 세션 등의 정보를 저장한 데이터

- 사용자 행동 분석을 통해 웹/앱의 UI/UX를 개선하고자 할 때 주로 사용

- 정확도는 업무데이터에 비해 낮음(크롤러의 로그 포함 고려 필요)

- 누적형

 

 

✅ 데이터 분석 과정

1. 탐색: 데이터와 관련된 주제? 데이터는 어디서 생성? 저장된 테이블은 어디?

2. 프로파일링: 데이터 정합성, 품질, 분포 등을 파악(이상치, 결측값, 중복값 등)

3. 정제: 잘못된 데이터, 불완전한 데이터, 결측값 처리, 형변환

4. 셰이핑: 분석할 결과 테이블을 만드는 과정(조인, 집계 등)

5. 분석: 통계 시각화 등

 

 

 

📗 SQL 기초

 

✅ ERD

schema 우클릭 > 다이어그램 보기

 

 

✅ 테이블 관계 표기

대부분은 IE 표기법을 사용하지만, postgreSQL은 IDEF1X 표기법을 사용한다.

 

 

✅ 속성(attribute, column)

1. 기본 속성: 업무로부터 추출한 속성

2. 설계 속성: 모델링을 위해 생성된 속성 ex) 코드, 일련번호

3. 파생 속성: 다른 속성으로부터 계산되거나 변형된 속성  ex) 총 주문금액, 할인 적용된 금액

 

 

✅ 식별자 - 각각의 개체를 구분할 수 있는 결정자

 

① 대표성 여부

- 주 식별자: primary key, 유일성, 최소성, 불변성, 존재성(NULL X)  ex) 사용자 id

- 보조 식별자: unique로 지정  ex) 주민번호, 이메일 주소

 

② 속성의 수

- 단일 식별자: 하나의 속성으로만 이루어진 식별자

- 복합 식별자: 두개 이상 속성으로 이루어진 식별자

 

③ 스스로 생성 여부

- 내부 식별자: 엔티티 내부에서 스스로 만들어지는 식별자

- 외부 식별자: 타 엔티티와의 관계를 통해 타 엔티티로부터 받아오는 식별자

 

④ 대체 여부

- 본질 식별자: 업무에 의해 만들어지는 식별자

- 인조 식별자: 인위적으로 만든 식별자  ex) 일련번호

 

 

✅ 정규화

 

① 제 1 정규화 - 데이터의 중복 제거

- 중복된 값을 가진 여러 컬럼이 존재하여 일부가 중복되는 행이 생기는 경우, 해당 컬럼을 따로 빼주는 방식으로 정규화

 

판매일자	부서코드	...	판매순번	제품번호	단가
2000.1.1	A01	...	1	A001	100
2000.1.1	A01	...	1	A002	50
2000.1.1	A01	...	2	A003	100
2000.1.1	A01	...	1	A004	20

 

위 경우에는 판매일자, 부서코드, 판매순번이 모두 중복된 경우가 존재하여 해당 컬럼을 따로 뺀 상태로 제 1 정규화를 진행할 수 있다.

 

 

- 한 컬럼에 여러 값이 동시에 들어있는 경우, 한 컬럼에 하나의 값만 들어가도록 정규화

 

부서코드	제품번호
A01	A101, A102
A02	A201
A03	A301
A04	A401, A402

 

부서코드	제품번호
A01	A101
A01	A102
A02	A201
A03	A301
A04	A401
A04	A402

 

위 경우에는 한 컬럼에 ,로 구분되어서 여러 값이 들어있다. 이 경우 한 컬럼에 하나의 값들만 들어가도록 행으로 쪼개서 제 1 정규화를 진행할 수 있다. 그 결과는 아래와 같다.

 

 

② 제 2 정규화 -  주 식별자 항목 중 코드화 할 수 있는 속성 분리

- 제 1 정규화를 만족한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것

- '완전 함수 종속'은 식별자의 부분집합이 결정자가 되어서는 안된다는 것이며, 결정자는 어떤 속성의 값이 다른 속성의 값을 고유하게 결정할 수 있는 것을 의미한다.

 

 

왼쪽 테이블을 보면 판매전표의 식별자 중 하나인 [부서코드]는 [부서명, 부서실적]을 결정짓는 하나의 고유한 키가 된다. 이는 완전 함수 종속을 위배하므로, 해당 테이블을 따로 분리하여 우측과 같은 [부서] 테이블을 생성하여 제 2 정규화를 진행할 수 있다.

 

 

③ 제 3 정규화 -  일반속성 항목 중 코드화 할 수 있는 속성 분리

- 일반속성 내의 항목들 중 여전히 서로 특정지을 수 있는 속성이 존재한다면, 해당 속성들을 따로 분리해주는 제 3 정규화를 진행한다.

 

 

제 2 정규화한 테이블에서 [고객번호]는 [고객명, 고객주소, 누적판매량, 채권액] 등 고객에 대한 정보를 특정지을 수 있는 속성이다. 이 속성들을 하나의 테이블로 분리하는 것이 제 3 정규화이다. 오른쪽 그림에서 해당 속성들이 [고객]이라는 새로운 테이블로 분리된 것을 확인할 수 있다.

 

 

✅ 테이블 정보 조회

 

1. 테이블 목록 조회

select * from pg_tables where schemaname = 'northwind' order by 2;

 

2. 컬럼별 상세 정보

select table_name as 테이블명
, column_name as 컬럼명
, column_default as 디폴트값
, is_nullable as null가능여부
, data_type as 자료형
, character_maximum_length as 문자열최대자리수
from information_schema.columns
where table_schema ='northwind'
order by table_name, ordinal_position;

 

3. 제약조건 정보

select table_name, column_name, constraint_name 
from information_schema.constraint_column_usage
where table_schema = 'northwind'
order by 1, 3 desc;

 

 

 

📗 Northwind 스키마 product 테이블 탐색

 

 

 

---

 

 

소감 및 정리

SQL 오프라인 수업의 첫 날이 끝났다. 첫날이지만 상당히 많은 부분에 대해서 배웠는데, 특히 그동안 미흡했던 데이터베이스 구조에 대한 내용을 배울 수 있었다. 테이블 사이의 관계를 표시한 ERD와 ERD에서 선의 의미에 대해서 배우며, 전체적인 DB구조를 파악하는 법을 배웠다. 또 데이터베이스 정규화 방법론에 대해서도 배웠는데, 쉬운 개념은 아니었지만 반복적으로 살펴보며 이해할 수 있었다. 또 간단한 팀별 과제를 진행했는데, 특정 테이블에 대해 구체적인 내용을 파악해보는 과정을 거치며 쿼리 작성 방법을 떠올리는 기회가 되었고, 앞으로 진행할 분석에 앞서 데이터셋의 내용을 살펴볼 수 있었다. 첫 날이지만 상당히 많은 내용을 배울 수 있었다! 내일도 화이팅😊