[21일차] BDA과정 데이터베이스와 SQL

[KDT] 패스트캠퍼스 비즈니스 데이터 분석가 양성과정 21일 차

데이터베이스와 SQL 

---

안녕하세요 ㅎㅎ 오늘은 데이터베이스와 SQL을 주제로 인강이 진행되었습니다. 정리한 내용 올리겠습니다.

---

 

데이터 베이스 - 컴퓨터 시스템에 전자적으로 저장된 체계적 데이터의 모음 

 

데이터베이스 관리시스템(DBMS) - 다수의 사용자들이 데이터베이스 내의 데이터를 접근할 수 있도록 해주는 소프트웨어 도구의 집합 

DBMS은 사용자 또는 다른 프로그램의 요구를 처리하고 적절히 응답하여 데이터를 사용할 수 있도록 해준다.

 

데이터베이스 시스템의 특징 - 실시간 접근성, 계속적인 변화 , 동시 공유 , 내용에 따른 참조 

 

데이터베이스의 기본기능 

데이터 조회 , 삽입, 삭제, 수정

동시성 제어 - 다중 사용자 환경을 지원하는 데이터베이스 시스템에서 동시에 실행되는 여러 트랜잭션 간의 간섭으로 문제가 발생하지 않도록 트랜잭션의 실행 순서를 제어하는 기법

장애 대응 기능 - 데이터베이스는 데이터 손실이 발생할 경우에 복원이 가능해야 함 

---

관계형 데이터베이스란

키(key)와 값(value)들의 간단한 관계를 테이블화 시킨 매우 간단한 원칙의 전산정보 DB 

2차 원표를 이용한 데이터 목록화 관리를 하는 것이 주목적

 

관계형 모델

집합론에 기반을 둔 일종의 데이터베이스 모델 

column과 low를 이루는 하나 이상의 테이블이 존재하고 테이블에 저장

각각의 테이블은 각각의 로우를 식별하는 기본키(Primary key)가 존재

칼럼은 fieldg혹은 attribute라고도 불림, 로우는 record혹은 tuple로 불림

 

SQL - RDBMS의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어, RDBMS에서 자료의 검색과 관리 DB 스키마 생성과 수정 , DB 객체 접근 조정 관리를 위해 고안 

---

아키텍처 - 시스템을 만들기 위한 물리 레벨의 조합

IT아키텍처 - 일정기준과 절차에 따라 조직 전체의 정보화 구성요소를 통합분석 후 그 관계를 구조적으로 정리한 체제 이를 바탕으로 정보시스템을 효율적으로 구성하기 위한 방법

 

아키텍쳐 역사와 개요 

Stand-alone 단계 - DBMS 서버가 네트워크 접속 없이 독립적으로 작동 

클라이언트/서버 단계 - 네트워크 연결을 통한 복수 사용자가 동시에 사용 가능 

WEB3계층 - 시스템을 3가지 계층의 조합으로 인식하기 시작(WEB , WAS , DBMS)

 

가용성(Availability) - 서버와 네트워크, 프로그램 등의 정보 시스템이 정상적으로 사용 가능한 정도를 말한다.

Availability(%) = ( uptime 정상적인 사용시간 / uptime + Downtime 정상적으로 운영되지 않은 시간  )  * 100  

확장성(Scalability) - IT시스템에서 대규모적인 재설계, 재설치가 필요 없이 확장이 얼마나 쉽고 가능한지에 대한 용이성 

단일 장애점(SPOF, Single Point Of Failure) - 시스템 구성 요소 중에서 동작하지 않으면 전체 시스템이 중단되는 요소

 

DB서버의 다중화 

 Active -Active 구성 다중화 - 하나의 저장소를 두고 2개의 서버가 운영,

                                      하나의 서버가 다운되어도 나머지 하나가 계속 처리 

Active-Standby 구성 다중화 - 평소에는 Active 서버로만 업무, Active 서버에 장애가 발생 시 Standby서버가 업무처리

 

리플리케이션 다중화 - DB서버와 저장소를 복수의 세트로 준비, 하나의 서버에 하나의 저장소

 

Database Shard - 데이터베이스나 웹 검색 엔진의 데이터의 수평분할 , 개개의 파티션은 Shard 또는 Database Shard로 부른다. 각 Shard는 개개의 데이터베이스 서버 인스턴스에서 부하 분산을 위해 보유하고 있다.

최적의 아키텍처 설계 전략 가용성, 신뢰성, 재해대책 , 성능, 보안 , 비용 등 다양한 조건 고려 해당 비즈니스의 성장성이나 사용자의 유입 예측도 감안 한번 구축하면 변경 시 비용과 시간이 소요

---

SQL 기본 

 

select 조회 

SELECT first_name, last_name  

FROM CONTACTS 

 

오라클 기능 Dummy 테이블 생성 

select * # *는 모든 값 반환 

from Dual ;

 

Dual이란 가상 테이블에서 연산 가능 

select  (5 +5) / 2  as A    # as는 칼럼명 지정 

from dual ; 

 

order by 문

select first_name , last_name

from CONTACTS 

order by first_name asc  # asc 오름차순 정렬 , desc 내림차순 정렬 

 

distinct 문

select distinct first_name # 기재된 값이 중복이 제거되어 출력

from CONTACTS 

 

where문  #원하는 조건을 입력 

SELECT distinct product_id     
FROM INVENTORIES i 
WHERE QUANTITY >=160

 

SELECT product_name, LIST_PRICE 
FROM PRODUCTS p 
WHERE STANDARD_COST BETWEEN 570 AND 600  # 570과 600 사이에 값을 조건으로 지정 
ORDER BY LIST_PRICE  asc

 

SELECT product_name, CATEGORY_ID 
FROM PRODUCTS p 
WHERE CATEGORY_ID IN (1,3) #  CATEGORY_ID 칼럼에서 1,3이 포함된 값을 조건으로 지정 

 

SELECT FIRST_NAME , LAST_NAME 
FROM CONTACTS c 
WHERE FIRST_NAME LIKE   'je%'     # first_name칼럼에서 "je"로 시작하는 데이터를 반환 "

"% je" je로 끝나는 데이터 반환 ,  % je% je로 시작하거나 끝나는 데이터 반환 

---

테이블 생성 - 칼럼명과 데이터 타입 지정 

CREATE TABLE DISCOUNTS ( DISCOUNT_ID NUMBER GENERATED BY DEFAULT AS IDENTITY , DISCOUNT_NAME VARCHAR2(255) NOT NULL , AMOUNT NUMBER(3, 1) NOT NULL , START_DATE DATE NOT NULL , EXPIRED_DATE DATE NOT NULL );

 

Insert 문

INSERT INTO
DISCOUNTS (DISCOUNT_NAME, AMOUNT, START_DATE, EXPIRED_DATE )
VALUES ( 'Summer Promotion', 9.5, DATE '2017-05-01', DATE '2017-08-31') 

 

Update 문 데이터 변경

UPDATE DISCOUNTS  SET AMOUNT = 10 WHERE START_DATE = '2017-05-01'' 

# where로 조건 설정 없이 update 할 경우 칼럼 전체의 데이터가 바뀌기 때문에 유의 필요

 

DELETE 문 데이터 삭제

DELETE  FROM DISCOUNTS WHERE AMOUNT =  10

---

INLINE VIEW - 서브 쿼리가 FROM 절에서 안에서 사용되는 경우, 해당 서브 쿼리를 '인라인 뷰'라고 한다. FROM 절에서 사용된 서브 쿼리의 결과가 하나의 테이블에 대한 뷰(View)처럼 사용 

 

SELECT *
FROM (SELECT category_name FROM PRODUCT_CATEGORIES pc  )

 

VIEW - 관계 데이터베이스의 데이터베이스 언어 SQL에서 하나 이상의 테이블에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것

 

CREATE OR REPLACE VIEW manager AS 
SELECT JOB_TITLE 
FROM HR.JOBS j 
WHERE JOB_TITLE  LIKE '% manager'

 

---

서브 쿼리- 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 말한다

SELECT PRODUCT_NAME , LIST_PRICE
FROM PRODUCTS p 
WHERE LIST_PRICE = (SELECT max(LIST_PRICE ) FROM PRODUCTS p2 )

 

스칼라 서브쿼리 - select절 내에 사용하는 서브쿼리

SELECT A.PRODUCT_NAME , A.LIST_PRICE , ROUND( (SELECT AVG(K.LIST_PRICE)

FROM PRODUCTS K WHERE K.CATEGORY_ID = A.CATEGORY_ID ), 2 ) AVG_LIST_PRICE

FROM PRODUCTS A ORDER BY A.PRODUCT_NAME;

---

트랜잭션 - 데이터베이스 관리 시스템 또는 유사한 시스템에서 상호작용의 단위 , 유사한 시스템이란 트랜잭션의 성공과 실패가 분명하고 상호 독립적이며, 일관되고 믿을 수 있는 시스템을 의미

 

트랜잭션 특징 - 원자성(Atomicity) , 일관성(Consistency), 고립성(Isolation), 지속성(Durability)

 

ANSI 표준 격리 수준

Read Uncommitted - 트랜잭션에서 처리 중인 아직 commit 되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용

Read Committed - 트랜잭션이 Commit 확정된 데이터만 다른 트랜잭션이 읽도록 허용

Repeatable Read - 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때 , 첫번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지

Serializable - 트랜잭션 내에서 쿼리를 두번 이상 수행할 때 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론 새로운 레코드가 나타나지도 않음 

 

락의 개념 - 갱신 손실 문제를 해결하려면 상대방 트랜잭션이 데이터를 사용하는지 여부를 알 수 있는 규칙 

                데이터를 수정 중이라는 사실을 알리는 방법의 잠금장치 

 

데드락 - 두 개 이상의 트랜잭션이 각각 자신의 데이터에 대하여 락을 획득하고 상대방 데이터에 대하여 락을 요청하면 무한 대기 상태에 빠질 수 있는 현상 

---

테이블 설계 

 

테이블 - 관계형 데이터베이스에서 데이터를 관리 및 저장하는 장소

            데이터의 효율적인 관리 및 적절한 조작이 중요

 

기본키(Primary Key) 후보 키들 중에서 하나를 선택한 키로 최소성과 유일성을 만족하는 속성이다. 테이블에서 기본키는 오직 1개만 지정할 수 있다. 기본키는 테이블 안에서 유일하게 각 행들을 구별할 수 있도록 쓰인다. 기본키는값을 절대 가질 수 없고, 중복된 값을 가질 수 없다. 각 행들을 구별하려면 값이 없어선 안되고, 중복되어서도 안되기 때문이다.

 

정규형 - 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화(Normalization)라고 한다. 데이터베이스 정규화의 목표는 이상이 있는 관계를 재구성하여 작고 잘 조직된 관계를 생성하는 것에 있다. 일반적으로 정규화란 크고, 제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것을 포함한다. 정규화의 목적은 하나의 테이블에서의 데이터의 삽입, 삭제, 변경이 정의된 관계들로 인하여 데이터베이스의 나머지 부분들로 전파되게 하는 것

 

ER 다이어그램 (Entity - Relationship Diagram)

구조화된 데이터를 저장하기 위해 데이터베이스를 쓴다. 이 데이터의 구조 및 그에 수반한 제약 조건들은 다양한 기법에 의해 설계될 수 있다. 그 기법 중 하나가 개체-관계 모델링(Entity-Relationship Modelling)이다.

---

백업 및 복구 

 

로그 선행 기입(write-ahead logging, WAL)을 사용하는 모든 수정은 적용 이전에 로그에 기록 

일반적으로 redo 및 undo 정보는 둘 다 로그에 저장 

 

데이터베이스 버퍼 

데이터 파일로의 입력을 데이터베이스 버퍼 경유로 일원화

트랜잭션마다 버퍼 취할 경우 로그와 데이터 파일 간 일관성 저하

효율적인 데이터 일관성 유지

 

백업의 3가지 관점 

핫팩 업(Hot Backup) - 백업 대상의 데이터베이스가 정지하지 않고 가동한  상태로 백업 데이터 얻음

콜드 백업(Cold Backup) - 백업 대상 데이터베이스가 정지한 후 백업 데이터 얻음, 데이터 디렉터리의 파일 전부를 OS명령으로 복사

 

논리 백업 - SQL 기반 텍스트 형식으로 백업 데이터 기록

물리 백업 - 데이터 영역을 그대로 덤프 하는 이미지로 바이너리 형식 기록 

 

풀백업(전체 백업)  - 데이터베이스 전체 데이터를 매일 백업

부분 백업 - 풀백업 이후 갱신된 데이터를 백업

 

---

데이터 베이스 성능

 

Concurrent User = Active User + Inactive User 

Active User - 대상 서버에 부하를 발생시키고 있는 사용자

Inactive User  - 현재 서버에 요청을 보내고 있지 않은 사용자

 

TPS(Transaction Per Second) - 서버가 일정 시간 내에 처리한 트랜잭션의 양

Response time - 요청한 후부터 응답을 받을 때까지 소요된 시간

Resource - 한정된 값을 가진 시스템의 구성요소를 의미