[국비 6일차 TIL] 데이터베이스 구성, 설계, 사용자, 권한, 롤, 조인

관계형 데이터 모델

-- 개념적 구조를 논리적 구조로 표현하는 논리적 데이터 모델

-- 하나의 개체에 대한 데이터를 하나의 릴레이션에 저장

-- 열(속성, 애트리뷰트), 행(투플), 도메인, 널(NULL), 차수(Degree), 카디널리티(Cardicality)

 

데이터베이스의 구성

-- 데이터베이스 스키마(database schema)

--> 데이터베이스의 전체 구조, 개념적인 상태, 논리적인 상태

 

-- 데이터베이스 인스턴스(database instance)

--> 데이터베이스를 구성하는 릴레이션 인스턴스의 모음, 구체화되어 만들어진 것.

 

릴레이션의 특징

-- 투플의 유일성, 투플의 무순서, 속성의 무순서, 속성의 원자성

 

키(Key)

--> 릴레이션에서 투플들을 유일하게 구별하는 속성 또는 속성들의 집합

--> 특성 : 유일성, 최소성

--> 기본키, 대체키, 후보키, 슈퍼키, 외래키

 

-- 기본키(Primary)  : 기본적으로 사용하기 위한 키

-- 후보키(Candidate) : NOT NULL, UNIQUE, 유일성과 최소성을 만족하는 속성 또는 속성집합

-- 대체키(Alternate) : 기본키로 선택되지 못한 키

-- 슈퍼키(Super)      : 유일성을 만족하는 속성 또는 속성집합

-- 외래키(foreign)     : 다른 릴레이션의 기본키를 참조하는 속성 또는 속성집합

 

 

제약조건

-- 무결성 제약조건(integrity constraint)

--> 데이터의 무결성을 보장, 정확하고 유효하게 유지하는 것

 

-- 개체 무결성 제약조건(entity integrity constraint)

--> 기본키를 구성하는 모든 속성은 널 값을 가질 수 없는 규칙

 

-- 참조 무결성 제약조건(referential integrity constraint)

--> 외래키는 참조할 수 없는 값을 가질 수 없는 규칙, 참조 필수

--> 외래키는 null이 될 수 있다.

 

 

데이터베이스 설계

-- 데이터베이스 설계 단계

• 1단계 : 요구 사항 분석
• 2단계 : 개념적 설계
• 3단계 : 논리적 설계
• 4단계 : 물리적 설계
• 5단계 : 구현

-- 설계 1단계 : 요구 사항 분석

--> 사용자의 요구 사항을 수집하고 분석하여 개발할 데이터베이스의 용도를 파악

 

-- 설계 2단계 : 개념적 설계

--> DBMS에 독립적인 개념적 스키마 설계 (스키마 : 전체구조)

--> 요구사항 분석 결과물을 개념적 데이터 모델을 이용해 개념적 구조로 표현

--> 개념적 모델링 : 일반적으로 E-R 다이어그램

 

-- 개념적 설계 작업 과정 --

 

-- 1) 개체와 속성 추출 : 저장할 만한 가치가 있는 중요 데이터를 가진 사람이나 사물 등

--> 개체 추출 방법 : 업무와 관련이 깊은 의미 있는 [명사]를 찾아 개체와 속성으로 분류

--> ex) 개체 : 회원, 상품, 제조업체 등 / 속성 : 회원아이디, 상품명, 제조업체명 등

 

-- 2) 관계 추출 : 개체 간의 연관성을 의미 있게 표현한 [동사]

--> ex) 주문할 수 있다. 유지해야 한다.

 

-- 설계 3단계 : 논리적 설계

--> DBMS에 적합한 논리적 스키마 설계

--> 개념적 스키마를 논리적 데이터 모델을 이용해 논리적 구조로 표현

--> 논리적 스키마 : 릴레이션 스키마 (관계 데이터 모델)

--> 속성들을 세부적으로 결정하고 결과를 문서화시킴.

 

-- E-R DIAGRAM을 릴레이션 스키마로 변환하는 규칙

• 규칙 1. : 모든 개체는 릴레이션으로 변환한다.
• 규칙 2. : 다대다(n:m) 관계는 릴레이션으로 변환한다.
• 규칙 3. : 일대다(1:n) 관계는 외래키로 표현한다.
• 규칙 4. : 일대일(1:1) 관계는 외래키로 표현한다.
• 규칙 5. : 다중 값 속성은 릴레이션으로 표현한다.

 

-- 테이블 명세서 작성

--> 릴레이션 스키마에 대한 설계 정보를 기술한 문서

--> 릴레이션 스키마 변환 후 속성의 데이터 타입과 길이, NULL값 허용 여부, 기본값, 제약조건 등을 세부적으로 결정하고 문서화시킴.

 

-- 설계 4단계 : 물리적 설계

--> 하드웨어나 운영체제의 특성을 고려하여 필요한 인덱스 구조나 내부 저장 구조 등에 대한 물리적인 구조를 설계

 

-- 설계 5단계 : 구현

--> SQL로 작성한 명령문을 DBMS에서 실행하여 데이터베이스를 실제로 생성

 

-- 표기법

--> Barker : 직사각형 상자로 나타내고 상자 안에 엔터티의 이름을 기재

--> Crow's foot : IE(Information Engineering) 표기법

---

 

-- 사용자 USER

-- 데이터베이스 스키마(Schema): 모든 객체 구조의 범위

-- 오라클 데이터베이스에서는 스키마와 사용자를 구별하지 않고 사용하기도 함.

-- 사용자 = 데이터베이스 이기도 함.

-- 모든 사용자는 데이터베이스를 가질 수 있음.

 

CREATE USER ORCLSTUDY
IDENTIFIED BY ORACLE;

 

1. 사용자 생성
2. SYSTEM에서 권한 필요
3. SESSION을 사용해 사용자에게 권한 부여

-> 사용자를 만들었다고 바로 접속한 것은 아님, 세세한 권한을 부여해야 함.

 

 

-- 권한 관리

-- 시스템 권한 : 데이터베이스 관리 권한이 있는 사용자가 부여할 수 있는 권한

-- 시스템 권한 취소 : REVOKE [시스템 권한] FROM ~

 

 

-- 객체 권한 : 특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한

 

-- ORCLSTUDY에 SELECT, INSERT 권한 부여하기

 

GRANT SELECT, INSERT ON EMP
TO ORCLSTUDY;

 

-- SCOTT의 데이터베이스에 있는 EMP 테이블 읽는 방법

 

SELECT * FROM SCOTT.EMP;

GRANT INSERT ON SCOTT.EMP TO ORCLSTUDY;

INSERT INTO SCOTT.EMP (EMPNO, ENAME)
                VALUES(7778, '홍길동');

REVOKE SELECT, INSERT ON EMP FROM ORCLSTUDY;

 

-- 롤 (Role)

-- 여러 종류의 권한을 묶어놓은 그룹

 

-- 사전 정의된 롤

-- CONNECT 롤  = CREATE SESSION...

-- RESOURCE 롤 =  CREATE TRIGGER, CREATE TYPE, CREATE TABLE...

 

-- 사용자 정의 롤 

-- JOIN

SELECT * FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
     AND E.SAL >= 3000;

   

-- NATURAL JOIN

-- FROM 뒤에서 조인

-- 양쪽에 다 있는 기준열 이름은 그대로 작성.(아래의 예시에서 DEPTNO)

 

SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, DEPTNO
FROM EMP E NATURAL JOIN DEPT D;

 

-- JOIN ~ USING (~)

 

SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, DEPTNO
FROM EMP E JOIN DEPT D USING(DEPTNO);

 

-- JOIN ~ ON

 

SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, E.DEPTNO
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

SELECT * FROM EMP E
LEFT OUTER JOIN D USING (DEPTNO);

 

-- JOIN 3개 이상의 테이블을 조인할 때

FROM TABLE1 JOIN TABLE2 ON (조건식)
            JOIN TABLE3 ON (조건식)

 

-- JOIN ~ USING 사용 예시

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, DEPTNO
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL >= 3000
AND MGR IS NOT NULL;

 

-- JOIN ~ ON 사용 예시

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, D.DEPTNO
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
WHERE SAL >= 3000
AND MGR IS NOT NULL;

 

-- 연습문제 

SELECT DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL > 2000
ORDER BY DEPTNO;


SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
FROM EMP E JOIN DEPT D ON(E.DEPTNO = E.DEPTNO)
WHERE SAL > 2000
ORDER BY DEPTNO;

 

--? JOIN ~ USING 사용 예시

SELECT DEPTNO, D.DNAME,
        ROUND(AVG(E.SAL),0) AS AVG_SAL,
        ROUND(MAX(E.SAL),0) AS MAX_SAL,
        MIN(E.SAL) AS MIN_SAL,
        COUNT(E.EMPNO) AS CNT
FROM EMP E JOIN DEPT D USING(DEPTNO)
GROUP BY DEPTNO, D.DNAME;

 

--? JOIN ~ ON 사용 예시 

SELECT D.DEPTNO, D.DNAME,
        ROUND(AVG(E.SAL),0) AS AVG_SAL,
        ROUND(MAX(E.SAL),0) AS MAX_SAL,
        MIN(E.SAL) AS MIN_SAL,
        COUNT(E.EMPNO) AS CNT
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)
GROUP BY D.DEPTNO, D.DNAME;

 

-- 3개 이상의 테이블을 묶을 때

 

-- 기존 조인 방식

FROM TABLE1, TABLE2, TABLE3
WHERE TABLE1.COL = TABLE2.COL
AND TABLE2.COL = TABLE3.COL

 

-- SQL-99 방식

FROM TABLE1 JOIN TABLE2 ON (겹치는 = 부분끼리)
            JOIN TABLE3 ON (겹치는 = 부분끼리)