Copy Coding

DBA는 아니어도 개발자로서 기본적인 소양을 갖추기 위해서 한번쯤 공부해도 좋은 SQL 개발자 자격증 시험에 대해 조금 정리를 해보았습니다.  시험과목과 점수는 아래와 같습니다.

- 검정 시간 90분(1시간 30분)

- 합격 기준 : 총점 60점 이상(과목별 40% 미만 과락)

- 50문제 중 30문제 맞추면 합격

- 20문제를 틀리면 합격. 포기할 부분은 포기(?)

SQL 활용 부분의 뒤쪽은 일반적인 프로젝트에 잘 사용하지 않는 부분이라 초보자에게는 어려울 수 있을 것 같습니다.  물론 이 책에 모든 쿼리가 다 들어있지 않기 때문에 사용하게 되는 데이터베이스에 맞추어 추가적인 학습을 해야 실전에서는 사용이 가능 하겠지만 기초를 쌓기에는 좋을 것 같습니다. 

아래 내용은 SQL 개발자 자격증에 대한 서적 중 [이기적 SQL 개발자]를 참고하여 요약을 하였습니다.

I. 데이터 모델링

1.1 데이터 모델링의 특징

1.2 데이터 모델링 단계

1.3 데이터 모델링 관점

1.4 ERD(Entity Relationship Diagram)

ERD 작성 절차

(1) Entity를 도출 하고 그린다.

(2) Entity를 배치 한다.

(3) Entity 간의 관계를 설정 한다.

(4) 관계 명을 서술 한다.

(5) 관계 참여도를 표현한다.

(6) 관계의 필수 여부를 표현한다.

2. 3층 스키마(3-Level Schema)

2.1 3층 스키마란

- 사용자, 설계자, 개발자가 데이터베이스를 보는 관점에 따라 기술한 ANSI 표준

- 데이터베이스의 독립성을 확보하기 위한 방법

- 독립성이 확보되면 대응력 향상, 관리 및 유지보수 비용 절감 등의 장점이 있다.

2.2 3층 스키마의 독립성

2.3 3층 스키마 구조

3층 스키마 구조

3. 엔터티(Entity)

실체, 객체. 실세계에서 개별적으로 인식될 수 있는 것. Table을 의미 한다.

3.1 Entity에 대한 정의

변별할수 있는 사물 - Peter Chen (1976) -

데이터베이스 내에서 변별 가능한 객체 - C.J Date (1986) -

정보를 저장할 수 있는 어떤 것 - James Martin (1989) -

정보가 저장될 수 있는 사람, 장소, 물건, 사건 그리고 개념 등 - Thomas Bruce (1992) -

3.2 Entity 도출

엔터티는 사람, 장소, 물건, 사건, 개념 등의 명사에 해당한다.

엔터티는 업무상 관리가 필요한 관심사에 해당한다.

엔터티는 저장이 되기 위한 어떤 것(Thing)이다.

엔터티는 인스턴스의 집합

3.3 Entity 특징

3.4 Entity 종류

1) 유형과 무형에 따른 엔터티 종류

2) 발생 시점에 따른 엔터티 종류

4. 속성(Attribute)

4.1 속성

1) 사전적 의미

- 사물(事物)의 성질, 특징 또는 본질적인 성질, 그것이 없다면 실체를 생각할 수 없는 것

- 본질적 속성이란 어떤 사물 또는 개념에 없어서는 안 될 징표(徵表)의 전부

- 이 징표는 사물이나 개념이 어떤 것인지를 나타내고 그것을 다른 것과 구별하는 성질

- Table의 column을 의미 한다.

2) 데이터 모델링 관점

- 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

- 업무상 관리하기 위한 최소의 의미 단위

- 의미상 더 이상 분리되지 않는다.

- 엔터티를 설명하고 인스턴스의 구성요소가 된다.

4.2 속성의 종류

1) 분해 여부에 따른 속성의 종류

2) 특성에 따른 속성의 종류

4.3 도메인

속성이 가질 수 있는 값의 범위

5. 관계(Relationship)

5.1 관계

Entity 간의 관련성을 의미하며 존재 관계와 행위 관계로 분류 된다.

5.2 관계의 분류

1) 존재 관계

- Entity간의 관계가 존재 상태로 연결 되는 것. (고객이 은행지점에서 회원가입 하면 관리지점이 할당되고 관리 된다.)

2) 행위 관계

- Entity간의 관계가 행위로서 연결 되는 것. (계좌를 이용해서 주문을 하면 주문이력이 발생)

5.3 관계 차수

1) 1:1(One To One)

2) 1:M (One To Many)

- 하나의 엔터티에 관계된 엔터티에 값이 여러개 있는 관계.

- 고객 엔터티와 구매내역 엔터티의 경우 고객이 많은 구매내역을 가질 수 있다.

3) M:N (Many to Many)

- 두개의 엔터티가 서로 여러개의 관계를 가지고 있다.

- 학생과 과목 엔터티의 경우 한 명의 학생이 여러 개의 과목을 수강하거나, 하나의 과목을 여러 학생이 수강하는 경우 M대N 관계가 발생 한다.

- M대N의 관계 조인은 카데시안 곱이 발생 하며 1대N, N대1로 분리 하여야 한다.

4) 선택성

6. 엔터티 식별자(Entity Identifier)

식별자의 개념

Entity 내에서 Instance 들을 구분할 수 있는 구분자

6.1. 주식별자의 특징(기본키, Primary key)

6.2 데이터베이스 키의 종류

6.3 식별자의 종류

1) 식별자의 대표성

2) 생성 여부

3) 속성의 수

4) 대체 여부

II. 데이터 모델과 성능

1. 정규화

- 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 데이터 분해 과정

- 함수적 종속성을 이용하여 잘못 설계된 스키마를 쪼개어 분해하는 과정으로 하나의 릴레이션에 하나의 함수적 종속성만 존재하도록 정규화 한다.

- 정규화를 통해 추상화가 되면 비즈니스가 변화해도 수정사항이 최소화된다.

- 정규화는 1정규화에서 5정규화까지 존재. 실제 3정규화까지 수행

1.1 정규화 절차

1.2 함수적 종속성(Functional Dependency)

- 데이터나 데이터베이스 구성요소가 다른 구성요소에 영향을 끼치는 현상

- 릴레이션의 어떤 속성 값에 의하여 다른 속성 값이 결정되는 경우 함수적 종속이라한다.

- 함수적 종속 관계는 X -> Y 로 표시하고 “Y는 X에 함수적으로 종속된다”라고 한다.

1) 제1정규화

- 기본키를 잡는것

- 하나의 속성이 단일 값을 갖도록 한다.

2) 제2정규화

- 부분 함수 종속성을 제거하기 위해 분해하는 과정

- 부분 함수 종속성 : 기본키가 2개 이상의 column으로 이루어진 경우에 발생.

- 종속 관계에 데이터의 중복 또는 불일치를 해결.

3) 제3정규화

- 이행 함수 종속성을 제거하기 위해 분해하는 과정.

- 이행 함수 종속성 : 기본키를 제외하고 column간에 종속성이 발생하는것.

- 주식별자가 아닌 속성들 중에 종속관계가 발생하면 분해하는 작업.

- 제3정규화는 제1정규화, 제2정규화를 수행하고 작업 한다.

4) BCNF(Boyce-Codd Normal Form)

복수의 후ㅗㅂ키가 있고, 후보키들이 복합 속성이어야 하고 서로 중첩되는 경우.

2. 정규화와 성능

2.1 정규화의 문제점

- 정규화는 데이터 조회(SELECT) 시 조인(JOIN)을 유발하여 자원(CUP와 메모리)을 많이 사용한다.

- 조회를 프로그램으로 구현하는 경우 중첩된 루프(Nested Loop)를 사용하게 된다.

- 정규화의 문제점 해결을 위해서 반정규화를 통해 테이블 하나에 값을 저장 하여 해결한다.

- 반정규화는 데이터를 중복시키기 때문에 또다른 문제점 발생.

- 정규화는 테이블을 분해하여 입출력 데이터의 양을 줄여서 성능을 향상 시켜야 한다.

3. 반정규화(De-Normalization)

- 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하여 조인(Join)을 줄이는 방법.

- 반정규화는 조회(SELECT)의 속도를 향상 하지만 데이터 모델의 유연성은 낮아진다.

3.1 반정규화를 하는 경우

- 정규화를 통하여 수행 속도가 느려진 경우

- 다량의 범위를 자주 처리해야 하는 경우

- 특정 범위의 데이터만 자주 처리하는 경우

- 요약/집계 정보가 자주 요구되는 경우

3.2 반정규화 절차

3.3 반정규화 기법

1) 계산된 칼럼 추가

배치 프로그램으로 총판매액, 평균잔고, 계좌평가 등을 미리 계산하고, 그 결과를 특정 칼럼에 추가한다.

2) 테이블 수직 분할

하나의 테이블의 두 개 이상의 테이블로 분할한다. 즉, 칼럼을 분할하여 새로운 테이블을 만드는 것이다.

3) 테이블 수평 분할

하나의 테이블에 있는 값을 기준으로 테이블을 분할하는 방법이다.

4) 테이블 병합 : 조인(Join) 발생 차단

- 1:1 관계의 테이블을 하나의 테이블로 병합해서 성능을 향상시킨다.

- 1:N 관계의 테이블을 병합하여 성능을 향상시킨다. 하지만 많은 양의 데이터 중복이 발생한다.

- 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 통합하여 성능을 향상시킨다.

5) 슈퍼 타입 및 서브 타입 변환 방법

4. 분산 데이터베이스

4.1 분산 데이터베이스

- 중앙 집중형 데이터베이스 : 한 대의 물리적 시스템에 데이터베이스 관리 시스템을 설치하고 여러 명의 사용자가 데이터베이스 관리 시스템에 접속하여 데이터베이스를 사용하는 구조.

- 분산 데이터 베이스 : 물리적으로 떨어진 데이터베이스에 네트워크로 연결하여 단일 데이터베이스 이미지를 보여주고 분산된 작업 처리를 수행하는 데이터베이스.

- 투명성 제공 : 분산 데이터베이스를 사용하는 고객이, 시스템이 분산되어있는지 인식하지 못하면서, 자신만의 데이터베이스를 사용하는 것 처럼 사용

4.2 투명성 종류

4.3 분산 데이터베이스의 설계 방식

1) 상향식 설계 방식

지역 스키마 작성 후 향후 전역 스키마를 작성하여 분산 데이터베이스를 구축한다.

2) 하향식 설계 방식

전역 스키마 작성 후 해당 지역 사상 스키마를 작성하여 분산 데이터베이스를 구축한다.

3) 분산 데이터베이스 장점과 단점

 - copy coding -

Linux에 PostgreSQL가 설치 되어 있는데 신규 프로젝트를 위한 DB를 생성할 일이 발생했습니다.

PostgreSQL에 사용자 계정을 생성하고 신규 Database를 만들어 외부에서 접속 가능하도록 설정을 하려고 합니다.  작업 순서도 계정 생성, DB생성, 접속 설정 순으로 진행 합니다.

작업시 주의사항은 root 권한으로 하는 작업과 postgres 권한으로 하는 작업 그리고 DB에 접속해서 하는 작업이 섞여 있습니다.

우선 postgresql에 접속을 합니다.

$ sudo -u postgres psql

접속이 성공하면 계정 생성과 디비 생성을 진행 합니다.

- 계정 및 권한

postgres=# CREATE USER copycoding SUPERUSER;

- 비번 생성

postgres=# ALTER USER copycoding WITH PASSWORD 'copycopy';

- DB 생성

postgres=# create database copycodingdb with owner copycoding encoding 'UTF8';

이제 postgresql을 재시작 해주는데 설치를 어떻게 했냐에 따라 뒤에 버전 정보가 붙을 수도 있습니다.  저는 9.6을 설치하며 버전정보를 그냥 사용했습니다.

$ sudo systemctl restart postgresql-9.6

DB가 잘 생성이 되었는지 확인하기 위해 다시 DB에서 명령어로 확인합니다.

postgres=# \l

상단에 생성된 DB가 보이니 잘 만들어 졌습니다. 

이제 외부에서 접속 할 수 있도록 설정 합니다.  설정 파일 위치를 확인 하고

계정을 postgres로 변경 합니다.

[root@localhost copycoding]# su postgres

그리고 검색한 파일을 수정 합니다.

$ vi /var/lib/pgsql/9.6/data/pg_hba.conf

파일을 열고 IPv4 부분을 찾아 라인을 하나 추가 합니다.

host    all             copycoding      0.0.0.0/0               md5

신규 User 정보를 추가가 끝나면 저장하고

postgres=# pg_ctl reload

디비를 재시작 합니다.

# sudo systemctl restart postgresql-9.6

아직 설정이 하나 더 남아 있습니다.  postgres 계정으로 conf 파일을 수정 합니다.

bash-4.2$ vi /var/lib/pgsql/9.6/data/postgresql.conf

파일에서 주석이 되어 있으면 주석을 풀고 다음처럼 수정 합니다.

listen_addresses = '*'

port = 5432

방화벽도 설정을 합니다.

$ firewall-cmd --permanent --add-port=5432/tcp

$ firewall-cmd --reload

설정이 모두 완료 되었습니다.

Windows에서 pgAdmin III를 이용하여 접속을 해 봅니다.

서버에서 디비를 생성할때 기록한 정보를 입력하고 OK 버튼을 클릭 합니다.

9.6을 설치해서 pgAdmin III는 지원이 잘 안될 수 있다고 하는데 무시하고 확인버튼을 클릭 합니다.

접속이 잘 되는 군요.  아직 Table 작업은 안되어있긴 하지만 성공입니다.

- copy coding -

CentOS 7에 PoatgreSQL을 설치하려면 먼저 운영체제 종류와 버전 별로 제품의 어떤 버전이 지원 되고 있는지 확인을 해야 합니다.

PostgreSQL도 홈페이지에 들어가 보면 2020년 05월 현재 지원 하는 상황을

이렇게 알려주고 있습니다.

지원하는 패키지가 많이 있지만 주요 패키지 구성은

이런거라네요.  당연히 server는 설치를 해야 하는 거고 나머지는 필요하면 같이 설치 해도 되고 나중에 따로 설치를 하면 됩니다.  이제 설치를 진행 합니다.

1. PostgreSQL 설치

설치하기 전에 yum을 업데이트 합니다.

$ sudo yum update -y

현재 상태에서 어떤 버전이 설치 가능 한지 확인 해 봅니다.

$ yum list | grep ^postgresql

현재는 재가 설치한 CentOS에서는 9.2 버전을 설치 할 수 있군요.  저는 다른 버전을 설치 하고 싶은데 그러려면 먼저 rpm을 install 해야 합니다.  다운로드 사이트에 접속해서

https://download.postgresql.org/pub/repos/yum/srpms/

원하는 버전 별로 찾아 들어가 rpm 주소를 복사해서 설치를 하면 됩니다.  꼭 아래처럼 하지 않아도 본인이 원하는 파일을 찾아서 설치 하세요.

$ sudo yum install https://yum.postgresql.org/9.6/redhat/rhel-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

설치를 완료하고 다시 $ yum list | grep ^postgresql  명령을 실행해보면

이제는 9.4, 9.5, 9.6, 10, 11, 12등 설치할 수 있는 버전이 많아졌습니다.

이중 설치하려는 버전 번호를 붙여서 명령어를 생성하면 됩니다.

$ sudo yum install postgresql96 postgresql96-server

설치가 완료되면 자동으로 postgres라는 계정이 하나 생성 됩니다.  /etc/passwd에 추가되고 /var/lib/pgsql/에 설치 됩니다.

2. PostgreSQL 초기화

initdb 명령으로 초기화를 진행 합니다.

$ sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

PostgreSQL을 등록하고 서버 재시작 후에도 자동으로 실행되도록 합니다.

$ sudo systemctl start postgresql-9.6

$ sudo systemctl enable postgresql-9.6

초기화 작업과 등록 작업이 완료 되었습니다.

3. PostgreSQL 설치 확인

다음 명령으로 설치를 확인해 봅니다.

$ ps -ef | grep post

데이터 베이스에 접속해 봅니다.

$ sudo -u postgres psql

설치 작업이 잘 되었습니다.

- copy coding -

오라클을 설치하지 않은 경우 간단하게 database를 테스트 할 수 있도록 oracle에서 제공하는 사이트가 있습니다.  어떻게 보면 별로 소용이 없을 수도 있지만 table 생성이나 쿼리 조회가 자유롭지 못한 환경에서는 자유롭게 테스트 하는데 유용할 수도 있습니다.

일단 사이트에 접속을 합니다.

https://livesql.oracle.com/

상단 우측 Sing In을 클릭하고 로그인을 진행 합니다.  오라클에 계정이 없다면 신규로 생성합니다.  

계정 생성이 귀찮을 수 있지만 오라클에서 제공하는 프로그램을 다운로드 받는 경우 필요하기 때문에 하나 만들어 두는 것도 좋습니다.

livesql 사이트에 처음 접속하는 경우에는 면책조항 확인을 합니다.

좌측 메뉴에서 SQL Worksheet를 선택하면 일반적인 퀴리 테스트가 가능 합니다.

select to_char(sysdate, 'YYYY-MM-DD') from dual;을 입력하고 상단 우측 Run 버튼을 클릭해서 테스트를 진행 합니다.

물론 신규 테이블 생성도 가능 합니다.

생성한 테이블에 데이터 입력도 가능 합니다.

쿼리의 일부만 실행 하려면 마우스로 드래그 해서 영역을 설정하고 Run 버튼을 클릭 합니다.

이제 입력한 데이터를 조회하면 됩니다.

select * from customers;

Table을 생성하고 데이터를 입력하는 작업이 귀찮으면 기존에 오라클에서 제공하는 테이블을 선택해서 쿼리 테스트를 진행해도 됩니다.

상단 Find를 선택하고 팝업창에서 Schema를 확장하면 내가 생성한 My Schema와 오라클에서 제공하는 친숙한 HR과 SCOTT도 보이는 군요.

My Session 메뉴에는 지금까지 작업한 log 정보를 확인할 수 있습니다.

본인이 생성한 Table 정보는 Schema에서 확인하고 작업할 수 있습니다.

단, Schema 정보는 Oracle 사이트에 Session이 연결된 동안만 존재 합니다.  Session을 저장 하려면 SQL Worksheet 상단에 있는 Save 버튼을 이용해서 저장을 해놓으세요.

- copy coding -