SQL - 데이터모델링의 이해 (식별자)

공부 정리용! (아직 10점에 다가가지 못함) 

 

자료는 한국데이터산업진흥원 참고 사용   

https://dataonair.or.kr/db-tech-reference/d-guide/sql

SQL – DATA ON-AIR

 

1. 식별자(Identifiers)개념 

하나의 엔터티에서 구성되어 있는 여러 개의 속성 중에서 엔터티를 대표할 수 있는 속성 의미 

하나의 엔터티는 반드시 하나의 유일한 식별자 존재 

- 논리데이터모델 >> 식별자 사용 

- 물리데이터모델 >>데이터테이블에 접근을 위한 매개체로 '키' 사용

2. 식별자의 특징 

1) 주식별자 

- 주식별자에 의해 엔터티내에서 모든 인스턴스들이 유일하게 구분되어야 함

- 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수 

- 지정된 주 식별자 값은 자주 변하지 않는 것

- 주식별자가 지정되면 반드시 값이 들어와야 함 

 

>> 유일성 : 주식별자에 의해 엔터티 내의 모든 인스턴스들을 유일하게 구분 Ex) 사원번호

>> 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수 Ex) 사원번호+부서코드 =>부적절

>> 불변성 : 주식별자가 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야 함 Ex) 사원번호 변경 > 이전기록 말소 

>> 존재성 : 주식별자가 지정되면 반드시 데이터 값이 존재 (Null 불가) Ex) 사원번호 없는 회사 직원은 존재 불가

 

2) 타 식별자

대체식별자 : 주 식별자의 특징과 일치

외부식별자 : 주식별자의 특징과 일치하지 않으며 참조무결성 제약조건(Referential Integrity)에 따른 특징을 가짐 

3. 식별자 분류 및 표기법

가. 식별자 분류 

1) 대표성 여부 : 주식별자(Primary Identifier) vs 보조식별자(Alternate Identifier) 

   - 주식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있는 구분자 , 타 엔터티와 참조관계연결 가능 

   - 보조식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있는 구분자이나 대표성을 가지지 못함

 

2) 스스로 생성 여부: 내부식별자 VS 외부 식별자

   - 내부식별자 : 엔터티 내부에서 스스로 만듬 

   - 외부식별자 : 타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자  

 

3) 단일 속성 식별 여부 : 단일식별자(Single Identifier) VS 복합식별자(Composit Identifier) 

   - 단일식별자 : 하나의 속성으로 구성된 식별자 

   - 복합식별자 : 둘 이상의 속성으로 구성된 식별자 

 

4) 대체여부 : 본질식별자, 인조식별자 

   - 본질식별자 : 업무에 의해 만들어지는 식별자 

   - 인조 식별자  : 업무에 의해 만들어지지 않지만, 원조식별자가 복잡한 구성을 가져 인위적으로 만든 식별자

 

나. 식별자 표기법

4. 주식별자 도출기준

- 해당 업무에서 자주 이용되는 속성을 주식별자로 지정

- 명칭, 내역 등과 같이 이름으로 기술되는 것들은 가능하면 주식별자로 지정하지 않음

- 복합으로 주식별자를 구성할 경우 너무 많은 속성이 포함되지 않도록 함 

 

가. 해당 업무에서 자주 이용되는 속성을 주식별자로 지정하도록 함 

직원 (엔터티) - 주민번호 , 사원번호 (식별가능 속성)  >> 사원번호 (주식별자), 주민번호(보조식별자)

나. 명칭, 내역등과 같이 이름으로 기술되는 것은 피함

명칭이나 내역이 있고 인스턴스를 식별할 수 있는 구분자가 존재하지 않을 경우 새로운 식별자 사용

Ex)  부서이름이 유일하게 구분할 수 있다고 하여 사용하면 X > 일련번호,코드번호 를 생성하여 사용 

 

다. 속성의 수가 많아지지 않도록 함 

- 주식별자로 선정한 속성이 증손자 엔터티까지 상속되어 내려갈 경우 성능저하가 발생 가능

- 일반적으로 주식별자의 속성의 개수가 많다는 것(7~8개 이상)은 새로운 인조식별자 (Artificial Identifier)를 생성하여 데이터모델을 구성

- 복잡한 소스 구성을 피하기 위한 과도한 복합키 배제 필요 

5. 식별자와 비식별자 관계에 따른 식별자 

가. 식별자관계와 비식별자 관계의 결정

- 외부식별자(Foreign Identifier) : 자기자신의 엔터티에서 필요한 속성이 X,  다른 엔터티와의 관계를 통해 자식쪽 엔터티에 생성되는 속성. DB 생성 시에 Foreign Key 역활을 함 

- 자식엔터티에서 부모엔터티로 부터 받은 외부식별자를 자신의 '주식별자' 로 이용할 것인지 연결의 속성으로만 이용할 것인지 결정 필요 

나. 식별자 관계(Identifying Relationship)

자식엔터티의 주식별자로 부모의 주식별자가 상속이 되는 경우를 식별자 관계(Identifying Relationship)

1) 1:1 관계 : 부모로 부터 받은 속성을 자식 엔터티가 모두 사용하고 그것만으로 주식별자로 사용 시 

2) 1:M 관계 : 부모로 부터 받은 속성을 포함하여 다른 부모 엔터티에서 받은 속성을 포함하거나 스스로 갖고 있는 속성과 함께 주식별자로 구성 하는 경우 

Ex) 

- 발령 엔터티: 

1. 반드시 사원 엔터티가 있어야 자신이 생성

2. 주식별자 = 사원번호(주식별자가  부모 엔터티의 외부식별자) + 발령번호(자신의 속성)

사원 -발령 관계 = 1:M

 

- 임시직사원 : 

1. 임시직사원 주식별자 = 사원번호(사원의 주식별자)

사원-임시직사원 관계 = 1:1

 

다. 비식별관계 (Non-Identifying Relationship)

부모엔터티로 부터 속성을 받았지만, 자식엔터티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 경우 

1) 자식엔터티에서 받은 속성이 반드시 필수가 아니라도 무방. Ex) 부모없는 자식이 생성 될 수 있는 경우 

2) 엔터티별로 데이터생명주기(Life Cycle)을 다르게 관리할 경우

                      Ex) 부모엔터티에 인스턴스가 자식의 엔터티와 관계를 가지고 있었지만, 자식만 남겨두고 소멸될 경우 

                            >> 데이터 모델상에서 관계를 비 식별자로 조정하는게 가장 좋은 방법

3) 여러개의 엔터티가 하나의 엔터티로 통합되어 표현되었는데 각각의 엔터티가 별도의 관계를 가질때 

통합된 엔터티에 각각의 엔터티가 별도의 관계를 가지고 각 엔터티가 접수엔터티의 주식별자로 사용불가

4) 자식엔터티의 주식별자 보다 별도의 주식별자를 생성하는 것이 더 유리할때 비식별자 관게에 의한 외부식별자로 표현 

Ex) 계약번호 단독으로도 계약 엔터티의 주식별자를 구성 가능하여 하나만 가지는 것이 더 효율적이라 판단 할때

 

라. 식별자 관계로만 설정할 경우의 문제점 

- 식별자 관계로만 연결된 데이터모델의 특징은 주식별자 속성이 지속적으로 증가할 수 밖에 없는 구조를 지님

- 개발자의 복잡성과 오류가능성을 유발시킬 수 있는 요인이 될 수 있음 

PLANT(엔터티) - PK :PLANT(1개) 

EQUIPMENT - 관계 : 1:M -  PK : PLANTK(FK), EQUIPMENT_ID  (2개) 

EQPSTSTS - 관계 : 1:M - PK : PLANT(FK), EQUIPMENT_ID(FK)  (2개)

EQPEVTSTS - 관계 : 1:M - PK : PLANT(FK), EQUIPMENT_ID(FK), STATUS_SEQ  (3개)

EQPEVTSTSHST - 관계 : 1:M - PK : PLANT(FK), EQUIPMENT_ID(FK), STATUS_SEQ(FK), EVENT_ID, TRANS_TIME  (5개)

> PLANT 속성수가 1개 이고 관계가 1:M이므로 자식 엔터티는 PLANT엔터티 PK속성 수 +1  >> 무한 증식 

 

Ex)맨 하위에 있는 EQPEVTSTSHST에서 새로운 엔터티 EQP_ITEM, EQP_WORKER와 관계를 맺고 있을 때, 

     이 세 개의 엔터티에서 정보를 가져오는 SQL구문을 작성

SELECT A.EVENT_ID, A.TRANS_TIME, A.HST_DEL_FLAG, A.STATUS_PROMPT, A.STATUS_OLD, A.STATUS_NEW 
FROM EQPEVTSTSHST A, EQP_ITEM B, EQP_WORKER C 
    WHERE A.PLANT = B.PLANT 
        AND A.EQUIPMENT_ID = B.EQUIPMENT_ID 
        AND A.STATUS_SEQ = B.STATUS_SEQ 
        AND A.EVENT_ID = B.EVENT_ID 
        AND A.TRANS_TIME = B.TRANS_TIME 
        AND B.ITEM_CD = 'A001' 
        AND A.PLANT = C.PLANT 
        AND A.EQUIPMENT_ID = C.EQUIPMENT_ID 
        AND A.STATUS_SEQ = C.STATUS_SEQ 
        AND A.EVENT_ID = C.EVENT_ID 
        AND A.TRANS_TIME = C.TRANS_TIME 
        AND C.WORKER_SID = 'A012008001'

 

>>식별자 관계만으로 연결된 데이터 모델의 특징은 주식별자 속성이 지속적으로 증가할 수 밖에 없는 구조로서 개발자 복잡성과 오류가능성의 유발 요인이 됨 

 

마. 비식별자 관계로만 설정할 경우의 문제점 

각각의 엔터티에 있는 중요 기준 속성이 부모 엔터티의 PK속성으로부터 상속되어 자식엔터티에 존재하는 경우가 다수

Ex) 주민등록번호, 사원번호, 주문번호, 목록번호 등 

이 속성은 부모를 조회하거나 자식엔터티의 데이터 조회시에도 사용되는데, 데이터 모델링 전개시 각 엔터티 관계를 비식별자 관계로 설정하면 이런 속성이 자식엔터티로 상속되지 않음 > 부모 엔터티까지 찾아가야 함 

REP001T, REP005T, REP007T 간에 비식별자 관계로 연결되어 REP007T 에서 '점검번호', '분야번호' 속성 검색시 불필요한 조인이 다량으로 발생되며 SQL 성능저하의 현상 발생 

>> 표 I-1-9와 같이 일정한 규칙을 가지고 데이터 모델링을 하는 기술이 필요하며, 아래 고려사항을 데이터 모델링에 반영하면 효과적인 데이터 모델을 만들 수 있음 

 

바. 식별자관계와 비식별자관계 모델링 

1) 비식별자관계 선택 프로세스 

실 프로젝트에서 식별자관계와 비식별자 관계를 취사선택 함으로 식별자 관계에서 비식별자 관계를 파악하는 기술이 중요

기본적으로 식별자 관계로 모든 관계가 연결되면서 다음 조건에 해당할 경우, 비식별자 관계로 조정 

- 관계분석 

비식별자 관계 설정 고려: 관계의 강/약분석(약한 관계) > 자식테이블 독립PK 필요 (독립PK 구성) > SQL 복잡도 증가/개발 생산성 저하(PK속성 단순화)

>>> 가장 중요 요인은 자식엔터티의 독립된 주식별자 구성이 필요한지 분석  (업무적 필요성/성능상 필요 모두 고려)

 

2) 식별자와 비식별자관계 비교

3) 식별자와 비식별자를 적용한 데이터 모델 

 

SQL 자격검정 실전문제 (오답노트)

10. 다음 중 엔터티의 특징으로 가장 부적절한 것은?
1. 속성이 없는 엔터티는 있을 수 없다. 엔터티는 반드시 속성을 가져야 한다.
2. 엔터티는 다른 엔터티와 관계가 있을 수 밖에 없다. 단, 통계성 엔티티나, 코드성 엔티티의 경우 관계를 생략할 수 있다.
3. 객체지향의 디자인 패턴에는 싱글턴패턴이 있어 하나의 인스턴스를 가지는 클래스가 존재한다. 이와 유사하게 엔터티는 한 개의 인스턴스를 가지는 것 만으로도 충분한 의미를 부여할 수 있다.
4. 데이터로서 존재하지만 업무에서 필요로 하지 않으면 해당 업무의 엔터티로 성립될 수 없다.

 

13. 다음 중 엔터티의 이름을 부여하는 방법으로서 가장 부적절한것은?
1. 가능하면 약어를 사용하여 엔터티의 이름을 간결하고 명확하게 한다.
2. 현업의 업무 용어를 사용하여 업무상의 의미를 분명하게 한다.
3. 모든 엔터티에서 유일한 이름이 부여되어야 한다.
4. 엔터티가 생성되는 의미대로 자연스럽게 부여하도록 한다.

 

14. 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위를 무엇이라 하는가?

 

16. 다음 중 아래와 같은 사례에서 속성에 대한 설명으로 가장 부적절한것은?

우리은행은 예금분류(일반예금, 특별예금)의 원금, 예치기간, 이자율을 관리할 필요가 있다. 또한 원금에 대한 이자율을 적용하여 계산된 이자에 대해서도 속성으로 관리하고자 한다. 예를 들어 원금이 1000원이고 예치기간이 5개월이며 이자율이 5.0%라는 속성을 관리하고 계산된 이자도 관리한다. 일반예금이나 특별예금 등에 대해서는 코드를 부여(예. 01 - 일반예금, 02 - 특별예금)하여 관리한다.

1. 일반예금은 코드 엔터티를 별도로 구분하고 값에는 코드값만 포함한다.
2. 원금, 예치기간은 기본(BASIC) 속성이다.
3. 이자와 이자율(DERIVED)은 파생속성이다.
4. 예금분류는 설계(DESIGNED) 속성이다

 

20. 다음 중 데이터모델링의 관계에 대한 설명으로 가장 부적절한 것을 2개 고르시오.
1. 관계는 존재에 의한 관계와 행위에 의한 관계로 구분될 수 있으나 ERD에서는 관계를 연결할 떄 존재와 행위를 구분하지 않고 단일화된 표기법을 사용한다.
2. UML에는 클래스다이어그램의 관계 중 연관관계와 의존관계가 있고 이것은 실선과 점선의 표기법으로 다르게 표현이 된다.
3. 관계는 존재에 의한 관계와 행위에 의한 관계로 구분될 수 있고 ERD에서는 관계를 연결할 때, 존재와 행위를 구분하여 실선과 점선의 표기법으로 다르게 표현한다.
4. UML에는 클래스다이어그램의 관계 중 연관관계와 의존관계가 있으나 구분하지 않고 단일화된 표기법을 사용한다.

 

 

3/1/속성/3/3.4

16. >>이자:파생속성, 이자율:기본속성

 

 

참고사이트 : 

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=5&mod=document&uid=329 

식별자

SQL 자격검정 실전문제
https://product.kyobobook.co.kr/detail/S000001399867

SQL 자격검정 실전문제 | 한국데이터진흥원 - 교보문고