[SQL입문] MySQL_ERD 다이어그램 생성과 DB 백업 및 불러오기

 

 

---

 

 

데이터 분석을 위한 SQL 입문

 

✔️ ERD

✔️ DB 백업 및 DB 불러오기

 

 

---

 

 

✔️ ERD

 

 

ERD(Entity- Relationship Diagram)

개체-관계 다이어그램

 

 

개체(Entity)

DB에서 관리하고자 하는 대상 또는 객체

예) 고객,제품, 주문 등

일반적으로 사각형(Box)로 표현, 내부 개체 이름이 들어감

 

 

개체의 속성(Attribute)

각 개체는 여러 속성을 가질 수 있음

예) 고객ID, 전화번호 등

타원형 또는 개체 내부에 나열하며

경우에 따라 PK와 같은 중요 속성은 밑줄로 표시

 

+

 

속성의 종류

단순 속성 : 더 이상 분해되지 않는 속성 예) 주민번호

복합 속성 : 여러 하위 속성으로 분해할 수 없는 속성 예) 주소 -> 시,구,동

다중 값 속성 : 하나의 개체가 여러 값을 가질 수 있는 속성 예) 전화번호 여러 개

 

 

관계(Relationship)

두 개체 간의 연관성

예) 고객 - 주문(주문한다라는 관계 성립)

선(line) 또는 다이아몬드로 표현, 선 위나 옆에 관계 이름 개체

 

 

카디널리티(Cardinality)

 

1:1 (일대일) 관계

한 개체가 다른 개체와 1:1 관계 성립

예) 고객 - 주민등록번호 관계 등

 

1:N (일대다) 관계

한 개체가 여러 개체와 관계를 맺고, 반대로 여러 개체가 한 개체와 관계 맺음

예) 다이소 물건 - 고객(고객이 여러 물건 구매)

 

N:M (다대다) 관계

두 개체가 서로 다수의 관계 맺음

중간 연결 테이블(조인 테이블)을 만들어 일대다 관계로 분해 활용

예) 학생 - 수업과목(학생은 여러 과목 듣고, 수업과목은 여러 학생이 참여)

 

 

---

 

 

ERD 작성 고려 사항

명확한 개체 정의 : DB에 포함될 주요 개체 명확한 식별 필요

정확한 관계 설정 : 개체 간의 관계 정의와 카디널리티의 명확화

속성 선택 : 개체 필요 속성과 기본키 포함하여 식별 가능 구조 생성

정규화 : 중복 최소화하고 데이터 무결성 유지 위함 

 

 

 

ERD 실습(MySQL 워크벤치 활용)

 

 

단일 테이블의 비효율성

고객 구매 데이터 확인

구매를 하지 않은 경우는 데이터 없음 -> 공간의 낭비

 

고객 테이블과 구매 테이블 분리

고객 테이블과 구매 테이블로 나누어 저장하게 되면

공간의 낭비X, 대신 물건을 여러 번 구매한 고객의 정보 중복 제거 필요

 

고객 테이블 중복 제거와 PK 설정

고객 테이블의 중복 제거 진행

이름으로 PK 지정 위험 = 동명이인이 존재 가능성

고객 테이블에 고객ID 컬럼을 추가해 PK로 활용

 

구매 테이블 최적화

구매 테이블도 공간의 낭비 사라짐

그러나 누가 무엇을 구매했는지 알 수 없음

고객ID 컬럼을 추가해 명확하게 구별 가능

(고객이름으로 구분하면 동명이인 가능성(중복 데이터 존재 가능성))

 

테이블 간 관계 정의

고객 테이블 - 구매 테이블(1:N의 관계를 가짐)

고객 테이블 고객ID(PK)

구매 테이블 고객ID(FK)

위 데이터를 저장하기 위한 ERD 생성

 

 

 

1. MySQL워크벤치의 File -> New Modle -> 새로운 모델이 생성 ->

생성된 모델 우클릭하여 Edit Schmas 통해 이름 변경

 

 

2. 상단의 Add Diagram ->

ERD Diagram을 통해 새로운 ERD 다이어그램 페이지 생성

 

 

 

3.좌측 기능 선택 중 테이블 생성 선택 ->

테이블을 생성 후 테이블 우클릭 Edit Table을 통해 테이블 세부 설정

 

 

 

4. 1:n 관계를 선택 -> buy_table의 user_name(FK) 선택 ->

user_table의 user_name(PK) 선택 -> 1:N 관계 성립 확인 ->

buy_table에 fk로 설정된 것이 있는지 확인(안됬다면 다시 반복)

 

 

 

5. 생성한 ERD 다이어그램을 Database로 불러오기

상단의 Forward Engineer -> 원하는 서버 접속 ->

생성한 db_model_pratice 불러오기 -> SCHMAS에 db_model_pratice가 생성되었는지 확인

(buy_table 테이블의 세부 설정에서 Foreign Keys 확인)

 

 

 

6. 원하는 데이터베이스를 ERD로 확인

상단의 Reverse Engineer -> 원하는 서버 접속 ->

워크벤치의 샘플데이터 중 sakila 선택 ->  sakila 데이터베이스로 생성된 ERD 확인

 

 

---

 

 

✔️ DB 백업 및 DB 불러오기

 

 

 

데이터베이스의 백업

좌측의 Adimnistration -> Data Export -> Export to Self-Contracted File ->

Include Create Schema -> Start Export

 

 

 

 

데이터베이스 파일을 통한 DB 불러오기

백업된 DB 파일 다운로드 -> Data Import/Restore -> 백업 DB 파일 선택 -> Start Import

 

 

DB 불러오기 실습 (주식/환율 데이터)

 

Data Import를 하고 Schemas에 생성된 데이터베이스 확인하고

환율 DB 활용한 데이터 확인

-- 한국 환율과 주식 데이터셋 -> import 수행 -> DB 확인
show databases;

-- 환율 DB 데이터수 확인
use korea_exchange_rate;
select count(*) from exchange_rate;

-- 2020년 1월부터 12월까지 유로 조회
select 
	* 
from exchange_rate 
where date between '2020-01-01' and '2020-12-31'
and 통화 = '유로 EUR';

-- 2020년 1월 1일부터 12월 31일까지 데이터에서 통화별 현찰_살때_환율의 최소, 최대, 평균가 조회
select 
	통화,
    min(현찰_살때_환율) as 최소환율,
    max(현찰_살때_환율) as 최대환율,
    round(avg(현찰_살때_환율),2) as 평균환율 -- ROUND(값, 소수점 자리)
from exchange_rate 
where date between '2020-01-01' and '2020-12-31'
group by 통화
order by 통화;

 

 

 

주식 정보 DB 활용한 데이터 확인

-- 주식 DB 확인
use korea_stock_info;
show tables;

-- 테이블의 데이터 확인
select * from stock_company_info;

-- 월별 주식 가격 데이터 확인
select * from 2024_07_stock_price_info;
select * from 2024_08_stock_price_info;

-- 7, 8,9월 나눠져 있는 데이터를 1개의 테이블로 모아서 생성
-- JOIN은 사용할 수 없음(중복되는 값이 너무 많음)
-- UNION을 사용하여 1개의 테이블로 모음

create table stock_2024_all as (
	select * from 2024_07_stock_price_info
	union all
	select * from 2024_08_stock_price_info
	union all
	select * from 2024_09_stock_price_info
);
-- CSV로 바로 저장

-- 생성된 테이블 데이터 확인
select
	*
from stock_2024_all;

 

+

 

SQL로 조회한 데이터 CSV 파일로 저장

 

 

---

 

 

데이터 분석을 위한 SQL 입문_3

ERD 다이어그램과 데이터베이스 백업 및 불러오는 방법에 대해 알아보았습니다.

자세한 내용은 혼자 공부하는 SQL 블로그 글도 참고해주시기 바랍니다.

 

'혼공시리즈/혼공S_혼자 공부하는 SQL' 카테고리의 글 목록