REVI1337

Data Modeling

19 min read

Data Modeling

  • 데이터 모델링이란 정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법
  • 현실 세계의 데이터를 약속된 표기법으로 표현하는 과정
  • 데이터베이스를 구축하기 위한 분석 및 설계의 과정

Data Modeling 특징

  1. 추상화 : 현실세계를 일정한 형식에 맞추어 표현하려는 추상화의 의미를 갖는다.

  2. 단순화 : 복잡한 현실을 제한된 언어나 표기법으로 이해하기 쉽게할 수 있다.

  3. 정확화(명확화) : 애매모함을 배제하고 누구나 이해가 가능하도록 정확하게 현상을 기술할 수 있다.

    Data Modeling 의 중요성

  4. 파급효과

  5. 간결한 표현

  6. 데이터 품질 유지

Data Modeling 유의점

중복 (Duplication)

  • 여러 곳에 동일한 정보를 저장하는것을 지양해야 한다.

비유연성 (Inflexibility)

  • 데이터를 어떻게 설계했느냐에 따라 사소한 업무변화에도 데이터모델이 수시로 변경될 수 있다. 따라서 데이터 정의를 데이터의 사용 프로세스와 분리 하여 작은 변화에도 데이터모델이 수시로 변경되는 것을 줄여야 한다.

비일관성 (Inconsistency)

  • Modeling 을 할 때 데이터 간 상호 연관 관계를 명확하게 하여 데이터의 비일관성을 예방하여야 한다.

Data Modeling 3 단계

  1. 개념적 모델링 (계획/분석 = 추상) : ERD 도출, 업무중심, 포괄적인 수준의 모델링
  2. 논리적 모델링 (분석 = 정규화) : 테이블 도출, (key, 속성, 관계)를 표현, 재사용성, 정규화 수행
  3. 물리적 모델링 (설계 = DB) : DB구축, 물리적 성격, 개념적보다 구체적

데이터 독립성

데이터 독립성이란 데이터베이스의 구조와 데이터의 내용이 서로 영향을 미치지 않는 것을 의미한다. 데이터 독립성은 논리적 데이터 독립성과 물리적 데이터 독립성으로 나눌 수 있다.

데이터 독립성 장점

  • 데이터 독립성은 데이터베이스의 유지보수와 확장성을 향상시키는 장점을 갖는다.
  • 데이터베이스의 구조가 변경되더라도 응용 프로그램이나 사용자에게 영향을 주지 않으므로, 시스템의 안정성과 신뢰성을 높일 수 있다.
  • 데이터베이스의 성능이나 보안을 개선하기 위해 필요한 조치를 쉽게 적용할 수 있다.

논리적 데이터 독립성

논리적 데이터 독립성은 데이터베이스의 논리적 구조가 변경되어도 응용 프로그램이나 사용자의 요구에 영향을 주지 않는 것을 말한다.

물리적 데이터 독립성

물리적 데이터 독립성은 데이터베이스의 물리적 구조가 변경되어도 논리적 구조나 데이터의 내용에 영향을 주지 않는 것을 말한다. 예를 들어, 파일의 저장 방식이나 인덱스의 구성이 바뀌어도 데이터베이스의 스키마나 데이터는 그대로 유지될 수 있다.

Data Modeling 3 요소

  1. 엔티티 (Entity) : 데이터베이스를 구성하는 데이터 개체
  2. 관계 (Relationships) : 개체 사이에 존재하는 관계
  3. 속성 (Attributes) : 개체의 특성

데이터베이스 인스턴스 (Instance)

특정 시점에 데이터베이스에 실제로 저장되어 있는 데이터의 값. Entity 의 실제 데이터라고 생각하면 된다.

Database Schema

  • 스키마란 데이터베이스의 구조, 데이터 타입, 제약조건(Constraints) 에 관한 전반적인 명세를 기술한 것이다.
  • 데이터베이스의 논리적인 설계를 나타내며, 데이터베이스에서 어떤 데이터가 저장되고 어떻게 관련되어 있는지를 표현한다.
  • 데이터베이스 의 물리적 모델링 단계(설게 단계)에서 명시되며 자주 변경되지 않는다.

Database Schema 3단계 구조

스키마는 데이터베이스의 전체적인 모습을 보여주는 외부 스키마, 데이터베이스의 물리적인 저장 방식을 결정하는 내부 스키마, 그리고 외부 스키마와 내부 스키마 사이의 관계를 매핑하는 개념 스키마로 구성된다. 이렇게 3단계로 나뉘는 DB 구조를 3단계 데이터베이스 구조라고 한다.

외부 스키마

개념 스키마

내부 스키마

ERD

  • ERD (Entity Relationship Diagram) 는 개체 관계 모델이란 구조화된 데이터에 대한 일련의 표현이다.
  • 관계의 의미를 직관적으로 표현할 수 있는 수단이다.
  • Entity 를 사각형, Relationship 를 마름모, Attributes 를 타원형으로 표현한다.

ERD 작성 순서

  1. 엔터티 도출
  2. 엔티티 배치
  3. 엔티티 간 관계 설정 (가급적 Cycle 이 발생하지 않아야 한다.)
  4. 관계명 기술 (1:1, N:1, 1:N, N:M)
  5. 관계 차수 설정
  6. 선택사양 기술

Reference

데이터 독립성과 스키마

col1 col2 col3 30 null 20 null 50 10 0 10 null

위와 같은 tab_a 라는 테이블이 있을때

select sum(col2) + sum(col3) from tab_a where col1 >0; 의 결과가 뭐야

개념 정리

개념적인 단계 ⇒ 엔티티 물리적인 단계 ⇒ 테이블

엔티티

(엔티티 1) 주식별자 기본키 PK 외래키 FK 일반 속성 (주식별자, 외래키 에 포함되지 않는 속성

(엔티티 2) 분해 여부에 따른 속성 단일 속성 : 하나의 의미로 구성된 경우 (회원ID, 이름) 복합 속성 : 여러개의 의미로 구성된 경우 (주소 (시, 구, 동 으로 분해 가능)) 다중값 속성 : 속성에 여러 값을 가질 수 있는 경우 (상품 리스트)

관계

(관계의 종류) 존재적 관계 : 엔티티의 존재가 다른 엔티티의 존재에 영향을 미치는 관계 (부서 엔티티가 삭제되면 사원 엔티티의 존재에 영향) 행위적 관계 : 엔티티 간의 어떤 행위가 있는 것을 의미 (고객 엔티티의 행동에 의해 주문 엔티티가 발생)

(관계의 페어링)

  • 엔티티 안에 인스턴스가 개별적으로 관계를 가지는 것
  • 관계란 페어링의 집합을 의미함

(관계와 차수, 페어링 차이)

  • 학생과 강의 엔티티는 관계를 가짐
  • 한 학생은 여러 강의를 수강할 수 있고, 한 강의도 여러 학생에게 수강될 수 있으므로 M 대 N 관계이며, 이 떄 차수는 M : N 가 됨.
  • 인스턴스의 관계를 보면 “학생 A 가 강의 B 를 2023 년 1학기에 수강했고 성적은 A+ 을 받았다” 와 같은 특정한 페어링이 형성
  • 이런식으로 관계의 차수는 하나의 엔티티와 다른 엔티티 간의 레코드 연결 방식 을 나타내는 반면, 관계 페어링은 두 엔티티 간의 특정 연결을 설명하고 추가 정보를 제공하는 용도로 사용.

식별자

(주식별자 도출기준)

  1. 해당 업무에서 자주 이용되는 속성을 주식별자로 지정한다.
    • 같은 식별자 조건을 만족하더라도 업무적으로 더 많이 사용되는 속성을 주식별자로 지정
    • ex) 학생번호와 주민번호 중에 학생번호가 주식별자, 주민번호는 보조식별자
  2. 명칭이나 내역 등과 같은 이름은 피함
  3. 속성의 수를 최대한 적게 구성 (조인으로 인한 성능 저하 발생 우려)

(관계간 엔티티 구분)

  1. 강한 개체
    • 독립적으로 존재할 수 있는 엔티티
    • ex) 고객과 계좌 엔티티 중, 고객은 독립적으로 존재할 수 있음.
  2. 약한 개체
    • 독립적으로 존재할 수 없는 엔티티
    • ex) 고객과 계좌 엔티티 중, 계좌는 독립적으로 존재할 수 없음. (고객에 의해 파생되는 엔티티)

(식별 관계와 비식별관계)

  1. 식별관계
    • 하나의 엔티티의 기본키를 다른 엔티티가 기본키의 하나로 공유하는 관계
    • 식별관계는 ERD 에서 실선으로 표시
  2. 비식별관계
    • 강한 개체의 기본키를 다른 엔티티의 기본키가 아닌 일반 속성으로 관계를 가지는 것.
    • ERD 에서는 점선으로 표시

정규화

(제 1 정규화 (1NF))

  • 테이블의 컬럼이 원자성(한 속성이 하나의 값을 갖는 특성) 을 갖도록 테이블을 분리하는 단계
  • 쉽게 말해 하나의 행과 컬럼의 값이 반드시 한 값만 입력되도록 행을 분리하는 단계

(제 2 정규화 2NF)

  • 1NF 를 만족하면서 완전 함수 종속 을 만들도록 테이블을 분해하는 단계
    • 완전함수 종속이란, 기본키를 구성(1 개 또는 그 이상일 수 있음)하는 모든 컬럼의 값이 다른 컬럼을 결정짓는 상태
    • 기본키의 부분집합이 다른 커럼과 1 : 1 대응 관계를 갖지 않는 상태를 의미
  • 즉, PK 가 2개 개 이상일 때 발생하며 (PK 의 일부와 종속(1 : 1)되는 관계 == 완전 함수 종속 위배) 가 있다면 분리한다.

(제 3 정규화 3NF)

  • 2NF 를 만족하면서 이행적 종속 을 없애도록 테이블을 분리하는 단계
    • 이행적 종속성이란 A→B, B→C 의 관계가 성립할 때, A→C 가 성립되는 것을 말함
  • (A,B) 와 (B,C) 로 분리하는 것이 제 3 정규화

substr(col, start, count) ⇒ col 의 start 위치에서 count 개의 길이만큼 출력 len(start + count) start 는 음수일 수 있음. 음수여도 탐색은 오른쪽으로 탐색함.

instr(col, 찾을문자, 시작점, 번째) ⇒ col 에서 시작점부터 탐색하여 찾을문자 가 N 번째 인 index 르 출력 substr 과 다르게 시작점이 다르면 왼쪽으로 역방향 탐색을 함.

replace(col, 찾을문자, 치환문자) ⇒ (단어를 치환) translate(col, 찾을문자, 치환문자) ⇒ (문자를 치환) translate(col, ‘ab’, ‘AB’) 이면 a 를 A 로, b 를 B 로 치환한다.

isnull(col, val) == NVL(col, val) nvl2(col, if not null, if null)

DML

  • 반드시 transaction 으로 관리해주어야 하낟.

  • insert, delete, update, merge

  • truncate 는 dml 이 아님

    insert oracle 은 1 개씩만 할 수 있음 하지만 sql server 에서는 여러개를 같이 insert 가능 숫자를 ‘001’ 처럼 insert 는 가능하지만 (권장 X)

    update set 에 서브쿼리를 사용해서 여러 컬럼을 동시에 수정할 수 있음.

    merge 반드시 기준 테이블이 있어야 함. 기준테이블에 없는 데이터는 insert 됨 기준테이블과 겹치는 데이터는 병합하는 값 으로 update 됨

DDL

CREATE

ALTER

컬럼 추가 - ADD 사용 - 순서는 ALTER TABLE ADD 컬럼명 타입 [DEFAULT] [CONSRAINT] - 여러 컬럼 동시 추가 가능 (반드시 괄호 사용). 하나의 컬럼은 생략 가능. ADD (Col1 type, Col2 type) ADD 는 한번 사용된다. - 데이터가 있는 상태에서 새롭게 colmn 을 추가하면 not null 불가 (기존 데이터는 null 이 들어감) - 테이터가 있고 추가되는 컬럼에 not null 을 추가하고싶으면 type 뒤에 default 를 반드시 붙여야 한다. - 반면 데이터가 없는상태에서는 not null 추가 가능 컬럼 변경 - 컬럼 사이즈, 데이터 타입, default 값 변경 가능 - 여러개 동시 변경 가능. (괄호 필수) 컬럼 사이즈 변경 - MODIFY 를 사용 - 컬럼 사이즈 증가는 항상 가능 - 컬럼 사이즈 축소는 데이터 존재 여부에 따란 제한(데이터가 있는 경우 데이터의 최대 사이즈만큼 축소 가능) - 동시 변경 가능 (괄호 필수) - ex) ALTER TABLE test MODIFY (col_a NUMBER(10), col_b VARCHAR(6)); 데이터 타입 변경 - MODIFY 를 사용 - Syntax 는 컬럼 사이즈 변경과 동일 - 주의할 것은 데이터가 비어있을떄만 타입 변경 가능 - CHAR, VARCHAR 타입일 경우 데이터가 있어도 서로 변경 가능 컬럼 이름 변경 - RENAME COLUMN 를 사용 (명령에 COLUMN 이 들어가면 여러개의 컬럼을 동시에 변경할 수 없다.) - 데이터가 있건 없건 항상 변경 가능 - RENAME COLUMN a TO b 사용 - 동시 여러 컬럼 이름 변경 불가. 컬럼 삭제 - DROP COLUMN 사용 (마찬가지로 명령에 COLUMN 이 들어가기 때문에 여러개 동시삭제 불가) - 동시 여러 컬럼 삭제 불가

DROP

  • 객체 (테이블 인덱스) 삭제 가능
  • DROP 후에는 조회 불가
  • DROP TABLE table PURGE; ⇒ 영구 삭제
  • DROP TABLE table; ⇒ RECYCLEBIN 에서 조회 가능

TRUNCATE

  • 구조만 남기고 데이터만 즉시 삭제 (즉시 반영 AUTO COMMIT)
  • RECYCLE BIN 에 남지 않음 (복구 불가)

제약조건 (CONSTRINAT)

  • 테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약조건만 추가 가능 PK (기본키)

    • 유일한 식별자\

  • 제약 조건 이름이 있을수도 있고, 없을수도 있음.

    • 이름을 직접 만들려면 CONSTRAINT 이름 제약조건종류 로 정의할 수 있음.
    • 이름을 만들지 않으려면 그냥 제약조건 종류 만 선언. (제약조건 이름은 DBMS 가 알아서 만들어 줌)

  • NOT NULL 은 제약조건 생성이 아닌, 컬럼 수정 (MODIFY) 로 해결해야 한다.

    • FK
      • 반드시 참조(부모) 테이블의 참조 컬럼(REFERENCE KEY) 이 사전에 PK 혹은 UNIQ 를 가져야 함.

SQL 활용 꿀팁

  • cube((a, b)) = grouping_sets((a,b), (a), (b), ())

  • rank() 는 동일한 순위를 별개의 건수로 취급한다

다중행 서브쿼리 연산자 IN, any, all 사용가능 > ALL (2000, 3000) : 최댓값 3000 보다 큰 행들 반환 < ALL (2000, 3000) : 최솟값 2000 보다 작은 행들 반환 > ANY (2000, 3000) : 최솟값 2000 보다 큰 행들 반환 > ANY (2000, 3000) : 최댓값 3000 보다 작은 행들 반환

Recent Posts