SQL

{ SQL } 데이터 모델링 / SQL 기본

데이터 모델링과 이해

스키마 3단계

  1. 외부스키마 - 사용자가 보는 관점에서 데이터베이스 스키마 정의 (사용자나 응용프로그램이 필요한 데이터 정의-View)
  2. 개념스키마 - 사용자 관점의 데이터 스키마를 통합해서 데이터베이스의 전체적인 ‘논리적’ 구조를 정의
  3. 내부스키마 - 데이터가 물리적으로 어떻게 저장되는지를 정의 - 물리적인 저장 구조 (컬럼정의,인덱스 등)

데이터 모델링 유의사항

  • 같은 정보를 저장하지 않도록 하여 중복성을 최소화한다.
  • 데이터 간의 상호 연관관계를 명확하게 정의하여 일관성 있게 데이터가 유지되게 한다.
  • 데이터의 정의를 프로세스와 프로세스의 집합은 프로그램과 분리하여 유연성을 높힌다.
  • 테이블과 프로그램과의 연계성이 높으면 사소한 변화에서 데이터 모델링에 큰 변화를 줌으로서 연계성을 낮춰 유연성을 높힌다.

엔터티 이름을 정할 때 유의사항

  • 약어 사용은 자제한다.
  • 현업에서 사용하는 이름으로 정한다.
  • 사용되지 않는 고립 엔터티는 제거를 고려한다.

엔터티 특징

  • 두 개 이상의 인스턴스의 집합이어야 한다.
  • 두 개 이상의 속성을 갖는다.
  • 하나의 속성은 한개의 속성만 갖는다.
  • 하나의 엔터티의 인스턴스는 다른 엔터티의 인스턴스 관계인 Pairing을 갖는다.

엔터티 관계 도출 시 고려사항

  • 연관 규칙과 정보의 조합이 발생하는지
  • 업무 기술서, 장표에 관계 연결에 대한 규칙이 있는지
  • 업무 기술서, 장표에 관계 연결을 가능하게 하는 동사가 있는지

엔터티 분류

  • 발생시점
    • 기본 엔터티: 그 업무에 원래 존재하는 정보, 다른 엔터티로부터 주식별자를 상속받지 않고 자신의 고유한 주식별자를 가짐 (사원,부서,고객,상품 등)
    • 중심 엔터티: 기본 엔터티로부터 발생되고 그 업무에서 중심적인 역할(계약, 사고, 청구, 주문, 매출 등)
    • 행위 엔터티: 2개 이상의 부모 엔터티로부터 발생, 상세 설계 단계나 프로세스와 상관모델링을 진행하면서 도출 (주문 - 고객<->상품)
  • 유형과 무형
    • 유형엔터티: 물리적 형태가 있음 (실체가 있음)
    • 개념엔터티: 물리적 형태가 없음(조직, 보험상품 등)
    • 사건엔터티: 업무 수행에 따라 발생함(주문,청구,미납 등)

분해 여부에 따른 속성

  • 단일속성: 하나의 의미로 구성이 된 속성 (회원이름, 성별, 핸드폰 번호 등)
  • 복합속성: 여러 의미로 구성되어 분해가 가능한 속성 (주소)
  • 다중값 속성: 속성에 여러 개의 속성을 가질 수 있는 경우 (상품 리스트) => 엔터티로 분해 해야함

속성

  • 업무상 인스턴스로 관리하고자 하는 더 이상 분리되지 않는 최소 데이터 단위를 나타냄
  • 하나의 인스턴스에서 각각의 속성은 반드시 하나의 속성값만을 가져야 한다(원자성)
  • 속성은 정해진 주식별자에 함수적 종속성을 가져야 한다.

주식별자 특징

  • 최소성: 유일성을 만족하는 최소한의 속성의 수로 구성되어야 한다 (굳이 필요없는 속성 조합은 제외)
  • 유일성: 주식별자로 인해 모든 인스턴스는 유일하게 구분되어야 한다 (이름 같은 중복이 가능한 속성은 주식별자가 될 수 없음)
  • 불변성: 주식별자가 한번 특정 엔터티에 지정되면 그 식별자의 값은 변하면 안된다 (자주 변하지 않는 값이어야 한다)
  • 존재성: 반드시 값이 존재해야 하며 NULL은 허용되지 않는다.

비식별자 / 식별자 관계

  • 식별관계: 두 개의 엔터티가 하나의 속성을 기본키로 ‘공유’하는 관계
    • 예) 사원번호를 기본키로 서로 다른 두 엔터티에서 기본키로 구별하면 식별 관계이다.
  • 비식별관계: 다른 엔터티에서는 기본키인데 한 엔터티에서는 일반속성인 경우
    • 예) 부서,사원의 관계에서 부서의 부서번호(기본키)를 사원에서는 일반키로 가짐

관계(Relationship)

  • 부모의 식별자를 자식에게 상속하여 상속된 속성을 매핑키로 사용하는 것을 뜻한다.
  • 관계명, 관계차수, 선택성 3가지 개념을 사용한다.
  • 관계의 pairing 이란 엔터티 안에 인스턴스가 개별적으로 관계를 가지는 것이다.
  • 엔터티와 엔터티의 관계는 차수로 표현된다.
  • 존재 관계: 한 엔터티의 존재가 다른 엔터티의 존재에 영향을 미치는 관계 (주문항목은 반드시 주문이 있어야 존재 가능)
  • 사원과 부서의 ‘소속’ 관계도 존재 관계
  • 행위 관계: 엔터티 간에 어떠한 행위가 있는 것 (고객 엔터티의 행동을 통해 주문 엔터티가 발생)
  • ERD 에서는 존재관계와 행위 관계를 구분하지 않는다.
  • 관계를 통해 데이터의 중복을 피하여 유지관리의 복합성을 줄일 수 있다.

함수 NULL 처리방식

  • NULL만 존재하면 그대로 NULL을 리턴하는 집계 함수: SUM,AVG,MIN,MAX..
  • NULL을 무시하는 함수: COUNT - NULL만 존재하면 0을 반환한다.

데이터 무결성 종류

  • 개체 무결성: 기본키를 구성하는 컬럼은 NULL 값이나 중복값을 가질 수 없다.
  • 참조 무결성: 참조하는 외래키는 존재하지 않거나 NULL 일 수 없다.
  • 도메인 무결성: 컬럼에 지정되는 값이 지정한 타입의 값이 아니거나 범위 밖일 수 없다. 즉, 정의된 도메인의 값이어야 한다.
  • NULL 무결성: 특정 컬럼에 NULL을 허용하지 않는 특징
  • 고유 무결성: 특정 속성에 대해 값이 중복되지 않는 특징
  • 키 무결성: 하나의 릴레이션에는 적어도 하나의 키가 존재해야 함(반드시 하나의 조인키를 가져야함)

용어

  • 도메인: 속성이 허용되는 값들의 범위 또는 집합
  • 튜플: 속성의 값(행)
  • 릴레이션: 중복된 튜플을 가지지 않는 행과 열
  • 키: 튜플을 고유하게 식별할 수 있는 속성

SQL 분류

  • DDL: CREATE, ALTER, DROP, TRUNCATE
  • DML: SELECT, UPDATE, DELETE, INSERT
  • DCL: COMMIT, ROLLBACK
  • TCL: GRANT, REVOKE

별칭 정의할 때 주의할점

  • 띄어쓰기나 특수기호 사용 시 쌍따옴표(“ “) 필수

테이블 구조 변경

  • 데이터 존재해도 컬럼 삭제가 가능
  • 다중 컬럼 추가시 반드시 괄호로 묶어 전달
  • 컬럼 추가시 새 컬럼 데이터는 NULL 로 삽입되므로 DEFAULT 값 선언 없이는 NOT NULL 속성을 갖는 컬럼추가는 불가능하다.
  • 기본키 생성 시 기본기 컬럼에 인덱스가 자동 생성된다.
  • 컬럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다
  • 컬럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가 할 수 있다.
  • 중간에 DEFAULT 값 변경 시 변경 작업 이후 발생하는 행 삽입에만 영향을 미친다.

DBMS 특징

  • DBMS에 저장된 데이터는 여러 사용자에게 공유 가능하다.
  • 허가된 사용자만이 참조할 수 있는 보안기능이 제공된다.
  • 실시간 접근, 계속적인 변화 적용에 유리하다.
  • 제약조건으로 데이터 무결성을 유지 할 수 있다.

DML 특징

  • DELETE 에 FROM 절 생략 가능
  • 원하는 데이터 DELETE시 WHERE 절 필수
  • DML은 반드시 COMMIT, ROLLBACK 을 입력하여 트랜잭션을 종료해야 한다: 하지 않으면 변경된 행의 잠금이 발생한다.
  • UPDATE 사용 시 동시에 여러 컬럼 수정 가능
  • ON DELETE SET NULL 옵션: 데이터 삭제시 자식의 외래키 컬럼은 NULL 로 수정

COMMIT

  • DML 후 데이터에 이상이 없을 경우 데이터를 저장하는 명령어
  • COMMIT 이전에 수행된 DML이 모두 저장되어 이전 데이터는 영원히 되돌릴 수 없다.
  • 데이터에 대한 변경 사항이 데이터베이스에 영구 저장되고 다른 사용자에게 공유되어 보여진다.
  • DML 수행 후 트랜잭션을 정상 종료 하지 않으면 LOCK이 걸린다.
  • 이때, COMMIT 을 해주면 변경된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작 할 수 있다.

ROLLBACK

  • 테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 변경을 취소하는 명령어.
  • DB에 저장되지 않고 최종 COMMIT 지점/ 변경 전/ 특정 SAVEPOINT 지점으로 원복된다.
  • 최종 COMMIT 이전까지 ROLLBACK 가능
  • SAVEPOINT: 사용자가 롤백을 원하는 시점으로 원하는 이름으로 설정가능

기본키

  • 기본키는 고유키와 NOT NULL 제약조건을 합친 것과 같다.
  • 중복될 수 없으며 NULL이 삽입 될 수 없다.

제약조건

  • 외래키를 생성한 경우 부모 테이블의 참조키 컬럼을 삭제 할 수 없다.
  • 제약 조건 추가시 제약 조건 이름을 명시하지 않을 수 있다.
  • 이미 존재하는 컬럼에 대해 NOT NULL 제약조건 추가 시 반드시 MODIFY로 처리한다.
  • NULL 값이 존재하더라고 중복된 값만 없다면 UNIQUE 제약조건을 추가 할 수 있다.

권한

  • 권한은 테이블 소유자와 관리자 모두 부여 할수있다.
  • 롤에 있는 권한을 회수하는 경우 롤을 부여받은 유저도 해당 권한을 잃게 된다.
  • WITH GRANT OPTION 을 통해 부여받은 테이블 조회 권한을 다른 유저에게 부여 할수있다.

정규화

  • 최소한의 데이터만을 하나의 엔터티에 넣는식으로 데이터를 분해하는 과정
  • 모델의 일관성을 확보하고 중복을 제거하여 모델의 독립성을 확보하는 과정
  • 데이터 이상현상을 줄이기 위한 기법

  • 엔터티를 상세화하는 과정으로 논리 데이터 모델링 수행 시점에서 고려

  • 제 1 정규화: 속성의 원자성 (한 속성이 하나의 값만 갖는 특성)을 갖도록 엔터티 분해
  • 제 2 정규화: 완전 함수 종속을 갖도록 분해 (기본키의 완전(전체)가 다른 컬럼을 결정한다)
  • 제 3 정규화: 이행적 종속을 갖지 않도록 분해 (일반속성은 주식별자 전체에 종속적이다)

트랜잭션

  • 하나의 연속적인 업무 단위
  • 무조건 두개 다 성공하거나 취소되어야 하는 연속적인 업무 단위 (필수적 관계 - 원자성)
  • 부분 COMMIT 불가 (동시 COMMIT 또는 ROLLBACK 처리)
  • 순차적 A,B 작업이 하나의 트랜잭션일 경우 A만 실행되고 시스템 장애 => UNDO 해야함

함수 NULL 처리방식

  • NULL만 존재하면 그대로 NULL을 리턴하는 집계 함수: SUM,AVG,MIN,MAX..
  • NULL을 무시하는 함수: COUNT - NULL만 존재하면 0을 반환한다.

데이터 무결성 종류

  • 개체 무결성: 기본키를 구성하는 컬럼은 NULL 값이나 중복값을 가질 수 없다.
  • 참조 무결성: 참조하는 외래키는 존재하지 않거나 NULL 일 수 없다.
  • 도메인 무결성: 컬럼에 지정되는 값이 지정한 타입의 값이 아니거나 범위 밖일 수 없다. 즉, 정의된 도메인의 값이어야 한다.
  • NULL 무결성: 특정 컬럼에 NULL을 허용하지 않는 특징
  • 고유 무결성: 특정 속성에 대해 값이 중복되지 않는 특징
  • 키 무결성: 하나의 릴레이션에는 적어도 하나의 키가 존재해야 함(반드시 하나의 조인키를 가져야함)

용어

  • 도메인: 속성이 허용되는 값들의 범위 또는 집합
  • 튜플: 속성의 값(행)
  • 릴레이션: 중복된 튜플을 가지지 않는 행과 열
  • 키: 튜플을 고유하게 식별할 수 있는 속성

SQL 분류

  • DDL: CREATE, ALTER, DROP, TRUNCATE
  • DML: SELECT, UPDATE, DELETE, INSERT
  • DCL: COMMIT, ROLLBACK
  • TCL: GRANT, REVOKE

별칭 정의할 때 주의할점

  • 띄어쓰기나 특수기호 사용 시 쌍따옴표(“ “) 필수

테이블 구조 변경

  • 데이터 존재해도 컬럼 삭제가 가능
  • 다중 컬럼 추가시 반드시 괄호로 묶어 전달
  • 컬럼 추가시 새 컬럼 데이터는 NULL 로 삽입되므로 DEFAULT 값 선언 없이는 NOT NULL 속성을 갖는 컬럼추가는 불가능하다.
  • 기본키 생성 시 기본기 컬럼에 인덱스가 자동 생성된다.
  • 컬럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다
  • 컬럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가 할 수 있다.
  • 중간에 DEFAULT 값 변경 시 변경 작업 이후 발생하는 행 삽입에만 영향을 미친다.

DBMS 특징

  • DBMS에 저장된 데이터는 여러 사용자에게 공유 가능하다.
  • 허가된 사용자만이 참조할 수 있는 보안기능이 제공된다.
  • 실시간 접근, 계속적인 변화 적용에 유리하다.
  • 제약조건으로 데이터 무결성을 유지 할 수 있다.

DML 특징

  • DELETE 에 FROM 절 생략 가능
  • 원하는 데이터 DELETE시 WHERE 절 필수
  • DML은 반드시 COMMIT, ROLLBACK 을 입력하여 트랜잭션을 종료해야 한다: 하지 않으면 변경된 행의 잠금이 발생한다.
  • UPDATE 사용 시 동시에 여러 컬럼 수정 가능
  • ON DELETE SET NULL 옵션: 데이터 삭제시 자식의 외래키 컬럼은 NULL 로 수정

COMMIT

  • DML 후 데이터에 이상이 없을 경우 데이터를 저장하는 명령어
  • COMMIT 이전에 수행된 DML이 모두 저장되어 이전 데이터는 영원히 되돌릴 수 없다.
  • 데이터에 대한 변경 사항이 데이터베이스에 영구 저장되고 다른 사용자에게 공유되어 보여진다.
  • DML 수행 후 트랜잭션을 정상 종료 하지 않으면 LOCK이 걸린다.
  • 이때, COMMIT 을 해주면 변경된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작 할 수 있다.

ROLLBACK

  • 테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 변경을 취소하는 명령어.
  • DB에 저장되지 않고 최종 COMMIT 지점/ 변경 전/ 특정 SAVEPOINT 지점으로 원복된다.
  • 최종 COMMIT 이전까지 ROLLBACK 가능
  • SAVEPOINT: 사용자가 롤백을 원하는 시점으로 원하는 이름으로 설정가능

기본키

  • 기본키는 고유키와 NOT NULL 제약조건을 합친 것과 같다.
  • 중복될 수 없으며 NULL이 삽입 될 수 없다.

제약조건

  • 외래키를 생성한 경우 부모 테이블의 참조키 컬럼을 삭제 할 수 없다.
  • 제약 조건 추가시 제약 조건 이름을 명시하지 않을 수 있다.
  • 이미 존재하는 컬럼에 대해 NOT NULL 제약조건 추가 시 반드시 MODIFY로 처리한다.
  • NULL 값이 존재하더라고 중복된 값만 없다면 UNIQUE 제약조건을 추가 할 수 있다.

권한

  • 권한은 테이블 소유자와 관리자 모두 부여 할수있다.
  • 롤에 있는 권한을 회수하는 경우 롤을 부여받은 유저도 해당 권한을 잃게 된다.
  • WITH GRANT OPTION 을 통해 부여받은 테이블 조회 권한을 다른 유저에게 부여 할수있다.

정규화

  • 최소한의 데이터만을 하나의 엔터티에 넣는식으로 데이터를 분해하는 과정
  • 모델의 일관성을 확보하고 중복을 제거하여 모델의 독립성을 확보하는 과정
  • 데이터 이상현상을 줄이기 위한 기법

  • 엔터티를 상세화하는 과정으로 논리 데이터 모델링 수행 시점에서 고려

  • 제 1 정규화: 속성의 원자성 (한 속성이 하나의 값만 갖는 특성)을 갖도록 엔터티 분해
  • 제 2 정규화: 완전 함수 종속을 갖도록 분해 (기본키의 완전(전체)가 다른 컬럼을 결정한다)
  • 제 3 정규화: 이행적 종속을 갖지 않도록 분해 (일반속성은 주식별자 전체에 종속적이다)

트랜잭션

  • 하나의 연속적인 업무 단위
  • 무조건 두개 다 성공하거나 취소되어야 하는 연속적인 업무 단위 (필수적 관계 - 원자성)
  • 부분 COMMIT 불가 (동시 COMMIT 또는 ROLLBACK 처리)
  • 순차적 A,B 작업이 하나의 트랜잭션일 경우 A만 실행되고 시스템 장애 => UNDO 해야함