오래된 개발자!!!

정규화(normalization) 에 대해 ChatGPT 에 물어 보았다.

관계형 데이터베이스 설계 시 데이터 중복을 줄이고 무결성을 유지하기 위한 정규화 과정입니다. 각 단계에서 데이터 구조가 개선됩니다.

1차 정규화 (1NF)

• 목표: 테이블 내 모든 필드가 원자값(더 이상 나눌 수 없는 값)을 갖도록 만듦.
• 조건: 각 컬럼에 중복되는 데이터나 여러 값이 포함되지 않도록, 하나의 셀에는 하나의 값만 존재해야 함.

예시:

• 전화번호가 하나의 컬럼에 "010-1234-5678, 010-9876-5432"처럼 들어있으면 1NF가 아님.
• 이를 각각의 행으로 분리하거나 별도의 테이블로 분리하여 원자값을 만듦.

2차 정규화 (2NF)

• 목표: 부분 함수 종속을 제거.
• 조건: 1NF를 만족하고, 테이블의 모든 비주요 속성이 기본키에 완전 함수 종속되어야 함. 즉, 기본키의 일부분에만 종속되는 속성들을 제거.

예시:

• 학생 테이블에서 학번과 과목으로 성적을 관리하는 경우, 과목명에 대한 정보는 학번과 관계없이 과목에만 종속됨. 이를 별도의 테이블로 분리하여 성적 테이블은 학번과 과목만 다루고, 과목 정보는 과목 테이블로 관리.

3차 정규화 (3NF)

• 목표: 이행적 함수 종속을 제거.
• 조건: 2NF를 만족하고, 기본키가 아닌 속성들이 서로 종속되지 않아야 함. 즉, 비주요 속성이 다른 비주요 속성에 의존하지 않도록 분리.

예시:

• 직원 테이블에 직원 ID, 부서 ID, 부서명 등이 있는 경우, 부서명은 부서 ID에만 종속되어야 함. 이때 부서 정보를 따로 분리하여 관리.

이러한 정규화 과정을 통해 데이터 중복을 최소화하고, 데이터 일관성과 무결성을 유지할 수 있습니다.

더 쉽게 설명해달라고 해보았다.

정규화는 데이터베이스에서 데이터를 깔끔하고 효율적으로 관리하기 위한 과정이에요. 1차, 2차, 3차 정규화는 데이터를 단계별로 더 잘 정리하는 방법입니다. 쉽게 설명하면:

1차 정규화 (1NF)

• 뭐하는 거야?: 각 칸에 하나의 정보만 넣기.
• 예시: "전화번호" 칸에 여러 개의 번호가 들어 있으면 안 돼요. 각각의 번호를 따로 칸에 넣어야 해요.
  • 잘못된 예시: 전화번호: 010-1234-5678, 010-9876-5432
  • 고친 예시: 전화번호: 010-1234-5678 (각 번호를 따로 관리)

2차 정규화 (2NF)

• 뭐하는 거야?: 주요한 정보(기본키)에만 의존하도록 정리하기.
• 예시: 학생의 학번과 과목별 성적을 기록하는데, 과목 이름과 학번이 섞여 있으면 안 돼요. 과목 정보는 따로 테이블로 만들어서 관리하면 깔끔해요.
  • 잘못된 예시: 학번 + 과목 이름 + 성적
  • 고친 예시: 과목은 따로 과목 테이블에서 관리하고, 성적 테이블은 학번과 과목 ID만 기록.

3차 정규화 (3NF)

• 뭐하는 거야?: 불필요한 연결 없애기.
• 예시: 직원 테이블에 부서 이름이 들어가 있으면, 나중에 부서 이름이 바뀔 때 여러 곳을 고쳐야 할 수 있어요. 이럴 땐 부서 ID만 넣고, 부서 정보는 부서 테이블에서 따로 관리하면 좋아요.
  • 잘못된 예시: 직원ID + 부서 이름
  • 고친 예시: 직원ID + 부서ID (부서 이름은 부서 테이블에서 따로 관리)

정리하면:

1. 각 칸에 하나의 정보만 넣고,
2. 중복된 정보는 따로 떼어내서,
3. 관련 없는 정보끼리는 따로 보관해요.

이렇게 하면 데이터가 덜 중복되고, 나중에 변경하기 쉬워집니다!

소숫점 자리수를 표시할 때 화면에서 정렬을 위해 0으로 채워달라는 경우가 있다.

예 ) 0.123 -> 0.12300

FM (Fill Mode) 을 사용하자.

SELECT TO_NUMBER('0.123') AS VAL FROM DUAL

-> 0.123

SELECT TO_CHAR(TO_NUMBER('0.123'),'FM999,999,990.00000') AS VAL FROM dual;

-> 0.12300

많이 사용하는 라운드(ROUND:반올림)와 같이

SELECT TO_CHAR(ROUND(TO_NUMBER('0.123'),5),'FM999,999,990.99999') AS VAL FROM dual;

FM 에 대해서 공부하려면 

https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00210

끝 

Table 복사

* 테이블 변경 시 테이블 자체를 백업 해 놓고 싶을때 자주 사용.

CREATE TABLE [TBL_NEW_NAME] AS SELECT * FROM [TBL_NAME]

CREATE TABLE TBL_MEMBER_BACKUP AS SELECT * FROM TBL_MEMBER

주로 사용하는 표현

column_name like '%'|| '검색어' || '%'

대소문자 구분 없이 검색하려면

UPPER(column_name) like '%'|| UPPER( '검색어') || '%'

regexp_like 로 한방에

regexp_like( column_name , '검색어' , 'i' )

대소문자 구별해야 할 때는
regexp_like( column_name , '검색어' )

사용 /*+ parallel( ) */ 

SELECT
      /*+ parallel(TBL1,4) parallel(TBL2,4) */ 
FROM TBL_TEST1 TBL1, TBL_TEST2 TBL2
WHERE TBL1.USER_NAME = TBL2.USER_NAME

임시 정리

 mdate (속성 timestamp) 컬럼을 '2020-11-01' 에서 '2020-11-06' 일 까지 검색

# 잘못 됨
WHERE to_char(mdate,'YYYY-MM-DD') BETWEEN '2020-11-01' AND '2020-11-06'

# 권장하지 않음
WHERE mdate::date BETWEEN '2020-11-01'::date AND '2020-11-06'::date

데이터가 많은 테이블에서 timestamp 를 to_char 로 변한하면 안 됨.  Index 안 탐.

개선

# mdate 컬럼 속성을 변경하지 않아야 함
WHERE mdate >= '2020-11-01'::timestamp AND mdate < '2020-11-06'::timestamp + interval '1 day'

--> 앞으로 고민 해 봐야 할것

between 을 사용하고 싶은데... + interval '+1day -1second' 이렇게 해도 되긴되는데 

12 버전 이상에서는 rownum 으로 처리 할 필요가 없다.

OFFSET 시작번호 ROWS FETCH NEXT 페이지보여줄갯수 ROWS ONLY

OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

select 
    user_id
   , user_name
from tbl_user
order by user_id
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

* 주의!!! 성능문제가 발생 한다고 하네요. 이런 방법도 있구나 하고 읽어 주세요 ^^

OFFSET 시작번호 limit 페이지보여줄갯수

OFFSET 0 limit 20;

select 
    user_id
   , user_name
from tbl_user
order by user_id
OFFSET 0 limit 20;

ORACLE 문법 도 된다.

OFFSET 시작번호 ROWS FETCH NEXT 페이지보여줄갯수 ROWS ONLY

OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

select 
    user_id
   , user_name
from tbl_user
order by user_id
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

되는구나! 

테이블 명 (table name) 이나 컬럼명(column name) 이 맞는지 확인!

거의 오타 때문이다! 

ㅠㅠ

끝.

psql 실행 후 원하는 테이블을 조회하면서 csv 파일로 복사한다.

copy (select * from 테이블명) to '/원하는경로/result.csv' with csv 

끝이다.

=# copy (SELECT * FROM table_demo) to '/home/demo/table_demo_data.20190731.csv' with csv

* CentOS 에서 기본으로 postgresql 가 설치되어있는지 확인한다.

~]# rpm -qa | grep postgres
postgresql-docs-9.2.24-1.el7_5.x86_64
postgresql-libs-9.2.24-1.el7_5.x86_64
postgresql-9.2.24-1.el7_5.x86_64
postgresql-server-9.2.24-1.el7_5.x86_64

* 설치된 것이 있다면 삭제 한다.

~]# yum remove postgresql*

* PostgreSQL 11 버전을 설치 하기 위해서는 Yum repository 를 업데이트 해야 한다.

~]# rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm 

* PostgreSQL 11 을 설치 한다.

~]# sudo yum install -y postgresql11-server postgresql11-contrib 

postgresql11-server : PostgreSQL 11 Database Server
postgresql11-contrib : 추가 지원 모듈

주의! 설치 후 최초 Database 를 생성 해야 한다 !

~]# /usr/pgsql-11/bin/postgresql-11-setup initdb 

* 서비스 등록 및 실행 

~]# systemctl start postgresql-11 
~]# systemctl enable postgresql-11 

* 서비스 상태 확인

~]# systemctl status postgresql-11 

* 관리자 passsword 변경

~]# su - postgres -c 'psql' 
=# ALTER USER postgres PASSWORD '변경할비밀번호'; 

-- postgres 사용자 권한으로 psql 명령어 실행 

* 외부 접속 허용

~]# vi /var/lib/pgsql/11/data/postgresql.conf  

-- 중략 --
listen_addresses = '*' 

-- 중략 --

~]# vi /var/lib/pgsql/11/data/pg_hba.conf 

-- 중략 --

host      all             all         0.0.0.0/0      md5 

* 외부 접속 허용 후 서비스를 Restart 해 줘야 한다.

~]# systemctl restart postgresql-11 

* 방화벽 허용

~]# firewall-cmd --permanent --zone=public --add-port=5432/tcp 
~]# firewall-cmd --reload

PostgreSQL 프로시져 를 구현 하고자 할때 Function 을 만들어 사용한다.

(최신버전 11 부터는 procedure 를 지원한다.)

결과가 없는 Function을 실행 할 때에는 select 대신 PERFORM 을 사용하자.

PERFORM FUNCTION_NAME();

결과가 있는 Function 이라면 당연히 select 를 사용한다.

SELECT FUNCTION_NAME();

터미널 에서 postgres 사용자 전환 후 psql 을 실행 한다.

~]# su - postgres

~]$ psql

적용할 데이터베이스 로 이동 후 pgcrypto 를 설치 한다.

# \c demo_db

# CREATE EXTENSION pgcrypto;

서버 IP 변경 후 Mongo DB Replicat Set 이 말썽 이다.

변경해 보자

* Replica Set 은 Primary 에서 작업 해야 한다.

> rs.status()
 {
        "startupStatus" : 1,
        "ok" : 0,
        "errmsg" : "loading local.system.replset config (LOADINGCONFIG)"
}

오류가 있다. "errmsg" : "loading local.system.replset config (LOADINGCONFIG)"

> var cfg = rs.config()

> cfg.members[0].host = "192.168.0.101:27017"

> cfg.members[1].host = "192.168.0.102:27017"

> rs.reconfig(cfg,{force:true})

먼저 postgres 로 사용자 변경을 한다.

 ~]# su - postgres

만약 db name 이 test_db 라면 백업 해 보자

 ~]$ pg_dump test_db > test_db.dump

좀더 자세히 알고 싶다면 아래 내용을 확인해 보자.

pd_dump 매뉴얼 에 설명된 내용이다.

EXAMPLES
    To dump a database called mydb into a SQL-script file:

       $ pg_dump mydb > db.sql

    To reload such a script into a (freshly created) database named newdb:

       $ psql -d newdb -f db.sql

    To dump a database into a custom-format archive file:

       $ pg_dump -Fc mydb > db.dump

    To dump a database into a directory-format archive:

       $ pg_dump -Fd mydb -f dumpdir

    To reload an archive file into a (freshly created) database named newdb:

       $ pg_restore -d newdb db.dump

    To dump a single table named mytab:

       $ pg_dump -t mytab mydb > db.sql

    To dump all tables whose names start with emp in the detroit schema, except for the table named 

    employee_log:

       $ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

    To dump all schemas whose names start with east or west and end in gsm, excluding any schemas 
    whose names contain the word test:

        $ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

    The same, using regular expression notation to consolidate the  switches:

       $ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

    To dump all database objects except for tables whose names begin with ts_:

       $ pg_dump -T 'ts_*' mydb > db.sql