DBMS-Oracle(웹개발)

DB(Database)

데이터가 모여 있는 기지

DBMS(Database Management System)

DB를 관리할 수 있는 구체적인 시스템
오라클, 마리아DB, MySQL, MS-SQL, MongoDB, ...

오라클 DBMS 버전

i: internet
g: grid
c: cloud

DBMS의 소통 방식

-----------------------------------------------------
                        사용자
-----------------------------------------------------
         ↕                               ↕
고객 관리 응용프로그램      ↕     주문 관리 응용프로그램
         ↕                               ↕
-----------------------------------------------------
                         DBMS
-----------------------------------------------------

RDBMS(관계형 데이터베이스 시스템)

테이블끼리 서로 관계를 맺는다.

  Table A (USER, INFOMATION, CUSTOMOR)  Table B (ORDER)

  번호(PK) 이름   나이 아이디(UK)         주문번호(PK)  번호(FK) 날짜        상품수량
  1        이도은 20   lde1234           20230705001  3        2023-07-05 5
  2        김홍준 21   khj6666           20230705002  4        2023-07-05 100
  3        박민수 22   pms7777           20230703001  1        2023-07-03 35
  4        한동석 18   hds1234           20230703002  1        2023-07-03 15
  5        임준영 19   ljy7878           20230703003  1        2023-07-03 25

  이러한 구조를 가지는 것을 Table, Relation(오라클), Class라고 부른다.

COLUMN(열, 속성, 필드)

공통된 값들의 주제

ROW(행, 레코드, 튜플)

하나의 정보

PRIMARY KEY(PK)

고유한 값.
각 행의 구분점으로 사용된다.
중복이 없고 NULL값을 허용하지 않는다.
*NULL : 아직 어떤 값을 넣을 지 모르겠다는 의미로 넣는 값.

FOREIGN KEY(FK)

다른 테이블의 PK를 의미한다.
보통 테이블끼리 관계를 맺을 때 사용한다.
중복이 가능하고 NULL도 허용한다.

UNIQUE KEY(UK)

NULL은 허용하지만 중복은 허용하지 않는다.

컴파일 언어와 인터프리터 언어

컴파일

파일 단위로 해석한다(일괄처리) 수정이 거의 없을 때 효율적이다.

인터프리트

한 줄 단위로 해석한다(개별처리) 빈번한 수정 또는 부분 실행 시 효율적이다.

SQL문(쿼리문) - DDL, DML, DCL, TCL

스크립트 언어(인터프리터 언어).
DBMS와 소통하는 언어.

DDL(Data Definition Language) : 데이터 정의어

테이블 조작, 제어 관련 쿼리문

CREATE : 테이블 생성

CREATE TABLE [테이블명]( [컬럼명] [자료형(용량)] [제약조건], ... );

DROP : 테이블 삭제
```
DROP TABLE [테이블명]
```

ALTER : 테이블 수정

- 테이블명 수정 : RENAME TO [새로운 테이블명]
- 컬럼 추가 : ADD([새로운 컬럼명] [자료형(용량)])
- 컬럼명 변경 : RENAME COLUMN [기존 컬럼명] TO [새로운 컬럼명]
- 컬럼 삭제 : DROP COLUMN [기존 컬럼명]
- 컬럼 수정 : MODIFY([기존 컬럼명] [자료형(용량)])

TRUNCATE : 테이블 내용 전체 삭제
```
TRUNCATE TABLE [테이블명]
```

자료형(TYPE): 용량은 항상 넉넉하게 주도록 한다.

숫자

NUMBER(precision) : 정수
NUMBER(precision, 소수점 자리수) : 실수

문자열

CHAR(용량) : 고정형
- CHAR(4)에 'A'를 넣으면 A^^^ 빈 자리가 공백으로 채워진다.
- 형식을 정한 날짜, 주민등록번호처럼 글자 수가 절대 변하지 않는 값는 넣는다.
VARCHAR(용량), VARCHAR2(용량) : 가변형
- 값의 길이 만큼 공간이 배정된다.
- 글자 수에 변화가 있는 값는 넣는다.

날짜

DATE : FORMAT에 맞춰서 날짜를 저장하는 타입

무결성

데이터의 정확성, 일관성, 유효성이 유지되는 것.

  정확성: 데이터는 애매하지 않아야 한다.
  일관성: 각 사용자가 일관된 데이터를 볼 수 있도록 해야한다.
  유효성: 데이터가 실제 존재하는 데이터여야 한다.

개체 무결성
- 모든 테이블이 PK로 선택된 컬럼을 가져야 한다.
참조 무결성
- 두 테이블의 데이터가 항상 일관된 값을 가지도록 유지하는 것.
도메인 무결성
- 컬럼의 타입, NULL값의 허용 등에 대한 사항을 정의하고 올바른 데이터가 입력되었는 지를 확인하는 것.

모델링(기획)

추상적인 주제를 db에 맞게 설계하는 것

요구사항 분석
- 회원, 주문, 상품 : 3가지를 관리하고자 한다.

개념적 설계(개념 모델링)

 회원     주문      상품
 ---------------------------
 아이디   주문번호  상품번호
 ---------------------------
 비밀번호 주문날짜  상품명
 이름     아이디    가격
 주소     상품번호  재고량
 이메일
 생일

논리적 설계(논리 모델링)

 회원      주문         상품
 ---------------------------------
 번호PK    주문번호PK   상품번호PK
 ---------------------------------
 비밀번호  주문날짜     상품명
 이름      회원번호FK   가격
 주소      상품번호FK   재고량
 이메일
 생일
 아이디UK

물리적 설계(물리 모델링)

 TBL_USER
 -----------------------
 id : NUMBER PRIMARY KEY
 -----------------------
 user_id : VARCHAR2(1000)
 user_pw : VARCHAR2(1000)
 user_address : VARCHAR2(2000)
 user_email : VARCHAR2(2000) : UNIQUE
 user_birth : DATE

 ORDER

 PRODUCT

구현

정규화

삽입/수정/삭제의 이상현상을 제거하기 위한 작업.
데이터의 중복을 최소화하는 데에 목적이 있다.
5차 정규화까지 있으나 3차 정규화까지만 진행한다.

1차 정규화

같은 성격과 내용의 컬럼이 연속적으로 나타날 경우

  상품명
  와이셔츠1, 와이셔츠2,  와이셔츠3

  상품명1	상품명2	    상품명3
  와이셔츠1	와이셔츠2    와이셔츠3

  * 검색이 힘들다

  1차 정규화 진행

  상품명
  와이셔츠1
  와이셔츠2
  와이셔츠3

2차 정규화

조합키(복합키)로 구성되었을 경우 조합키의 일부분에만 종속되는 속성이 있을 경우(부분 종속)

  꽃
  이름	    색상	꽃말	과
  해바라기  노란색       행운	국화
  장미	    빨간색	사랑	장미

  2차 정규화 진행

  꽃
  이름	    색상	꽃말
  해바라기   노란색      행운
  장미	    빨간색	사랑

  과
  이름	    과
  해바라기  국화
  장미	    장미

3차 정규화

PK가 아닌 컬럼이 다른 컬럼을 결정하는 경우 이행함수 종속 제거

  회원번호       이름	시	구	동      우편번호
  1	        한동석	경기도	남양주	화도	12345
  2	        홍길동	서울	강남	역삼	56466


  * 우편번호로 시, 구, 동을 알 수 있다.
  * 중복된 데이터가 생길 가능성이 있다.

  - 3차 정규화 진행

  회원번호	이름	우편번호
  1	        한동석	12345
  2	        홍길동	56466


  우편번호   시	        구	동
  12345	    경기도	남양주	화도
  56466	    서울	강남	역삼

데이터베이스에서 정규화가 필요한 이유

데이터베이스를 잘못 설계하면 불필요한 데이터 중복으로 인해 공간이 낭비된다.

이런 현상을 이상(Anomaly)현상이라고 한다.

  회원번호와 프로젝트코드 두 컬럼의 조합키로 설정되어 있는 테이블이고
  한 사람은 하나의 부서만 가질 수 있다.

  회원번호           이름         부서	    프로젝트코드	급여	부서별 명수
  22080101	    한동석	개발팀	    ABC0001	3000	4
  22080101	    한동석	개발팀	    DEF1112	2000	4
  22080101	    한동석	개발팀	    CBA9474	4000	4
  22080104	    홍길동	기획팀	    EFG0881	5000	2
  22081106	    이순신	디자인팀	    GHI9991	6000	3

이상 현상의 종류

삽입 이상

새 데이터를 삽입하기 위해 불필요한 데이터도 삽입해야하는 문제
담당 프로젝트가 정해지지 않은 사원이 있다면, 프로젝트 코드에 NULL을 작성할 수 없으므로 이 사원은 테이블에 추가될 수 없다.

따라서 '미정'이라는 프로젝트 코드를 따로 만들어서 삽입해야 한다.

갱신 이상

  중복 행 중에서 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제

  한 명의 사원은 반드시 하나의 부서에만 속할 수 있다.
  만약 "한동석"이 보안팀으로 부서를 옮길 시 3개 모두 갱신해주지 않는다면
  개발팀인지 보안팀인지 알 수 없다.

삭제 이상

  행을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 문제

  "이순신"이 담당한 프로젝트를 박살내서 드랍된다면 "이순신" 행을 모두 삭제하게 된다.
  따라서 프로젝트에서 드랍되면 정보를 모두 드랍하게 된다.

2차 정규화 진행

  회원번호    프로젝트코드     급여
  22080101    ABC0001        3000
  22080101    DEF1112        2000
  22080101    CBA9474        4000
  22080104    EFG0881        5000
  22081106    GHI9991        6000


  회원번호     이름      부서     부서별 명수
  22080101    한동석    개발팀    4
  22080104    홍길동    기획팀    2
  22081106    이순신    디자인팀  3

3차 정규화 진행

  회원번호    이름       부서
  22080101    한동석    개발팀
  22080104    홍길동    기획팀
  22081106    이순신    디자인팀
  22080103    장보고    개발팀

  부서      부서별 명수
  개발팀    4
  기획팀    2
  디자인팀  3

DML(Data Manipulation Language): 데이터 조작어

SELECT : 조회(검색)

SELECT [컬럼명1, 컬럼명2, ...]
FROM [테이블명]
WHERE [조건식]

INSERT : 추가
1. 컬럼명을 생략할 수 있으며, 이 경우 DEFAULT 제약조건이 발생된다.
```
INSERT INTO [테이블명]
([컬럼명1, 컬럼명2, ...])
VALUES([값1, 값2, ...])
```
2. 모든 값을 전부 작성해야 되며, 컬럼명은 직접 작성하지 않는다.
```
INSERT INTO [테이블명]
VALUES([값1, 값2, ...])
```

UPDATE : 수정

UPDATE [테이블명]
SET [기존 컬럼명1] = [새로운 값1], [기존 컬럼명2] = [새로운 값2], ...
WHERE [조건식]

DELETE : 삭제

DELETE FROM [테이블명]
WHERE [조건식]

조건식 : 참 또는 거짓 둘 중 하나가 나오는 식

>, < : 초과, 미만
>=, <= : 이상, 이하
= : 같다
<>, !=, ^= : 같지 않다
AND : 둘 다 참이면 참
OR : 둘 중 하나라도 참이면 참

※ 위 연산자들은 WHERE 절에서 사용 가능하다.

JOIN

여러 테이블에 흩어져 있는 정보 중 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들고 결과를 보여주는 것.
정규화를 통해 조회 테이블이 너무 많이 쪼개져 있으면 작업이 불편하기 때문에
조회의 성능을 향상시키기 위해서 JOIN을 통해 합친 후 사용한다.

내부 조인(INNER JOIN)

  조건이 일치하는 값만 합쳐서 조회

  FROM [테이블명]
  INNER JOIN [테이블명]
  ON 조건식
  INNER JOIN [테이블명]
  ON 조건식
  INNER JOIN [테이블명]
  ON 조건식
  ...

  ※ 세타조인(막조인)
  FROM [테이블명], [테이블명], [테이블명]
  WHERE 조건식

등가 조인

  ON절에 등호가 있을 때, 서로 관계를 맺고 있는 테이블끼리 JOIN할 때 자주 사용된다.

비등가 조인
```
  ON절에 등호가 없을 때
```

외부 조인(OUTER JOIN)

  on절에 작성된 조건식이 false일지라도 모든 정보를 조회해야 할 때 사용한다.

left outer join

  선행 테이블의 모든 정보를 가져오고 싶을 때 사용한다.

right outer join

  후행 테이블의 모든 정보를 가져오고 싶을 때 사용한다.

gyoungDeok-Seo/study-dbms